mysql dump备份_mysqldump备份和恢复

本文详细介绍了如何使用mysqldump工具进行MySQL数据库的备份,包括单库备份、多库备份、所有库备份,并讲解了不同场景下的备份策略。同时,还展示了如何进行数据恢复,包括在线业务备份、存储过程和事件的备份,以及如何结合二进制日志文件进行数据恢复。
摘要由CSDN通过智能技术生成

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 |

+----+---------+

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值