MySQL的数据导入导出

1. mysqldump数据导入导出

1.1 数据导出

• 导出前先检查一下该库有没有开启GTID。

MariaDB [database]> show global variables like '%gtid%';

MariaDB [database]> show master status;

如果开启了gtid模式的话，请在导出的时候，加上参数：--set-gtid-purged=off

• 导出的时候压缩下，有的时候下载数据包到自己机器的时候，可能会丢包。压缩下就好了。

• 导出的时候，检查下表的行数，一会儿和导入的数据做double check。

MariaDB [none]> show databases;

MariaDB [database]> use database;

MariaDB [database]> show tables;

MariaDB [database]> select count(*) from tbs;

全库导出

shell> mysqldump -R -E --triggers \
--master-data=2 --single-transaction  -A \
-uroot -p -S /data/mysql/3322/mysql.sock|gzip > /tmp/full.sql.gz

指定库导出

shell> mysqldump -R -E --triggers --master-data=2 --single-transaction \
-uroot -p -S /data/mysql/3322/mysql.sock \
-B sakila world_x|gzip > /tmp/db.sql.gz

指定表导出

shell> mysqldump -R -E --triggers --master-data=2 --single-transaction \
-uroot -p -S /data/mysql/3322/mysql.sock \
world_x city country countryinfo|gzip > /tmp/tbs.sql.gz

所有表的单独备份

这种情况下，需要有secure-file-priv权限。

mysql> select concat("mysqldump -uroot -p ",table_schema," ",table_name," --master-data=2 --single-transaction -R -E --triggers|gzip >/backup/",table_schema,"_",table_name,".sql.gz") 
from information_schema.tables 
where table_schema not in ('sys','information_schema','performance_schema')
into outfile "/tmp/file_name";

1.2 数据的导入

导入数据的时候，最好把binlog关掉，可以快点。
以下用导入表作为例子。
导入表的时候，需要先use到需要导入的database。

# gzip -d /tmp/tbs.sql.gz
# ll
# mysql -S /data/mysql/3322/mysql.sock -uroot -proot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 30169
Server version: 10.1.17-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;

MariaDB [(database)]> use database;

MariaDB [(database)]> show variables like '%sql_log_bin%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
+---------------+-------+
1 row in set (0.01 sec)

MariaDB [(database)]> set sql_log_bin = 0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(database)]> show variables like '%sql_log_bin%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [(database)]> source /tmp/tbs.sql

MariaDB [(database)]> set sql_log_bin = 1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(database)]> show variables like '%sql_log_bin%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
+---------------+-------+
1 row in set (0.01 sec)

MariaDB [(database)]> show tables;

MariaDB [(database)]> select count(*) from tbs;

---

2. 需要CSV的数据

2.1 有secure-file-priv权限

MariaDB [(none)]> show global variables like '%secure_file_priv%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |  /tmp |
+------------------+-------+
1 row in set (0.00 sec)

2.1.1 mysqldump导出CSV

shell> mysqldump -uroot -p -S /data/mysql/mysql.sock -t \
-T /tmp/ discuz pre_common_member \
--fields-terminated-by=',' --fields-enclosed-by='"' \
--lines-terminated-by='\n'  

2.1.2 SELECT INTO OUTFILE导出

MariaDB [(database)]> select app,day,hour_period,user_numbers,model 
from spark_app_time_day 
INTO OUTFILE '/tmp/tbs.csv' 
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\n';

2.1.3 LOAD DATA INFILE导入

MariaDB [(database)]> begin; //开启事务
MariaDB [(database)]> LOAD DATA INFILE '/tmp/tbs.csv'
INTO TABLE st_app_time_day
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(app,day,hour_period,user_numbers,model);
MariaDB [(database)]> commit; //确定没问题，提交事务
MariaDB [(database)]> rollback; //如果数据导入有误，回滚整个事务

2.2 无secure-file-priv权限

MariaDB [(none)]> show global variables like '%secure_file_priv%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv |       |
+------------------+-------+
1 row in set (0.00 sec)

2.2.1 msyql -e导出

shell> mysql -S /data/mysql/3322/mysql.sock -uroot -proot -e \
'select * from test.file_push_log ' > /tmp/tbs.txt