MySQL 王者晋级之路白银篇

爱学习de小馋猫 2020-02-25

748

第九章备份恢复

9.1 MySQL备份方式

按数据库运行状态分类：

（1）冷备：

（2）热备：分为：1）逻辑备份：mysqldump；select ... into outfile ；mydumper

2）裸文件备份：xtrabackup

按备份后的内容分类：

（1）全量备份

（2）增量备份

9.2 冷备及恢复

过程：

（1）停止MySQL服务

/usr/local/mysql/bin/mysqladmin -uroot -proot shutdown

（2）复制整个数据目录到远程备份机或本地磁盘

scp -r data/mysql/ root@远程备份机IP:/新的目录
copy -r data/mysql/ 本地新目录

（3）恢复

把已备份的数据目录替换原有目录，重启MySQL服务

9.3 热备及恢复

逻辑备份：mysqldump 、select ... into outfile 、mydumper

实质是：备份SQL语句

裸文件备份：是基于底层数据文件的copy datafile

9.3.1 mysqldump 备份恢复

MySQL自带的工具。

实现过程：先从buffer中找到需要备份的数据进行备份，如果buffer中没有，就去磁盘中的数据文件中查找并调回到buffer中再备份，最后形成一个可编辑的备份文件。

常用参数：

--single-transaction

用于保证InnoDB备份数据的一致性，配合RR隔离级别一起使用；当发起事务时，读取一个数据的快照，直到备份结束时，都不会读取到本事务开始之后提交的任何数据

--all-databases(-A)

备份所有数据库

--master-data

该参数有1和2两个值，1：在备份出来的文件中添加一个change master的语句；2注释备份文件中的change master语句

--dump-slave

用于在从库备份数据，在线搭建新从库使用。该参数有1，2两个值，同上

--no-create-info(-t)

备份过程中，只备份表数据，不备份表结构

--no-data(-d)

只备份表结构，不备份表数据

--complete-insert(-c)

使用完整insert语句会包含表中的列信息，提高插入效率

--database(-B)

备份多个数据库。如：mysqldump -uroot proot --database db1 db2

--default-character-set

字符集，MySQL目前默认字符集是UTF8，要与备份出的表的字符集一致

--quick(-q)

相当于sql_noquery，意味着并不会读取缓存中的数据

--where=name(-w)

按条件备份出想要的数据

备份恢复过程：

1.备份全库

/usr/local/mysql/bin/mysqldump --single-transaction -uroot -proot -A>all_20200115.sql

注意：最好给备份文件标注备份时间，如果库里面已经开启gtid，但是备份过程中不想带gtid，可以加上--set-gtid-purged=off参数

2.恢复全库

/usr/local/mysql/bin/mysql -uroot -proot <all_20200115.sql

3.备份单库db1

/usr/local/mysql/bin/mysqldump --single-transaction -uroot -proot db1>db1_20200115.sql

4.恢复单库db1

/usr/local/mysql/bin/mysql -uroot -proot db1<db1_20200115.sql

注意：如果db1库已经存在，可以直接恢复，如果不存在，需要先创建db1

create database db1;

然后再做单库的恢复

5.备份单库db1下的表t

/usr/local/mysql/bin/mysqldump --single-transaction -uroot -proot db1 t>t_db1_20200115.sql

6.恢复单库db1下的表t

/usr/local/mysql/bin/mysql -uroot -proot db1<t_db1_20200115.sql

注意：恢复单表时：导入符号前不需要写表名，写库名即可

7.备份db1库表t表结构信息

/usr/local/mysql/bin/mysqldump --single-transaction -uroot -proot db1 t -d >t.sql

8.备份db1库下表t中的数据信息

/usr/local/mysql/bin/mysqldump --single-transaction -uroot -proot db1 t -t >t.sql

9.备份db1库下表t中id>3的记录

/usr/local/mysql/bin/mysqldump --single-transaction -uroot -proot db1 t --where="id>3">t.sql

注意：where后面的双引号

说明：实际测试中发现，5.7版本备份非全库（--all-database）时必须加上--set-gtid-purged=off 参数，否则会报：

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

并且恢复失败。

或者编辑备份文件注释以下参数

#SET @@GLOBAL.GTID_PURGED='44d27feb-3139-11ea-9e1e-000c29e38e77:1-40';

注意：使用mysqldump备份时可能会出现数据库性能抖动，出现急剧下降现象，原因是：mysqldump先在buffer中查找备份内容，如果buffer没有，需要从磁盘回调数据到buffer，在回调的过程中把内存的热数据冲掉了，以致于影响了我们现有业务的访问。

MySQL5.7版本后新增参数：innodb_buffer_pool_dump_pct，使用他可以控制每一个innodb buffer中转储活跃使用的innodb buffer pages的比例，只有当数据在1s内再次被访问时，才能放在热区域，避免热数据被冲走的现象。默认值：25%。

show variables like '%innodb_buffer_pool_dump_pct%';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| innodb_buffer_pool_dump_pct | 25 |
+-----------------------------+-------+

9.3.2 select ... into outfile

恢复速度快，快于insert速度；但是只能备份表数据，不能备份表结构，如果备份完成后，表被drop了，是无法恢复的。

常用语法：select col1,col2 ... from table_name into outfile '/path/备份文件名'

例：

select * from t into outfile '/tmp/t.sql';
cat tmp/t.sql
1
2
3

删除t表中的数据：

delete from t;

恢复：

load data infile '/tmp/t.sql' into table db_ljing.t;

9.3.3 load data 与 insert 的插入速度对比

使用mysql -e 可以调用数据库命令行命令

如：(恢复393216数据耗时15.8秒)

time mysql -uroot -proot -e "LOAD DATA INFILE '/tmp/t.sql' INTO TABLE DB_LJING.T"
mysql: [Warning] Using a password on the command line interface can be insecure.
real 0m15.812s
user 0m0.023s
sys 0m0.284s

发现一件事：

create table t1 as select * from t;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

使用上面语法创建表时报错gtid，关闭gtid可以解决问题

使用sqlyog工具导出insert语句，开头结尾加上begin;和commit;

关于sqlyog的使用见：

然后重新写入t表

time mysql -uroot -proot db_ljing<t.sql
real 0m40.143s
user 0m2.470s
sys 0m1.546s

耗时40.1s

可见load data的效率优于insert

9.3.4 mydumper

mysqldump：MySQL自带的，只支持单线程工作，只能逐个导出表

mydumper：针对MySQL和Drizzle的高性能多线程备份工具，备份速度优于mysqldump，使用myloader工具还原。

安装

下载地址：https://launchpad.net/mydumper/+download

准备环境：

yum install cmake*
yum install glib2-devel mysql-devel zlib-devel pcre-devel openssl-devel
tar -zxvf mydumper-0.9.1.tar.gz 
mv mydumper-0.9.1 usr/local
ln -s mydumper-0.9.1/ mydumper
cd mydumper
cmake .

注意：cmake后面有空格

报错：

CMake Error: your CXX compiler: "CMAKE_CXX_COMPILER-NOTFOUND" was not found. Please set CMAKE_CXX_COMPILER to a valid compiler path or name.

再次执行cmake .

返回如下内容代表成功：

-- Configuring done

-- Generating done

-- Build files have been written to: usr/local/mydumper

继续执行：

make
make install

完成

mydumper 和 myloader 语法参数详解

mydumper 重点参数说明：

-B，--database ：需要备份的数据库

-T，--table-list ：需要备份的表，多表之间用逗号分隔

-o，--outputdir：输出文件目录

-s，--statement-size：生成的insert语句的字节数，默认100万

-r，--rows：将表按行分块时，指定的块行数，指定这个选项会关闭--chunk-filesize

-F，--chunk-filesize：将表按大小分块时，指定的块大小，单位MB

-c，--compress：压缩输出文件

-e，--build-empty-files：即使表没有数据还是会产生一个空文件

-x，--regex：正则表达式：'db.table'

-i，--ignore-engines：忽略的存储引擎，用逗号分隔

-m，--no-schemas：不到处表结构

-k，--no-locks：不执行共享读锁，警告，会导致不一致的备份

-l，--long-query-guard：设置长查询时间，默认60秒

-K，--kill-long-queries：kill掉长时间执行的查询

-D，--daemon：启用守护进程模式

-I，--snapshot-interval dump：快照时间间隔，默认60秒，需要在daemon模式下

-L，--logfile：日志文件

-h，--host：MySQL服务器IP地址

-u，--user：备份时使用的用户名

-p，--password：用户密码

-P，--port：数据库连接端口

-S，--socket：套接字文件

-t，--threads：使用线程数，默认4个

-C，--compress-protocol：在MySQL连接上使用压缩协议

myloader重点参数说明：

-d，--directory：备份文件目录

-q，--queries-per-transaction：每次事务执行的查询数量，默认1000

-o，--overwrite-tables：如果要恢复表的存在，则先drop该表

-B，--database：需要还原的数据库

-e，--enable-binlog：启用还原数据的二进制日志

-h，--host：MySQL服务器的IP地址

-u，--user：还原时使用的用户

-p，--password：用户密码

-P，--port：连接数据库端口号

-S，--socket：套接字文件

-t，--threads：还原所使用的线程数，默认是4个

-C，--compress-protocol：压缩协议

备份恢复演示：

1.备份全库：（注意空格）

mydumper -u root -p root -o home/mysql/mydumper/all_databases

2.备份/还原单个库db_ljing

mydumper -u root -p root -B db_ljing -o home/mysql/mydumper/db_ljing

注意：备份目录下面会有一个metadata文件，该文件记录着当前的binlog和position号，方便日后搭建slave库

drop databases db_ljing;

还原单个库

myloader -u root -p root -B db_ljing -d home/mysql/mydumper/db_ljing

检查一下db_ljing数据库和数据库里面原来的表都恢复了

3.备份/还原 db_ljing 库里面的单个表t

mydumper -u root -p root -B db_ljing -T t -o home/mysql/mydumper/db_ljing_t

删掉表t，drop table t;

恢复t表：

myloader -u root -p root -B db_ljing -o t -d home/mysql/mydumper/db_ljing_t

备份多表：db_ljing库下的t和t1表

mydumper -u root -p root -B db_ljing -T t,t1 -o home/mysql/mydumper/db_ljing_t_t1

备份db_ljing库下的t表数据，不备份表结构：

mydumper -u root -p root -B db_ljing -T t -m -o home/mysql/mydumper/db_ljing_t_m

备份db_ljing库下的t表并进行压缩

mydumper -u root -p root -B db_ljing -T t -c -o home/mysql/mydumper/db_ljing_t_c

mysqldump 与 mydumper 速度比较

mydumper 备份恢复速度均优于 mysqldump

mydumper优点：

多线程备份工具；

支持文件压缩功能；

支持多线程恢复功能；

保证数据的一致性；

比mysqldump备份恢复速度都快

9.3.5 裸文件备份xtrabackup

目前唯一能对InnoDB和XtraDB数据库进行热备的工具，备份恢复速度快，安全可靠，且备份过程中不会坐标，不影响现有业务。

但是目前不能对表结构文件和其他非事务类型的表进行备份。

包含两个主要工具：xtrabackup 和 innobackupex

最新版本中innobackupex是xtrabackup的软连接，之前版本innobackupex是一个Perl脚本，既可以备份InnoDB表，也可以备份MyISAM这类非事务表的需要，但是会在备份过程中加锁。

详细使用在之前已经介绍过了

详细使用情况参考：MySQL 备份恢复 - xtrabackup

9.4 流式化备份

不用备份到本地磁盘，有两种输出格式：基于tar 和基于xbstream 的，下面主要介绍tar备份

9.4.1 非压缩模式的备份

重要参数：–stream，对于基于tar格式的，stream=tar

例：

innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user zsbak --host 192.168.247.130 --password zsbak --stream=tar tmp>/data/bakup/all.tar

9.4.2 压缩模式的备份

加入打包符号即可，例：

./innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user zsbak --host 192.168.247.130 --password zsbak --stream=tar tmp |gzip ->/data/backup/all-backup.tar.gz

得到的压缩包解压后就是全备份文件了

9.4.3 远程备份

对于备份数据量较大的情况，建议使用流式化备份，把备份文件传到远程备份机上，以免备份到本机上出现磁盘空间不足而影响本机的运行。

基于SSH的远程备份操作：

innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user bkpuser --password bkpuser --stream=tar tmp |gzip |ssh root@192.168.247.129 "cat - > home/mysql/xtrabackup/all-20200119.tar.gz"

报错：

Failed to connect to MySQL server as DBD::mysql module is not installed at - line 1327.

原因：未安装基于 perl 的 mysql 接口工具 perl-DBD-MySQL

[root@localhost local]# yum install perl-DBD-MySQL.x86_64
[root@localhost local]# yum list installed | grep perl-DBD-MySQL
Existing lock /var/run/yum.pid: another copy is running as pid 68961.
Another app is currently holding the yum lock; waiting for it to exit...
The other application is: PackageKit
Memory : 35 M RSS (358 MB VSZ)
Started: Sun Jan 19 10:51:49 2020 - 00:04 ago
State : Sleeping, pid: 68961
Another app is currently holding the yum lock; waiting for it to exit...
The other application is: PackageKit
Memory : 35 M RSS (358 MB VSZ)
Started: Sun Jan 19 10:51:49 2020 - 00:06 ago
State : Sleeping, pid: 68961
perl-DBD-MySQL.x86_64 4.013-3.el6 @local
Failed to connect to MySQL server: Access denied for user 'bkpuser'@'192.168.247.130' (using password: YES).

在192.168.247.129上将root@localhost 改成了 root@%解决

备份过程中一直显示：200119 11:15:37 >> log scanned up to (159650183)，持续很久

原因：未添加免密

给130服务器添加129的免密

[root@localhost ~]# ssh-keygen -t rsa
# 输入三个回车
[root@localhost ~]# cd /root/.ssh
[root@localhost .ssh]# ll
total 16
-rw-------. 1 root root 408 Jan 19 16:59 authorized_keys
-rw-------. 1 root root 1675 Jan 19 17:04 id_rsa
-rw-r--r--. 1 root root 408 Jan 19 17:04 id_rsa.pub
-rw-r--r--. 1 root root 397 Jan 8 11:05 known_hosts
[root@localhost .ssh]# ssh-copy-id root@192.168.247.129
# 输入129密码
[root@localhost .ssh]# ssh root@192.168.247.129
Last login: Sun Jan 19 17:00:18 2020 from 192.168.247.130

完成，再次执行上面的脚本成功

9.5 表空间传输

5.6版本开始引入表空间传输，可以实现把一张表从一个数据库移到另一个数据库或另一台机器上，常用在做数据迁移上。相比mysqldump，更快也更灵活。

使用条件：

（1）MySQL 5.6版本及以上

[root@localhost xtrabackup]# mysql -V
mysql Ver 14.14 Distrib 5.7.24, for linux-glibc2.12 (x86_64) using EditLine wrapper

（2）使用独立表空间方式，现在版本默认开启innodb_file_per_table

(root@localhost) [mysql]> show variables like '%innodb_file_per_table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_file_per_table | ON |
+-----------------------+-------+

（3）源库与目标库page size必须一致

(root@localhost) [mysql]> show variables like '%innodb_page_size%';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+

（4）当表做导出操作时，该表只能进行只读操作

例：

把db_ljing 库下的表t数据传输到db_test库下的t表

首先需要在db_test库下建立与t表一样的表结构

create table t(c1 int(8));

在数据层面查看：（已经有了t表的信息）

[root@localhost db_test]# cd /home/mysql/data/db_test
[root@localhost db_test]# ll
total 228
-rw-r-----. 1 mysql mysql 61 Jan 7 23:03 db.opt
-rw-r-----. 1 mysql mysql 8586 Jan 8 11:27 test.frm
-rw-r-----. 1 mysql mysql 98304 Jan 8 11:28 test.ibd
-rw-r-----. 1 mysql mysql 8556 Jan 19 13:41 t.frm
-rw-r-----. 1 mysql mysql 98304 Jan 19 13:41 t.ibd

在目标库（db_test）执行卸载t表表空间操作

(root@localhost) [db_test]> alter table t discard tablespace;
Query OK, 0 rows affected (0.18 sec)

再在外面查看下当前目录数据：

[root@localhost db_test]# ll
total 128
-rw-r-----. 1 mysql mysql 61 Jan 7 23:03 db.opt
-rw-r-----. 1 mysql mysql 8586 Jan 8 11:27 test.frm
-rw-r-----. 1 mysql mysql 98304 Jan 8 11:28 test.ibd
-rw-r-----. 1 mysql mysql 8556 Jan 19 13:41 t.frm

发现t.ibd文件不见了

注意：卸载操作在目标库的数据库命令下执行，不是在系统层面进行rm。

然后在db_ljing下执行表空间导出操作：

执行前我们先看下db_ljing库在系统层面的文件有哪些

[root@localhost data]# cd /home/mysql/data/db_ljing
[root@localhost db_ljing]# ll
total 40996
-rw-r-----. 1 mysql mysql 61 Jan 17 10:49 db.opt
-rw-r-----. 1 mysql mysql 8556 Jan 17 10:49 t1.frm
-rw-r-----. 1 mysql mysql 20971520 Jan 17 10:49 t1.ibd
-rw-r-----. 1 mysql mysql 8556 Jan 17 13:26 t.frm
-rw-r-----. 1 mysql mysql 20971520 Jan 17 13:26 t.ibd

然后在数据库中执行：

(root@localhost) [db_ljing]> flush table t for export;
Query OK, 0 rows affected (0.11 sec)

再在系统中查下当前文件情况：

[root@localhost db_ljing]# ll
total 41000
-rw-r-----. 1 mysql mysql 61 Jan 17 10:49 db.opt
-rw-r-----. 1 mysql mysql 8556 Jan 17 10:49 t1.frm
-rw-r-----. 1 mysql mysql 20971520 Jan 17 10:49 t1.ibd
-rw-r-----. 1 mysql mysql 375 Jan 19 13:51 t.cfg
-rw-r-----. 1 mysql mysql 8556 Jan 17 13:26 t.frm
-rw-r-----. 1 mysql mysql 20971520 Jan 17 13:26 t.ibd

增加了一个t.cfg文件

切换到mysql用户下，复制db_ljing下的 t.ibd 和 t.cfg 文件到 db_test文件下

[root@localhost db_ljing]# su - mysql
-bash-4.1$ cp /home/mysql/data/db_ljing/t.{ibd,cfg} /home/mysql/data/db_test

此时在db_ljing 库下的t表是locked的

(root@localhost) [db_ljing]> update t set c1=4 where c1 =1;
ERROR 1099 (HY000): Table 't' was locked with a READ lock and can't be updated

db_test库下的t表也是locked的

(root@localhost) [db_test]> insert into t(c1) values (1);
ERROR 1100 (HY000): Table 't' was not locked with LOCK TABLES

接下来解锁db_ljing库下的t表

(root@localhost) [db_ljing]> unlock tables;
Query OK, 0 rows affected (0.00 sec)

然后在目标库db_test下执行导入操作

(root@localhost) [db_test]> alter table t import tablespace;
Query OK, 0 rows affected (0.28 sec)

最后查看下t表的数据，确认已经导入成功。

9.6 利用binlog2sql进行闪回

之前已经介绍过详细参考：使用binlog恢复MySQL误删除数据

9.7 binlog server

自MySQL5.6版本后，可以使用mysqlbinlog命令把远程机器的日志备份到本地目录，这样更加方便快捷的实现了binlog日志的安全备份。

重点参数介绍：

-R --read-from-remote-server：代表从远程MySQL服务器读取binlog

-raw：以binlog格式存储日志，方便日后使用

--stop-never：连接到远程的MySQL服务器上读取日志，直到远程服务关闭才会推出，或是被kill掉

--mysql-bin.***：代表从哪个日志开始备份

--stop-nerver-slave-server-id mysqlbinlog：相当于从库拉取主库日志，所以需要server-id来做唯一标识

例：把192.168.247.130服务器上的binlog备份到192.168.247.129上，需要在129上执行备份操作：

[root@localhost mysql]# mkdir binlog_130_bak
[root@localhost binlog_130_bak]# /usr/local/mysql/bin/mysqlbinlog --raw --read-from-remote-server --stop-never --host=192.168.247.130 --port=3306 --user=root --password=root mysql-bin.000001

报错：

mysqlbinlog: [Warning] Using a password on the command line interface can be insecure.
ERROR: Got error reading packet from server: Could not find first log file name in binary log index file

在130中执行：

(root@localhost) [db_test]> show binary logs;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 697 |
| mysql-bin.000003 | 217 |
| master-bin.000001 | 4811 |
| master-bin.000002 | 40537579 |
+-------------------+-----------+

之前建立主从复制以后就没再更新过mysql-bin.000003，使用的是master-bin.000001，改为执行下面内容：

[root@localhost binlog_130_bak]# /usr/local/mysql/bin/mysqlbinlog --raw --read-from-remote-server --stop-never --host=192.168.247.130 --port=3306 --user=root --password=root master-bin.000001

如果上面加入了--stop-never ，当前窗口会hang住，因为他会一直复制，去掉就好了

mysql

文章转载自爱学习de小馋猫，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL 王者晋级之路 白银篇

第九章 备份恢复