mysql5.7 xtrabackup_Mysql-xtrabackup 与MySQL5.7 binlog 实现数据即时点恢复

Mysql-xtrabackup 与MySQL5.7 binlog  实现数据即时点恢复

一、数据库准备

1. rpm -e mariadb-libs postfix

tar xf mysql-5.7.14-1.el7.x86_64.rpm-bundle.tar

2. 安装

yum install mysql-community-client-5.7.14-1.el7.x86_64.rpm mysql-community-server-5.7.14-1.el7.x86_64.rpm mysql-community-libs-compat-5.7.14-1.el7.x86_64.rpm mysql-community-libs-5.7.14-1.el7.x86_64.rpm mysql-community-common-5.7.14-1.el7.x86_64.rpm

3. 数据库初始化

5.6:mysql_install_db --user=mysql

有可能报错-> 解决方法:yum install perl-Data-Dumper -y

5.7: mysqld --initialize --user=mysql

tail /var/log/mysqld.log

………

2017-12-01T02:18:54.261204Z 1 [Note] A temporary password is generated

for root@localhost: Xrpwbb#hP8yp

4. 启动服务并登录数据库

[root@localhost ~]# mysql -uroot -pXrpwbb#hP8yp

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

报错解决:

[root@localhost ~]# chown -R mysql:mysql /var/lib/mysql

[root@localhost ~]# systemctl restart mysqld

5. 修改数据库密码

[root@localhost ~]# mysqladmin -uroot  -pXrpwbb#hP8yp password  123456

6. vim /etc/my.cnf

添加

.............

server-id=1

log-bin=mysql-bin                   //开启binlog日志

character-set-server=utf8            //设置字符集

validate_password=off                //关闭密码检测插件

7. 准备库表素材

[root@localhost ~]# mysql -uroot -p123456 -e 'create database db1;'

[root@localhost ~]# mysql -uroot -p123456 -e 'create table db1.t1(id int,name varchar(20));'

[root@localhost ~]# mysql -uroot -p123456 -e 'insert into db1.t1 values(1,"a"),(2,"b"),(3,"c");'

[root@localhost ~]# mysql -uroot -p123456 -e 'select * from db1.t1;'

mysql: [Warning] Using a password on the command line interface can be insecure.

+------+------+

| id   | name |

+------+------+

|    1 | a    |

|    2 | b    |

|    3 | c    |

+------+------+

二. 安装percona-xtrabackup

1. 使用innobackup完全备份

创建backup用户:

[root@localhost ~]# mysql -uroot -p123456 -e "grant SELECT,RELOAD,SHOW DATABASES,LOCK TABLES,SUPER,REPLICATION CLIENT,CREATE TABLESPACE,PROCESS on *.* to backup@'localhost' identified by '123456';"

完全备份:

[root@localhost ~]# mkdir -p /bak/full

[root@localhost~]# innobackupex --user=backup --password=123456 --no-timestamp /mysqlbackup/full/full_`date +%F`

查看

[root@localhost full]# ls

full_2017-12-02-11:21:08

[root@localhost full]# cd full_2017-12-02-11\:21\:08/

[root@localhost full_2017-12-02-11:21:08]# ls

backup-my.cnf   ibdata1             sys                     xtrabackup_info

db1             mysql               xtrabackup_binlog_info  xtrabackup_logfile

ib_buffer_pool  performance_schema  xtrabackup_checkpoints

继续插入数据

[root@localhost ~]# mysql -uroot -p123456 -e 'insert into db1.t1 values(4,"d");'

[root@localhost ~]# mysql -uroot -p123456 -e 'insert into db1.t1 values(5,"e");'

误操作删除

[root@localhost ~]# mysql -uroot -p123456 -e 'delete from db1.t1 where id>3;'

继续插入数据

[root@localhost ~]# mysql -uroot -p123456 -e 'insert into db1.t1 values(6,"f"),(7,"g");'

误删除数据库

[root@localhost ~]# mysql -uroot -p123456 -e 'drop database db1;'

2.过程恢复操作

1) 刷新binlog日志

[root@localhost ~]# mysql -uroot -p123456 -e 'flush logs;'

2) 查看binlog日志

方法一

[root@localhost ~]# mysql -uroot -p123456 -e "show master status;"

mysql: [Warning] Using a password on the command line interface can be insecure.

+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000003 |      154 |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

#一般查看前一个日志文件

mysql> show binlog events in 'mysql-bin.000002';

+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                                                                                                                                                                                                           |

+------------------+------+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| mysql-bin.000002 |    4 | Format_desc    |         1 |         123 | Server ver: 5.7.18-log, Binlog ver: 4                                                                                                                                                                                          |

| mysql-bin.000002 |  123 | Previous_gtids |         1 |         154 |                                                                                                                                                                                                                                |

| mysql-bin.000002 |  154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                                                                                                                                                           |

| mysql-bin.000002 |  219 | Query          |         1 |         310 | create database db1

………………………

方法二:使用mysqlbinlog

#注意5.7版本,insert语句已经加密,默认看不到,查看时加上选项[root@localhost ~]# mysqlbinlog --base64-output=DECODE-ROWS –vv  /var/lib/mysql/mysql-bin.000002

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#171202 13:28:13 server id 1  end_log_pos 123 CRC32 0xfbf051da     Start: binlog v 4, server v 5.7.18-log created 170810 20:35:03 at startup

ROLLBACK/*!*/;

# at 123

#171202 13:29:03 server id 1  end_log_pos 154 CRC32 0x7761f86f     Previous-GTIDs

# [empty]

# at 154

#171202 31:29:32 server id 1  end_log_pos 219 CRC32 0xb60eddec     Anonymous_GTID    last_committed=0  sequence_number=1

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 219

#171202 13:29:44 server id 1  end_log_pos 310 CRC32 0xbbe85598     Query  thread_id=5   exec_time=0   error_code=0

SET TIMESTAMP=1502368628/*!*/;

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 utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

create database db1

/*!*/;

# at 310

#171202 13:31:08 server id 1  end_log_pos 375 CRC32 0x3d54173c     Anonymous_GTID    last_committed=1  sequence_number=2

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;

# at 375

#171302 13:32:08 server id 1  end_log_pos 488 CRC32 0x10705617     Query  thread_id=6   exec_time=0   error_code=0

SET TIMESTAMP=1502368628/*!*/;

create table db1.t1(id int,name varchar(20))

…………………………………….

…………………..

3) 根据日志文件提取全备后数据

基于时间点提取插入第4条和第5条数据

[root@localhost ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000002 --start-datetime="2017-12-02 13:47:41" --stop-datetime="2017-12-02 13:47:57" > time.sql

基于位置点提取插入第6条和第7条数据

[root@localhost ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000002 --start-position="2306" --stop-position="2502" -r position.sql

3.恢复全备

应用日志

[root@localhost ~]# innobackupex --user=backup --password=123456 --apply-log /mysqlbackup/full/full_2017-12-02-13\:47\:17

[root@localhost ~]# systemctl stop mysqld

删除数据文件

[root@localhost ~]# rm -rf /var/lib/mysql/*

还原数据

[root@localhost ~]# innobackupex --user=backup --password=123456 --copy-back / mysqlbackup/full/full_2017-12-02-13\:47\:17

授权

[root@localhost ~]# chown -R mysql.mysql /var/lib/mysql

[root@localhost ~]# systemctl restart mysqld

检查全备:

[root@localhost ~]# mysql -uroot -p -e 'select * from db1.t1;'

Enter password:

+------+------+

| id   | name |

+------+------+

|    1 | a    |

|    2 | b    |

|    3 | c    |

+------+------+

4. 增量恢复

[root@localhost ~]# mysql -uroot -p < time.sql

Enter password:

[root@localhost ~]# mysql -uroot -p -e 'select * from db1.t1;'

Enter password:

+------+------+

| id   | name |

+------+------+

|    1 | a    |

|    2 | b    |

|    3 | c    |

|    4 | d    |

|    5 | e    |

+------+------+

[root@localhost ~]# mysql -uroot -p < position.sql

Enter password:

[root@localhost ~]# mysql -uroot -p -e 'select * from db1.t1;'

Enter password:

+------+------+

| id   | name |

+------+------+

|    1 | a    |

|    2 | b    |

|    3 | c    |

|    4 | d    |

|    5 | e    |

|    6 | f    |

|    7 | g    |

+------+------+

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值