如何使用mysqldump/xtrabackup备份
发表于：2024-10-22 作者：千家信息网编辑
千家信息网最后更新 2024年10月22日，Mariadb备份本文主要详细说明Mariadb如何使用mysqldump和Xtrabackup备份mysql数据库mysqldump实现如下功能：1、对hellodb数据库进行增删后还原hellod
千家信息网最后更新 2024年10月22日如何使用mysqldump/xtrabackup备份
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 20Server version: 5.5.44-MariaDB-log 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)]> use students;ERROR 1049 (42000): Unknown database 'students'MariaDB [(none)]> use hellodb;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedMariaDB [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 'O5CrWA8BAAAA8QAAAPUAAAABAAQANS41LjQ0LU1hcmlhREItbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAsXMXYw=='/*!*/;# 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;
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.confinnodb_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] ...donextrabackup: 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-backupedfrom_lsn = 0to_lsn = 1680362last_lsn = 1680362compact = 0recover_binlog_info = 0[root@localhost ~]# mysqlWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 27Server 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)]> 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 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)]> 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 = incrementalfrom_lsn = 1680362to_lsn = 1708884last_lsn = 1708884compact = 0recover_binlog_info = 0[root@localhost ~]#
在hellodb数据库中增加haha表；
[root@localhost ~]# mysqlWelcome to the MariaDB monitor. Commands end with ; or \g.Your MariaDB connection id is 32Server 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 1MariaDB [(none)]> use hellodb;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedMariaDB [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 = incrementalfrom_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 = incrementalfrom_lsn = 1680362to_lsn = 1711375last_lsn = 1711375compact = 0recover_binlog_info = 0[root@localhost ~]#
增量还原
将数据还原至新的一台服务器上
数据
备份
数据库
二进制
增量
文件
日志
位置
收据
第一次
信息
功能
同时
命令
差异
时候
服务器
步骤
目录
软件
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
查找软件开发者
万方数据库可以获取哪些信息
近年网络安全
进入网络安全密钥
火山口网络技术
爱看小说软件开发
upf服务器
服务器双电源什么意思
网络安全厂商电话多少
数据库的属性类型
软件开发工作汇报模块的思路
神武副本加成和服务器等级
中资科技互联网
数据库查询每个学生所得学分
服务器当矿机
哪里有服务好的数据库防火墙
大庆采油厂软件开发工资收入
登录linux 使用数据库
软件开发公司的英语
网络安全类etf
我的世界服务器要关闭吗
普洱网络安全宣传活动
云数据中心网络技术规划
服务器振动共振频率是多少
数据库怎么样查询密码
安徽网络安全
韩国评中国科技互联网公司
网络安全手抄报六年级获奖
das可以对哪些数据库进行管理
我的世界数据库mysql
