Mariadb备份
本文主要详细说明Mariadb如何使用mysqldump和Xtrabackup备份mysql数据库
mysqldump实现如下功能：
1、对hellodb数据库进行增删后还原hellodb数据库；
2、由还原后的hellodb收据库再还原至增删后的hellodb收据库；
msyqldump备份及还原
mysqldump备份数据库
[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
;;;
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;;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 ;;;可以看到在位置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;
mysqldump还原数据库
还原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;
使用Xtrabackup进行备份还原
使用Xtrabackup对当前数据库进行备份
安装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使用Xtrabackup对当前数据库进行还原
[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
使用Xtrabackup对当前数据库进行增量备份
第一次备份
[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 ~]#增量还原
将数据还原至新的一台服务器上
阅读原文内容投诉
