美文网首页MySQL
使用mysqldump进行逻辑备份

使用mysqldump进行逻辑备份

作者: 一个小运维 | 来源:发表于2021-06-13 09:08 被阅读0次
    备份策略
    • 完全备份：备份所有数据（单一或多个库、单一或多张表、整个数据库）
    • 只备份更新数据：
      • 差异备份：备份自完全备份后产生的数据
      • 增量备份：备份自上次备份之后产生的数据

    完全备份

    命令格式
    • 备份命令
    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

    相关文章

      网友评论

        本文标题：使用mysqldump进行逻辑备份

        本文链接：https://www.haomeiwen.com/subject/shcjeltx.html

        延伸阅读

        深度阅读

        • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

        栏目导航

        热点阅读

        MySQL

        关于我们|服务条款|联系我们|使用mysqldump进行逻辑备份|投稿指南|网站地图|RSS订阅|排版工具|手机版

        提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

        Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

        备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

        本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

        所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！