300字范文 > Mysql数据库增量备份与恢复

Mysql数据库增量备份与恢复

时间：2024-07-18 18:37:34

相关推荐

Mysql数据库增量备份与恢复

一、MySQL 增量备份概念

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

1、增量备份的特点:

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

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

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

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

[root@mysql-master ~]# vim /etc/f53 max_binlog_size=1024000//二进制日志最大1M

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

要进行 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 请为该公司每周进行完全备份
每天为该公司进行增量备份
新增加的用户信息如表所示

安装 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 MariaDBSERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!In order to log into MariaDB to secure it, we'll need the currentpassword for the root user. If you've just installed MariaDB, andyou 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 MariaDBroot user without the proper authorisation.Set root password? [Y/n] yNew password:123123Re-enter new password:123123Password updated successfully!Reloading privilege tables..... Success!By default, a MariaDB installation has an anonymous user, allowing anyoneto log into MariaDB without having to have a user account created forthem. This is intended only for testing, and to make the installationgo a bit smoother. You should remove them before moving into aproduction environment.Remove anonymous users? [Y/n] y... Success!Normally, root should only be allowed to connect from 'localhost'. Thisensures 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 canaccess. This is also intended only for testing, and should be removedbefore 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 farwill 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 MariaDBinstallation should now be secure.Thanks for using MariaDB![root@mysql-server ~]# cp /usr/share/mysql/my-f /etc/f[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--04-23.sql client_userinfo--04-23.sql

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

[root@mysql-server ~]# ls /var/lib/mysql/aria_log.00000001 ibdata1mysql mysql-bin.index mysql-server-slow.logaria_log_control ib_logfile0 mysql-bin.000001 mysql-server.err mysql.sockclient 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.pidaria_log_control ib_logfile1 mysql-bin.000003 mysql-server-slow.logclient mysql mysql-bin.index mysql.sockibdata1 mysql-bin.000001 mysql-server.err performance_schema

继续录入新的数据

[root@mysql-server ~]# mysql -uroot -p123123MariaDB [(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_schemaaria_log_control ib_logfile1 mysql-bin.000003 mysql-server.pidclient mysql mysql-bin.000004 mysql-server-slow.logibdata1 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--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--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:02BINLOG 'xtS+XA8BAAAA8QAAAPUAAAAAAAQANS41LjYwLU1hcmlhREIAbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAIm1Ljg=='/*!*/;# at 245#190423 17:03:56 server id 1 end_log_pos 315 Querythread_id=6exec_time=0error_code=0SET 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 Querythread_id=6exec_time=0error_code=0use `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 = 55COMMIT/*!*/;# at 477#190423 17:04:01 server id 1 end_log_pos 547 Querythread_id=6exec_time=0error_code=0SET TIMESTAMP=1556010241/*!*/;BEGIN/*!*/;# at 547#190423 17:04:01 server id 1 end_log_pos 680 Querythread_id=6exec_time=0error_code=0SET TIMESTAMP=1556010241/*!*/;insert into user_info values('0000000010','罗纲','男','020','36')/*!*/;# at 680#190423 17:04:01 server id 1 end_log_pos 707 Xid = 56COMMIT/*!*/;# at 707#190423 18:40:52 server id 1 end_log_pos 750 Rotate to mysql-bin.000004 pos: 4DELIMITER ;# End of log fileROLLBACK /* 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='-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--04-23.sql[root@mysql-server mysql]# mysqlbinlog --no-defaults --start-datetime='-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 ~]# 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_DIRcd $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 -e0 0 * * 1 /opt/mysql_bak_wanbei.sh //每周一 0:00 进行完备0 0 * * * /opt/mysql_bak_zengbei.sh //每天 0:00 进行增量备份

本内容不代表本网观点和政治立场，如有侵犯你的权益请联系我们处理。

网友评论

网友评论仅供其表达个人看法，并不表明网站立场。