MySQL学习——备份和还原
摘要：本文主要学习了如何备份和还原数据库。
部分内容来自以下博客：
https://www.cnblogs.com/chenmh/p/5300370.html
常用命令
导出数据
导出2019年1月的数据，数据库是ide，表是user，要求不需要导出建库语句，要求使用完成的插入语句，并且合并插入：
1 [root@localhost ~]# mysqldump -h 127.0.0.1 -P 3306 -uroot -p'123456' --no-create-info --databases ide --tables user 2 > --where=" date >= '2019-01-01' and date < '2019-02-01' " --complete-insert --extended-insert > '/data/dump/201901.txt'
导入数据
1 [root@localhost ~]# mysql -uroot -p'123456' --database ide < db_back.sql
导出数据
导出数据一般使用mysqldump命令在Linux系统执行。
语法
导出全部数据库：
1 mysqldump [OPTIONS] --all-databases [OPTIONS]
导出指定数据库：
1 mysqldump [OPTIONS] --databases DB1 [DB2 DB3...] [OPTIONS]
导出指定数据库的指定表：
1 mysqldump [OPTIONS] --database DB --tables TB [TB2 TB3...] [OPTIONS]
说明
用户名：
登录数据库的用户名，当前用户可省略该配置，“-u”后面紧跟用户名，或者使用“--user=”后面紧跟用户名的方式。
1 --user, -u
密码：
登录数据库的密码，“-p”后面紧跟密码，或使用“--password=”后面紧跟密码的方式。
1 --password, -p
主机地址：
设置数据库所在的主机地址，默认是localhost。
1 --host, -h
端口号：
设置用于连接的端口号。
1 -P, --port
连接协议：
设置使用的连接协议，取值有TCP、SOCKET、PIPE、MEMORY四种。
1 --protocol
套接字文件：
指定当连接localhost时的套接字文件位置，默认路径是/tmp/mysql.sock。
1 -S, --socket
导出全部数据库：
1 --all-databases, -A
导出指定数据库：
1 --databases, -B
导出指定表：
只能针对一个数据库进行导出，并且导出的内容中没有创建数据库的判断语句。
1 --tables
不导出指定表：
指定忽略多个表时，需要重复多次，每次一个表，每个表必须同时指定数据库和表名。
1 --ignore-table
导出全部表空间：
1 --all-tablespaces, -Y
不导出表空间信息：
1 --no-tablespaces, -y
不添加创建数据库语句：
默认添加。
1 --no-create-db, -n
添加删除数据库语句：
默认不添加。
1 --add-drop-database
不添加创建表语句：
默认添加。
1 --no-create-info, -t
添加删除表语句：
默认添加，使用“--skip-add-drop-table”参数取消。
1 --add-drop-table 2 –-skip-add-drop-table
不导出数据只导出表结构：
1 --no-data, -d
导出存储过程以及自定义函数：
1 --routines, -R
在每个表导出前锁表并在导出后解锁：
默认为打开状态，使用“--skip-add-locks”参数取消。
1 --add-locks 2 --skip-add-locks
设置默认的字符集：
默认是utf8。
1 --default-character-set
在客户端和服务器之间启用压缩传递所有信息：
1 --compress, -C
处理换行：
直接输出到指定文件中。该选项应该用在使用回车换行对（\r\n）换行的系统上（例如：DOS，Windows）。该选项确保只有一行被使用。
1 --result-file, -r
添加导出时间：
将导出时间添加到输出文件中。默认为打开状态，使用“--skip-dump-date”参数取消。
1 --dump-date 2 --skip-dump-date
只导出符合指定条件的记录：
如果条件包含命令解释符专用空格或字符，一定要将条件引用起来。
1 --where, -w
合并多个插入语句：
默认开启，使用“--skip-extended-insert”关闭。
1 --extended-insert 2 --skip-extended-insert
使用完整的插入语句：
使用包含列名称的完整插入语句。这么做能提高插入效率，但是可能会受到max_allowed_packet参数的影响而导致插入失败。
1 --complete-insert, -c
忽略插入错误：
默认不添加，在插入语句中添加忽略错误语句。
1 --insert-ignore
服务器发送和接受的最大包长度：
客户端/服务器之间通信的缓存区的最大大小，最大为1GB。
1 --max_allowed_packet
强制插入：
在表转储过程中，即使出现SQL错误也继续。
1 --force, -f
实例
导出全部数据库：
1 [root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases > db_back.sql
导出指定数据库：
1 [root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --databases test demo > db_back.sql
导出指定表：
1 [root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --database test --tables demo user > db_back.sql
不导出指定表：
1 [root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases 2 > --ignore-table=database.table1 --ignore-table=database.table2 > db_back.sql
设置默认字符集：
1 [root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases --default-character-set=latin1 > db_back.sql
处理换行：
1 [root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases --result-file=/tmp/mysqldump_result_file.txt
只导出符合指定条件的记录：
1 [root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases --where=" id=1 limit 10"
使用完整的插入语句：
1 [root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases --complete-insert
导入数据
导入数据有两种方式，一种是在Linux系统使用<命令导入，另一种是在MySQL数据库使用source命令导入。
语法
在Linux系统使用<命令：
1 mysql -u用户名 -p密码 数据库名 < 文件路径
在MySQL数据库使用source命令：
1 source 文件路径
实例
在Linux系统使用<命令：
1 [root@localhost ~]# mysql -uroot -p123456 database < db_back.sql
在MySQL数据库使用source命令：
1 mysql> source /home/user/data/db_back.sql
效率比较
在文件较小的情况下，source速度比mysql高。在导入大的文件时，建议使用mysql命令。
优化导入速度
对于百M级以上文件，根据MySQL官方建议，有几个措施可以极大提高导入的速度。
修改配置文件
配置如下：
1 [mysqld] 2 bulk_insert_buffer_size=2G; 3 innodb_log_buffer_size=2G; 4 innodb_autoinc_lock_mode=2;
说明：
对于MyISAM，调整系统参数：bulk_insert_buffer_size。改为至少单个文件大小的2倍以上。
对于InnoDB，调整系统参数：innodb_log_buffer_size。改为至少单个文件大小的2倍以上，导入完成后可以改回默认的8M，注意不是innodb_buffer_pool_size。
有自增列的，设置：innodb_autoinc_lock_mode的值为2。
删除索引
除主键外，删除其他索引，导入完成后重建索引。
执行批量脚本
bash脚本内容：
1 for SQL in *.sql; 2 do 3 echo $SQL; 4 sed -i '1iSET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0;' $SQL 5 sed -i '$aCOMMIT; SET autocommit=1; SET unique_checks=1; SET foreign_key_checks=1;' $SQL 6 done
说明：
关闭自动提交：autocommit=0。
关闭唯一索引检查：unique_checks=0。
关闭外键检查：foreign_key_checks=0。