ITPub博客

首页 > 数据库 > MySQL > 【mysqldump】mysqldump及备份恢复示例

【mysqldump】mysqldump及备份恢复示例

原创 MySQL 作者:恩强Boy 时间:2021-01-11 10:14:48 0 删除 编辑

mysqldump 逻辑备份

一、  mysqldump 介绍

1.  mysqldump 基本介绍

mysqldump 是基于 SQL 的逻辑备份方式,专门用于备份 mysql 数据库。 它的备份原理是,通过协议连接到mysql 数据库,将需要备份的数据查询出来,将查询出的数据转换成对应的 SQL 语句,当我们需要还原这些数据时,只要执行这些 SQL 语句,即可将对应的数据还原。

2.  mysqldump 的优点

可以直接使用文本处理工具处理对应的备份数据,因为备份数据已经被mysqldump 转换为了对应的 SQL 语句,所以,我们可以借助文件系统中的文本处理工具对备份数据进行直接处理。

mysqldump innodb 存储引擎支持热备, innodb 支持事务,我们可以基于事务通过 mysqldump 对数据库进行热备。

二、  mysqldump 语法

# mysqldump -h 主机名 -u 用户名 -p 密码 数据库名 > 备份文件 .sql

也可以使用本地socket 连接

# mysqldump -Ssocketname -u 用户名 -p 密码 数据库名 > 备份文件 .sql

注: 数据库名选项

-A, --all-database # 所有库

db1       # 数据库名

db1 tab1 tab2   #db1 库的表 tab1,tab2

-B --database db1 db2 # 多个数据库

其他常用参数说明:

 

三、  备份及恢复示例

思路清晰

step1: 插入数据“ 1 ”“ 2

step2: 执行 mysqldump 备份

step3: 再次插入数据“ 3 ”“ 4

step4: 通过 dump 备份执行恢复,只能恢复“ 1 ”“ 2

step5: 通过 binlog 继续恢复,可以恢复“ 3 ”“ 4

1.  模拟数据准备

mysql> create database test_db;

mysql> create table test_tab(id int);

mysql> insert into test_tab values(1);

mysql> insert into test_tab values(2);

mysql> select * from test_tab;

+------+

| id   |

+------+

|    1 |

|    2 |

+------+

2 rows in set (0.00 sec)

2.  执行第一次备份

执行以下备份语句

# mysqldump -uroot -p'P@ssw0rd' \

-S/usr/local/mysql/data/mysql.sock \

--all-databases \

--single-transaction \

--master-data=1 \

--flush-logs > /mysqldump/`date +%F-%H`-mysql-all.sql

在备份完成后,在/mysqldump 文件夹中会生成一个 sql 结尾的文件,是 mysqldump 命令产生的逻辑备份文件。

需要注意的是,--flush-logs ”参数会截断日志,并且产生新的 binlog 日志。“ --master-data=1 ”参数,会在生成的文件中,加入一句话“ CHANGE MASTER TO MASTER_LOG_FILE='slave.000008', MASTER_LOG_POS=154; ”。这句话告诉我们这次 dump 备份,截止的日志为“ slave.000007 ”, position 为“ 154 ”。产生了新的 binlog 日志“ slave.000008 ”。在执行恢复的时候,通过产生的 dump 文件恢复至“ slave.000007 ”日志结束, position 为“ 154 ”的位置,剩下的数据需要通过 binlog slave.000008 ”进行恢复。

3.  再次插入数据

这次的插入数据是在mysqldump 备份后进行,意在模拟新的数据通过 binlog 恢复。

mysql> use test_db;

mysql> insert into test_tab values( 3 );

mysql> insert into test_tab values( 4 );

mysql> select * from test_tab;

------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

+------+

4 rows in set (0.00 sec)

4.  执行恢复

在进行恢复的时候,思路一定要清晰。由于我们dump 备份的临界值为“ slave.000007 ”,那我们想要进行完全恢复,就需要保留“ slave.000008 ”。此时,我们通过 dump 文件将数据库恢复至“ slave.000007 ”,剩下的数据需要借助 binlog slave.000008 ”恢复。

1)  保留新的binlog

这里需要将新产生的slave.000008 ”进行保存到其他位置

# cp /usr/local/mysql/mysql-bin/slave.000008 /root/mysql_bak/

2)  停止数据库

# service mysqld stop

3)  清理环境

由于我这里是在本机恢复,需要清理一下环境。mysqldump 的恢复需要数据库在启动时进行,所以这里我清理环境的方法是重新初始化数据库,相当于重建新库。

# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

4)  启动数据库

( 初始密码 /var/log/mysqld.log)

# grep password /var/log/mysqld.log

A temporary password is generated for root@localhost: U/y1skwOfB,(

5)  重置密码

# mysqladmin -u'root' -p' U/y1skwOfB,( ' password "P@sswrd"

6)  mysql 导入数据

执行以下命令

# mysql -uroot -p'P@ssw0rd' -S/usr/local/mysql/data/mysql.sock < /mysqldump/2021-01-09-22-mysql-all.sql

此时数据已经导入成功

7)  验证数据

mysql> select * from test_db.test_tab;

+------+

| id   |

+------+

|    1 |

|    2 |

+------+

2 rows in set (0.00 sec)

注意,此时导入的数据只有1 ”“ 2 ”两个结果,这个是我们执行 mysqldump 时的数据。后面添加的“ 3 ”“ 4 ”并没有在 dump 文件中,这两个数据需要通过保存的 binlog slave.000008 ”恢复。

8)  恢复binlog 数据

# mysqlbinlog slave.000008 | mysql -uroot -p'P@ssw0rd' -S/usr/local/mysql/data/mysql.sock

如果要从slave.000007 ”的 position 为“ 154 ”进行恢复,需要执行以下语句

# mysqlbinlog slave.000007 --start-position=154 | mysql -uroot -p'P@ssw0rd' -S/usr/local/mysql/data/mysql.sock

9)  再次查询数据

mysql> select * from test_db.test_tab;

+------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

|    4 |

+------+

4 rows in set (0.00 sec)

此时,所有数据已经找到。

5.  其他问题

1)  建议在恢复的时候暂停binlog

mysql> set sql_log_bin=0

set sql_log_bin=0 写进 dump 文件中

2)  执行恢复语句有两种方式

方式一:在操作系统层面执行

# mysql -u -p -S < dump 文件

方式二:进入mysql 客户端执行

mysql> source dump 文件

 

 

---- end ----


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31529886/viewspace-2748901/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
勤奋,专注和练习

注册时间:2018-04-03

  • 博文量
    75
  • 访问量
    140880