Mysql数据库备份—-mysqldump备份与还原

layout: post
title: Mysql数据库备份—-mysqldump备份与还原
date: 2018-02-23
tags: [“Mariadb”,”软件服务”]

一、实验环境

一台测试机：A（172.18.30.1）

操作系统：Centos7

操作对象数据库版本：mariadb-10.2

二、实现目的

从A机器（172.18.30.1）简单搭建数据库，创建测试数据，使用mysqldump工具实现数据的备份，执行误操作，通过备份实现数据的还原

三、简单实验步骤

创建mysql的rpm源
A、B两台机器yum安装maraidb
A机器创建测试数据
使用mysqldump进行数据的完整备份
再次添加新数据到A机器数据库
执行误操作
通过BINlog删除执行的误操作指令，并还原数据库

四、mysqldump工具命令选项

mysqldump常见选项：

-A， —all-databases 备份所有数据库，含create database
-B , —databases db_name… 指定备份的数据库，包括create database语句
-E, —events：备份相关的所有event scheduler
-R, —routines：备份所有存储过程和存储函数
—triggers：备份表相关的触发器，默认启用,用—skip-triggers，不备份触发器
—master-data[=#]：此选项须启用二进制日志

1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1
2：记录为注释的CHANGE MASTER TO语句此选项会自动关闭—lock-tables功能，自动打开—lock-all-tables功能（除非开启—single-transaction）

-F, —flush-logs ：备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A时，会导致刷新多次数据库，在同一时刻执行转储和日志刷新，则应同时使用—flush-logs和-x，—master-data或-single-transaction,此时只刷新一次，建议：和-x，—master-data或 —single-transaction一起使用
—compact 去掉注释，适合调试，生产不使用
-d, —no-data 只备份表结构
-t, —no-create-info 只备份数据,不备份create table
-n,—no-create-db 不备份create database，可被-A或-B覆盖
—flush-privileges 备份mysql或相关时需要使用
-f, —force 忽略SQL错误，继续执行
—hex-blob 使用十六进制符号转储二进制列（例如，”abc”变为0x616263），受影响的数据类型包括BINARY， VARBINARY，BLOB，BIT
-q, —quick 不缓存查询，直接输出，加快备份速度

五、实验步骤

1、创建mariadb的yum源（在开源 maraidb.org最新版中获得）

vim /etc/yum.repos.d/mariadb.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.2/rhel7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

注意：rpm安装的mariadb需要个epel源端的包，epel源可以在http://mirrors.aliyun.com/repo/下载

2、安装mariadb

yum install -y mariadb-server

3、配置A机器的mariadb-server的配置文件

vim /etc/my.cnf.d/server.cnf
 
在[mysqld]下添加如下项目
 
innodb_file_per_table    #每个标的库数据存放在单独的位置
log_bin                  #保存的二进制文件

注意：不同版本的mariadb其配置文件位置可能不同（老版本的mariadb配置文件是/etc/my.cnf）

4、启动A机器的数据库

systemctl start mariadb

5、登录A机器数据库，并创建测试数据( 我们制作备份实验，有关其他设置就不在配置了）

[root@localhost ~]# mysql
 
MariaDB [(none)]>use test
MariaDB [test]> show tables;
Empty set (0.00 sec)
 
MariaDB [test]> CREATE TABLE IF NOT EXISTS `test`(
    ->    `runoob_id` INT UNSIGNED AUTO_INCREMENT,
    ->    `runoob_title` VARCHAR(100) NOT NULL,
    ->    `runoob_author` VARCHAR(40) NOT NULL,
    ->    `submission_date` DATE,
    ->    PRIMARY KEY ( `runoob_id` )
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
 
MariaDB [test]>

插入3条数据

MariaDB [test]> insert into test (runoob_title,runoob_author,submission_date) values ('test1','stuff',now());
Query OK, 1 row affected, 1 warning (0.01 sec)
 
MariaDB [test]> insert into test (runoob_title,runoob_author,submission_date) values ('test2','stuff',now());
Query OK, 1 row affected, 1 warning (0.01 sec)
 
MariaDB [test]> insert into test (runoob_title,runoob_author,submission_date) values ('test3','Goodluck',now());
Query OK, 1 row affected, 1 warning (0.01 sec)

MariaDB [test]> select * from test
    -> ;
+-----------+--------------+---------------+-----------------+
' runoob_id ' runoob_title ' runoob_author ' submission_date '
+-----------+--------------+---------------+-----------------+
'         1 ' test1        ' stuff         ' 2018-02-23      '
'         2 ' test2        ' stuff         ' 2018-02-23      '
'         3 ' test3        ' Goodluck      ' 2018-02-23      '
+-----------+--------------+---------------+-----------------+
3 rows in set (0.00 sec)

6、在A机器使用mysqldump实现数据库的全库备份

[root@localhost etc]# mysqldump -A -F --master-data=2 --single-transaction > /backup/mysql-`date +%F_%T`.sql
[root@localhost etc]# ls /backup/
mysql-2018-02-23_21:05:49.sql

注意：

innodb引擎需要指定—single-transaction ,如果是是MyISAM则需要使用 -x 选项（加全局读锁）代替该选项

7、在A机器再次插入一条数据

[root@localhost ~]# mysql
 
MariaDB [(none)]> use test;
MariaDB [test]> desc test;
+-----------------+------------------+------+-----+---------+----------------+
' Field           ' Type             ' Null ' Key ' Default ' Extra          '
+-----------------+------------------+------+-----+---------+----------------+
' runoob_id       ' int(10) unsigned ' NO   ' PRI ' NULL    ' auto_increment '
' runoob_title    ' varchar(100)     ' NO   '     ' NULL    '                '
' runoob_author   ' varchar(40)      ' NO   '     ' NULL    '                '
' submission_date ' date             ' YES  '     ' NULL    '                '
+-----------------+------------------+------+-----+---------+----------------+
MariaDB [test]> insert into test (runoob_title,runoob_author,submission_date) values ('Thanks a lot','sun',now());
MariaDB [test]> flush logs;     #用来生成新的二进制日志文件
MariaDB [test]> show binary logs;
+----------------------+-----------+
' Log_name ' File_size '
+----------------------+-----------+
' localhost-bin.000001 ' 1577 '
' localhost-bin.000002 ' 444 '
' localhost-bin.000003 ' 725 '
' localhost-bin.000004 ' 393 '
+----------------------+-----------+
4 rows in set (0.00 sec)
 
MariaDB [test]>

8、执行误操作，删除表

[root@localhost backup]# mysql
 
MariaDB [(none)]> use test;
MariaDB [test]> drop tables test;
Query OK, 0 rows affected (0.01 sec)
 
MariaDB [test]> show tables;
Empty set (0.00 sec)
 
MariaDB [test]>

我已经故意将test这个表删除了，之后在B机器恢复数据库的时候，我会去除这条drop指令

9、将A机器在完整备份之后的binlog文件还原成sql并追加在一起

查看完整备份binlog所备份到的position

[root@localhost mysql]# head -50 /backup/mysql-2018-02-2321\:05\:49.sql ' grep CHANG   
-- CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000002', MASTER_LOG_POS=393;

完整备份备份到localhost-bin.000002这个文件的”393”这个位置，随意后续我们需要将这个文件的从393的位置之后的所有数据还原成sql

10、将binlog还原成sql语句

[root@localhost mysql]# cd /var/lib/mysql
[root@localhost mysql]# ll
总用量 122936
-rw-rw---- 1 mysql mysql    16384 2月  13 20:32 aria_log.00000001
-rw-rw---- 1 mysql mysql       52 2月  13 20:32 aria_log_control
-rw-rw---- 1 mysql mysql 12582912 2月  24 19:10 ibdata1
-rw-rw---- 1 mysql mysql 50331648 2月  24 19:11 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 2月  23 20:42 ib_logfile1
-rw-rw---- 1 mysql mysql 12582912 2月  23 20:42 ibtmp1
-rw-rw---- 1 mysql mysql     1577 2月  23 21:05 localhost-bin.000001
-rw-rw---- 1 mysql mysql      444 2月  23 21:12 localhost-bin.000002
-rw-rw---- 1 mysql mysql      725 2月  23 21:34 localhost-bin.000003
-rw-rw---- 1 mysql mysql      444 2月  24 19:08 localhost-bin.000004
-rw-rw---- 1 mysql mysql      545 2月  24 19:10 localhost-bin.000005
-rw-rw---- 1 mysql mysql      115 2月  24 19:08 localhost-bin.index
-rw-rw---- 1 mysql mysql        5 2月  23 20:42 localhost.pid
-rw-rw---- 1 mysql mysql        0 2月  23 20:42 multi-master.info
drwx------ 2 mysql mysql     4096 2月  13 20:32 mysql
srwxrwxrwx 1 mysql mysql        0 2月  23 20:42 mysql.sock
drwx------ 2 mysql mysql     4096 2月  13 20:32 performance_schema
drwx------ 2 mysql mysql        6 2月  24 19:10 test
[root@localhost mysql]# mysqlbinlog --start-position=393 localhost-bin.000002 >/backup/binlog.`date +%F`  
[root@localhost mysql]# mysqlbinlog localhost-bin.000003 localhost-bin.000004 localhost-bin.000005 >/backup/binlog.`date +%F`

现在我要做的是将之前的那条”drop”指令删除，还原误操作

[root@localhost mysql]# vim /backup/binlog.2018-02-24
 
找到并删除那条"drop"指令,这里不再演示

11、在A机器上通过完整备份和binlog来实现数据的恢复

登录数据库，并锁定全表，禁止写操作，关闭binlog的产生

[root@localhost backup]# mysql
 
MariaDB [(none)]> flush tables with read lock;  #锁定写
Query OK, 0 rows affected (0.01 sec)
 
MariaDB [(none)]> set sql_log_bin=0;    #关闭binlog的记录
Query OK, 0 rows affected (0.00 sec)
 
 MariaDB [(none)]> set global read_only=1;
 
MariaDB [(none)]> unlock tables;       #再次开启表锁
Query OK, 0 rows affected (0.00 sec)

12、隔离mysql环境，通过一定手段禁止其他用户执行写操作

方法：

1）、开启mysql的read_only功能，禁止非管理员用户修改数据(不推荐，有可能会产生问题，因为在启动数据库之后，设置这条属性之前有可能已经插入了数据）

mysql -e 'set global read_only =1'

2）、设置防火墙策略，禁用mysql数据库连接端口，使mysql与外界连接隔离

现在可以恢复数据库了

MariaDB [(none)]> source /backup/mysql-2018-02-2321:05:49.sql  #先使用完整备份还原
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
.
.
.
.
 
MariaDB [test]> source /backup/binlog.2018-02-24   #使用binlog生成的sql文件恢复剩余的数据
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
 
Query OK, 0 rows affected (0.00 sec)
.
.
.
.
.

登录到数据库，查看所恢复的数据

[root@localhost backup]# mysql
MariaDB [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
MariaDB [test]> select * from test;
+-----------+--------------+---------------+-----------------+
' runoob_id ' runoob_title ' runoob_author ' submission_date '
+-----------+--------------+---------------+-----------------+
'         1 ' test1        ' stuff         ' 2018-02-23      '
'         2 ' test2        ' stuff         ' 2018-02-23      '
'         3 ' test3        ' Goodluck      ' 2018-02-23      '
'         4 ' Thanks a lot ' sun           ' 2018-02-23      '
+-----------+--------------+---------------+-----------------+
4 rows in set (0.00 sec)
 
MariaDB [test]>

数据已经完整恢复

13、恢复可写并开启binlog记录

MariaDB [test]> set sql_log_bin=1;
MariaDB [(none)]> set global read_only=0;

五、使用mysqldump实现数据恢复的重要事项

使用mysqldump备份还原，其时间会比较长，在恢复的过程中，用户不可读写数据库！
在恢复的过程中要关闭2禁止日志的记录
恢复数据库需要注意场景，某些场景系统服务的持续运行要比数据更重要。
使用binlog恢复数据要注意其他用户的权限问题，如果在恢复数据的过程中有其他用户插入数据会造成数据不一致
使用binlog恢复数据时，必须关闭binlog的记录，待恢复完成后开启
InnoDB建议备份策略
mysqldump -uroot -A -F -E -R --single-transaction --master-data=1 --flush-privileges --triggers --hex-blob >$BACKUP_DIR/fullbak_$BACKUP_TIME.sql
MyISAM建议备份策略
mysqldump -uroot -A -F -E -R -x --master-data=1 --flush-privileges --triggers --hex-blob >$BACKUP_DIR/fullbak_$BACKUP_TIME.sql

文档更新时间: 2020-05-25 12:16 作者：张尚