Mysql之备份与还原
1. Mysql常用的备份工具
1）mysqldump：
通常为小数据情况下的备份
innodb： 热备，温备
MyISAM, Aria: 温备
单线程备份恢复比较慢
2）Xtrabackup（通常用innobackupex工具）：
备份mysql大数据
InnoDB热备，增量备份；
MyISAM温备，不支持增量，只有完全备份
属于物理备份，速度快；
3）lvm-snapshot：
接近于热备的工具：因为要先请求全局锁，而后创建快照，并在创建快照完成后释放全局锁；
使用cp、tar等工具进行物理备份；
备份和恢复速度较快；
很难实现增量备份，并且请求全局需要等待一段时间，在繁忙的服务器上尤其如此；
除此之外，还有其他的几个备份工具：
-->mysqldumper: 多线程的mysqldump
-->SELECT clause INTO OUTFILE '/path/to/somefile' LOAD DATA INFILE '/path/from/somefile'
部分备份工具， 不会备份关系定义，仅备份表中的数据；
逻辑备份工具，快于mysqldump，因为不备份表格式信息
-->mysqlhotcopy: 接近冷备，基本没用
2. 数据备份与还原
1. 物理备份与还原
1. 直接复制整个数据库目录
Linux平台下，数据库目录位置通常为/var/lib/Mysql,将这个目录迁移到备份服务器上，然后启动数据库，再把binlog日志恢复一下，就可以了。
2. 备份的过程(保持数据的一致性)
1. 停止数据库
/etc/init.d/mysql stop
2. tar备份数据库文件目录
tar -czf /backup/mysql-`date +%F`_full.tar.gz /server/mysql
3. 启动数据库
/etc/init.d/mysql start
4. 把打包的压缩文件拷贝其它服务器上
3. 还原的过程
1. 迁移到其它服务器上
1. 停止数据库
/etc/init.d/mysql stop
2. 清理环境
rm -rf /server/mysql
3. 导入备份数据
tar -zxvf mysql-2019-07-17_full.tar.gz -C /
4. 启动数据库
/etc/init.d/mysql start
5. 根据binlog恢复剩下的数据
2. Xtrabackup备份(属于物理备份)
1. 安装xtrabackup
2. 完整备份
innobackupex --defaults-file=/etc/my.cnf --user=root --password='123.com' --socket=/var/lib/mysql/mysql.sock --backup /root 会自动生成一个以当前时间为名的目录
3. 还原完整备份
先停止mysql服务
/etc/init.d/mysql stop
备份数据目录
mv /var/lib/mysql /var/lib/mysqlback
创建一个新的数据目录
mkdir /var/lib/mysql
准备恢复文件
innobackupex --defaults-file=/etc/my.cnf --user=root --password='123456' --apply-log /root/2021-11-08_16-53-10/
执行恢复
innobackupex --defaults-file=/etc/my.cnf --user=root --password='123456' --copy-back /root/2021-11-08_16-53-10/
更改目录权限
chown -R mysql.mysql /var/lib/mysql
启动数据库
/etc/init.d/mysql start
配置主从信息
CHANGE MASTER TO MASTER_HOST='10.104.xx.xx',MASTER_PORT=3306,MASTER_USER='root',MASTER_PASSWORD='gzydro16pass',MASTER_LOG_FILE='xxx',MASTER_LOG_POS=xx;
根据实际填写MASTER_HOST、MASTER_LOG_FILE和MASTER_LOG_POS
注：MASTER_LOG_FILE和MASTER_LOG_POS 查看文件xtrabackup_binlog_info
cat /data/backup/2020-12-03_11-22-15/xtrabackup_binlog_info
开启复制
start slave;
show slave status\G;
注意：
报错：Original data directory /server/mysql/var/data is not empty!
datadir目录必须为空
mysql服务一定要停
恢复的文件的属主要记得修改为mysql
chown -R mysql.mysql /server/mysql/var/data
修改mysql-bin.index，删除清空mysql-bin.index的内容
4. 增量备份
innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 --incremental /backup/ --incremental-basedir=/home/zhangshaohua1510/2019-08-19_15-17-35
5. 还原备份
1. 先检查完整备份日志
innobackupex --apply-log --redo-only /home/zhangshaohua1510/2019-08-19_15-17-35
2. 再检查增量备份日志
innobackupex --apply-log /home/zhangshaohua1510/2019-08-19_15-17-35/ --incremental-dir=/backup/2019-08-20_15-41-38/
3. 还原数据
innobackupex --defaults-file=/etc/my.cnf --user=root --password='123456' --copy-back /home/zhangshaohua1510/2019-08-19_15-17-35/
发生错误的话，与上面的还原备份的处理方法一致。
6. 单独备份某个库
备份 innobackupex --defaults-file=/etc/my.cnf --user=root --password='123456' --backup /root --databases=test
还原 innobackupex --defaults-file=/etc/my.cnf --user=root --password='123456' --copy-back /root/2019-08-20_17-31-31/
7. 备份某几个库
备份 innobackupex --defaults-file=/etc/my.cnf --user=root --password='123456' --backup /root --databases="test test2"
还原 innobackupex --defaults-file=/etc/my.cnf --user=root --password='123456' --copy-back /root/2019-08-20_18-10-18/
8. 常用参数
innobackupex Options 这里只对常用参数进行描述 –defaults-file 数据库的配置文件路径，感觉本地备份不写也可以，远程没测试过。 –apply-log 此选项作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。 –copy-back 从备份目录拷贝数据，索引，日志到my.cnf文件里规定的初始位置。 –no-timestamp 创建备份时不自动生成时间目录，可以自定义备份目录名例如： /backups/mysql/base –databases 用于指定要备份的数据库, 多个库文件使用方法: “database1 database2″ –incremental 在全备份的基础上进行增量备份，后跟增量备份存贮目录路径 –incremental-basedir=DIRECTORY 增量备份所需要的全备份路径目录或上次做增量备份的目录路径 –incremental-dir=DIRECTORY 增量备份存贮的目录路径 –redo-only 用于准备增量备份内容把数据合并到全备份目录，配合–incremental-dir 增量备份目录使用。 –force-non-empty-directories 如果是特定库备份还原，不需要删掉整个mysql目录，只是特定库的及相关文件就可以，还原时加上此参数就不会报错。
3. Mysql之lvm快照备份与还原
1.
4. 逻辑备份与还原
1. 使用mysqldump命令备份与还原
1. 使用mysqldump备份单个数据库中的所有表
语法： mysqldump -u root -p123456 数据库名 > /opt/数据库名.sql
例子： mysqldump -uroot -p123456 yangjianbo -S /data/3306/mysql.sock > /opt/yangjianbo.sql
2. 使用mysql命令还原单个数据库中的所有表
例子： mysql -u root -p 123456 yangjianbo < /opt/yangjianbo.sql
3. 使用mysqldump备份库中的某个表
语法： mysqldump -uroot -p123456 -S /data/3306/mysql.sock 库名 表1 表2 > /opt/a.sql
例子： mysqldump -uroot -p123456 -S /data/3306/mysql.sock yangjianbo yang zhang >/opt/bak/yangjianb_yang.sql
4. 使用mysql命令还原数据库中的多个表
例子： mysql -uroot -p123.com yangjianbo < /tmp/1.sql
5. 使用mysqldump备份多个数据库
语法： mysqldump -uroot -p123456 -B yangjianbo wangyanhe -S /data/3306/mysql.sock > /opt/a.sql 多个库用空格隔开
6. 使用mysql命令还原多个数据库
语法： mysql -uroot -p123.com < /opt/a.sql
7. 使用source命令还原数据
登录到mysql服务器中，使用source命令
use yangjianbo;
source /tmp/a.sql
8. mysqldump命令的其它参数
-A 所有数据库的所有表 等同于--all-databases
-B 指定若干数据库，包含create database和use database的命令 等同于--databases
mysqldump -uroot -p123.com -B yangjianbo > /tmp/B.sql
-d 备份表结构
mysqldump -uroot -p123.com -d yangjianbo temp1 > /tmp/1.sql
-t 只备份表数据
mysqldump -uroot -p123.com -t yangjianbo temp1 > /tmp/1.sql
-F 开始备份前刷新mysql服务器日志文件 等同于--flush-logs
-x 对所有数据库中的所有表加锁 等同于--lock-all-tables
--master-data=[0|1|2]
0: 不记录
1: 记录CHANGE MASTER语句
2: 记录注释的CHANGE MASTER语句
9. 将shop_zp的部分表，导入到另外一个数据库中
1. 使用mysqldump命令导出shop_zp的部分表到各自的sql文件中，使用脚本实现。
#!/bin/bash
USER=root
PASSWD="123.com"
BACK_PATH=/server/backup
MYSQL_CMD="mysql -u$USER -p$PASSWD"
MYSQL_DUMP="mysqldump -u$USER -p$PASSWD "
dbname="shop_zp"
[ ! -d $BACK_PATH ] && mkdir -p $BACK_PATH
[ ! -d $BACK_PATH/${dbname} ] && mkdir -p $BACK_PATH/${dbname}
cat /root/backup.txt | while read line
do
`$MYSQL_DUMP ${dbname} ${line} |gzip > $BACK_PATH/${dbname}/${line}_$(date +%F).sql.gz`
done
表的名称来自于/root/backup.txt文件。
2. 使用for循环导入表到zp_product库中
for i in `ls /server/backup/shop_zp/`;do `mysql -uroot -p123.com zp_product < $i`;done
2. mysql5.7之后添加新的备份工具mysqlpump
1. mysqlpump主要特点
并行备份数据库和数据库中的对象，加快备份过程
备份用户账号作为帐户管理语句（CREATE USER，GRANT），而不是直接插入到MySQL的系统数据库
备份出来直接生成压缩后的备份文件
备份进度指示（估计值）
重新加载（还原）备份文件，先建表后插入数据最后建立索引，减少了索引维护开销，加快了还原速度
备份可以排除或则指定数据库
2. mysqlpump缺点
只能并行到表级别,如果表特别大,开多线程和单线程是一样的,并行度不如mydumper；
无法获取当前备份对应的binlog位置；
MySQL5.7.11之前的版本不要使用,并行导出和single-transaction是互斥的；
3. 参数（黄色部分为mysqlpump专有参数）
1) --add-drop-database: 在建立库之前先执行删库操作
|
1
|
DROP DATABASE IF EXISTS `...`; |
2) --add-drop-table：在建表之前先执行删表操作
|
1
|
DROP TABLE IF EXISTS `...`.`...`; |
3) --add-drop-user：在CREATE USER语句之前增加DROP USER。 注意：这个参数需要和--users一起使用，否者不生效。
|
1
|
DROP USER 'backup'@'172.16.60.%'; |
4) --add-locks：备份表时，使用LOCK TABLES和UNLOCK TABLES。注意：这个参数不支持并行备份，需要关闭并行备份功能：--default-parallelism=0
|
1
2
3
|
LOCK TABLES `...`.`...` WRITE;...UNLOCK TABLES; |
5) --all-databases：备份所有库，即 -A。
6) --bind-address：指定通过哪个网络接口来连接Mysql服务器（一台服务器可能有多个IP），防止同一个网卡出去影响业务。
7) --complete-insert：dump出包含所有列的完整insert语句。
8) --compress： 压缩客户端和服务器传输的所有的数据，即 -C。
9) --compress-output：默认不压缩输出，目前可以使用的压缩算法有LZ4和ZLIB
|
1
2
3
4
5
|
[root@localhost ~]# mysqlpump --compress-output=LZ4 > dump.lz4[root@localhost ~]# lz4_decompress dump.lz4 dump.txt[root@localhost ~]# mysqlpump --compress-output=ZLIB > dump.zlib[root@localhost ~]# zlib_decompress dump.zlib dump.txt |
10) --databases：手动指定要备份的库，支持多个数据库，用空格分隔，即-B。
11) --default-character-set：指定备份的字符集。
12) --default-parallelism：指定并行线程数，默认是2，如果设置成0，表示不使用并行备份。注意：每个线程的备份步骤是：先create table但不建立二级索引（主键会在create table时候建立），再写入数据，最后建立二级索引。
13) --defer-table-indexes：延迟创建索引，直到所有数据都加载完之后，再创建索引，默认开启。若关闭则会和mysqldump一样：先创建一个表和所有索引，再导入数据，因为在加载还原数据的时候要维护二级索引的开销，导致效率比较低。关闭使用参数：--skip--defer-table-indexes。
14) --events：备份数据库的事件，默认开启，关闭使用--skip-events参数。
15) --exclude-databases：备份排除该参数指定的数据库，多个用逗号分隔。类似的还有--exclude-events、--exclude-routines、--exclude-tables、--exclude-triggers、--exclude-users
|
1
2
3
4
|
[root@localhost ~]# mysqlpump --exclude-databases=mysql,sys -p123456 --set-gtid-purged=off >/root/db.sql #备份过滤mysql和sys数据库[root@localhost ~]# mysqlpump --exclude-tables=rr,tt -p123456 --set-gtid-purged=off > /root/db.sql #备份过滤所有数据库中rr、tt表[root@localhost ~]# mysqlpump -B test --exclude-tables=tmp_ifulltext,tt -p123456 --set-gtid-purged=off >/root/db.sql #备份过滤test库中的rr、tt表... |
注意：要是只备份数据库的账号，需要添加参数--users，并且需要过滤掉所有的数据库，如
|
1
2
|
#备份除dba和backup的所有账号。[root@localhost ~]# mysqlpump --users --exclude-databases=sys,mysql,db1,db2 --exclude-users=dba,backup -p123456 --set-gtid-purged=off >/root/db.sql |
16) --include-databases：指定备份数据库，多个用逗号分隔，类似的还有--include-events、--include-routines、--include-tables、--include-triggers、--include-users，大致方法使用同15。
17) --insert-ignore：备份用insert ignore语句代替insert语句。
18) --log-error-file：备份出现的warnings和erros信息输出到一个指定的文件。
19) --max-allowed-packet：备份时用于client/server直接通信的最大buffer包的大小。
20) --net-buffer-length：备份时用于client/server通信的初始buffer大小，当创建多行插入语句的时候，mysqlpump 创建行到N个字节长。
21) --no-create-db：备份不写CREATE DATABASE语句。要是备份多个库，需要使用参数-B，而使用-B的时候会出现create database语句，该参数可以屏蔽create database 语句。
22) --no-create-info：备份不写建表语句，即不备份表结构，只备份数据，即 -t。
23) --hex-blob： 备份binary字段的时候使用十六进制计数法，受影响的字段类型有BINARY、VARBINARY、BLOB、BIT。
24) --host ：备份指定的数据库地址，即 -h。
25) --parallel-schemas=[N:]db_list：指定并行备份的库，多个库用逗号分隔，如果指定了N，将使用N个线程的地队列，如果N不指定，将由 --default-parallelism才确认N的值，可以设置多个--parallel-schemas
|
1
2
3
4
5
6
7
|
#4个线程备份vs和aa，3个线程备份pt。通过show processlist 可以看到有7个线程。[root@localhost ~]# mysqlpump --parallel-schemas=4:vs,aa --parallel-schemas=3:pt -p123456 --set-gtid-purged=off > /root/db.sql #默认2个线程，即2个线程备份vs和abc，2个线程备份pt[root@localhost ~]# mysqlpump --parallel-schemas=vs,abc --parallel-schemas=pt -p123456 --set-gtid-purged=off > /root/db.sql #当然要是硬盘IO不允许的话，可以少开几个线程和数据库进行并行备份 |
26) --password：备份需要的密码。
27) --port ：备份数据库的端口。
28) --protocol={TCP|SOCKET|PIPE|MEMORY}：指定连接服务器的协议。
29) --replace：备份出来replace into语句。
30) --routines：备份出来包含存储过程和函数，默认开启，需要对 mysql.proc表有查看权限。生成的文件中会包含CREATE PROCEDURE 和 CREATE FUNCTION语句以用于恢复，关闭则需要用--skip-routines参数。
31) --triggers：备份出来包含触发器，默认开启，使用--skip-triggers来关闭。
31) --set-charset：备份文件里写SET NAMES default_character_set 到输出，此参默认开启。 -- skip-set-charset禁用此参数，不会在备份文件里面写出set names...
32) --single-transaction：该参数在事务隔离级别设置成Repeatable Read，并在dump之前发送start transaction 语句给服务端。这在使用innodb时很有用，因为在发出start transaction时，保证了在不阻塞任何应用下的一致性状态。对myisam和memory等非事务表，还是会改变状态的，当使用此参的时候要确保没有其他连接在使用ALTER TABLE、CREATE TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLE等语句，否则会出现不正确的内容或则失败。--add-locks和此参互斥，在mysql5.7.11之前，--default-parallelism大于1的时候和此参也互斥，必须使用--default-parallelism=0。5.7.11之后解决了--single-transaction和--default-parallelism的互斥问题。
33) --skip-definer：忽略那些创建视图和存储过程用到的 DEFINER 和 SQL SECURITY 语句，恢复的时候，会使用默认值，否则会在还原的时候看到没有DEFINER定义时的账号而报错。
34) --skip-dump-rows：只备份表结构，不备份数据，即-d。注意：mysqldump支持--no-data，mysqlpump不支持--no-data
35) --socket：对于连接到localhost，Unix使用套接字文件，在Windows上是命名管道的名称使用，即 -S。
36) --ssl：--ssl参数将要被去除，用--ssl-mode取代。关于ssl相关的备份。
37) --tz-utc：备份时会在备份文件的最前几行添加SET TIME_ZONE='+00:00'。注意：如果还原的服务器不在同一个时区并且还原表中的列有timestamp字段，会导致还原出来的结果不一致。默认开启该参数，用 --skip-tz-utc来关闭参数。
38) --user：备份时候的用户名，即 -u。
39) --users：备份数据库用户，备份的形式是CREATE USER...，GRANT...，只备份数据库账号可以通过如下命令
|
1
2
|
#过滤掉所有数据库[root@localhost ~]# mysqlpump --exclude-databases=% --users -p123456 --set-gtid-purged=off >/root/db.sql |
40) --watch-progress：定期显示进度的完成，包括总数表、行和其他对象。该参数默认开启，用--skip-watch-progress来关闭。
4. 备份演示
mysqlpump --single-transaction --set-gtid-purged=OFF --parallel-schemas=2:kevin --parallel-schemas=4:dbt3 -B kevin dbt3 -p123456 > /tmp/backup.sql 并行备份两个库，不压缩
mysqlpump --single-transaction --compress-output=lz4 kevin --set-gtid-purged=OFF -p123456 > /tmp/backup_kevin.sql 压缩输出
5. 备份还原
未压缩的备份
mysql < source /tmp/backup.sql;
压缩过的备份
lz4_decompress /tmp/backup_kevin.sql /tmp/kevin.sql 先解压
mysql < source /tmp/kevin.sql;
2. 数据库迁移
1. 相同版本的Mysql数据库之间的迁移
使用mysqldump命令导出数据，使用mysql命令导入数据
2. 不同版本的Mysql数据库之间的迁移
低版本向高版本迁移
3. 不同数据库之间的迁移
不同数据库类型之间的迁移
1. 下载安装一个mysql的管理工具，地址如下：
http://www.xue51.com/soft/2982.html
2. 使用工具连接到mssql，然后把数据导出到mysql中。
https://www.cnblogs.com/xqschool/p/6381825.html
3. 表的导出和导入
1. 使用select .... into OUTFILE 'filename'
默认会有报错 ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
解决方法：
查看一下SHOW VARIABLES LIKE "secure_file_priv";
如果返回的是null,则为禁止，如果有文件夹目录，则只允许导出到该目录下，如果为空，则不限制目录。
通过修改mysql配置文件，可以配置secure_file_priv的值
[mysqld]
secure_file_priv=/home
修改完以后，重启mysql才能生效
select ... into OUTFILE 'filename' OPTIONS选项参数
FIELDS TERMINATED BY 'value' 设置字段之间的分隔字符，默认为'\t'
select * from students into outfile "/var/lib/mysql-files/2.txt" fields terminated by ','; 以逗号作为分隔符
1,yangjianbo,1 2,yichangkun,1 3,luoying,1 4,zhangyan,2 5,wujie,3 6,lexiang,4 7,houzhen,2 8,wangzhiyong,1 9,wangshiqiang,1 10,maojiangzhong,2 11,wangbo,5 12,mahuichuan,\N 13,liuxue,3 14,zhangyuan,3
FIELDS ENCLOSED BY 'value' 设置字段的包围字符
select * from students into outfile "/var/lib/mysql-files/3.txt" fields enclosed by ',';
,1, ,yangjianbo, ,1, ,2, ,yichangkun, ,1, ,3, ,luoying, ,1, ,4, ,zhangyan, ,2, ,5, ,wujie, ,3, ,6, ,lexiang, ,4, ,7, ,houzhen, ,2, ,8, ,wangzhiyong, ,1, ,9, ,wangshiqiang, ,1, ,10, ,maojiangzhong, ,2, ,11, ,wangbo, ,5, ,12, ,mahuichuan, \N ,13, ,liuxue, ,3, ,14, ,zhangyuan, ,3,
2. 使用mysqldump命令导出文本文件
mysqldump -uroot -p123.com -T /var/lib/mysql-files/ yangjianbo students
会在/var/lib/mysql-files目录下创建两个文件students.sql和students.txt
OPTIONS参数
--fields-terminated-by=value 设置字段之间的分隔符
--fields-enclosed-by=value 设置字段的包围字符
--fields-optionally-enclosed-by=value 设置字段的包围字符，只能为单个字符，只能包括CHAR和VARCHAR等字符数据字段
3. 使用mysql命令导出文件
mysql -uroot -p123.com -e "select * from students;" yangjianbo > /tmp/students.txt
mysql -uroot -p123.com --html -e "select * from students;" yangjianbo > /tmp/students.html 导出到html文件
4. 使用LOAD DATA INFILE 'filename.txt' INTO TABLE tablename
load data infile '/var/lib/mysql-files/temp1.txt' into table temp1; 前提是这个表要存在
5. 使用mysqlimport命令导入文本文件
mysqlimport -uroot -p123.com yangjianbo /var/lib/mysql-files/temp1.txt 前提是这个表要存在
浙公网安备 33010602011771号