MySQL增量备份与恢复(二)
MySQL企业备份案例
需求描述:
上海移电通信公司的用户信息数据库为client，用户资费数据表为user_info请为该公司每周进行完全备份
每天为该公司进行增量备份
新增加的用户信息如表所示
身份证 姓名 性别 用户ID号 资费
000000006 孙空悟 男 016 10
000000007 蓝精灵 女 017 91
000000008 姜姜 女 018 23
000000009 关云长 男 019 37
0000000010 罗钢 男 020 36
安装 mysql
此次过程省略,详见博客mysql安装
添加数据库、表，录入数据
[root@mysql5 mysql]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.23-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database client;
Query OK, 1 row affected (0.00 sec)
mysql> use client;
Database changed
mysql> create table user_info(身份证 char(20) not null,姓名 char(20) not null,性别 char(4),用户ID号 char(10) not null,资费 int(10)) default charset=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into user_info values('000000006','孙空悟','男','016','10');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user_info values('000000007','蓝精灵','女','017','91');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user_info values('000000008','姜姜','女','018','23');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user_info;
+-----------+-----------+--------+-------------+--------+
| 身份证 | 姓名 | 性别 | 用户ID号 | 资费 |
+-----------+-----------+--------+-------------+--------+
| 000000006 | 孙空悟 | 男 | 016 | 10 |
| 000000007 | 蓝精灵 | 女 | 017 | 91 |
| 000000008 | 姜姜 | 女 | 018 | 23 |
+-----------+-----------+--------+-------------+--------+
3 rows in set (0.00 sec)
先进行一次完全备份
[root@mysql5 mysql]# mysqldump -uroot -p123456 client user_info >/mysql_bak/client_userinfo-$(date +%F).sql
[root@mysql5 mysql]# mysqldump -uroot -p123456 client user_info >/mysql_bak/client-$(date +%F).sql
[root@mysql5 mysql]# ll /mysql_bak/
总用量 8
-rw-r--r--. 1 root root 2070 5月 20 17:55 client-2020-05-21.sql
-rw-r--r--. 1 root root 2070 5月 20 17:55 client_userinfo-2020-05-21.sql
进行一次日志回滚
生成新的二进制日志
[root@mysql5 mysql_bak]# ls /var/lib/mysql
auto.cnf ca.pem client-cert.pem ib_buffer_pool ib_logfile0 ibtmp1 mysqlbin.000001 mysqlbin.index mysql.sock.lock private_key.pem server-cert.pem sys
ca-key.pem client client-key.pem ibdata1 ib_logfile1 mysql mysql-bin.index mysql.sock performance_schema public_key.pem server-key.pem
[root@mysql5 mysql_bak]# mysqladmin -uroot -p123456 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@mysql5 mysql_bak]# ls /var/lib/mysql
auto.cnf ca.pem client-cert.pem ib_buffer_pool ib_logfile0 ibtmp1 mysqlbin.000001 mysql-bin.index mysql.sock performance_schema public_key.pem server-key.pem
ca-key.pem client client-key.pem ibdata1 ib_logfile1 mysql mysqlbin.000002 mysqlbin.index mysql.sock.lock private_key.pem server-cert.pem sys
继续录入新的数据
[root@mysql5 mysql]# mysql -uroot -p123456
mysql> use client;
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
mysql> insert into user_info values('000000009','关云长','男','019','37');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user_info values('0000000010','罗纲','男','020','36');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user_info;
+------------+-----------+--------+-------------+--------+
| 身份证 | 姓名 | 性别 | 用户ID号 | 资费 |
+------------+-----------+--------+-------------+--------+
| 000000006 | 孙空悟 | 男 | 016 | 10 |
| 000000007 | 蓝精灵 | 女 | 017 | 91 |
| 000000008 | 姜姜 | 女 | 018 | 23 |
| 000000009 | 关云长 | 男 | 019 | 37 |
| 0000000010 | 罗纲 | 男 | 020 | 36 |
+------------+-----------+--------+-------------+--------+
5 rows in set (0.00 sec)
进行增量备份
[root@mysql5 mysql_bak]# mysqladmin -uroot -p123456 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@mysql5 mysql_bak]# ls /var/lib/mysql/
auto.cnf ca.pem client-cert.pem ib_buffer_pool ib_logfile0 ibtmp1 mysqlbin.000001 mysqlbin.000003 mysqlbin.index mysql.sock.lock private_key.pem server-cert.pem sys
ca-key.pem client client-key.pem ibdata1 ib_logfile1 mysql mysqlbin.000002 mysql-bin.index mysql.sock performance_schema public_key.pem server-key.pem
[root@mysql5 mysql_bak]# cd /var/lib/mysql/
[root@mysql5 mysql]# mysqlbinlog --no-defaults mysqlbin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200521 9:59:11 server id 2 end_log_pos 123 CRC32 0x328fe7c7 Start: binlog v 4, server v 5.7.23-log created 200521 9:59:11
BINLOG '
b+DFXg8CAAAAdwAAAHsAAAAAAAQANS43LjIzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AcfnjzI=
'/*!*/;
# at 123
#200521 9:59:11 server id 2 end_log_pos 154 CRC32 0x2d64bf0d Previous-GTIDs
# [empty]
# at 154
#200521 10:03:02 server id 2 end_log_pos 219 CRC32 0xfad18171 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#200521 10:03:02 server id 2 end_log_pos 293 CRC32 0x25a003df Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1590026582/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 293
#200521 10:03:02 server id 2 end_log_pos 359 CRC32 0x53b7d73e Table_map: `client`.`user_info` mapped to number 108
# at 359
#200521 10:03:02 server id 2 end_log_pos 427 CRC32 0x3ed5e2cb Write_rows: table id 108 flags: STMT_END_F
BINLOG '
VuHFXhMCAAAAQgAAAGcBAAAAAGwAAAAAAAEABmNsaWVudAAJdXNlcl9pbmZvAAX+/v7+Awj+PP48
/gz+HhQ+17dT
VuHFXh4CAAAARAAAAKsBAAAAAGwAAAAAAAEAAgAF/+AJMDAwMDAwMDA5CeWFs+S6kemVvwPnlLcD
MDE5JQAAAMvi1T4=
'/*!*/;
# at 427
#200521 10:03:02 server id 2 end_log_pos 458 CRC32 0xb1edc08f Xid = 63
COMMIT/*!*/;
# at 458
#200521 10:03:02 server id 2 end_log_pos 523 CRC32 0xd0bec550 Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 523
#200521 10:03:02 server id 2 end_log_pos 597 CRC32 0x6d7ad1e0 Query thread_id=5 exec_time=0 error_code=0
SET TIMESTAMP=1590026582/*!*/;
BEGIN
/*!*/;
# at 597
#200521 10:03:02 server id 2 end_log_pos 663 CRC32 0x76600dc5 Table_map: `client`.`user_info` mapped to number 108
# at 663
#200521 10:03:02 server id 2 end_log_pos 729 CRC32 0xb13d775b Write_rows: table id 108 flags: STMT_END_F
BINLOG '
VuHFXhMCAAAAQgAAAJcCAAAAAGwAAAAAAAEABmNsaWVudAAJdXNlcl9pbmZvAAX+/v7+Awj+PP48
/gz+HhTFDWB2
VuHFXh4CAAAAQgAAANkCAAAAAGwAAAAAAAEAAgAF/+AKMDAwMDAwMDAxMAbnvZfnurID55S3AzAy
MCQAAABbdz2x
'/*!*/;
# at 729
#200521 10:03:02 server id 2 end_log_pos 760 CRC32 0x33efaf64 Xid = 64
COMMIT/*!*/;
# at 760
#200521 10:03:44 server id 2 end_log_pos 806 CRC32 0x62941919 Rotate to mysqlbin.000003 pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@mysql5 mysql]# cp -p mysqlbin.000002 /mysql_bak/
模拟误操作删除 user_info 表
[root@mysql5 mysql]# mysql -uroot -p123456 -e 'drop table client.user_info;'
[root@mysql5 mysql]# mysql -uroot -p123456 -e 'select * from client.user_info;'
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist
恢复完全备份
[root@mysql5 mysql]# mysql -uroot -p123456 client </mysql_bak/client_userinfo-2020-05-21.sql
[root@mysql5 mysql]# mysql -uroot -p123456 -e 'select * from client.user_info;'
+-----------+-----------+--------+-------------+--------+
| 身份证 | 姓名 | 性别 | 用户ID号 | 资费 |
+-----------+-----------+--------+-------------+--------+
| 000000006 | 孙空悟 | 男 | 016 | 10 |
| 000000007 | 蓝精灵 | 女 | 017 | 91 |
| 000000008 | 姜姜 | 女 | 018 | 23 |
+-----------+-----------+--------+-------------+--------+
恢复增量备份
[root@mysql5 mysql]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 |mysql -uroot -p123456
[root@mysql5 mysql]# mysql -uroot -p123456 -e 'select * from client.user_info;'
+------------+-----------+--------+-------------+--------+
| 身份证 | 姓名 | 性别 | 用户ID号 | 资费 |
+------------+-----------+--------+-------------+--------+
| 000000006 | 孙空悟 | 男 | 016 | 10 |
| 000000007 | 蓝精灵 | 女 | 017 | 91 |
| 000000008 | 姜姜 | 女 | 018 | 23 |
| 000000009 | 关云长 | 男 | 019 | 37 |
| 0000000010 | 罗纲 | 男 | 020 | 36 |
+------------+-----------+--------+-------------+--------
基于时间点的增量备份恢复
(因本次实验insert数据时间间隙短，所以下述摘自它处)
[root@mysql5 mysql]# mysql -uroot -p123456 -e 'drop table client.user_info;'
[root@mysql5 mysql]# mysql -uroot -p123456 -e "select * from client.user_info;"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist
[root@mysql5 mysql]# mysql -uroot -p123456 client < /mysql_bak/client-2020-05-20.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql5 mysql]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002
/*!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
#190423 17:03:02 server id 1 end_log_pos 245 Start: binlog v 4, server v 5.5.60-MariaDB created 190423 17:03:02
BINLOG '
xtS+XA8BAAAA8QAAAPUAAAAAAAQANS41LjYwLU1hcmlhREIAbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAIm1Ljg==
'/*!*/;
# at 245
#190423 17:03:56 server id 1 end_log_pos 315 Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1556010236/*!*/;
SET @@session.pseudo_thread_id=6/*!*/;
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 315
#190423 17:03:56 server id 1 end_log_pos 450 Query thread_id=6 exec_time=0 error_code=0
use `client`/*!*/;
SET TIMESTAMP=1556010236/*!*/;
insert into user_info values('000000009','关云长','男','019','37')
/*!*/;
# at 450
#190423 17:03:56 server id 1 end_log_pos 477 Xid = 55
COMMIT/*!*/;
# at 477
#190423 17:04:01 server id 1 end_log_pos 547 Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1556010241/*!*/;
BEGIN
/*!*/;
# at 547
#190423 17:04:01 server id 1 end_log_pos 680 Query thread_id=6 exec_time=0 error_code=0
SET TIMESTAMP=1556010241/*!*/;
insert into user_info values('0000000010','罗纲','男','020','36')
/*!*/;
# at 680
#190423 17:04:01 server id 1 end_log_pos 707 Xid = 56
COMMIT/*!*/;
# at 707
#190423 18:40:52 server id 1 end_log_pos 750 Rotate to mysql-bin.000004 pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
仅恢复到190423 17:04:01之前的数据，即不恢复‘罗钢’的信息
[root@mysql5 mysql ]# mysqlbinlog --no-defaults --stop-datetime='2019-04-23 17:04:01' /mysql_bak/mysqlbin.000002 |mysql -uroot -p123456
[root@mysql5 mysql ]# mysql -uroot -p123456 -e 'select * from client.user_info;'
+-----------+-----------+--------+-------------+--------+
| 身份证 | 姓名 | 性别 | 用户ID号 | 资费 |
+-----------+-----------+--------+-------------+--------+
| 000000006 | 孙空悟 | 男 | 016 | 10 |
| 000000007 | 蓝精灵 | 女 | 017 | 91 |
| 000000008 | 姜姜 | 女 | 018 | 23 |
| 000000009 | 关云长 | 男 | 019 | 37 |
+-----------+-----------+--------+-------------+--------+
仅恢复“罗纲”的信息，跳过“关云长”的信息恢复
[root@mysql5 mysql]# mysql -uroot -p123456 -e 'drop table client.user_info;'
[root@mysql5 mysql]# mysql -uroot -p123456 client </mysql_bak/client_userinfo-2020-04-23.sql
[root@mysql5 mysql]# mysqlbinlog --no-defaults --start-datetime='2019-04-23 17:04:01' /mysql_bak/mysqlbin.000002 |mysql -uroot -p123456
[root@mysql5 mysql]# mysql -uroot -p123456 -e 'select * from client.user_info;'
+------------+-----------+--------+-------------+--------+
| 身份证 | 姓名 | 性别 | 用户ID号 | 资费 |
+------------+-----------+--------+-------------+--------+
| 000000006 | 孙空悟 | 男 | 016 | 10 |
| 000000007 | 蓝精灵 | 女 | 017 | 91 |
| 000000008 | 姜姜 | 女 | 018 | 23 |
| 0000000010 | 罗纲 | 男 | 020 | 36 |
+------------+-----------+--------+-------------+--------+
基于位置的恢复
[root@mysql5 mysql]# mysql -uroot -p123456 -e 'drop table client.user_info;'
[root@mysql5 mysql]# mysql -uroot -p123456 -e 'select * from client.user_info;'
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist
[root@mysql5 mysql]# mysql -uroot -p123456 client </mysql_bak/client_userinfo-2019-04-23.sql
[root@mysql5 mysql]# mysqlbinlog --no-defaults --stop-position='547' /mysql_bak/mysqlbin.000002 |mysql -uroot -p123456
[root@mysql5 mysql]# mysql -uroot -p123456 -e 'select * from client.user_info;'
+-----------+-----------+--------+-------------+--------+
| 身份证 | 姓名 | 性别 | 用户ID号 | 资费 |
+-----------+-----------+--------+-------------+--------+
| 000000006 | 孙空悟 | 男 | 016 | 10 |
| 000000007 | 蓝精灵 | 女 | 017 | 91 |
| 000000008 | 姜姜 | 女 | 018 | 23 |
| 000000009 | 关云长 | 男 | 019 | 37 |
+-----------+-----------+--------+-------------+--------+
[root@mysql5 mysql]# mysql -uroot -p123456 -e 'drop table client.user_info;'
[root@mysql5 mysql]# mysql -uroot -p123456 client </mysql_bak/client_userinfo-2019-04-23.sql
[root@mysql5 mysql]# mysqlbinlog --no-defaults --start-position='547' /mysql_bak/mysqlbin.000002 |mysql -uroot -p123456
[root@mysql5 mysql]# mysql -uroot -p123456 -e 'select * from client.user_info;'
+------------+-----------+--------+-------------+--------+
| 身份证 | 姓名 | 性别 | 用户ID号 | 资费 |
+------------+-----------+--------+-------------+--------+
| 000000006 | 孙空悟 | 男 | 016 | 10 |
| 000000007 | 蓝精灵 | 女 | 017 | 91 |
| 000000008 | 姜姜 | 女 | 018 | 23 |
| 0000000010 | 罗纲 | 男 | 020 | 36 |
+------------+-----------+--------+-------------+--------+
企业数据库备份脚本
完全备份脚本
#!/bin/bash
# MySQL 数据库完全备份脚本
USER="root"
PASS="123456"
HOST="192.168.200.75"
# 设置备份的数据库(或表)
DBNAME="client"
DIR="/mysql_bak"
CMD="/usr/bin/mysqldump -u$USER -p$PASS -h$HOST"
[ -d $DIR ] || mkdir -p $DIR
cd $DIR
$CMD --databases $DBNAME |gzip > $DIR/$DBNAME-$(date +%Y-%m-%d).sql.gz
find $DIR -mtime +7 -type d|grep client|xargs rm -rf
增量备份脚本
#!/bin/bash
#定义初始值
user='root'
passwd='123.com'
datadir="/usr/local/mysql/data"
backupdir="/opt/database/dailybackup/"
dump_cmd='/usr/local/mysql/bin/mysqlbinlog'
#判断备份文件存放路径是否存在
[ -d ${backupdir} ] || mkdir -p ${backupdir}
#切换至mysql的数据存放路径
cd ${datadir}
#取得所有二进制日志文件总数
sum=`cat bin_log.index | wc -l`
#在备份前刷新二进制日志文件
/usr/local/mysql/bin/mysql -u${user} -p${passwd} -e "flush logs" &> /dev/null
#定义一个初始值为0
nextnum=0
backupsum=0
#对所有二进制日志文件进行遍历
for file in `cat bin_log.index`
do
#获取二进制日志文件名
binlogname=`basename $file`
#如果当前备份的文件数目比总数小（新刷新的二进制文件不需要备份）
if [ $nextnum -lt $sum ];then
[ -f ${backupdir}${binlogname} ] || cp -a ${file} ${backupdir}${binlogname} #如果备份目录下不存在该二进制文件，则进行copy动作
let nextnum++ #对备份成功数量进行增加
fi
done
echo "跳过最新二进制日志文件，备份完成！！！"
版权声明：
本站所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自
爱吃可爱多！
喜欢就支持一下吧
&pics=https://www.youqiqi.cn/upload/2020/06/mysql-logo-dc875bb8e94f4b338b3c26c60514d1de.png&summary=MySQL企业备份案例需求描述:上海移电通信公司的用户信息数据库为client，用户资费数据表为user_info请为该公司每周进行完全备份每天为该公司进行增量备份新增加的用户信息如表所示|身份证|姓名|性别|用户ID号|资费||-------|-------|-------|-------|---){kind=link}
&pics=https://www.youqiqi.cn/upload/2020/06/mysql-logo-dc875bb8e94f4b338b3c26c60514d1de.png&desc=MySQL企业备份案例需求描述:上海移电通信公司的用户信息数据库为client，用户资费数据表为user_info请为该公司每周进行完全备份每天为该公司进行增量备份新增加的用户信息如表所示|身份证|姓名|性别|用户ID号|资费||-------|-------|-------|-------|---){kind=link}
打赏
微信
支付宝
微信
支付宝