Mysql实战之数据备份
author:JevonWei
版权声明:原创作品
数据备份和恢复
mysqldump
冷备份单库(不会创建新库,需要手动创建并指定导入数据的库)
将本机的mydb数据库备份到/app/mydb.backup-$(date +%F-%H-%M-%S)文件
[root@danran ~]# mysqldump mydb > /app/mydb.backup-$(date +%F-%H-%M-%S)
[root@danran ~]# ll /app/mydb.backup-2017-09-12-14-18-44
-rw-r--r--. 1 root root 70488 Sep 12 14:18 /app/mydb.backup-2017-09-12-14-18-44
恢复数据
新建一个数据库做示例
MariaDB [(none)]> CREATE DATABASE testdb;
将备份的数据库文件导入到testdb新建数据库中
[root@danran ~]# mysql testdb < /app/mydb.backup-2017-09-12-14-18-44
进入到testdb测试数据库中,查看数据是否恢复
MariaDB [testdb]> use testdb;
MariaDB [testdb]> SHOW TABLES;
+------------------+
| Tables_in_testdb |
+------------------+
| students |
+------------------+
冷备份多个库文件(可自动创建新库)
mysql节点A备份数据
[root@danran ~]# mysqldump --databases mydb testdb mysql > /app/mydb-testdb-mysql-backup-$(date +F-%H-%M-%S)
mysql及节点B远程恢复数据
创建并授权恢复数据时使用的账号
MariaDB [(none)]> GRANT ALL ON *.* TO 'admin'@'172.16.253.105' IDENTIFIED BY 'admin';
MariaDB [(none)]> GRANT ALL ON *.* TO 'admin'@'%.danran.com' IDENTIFIED BY 'admin';
MariaDB [(none)]> FLUSH PRIVILEGES;
Mysql节点A将备份的多库数据文件导入到远程mysql服务器节点B
[root@danran ~]# mysql -uadmin -h'172.16.253.108' -padmin < /app/mydb-testdb-mysql-backup-F-14-32-28
Mysql节点B查看数据是否恢复成功,即mydb,testdb,mysql文件恢复成功
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| testdb |
+--------------------+
InnoDB引擎热备份数据文件
备份前应该先将所有表锁定
MariaDB [testdb]> FLUSH TABLES WITH READ LOCK;
滚动数据库
MariaDB [testdb]> FLUSH LOGS;
查看二进制日志文件
MariaDB [testdb]> SHOW MASTER LOGS;
+-------------------+-----------+
| Log_name | File_size |
+-------------------+-----------+
| master-log.000001 | 289 |
| master-log.000002 | 654 |
| master-log.000003 | 69738 |
| master-log.000004 | 245 |
+-------------------+-----------+
热备份数据库文件
[root@danran ~]# mysqldump --single-transaction mydb > /app/mydb-backup-$(date +%F-%H-%M-%S);
数据恢复如上
模拟数据温备份与数据恢复
开启二进制日志文件记录
[root@danran ~]# vim /etc/my.cnf.d/server.cnf
[mysqld]
log_bin=master-log
[root@danran ~]# systemctl restart mariadb
有可以远程登录进行数据恢复的用户,进而使用远程恢复数据
MariaDB [(none)]> GRANT ALL ON *.* TO 'admin'@'172.16.253.%' IDENTIFIED BY 'admin';
MariaDB [(none)]> GRANT ALL ON *.* TO 'admin'@'%.danran.com' IDENTIFIED BY 'admin'; \\防止反解主机名
MariaDB [(none)]> FLUSH PRIVILEGES;
滚动数据库
MariaDB [testdb]> FLUSH LOGS;
温备份所有数据库文件到远程服务器
[root@danran ~]# mysqldump -x -R -E --triggers --all-databases --master-data=1 > /tmp/alldatabases-backup-$(date +%F-%H-%M-%S);
[root@danran ~]# mysqldump -x -R -E --triggers --all-databases --master-data=2 --flush-logs > /tmp/alldatabases-backup-$(date +%F-%H-%M-%S);
创建新表,此新表不在备份的文件中,而仅仅存在于二进制日志文件中
MariaDB [mydb]> CREATE TABLE teachers (tid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,name VARCHAR(200));
插入数据
MariaDB [mydb]> INSERT INTO teachers (name) VALUES ('LI'),('WEI');
MariaDB [mydb]> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000006 | 628 | | |
+-------------------+----------+--------------+------------------+
mariadb服务器损坏,所有的数据文件丢失,但备份的二进制日志文件/var/lib/mysql/master-log.000006仍然存在
[root@danran ~]# systemctl stop mariadb
将备份之后的所有二进制日志文件读取出来并保存到文件中
[root@danran ~]# mysqlbinlog /var/lib/mysql/master-log.000006 > /app/alldatabase.binlog
模拟数据文件全部丢失
[root@danran ~]# rm -rf /var/lib/mysql/*
数据库恢复启动之后,所有库文件丢失
[root@danran ~]# systemctl start mariadb
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
先暂时关闭二进制日志文件
[root@danran ~]# vim /etc/my.cnf.d/server.cnf
[mysqld]
#log_bin=master-log
[root@danran ~]# systemctl restart mariadb
将备份文件和二进制日志文件依次恢复数据
[root@danran ~]# mysql -uadmin -h172.16.253.108 -padmin < /tmp/alldatabases-backup-2017-09-12-15-07-37;
[root@danran ~]# mysql -uadmin -h172.16.253.108 -padmin < /app/alldatabase.binlog
登录数据库,数据以完全恢复
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
| testdb |
+--------------------+
MariaDB [(none)]> use mydb;
MariaDB [mydb]> SHOW TABLES; \\teachers表是从二进制日志文件中恢复而来
+----------------+
| Tables_in_mydb |
+----------------+
| students |
| teachers |
+----------------+
开启二进制日志文件记录
[root@danran ~]# vim /etc/my.cnf.d/server.cnf
[mysqld]
log_bin=master-log
[root@danran ~]# systemctl restart mariadb
Xtrabackup
下载Xtrabackup软件包,官网为http://www.percona.com/software/percona-xtrabackup/
安装Xtrabackup软件包
[root@danran ~]# ls percona-xtrabackup-24-2.4.7-2.el7.x86_64.rpm
percona-xtrabackup-24-2.4.7-2.el7.x86_64.rpm
[root@danran ~]# yum -y install ./percona-xtrabackup-24-2.4.7-2.el7.x86_64.rpm
[root@danran ~]# rpm -ql percona-xtrabackup-24
[root@danran ~]# man xtrabackup
创建备份存放目录
[root@danran ~]# mkdir /mydata/xbdata -pv
备份本地数据库文件到mydata/xbdata
[root@danran ~]# innobackupex --user=root --host=localhost [--password] /mydata/xbdata
[root@danran ~]# ls /mydata/xbdata/2017-09-12_16-45-10/
backup-my.cnf mydb performance_schema testdb xtrabackup_info
ibdata1 mysql test xtrabackup_checkpoints xtrabackup_logfile
[root@danran ~]# less /mydata/xbdata/2017-09-12_16-45-10/backup-my.cnf \\ mysql关键配置文件信息
[root@danran ~]# less /mydata/xbdata/2017-09-12_16-45-10/ibdata1 \\表空间文件
[root@danran ~]# less /mydata/xbdata/2017-09-12_16-45-10/xtrabackup_binlog_info \\备份时自动记录处于二进制日志文件的哪个位置
[root@danran ~]# less /mydata/xbdata/2017-09-12_16-45-10/xtrabackup_checkpoints
backup_type = full-backuped 记录了备份类型为 full-backuped
from_lsn = 0 范围内的所有数据
to_lsn = 2085723
last_lsn = 2085723
compact = 0
recover_binlog_info = 0
[root@danran ~]# less /mydata/xbdata/2017-09-12_16-45-10/xtrabackup_info \\记录了备份程序文件的信息
基于全量备份恢复数据
模拟数据库损坏
[root@danran ~]# systemctl stop mariadb
[root@danran mysql]# rm -rf /var/lib/mysql/*
恢复还原数据
[root@danran ~]# cd /mydata/xbdata/2017-09-12_16-45-10/
同步该同步的数据
[root@danran 2017-09-12_16-45-10]# innobackupex --apply-log ./
将当前目录拷贝到/var/lib/mysql
[root@danran 2017-09-12_16-45-10]# innobackupex --copy-back ./
[root@danran 2017-09-12_16-45-10]# cd /var/lib/mysql
修改/var/lib/mysql目录下的所有文件属主、属组为mysql
[root@danran mysql]# chown -R mysql.mysql ./*
启动mariadb服务
[root@danran mysql]# systemctl start mariadb
登录数据库,数据已完全恢复
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
| testdb |
+--------------------+
模拟数据备份与恢复
数据库中插入大量数据,用做测试
MariaDB [mydb]> create table students (stuid INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(200),age TINYINT UNSIGNED,gender ENUM('F','M'),major VARCHAR(200));
[root@danran ~]# for i in {5..1000};do mysql -e "INSERT INTO mydb.students VALUES ('$i','stu$i',$[$RANDOM%100+1],'F','major$i')";done
[root@danran ~]# for i in {1001..2000};do mysql -e "INSERT INTO mydb.students VALUES ('$i','stu$i',$[$RANDOM%100+1],'M','major$i')";done
开启二进制日志文件记录
[root@danran ~]# vim /etc/my.cnf.d/server.cnf
[mysqld]
log_bin=master-log
1. 数据库创建备份用户并授予最小权限
mysql> CREATE USER ’bkpuser’@’localhost’ IDENTIFIED BY ’admin’;
mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM ’bkpuser’;
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ’bkpuser’@’localhost’;
mysql> FLUSH PRIVILEGES;
2. 完全备份所有库,即全量备份
[root@danran ~]# innobackupex /mydata/xbdata/ \\备份所有库文件到/mydata/xbdata/
[root@danran ~]# cd /mydata/xbdata/2017-09-12_17-09-40/
[root@danran 2017-09-12_17-09-40]# cat xtrabackup_checkpoints
backup_type = full-backuped \\完全备份
from_lsn = 0
to_lsn = 2085937 \\结束日志序列号为2085937
last_lsn = 2085937
compact = 0
recover_binlog_info = 0
3. 登录数据库,修改数据
MariaDB [(none)]> use mydb;
MariaDB [mydb]> DELETE FROM students WHERE stuid=1300; \\修改数据文件
MariaDB [mydb]> DELETE FROM students WHERE stuid=1400;
4. 增量备份
[root@danran ~]# innobackupex --incremental /mydata/xbdata/ --incremental-basedir=/mydata/xbdata/2017-09-12_17-09-40 \\--incremental意为当前为增量备份,--incremental-basedir意为以哪个目录为基准做增量备份
[root@danran ~]# cd /mydata/xbdata/2017-09-12_17-18-39/ \\增量备份文件
[root@danran 2017-09-12_17-18-39]# ls
backup-my.cnf mydb test xtrabackup_info
ibdata1.delta mysql testdb xtrabackup_logfile
ibdata1.meta performance_schema xtrabackup_checkpoints
[root@danran 2017-09-12_17-18-39]# less xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2085937 \\增量备份的开始,同时也为完全备份的结束点
to_lsn = 2088070 \\结束日志序列号为2085937
last_lsn = 2088070
compact = 0
recover_binlog_info = 0
4. 数据库中的数据再次发生变化
MariaDB [mydb]> INSERT INTO teachers (name) VALUES ('xin'); \\teschers表中插入数据,从而使数数据发生变化
MariaDB [mydb]> SELECT * FROM teachers;
+-----+------+
| tid | name |
+-----+------+
| 1 | LI |
| 2 | WEI |
| 3 | xin |
+-----+------+
5. 再次增量备份
[root@danran ~]# innobackupex --incremental /mydata/xbdata/ --incremental-basedir=/mydata/xbdata/2017-09-12_17-18-39 \\以上次增量为基准做增量备份
[root@danran ~]# cat /mydata/xbdata/2017-09-12_17-27-51/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2088070 \\日志序列号开始,同时为上一此增量序列号的结束
to_lsn = 2088893 \\当前结束序列号
last_lsn = 2088893
compact = 0
recover_binlog_info = 0
6. 数据库中的数据再次发生变化
MariaDB [mydb]> DELETE FROM teachers WHERE name='LI';
MariaDB [mydb]> SELECT * FROM teachers;
+-----+------+
| tid | name |
+-----+------+
| 2 | WEI |
| 3 | xin |
+-----+------+
7. 查看最后一次备份的xtrabackup_info文件中的binlog_pos位置
[root@danran ~]# cat /mydata/xbdata/2017-09-12_17-38-44/xtrabackup_info
uuid = 2c60babb-979e-11e7-b88f-000c290e4787
name =
tool_name = innobackupex
tool_command = --incremental /mydata/xbdata/ --incremental-basedir=/mydata/xbdata/2017-09-12_17-27-51
tool_version = 2.4.7
ibbackup_version = 2.4.7
server_version = 5.5.52-MariaDB
start_time = 2017-09-12 17:38:44
end_time = 2017-09-12 17:38:46
lock_time = 0
binlog_pos = filename 'master-log.000001', position '709' \\binlog_pos位置为master-log.000001中的709
innodb_from_lsn = 2088893
innodb_to_lsn = 2090173
partial = N
incremental = Y
format = file
compact = N
compressed = N
encrypted = N
8. 读取二进制日志文件
[root@danran ~]# mkdir /mydata/binlogs \\新建存放读取二进制文件后数据的目录
[root@danran ~]# mysqlbinlog -j 709 /var/lib/mysql/master-log.000001 > /mydata/binlogs/2017-09-12_17-38-44-binlog
从master-log.000001二进制文件的709节点开始读取数据并保存在/mydata/binlogs/2017-09-12_17-38-44-binlog文件中
9. 模拟服务器损坏
[root@danran ~]# systemctl stop mariadb
[root@danran ~]# rm -rf /var/lib/mysql/*
10. 恢复数据
[root@danran ~]# cd /mydata/xbdata/2017-09-12_17-09-40/ \\进入完全备份目录
[root@danran 2017-09-12_17-09-40]# innobackupex --apply-log --redo-only ./ \\对当前目录仅做重做,不做撤销回滚
[root@danran 2017-09-12_17-09-40]# innobackupex --apply-log --redo-only ./ --incremental-dir=/mydata/xbdata/2017-09-12_17-18-39
在完全备份目录下,指定第一次增量备份的目录,做重做操作
[root@danran 2017-09-12_17-09-40]# innobackupex --apply-log --redo-only ./ --incremental-dir=/mydata/xbdata/2017-09-12_17-27-51
在完全备份目录下,指定第二次增量备份的目录,做重做操作
[root@danran 2017-09-12_17-09-40]# innobackupex --apply-log --redo-only ./ --incremental-dir=/mydata/xbdata/2017-09-12_17-38-44
在完全备份目录下,指定第三次增量备份的目录,做重做操作
[root@danran 2017-09-12_17-09-40]# cat xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 2090173 日志序列号已重读到最后一次备份的时刻
last_lsn = 2090173
compact = 0
recover_binlog_info = 0
增量命令执行完毕后,在当前完全备份目录下执行回滚操作
[root@danran 2017-09-12_17-09-40]# innobackupex --apply-log ./
将当前目录拷贝到/var/lib/mysql
[root@danran 2017-09-12_17-09-40]# innobackupex --copy-back ./
11. 修改/var/lib/mysql目录下的所有文件属主、属组为mysql
[root@danran 2017-09-12_17-09-40]# cd /var/lib/mysql
[root@danran mysql]# chown -R mysql.mysql ./*
12. 启动mariadb服务
[root@danran mysql]# systemctl start mariadb
13. 登录数据库查看数据,数据恢复到最后一次增量备份前的数据,最后一次增量备份后删除的数据LI没有恢复
MariaDB [mydb]> SELECT * FROM students WHERE stuid=1300; \\数据以删除
MariaDB [mydb]> SELECT * FROM students WHERE stuid=1400; \\数据以删除
MariaDB [mydb]> SELECT * FROM teachers;
+-----+------+
tid | name |
+-----+------+
| 1 | LI |
| 2 | WEI |
| 3 | xin |
+-----+------+
14. 重放二进制日志文件重记录的sql语句
MariaDB [mydb]> SET @@session.sql_log_bin=OFF \\暂时关闭会话中的写操作记录二进制日志
MariaDB [mydb]> \. /mydata/binlogs/2017-09-12_17-38-44-binlog
15. 登录数据库,查看数据恢复情况,数据恢复到mysql服务器崩溃前的状态
MariaDB [mydb]> SELECT * FROM teachers;
+-----+------+
| tid | name |
+-----+------+
| 2 | WEI |
| 3 | xin |
+-----+------+
16. 打开之前关闭的写操作记录日志的功能
MariaDB [mydb]> SET @@session.sql_log_bin=ON;
17. 即刻在做一次全量备份
[root@danran ~]# innobackupex /mydata/xbdata/
18. mysql数据库上线
danran