现象：5.7数据库集群，客户在导入数据库的过程对系统进行了删除，导致相关功能异常；

分析过程：

• 解析binlog确定是否执行了相关的删除动作，如下图：
• 
• 确认是否还能登录，没有重启数据库，确认还能登录数据库（否则还得在配置文件里加上upgrade=force以及skip-grant-tables等参数）

对非系统库进行，备份：

 /teledb/mysql-install/bin/mysql  --socket=/tmp/mysql_6301.sock   -uroot -p'*********'  -e "show databases;" | grep -Ev  "Database|information_schema|mysql|performance_schema|sys1" |xargs  /teledb/mysql-install/bin/mysqldump --socket=/tmp/mysql_6301.sock  -uroot -p'*********************' --databases    --default-character-set=utf8  --opt  -Q   --skip-lock-tables    --set-gtid-purged=OFF  > /tmp/mysql_dump1.sql

注意：

A、由于库中有sysysyys库，故上述的命令中用了sys1过滤来保存该库;

B、此处没有在-Q 后面加上-R参数，加上此参数会报如下错误，因为-R是对函数和存储过程进行备份：

2、然后对数据目录下的binlog  data   relaylog目录进行备份

cp -rf binlog binlog_bak  
cp -rf data data_bak

cp relaylog relaylog_bak

cp logs logs_old

3、kill原数据库进程，然后进去到数据目录，清空data binlog logs relaylog里面的内容；

 

4、拷贝一份配置文件

cp Default-MySQL-5.7.cnf  Default-MySQL-5.7-ini.cnf

然后注释掉如下参数
#rpl_semi_sync_slave_enabled = 1

#rpl_semi_sync_master_enabled = 1

#rpl_semi_sync_master_wait_point = after_sync

#rpl_semi_sync_master_timeout =1000000000

#rpl_semi_sync_master_wait_no_slave=0

 

5、重新初始化数据库（用Default-MySQL-5.7-ini.cnf）

./bin/mysqld --defaults-file=/teledb/mysql-install/etc/Default-MySQL-5.7-ini.cnf  --initialize-insecure

6、启动数据库

 ./bin/mysqld_safe  --defaults-file=/teledb/mysql-install/etc/Default-MySQL-5.7-ini.cnf   &

7、登录数据库，清除用户名为空的用户

delete from mysql.user where  user='';

8、登录数据库，安装半同步插件

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

9、关闭数据库，然后用原来得配置文件启动数据库

./bin/mysqladmin   --socket=/tmp/mysql_6301.sock   -uroot -p shutdown

./bin/mysqld_safe  --defaults-file=/teledb/mysql-install/etc/Default-MySQL-5.7.cnf   &

 

10、登录数据库，创建相关内置用户

create user RDS_gw@'%' identified by '******';

grant Select,Insert,Update,Delete,Create,Drop,Process,References,Index,Alter,SHOW DATABASES,CREATE TEMPORARY TABLES,LOCK TABLES,Execute,CREATE VIEW,SHOW VIEW,CREATE ROUTINE,ALTER ROUTINE,Event,Trigger,REPLICATION CLIENT,reload on *.* to  'RDS_gw'@'%'  with grant option;

create user RDS_orzdba@'%' identified by '*******';

grant SELECT, PROCESS, REPLICATION SLAVE, REPLICATION CLIENT on *.* to 'RDS_orzdba'@'%'  with grant option;

create user RDS_sla@'%' identified by '*******';

grant replication slave ,replication client  on *.* to 'RDS_sla'@'%'  with grant option;

create user RDS_agent@'%' identified by '*******';

grant all privileges on *.* to 'RDS_agent'@'%'  with grant option;

create user RDS_telemonitor@'%' identified by '*******';

GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE, REPLICATION CLIENT on *.* to RDS_telemonitor@'%';

flush privileges;

11、创建用户用的root@%账户

grant FILE, SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER  on *.* to root@'%' identified by 'XXXXXXXX' with grant option;flush privileges;

12、给root@localhost创建密码

grant all privileges on *.* to 'root'@localhost identified by '******' with grant option; flush privileges;

13、创建心跳表

create database SysDB;

use SysDB;

create table StatusTable(ip varchar(20),port int,ts timestamp,primary key(ip,port))engine=MyISAM; reset master;

create table kernel_version(id int unsigned not null auto_increment primary key, version varchar(20) not null, create_time timestamp not null default current_timestamp on update current_timestamp);reset master;

14、如果是单实例，执行下面的语句

insert into kernel_version(version) values('5.7.25-log');

如果非单实例，执行下面的语句

insert into kernel_version(version) values('5.7.25-log');reset master;

15、导入原表数据

登录数据库，执行下面的语句，source /tmp/mysql_dump1.sql

16、验证数据已恢复

注意：如果数据库是集群，恢复一个库以后其他库可以通过alldump重做的方法来进行恢复；