备份策略

  • 完全备份:备份所有数据(单一或多个库、单一或多张表、整个数据库)
  • 只备份更新数据:
  • 差异备份:备份自完全备份后产生的数据
  • 增量备份:备份自上次备份之后产生的数据

完全备份

命令格式

  • 备份命令
mysqldump -u用户名 -p密码 > 路径/文件名.sql
  • 恢复命令
mysql -u用户名 -p密码 < 路径/备份文件名.sql

备份示例

备份所有库

  • 源数据库
[root@node10 ~]# mysqldump -uroot -pTEST2021@guodong.com --all-databases > dbbackup/alldb.sql
或
[root@node10 ~]# mysqldump -uroot -pTEST2021@guodong.com -A > dbbackup/alldb.sql

# 拷贝文件到目标服务器
[root@node10 ~]# rsync -r dbbackup root@192.168.4.20:/root/
  • 目标数据库
[root@node20 ~]# mysql -uroot -pTEST2021@guodong.com < dbbackup/alldb.sql

备份某一个库

  • 源数据库
[root@node10 ~]# mysqldump -uroot -pTEST2021@guodong.com test2021 > dbbackup/tedu_db.sql

# 拷贝文件到目标服务器
[root@node10 ~]# rsync -r dbbackup root@192.168.4.20:/root/
  • 目标数据库
# 删除库,以便测试结果
[root@node20 ~]# mysql -uroot -pTEST2021@guodong.com
mysql> drop database test2021;

mysql> CREATE DATABASE my_db DEFAULT CHARSET utf8mb4;
[root@node20 ~]# mysql -uroot -pTEST2021@guodong.com test2021 < dbbackup/tedu_db.sql

备份某一张表

  • 源数据库
[root@node10 ~]# mysqldump -uroot -pTEST2021@guodong.com test2021 salary > dbbackup/test2021_salary.sql

# 拷贝文件到目标服务器
[root@node10 ~]# rsync -r dbbackup root@192.168.4.20:/root/
  • 目标数据库
# 清空表,以便测试结果
mysql> use test2021;
mysql> truncate salary;

[root@node20 ~]# mysql -uroot -pTEST2021@guodong.com tedu_db < dbbackup/test2021_salary.sql

备份某多个库

  • 源数据库
[root@node10 ~]# mysqldump -uroot -pTEST2021@guodong.com -B test2021 mysql > dbbackup/twodb.sql

# 拷贝文件到目标服务器
[root@node10 ~]# rsync -r dbbackup root@192.168.4.20:/root/
  • 目标数据库
[root@node20 ~]# mysql -uroot -pTEST2021@guodong.com my_db < dbbackup/twodb.sql

备份多张表

  • 源数据库
[root@node10 ~]# mysqldump -uroot -pTEST2021@guodong.com test2021 employees salary > dbbackup/test2021_employees_salary.sql

# 拷贝文件到目标服务器
[root@node10 ~]# rsync -r dbbackup root@192.168.4.20:/root/
  • 目标数据库
# 清空表,以便测试结果
mysql> truncate salary;
mysql> delete from employees;

[root@node20 ~]# mysql -uroot -pTEST2021@guodong.com test2021 < dbbackup/test2021_employees_salary.sql

增量备份

binlog日志

binlog日志概述

  • 也叫做二进制日志
  • 它是MySQL服务日志文件的一种
  • 默认没有启用
  • 记录除查询之外的所有SQL命令
  • 可用于数据的备份和恢复
  • 它是MySQL主从同步的必要条件

启用binlog日志

  • 修改/etc/my.cnf启用日志

配置项

用途

server_id=数字

指定服务器id值(1-255)

log-bin/log_bin=目录/文件名

启用binlog日志

max_binlog_size=数值m

日志文件容量,默认1GB

  • 启用 binlog
[root@node10 ~]# vim /etc/my.cnf
[mysqld]
server_id = 10
log-bin
... ...

[root@node10 ~]# systemctl restart mysqld

# 查看结果
[root@node10 ~]# mysql -uroot -pTEST2021@guodong.com
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| node10-bin.000001 |      154 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[root@node10 ~]# ls /var/lib/mysql/node10-bin.*
/var/lib/mysql/node10-bin.000001  /var/lib/mysql/node10-bin.index

手动创建binlog日志文件

  • binlog文件默认存在/var/lib/mysql目录下
  • 也可以手工进行修改
# 创建用于保存日志文件的目录
[root@node10 ~]# mkdir /mybinlog
[root@node10 ~]# chown mysql:mysql /mybinlog/

# 修改配置文件
[root@node10 ~]# vim /etc/my.cnf
[mysqld]
server_id = 10
log-bin = /mybinlog/mylog
... ...

# 验证
[root@node10 ~]# systemctl restart mysqld
[root@node10 ~]# ls /mybinlog/
mylog.000001  mylog.index
mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mylog.000001 |      154 |              |                  |                   |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
  • 新建binlog日志文件
# 方法一:重启mysqld服务
[root@node10 ~]# systemctl restart mysqld
[root@node10 ~]# ls /mybinlog/
mylog.000001  mylog.000002  mylog.index
mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mylog.000002 |      154 |              |                  |                   |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# 方法二:flush指令
mysql> flush logs;
Query OK, 0 rows affected (0.10 sec)

mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mylog.000003 |      154 |              |                  |                   |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[root@node10 ~]# ls /mybinlog/
mylog.000001  mylog.000002  mylog.000003  mylog.index

# 方法三:备份时刷新日志
[root@node10 ~]# mysqldump -uroot -pNSD2021@tedu.cn --flush-logs nsd2021 > dbbackup/nsd2021_full.sql
[root@node10 ~]# ls /mybinlog/
mylog.000001  mylog.000002  mylog.000003  mylog.000004  mylog.index
mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mylog.000004 |      154 |              |                  |                   |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

删除已有binlog日志

  • 删除指定编号之前的日志文件
# 删除mylog.000003(不包含)之前的日志
mysql> purge master logs to "mylog.000002";
Query OK, 0 rows affected (0.05 sec)
[root@node10 ~]# ls /mybinlog/
mylog.000003  mylog.000004  mylog.index

# 删除所有日志,重新新日志
mysql> reset master;
Query OK, 0 rows affected (0.13 sec)

mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mylog.000001 |      154 |              |                  |                   |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[root@node10 ~]# ls /mybinlog/
mylog.000001  mylog.index

binlog日志内容

通过binlog日志修改数据库

  • 向departments表中插入数据
mysql> use test2021;
mysql> INSERT INTO departments(dept_name) VALUES ('sales1');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO departments(dept_name) VALUES ('sales2');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO departments(dept_name) VALUES ('sales3');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO departments(dept_name) VALUES ('sales4');
Query OK, 1 row affected (0.05 sec)

mysql> show master status;
+--------------+----------+--------------+------------------+-------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+--------------+----------+--------------+------------------+-------------------+
| mylog.000001 |     1274 |              |                  |                   |
+--------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

[root@node10 ~]# mysqlbinlog /mybinlog/mylog.000001
  • 在目标主机上通过binlog同步源主机上的数据
# 将binlog日志拷贝到目标主机
[root@node10 ~]# rsync -r /mybinlog 192.168.4.20:/root

# 在目标主机上执行一遍binlog日志
[root@node20 ~]# mysqlbinlog mybinlog/mylog.000001 | mysql -uroot -pTEST2021@guodong.com

修改binlog记录格式

  • binlog日记记录方式
  • row:行模式
  • statement:行模式
  • mixed:混合模式
mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)
  • 修改日志记录格式
[root@node10 ~]# vim /etc/my.cnf
[mysqld]
server_id = 10
log-bin = /mybinlog/mylog
binlog_format = "mixed"
... ...

[root@node10 ~]# systemctl restart mysqld

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.01 sec)
  • 检查日志
# 修改数据
mysql> use test2021;
mysql> INSERT INTO departments(dept_name) VALUES('sales5');
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO departments(dept_name) VALUES('sales6');
Query OK, 1 row affected (0.03 sec)

# 查看日志
[root@node10 ~]# mysqlbinlog /mybinlog/mylog.000002

通过binlog日志修改指定范围内的数据

  • 执行删除操作
mysql> delete from departments where dept_name like 'sales_';
  • 拷贝日志文件到目标服务器
[root@node10 ~]# rsync -r /mybinlog 192.168.4.20:/root
  • 在目标主机上查看日志,找到要恢复数据的起始和结束偏移量
# 读取日志,找到插入sales5日志上面的at偏移量作为起始值,找到插入sales6日志下面的COMMIT之后的at偏移量作为结束值
[root@node20 ~]# mysqlbinlog mybinlog/mylog.000002 

[root@node20 ~]# mysqlbinlog --start-position=328 --stop-position=810 mybinlog/mylog.000002 | mysql -uroot -pTEST2021@guodong.com

作者:一个小运维