使用mysqldump工具实现MySQL数据备份

mysqldump是一款mysql服务自带的功能强大的逻辑备份工具，通常使用mysqldump对 MySQL 数据库进行逻辑全量备份(所有数据集)。 mysqldump可以把要备份的数据库装载到一个单独的文本文件中，这个文件包含有所有重建该数据库所需要的SQL命令，这个文本文件可以用一个简单的批处理和一个合适SQL语句导回到MySQL中，完成恢复。

• 备份单个数据库或单个数据库中的指定表：

​ mysqldump [OPTIONS] database [tables]

• 备份多个数据库：

​ mysqldump [OPTIONS] –databases [OPTIONS] DB1 [DB2 DB3…]

• 备份所有数据库：

​ mysqldump [OPTIONS] –all-databases [OPTIONS]

对于使用MyISAM存储引擎的mysql只支持温备, Innodb存储引擎还支持热备

热备：读写操作均可进行的状态下所做的备份；

温备：可读但不可写状态下进行的备份

冷备：读写操作均不可进行的状态下所做的备份； 服务不在线

mysqldump命令行常用参数：

-x, –lock-all-tables：锁定所有库的所有表，读锁 <– 温备，备份时要锁定表； -l, –lock-tables：锁定指定库所有表 <–温备，备份时要锁定表；

–single-transaction：创建一个事务，基于此快照执行备份 <–InnoDB存储引擎支持热备

-R, –routines：备份指定库的存储过程和存储函数

–triggers：备份指定库的触发器

-E, –events ：备份指定库的事件调度器

–master-data[=#] <– 记录备份时二进制日志的position

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

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

–flush-logs：锁定表完成后，即进行日志刷新操作； <– 滚动日志，方便按备份时间点恢复

备份恢复单个数据库

#

1. 查看数据库testdb，数据库下的表引擎均为Innodb

$	 mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 29
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use testdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [testdb]> show table status\G;
*************************** 1. row ***************************
           Name: test1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 3
 Avg_row_length: 5461
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2017-09-13 16:22:53
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

MariaDB [testdb]> show table status where Engine!='Innodb';
Empty set (0.00 sec)

2. 使用mysqldump进行备份

# 热备
$ mysqldump -uroot -p --single-transaction --databases testdb --master-data=2 --flush-logs > /tmp/testdb-fullbackup-$(date +%F-%H-%M-%S)
Enter password: 
$ ls /tmp
testdb-fullbackup-2017-09-14-19-12-45

# 温备
$ mysqldump -uroot -p -x -R -E --triggers  --databases testdb --master-data=2 --flush-logs > /tmp/testdb-fullbackup-$(date +%F-%H-%M-%S)
Enter password: 
$ ls /tmp
testdb-fullbackup-2017-09-14-19-42-54

3. 删除数据库testdb，并恢复

[root@Centos7 ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 35
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> drop database testdb;
Query OK, 1 row affected (0.09 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

$ mysql -uroot -p123456 < /tmp/testdb-fullbackup-2017-09-14-19-12-45
$ mysql -uroot -p123456 -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| testdb             |
+--------------------+

上例只是简单演示数据库的备份恢复，实际生产中数据是在一直更新的。所有只是恢复备份是不够的，我们还需要重放执行备份后更新的二进制日志

备份恢复所有数据库

#

1. 使用mysqldump进行备份

$ mysqldump -uroot -p123456 -x -R -E --triggers  --databases testdb --master-data=2 --flush-logs > /tmp/testdb-fullbackup-$(date +%F-%H-%M-%S)

$ ls /tmp
testdb-fullbackup-2017-09-14-20-23-01

2. testdb库中创建新表test2

$ mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 43
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [testdb]> create table test2 (id int unsigned primary key,name varchar(200));
Query OK, 0 rows affected (0.03 sec)

MariaDB [testdb]> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| test1            |
| test2            |
+------------------+
2 rows in set (0.00 sec)
MariaDB [testdb]> insert into test2 values(1,'tom'),(2,'jerry');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [testdb]> select * from test2;
+----+-------+
| id | name  |
+----+-------+
|  1 | tom   |
|  2 | jerry |
+----+-------+
2 rows in set (0.00 sec)

3. 备份二进制日志文件（执行备份操作后的）

$ mysqlbinlog master-log.000005 > /tmp/testdb-backup-binlog

4. 删除/var/lib/mysql目录下所有数据库文件,模拟服务器崩溃

$ service mariadb stop
$ rm -rf /var/lib/mysql/*

5. 关闭mysql服务记录二进制日志功能(恢复数据无需记录)

$ vim /etc/my.cnf.d/server.cnf 
...
[mysqld]
server_id = 1
#log_bin = master-log
skip_name_resolve = ON
innodb_file_per_table = ON
...

6. 启动mysql服务并恢复备份数据

$ service mariadb start
$ mysql  < /tmp/testdb-fullbackup-2017-09-14-20-23-01

$ mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| table              |
| test               |
| testdb             |
+--------------------+
7 rows in set (0.00 sec)

MariaDB [(none)]> use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [testdb]> show tables;                             <-- 连入mysql，备份后的testdb.test2没有恢复
+------------------+ 
| Tables_in_testdb |
+------------------+
| test1            |
+------------------+
1 row in set (0.00 sec)

7. 重放二进制文件，恢复备份时间点后的数据

$ mysql < /tmp/testdb-backup-binlog 
$ mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.52-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [testdb]> show tables;                        <-- test2表数据也恢复了
+------------------+
| Tables_in_testdb |
+------------------+
| test1            |
| test2            |
+------------------+
2 rows in set (0.00 sec)

MariaDB [testdb]> select * from test2;           
+----+-------+
| id | name  |
+----+-------+
|  1 | tom   |
|  2 | jerry |
+----+-------+
2 rows in set (0.00 sec)

由于mysqldump是基于mysql客户端协议的备份工具，所以它也支持远程恢复指定数据库。还有一点mysqldump工具是将数据转换为特定格式的字符串并显示出来，所以使用mysqldump工具备份对于浮点型数据会丢失精度。