mysql导数据及注意事项

导出表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)