八、MySQL备份与数据恢复

mysqldump --single-transaction(必加) -uroot -proot -h127.0.0.1 -P3306 test test> test.sql

1、备份 test 数据库的 触发器、存储过程、定时任务，并且压缩
mysqldump -u root -p --set-gtid-purged=OFF --single-transaction  --triggers --master-data -R -E -B test | gzip > test.backup.gz

2、将备份的数据传输到另一台机器上
[root@rabbitmq_1 tmp]# mysqldump --single-transaction --set-gtid-purged=OFF --master-data --triggers -R -E -B test | gzip -c | ssh root@rabbitmq_2 'cat > /tmp/sbtest.sql.gz'

常用参数：
--single-transaction 参数用于确保在备份过程中数据库处于一致状态，这是在执行事务性数据库备份时非常重要的。
-A：备份全部数据库。
--all-databases：备份全部数据库。
-B：备份哪几个数据库
-t：不导出创建表的语句。
-d：不导出数据。
-R，--routines：导出存储过程和函数。
-E：--evens 备份定时任务
-w "a=1"：解释加where条件，值备a=1的数据
--default-character-set：指定编码
--result-file：指定导出文件路径及文件名。
--skip-triggers：不导出触发器。
--master-data=[0][1][2] #在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息，通常情况下=1 就行了
    # 0: 不记录
    # 1：记录CHANGE MASTER语句
    # 2：记录但注释CHANGE MASTER语句

--no-data：不备份数据。
--no-create-db：不备份数据库创建语句。
--skip-comments：跳过备份文件中的注释。
--skip-lock-tables：备份时不锁定表。
--no-create-info：不备份表结构。
--triggers：备份触发器。
--result-file：指定备份结果保存到的文件。

--set-gtid-purged=OFF：mysql导出时加 --set-gtid-purged=OFF，导入新数据库时，会触发记录到新数据库的binlog日志。如果不加，则新数据库不记录binlog日志。
所以做主从时用了gtid，用mysqldump备份时就要加--set-gtid-purged=OFF，否则在主上导入恢复了数据，主没有了binlog日志，同步则不会被同步。

2、数据备份

使用mysqldump是可以进行多种不同方案的备份的。

(1) 全库备份：使用全库备份会将所有数据库中的数据全部备份到同一个sql文件中。
mysqldump -uroot -p -h192.168.134.132 -P3306 --all-databases > /tmp/all.sql

(2) 备份单个数据库：将lxy数据库中的数据备份到sql文件lxy.sql中。
mysqldump -uroot -p -h49.235.28.88 -P3306 lxy > /usr/local/lxy.sql

(3) 备份lxy数据库中的users表到user_test.sql文件。
mysqldump -uroot -p -h49.235.28.88 -P3306 lxy users> /usr/local/user_test.sql

(4) 只备份lxy数据库的表结构，不备份数据到lxy1.sql文件中。
mysqldump -uroot -p -h49.235.28.88 -P3306 lxy -d> /usr/local/lxy1.sql

(5) 备份lxy数据库的表结构和存储过程，不备份数据到lxy2.sql中。
mysqldump -uroot -p -h49.235.28.88 -P3306 lxy -d -R> /usr/local/lxy2.sql

(6) 不备份表结构，只备份数据到lxy3.sql。
mysqldump -uroot -p -h49.235.28.88 -P3306 lxy -t> /usr/local/lxy3.sql

3、数据恢复

表级恢复

(1) 登录mysql，选择对应的数据库，然后设置编码。

(2) 使用source命令导入备份数据。

source /usr/local/all.sql

单库恢复

(1) 登录Mysql，删除要恢复的旧数据库。

(2) 使用source命令导入备份数据。

全库恢复

(1) 退出mysql，使用mysql命令恢复数据。

mysql < /usr/local/all.sql -p

然后在控制台输入密码，密码不会显示别以为没有输入成功。

压缩数据文件恢复

gunzip < test.backup.tgz > a.sql
mysql < a.sql;
h或者
gunzip < test.backup.tgz | mysql -uroot -p12356

三、mysqldump全量备份 + mysqlbinlog增量备份

刚才已经将mysql全量备份及数据恢复的方式讲完了，可以发现mysqldump的使用方式很简单，但是使用mysqldump进行数据备份会存在一个问题：恢复数据时会丢失掉从备份点开始的更新数据，所以我们需要结合mysqlbinlog进行二进制日志增量备份，

首先修改/etc/my.cnf文件，在[mysqld]下启用二进制日志：

log_bin=mysql-bin #开启二进制日志，

数据库的任何更新操作都会记录到二进制日志中。

接下来看下mysqldump全量备份 + mysqlbinlog增量备份如何进行操作：

1、全量备份

mysqldump -uroot -p --flush-logs --single-transaction --master-data=2 databaseName tax-disk > /tmp/tax-disk.sql
# databaseName 备份的数据库
# --flush-logs参数生成新的二进制日志文件
# /home/sql/test.sql 备份的文件路径及文件
# --single-transaction：该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。
# --master-data=[0][1][2] #在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息
# 0: 不记录
# 1：记录CHANGE MASTER语句
# 2：记录但注释CHANGE MASTER语句

2、增量备份

1、开启mysql的binlog日志（增量备份）

1.1查看是否开启,mysql8 默认是开启的，如果没有开启，就编辑 vim /etc/my.cnf文件 添加
        #节点Id,注意集群中不能重复,单节点不配置也可以
        server-id=123
        #开启binlog日志,指定其存放位置
        log-bin=/var/lib/mysql/mysql-bin
        #开启binlog自动过期
        expire_logs_days=3

1.2重启数据库：service mysqld restart

1.3然后在查看是否开启
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin      日志文件 
| log_bin_index                   | /var/lib/mysql/mysql-bin.index  是索引
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
1.2 rows in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000056 |      155 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql-bin是日志文件 、000056是索引、pos=155是事件位置也可以叫偏移值

- - - -做一次 增量备份

1、查看binlog
mysql> show master logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+

| mysql-bin.000072 |    523978 | No        |
| mysql-bin.000073 |       381 | No        |
+------------------+-----------+-----------+

14 rows in set (0.00 sec)

2、全量备份所有库,并刷新binlog
[root@rabbitmq_1 tmp]#  mysqldump -uroot -p123456 -P3306 -F --all-databases > /tmp/all.sql;
[root@rabbitmq_1 tmp]# mysqldump -uroot -p123456 --flush-logs --single-transaction --master-data=2  -B online_invoice > /tmp/online_invoice.sql

3、再次查看binlog，多出了一条mysql-bin.000074
mysql> show master logs;
+------------------+-----------+-----------+
| Log_name         | File_size | Encrypted |
+------------------+-----------+-----------+

| mysql-bin.000072 |    523978 | No        |
| mysql-bin.000073 |       428 | No        |
| mysql-bin.000074 |       155 | No        |
+------------------+-----------+-----------+
15 rows in set (0.00 sec)

4、创建一个表 添加测试数据
CREATE TABLE dept( deptno INT PRIMARY KEY, dname VARCHAR(14), loc VARCHAR(13));
INSERT INTO dept VALUES(10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES(20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES(30,'SALES','CHICAGO');
INSERT INTO dept VALUES(40,'OPERATIONS','BOSTON');

5、删除数据库
mysql> show databases;

+------------------------+
| Database               |
+------------------------+

| newtaxcontrol-server   |
| newtaxcontrol-weixin   |
| online_invoice         |

+------------------------+
12 rows in set (0.00 sec)
12 rows in set (0.00 sec)

mysql> drop database online_invoice;
Query OK, 58 rows affected (0.51 sec)

所以说实际上备份有两个文件：mysqldump全量备份生成的/tmp/online_invoice.sql以及二进制日志文件/var/lib/mysql/mysql-bin.000073文件。所以恢复数据时，我们需要分成两部分去进行恢复数据：

3、数据恢复

1、先刷新下日志，此时会生成一个新的日志文件mysql-bin.000075 ，就是为了方便我们分析上一个文件mysql-bin.000074
mysql> flush logs;

2、查看mysql-bin.000075
方式一：
#-vvv :查看sql,方便判断pos点
#--base64-output=decode-rows :  base64编码的数据转成row
mysqlbinlog -vvv --base64-output=decode-rows   mysql-bin.000074

方式二：
mysql> show binlog events in 'mysql-bin.000074';
+------------------+-----+----------------+-----------+-------------+-------------------------------------------------
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                                                
| mysql-bin.000074 | 1789 | Query          |         1 |        1923 | drop database online_invoice /* xid=15868 */                                                                         |
| mysql-bin.000074 | 1923 | Rotate         |         1 |        1970 | mysql-bin.000075;pos=4 
--------------------------------------------------------------------------------------+
31 rows in set (0.00 sec)

(root@localhost) [test]> show binlog events in 'mysql-bin.000137';
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                          |
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------+

| mysql-bin.000137 | 1444 | Query          |         1 |        1519 | BEGIN                                                                                         |
| mysql-bin.000137 | 1519 | Table_map      |         1 |        1579 | table_id: 96 (test.t_stu)                                                                     |
| mysql-bin.000137 | 1579 | Delete_rows    |         1 |        1646 | table_id: 96 flags: STMT_END_F                                                                |
| mysql-bin.000137 | 1646 | Xid            |         1 |        1677 | COMMIT /* xid=74 */                                                                           |
+------------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------+
17 rows in set (0.01 sec)


恢复时需要在binlog中找到两个位置：
    1、数据恢复的起始位置
    2、数据恢复的结束位置
    如：在数据准备中的drop操作，需要在binlog中找到该位置，并将该位置作为数据恢复的结束位置
    找到问题pos在1789~1923之间，所以将数据恢复到1789之前就可以了。

3、恢复数据

3.1 先恢复全量备份的数据：
[root@rabbitmq_1 tmp]# mysql -uroot -p -v  < online_invoice.sql
然后输入密码确认恢复数据。

或者

source命令：登录数据库，执行下述命令：
mysql> source /tmp/online_invoice.sql
此时 恢复的库里边 是没有刚才新创建的表 和数据的

3.2 然后恢复增量备份：

增量数据恢复就是恢复binlog中记录的数据，

[root@rabbitmq_1 mysql]# mysqlbinlog --stop-position=1789 -d online_invoice  mysql-bin.000074 | mysql -uroot -p123456
mysqlbinlog恢复数据常用参数:
--start-position=875 起始pos点
--stop-position=954 结束pos点
--start-datetime="2016-9-25 22:01:08" 起始时间点
--stop-datetime="2019-9-25 22:09:46" 结束时间点
--skip-gtids 是忽略GTIDs报错
-d 指定数据库

 或者  通过mysqlbinlog 将log文件，转化为sql文件，方便查询

 [root@rabbitmq_1 mysql]# mysqlbinlog --stop-position=1789 -d online_invoice  mysql-bin.000074 > ./test.sql
 [root@rabbitmq_1 mysql]# mysql -uroot -p123456 < ./test.sql
 
  恢复完之后再查询  刚才创建的表和数据已经恢复
  博客有详解：
  https://blog.csdn.net/Jokers_lin/article/details/126449539

4、mysqldump备份存储过程

mysqldump -u <username> -p<password> --routines --no-create-info --no-data --no-create-db <database_name> > <backup_file.sql>
命令中的参数解释如下：
--routines：指定备份存储过程。
--triggers  指定备份触发器
--no-create-info：不包含创建表的信息。
--no-data：不包含表中的数据。
--no-create-db：在导出时不包含创建数据库的语句。
--no-create-db：在导出时不包含创建数据库的语句。
举个例子，假设您的用户名是 root，密码是 password，要备份的数据库名是 mydatabase，您可以执行以下命令来备份存储过程：
mysqldump -u root -p password --routines --no-data --no-create-info --no-create-db mydatabase >back.sql

5、mysql备份用户数据

mysqldump -u <username> -p<password> --no-create-info --no-create-db --where="user='<user_name>'" <database_name> > <backup_file.sql>
将 <username> 替换为您的 MySQL 用户名，<password> 替换为您的密码，<user_name> 替换为要备份数据的用户名称，<database_name> 替换为要备份数据的数据库名称，<backup_file.sql> 替换为您要保存备份的文件路径和文件名。
例如，假设您的用户名是 root，密码是 password，要备份的数据库名是 mydatabase，要备份的用户是 myuser，您可以执行以下命令来备份用户数据：
mysqldump -u root -ppassword --no-create-info --no-create-db --where="user='myuser'" mydatabase > backup.sql
执行该命令后，特定用户的数据将被导出到 backup.sql 文件中。

原创不易，如文章存在问题，请及时通知我改正，大家互相进步。

繁华依在

关注

38
点赞
踩
18

收藏

觉得还不错? 一键收藏
打赏
0
评论
八、MySQL备份与数据恢复

刚才已经将mysql全量备份及数据恢复的方式讲完了，可以发现mysqldump的使用方式很简单，但是使用mysqldump进行数据备份会存在一个问题：恢复数据时会丢失掉从备份点开始的更新数据，所以我们需要结合mysqlbinlog进行二进制日志增量备份，所以说实际上备份有两个文件：mysqldump全量备份生成的/tmp/online_invoice.sql以及二进制日志文件/var/lib/mysql/mysql-bin.000073文件。(1) 登录mysql，选择对应的数据库，然后设置编码。
复制链接

扫一扫