MySQL的备份还原(mysqldump)
时间:2019-05-08 16:25 来源:linux.it.net.cn 作者:IT
MySQL的备份还原(mysqldump)
MySQL的还原前提是要建立在，有完全备份和二进制日志开启的前提下，并且二进制日志文件和完全备份存放在与数据库文件不同的磁盘上，否则当磁盘发生损坏数据将无法进行恢复。
开启二进制日志
开启二进制日志需要将MySQL中的sql_log_bin和log_bin这两个选项
1.开启sql_log_bin
系统中默认开启sql_log_bin选项所以此处无需修改
MariaDB [(none)]> SHOW VARIABLES LIKE 'sql_log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin | ON |
+---------------+-------+
1 row in set (0.01 sec)
2.开启log_bin
此选项需要对MySQL的配置文件进行修改，在修改之前需要先创建一个二进制日志存放的位置。注意：不要和数据库存放在统一磁盘内，不要和数据库存放在统一磁盘内，不要和数据库存放在统一磁盘内重要的事情说三遍。
[root@localhost ~]# mkdir /data/bin
[root@localhost ~]# chown -R mysql.mysql /data/bin #将目录的属主和属组都改为mysql
二进制日志目录创建完毕后，修改配置文件，添加log-bin选项，并指定路径，此处要注意mysql-bin是二进制日志的抬头。
[root@localhost ~]# vim /etc/mysql/my.cnf
log-bin=/data/bin/mysql-bin #mysql-bin为二进制日志的文件名的抬头
以上配置完成后重启mysql服务
[root@localhost ~]# service mysqld restart
Restarting mysqld (via systemctl): [ OK ]
此时二进制日志目录下已经多出了两个二进制日志文件
[root@localhost ~]# ll /data/bin/
total 12
-rw-rw---- 1 mysql mysql 351 May 6 16:51 mysql-bin.000001
-rw-rw---- 1 mysql mysql 54 May 6 16:51 mysql-bin.index
场景一、磁盘损坏恢复
生产中会发生存放数据的磁盘突然发生损坏而造成数据丢失的情况，此时就需要结合之前所作的完全备份以及二进制日志进行将数据完全恢复
场景模拟
一、对数据库进行备份
[root@localhost ~]# mysql -e "SHOW DATABASES;"
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
[root@localhost ~]# mysqldump -A --single-transaction --master-data=2 | xz > /data/all.sql.xz
二、备份完毕后数据库发生小部分变化
MariaDB [(none)]> INSERT hellodb.students(stuid,name,gender,age) VALUE(26,'linchong','M',30) ;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> INSERT hellodb.students(stuid,name,gender,age) VALUE(27,'Lujunyi','M',30);
Query OK, 1 row affected (0.00 sec)
三、数据库发生破坏，数据丢失
[root@localhost ~]# rm -rf /data/mysql/*
数据恢复
一、将mysqld服务停止
[root@localhost ~]# service mysqld stop
Stopping mysqld (via systemctl): [ OK ]
二、查看下二进制日志。
由于每次启动服务都会重新生成一个新的二进制日志，所以先查看下二进制日志的编号以免等下在使用二进制日志还原数据时还原了不必要的数据
[root@localhost ~]# ll /data/bin/
total 504
-rw-rw---- 1 mysql mysql 351 May 6 16:51 mysql-bin.000001
-rw-rw---- 1 mysql mysql 9388 May 6 17:01 mysql-bin.000002
-rw-rw---- 1 mysql mysql 81 May 6 17:03 mysql-bin.index
三、重启MySQL服务，初始化数据库
[root@localhost ~]# service mysqld start
Starting mysqld (via systemctl): [ OK ]
[root@localhost ~]# ll /data/mysql/
total 122924
-rw-rw---- 1 mysql mysql 16384 May 6 17:01 aria_log.00000001
-rw-rw---- 1 mysql mysql 52 May 6 17:01 aria_log_control
drwx------ 2 mysql mysql 272 May 6 16:54 hellodb
-rw-rw---- 1 mysql mysql 1298 May 6 17:01 ib_buffer_pool
-rw-rw---- 1 mysql mysql 12582912 May 6 17:03 ibdata1
-rw-rw---- 1 mysql mysql 50331648 May 6 17:03 ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Apr 29 12:49 ib_logfile1
-rw-rw---- 1 mysql mysql 12582912 May 6 17:03 ibtmp1
-rw-rw---- 1 mysql mysql 6 May 6 17:03 localhost.localdomain.pid
-rw-rw---- 1 mysql mysql 0 Apr 29 12:57 multi-master.info
drwx------ 2 mysql root 4096 Apr 29 12:49 mysql
-rw-rw---- 1 mysql mysql 351 Apr 29 14:06 mysql-bin.000001
-rw-rw---- 1 mysql mysql 351 May 6 16:50 mysql-bin.000002
-rw-rw---- 1 mysql mysql 38 May 6 16:26 mysql-bin.index
-rw-rw---- 1 mysql mysql 0 May 6 16:50 mysql-bin.state
srwxrwxrwx 1 mysql mysql 0 May 6 17:03 mysql.sock
drwx------ 2 mysql mysql 20 Apr 29 12:49 performance_schema
drwx------ 2 mysql root 6 Apr 29 12:49 test
四、将完全备份解压
[root@localhost ~]# unxz /data/all.sql.xz
五、由于完全备份后数据又发生过改变所以需要利用二进制日志进行还原在利用二进制还原前，先查看下完全备份时二进制日志所在的位置。
[root@localhost ~]# vim /data/all.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=8946;
六、将完全备份后产生二进制日志的数据导出
[root@localhost ~]# mysqlbinlog --start-position=8946 /data/bin/mysql-bin.000002 > /data/inc.sql
七、进入MySQL,停止二进制日志记录
由于接下来的操作是恢复数据，所以此处不需要让二进制日志记录数据。
MariaDB [(none)]> SET sql_log_bin=off;
Query OK, 0 rows affected (0.00 sec)
八、导入之前做的完全备份
MariaDB [(none)]> source /data/all.sql
九、导入完全备份后产生的数据
MariaDB [(none)]> source /data/inc.sql
十、验证
查看数据库，以及完全备份后增加的内容是否存在
[root@localhost ~]# mysql -e 'SHOW DATABASES;SELECT * FROM hellodb.students where stuid>25;'
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
+-------+----------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+----------+-----+--------+---------+-----------+
| 26 | linchong | 30 | M | NULL | NULL |
| 27 | Lujunyi | 30 | M | NULL | NULL |
+-------+----------+-----+--------+---------+-----------+
场景二、误删除的恢复
生产环境中通常会出现误删除的可能性，此时就需要用到完全备份，和部分修改后的二进制日志来还原数据
场景模拟
一、对数据库进行完全备份
[root@localhost ~]# mysqldump -A --single-transaction --master-data=2 | xz > /data/all.sql.xz
一段时间后数据库发生了误删除操作,然后又进行了部分其他的曾的操作
MariaDB [(none)]> DROP TABLE hellodb.students;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> INSERT hellodb.teachers VALUE (5,'Jiang Jieshi',50,'M');
Query OK, 1 row affected (0.00 sec)
此时发现了数据库hellodb.students表被删除
恢复数据库
一、停止MySQL服务
[root@localhost ~]# service mysqld stop
Stopping mysqld (via systemctl): [ OK ]
二、将数据库文件内的文件清空
[root@localhost ~]# rm -rf /data/mysql/*
三、将完全备份解压
[root@localhost ~]# unxz /data/all.sql.xz
四、查看完全备份内，二进制日志的记录点
[root@localhost ~]# vim /data/all.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=494073;
五、查看二进制日志，并从二级制日志中导出数据
[root@localhost ~]# ll /data/bin/
total 508
-rw-rw---- 1 mysql mysql 351 May 6 16:51 mysql-bin.000001
-rw-rw---- 1 mysql mysql 9388 May 6 17:01 mysql-bin.000002
-rw-rw---- 1 mysql mysql 494449 May 6 17:52 mysql-bin.000003
-rw-rw---- 1 mysql mysql 81 May 6 17:03 mysql-bin.index
-rw-rw---- 1 mysql mysql 8 May 6 17:52 mysql-bin.state
[root@localhost ~]# mysqlbinlog --start-position=494073 /data/bin/mysql-bin.000003 > /data/inc.sql
六、从导出的数据中找到那条误删除的数据将其删除或注释
[root@localhost ~]# vim /data/inc.sql
#DROP TABLE `hellodb`.`students` /* generated by server */
七、重启MySQL服务
[root@localhost mysql]# service mysqld start
Starting mysqld (via systemctl): [ OK ]
八、将二进制日志关闭
MariaDB [(none)]> SET sql_log_bin=off;
Query OK, 0 rows affected (0.00 sec)
九、导入完全备份
MariaDB [(none)]> SOURCE /data/all.sql
MariaDB [test]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
#此时被删除的students表已经找回
MariaDB [test]> SHOW TABLES FROM hellodb;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
#但是误操作后新加的记录还没有找回
MariaDB [test]> SELECT * FROM hellodb.teachers ;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
十、导入二进制日志所生成的修改后的文件
MariaDB [test]> SOURCE /data/inc.sql
#导入后再次查看students表在
MariaDB [test]> SHOW TABLES FROM hellodb;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
#新添加的记录也有了
MariaDB [test]> SELECT * FROM hellodb.teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | Jiang Jieshi | 50 | M |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)
(责任编辑：IT)
| MySQL的备份还原(mysqldump) MySQL的还原前提是要建立在，有完全备份和二进制日志开启的前提下，并且二进制日志文件和完全备份存放在与数据库文件不同的磁盘上，否则当磁盘发生损坏数据将无法进行恢复。 开启二进制日志 开启二进制日志需要将MySQL中的sql_log_bin和log_bin这两个选项 1.开启sql_log_bin 系统中默认开启sql_log_bin选项所以此处无需修改 MariaDB [(none)]> SHOW VARIABLES LIKE 'sql_log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_log_bin | ON | +---------------+-------+ 1 row in set (0.01 sec) 2.开启log_bin 此选项需要对MySQL的配置文件进行修改，在修改之前需要先创建一个二进制日志存放的位置。注意：不要和数据库存放在统一磁盘内，不要和数据库存放在统一磁盘内，不要和数据库存放在统一磁盘内重要的事情说三遍。 [root@localhost ~]# mkdir /data/bin [root@localhost ~]# chown -R mysql.mysql /data/bin #将目录的属主和属组都改为mysql 二进制日志目录创建完毕后，修改配置文件，添加log-bin选项，并指定路径，此处要注意mysql-bin是二进制日志的抬头。 [root@localhost ~]# vim /etc/mysql/my.cnf log-bin=/data/bin/mysql-bin #mysql-bin为二进制日志的文件名的抬头 以上配置完成后重启mysql服务 [root@localhost ~]# service mysqld restart Restarting mysqld (via systemctl): [ OK ] 此时二进制日志目录下已经多出了两个二进制日志文件 [root@localhost ~]# ll /data/bin/ total 12 -rw-rw---- 1 mysql mysql 351 May 6 16:51 mysql-bin.000001 -rw-rw---- 1 mysql mysql 54 May 6 16:51 mysql-bin.index 场景一、磁盘损坏恢复 生产中会发生存放数据的磁盘突然发生损坏而造成数据丢失的情况，此时就需要结合之前所作的完全备份以及二进制日志进行将数据完全恢复 场景模拟 一、对数据库进行备份 [root@localhost ~]# mysql -e "SHOW DATABASES;" +--------------------+ | Database | +--------------------+ | hellodb | | information_schema | | mysql | | performance_schema | | test | +--------------------+ [root@localhost ~]# mysqldump -A --single-transaction --master-data=2 | xz > /data/all.sql.xz 二、备份完毕后数据库发生小部分变化 MariaDB [(none)]> INSERT hellodb.students(stuid,name,gender,age) VALUE(26,'linchong','M',30) ; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> INSERT hellodb.students(stuid,name,gender,age) VALUE(27,'Lujunyi','M',30); Query OK, 1 row affected (0.00 sec) 三、数据库发生破坏，数据丢失 [root@localhost ~]# rm -rf /data/mysql/* 数据恢复 一、将mysqld服务停止 [root@localhost ~]# service mysqld stop Stopping mysqld (via systemctl): [ OK ] 二、查看下二进制日志。 由于每次启动服务都会重新生成一个新的二进制日志，所以先查看下二进制日志的编号以免等下在使用二进制日志还原数据时还原了不必要的数据 [root@localhost ~]# ll /data/bin/ total 504 -rw-rw---- 1 mysql mysql 351 May 6 16:51 mysql-bin.000001 -rw-rw---- 1 mysql mysql 9388 May 6 17:01 mysql-bin.000002 -rw-rw---- 1 mysql mysql 81 May 6 17:03 mysql-bin.index 三、重启MySQL服务，初始化数据库 [root@localhost ~]# service mysqld start Starting mysqld (via systemctl): [ OK ] [root@localhost ~]# ll /data/mysql/ total 122924 -rw-rw---- 1 mysql mysql 16384 May 6 17:01 aria_log.00000001 -rw-rw---- 1 mysql mysql 52 May 6 17:01 aria_log_control drwx------ 2 mysql mysql 272 May 6 16:54 hellodb -rw-rw---- 1 mysql mysql 1298 May 6 17:01 ib_buffer_pool -rw-rw---- 1 mysql mysql 12582912 May 6 17:03 ibdata1 -rw-rw---- 1 mysql mysql 50331648 May 6 17:03 ib_logfile0 -rw-rw---- 1 mysql mysql 50331648 Apr 29 12:49 ib_logfile1 -rw-rw---- 1 mysql mysql 12582912 May 6 17:03 ibtmp1 -rw-rw---- 1 mysql mysql 6 May 6 17:03 localhost.localdomain.pid -rw-rw---- 1 mysql mysql 0 Apr 29 12:57 multi-master.info drwx------ 2 mysql root 4096 Apr 29 12:49 mysql -rw-rw---- 1 mysql mysql 351 Apr 29 14:06 mysql-bin.000001 -rw-rw---- 1 mysql mysql 351 May 6 16:50 mysql-bin.000002 -rw-rw---- 1 mysql mysql 38 May 6 16:26 mysql-bin.index -rw-rw---- 1 mysql mysql 0 May 6 16:50 mysql-bin.state srwxrwxrwx 1 mysql mysql 0 May 6 17:03 mysql.sock drwx------ 2 mysql mysql 20 Apr 29 12:49 performance_schema drwx------ 2 mysql root 6 Apr 29 12:49 test 四、将完全备份解压 [root@localhost ~]# unxz /data/all.sql.xz 五、由于完全备份后数据又发生过改变所以需要利用二进制日志进行还原在利用二进制还原前，先查看下完全备份时二进制日志所在的位置。 [root@localhost ~]# vim /data/all.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=8946; 六、将完全备份后产生二进制日志的数据导出 [root@localhost ~]# mysqlbinlog --start-position=8946 /data/bin/mysql-bin.000002 > /data/inc.sql 七、进入MySQL,停止二进制日志记录 由于接下来的操作是恢复数据，所以此处不需要让二进制日志记录数据。 MariaDB [(none)]> SET sql_log_bin=off; Query OK, 0 rows affected (0.00 sec) 八、导入之前做的完全备份 MariaDB [(none)]> source /data/all.sql 九、导入完全备份后产生的数据 MariaDB [(none)]> source /data/inc.sql 十、验证 查看数据库，以及完全备份后增加的内容是否存在 [root@localhost ~]# mysql -e 'SHOW DATABASES;SELECT * FROM hellodb.students where stuid>25;' +--------------------+ | Database | +--------------------+ | hellodb | | information_schema | | mysql | | performance_schema | | test | +--------------------+ +-------+----------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+----------+-----+--------+---------+-----------+ | 26 | linchong | 30 | M | NULL | NULL | | 27 | Lujunyi | 30 | M | NULL | NULL | +-------+----------+-----+--------+---------+-----------+ 场景二、误删除的恢复 生产环境中通常会出现误删除的可能性，此时就需要用到完全备份，和部分修改后的二进制日志来还原数据 场景模拟 一、对数据库进行完全备份 [root@localhost ~]# mysqldump -A --single-transaction --master-data=2 | xz > /data/all.sql.xz 一段时间后数据库发生了误删除操作,然后又进行了部分其他的曾的操作 MariaDB [(none)]> DROP TABLE hellodb.students; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> INSERT hellodb.teachers VALUE (5,'Jiang Jieshi',50,'M'); Query OK, 1 row affected (0.00 sec) 此时发现了数据库hellodb.students表被删除 恢复数据库 一、停止MySQL服务 [root@localhost ~]# service mysqld stop Stopping mysqld (via systemctl): [ OK ] 二、将数据库文件内的文件清空 [root@localhost ~]# rm -rf /data/mysql/* 三、将完全备份解压 [root@localhost ~]# unxz /data/all.sql.xz 四、查看完全备份内，二进制日志的记录点 [root@localhost ~]# vim /data/all.sql -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=494073; 五、查看二进制日志，并从二级制日志中导出数据 [root@localhost ~]# ll /data/bin/ total 508 -rw-rw---- 1 mysql mysql 351 May 6 16:51 mysql-bin.000001 -rw-rw---- 1 mysql mysql 9388 May 6 17:01 mysql-bin.000002 -rw-rw---- 1 mysql mysql 494449 May 6 17:52 mysql-bin.000003 -rw-rw---- 1 mysql mysql 81 May 6 17:03 mysql-bin.index -rw-rw---- 1 mysql mysql 8 May 6 17:52 mysql-bin.state [root@localhost ~]# mysqlbinlog --start-position=494073 /data/bin/mysql-bin.000003 > /data/inc.sql 六、从导出的数据中找到那条误删除的数据将其删除或注释 [root@localhost ~]# vim /data/inc.sql #DROP TABLE `hellodb`.`students` /* generated by server */ 七、重启MySQL服务 [root@localhost mysql]# service mysqld start Starting mysqld (via systemctl): [ OK ] 八、将二进制日志关闭 MariaDB [(none)]> SET sql_log_bin=off; Query OK, 0 rows affected (0.00 sec) 九、导入完全备份 MariaDB [(none)]> SOURCE /data/all.sql MariaDB [test]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | hellodb | | information_schema | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.00 sec) #此时被删除的students表已经找回 MariaDB [test]> SHOW TABLES FROM hellodb; +-------------------+ | Tables_in_hellodb | +-------------------+ | classes | | coc | | courses | | scores | | students | | teachers | | toc | +-------------------+ 7 rows in set (0.00 sec) #但是误操作后新加的记录还没有找回 MariaDB [test]> SELECT * FROM hellodb.teachers ; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 93 | F | +-----+---------------+-----+--------+ 4 rows in set (0.00 sec) 十、导入二进制日志所生成的修改后的文件 MariaDB [test]> SOURCE /data/inc.sql #导入后再次查看students表在 MariaDB [test]> SHOW TABLES FROM hellodb; +-------------------+ | Tables_in_hellodb | +-------------------+ | classes | | coc | | courses | | scores | | students | | teachers | | toc | +-------------------+ 7 rows in set (0.00 sec) #新添加的记录也有了 MariaDB [test]> SELECT * FROM hellodb.teachers; +-----+---------------+-----+--------+ | TID | Name | Age | Gender | +-----+---------------+-----+--------+ | 1 | Song Jiang | 45 | M | | 2 | Zhang Sanfeng | 94 | M | | 3 | Miejue Shitai | 77 | F | | 4 | Lin Chaoying | 93 | F | | 5 | Jiang Jieshi | 50 | M | +-----+---------------+-----+--------+ 5 rows in set (0.00 sec) (责任编辑：IT) |