注册    登录
创作新主题
社区所有版块导航
Python
python开源   Django   Python   DjangoApp   pycharm  
DATA
docker   Elasticsearch  
分享
问与答   闲聊   招聘   翻译   创业   分享发现   分享创造   求职   区块链   支付之战  
aigc
aigc   chatgpt  
WEB开发
linux   MongoDB   Redis   DATABASE   NGINX   其他Web框架   web工具   zookeeper   tornado   NoSql   Bootstrap   js   peewee   Git   bottle   IE   MQ   Jquery  
机器学习
机器学习算法  
Python88.com
反馈   公告   社区推广  
产品
短视频  
印度
印度  
一周十大热门主题
从入门到入魔，100个Python实战项目练习(附答案)！
Github 今日推荐 WebGL2神技！deck.gl：秒渲海量数据的可视化神器"
顶刊《Materials Today》最新成果：机器学习+增材制造
航空发动机用上大模型：解决复杂时序问题，性能超越ChatGPT-4o实现SOTA｜上交创智复旦
31 岁程序员，6 个月赚了 5.8 亿。 看到一个案例，在外网我搜了搜相关新闻，竟然是真...
Nginx和Apache要成旧爱了？PHP有了新搭档：缝合怪FrankenPHP！
郑州大学田芸/周震 | 下一代电池安全管理：机器学习辅助寿命预测与性能提升
【2025版附安装包】超详细Python+Pycharm安装保姆级教程，永久免费使用，Python环...
ChatGPT化身生活操作系统：奥特曼预告下一代顶级AI
西南交通大学张云辉团队JH｜利用无监督机器学习和正定矩阵因子分解模型驱动煤矿农业区域的地下水化学成因...
关注
Py学习  »  DATABASE

使用mysqldump进行逻辑备份

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

完全备份

命令格式
  • 备份命令 
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
 
264 次点击  
登录后回复
关于   移动版
Py学习 - 专注于Python技术发展的社区(原Django社区)
沪ICP备11025650号