mysql数据库增量恢复多实例实战演示

mysql主从复制原理要点

       1、异步方式同步

         2、逻辑同步模式，多种模式，默认是通过sql语句执行

         3、主库通过记录binlog实现对从库的同步，binlog记录数据库的更新语句

         4、主库1个IO线程，从库由1个IO线程和一个sql线程来完成的

         5、从库关键文件master.info，relay-log，relay-info功能

         6、如果从库还想级联从库，需要打开bin-log和log-slave-updates参数

mysql数据库增量恢复多实例实战演示

[root@opm02-test ~]# mysql -uroot -poldboy-S /data/3306/mysql.sock

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 8

Server version: 5.5.32-log Sourcedistribution

mysql> show variableslike '%character_set%';               ##查看mysql字符集

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

| Variable_name            | Value                                     |

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

| character_set_client     | utf8                                      |

| character_set_connection | utf8                                      |

| character_set_database   | latin1                                    |

| character_set_filesystem | binary                                    |

| character_set_results    | utf8                                      |

| character_set_server     | latin1                                    |

| character_set_system     | utf8                                      |

| character_sets_dir       | /application/mysql-5.5.32/share/charsets/|

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

8 rows in set (0.02 sec)

mysql> create database oldboy;                                                    ##创建一个oldboy库

Query OK, 1 row affected (0.01 sec)

mysql> use oldboy;

Database changed

mysql> create table test( id int(4) not null auto_increment, name char(20) notnull, primary key (id));                                                                                                 ##创建一个test表

Query OK, 0 rows affected (0.11 sec)

mysql> show create table test;                                                      ##查看建表语句    

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

| Table | Create Table                                                                                                                                        |

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

| test | CREATE TABLE `test` (

 `id` int(4) NOT NULL AUTO_INCREMENT,

 `name` char(20) NOT NULL,

 PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

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

1 row in set (0.01 sec)

mysql> insert into test(id,name)values(1,'oldboy');                 ##在test表中插入数据

Query OK, 1 row affected (0.02 sec)

mysql> select * from test;

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

| id | name   |

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

|  1| oldboy |

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

1 row in set (0.01 sec)

mysql> insert into test(id,name)values(2,'oldboy11');            ##在test表中插入数据oldboy11

Query OK, 1 row affected (0.01 sec)

mysql> insert into test(id,name)values(3,'oldboy12');            ##在test表中插入数据oldboy12，以此类推

Query OK, 1 row affected (0.00 sec)

mysql> select * from test;                                                       ##查看表中数据

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

| id | name     |

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

|  1| oldboy   |

|  2| oldboy11 |

|  3| oldboy12 |

|  4| oldboy13 |

|  5| oldboy14 |

|  6| oldboy15 |

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

6 rows in set (0.00 sec)

mysql> quit

Bye

[root@opm02-test ~]# date -s '2016-06-28'                          ##在凌晨12点做备份

2016年 06月 28日 星期二 00:00:00 CST

[root@opm02-test ~]# mysqldump -uroot -poldboy -S /data/3306/mysql.sock-F -B oldboy  >/opt/quanbei.sql          ##做一次全量备份

[root@opm02-test ~]# ll /opt/

总用量 524

-rw-r--r--. 1 root root   2087 6月  28 00:02 quanbei.sql

-rw-r--r--. 1 root root 528847 5月  26 01:40 rep.sql

[root@opm02-test ~]# mysql -uroot -poldboy-S /data/3306/mysql.sock

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 10

Server version: 5.5.32-log Sourcedistribution

mysql> use oldboy

Database changed

mysql> desc test;

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

| Field | Type     | Null | Key | Default | Extra          |

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

| id   | int(4)   | NO   | PRI | NULL    | auto_increment |

| name | char(20) | NO   |     | NULL   |                |

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

2 rows in set (0.01 sec)

mysql> insert into test(name)values('oldboy101');                           ##插入2条语句用来做增量

Query OK, 1 row affected (0.02 sec)

mysql> insert into test(name)values('oldboy102');                           ##插入语句用来做增量

Query OK, 1 row affected (0.00 sec)

mysql> select * from test;

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

| id | name      |

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

|  1| oldboy    |

|  2| oldboy11  |

|  3| oldboy12  |

|  4| oldboy13  |

|  5| oldboy14  |

|  6| oldboy15  |

|  7| oldboy101 |

|  8| oldboy102 |

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

8 rows in set (0.00 sec)

mysql> show databases;                                                                           ##删除之前查看oldboy库

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

| Database           |

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

| information_schema |

| mysql              |

| oldboy             |

| performance_schema |

| test               |

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

5 rows in set (0.00 sec)

mysql> drop database oldboy;                                                                 ##删除oldboy库             

Query OK, 1 row affected (0.03 sec)

mysql> show databases;                                                                           #删除之后查看oldboy库,没有了

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

| Database           |

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

| information_schema |

| mysql              |

| performance_schema |

| test               |

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

4 rows in set (0.00 sec)

mysql> quit;

Bye

[root@opm02-test ~]# ll /opt/                                                              ##查看备份数据目录

总用量 524

-rw-r--r--. 1 root root   2087 6月  28 00:02 quanbei.sql

-rw-r--r--. 1 root root 528847 5月  26 01:40 rep.sql

[root@opm02-test ~]# cd /data/3306/                                                 ##查看数据库的binlog日志

[root@opm02-test 3306]# ll

总用量 32

drwxr-xr-x. 5 mysql mysql 4096 6月  28 00:07 data

-rw-r--r--. 1 mysql mysql 1899 5月  26 00:35 my.cnf

-rwxr-xr-x. 1 mysql mysql 1307 5月  26 00:41 mysql

-rw-rw----. 1 mysql mysql 2290 6月  28 00:02 mysql-bin.000001

-rw-rw----. 1 mysql mysql  656 6月  28 00:07 mysql-bin.000002           ##binlog位置点

-rw-rw----. 1 mysql mysql   56 6月  28 00:02 mysql-bin.index

-rw-rw----. 1 mysql mysql    6 5月  26 00:57 mysqld.pid

-rw-r-----. 1 mysql root  2669 5月  26 01:39 mysql_oldboy3306.err

srwxrwxrwx. 1 mysql mysql    0 5月  26 00:57 mysql.sock

[root@opm02-test 3306]# mysqladmin -uroot -poldboy -S/data/3306/mysql.sock flush-logs                ##不确定binlog位置点的情况下，用mysqladmin刷新binlog日志，新生成binlog之前就是恢复binlog位置点，也根据时间点确定是不是凌晨12操作的

[root@opm02-test 3306]# ll

总用量 36

drwxr-xr-x. 5 mysql mysql 4096 6月  28 00:07 data

-rw-r--r--. 1 mysql mysql 1899 5月  26 00:35 my.cnf

-rwxr-xr-x. 1 mysql mysql 1307 5月  26 00:41 mysql

-rw-rw----. 1 mysql mysql 2290 6月  28 00:02 mysql-bin.000001

-rw-rw----. 1 mysql mysql  699 6月  28 00:17 mysql-bin.000002

-rw-rw----. 1 mysql mysql  107 6月  28 00:17 mysql-bin.000003                 ##新生成的binlog日志

-rw-rw----. 1 mysql mysql   84 6月  28 00:17 mysql-bin.index

-rw-rw----. 1 mysql mysql    6 5月  26 00:57 mysqld.pid

-rw-r-----. 1 mysql root  2669 5月  26 01:39 mysql_oldboy3306.err

srwxrwxrwx. 1 mysql mysql    0 5月  26 00:57 mysql.sock

[root@opm02-test 3306]# cp mysql-bin.000002/opt/

[root@opm02-test 3306]# ll /opt/

总用量 528

-rw-r-----. 1 root root    699 6月  28 00:18 mysql-bin.000002

-rw-r--r--. 1 root root   2087 6月  28 00:02 quanbei.sql

-rw-r--r--. 1 root root 528847 5月  26 01:40 rep.sql

[root@opm02-test 3306]# mysqlbinlog  -d oldboy /opt/mysql-bin.000002##查看binlog日志内容

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

[root@opm02-test3306]# cd /opt/

[root@opm02-testopt]# mysqlbinlog  -d oldboy mysql-bin.000002>bin.sql                ##将binlog放到bin.sql下

[root@opm02-testopt]# vi bin.sql                                                                                   ##vi编辑进去删除drop数据的那条语句，保存退出

[root@opm02-test opt]# mysql -uroot-poldboy -S /data/3306/mysql.sock

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 13

Server version: 5.5.32-log Sourcedistribution

mysql> show variables like 'log_bin';

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

| Variable_name | Value |

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

| log_bin       | ON    |

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

1 row in set (0.00 sec)

mysql> quit

Bye

[root@opm02-test opt]# mysql -uroot-poldboy -S /data/3306/mysql.sock <quanbei.sql                   ##主库恢复全备

[root@opm02-test opt]# mysql -uroot-poldboy -S /data/3306/mysql.sock oldboy <bin.sql                   ##再恢复binlog

[root@opm02-test opt]# mysql -uroot -poldboy-S /data/3306/mysql.sock

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 17

Server version: 5.5.32-log Sourcedistribution

mysql> select * from oldboy test;                                        ##进入数据库查看，此时数据已恢复

ERROR 1046 (3D000): No database selected

mysql> select * from oldboy.test;

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

| id | name      |

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

|  1| oldboy    |

|  2| oldboy11  |

|  3| oldboy12  |

|  4| oldboy13  |

|  5| oldboy14  |

|  6| oldboy15  |

|  7| oldboy101 |

|  8| oldboy102 |

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

8 rows in set (0.00 sec)

mysql>

增量恢复小结：

         1、人为sql造成的误操作

         2、要有全备和增量

         3、恢复时建议对外停止更新

         4、恢复全量，然后把增量日志中有问题的sql语句删除，恢复到数据库

总结数据库增量恢复：

         1、停止一个从库，主库刷新binlog，把mysql-bin-000002恢复成bin.sql       (vi bin.sql)

         去掉删除的语句，/drop搜索删除的语句，在vi里干掉

         2、把全备quanbei.sql及10点前的增量bin.sql恢复到从库

         3、数据丢多少？ 10:10分刷新binlog以后的数据mysql-bin.000003

         4、停止主库，快速把mysql-bin.000003解析为sql，恢复到从库。

         5、切换到从库提供服务

增量恢复的核心思想：

         1、流程制度控制，防止问题发生，如果不做，面临服务和数据丢失问题

         2、通过延迟备份来解决，或者监控、黑名单、白名单机制

         3、业务需求容忍度，选择停库或锁表或容忍丢失部分数据