手机版
你好,游客 登录 注册 搜索
背景:
阅读新闻

使用mysqldump进行MariaDB 的备份

[日期:2015-07-22] 来源:Linux社区  作者:ximenfeibing [字体: ]

MySQLdump备份介绍
mysqldump是mysql用于转存储数据库的实用程序。它主要产生一个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文件。
1 [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)

基于mysqldump快速搭建从库  http://www.linuxidc.com/Linux/2015-04/116170.htm

恢复mysqldump创建的备份集  http://www.linuxidc.com/Linux/2015-02/113631.htm

mysqldump缺失-q参数导致MySQL被oom干掉 http://www.linuxidc.com/Linux/2013-09/90092.htm

CentOS/RHEL/Scientific Linux 6 下安装 LAMP (Apache with MariaDB and PHP) http://www.linuxidc.com/Linux/2013-07/86755.htm

MariaDB Proxy读写分离的实现 http://www.linuxidc.com/Linux/2014-05/101306.htm

Linux下编译安装配置MariaDB数据库的方法 http://www.linuxidc.com/Linux/2014-11/109049.htm

CentOS系统使用yum安装MariaDB数据库 http://www.linuxidc.com/Linux/2014-11/109048.htm

安装MariaDB与MySQL并存 http://www.linuxidc.com/Linux/2014-11/109047.htm

Ubuntu 上如何将 MySQL 5.5 数据库迁移到 MariaDB 10  http://www.linuxidc.com/Linux/2014-11/109471.htm

[翻译]Ubuntu 14.04 (Trusty) Server 安装 MariaDB  http://www.linuxidc.com/Linux/2014-12/110048htm

MariaDB 的详细介绍请点这里
MariaDB 的下载地址请点这里

本文永久更新链接地址http://www.linuxidc.com/Linux/2015-07/120294.htm

linux
相关资讯       mysqldump  MariaDB备份 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款