导出表test
mysql> use db1;
Database changed
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set (0.01 sec)使用mysqldump导出
加--single-transaction参数
[root@miles21 ~]# mysqldump -uroot -pbeijing --socket=/data/mysql.sock --single-transaction db1 test > /home/mysql/table_db1_test.sql
查看对应的general日志
151207 11:38:53 8 Connect root@localhost on
8 Query /*!40100 SET @@SQL_MODE='' */
8 Query /*!40103 SET TIME_ZONE='+00:00' */
8 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ #设置隔离级别为可重复读
8 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ #开启新事务
8 Query SHOW VARIABLES LIKE 'gtid\_mode'
8 Query UNLOCK TABLES
...
不加--single-transaction参数
[root@miles21 ~]# mysqldump -uroot -pbeijing --socket=/data/mysql.sock db1 test > /home/mysql/table_db1_test.sql
查看对应的general日志,可以看到LOCK TABLES的锁表操作。
...
9 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
9 Init DB db1
9 Query SHOW TABLES LIKE 'test'
9 Query LOCK TABLES `test` READ /*!32311 LOCAL */ #锁表
...
而且使用--single-transaction参数对导出的用户的权限要求更低
mysql> grant select on *.* to test@'localhost' identified by 'beijing';
Query OK, 0 rows affected (0.01 sec)
加--single-transaction参数，mysqldump正常导出
[root@miles21 ~]# mysqldump -utest -pbeijing --socket=/data/mysql.sock db1 test > /home/mysql/table_db1_test.sql
不加--single-transaction参数，mysqldump会报错
[root@miles21 ~]# mysqldump -utest -pbeijing --socket=/data/mysql.sock db1 test > /home/mysql/table_db1_test.sql
mysqldump: Got error: 1044: Access denied for user 'test'@'localhost' to database 'db1' when doing LOCK TABLES
导出csv文件
需要file权限
[root@miles21 mysql]# mysqldump -utest -pbeijing --socket=/data/mysql.sock --single-transaction --fields-terminated-by=, db1 test -T /home/mysql
mysqldump: Got error: 1045: Access denied for user 'test'@'localhost' (using password: YES) when executing 'SELECT INTO OUTFILE'
mysql> grant file on *.* to test@'localhost';
Query OK, 0 rows affected (0.00 sec)
[root@miles21 mysql]# mysqldump -utest -pbeijing --socket=/data/mysql.sock --single-transaction --fields-terminated-by=, db1 test -T /home/mysql
[root@miles21 mysql]# ll
...
-rw-r--r--. 1 root root 1335 Dec 7 16:32 test.sql #表定义文件
-rw-rw-rw-. 1 mysql mysql 24 Dec 7 16:32 test.txt #数据文件
查看对应的general日志
...
SELECT /*!40001 SQL_NO_CACHE */ * INTO OUTFILE '/home/mysql/test.txt' /*!50138 CHARACTER SET binary */ FIELDS TERMINATED BY ',' FROM `test`
...
所以可以直接使用select into outfile
mysql> select * from test into outfile '/home/mysql/test2.txt' fields terminated by ',';
Query OK, 5 rows affected (0.00 sec)
也可以根据业务需要使用连表查询
mysql> select a.id,a.name,b.age from test a,test2 b where a.id=b.id into outfile '/home/mysql/join.txt';
Query OK, 3 rows affected (0.00 sec)
