基于mysqldump备份mysql(MairaDB)

一、简介

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

   它可以把整个数据库装载到一个单独的文本文件中。这个文件包含有所有重建你的数据库所需要的SQL命令。这个命令取得所有的模式并且将其转换成DDL语法(CREATE语句,即数据库定义语句),取得所有的数据,并且从这些数据中创建INSERT语句。这个工具将你的数据库中所有的设计倒转。因为所有的东西都被包含到了一个文本文件中。这个文本文件可以用一个简单的批处理和一个合适SQL语句导回到MySQL中。这个工具令人难以置信地简单而快速。决不会有半点让人头疼的地方

   mysqldump是一个逻辑备份工具,它支持InnoDB的热备,MyISAM温备(需锁表),很难实现差异备份和增量备份,如果非要这么做的话,需结合binlog日志文件;它是一个单线程的备份工具,所以备份和恢复过程较慢,如果你的数据量大于10G的话,最好还是使用别的工具吧

二、mysqldump的备份方式及选项

1、备份类型

注意:备份前要加锁

(1)备份单个库或库中的指定表

1
# mysqldump [options] db_name [tb1][tb2]...

单个库备份例子:

1
#mysqldump -uroot -hlocalhost -p hellodb > /backups/hdb.sql

当备份完成后,如果数据小的话,可以使用vim打开查看;如果数据量大的话,千万别这么玩儿

恢复时:如果目标不存在,需事先手动创建

(2)备份多个数据库

1
#mysqldump [options] --databases[options] DB1 [DB2 DB3...] > /backups/bak.sql

哪怕只有一个库时,最好也加上--databases,真正的备份方式应该是这样的:

1
#mysqldump --databases hellodb --lock-all-tables > /backups/hdb.sql

(3)备份所有数据库

1
#mysqldump [options] --all-databases [options] > /backups/all.sql

2、选项[options]

(1)--lock-all-tables

   请求锁定所有表之后再备份,对MyISAM、InnoDB、Aria做温备

   但是这种方式并不理想,因为它锁定了所有表

(2)--single-transaction

   启用一个单一的大事务,基于多版本并发控制完成对InnoDB存储的热备

   能够对InnoDB存储引擎实现热备

   如果使用此选项,就不要使用--lock-all-tables了,因为它会自动加锁

(3)备份代码

   ①、--events:备份事件调度器代码

   ②、--routines:备份存储过程和存储函数

   ③、--triggers备份解发器

(4)备份时滚动日志

   --flush-logs:备份前,请求到锁之后滚动日志

(5)复制时的同步位置标记

   --master-data=[0|1|2]

   0:不记录

   1:记录为CHANGE MASTER语句

   2:记录为注释的CHANGE MASTER语句

三、建议及备份策略

1、使用mysqldump备份

   请求锁:--lock-all-tables或使用--single-transaction进行innodb热备

   滚动日志:--flush-logs

   选定要备份的库:--databases

   记录二进制日志文件及位置:--master-data=

2、恢复

   关闭二进制日志(恢复过程没有必要记录二进制日志),关闭其它用户连接

3、备份策略(基于mysqldump)

   备份:

       mysqldump+二进制日志文件

   恢复:

       完全备份+各二进制日志文件中至此刻的事件

   对于MySQL配置文件,以及与MySQL相关的OS配置文件在每次修改后都应该直接进行备份

四、备份及恢复

1、准备工作

(1)数据库前提说明

   数据目录在/mydata/data下,配置文件在/etc/my.cnf,二进制日志在/mydata/binlogs

(2)创建备份存储目录

1
[root@shuishui ~]# mkdir /backups/

(3)导入数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
[root@shuishui ~]# mysql < hellodb.sql
[root@shuishui ~]# mysql
MariaDB [(none)]>
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| bynr               |
| hellodb            |
| hhht               |
| hlbrc              |
| information_schema |
| makingware         |
| mysql              |
| performance_schema |
test               |
| ultrax             |
+--------------------+
MariaDB [(none)]> use hellodb;
MariaDB [hellodb]> 
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+

2、单库备份(温备)及恢复

(1)查看hellodb数据库当前所使用的二进制日志及位置

1
2
3
4
5
6
MariaDB [hellodb]> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000019 |     8951 |              |                  |
+-------------------+----------+--------------+------------------+

(2)查看hello的数据库引擎,然后选用备份方式(申请锁表还是单事务)

基于mysqldump备份mysql(MairaDB)

查看表状态信息得知,hellodb中的所有表都是MyISAM存储引擎的,那就可以申请全局锁来备份了

(3)备份操作

1
2
3
4
5
6
[root@shuishui ~]# mysqldump -uroot -p -hlocalhost --databases hellodb --flush-logs --master-data=2 > /backups/hellodb.bak.sql
Enter password:
[root@shuishui ~]# ll /backups/hellodb.bak.sql
-rw-r--r-- 1 root root 7953 Apr 29 15:29 /backups/hellodb.bak.sql
[root@shuishui ~]# file /backups/hellodb.bak.sql
/backups/hellodb.bak.sql: ASCII text, with very long lines

(4)恢复

   ①、在备份后,对hellodb又做了修改操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
MariaDB [hellodb]>
MariaDB [hellodb]> create table nmg (
    -> id tinyint auto_increment not null primary key,
    -> city varchar(20)
    -> );
Query OK, 0 rows affected (0.07 sec)
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| nmg               |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+

   ②、手误,删除hellodb数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
MariaDB [hellodb]> drop database hellodb;
Query OK, 8 rows affected (0.09 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| bynr               |
| hhht               |
| hlbrc              |
| information_schema |
| makingware         |
| mysql              |
| performance_schema |
test               |
| ultrax             |
+--------------------+

   ③、恢复

   第一步:导出二进制日志,以备即时点恢复使用

1
2
3
4
5
6
7
8
[root@shuishui ~]# cd /mydata/binlogs/
[root@shuishui binlogs]# ls
master-bin.000001  master-bin.000007  master-bin.000013  master-bin.000019
master-bin.000002  master-bin.000008  master-bin.000014  master-bin.000020
master-bin.000003  master-bin.000009  master-bin.000015  master-bin.index
master-bin.000004  master-bin.000010  master-bin.000016
master-bin.000005  master-bin.000011  master-bin.000017
master-bin.000006  master-bin.000012  master-bin.000018

   因为我们刚才在备份的时候做了滚动日志的操作(--flush-logs),所以在备份之后的所有操作都应该记录到了第20个二进制日志文件中,现在我们导出来看看:

1
2
[root@shuishui binlogs]# mysqlbinlog master-bin.000020 > mysqldump.sql
[root@shuishui binlogs]# vim mysqldump.sql

基于mysqldump备份mysql(MairaDB)


从上图可以看出来,我们在第20个二进制日志文件中的第593个点执行了"drop database hellodb",那我们在恢复时就以593这个点为stop-position

1
2
3
[root@shuishui binlogs]# pwd
/mydata/binlogs
[root@shuishui binlogs]# mysqlbinlog --stop-position=593 master-bin.000020 > /backups/binlogbakup.sql


  第二步:恢复完全备份 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
MariaDB [(none)]> set session sql_log_bin=0;   #恢复过程不需记录二进制日志,切记,在恢复完成后一定要开启二进制日志,否则后续操作都不会被记录到二进制日志
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]> source /backups/hellodb.bak.sql
MariaDB [hellodb]> show databases;
+--------------------+
| Database           |
+--------------------+
| bynr               |
| hellodb            |
| hhht               |
| hlbrc              |
| information_schema |
| makingware         |
| mysql              |
| performance_schema |
test               |
| ultrax             |
+--------------------+


1
2
3
4
5
6
7
8
9
10
11
12
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+

查看当前hellodb数据库中的表,是没有我们在备份这后创建的那个nmg表,所以接下来的操作我们应该做即时点恢复

   第三步:即时点恢复

   这里做即时点恢复,我们只需要导入刚才准备好的那个/backups/binlogbakup.sql就可以了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
MariaDB [hellodb]> source /backups/binlogbakup.sql
MariaDB [hellodb]>
MariaDB [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| nmg               |    #做即时点恢复表nmg成功
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+

   ④、开启二进制日志记录

1
2
3
MariaDB [hellodb]> set session sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
#千万别忘了,否则后续操作就不会被记录到二进制日志中

五、结语

   到这里,关于mysqldump的演示就算完成了,虽然只做了单库演示,但多库备份恢复也是一样的道理,就不再演示了

   还有一个要说的就是:这里备份的是MyISAM存储引擎的表,如果是InnoDB引擎的表,应该使用下面这样的命令

1
[root@MariaDB ~]# mysqldump -uroot -hlocalhost -p --database hellodb --single-transaction --flush-logs > /backups/abc.sql









本文转自 nmshuishui 51CTO博客,原文链接:http://blog.51cto.com/nmshuishui/1404963,如需转载请自行联系原作者
上一篇:Huge pages (标准大页)和 Transparent Huge pages(透明大页)


下一篇:CentOS 7更改yum源与更新系统