mysql数据库增量恢复多实例实战演示_程序开发

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

admin

2023-05-17 09:22:38

0次

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;

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

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

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

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

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 ##主库恢复全备

[root@opm02-test opt]# mysql -uroot-poldboy -S /data/3306/mysql.sock oldboy ##再恢复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、业务需求容忍度，选择停库或锁表或容忍丢失部分数据

上一篇：Zabbix 3.2.6通过ODBC监控MySQL&Oracle

下一篇：修复mysql slave复制1032错误

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

相关内容

热门资讯