mysql dump备份_mysqldump备份和恢复

r囧r小猫 2022-10-25 00:59 1阅读 0赞

mysqldump是mysql用于转存储数据库的实用程序。它主要产生一个SQL脚本，其中包含从头重新创建数据库所必需的命令CREATE TABLE INSERT等。

命令用法：

mysqldump \[OPTIONS\] database \[tables\] 备份单库，可以只备份部分表

mysqldump \[OPTIONS\] --databases \[OPTIONS\] DB1 \[DB2 DB3...\] 备份多库

mysqldump \[OPTIONS\] --all-databases \[OPTIONS\] 备份所有库

\-A，--all-databases 备份所有数据库

\-B db\_name

\--databases db\_name 备份指定数据库

执行mysqldump命令时，屏幕上会用文本格式会打印出一系列的mysql语句

mysqldump实质就是通过数据库的数据，生成一系列create、insert语句，当我们恢复数据时，就可以用这些语句，重新生成数据。

操作环境：CenOS7

\#数据库环境如下：

MariaDB \[(none)\]> show databases;

\+--------------------+

| Database |

\+--------------------+

| information\_schema |

| mydb |

| mysql |

| performance\_schema |

| test |

\+--------------------+

5 rows in set (0.00 sec)

MariaDB \[(none)\]> show tables from mydb;

\+----------------+

| Tables\_in\_mydb |

\+----------------+

| stu |

| students |

| tb2 |

| tb3 |

| tbl1 |

\+----------------+

5 rows in set (0.00 sec)

一、备份单库

1、备份

\[root@centos7b ~\]\#mysqldump -p mydb;

\#屏幕上会输出出关于mydb这个库的备份语句

\#可以利用这个命令，把输出的语句保存到一个文件中

\[root@centos7b ~\]\#mysqldump -p mydb > /app/mydb-fullbackup-\`date +%F-%H-%M-%S \`

Enter password:

\#注意：这里mysqldump -p mydb命令只是把mydb里面的所有表备份，当我们用生成的文件恢复数据时，需要指定实现创建好的数据库。

\[root@centos7b ~\]\#ll /app/

\-rw-r--r--. 1 root root 72718 Sep 17 00:00 mydb-fullbackup-2017-09-17-00-00-52

\#mydb-fullbackup-2017-09-17-00-00-52就是mydb这个库的备份文件

2、恢复

MariaDB \[(none)\]> create database backdb;

\[root@centos7b ~\]\#mysql -p backdb < /app/mydb-fullbackup-2017-09-17-00-00-52

Enter password:

MariaDB \[(none)\]> use backdb ;

MariaDB \[backdb\]> show tables;

\+------------------+

| Tables\_in\_backdb |

\+------------------+

| stu |

| students |

| tb2 |

| tb3 |

| tbl1 |

\+------------------+

5 rows in set (0.00 sec)

二、备份多个库

\[root@centos7b ~\]\#mysqldump --databases mydb testdb > /app/mydb-testdb-backup

\#注意，mysqldump --databases 语句会自动创建数据库，因此恢复时不需要手动创建数据库。

\[root@centos7b ~\]\#mysql -uadmin -padmin -h 172.16.80.100

\#把数据恢复到远程主机172.16.80.100

三、在线业务备份

在备份时，对于在线业务，用户的写操作会影响到备份的数据，那么此时我们需要锁定表。

MyISAM存储引擎：支持温备，备份时要锁定表；

\-x, --lock-all-tables：锁定所有库的所有表，读锁；

\-l, --lock-tables：锁定指定库所有表；

InnoDB存储引擎：

热备份：

\--single-transaction：创建一个事务，基于此快照执行备份；

温备份 :

\-x, --lock-all-tables：锁定所有库的所有表，读锁；

\-l, --lock-tables：锁定指定库所有表；

\[root@centos7b ~\]\#mysqldump --single-transaction --databases mydb;

\[root@centos7b ~\]\#mysqldump -l --databases mydb

其它选项：

\-R, --routines：存储过程和存储函数；

\--triggers

\-E, --events

\--master-data\[=\#\]

1：记录为CHANGE MASTER TO语句，此语句不被注释；

2：记录为CHANGE MASTER TO语句，此语句被注释；

\--flush-logs：锁定表完成后，即进行日志刷新操作；

四、实例

完全备份+二进制日志文件恢复数据。

1、对数据进行完全备份

\[root@centos7b ~\]\#mkdir -p /mydata/backup

\[root@centos7b ~\]\#mysqldump -x -R -E --triggers --all-databases --master-data=2 --flush-logs > /mydata/backup/all-databases-\`date +%F-%M-%H-%M\`

\#--master-data这个选项将会记录备份时，二进制日志的位置

2、模拟备份后新增数据

这部分数据，我们将通过二进制

MariaDB \[(none)\]> use mydb;

MariaDB \[mydb\]> create table teacher(id int unsigned auto\_increment primary key,name varchar(20) not null );

MariaDB \[mydb\]> insert into teacher (name) values ('Li Bai'),('Cao Cao');

3、查看备份的数据

\[root@centos7b ~\]\#less /mydata/backup/all-databases-2017-09-17-41-01-41

可以看到，备份时二进制日志定位在‘ON.000006’文件

![6d5d7527bbb9][]

image.png

把‘ON.000006’拷贝到/mydata/backup/all-databases-2017-09-17-41-01-41-binlog

\[root@centos7b mysql\]\#cd /var/lib/mysql/

\[root@centos7b mysql\]\#mysqlbinlog ON.000006 >/mydata/backup/all-databases-2017-09-17-41-01-41-binlog

4、模拟服务器崩溃：

删除/var/lib/mysql/目录下的文件，模拟数据丢失

\[root@centos7b mysql\]\#systemctl stop mariadb

\[root@centos7b mysql\]\#rm -rf /var/lib/mysql/\*

\#整个数据库被删除了

\[root@centos7b mysql\]\#systemctl start mariadb

\[root@centos7b mysql\]\#mysql

MariaDB \[(none)\]> show databases;

\+--------------------+

| Database |

\+--------------------+

| information\_schema |

| mysql |

| performance\_schema |

| test |

\+--------------------+

4 rows in set (0.00 sec)

5、恢复数据

\[root@centos7b mysql\]\#mysql < /mydata/backup/all-databases-2017-09-17-41-01-41

\#恢复备份的完全数据

\[root@centos7b mysql\]\#mysql < /mydata/backup/all-databases-2017-09-17-41-01-41-binlog

\#通过二进制日志，恢复备份后产生的数据

\#注意，恢复数据时会产生大量的二进制日志，建议先把该功能关闭了，恢复后再开启

6、验证

验证一下数据的恢复情况

\[root@centos7b mysql\]\#mysql

MariaDB \[(none)\]> show databases;

\+--------------------+

| Database |

\+--------------------+

| information\_schema |

| backdb |

| mydb |

| mysql |

| performance\_schema |

| test |

\+--------------------+

MariaDB \[mydb\]> show tables ;

\+----------------+

| Tables\_in\_mydb |

\+----------------+

| stu |

| students |

| tb2 |

| tb3 |

| tbl1 |

| teacher |

\+----------------+

MariaDB \[mydb\]> select \* from teacher;

\+----+---------+

| id | name |

\+----+---------+

| 1 | Li Bai |

| 2 | Cao Cao |

\+----+---------+

[6d5d7527bbb9]: /images/20221024/cb37faf079b54e4d8a27f2441c81f22b.png

mysql dump备份_mysqldump备份和恢复

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL——使用mysqldump备份与恢复数据

相关 Mysql备份与恢复工具mysqldump

相关 mysql dump xtrabackup_mysqldump xtrabackup 逻辑备份和物理备份

相关 mysql数据库逻辑备份与恢复_Mysqldump逻辑备份与恢复

相关 mysql dump备份_mysqldump备份和恢复

相关 MySQL binlogdump_mysqldump+binlog的备份与恢复和xtrabackup备份工具

相关 MySql备份之mysqldump

相关【MySQL】mysqldump备份与恢复

相关 MySQL5.7 --- mysqldump 备份与恢复

相关 MySql增量备份-mysqldump