注册    登录
关注
Py学习  »  DATABASE

使用mysqldump进行逻辑备份

一个小运维 • 2 年前 • 237 次点击  
备份策略
  • 完全备份：备份所有数据（单一或多个库、单一或多张表、整个数据库）
  • 只备份更新数据：
    • 差异备份：备份自完全备份后产生的数据
    • 增量备份：备份自上次备份之后产生的数据

完全备份

命令格式
  • 备份命令 
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
Python社区是高质量的Python/Django开发社区
本文地址：http://www.python88.com/topic/115566
Reply
 
237 次点击  
登录后回复
关于   移动版   ·   三行代码   ·   今天看啥   ·   Code   ·   link之家   ·   卧龙搜索   ·   藏经阁   ·   小百科
Py学习 - 专注于Python技术发展的社区(原Django社区)
沪ICP备11025650号