Home  >  Article  >  Database  >  使用mysqldump进行mariadb的备份

使用mysqldump进行mariadb的备份

WBOY
WBOYOriginal
2016-06-07 14:56:091291browse

mysqldump备份介绍 mysqldump 是 mysql 用于转存储数据库的实用程序。它主要产生一个 SQL 脚本 ,其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等,适用于备份数据量不大的数据库。 优点:备份简单,恢复容易。 备份缺点:schema和数据存储

mysqldump备份介绍

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

优点:备份简单,恢复容易。

备份缺点:schema和数据存储在一起,巨大的SQL语句、单个巨大的备份文件(备份的库和表都在一个文件中)。


mysqldump: 是一个mysql客户端命令,通过mysql协议连接至mysqld,实现数据库备份

命令的语法格式:

   mysqldump [OPTIONS] database [tables]:备份单个库,或库指定的一个或多个表

mysqldump[OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]:备份一个或多个库

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

常用选项:

    -A,--all-databases       #备份所有数据库

    InnoDB:

        --single-transaction:启动一个大的单一事务实现备份

     -B,--databases db_name1 db_name2 ...:备份指定的数据库

     -C,--compress:压缩传输;

     -x, --lock-all-tables:锁定所有表

     -l, --lock-tables:锁定备份的表

     --flush-logs,-F:锁定表之后执行flush logs命令;

其它选项:

    -E,--events:备份指定库的事件调度器;

    -R,--routines:备份存储过程和存储函数;

    --triggers:备份触发器

 

    --master-data[=#]

        1:记录CHANGE MASTER TO语句;此语句未被注释;

        2:记录为注释语句;


特别说明:Mariadb的数据恢复严重依赖与bin-log日志,所以为了防止磁盘故障导致数据文件和bin-log文件一起丢失,所以最好将bin-log日志存放到共享存储中。

设置方法:修改Mariadb配置文件,将日志文件存放位置指向本地挂载网络存储的路径,然后重启Mariadb服务即可。

[root@MariaDB ~]# vim /etc/my.cnf
log-bin="/backup/bin-log/mysql-bin.x"

验证:重启服务之后/backup/bin-log/目录下就有了Mysql的二进制日志文件和日志索引文件。

[root@MariaDB ~]# ll /backup/bin-log/mysql-bin.*
-rw-rw---- 1 mysql mysql 245 Jun 16 00:00/backup/bin-log/mysql-bin.000001
-rw-rw---- 1 mysql mysql  33 Jun 16 00:00/backup/bin-log/mysql-bin.index

单个库实现备份恢复

数据库中有hellodb表一个,需要做对hellodb表的备份,以实现数据库故障或者发生误删除操作时可以及时恢复数据库。

备份方案为:每周日完全备份一次数据库,周一到周六增量备份数据库

备份过程如下

第一天备份:完全备份hellodb数据库,并且在完全备份的时候锁定表和滚动二进制日志

[root@MariaDB~]# mysqldump -B hellodb -u root -p --lock-all-tables --flush-logs  --master-data=2 > /backup/hellodb-`date+%F`.sql
Enterpassword: 
[root@MariaDB~]# ll /backup/
total8
-rw-r--r--1 root root 7950 Jun 16 11:59 hellodb-2015-06-16.sql

由于在做完全备份的时候滚动了二进制日志,所以在做一些关于数据库更改的操作都会记录到新的二进制日志中,从查看中得知后续日志会记录到mysql-bin.000002中。

MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name        | File_size |
+------------------+-----------+
| mysql-bin.000001 |       288 |
| mysql-bin.000002 |       245 |
+------------------+-----------+
2 rows in set (0.00 sec)

备份完成之后的一天时间里,创建了一张表,并且向表中插入了一些数据

MariaDB[(none)]> use hellodb;
MariaDB[hellodb]> create table tb1 (id int);
MariaDB[hellodb]> insert into tb1 values (1),(2),(3);
MariaDB[hellodb]> select * from tb1;
+------+
|id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

第一天增量备份:当天的所有对数据库进行更改的语句都会记录到二进制日志文件中,只需要滚动二进制日志,把二进制日志进行备份即可,二进制滚动之后,第二天的所有对数据库进行更改的语句,都会记录到新的二进制日志文件中。

MariaDB[hellodb]> flush logs;
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name        | File_size |
+------------------+-----------+
| mysql-bin.000001 |       288 |
| mysql-bin.000002 |       577 |
| mysql-bin.000003 |       245 |
+------------------+-----------+
3 rows in set (0.00 sec)

将二进制日志文件转换为sql文件

[root@MariaDB ~]# mysqlbinlog/backup/bin-log/mysql-bin.000002 > /backup/1.sql

第二天操作:继续向tb1中插入数据

MariaDB[hellodb]> insert into tb1 values (21),(22),(23);
MariaDB[hellodb]> select * from tb1;
+------+
|id   |
+------+
|    1 |
|    2 |
|    3 |
|   21 |
|   22 |
|   23 |
+------+

故障模拟

hellodb数据库遭到误删除:

MariaDB[hellodb]> DROP database hellodb;

恢复前的准备

查看二进制日志是记录到了第三个日志

MariaDB[(none)]> show master logs;
+------------------+-----------+
|Log_name         | File_size |
+------------------+-----------+
|mysql-bin.000001 |       288 |
|mysql-bin.000002 |       577 |
|mysql-bin.000003 |       533 |    #当前日志的记录位置
+------------------+-----------+
3rows in set (0.00 sec)
MariaDB[(none)]> show binlog events in 'mysql-bin.000003'\G;
***************************5. row ***************************
   Log_name: mysql-bin.000005
        Pos: 446
 Event_type: Query
  Server_id: 1
End_log_pos:533
       Info: DROP database hellodb      #记录的删除语句
5rows in set (0.00 sec)
 
ERROR:No query specified



由于是整个hellodb数据库遭到误删除,所以需要用一开始的完全备份文件,以及第一天的增量备份文件,恢复hellodb数据库和第一天对数据库进行修改的内容。

第二天对数据库进行修改的内容恢复:如果直接把当天的二进制日志导入到数据库,由于数据库中包含了删除语句,所以数据库还是会被删除;所以在导入第二天二进制日志时,需要删除日志中的DROP语句。

 

将第二天的二进制日志文件转换成sql文件,放到backup目录下

[root@MariaDB~]# mysqlbinlog /backup/bin-log/mysql-bin.000003 > /backup/2.sql

打开2.sql文件可以看到DROP语句,删除这个语句

# at446
#15061612:15:22 server id 1  end_log_pos 533   Query  thread_id=20    exec_time=0     error_code=0
SETTIMESTAMP=1434428122/*!*/;
DROP database hellodb   #删除或者注释这一行

恢复过程:

恢复之前为了避免产生没有用的二进制日志,可以关闭二进制日志的记录

MariaDB[(none)]> SET SESSION sql_log_bin=0;

将第一次的完全备份数据导入到数据库

MariaDB [hellodb]> SOURCE /backup/hellodb-2015-06-16.sql;

查看数据库已经导入,但是tb1表不存在

MariaDB[(none)]> use hellodb;
Databasechanged
MariaDB[hellodb]> show tables;
+-------------------+
|Tables_in_hellodb |
+-------------------+
|classes           |
|coc               |
|courses           |
|scores            |
|students          |
|teachers          |
|toc               |
+-------------------+

导入第一天增量备份的1.sql文件。

MariaDB [hellodb]> SOURCE /backup/1.sql;

查看tb1表,发现第一天插入的数据都存在

MariaDB[hellodb]> select * from tb1;
+------+
|id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

导入当天的2.sql文件

MariaDB [hellodb]> SOURCE /backup/2.sql;

查看数据已经完全恢复

MariaDB[hellodb]> select * from tb1;
+------+
|id   |
+------+
|    1 |
|    2 |
|    3 |
|   21 |
|   22 |
|   23 |
+------+

恢复完成,启用记录二进制日志

MariaDB[hellodb]> SET SESSION sql_log_bin=1;

特别说明:如果在恢复中关闭二进制日志,导入数据必须在Mariadb命令行导入,如果在shell命令行导入还是会记录二进制日志的。


全库的备份恢复

备份

第一次完全备份,备份时锁定表,并滚动二进制日志

[root@MariaDB ~]# mysqldump -A -u root -p--lock-all-tables --flush-logs --master-data=2 > /backup/ALL-`date +%F`.sql
Enter password:
[root@MariaDB ~]# ll /backup/
total 532
-rw-r--r-- 1 root root    1980 Jun 16 00:46 1.sql
-rw-r--r-- 1 root root    1957 Jun 16 00:52 2.sql
-rw-r--r-- 1 root root  521774 Jun 16 01:04ALL-2015-06-16.sql
drwxr-xr-x 2 mysql mysql   4096 Jun 16 01:04 bin-log
-rw-r--r-- 1 root root    7950 Jun 16 00:43hellodb-2015-06-16.sql

由于备份的时候滚动了二进制日志,所以备份之后所有对数据库产生更改的操作都会记录到mysql-bin.000004中。

MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name        | File_size |
+------------------+-----------+
| mysql-bin.000001 |       288 |
| mysql-bin.000002 |       577 |
| mysql-bin.000003 |      8833 |
| mysql-bin.000004 |       245 |
+------------------+-----------+
4 rows in set (0.00 sec)

备份完成的当天做了一些操作。

MariaDB[hellodb]> delete from tb1 where id=21;
MariaDB[hellodb]> delete from tb1 where id=22;
MariaDB[hellodb]> select * from tb1;
+------+
|id   |
+------+
|    1 |
|    2 |
|    3 |
|   23 |
+------+


第一天增量备份

当天的所有对数据库进行更改的语句都会记录到二进制日志文件中,只需要滚动二进制日志,把二进制日志进行备份即可,二进制滚动之后,第二天的所有对数据库进行更改的语句,都会记录到新的二进制日志文件中。

MariaDB [hellodb]> flush logs;
MariaDB [hellodb]> show master logs;
+------------------+-----------+
| Log_name        | File_size |
+------------------+-----------+
| mysql-bin.000001 |       288 |
| mysql-bin.000002 |       577 |
| mysql-bin.000003 |      8833 |
| mysql-bin.000004 |       670 |
| mysql-bin.000005 |       245 |
+------------------+-----------+
5 rows in set (0.00 sec)

将第一天的二进制日志文件转换为sql文件

[root@MariaDB ~]# mysqlbinlog /backup/bin-log/mysql-bin.000004> /backup/all.1.sql

 

第二天对数据进行了一些操作

MariaDB[hellodb]> insert into tb1 values (1000),(9000);
MariaDB[hellodb]> select * from tb1;
+------+
|id   |
+------+
|    1 |
|    2 |
|    3 |
|   23 |
|1000 |
|9000 |
+------+

模拟故障

删除数据目录下的所有文件,模拟数据库故障:

[root@MariaDB ~]# rm -rf /mydata/data/*

数据库这个时候还可以登录,但是数据库都不再了

MariaDB [hellodb]> show databases;
+--------------------+
| Database          |
+--------------------+
| information_schema |
+--------------------+

 

恢复前的准备

发现数据库故障先关闭数据库,但是数据库无法正常关闭,只能关闭进程

[root@MariaDB ~]# service mysqld stop
MySQL server PID file could not be found!                  [FAILED]
[root@MariaDB ~]# killall mysqld

 

由于数据库数据目录的所有内容被删除,就算是导入了完全备份文件也是缺少一些文件,解决方法就是重新初始化一下数据库。

[root@MariaDB ~]# cd /usr/local/mysql/
[root@MariaDB mysql]# scripts/mysql_install_db--user=mysql --datadir=/mydata/data/

初始化完成之后,一些基本的文件都存在了

[root@MariaDB mysql]# ll /mydata/data/
total 36
-rw-rw---- 1 mysql mysql 16384 Jun 16 01:22aria_log.00000001
-rw-rw---- 1 mysql mysql    52 Jun 16 01:22 aria_log_control
-rw-r----- 1 mysql root     80 Jun 16 01:18 MariaDB.err
drwx------ 2 mysql root   4096 Jun 16 01:22 mysql
drwx------ 2 mysql mysql  4096 Jun 16 01:22 performance_schema
drwx------ 2 mysql root   4096 Jun 16 01:22 test

 

将第二天的二进制日志文件转换为sql文件

[root@MariaDB ~]# mysqlbinlog/backup/bin-log/mysql-bin.000005 > /backup/all.2.sql

恢复过程

恢复之前启动Mysql服务,否则无法导入备份文件

[root@MariaDB ~]# service mysqld start

导入第一天的完全备份文件,由于刚刚初始化完成数据,这个时候的数据库还没有密码

[root@MariaDB ~]# mysql </backup/ALL-2015-06-16.sql

登录数据库查看,所有的数据库都已经恢复,但是第一天对数据库更改的内容还是没有恢复

MariaDB [(none)]> show databases;
+--------------------+
| Database          |
+--------------------+
| information_schema |
| hellodb           |
| mysql             |
| performance_schema |
| test              |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [hellodb]> select * from tb1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   21 |
|   22 |
|   23 |
+------+
6 rows in set (0.00 sec)

导入第一天的增量备份文件

[root@MariaDB ~]# mysql -u root -p < /backup/all.1.sql

发现第一天删除的文件已经不存在,但是第二天添加的内容还是没有

MariaDB [hellodb]> select * from tb1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   23 |
+------+
4 rows in set (0.00 sec)

导入第二天的二进制日志转换后的sql文件。

[root@MariaDB ~]# mysql -u root -p </backup/all.2.sql

导入完成之后,数据库就回到了故障之前的状态

MariaDB [hellodb]> select * from tb1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|   23 |
| 1000 |
| 9000 |
+------+
6 rows in set (0.00 sec)
Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn