Mysql数据库增量备份与恢复

标签：备份 Mysql server client user mysql root 数据库

一、MySQL 增量备份概念

使用 mysqldump 进行完全备份，备份的数据中有重复数据，备份时间与恢复时间长。而增量备份就是备份自上一次备份之后增加或改变的文件或内容。

1、增量备份的特点:

没有重复数据，备份量不大，时间短
恢复麻烦:需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复。

MySQL没有提供直接的增量备份办法，可以通过MySQL提供的二进制日志(binary logs)间接实现增量备份。

2、MySQL 二进制日志对备份的意义:

二进制日志保存了所有更新或者可能更新数据库的操作。
二进制日志在启动MySQL 服务器后开始记录，并在文件达到 max_binlog_size 所设置的大小或者接收到 flush logs 命令后重新创建新的日志文件。

[root@mysql-master ~]# vim /etc/my.cnf

53 max_binlog_size=1024000      //二进制日志最大1M

只需定时执行flushlogs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份。

要进行 MySQL 的增量备份，首先要开启二进制日志功能，开启 MySQL 的二进制日志功能。

方法一:

MySQL 的配置文件的[mysqld]项中加入 log-bin=文件存放路径/文件前缀，如log-bin=mysql-bin，然后重启 mysqld 服务。默认此配置存在。

方法二:

使用 mysqld –log-bin=文件存放路径/文件前缀重新启动 mysqld 服务每周选择服务器负载较轻的时间段，或者用户访问较少的时间段进行备份。

二、MySQL 增量恢复

1、应用场景

(1)人为的 SQL 语句破坏了数据库

(2)在进行下一次全备之前发生系统故障导致数据库数据丢失

(3)在主从架构中，主库数据发生了故障

2、增量恢复的方法

(1)一般的恢复:备份的二进制日志内容全部恢复

格式:

mysqlbinlog [--no-defaults] 增量备份文件 | mysql -u 用户名 -p 密码

(2)基于时间点的恢复:便于跳过某个发生错误的时间点实现数据恢复

格式:

从日志开头截止到某个时间点的恢复:

mysqlbinlog [--no-defaults] --stop-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u用户名 -p密码

从某个时间点到日志结尾的恢复:

mysqlbinlog [--no-defaults] --start-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u用户名 -p密码

从某个时间点到某个时间点的恢复:

mysqlbinlog [--no-defaults] --start-datetime=’年-月-日 小时:分钟:秒’ --stop-datetime=’年-月-日 小时:分钟:秒’ 二进制日志 | mysql -u 用户名 -p 密码

(3)基于位置的恢复:可能在同一时间点既有错误的操作也有正确的操作，基于位置进行恢复更加精准

格式:

mysqlbinlog --stop-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码 

mysqlbinlog --start-position=’操作 id’ 二进制日志 |mysql -u 用户名 -p 密码

三、制定企业备份策略的思路

1、确定当前 mysql 是处于哪种表类型下工作的，它们支持事物处理还是非事物的，因为我们需要根据不同的特点来做一些设置。

2、要选择备份的形式是完全备份还是增量备份，它们各有优缺点。

3、为了保证恢复的完整性，我们得开启 binary log 功能，同时 binlog 给恢复工作也带来了很大的灵活性，可以基于时间点或是位置进行恢复。考虑到数据库性能，我们可以将 binlog 文件保存到其他安全的硬盘中。

4、正如最初所提到的，备份操作和应用服务得到同时运行，这样就十分消耗系统资源了，会导致数据库服务性能下降，这就要求我们选择一个合适的时间(比如在应用负担很小的时候)再来进行备份操作。

5、不是备份完就万事大吉，我们还得确认备份是否可用，所以之后的恢复测试是完全有必要的。

根据数据更新频繁，则应该较为频繁的备份
数据重要，则在有适当更新时进行备份
在数据库压力小的时段进行备份，如一周一次完全备份，然后每天进行增量备份
中小公司，全备一般可一天一次
大公司可每周进行一次全备，每天进行一次增量备份
尽量为企业实现主从复制架构

四、MySQL 企业备份案例

需求描述:

北京移电通信公司的用户信息数据库为 client，用户资费数据表为 user_info 请为该公司每周进行完全备份

每天为该公司进行增量备份

新增加的用户信息如表所示

身份证	姓名	性别	用户ID号	资费
000000006	孙空悟	男	016	10
000000007	蓝精灵	女	017	91
000000008	姜姜	女	018	23
000000009	关云长	男	019	37
0000000010	罗钢	男	020	36

安装 mysql(yum 方式)

[root@mysql-server ~]# yum -y install mariadb-server mariadb mariadb-libs

[root@mysql-server ~]# systemctl enable mariadb.service

[root@mysql-server ~]# systemctl start mariadb.service

[root@mysql-server ~]# mysql_secure_installation    //执行mysql初始化


NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password:123123
Re-enter new password:123123
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

[root@mysql-server ~]# cp /usr/share/mysql/my-medium.cnf /etc/my.cnf

[root@mysql-server ~]# systemctl restart mariadb

[root@mysql-server ~]# mysql -uroot -p123123

添加数据库、表，录入数据

MariaDB [(none)]> create database client;

MariaDB [(none)]> use client;

MariaDB [client]> create table user_info(身份证 char(20) not null,姓名 char(20) not null,性别 char(4),用户ID号 char(10) not null,资费 int(10)) default charset=utf8;

MariaDB [client]>  insert into user_info values('000000006','孙空悟','男','016','10');

MariaDB [client]> insert into user_info values('000000007','蓝精灵','女','017','91');

MariaDB [client]> insert into user_info values('000000008','姜姜','女','018','23');

MariaDB [client]> select * from user_info;

+-----------+-----------+--------+-------------+--------+
| 身份证    | 姓名      | 性别   | 用户ID号    | 资费   |
+-----------+-----------+--------+-------------+--------+
| 000000006 | 孙空悟    | 男     | 016         |     10 |
| 000000007 | 蓝精灵    | 女     | 017         |     91 |
| 000000008 | 姜姜      | 女     | 018         |     23 |
+-----------+-----------+--------+-------------+--------+

3 rows in set (0.00 sec)


#如果出现乱码则执行
MariaDB [client]> ALTER database client character set utf8;

MariaDB [client]> ALTER table user_info character set utf8;

先进行一次完全备份

[root@mysql-server ~]# mkdir /mysql_bak

[root@mysql-server ~]# mysqldump -uroot -p123123 client user_info >/mysql_bak/client_userinfo-$(date +%F).sql

[root@mysql-server ~]# mysqldump -uroot -p123123 client >/mysql_bak/client-$(date +%F).sql

[root@mysql-server ~]# ls /mysql_bak/

client-2019-04-23.sql  client_userinfo-2019-04-23.sql

进行一次日志回滚(生成新的二进制日志)

[root@mysql-server ~]# ls /var/lib/mysql/              //源码安装目录为/usr/local/mysql/data/

aria_log.00000001  ibdata1      mysql             mysql-bin.index   mysql-server-slow.log
aria_log_control   ib_logfile0  mysql-bin.000001  mysql-server.err  mysql.sock
client             ib_logfile1  mysql-bin.000002  mysql-server.pid  performance_schema

[root@mysql-server ~]# mysqladmin -uroot -p123123 flush-logs

[root@mysql-server ~]# ls /var/lib/mysql/

aria_log.00000001  ib_logfile0       mysql-bin.000002  mysql-server.pid
aria_log_control   ib_logfile1       mysql-bin.000003  mysql-server-slow.log
client             mysql             mysql-bin.index   mysql.sock
ibdata1            mysql-bin.000001  mysql-server.err  performance_schema

继续录入新的数据

[root@mysql-server ~]# mysql -uroot -p123123

MariaDB [(none)]> use client;

MariaDB [client]> insert into user_info values('000000009','关云长','男','019','37');

MariaDB [client]> insert into user_info values('0000000010','罗纲','男','020','36');

MariaDB [client]> 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@mysql-server ~]# mysqladmin -uroot -p123123 flush-logs

[root@mysql-server ~]# ls /var/lib/mysql/

aria_log.00000001  ib_logfile0       mysql-bin.000002  mysql-server.err       performance_schema
aria_log_control   ib_logfile1       mysql-bin.000003  mysql-server.pid
client             mysql             mysql-bin.000004  mysql-server-slow.log
ibdata1            mysql-bin.000001  mysql-bin.index   mysql.sock

[root@mysql-server mysql]# cd /var/lib/mysql/

[root@mysql-server mysql]# mysqlbinlog --no-defaults mysql-bin.000003

[root@mysql-server mysql]# cp -p mysql-bin.000003 /mysql_bak/

模拟误操作删除 user_info 表

[root@mysql-server mysql]# mysql -uroot -p123123 -e 'drop table client.user_info;'

[root@mysql-server mysql]# mysql -uroot -p123123 -e 'select * from client.user_info;'

ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist

恢复完全备份

[root@mysql-server mysql]# mysql -uroot -p123123 client </mysql_bak/client_userinfo-2019-04-23.sql

[root@mysql-server mysql]# mysql -uroot -p123123 -e 'select * from client.user_info;'

+-----------+-----------+--------+-------------+--------+
| 身份证    | 姓名      | 性别   | 用户ID号    | 资费   |
+-----------+-----------+--------+-------------+--------+
| 000000006 | 孙空悟    | 男     | 016         |     10 |
| 000000007 | 蓝精灵    | 女     | 017         |     91 |
| 000000008 | 姜姜      | 女     | 018         |     23 |
+-----------+-----------+--------+-------------+--------+

恢复增量备份

[root@mysql-server mysql]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000003 |mysql -uroot -p123123

[root@mysql-server mysql]# mysql -uroot -p123123 -e 'select * from client.user_info;'

+------------+-----------+--------+-------------+--------+
| 身份证     | 姓名      | 性别   | 用户ID号    | 资费   |
+------------+-----------+--------+-------------+--------+
| 000000006  | 孙空悟    | 男     | 016         |     10 |
| 000000007  | 蓝精灵    | 女     | 017         |     91 |
| 000000008  | 姜姜      | 女     | 018         |     23 |
| 000000009  | 关云长    | 男     | 019         |     37 |
| 0000000010 | 罗纲      | 男     | 020         |     36 |
+------------+-----------+--------+-------------+--------+

基于时间点的增量备份恢复

[root@mysql-server mysql]# mysql -uroot -p123123 -e 'drop table client.user_info;'

[root@mysql-server mysql]# mysql -uroot -p123123 -e 'select * from client.user_info;'

ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist

[root@mysql-server mysql]# mysql -uroot -p123123 client </mysql_bak/client_userinfo-2019-04-23.sql

[root@mysql-server mysql]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000003



/*!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@mysql-server mysql]# mysqlbinlog --no-defaults --stop-datetime='2019-04-23 17:04:01' /mysql_bak/mysql-bin.000003 |mysql -uroot -p123123

[root@mysql-server mysql]# mysql -uroot -p123123 -e 'select * from client.user_info;'

+-----------+-----------+--------+-------------+--------+
| 身份证    | 姓名      | 性别   | 用户ID号    | 资费   |
+-----------+-----------+--------+-------------+--------+
| 000000006 | 孙空悟    | 男     | 016         |     10 |
| 000000007 | 蓝精灵    | 女     | 017         |     91 |
| 000000008 | 姜姜      | 女     | 018         |     23 |
| 000000009 | 关云长    | 男     | 019         |     37 |
+-----------+-----------+--------+-------------+--------+

仅恢复“罗纲”的信息，跳过“关云长”的信息恢复

[root@mysql-server mysql]# mysql -uroot -p123123 -e 'drop table client.user_info;'

[root@mysql-server mysql]# mysql -uroot -p123123 client </mysql_bak/client_userinfo-2019-04-23.sql

[root@mysql-server mysql]# mysqlbinlog --no-defaults --start-datetime='2019-04-23 17:04:01' /mysql_bak/mysql-bin.000003 |mysql -uroot -p123123

[root@mysql-server mysql]# mysql -uroot -p123123 -e 'select * from client.user_info;'

+------------+-----------+--------+-------------+--------+
| 身份证     | 姓名      | 性别   | 用户ID号    | 资费   |
+------------+-----------+--------+-------------+--------+
| 000000006  | 孙空悟    | 男     | 016         |     10 |
| 000000007  | 蓝精灵    | 女     | 017         |     91 |
| 000000008  | 姜姜      | 女     | 018         |     23 |
| 0000000010 | 罗纲      | 男     | 020         |     36 |
+------------+-----------+--------+-------------+--------+

基于位置的恢复


[root@mysql-server mysql]# mysql -uroot -p123123 -e 'drop table client.user_info;'

[root@mysql-server mysql]# mysql -uroot -p123123 -e 'select * from client.user_info;'

ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist

[root@mysql-server mysql]# mysql -uroot -p123123 client </mysql_bak/client_userinfo-2019-04-23.sql

[root@mysql-server mysql]# mysqlbinlog --no-defaults --stop-position='547' /mysql_bak/mysql-bin.000003 |mysql -uroot -p123123

[root@mysql-server mysql]# mysql -uroot -p123123 -e 'select * from client.user_info;'

+-----------+-----------+--------+-------------+--------+
| 身份证    | 姓名      | 性别   | 用户ID号    | 资费   |
+-----------+-----------+--------+-------------+--------+
| 000000006 | 孙空悟    | 男     | 016         |     10 |
| 000000007 | 蓝精灵    | 女     | 017         |     91 |
| 000000008 | 姜姜      | 女     | 018         |     23 |
| 000000009 | 关云长    | 男     | 019         |     37 |
+-----------+-----------+--------+-------------+--------+

[root@mysql-server mysql]# mysql -uroot -p123123 -e 'drop table client.user_info;'

[root@mysql-server mysql]# mysql -uroot -p123123 client </mysql_bak/client_userinfo-2019-04-23.sql

[root@mysql-server mysql]# mysqlbinlog --no-defaults --start-position='547' /mysql_bak/mysql-bin.000003 |mysql -uroot -p123123

[root@mysql-server mysql]# mysql -uroot -p123123 -e 'select * from client.user_info;'

+------------+-----------+--------+-------------+--------+
| 身份证     | 姓名      | 性别   | 用户ID号    | 资费   |
+------------+-----------+--------+-------------+--------+
| 000000006  | 孙空悟    | 男     | 016         |     10 |
| 000000007  | 蓝精灵    | 女     | 017         |     91 |
| 000000008  | 姜姜      | 女     | 018         |     23 |
| 0000000010 | 罗纲      | 男     | 020         |     36 |
+------------+-----------+--------+-------------+--------+

五、企业数据库备份脚本

[root@mysql ~]# vim /opt/mysql_bak_wanbei.sh    //完全备份脚本

#!/bin/bash
# MySQL 数据库完全备份脚本
# 设置登录变量
MY_USER="root"

MY_PASS="123123"

MY_HOST="localhost"

MY_CONN="-u$MY_USER -p$MY_PASS -h$MY_HOST"

# 设置备份的数据库(或表)

MY_DB="client"

# 定义备份路径、工具、时间、文件名 
BF_DIR="/mysql_bak/wanbei" 

BF_CMD="/usr/bin/mysqldump"

BF_TIME=$(date +%Y%m%d-%H%M) 
NAME="$MY_DB-$BF_TIME"

# 备份为.sql 脚本，然后打包压缩(打包后删除原文件)
[ -d $BF_DIR ] || mkdir -p $BF_DIR

cd $BF_DIR

$BF_CMD $MY_CONN --databases $MY_DB > $NAME.sql 

/bin/tar zcf $NAME.tar.gz $NAME.sql --remove &>/dev/null

[root@mysql ~]# vim /opt/mysql_bak_zengbei.sh
//增量备份脚本

#!/bin/bash
# MySQL 数据库增量备份脚本
# 设置登录变量
MY_USER="root"
MY_PASS="123123"
MY_HOST="localhost"
MY_CONN="-u$MY_USER -p$MY_PASS -h$MY_HOST"
# 定义备份路径、工具、二进制日志前缀、二进制日志存放路径 

BF_TIME="$(date +%Y%m%d)" BF_DIR="/mysql_bak/zengbei/$BF_TIME"

CMD="/usr/bin/mysqladmin"

QZ="mysql-bin"

LOG_DIR="/var/lib/mysql"
# 拷贝二进制日志

[ -d $BF_DIR ] || mkdir -p $BF_DIR

$CMD $MY_CONN flush-logs

/bin/cp -p $(ls $LOG_DIR/$QZ.* |awk -v RS="" '{print $(NF-2)}') $BF_DIR

[root@mysql ~]# chmod +x /opt/mysql_bak_* 

[root@mysql ~]# crontab -e

0 0 * * 1 /opt/mysql_bak_wanbei.sh //每周一 0:00 进行完备

0 0 * * * /opt/mysql_bak_zengbei.sh //每天 0:00 进行增量备份

标签：备份,Mysql,server,client,user,mysql,root,数据库
From： https://blog.51cto.com/u_15947631/6081162