MySQL备份

环境：Redhat7.4，mysql5.7

1.mysql配置文件

mysql的配置文件为/etc/my.cnf
配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效

/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf

mysql常用配置文件参数：

参数	说明
port = 3306	设置监听端口
socket = /tmp/mysql.sock	指定套接字文件位置
basedir = /usr/local/mysql	指定MySQL的安装路径
datadir = /data/mysql	指定MySQL的数据存放路径
pid-file = /data/mysql/mysql.pid	指定进程ID文件存放路径
user = mysql	指定MySQL以什么用户的身份提供服务
skip-name-resolve	禁止MySQL对外部连接进行DNS解析 使用这一选项可以消除MySQL进行DNS解析的时间。 若开启该选项，则所有远程主机连接授权都要使用IP地址方 式否则MySQL将无法正常处理连接请求
log-bin = /var/log/mysql/bin/sql_230_15	binlog日志文件位置（可自行设置）

2. mysql数据库备份

2.1 数据库常用备份方案

数据库备份方案：

- 全量备份
- 增量备份
- 差异备份

备份方案	特点
全量备份	全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。 数据恢复快，但备份时间长
增量备份	增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份 与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象 是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量 备份后所产生的增加和修改的文件，如此类推。 没有重复的备份数据 备份时间短 恢复数据时必须按一定的顺序进行
差异备份	备份上一次的完全备份后发生变化的所有文件。 差异备份是指在一次全备份后到进行差异备份的这段时间内 对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。

2.2 mysql备份工具mysqldump

//语法：
    mysqldump [OPTIONS] database [tables ...]
    mysqldump [OPTIONS] --all-databases [OPTIONS]
    mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
    
//常用的OPTIONS：
    -uUSERNAME      //指定数据库用户名
    -hHOST          //指定服务器主机，请使用ip地址
    -pPASSWORD      //指定数据库用户的密码
    -P#             //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307

全备与恢复

[root@master ~]# mysql -uroot -p
Enter password: 
//查看数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.04 sec)
//创建数据库
mysql> create database chenshuai;
Query OK, 1 row affected (0.01 sec)
//创建表结构
mysql> use chenshuai;
mysql> create table student (id int(10) not null,name varchar(30) not null,age tinyint);
Query OK, 0 rows affected (0.06 sec)
//插入数据
mysql> insert into student values (1,'alice',20),(2,'tom',18),(3,'jack',15),(4,'lili',25);
Query OK, 4 rows affected (0.07 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | alice |   20 |
|  2 | tom   |   18 |
|  3 | jack  |   15 |
|  4 | lili  |   25 |
+----+-------+------+
4 rows in set (0.00 sec)

mysql> create table class (id int(5),name varchar(20));
Query OK, 0 rows affected (0.07 sec)
//再次创建一张表
mysql> show tables;
+---------------------+
| Tables_in_chenshuai |
+---------------------+
| class               |
| student             |
+---------------------+
2 rows in set (0.00 sec)
//备份整个数据库
[root@master ~]# mysqldump -uroot -pCS123.com -h127.0.0.1 --all-databases > all-$(date +%F-%H-%M).sql
[root@master ~]# ls
all-2019-02-21-11-50.sql
//备份chenshuai库下面的表
[root@master ~]# mysqldump -uroot -pCS123.com -h127.0.0.1 chenshuai student class > tables-$(date +%F-%H-%M).sql
[root@master ~]# ls
all-2019-02-21-11-50.sql  tables-2019-02-21-11-54.sql
//删除数据库
mysql> drop database chenshuai;
Query OK, 2 rows affected (0.06 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
//恢复
[root@master ~]# mysql -uroot -pCS123.com -h127.0.0.1 < all-2019-02-21-11-50.sql 
//验证
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| chenshuai          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use chenshuai;
Database changed
mysql> select * from student;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | alice |   20 |
|  2 | tom   |   18 |
|  3 | jack  |   15 |
|  4 | lili  |   25 |
+----+-------+------+
4 rows in set (0.00 sec)

//也可以用另一种方式恢复
mysql> drop database chenshuai;
Query OK, 2 rows affected (0.08 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
mysql> source all-2019-02-21-11-50.sql
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| chenshuai          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

全备+增量备份

//环境准备
mysql> create database chen;
mysql> use chen;
mysql> create table a1 (id int,name varchar(20));
mysql> insert into a1 values (1,'alice'),(2,'bob'),(3,'cidy');
mysql> select * from a1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | alice |
|    2 | bob   |
|    3 | cidy  |
+------+-------+
3 rows in set (0.00 sec)
//进行全备
[root@master ~]# mysqldump -uroot -p'CS123.com' --all-databases --single-transaction --master-data=1 --flush-logs > all-$(date +%F-%H-%M).sql
//再次插入一些数据
mysql> insert into a1 values (4,'dvd'),(5,'eve'),(6,'ff');
mysql> select * from a1;
+------+-------+
| id   | name  |
+------+-------+
|    1 | alice |
|    2 | bob   |
|    3 | cidy  |
|    4 | dvd   |
|    5 | eve   |
|    6 | ff    |
+------+-------+
6 rows in set (0.00 sec)
//模拟故障
mysql> drop database chen;
Query OK, 1 row affected (0.11 sec)
//恢复全量备份的数据
[root@master ~]# mysql -uroot -p'CS123.com' < all-2019-02-21-19-50.sql 
[root@master ~]# mysql -uroot -p'CS123.com' -e "select * from chen.a1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------+
| id   | name  |
+------+-------+
|    1 | alice |
|    2 | bob   |
|    3 | cidy  |
+------+-------+

//通过mysqlbinlog命令导出关于数据库chen的sql语句
[root@master ~]# cd /var/log/mysql/bin/
[root@master bin]# mysqlbinlog --base64-output="decode-rows" -v sql_230_15.000003 > 1.sql
//查看sql文件，找到误操作语句对应的位置,然后记录下来, 随后恢复时跳过即可
[root@master bin]# vim 1.sql 
# at 496
#190221 19:54:27 server id 15  end_log_pos 588 CRC32 0xb1e55379         Query   thread_    id=5     exec_time=0     error_code=0
SET TIMESTAMP=1550750067/*!*/;
drop database chen
//这里的位置点是at 496

//使用mysqlbinlog进行恢复跳过drop语句
[root@master bin]# mysqlbinlog --stop-position=496 sql_230_15.000003 | mysql -uroot -p'CS123.com'
[root@master bin]# mysql -uroot -p'CS123.com' -e "select * from chen.a1"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------+
| id   | name  |
+------+-------+
|    1 | alice |
|    2 | bob   |
|    3 | cidy  |
|    4 | dvd   |
|    5 | eve   |
|    6 | ff    |
+------+-------+

全备+差异备份

[root@master ~]# vim /etc/my.cnf
server-id = 15
log-bin = /var/log/mysql/bin/sql_230_15
[root@master ~]# touch /var/log/mysql/bin/sql_230_15
[root@master ~]# chown -R mysql.mysql /var/log/mysql/

//环境准备
mysql> create database shuai;
mysql> use shuai;
mysql> create table t1 (id int,name varchar(20));
mysql> insert into t1 values (1,'apple'),(2,'banana');
mysql> select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | apple  |
|    2 | banana |
+------+--------+
2 rows in set (0.00 sec)
//全备
[root@master ~]# mysqldump -uroot -p'CS123.com' --single-transaction --flush-logs --master-ddata=2 --all-databases --delete-master-logs > $(date +%F-%H-%M)-all.sql
[root@master ~]# ls
2019-02-22-14-56-all.sql
//插入新的数据
mysql> use shuai;
mysql> insert into t1 values (3,'car'),(4,'dog');
mysql> select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | apple  |
|    2 | banana |
|    3 | car    |
|    4 | dog    |
+------+--------+
4 rows in set (0.00 sec)
//模拟删除数据表
mysql> drop table shuai;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| chen               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)


//先恢复全备的
[root@master ~]# mysql -uroot -p'CS123.com' < 2019-02-22-14-56-all.sql 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| chen               |
| mysql              |
| performance_schema |
| shuai              |
| sys                |
+--------------------+
6 rows in set (0.00 sec)
mysql> use shuai
mysql> show tables;
+-----------------+
| Tables_in_shuai |
+-----------------+
| t1              |
+-----------------+
1 row in set (0.00 sec)
mysql> select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | apple  |
|    2 | banana |
+------+--------+
2 rows in set (0.00 sec)
//现在来恢复后来插入的数据，先查看下全备时候刷新的binlog位置
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| sql_230_15.000006 |   777600 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
这里看到的是sql_230_15.000006，所以删除表时候的binlog文件是sql_230_15.000005
| sql_230_15.000006 | 530 | Query          |        15 |      670 | use `shuai`; DROP TABLE IF EXISTS `t1` /* generated by server */     
[root@master ~]# mysqlbinlog --stop-position=530 /var/log/mysql/bin/sql_230_15.000005 |mysql -uroot -p'CS123.com'
mysql> use shuai;
mysql> select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | apple  |
|    2 | banana |
|    3 | car    |
|    4 | dog    |
+------+--------+