author：JevonWei

版权声明：原创作品

blog:http://119.23.52.191/

数据备份和恢复

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数据库上线

最新文章

1. TCP连接建立和终止小结
2. Winform开发框架之单据窗体生成(主从表，流水单号)
3. 51nod1476 括号序列的最小代价
4. window下编译ffmpeg 比较简单
5. Install Cocos2d-x v3.3 on Ubuntu 14.04 & Ubuntu 14.10(转)
6. c保存lua函数
7. PyCrpyto windows安装使用方法
8. java设计模式之——策略模式
9. 【原版的】PHP技术成长规划过程中猿人
10. 数位dp初步——数位dp的两种方式
11. Servlet - Upload、Download、Async、动态注册
12. MySQL读取Binlog日志常见的3种错误
13. 题解-Codeforces917D Stranger Trees
14. win10怎么录制电脑屏幕 电脑播放视频录制
15. Python中的函数介绍
16. IDEA操作技巧与Tomcat集成
17. 安装oracle经验
18. noip第33课作业
19. 关于try catch
20. 【转】【MySQL报错】ERROR 1558 (HY000): Column count of mysql.user is wrong. Expected 43, found 39.

热门文章

1. Android（java）学习笔记93：为什么局部内部类只能访问外部类中的 final型的常量
2. IIS6.0开启gzip压缩
3. 题解 CF734A 【Anton and Danik】
4. 前端面试题1：Object.prototype.toString.call() 、instanceof 以及 Array.isArray()三种方法判别数组的优劣和区别
5. javaweb基础(26)_jsp标签库开发二
6. 闭包 -------JavaScript
7. 一次线上CPU高的问题排查实践
8. oracle 多行数据合并一行数据
9. git与github账号建立SSH连接
10. day23-python之日志 re模块