mysql 备份逻辑 mysql逻辑备份采用什么命令

备份策略

• 完全备份：备份所有数据（单一或多个库、单一或多张表、整个数据库）
• 只备份更新数据：

• 差异备份：备份自完全备份后产生的数据
• 增量备份：备份自上次备份之后产生的数据

完全备份

命令格式

• 备份命令

mysqldump -u用户名 -p密码 > 路径/文件名.sql

• 恢复命令

mysql -u用户名 -p密码 < 路径/备份文件名.sql

备份示例

备份所有库

• 源数据库

[root@node10 ~]# mysqldump -uroot [e-mail] --all-databases > dbbackup/alldb.sql
或
[root@node10 ~]# mysqldump -uroot [e-mail] -A > dbbackup/alldb.sql

# 拷贝文件到目标服务器
[root@node10 ~]# rsync -r dbbackup root@192.168.4.20:/root/

• 目标数据库

[root@node20 ~]# mysql -uroot [e-mail] < dbbackup/alldb.sql

备份某一个库

• 源数据库

[root@node10 ~]# mysqldump -uroot [e-mail] test2021 > dbbackup/tedu_db.sql

# 拷贝文件到目标服务器
[root@node10 ~]# rsync -r dbbackup root@192.168.4.20:/root/

• 目标数据库

# 删除库，以便测试结果
[root@node20 ~]# mysql -uroot [e-mail]
mysql> drop database test2021;

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

备份某一张表

• 源数据库

[root@node10 ~]# mysqldump -uroot [e-mail] 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 [e-mail] tedu_db < dbbackup/test2021_salary.sql

备份某多个库

• 源数据库

[root@node10 ~]# mysqldump -uroot [e-mail] -B test2021 mysql > dbbackup/twodb.sql

# 拷贝文件到目标服务器
[root@node10 ~]# rsync -r dbbackup root@192.168.4.20:/root/

• 目标数据库

[root@node20 ~]# mysql -uroot [e-mail] my_db < dbbackup/twodb.sql

备份多张表

• 源数据库

[root@node10 ~]# mysqldump -uroot [e-mail] 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 [e-mail] 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 [e-mail]
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 [e-mail] --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 [e-mail]

修改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 [e-mail]

作者：一个小运维