鲜花的主人
文章
216
分类
23
评论
21

MySQL增量备份与恢复(二)

| MySQL •
字数总计 18269 | 阅读时长 45分钟 | 阅读量 723

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 许可协议。转载请注明来自 爱吃可爱多！
MySQL 数据库
喜欢就支持一下吧
打赏
微信 微信
支付宝 支付宝