本文主要详细说明Mariadb如何使用MySQLdump和Xtrabackup备份mysql数据库
创新互联-专业网站定制、快速模板网站建设、高性价比从江网站开发、企业建站全套包干低至880元,成熟完善的模板库,直接使用。一站式从江网站制作公司更省心,省钱,快速模板网站建设找我们，业务覆盖从江地区。费用合理售后完善，10多年实体公司更值得信赖。
mysqldump实现如下功能：
1、对hellodb数据库进行增删后还原hellodb数据库；
2、由还原后的hellodb收据库再还原至增删后的hellodb收据库；
[root@localhost ~]# mysqldump --databases hellodb --single-transaction -R --triggers -E --master-data=2 --flush-logs > /tmp/hellodb-$(date +%F)
数据库操作如下：
删除stuid=25,增加stuid=88,然后删除数据库同时备份二进制日志;
[root@localhost ~]# mysqlWelcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 5.5.44-MariaDB-log MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use students;
ERROR 1049 (42000): Unknown database 'students'
MariaDB [(none)]> use hellodb;
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 [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
MariaDB [hellodb]> delete from students where stuid=25;
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
24 rows in set (0.00 sec)
MariaDB [hellodb]> insert into students values ('88','tangceng','88','M','3','7');
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 88 | tangceng | 88 | M | 3 | 7 |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
MariaDB [hellodb]> drop database hellodb;
Query OK, 7 rows affected (0.04 sec)
MariaDB [hellodb]> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-log.000007 | 670 | | |
+-------------------+----------+--------------+------------------+使用mysqlbinlog命令查看日志文件master-log.000007 ；
[root@localhost ~]# mysqlbinlog /var/lib/mysql/master-log.000007
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!40019 SET @@session.max_insert_delayed_threads=0*/;/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170221 8:56:27 server id 1 end_log_pos 245 Start: binlog v 4, server v 5.5.44-MariaDB-log created 170221 8:56:27# Warning: this binlog is either in use or was not closed properly.BINLOG '
O5CrWA8BAAAA8QAAAPUAAAABAAQANS41LjQ0LU1hcmlhREItbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAsXMXYw==
'/*!*/;# at 245
#170221 8:59:58 server id 1 end_log_pos 316 Query thread_id=20 exec_time=0 error_code=0SET TIMESTAMP=1487638798/*!*/;SET @@session.pseudo_thread_id=20/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=0/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8 *//*!*/;SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;BEGIN/*!*/;# at 316
#170221 8:59:58 server id 1 end_log_pos 417 Query thread_id=20 exec_time=0 error_code=0use `hellodb`/*!*/;SET TIMESTAMP=1487638798/*!*/;delete from students where stuid=25/*!*/;# at 417
#170221 8:59:58 server id 1 end_log_pos 444 Xid = 913COMMIT/*!*/;# at 444
#170221 9:04:49 server id 1 end_log_pos 515 Query thread_id=20 exec_time=0 error_code=0SET TIMESTAMP=1487639089/*!*/;BEGIN/*!*/;# at 515
#170221 9:04:49 server id 1 end_log_pos 643 Query thread_id=20 exec_time=0 error_code=0SET TIMESTAMP=1487639089/*!*/;insert into students values ('88','tangceng','88','M','3','7')/*!*/;# at 643
#170221 9:04:49 server id 1 end_log_pos 670 Xid = 921COMMIT/*!*/;# at 670
#170221 9:15:08 server id 1 end_log_pos 757 Query thread_id=20 exec_time=0 error_code=0SET TIMESTAMP=1487639708/*!*/;drop database hellodb/*!*/;DELIMITER ;
# End of log fileROLLBACK /* added by mysqlbinlog */;/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;可以看到在位置670为删除数据库，所以备份670位置之前的二进制文件；
[root@localhost ~]#mysqlbinlog --stop-position=670 /var/lib/mysql/master-log.000007 > /tmp/bin.log
注意在备份的时候需要关闭二进制日志，避免将备份的步骤添加至二进制文件 MariaDB [(none)]> set @@session.sql_log_bin=OFF;
还原1
MariaDB [(none)]> source /tmp/hellodb-2017-02-21;
如下已完成数据库恢复至最初的数据；
MariaDB [hellodb]> select * from students; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 25 | Sun Dasheng | 100 | M | NULL | NULL | +-------+---------------+-----+--------+---------+-----------+ 25 rows in set (0.00 sec)
还原2
MariaDB [(none)]> source /tmp/bin.log
还原数据如下已还原至更改过得表信息：
MariaDB [hellodb]> select * from students; +-------+---------------+-----+--------+---------+-----------+ | StuID | Name | Age | Gender | ClassID | TeacherID | +-------+---------------+-----+--------+---------+-----------+ | 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 2 | Shi Potian | 22 | M | 1 | 7 | | 3 | Xie Yanke | 53 | M | 2 | 16 | | 4 | Ding Dian | 32 | M | 4 | 4 | | 5 | Yu Yutong | 26 | M | 3 | 1 | | 6 | Shi Qing | 46 | M | 5 | NULL | | 7 | Xi Ren | 19 | F | 3 | NULL | | 8 | Lin Daiyu | 17 | F | 7 | NULL | | 9 | Ren Yingying | 20 | F | 6 | NULL | | 10 | Yue Lingshan | 19 | F | 3 | NULL | | 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 12 | Wen Qingqing | 19 | F | 1 | NULL | | 13 | Tian Boguang | 33 | M | 2 | NULL | | 14 | Lu Wushuang | 17 | F | 3 | NULL | | 15 | Duan Yu | 19 | M | 4 | NULL | | 16 | Xu Zhu | 21 | M | 1 | NULL | | 17 | Lin Chong | 25 | M | 4 | NULL | | 18 | Hua Rong | 23 | M | 7 | NULL | | 19 | Xue Baochai | 18 | F | 6 | NULL | | 20 | Diao Chan | 19 | F | 7 | NULL | | 21 | Huang Yueying | 22 | F | 6 | NULL | | 22 | Xiao Qiao | 20 | F | 1 | NULL | | 23 | Ma Chao | 23 | M | 4 | NULL | | 24 | Xu Xian | 27 | M | NULL | NULL | | 88 | tangceng | 88 | M | 3 | 7 | +-------+---------------+-----+--------+---------+-----------+ 25 rows in set (0.00 sec)
还原完成后开启MariaDB [(none)]> set @@session.sql_log_bin=ON;
安装percona-xtrabackup-2.3.2-1.el7.x86_64.rpm软件
创建备份目录并备份数据库
[root@localhost ~]# mkdir -p /data/backup[root@localhost ~]# innobackupex --user=root --host=localhost /data/backupxtrabackup: Transaction log of lsn (1756943) to (1756943) was copied.170221 14:01:41 completed OK! [root@localhost mysql]# ll /data/backup/2017-01-19_10-40-32/ total 118804-rw-r----- 1 root root 385 Jan 19 10:41 backup-my.cnfdrwx------ 2 root root 138 Jan 19 10:41 hellodb-rw-r----- 1 root root 18874368 Jan 19 10:43 ibdata1-rw-r--r-- 1 root root 50331648 Jan 19 10:43 ib_logfile0-rw-r--r-- 1 root root 50331648 Jan 19 10:43 ib_logfile1drwx------ 2 root root 4096 Jan 19 10:41 mysqldrwx------ 2 root root 4096 Jan 19 10:41 performance_schemadrwx------ 2 root root 19 Jan 19 10:41 test-rw-r----- 1 root root 113 Jan 19 10:43 xtrabackup_checkpoints-rw-r----- 1 root root 438 Jan 19 10:41 xtrabackup_info-rw-r----- 1 root root 2097152 Jan 19 10:43 xtrabackup_logfile[root@localhost mysql]# vim /etc/my.conf.d/server.conf innodb_log_file_size = 50331648
[root@localhost mysql]# innobackupex --apply-log /data/backup/2017-01-19_10-40-32/ [root@localhost mysql]# innobackupex --copy-back /data/backup/2017-01-19_10-40-32/ [root@localhost mysql]# chown -R mysql.mysql ./* [root@localhost mysql]# systemctl start mariadb
第一次备份
[root@localhost ~]# innobackupex --user=root --host=localhost /data/backup/
170119 14:50:49 [00] ...done xtrabackup: Transaction log of lsn (1680362) to (1680362) was copied. 170119 14:50:49 completed OK! [root@localhost ~]# ll /data/backup/ total 4drwx------ 5 root root 4096 Jan 19 14:50 2017-01-19_14-50-46[root@localhost ~]# cat /data/backup/2017-01-19_14-50-46/xtrabackup_checkpoints backup_type = full-backuped from_lsn = 0to_lsn = 1680362last_lsn = 1680362compact = 0recover_binlog_info = 0[root@localhost ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 27 Server version: 5.5.44-MariaDB MariaDB Server Copyright (c) 2000, 2015, 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 | | mysql | | performance_schema | | test | +--------------------+4 rows in set (0.12 sec) MariaDB [(none)]>
在数据库中增加hellodb数据库
[root@localhost ~]# mysql < hellodb.sql [root@localhost ~]# msyql-bash: msyql: command not found [root@localhost ~]# mysqlWelcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 29Server version: 5.5.44-MariaDB MariaDB Server Copyright (c) 2000, 2015, 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 | | hellodb | | mysql | | performance_schema | | test | +--------------------+5 rows in set (0.00 sec) MariaDB [(none)]>
第一次增量备份
[root@localhost ~]# innobackupex --incremental --user=root --host=localhost /data/backup/ --incremental-basedir=/data/backup/2017-01-19_14-50-46/
[root@localhost ~]# ll /data/backup/total 8drwx------ 5 root root 4096 Jan 19 14:50 2017-01-19_14-50-46drwx------ 6 root root 4096 Jan 19 14:56 2017-01-19_14-56-12[root@localhost ~]# cat /data/backup/2017-01-19_14-56-12/xtrabackup_checkpoints backup_type = incremental from_lsn = 1680362to_lsn = 1708884last_lsn = 1708884compact = 0recover_binlog_info = 0[root@localhost ~]#
在hellodb数据库中增加haha表；
[root@localhost ~]# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 32 Server version: 5.5.44-MariaDB MariaDB ServerCopyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> ls -> ;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ls' at line 1 MariaDB [(none)]> use hellodb; 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 [hellodb]> create table haha(id int,name char(100));Query OK, 0 rows affected (0.01 sec) MariaDB [hellodb]>
进行第二次增量备份
[root@localhost ~]# innobackupex --incremental --user=root --host=localhost /data/backup/ --incremental-basedir=/data/backup/2017-01-19_14-56-12/
[root@localhost ~]# ll /data/backup/total 12drwx------ 5 root root 4096 Jan 19 14:50 2017-01-19_14-50-46drwx------ 6 root root 4096 Jan 19 14:56 2017-01-19_14-56-12drwx------ 6 root root 4096 Jan 19 15:02 2017-01-19_15-02-14[root@localhost ~]# cat /data/backup/2017-01-19_15-02-14/xtrabackup_checkpoints backup_type = incremental from_lsn = 1708884to_lsn = 1711375last_lsn = 1711375compact = 0recover_binlog_info = 0[root@localhost ~]#
*差异备份
[root@localhost ~]# innobackupex --incremental --user=root --host=localhost /data/backup/ --incremental-basedir=/data/backup/2017-01-19_14-50-46/
[root@localhost ~]# ll /data/backup/total 16drwx------ 5 root root 4096 Jan 19 14:50 2017-01-19_14-50-46drwx------ 6 root root 4096 Jan 19 14:56 2017-01-19_14-56-12drwx------ 6 root root 4096 Jan 19 15:02 2017-01-19_15-02-14drwx------ 6 root root 4096 Jan 19 15:06 2017-01-19_15-06-29[root@localhost ~]# cat /data/backup/2017-01-19_15-06-29/xtrabackup_checkpoints backup_type = incremental from_lsn = 1680362to_lsn = 1711375last_lsn = 1711375compact = 0recover_binlog_info = 0[root@localhost ~]#
将数据还原至新的一台服务器上
http://bzwzjz.com/article/gigsce.html