MySQL数据库导出导入备份

 

MySQL数据库备份

目录

1.  mysqldump / mysqlimport

2.  mysqlpump

3. select ... into outfile / load data 

优化

出现问题

---

1.  mysqldump / mysqlimport

mysqldump官方文档、 mysqlimport 官方文档  单线程

# 导出
mysqldump -uroot -p --single-transaction --all-databases > /tmp/test_db.sql

# 导入,必须表存在
mysqlimport -uroot -p --local test_table /tmp/test_table.sql

其他导入方式

# mysql中导入
source /tmp/test_db.sql;

# 其他
mysql -uroot -p test_db < /tmp/test_db.sql
mysqldump -uroot -p test_db < /tmp/test_db.sql
cat /tmp/test_db.sql | mysql -uroot -p -h <host>

# 查询表
mysql -uroot -p -e 'select * from test_db.test_table'

mysqldump常用参数 

-u, --user                  用户名
-p, --password              密码
-P, --port                  端口
-h, --host                  数据库地址
-A, --all-databases         导出全部数据库
-B, --databases,            导出指定数据库,多个库空格分隔
-d, --no-data               只导出表结构，不导出数据
-t, --no-create-info        只导出数据,不导出建表语句
-n, --no-create-db          只导出数据,不导出建库语句  
--tables                    指定表,--ignore-table排除表
-r, --result-file           指定输出文件
-R, --routines              导出存储过程以及自定义函数
-Y, --all-tablespaces       导出全部表空间
-l, --lock-tables           开始导出时锁定所有表
-C, --compress              在客户端和服务器之间启用压缩传递所有信息
-r, --replace               如果有相同的行就覆盖
--single-transaction        导出开始时开启一个事务,不锁表锁库,只对innodb有效
--add-drop-database         建库前先删除已存在的库,同drop database if exists
--add-drop-table            建库前先删除表,默认为打开状态,-skip-add-drop-table取消
--add-locks                 导出前锁表,导出完unlock,默认打开,--skip-add-locks关闭
-x, --lock-all-tables       锁定所有表,自动关闭--add-locks和--single-transaction
--flush-logs                导出前刷新日志,同时锁表
--flush-privileges          刷新
--force                     忽略错误
-w, --where                 只导出满足条件的数据,--where="user='root'"

示例，导出remain_result 和user_new_login 两个库所有表的建表语句

time mysqldump -uroot -p --databases remain_result user_new_login -d \
--single-transaction --default-character-set=utf8 > /tmp/db_create_schema.sql

 mysqlimport常用参数

-u, --user                  用户名
-p, --password              密码
-h, --host                  数据库地址
-L, --local                 从本机任意路径导入数据
-c, --columns=name          导入指定字段
-C, --compress              指定压缩格式
-l, --lock-tables           写入时锁定所有表
--fields-terminated-by      指定分隔符
--ignore-lines=n            忽略前n行

 

2.  mysqlpump

官方文档  5.7以后的新特性，多线程备份工具。

# 导出
mysqlpump -uroot -p -B test_db --single-transaction --default-parallelism=3 > /tmp/test_db.sql;

# 导入
source /tmp/test_db.sql

常用参数

-u, --user=username         用户名
-p, --password=             密码
-P, --port=                 端口
-h, --host=                 数据库地址
-A, --all-databases=	    导出所有数据库
-B, --databases=            导出指定数据库，多个库之间用逗号分隔
-C,--compress-output=       将导出数据压缩，目前支持的压缩算法有LZ4和ZLIB
-d, --skip-dump-rows        只导出表结构，不备份数据
--default-parallelism=n     并行线程数，默认为2，如果设置为0，表示不使用并行备份
--single-transaction        备份开始时开启事务,设置隔离级别为可重复读,只对innodb有效
-r, --result-file           指定输出文件
--default-character-set=    设置编码
--defer-table-indexes  	    延迟创建索引，将全部数据备份结束后再创建索引，默认开启
--exclude-databases=        导出时排除的数据库，多个数据库用逗号分隔,--include-databases指定备份库
--exclude-tables=           备份时排除的表，多个表用逗号分隔,--include-tables指定备份的表
--parallel-schemas=         指定并行备份的库，多个库用逗号分隔,--parallel-schemas=3:table1,table2 
--add-drop-database         建库前先删除已存在的库,同drop database if exists
--add-drop-table            建表前先删除表
--triggers                  导出触发器,默认开启,--skip-triggers关闭
--routines                  导出存储过程和函数,默认开启,--skip-routines关闭
--users                     导出grant tables用户授权表,要导出使用mysqlpump --exclude-databases=% --users
--watch-progress=           显示导出进度，默认开启

 实例，导出user_new_login库

time mysqlpump -uroot -p -B user_new_login --default-character-set=utf8 \
--single-transaction --default-parallelism=10 > /tmp/db_user_new_login.sql

7G，301个表，8700多万条数据，1分钟左右。 

 

3. select ... into outfile / load data 

官方文档、 load data官方文档   只导出纯数据，不会导入结构，必须结合使用。

# 导出
select * into outfile '/tmp/data.csv' fields terminated by ',' from tdatabase.table;  

# 导入
load data infile '/tmp/data.csv' into table tdatabase.table fields terminated by ',';  

 导出583w条数据9.84秒。 

 

优化

InnoDB引擎关闭自动提交

# 查看状态
show variables like "autocommit";

# 暂时关闭自动提交,不会把每个插入语句当成一个事务,开启长连接直到断开连接或者commit/rollback
set autocommit=0;

# 导入完成再开启
set autocommit=1;

MyISAM引擎关闭索引

# 大批量导入时先禁用索引
alter table table_name disable keys;

# 导入完成再开启
alter table table_name enable keys;

 

出现问题

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

show variables like '%secure_file_priv%';

 

vim /etc/my.conf

# 添加/修改 为空
secure-file-priv =