标签：rw 20 5.5 Aug MySQL 6.5 Master mysql MySQL5518
文档课题：rhel 6.5恢复MySQL 5.5.18从节点.
系统：rhel 6.5 64位
数据库：MySQL 5.5.18
数据库安装包：mysql-5.5.18.tar.gz
Xtrabackup安装包：percona-xtrabackup-24-2.4.6-2.el6.x86_64.rpm
系统架构：
应用场景：主库binlog未被从库应用便被binlog保留策略自动删除，主从同步出现异常.以下模拟该异常并探索解决步骤.
1、模拟异常
1.1、异常前数据
--主库数据
root@MySQL5518-Master [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| fruitsDB |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
--从库数据
root@MySQL5518-Slave [(none)]> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.133.122
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 107
Relay_Log_File: MySQL5518-Slave-relay-bin.000015
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 565
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
1.2、模拟异常
1.2.1、从库停止slave进程
root@MySQL5518-Slave [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)
root@MySQL5518-Slave [(none)]> show slave status \G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.133.122
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 107
Relay_Log_File: MySQL5518-Slave-relay-bin.000015
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 565
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
1.2.2、相关信息收集
--从库相关文件
[root@MySQL5518-Slave mysql]# ll data
total 49224
-rw-rw----. 1 mysql mysql 417 Aug 20 09:59 backup-my.cnf
drwx------. 2 mysql mysql 4096 Aug 20 11:08 fruitsDB
-rw-rw----. 1 mysql mysql 18874368 Aug 20 13:39 ibdata1
-rw-r-----. 1 mysql mysql 5242880 Aug 20 13:39 ib_logfile0
-rw-r-----. 1 mysql mysql 5242880 Aug 20 10:50 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Aug 20 10:50 ibtmp1
-rw-rw----. 1 mysql mysql 86 Aug 20 13:42 master.info
drwxrwxr-x. 2 mysql mysql 4096 Aug 20 10:46 mysql
-rw-rw----. 1 mysql mysql 14243 Aug 20 13:42 MySQL5518-Slave.err
-rw-rw----. 1 mysql mysql 6 Aug 20 13:39 MySQL5518-Slave.pid
-rw-rw----. 1 mysql mysql 312 Aug 20 13:41 MySQL5518-Slave-relay-bin.000014
-rw-rw----. 1 mysql mysql 253 Aug 20 13:41 MySQL5518-Slave-relay-bin.000015
-rw-rw----. 1 mysql mysql 70 Aug 20 13:41 MySQL5518-Slave-relay-bin.index
drwxrwxr-x. 2 mysql mysql 4096 Aug 20 10:46 performance_schema
-rw-rw----. 1 mysql mysql 62 Aug 20 13:42 relay-log.info
drwxrwxr-x. 2 mysql mysql 4096 Aug 20 10:46 test
-rw-rw----. 1 mysql mysql 21 Aug 20 09:59 xtrabackup_binlog_info
-rw-rw----. 1 mysql mysql 113 Aug 20 10:50 xtrabackup_checkpoints
-rw-rw----. 1 mysql mysql 581 Aug 20 09:59 xtrabackup_info
-rw-rw----. 1 mysql mysql 8388608 Aug 20 10:50 xtrabackup_logfile
[root@MySQL5518-Slave mysql]# ll binlog
total 28
-rw-rw----. 1 mysql mysql 126 Aug 20 11:05 mysql-bin.000001
-rw-rw----. 1 mysql mysql 126 Aug 20 11:06 mysql-bin.000002
-rw-rw----. 1 mysql mysql 126 Aug 20 13:36 mysql-bin.000003
-rw-rw----. 1 mysql mysql 126 Aug 20 13:36 mysql-bin.000004
-rw-rw----. 1 mysql mysql 126 Aug 20 13:39 mysql-bin.000005
-rw-rw----. 1 mysql mysql 107 Aug 20 13:39 mysql-bin.000006
-rw-rw----. 1 mysql mysql 210 Aug 20 13:39 mysql-bin.index
--主库查看状态
root@MySQL5518-Master [(none)]> show processlist;
+----+------+-----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| 1 | repl | 192.168.133.123:34854 | NULL | Binlog Dump | 116 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 2 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+------+-----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
1.2.3、主库新增数据
root@MySQL5518-Master [(none)]> create database booksDB;
Query OK, 1 row affected (0.00 sec)
root@MySQL5518-Master [(none)]> use booksDB;
Database changed
root@MySQL5518-Master [booksDB]> create table books
-> (
-> bk_id int not null primary key,
-> bk_title varchar(50) not null,
-> copyright year not null
-> );
Query OK, 0 rows affected (0.03 sec)
root@MySQL5518-Master [booksDB]> insert into books values
-> (11078,'Learning MYSQL',2010),
-> (11033,'Study Html',2011),
-> (11035,'How to use php',2003),
-> (11072,'Teach yourself javascript',2005),
-> (11028,'Learning C++',2005),
-> (11069,'MYSQL professional',2009),
-> (11026,'Guide to MySQL 5.7',2008),
-> (11041,'Inside VC++',2011);
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
root@MySQL5518-Master [booksDB]> create table authors
-> (
-> auth_id int not null primary key,
-> auth_name varchar(20),
-> auth_gender char(1)
-> );
Query OK, 0 rows affected (0.00 sec)
root@MySQL5518-Master [booksDB]> insert into authors values
-> (1001,'WriterX','f'),
-> (1002,'WriterA','f'),
-> (1003,'WriterB','m'),
-> (1004,'WriterC','f'),
-> (1011,'WriterD','f'),
-> (1012,'WriterE','m'),
-> (1013,'WriterF','m'),
-> (1014,'WriterG','f'),
-> (1015,'WriterH','f');
Query OK, 9 rows affected (0.00 sec)
Records: 9 Duplicates: 0 Warnings: 0
root@MySQL5518-Master [booksDB]> create table authorbook
-> (
-> auth_id int not null,
-> bk_id int not null,
-> primary key (auth_id,bk_id),
-> foreign key (auth_id) references authors (auth_id),
-> foreign key (bk_id) references books (bk_id)
-> );
Query OK, 0 rows affected (0.00 sec)
root@MySQL5518-Master [booksDB]> insert into authorbook values
-> (1001,11033),(1002,11035),(1003,11072),(1004,11028),
-> (1011,11078),(1012,11026),(1012,11041),(1014,11069);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
root@MySQL5518-Master [booksDB]> show tables;
+-------------------+
| Tables_in_booksDB |
+-------------------+
| authorbook |
| authors |
| books |
+-------------------+
3 rows in set (0.00 sec)
1.2.4、从库确认数据
--从库确认是否有新增数据库booksDB.
root@MySQL5518-Slave [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| fruitsDB |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
说明：如上从库并未新增数据库booksDB.
--从库确认是否产生新的中继日志或binlog.
[root@MySQL5518-Slave mysql]# ll data
total 49224
-rw-rw----. 1 mysql mysql 417 Aug 20 09:59 backup-my.cnf
drwx------. 2 mysql mysql 4096 Aug 20 11:08 fruitsDB
-rw-rw----. 1 mysql mysql 18874368 Aug 20 13:39 ibdata1
-rw-r-----. 1 mysql mysql 5242880 Aug 20 13:39 ib_logfile0
-rw-r-----. 1 mysql mysql 5242880 Aug 20 10:50 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Aug 20 10:50 ibtmp1
-rw-rw----. 1 mysql mysql 86 Aug 20 13:42 master.info
drwxrwxr-x. 2 mysql mysql 4096 Aug 20 10:46 mysql
-rw-rw----. 1 mysql mysql 14243 Aug 20 13:42 MySQL5518-Slave.err
-rw-rw----. 1 mysql mysql 6 Aug 20 13:39 MySQL5518-Slave.pid
-rw-rw----. 1 mysql mysql 312 Aug 20 13:41 MySQL5518-Slave-relay-bin.000014
-rw-rw----. 1 mysql mysql 253 Aug 20 13:41 MySQL5518-Slave-relay-bin.000015
-rw-rw----. 1 mysql mysql 70 Aug 20 13:41 MySQL5518-Slave-relay-bin.index
drwxrwxr-x. 2 mysql mysql 4096 Aug 20 10:46 performance_schema
-rw-rw----. 1 mysql mysql 62 Aug 20 13:42 relay-log.info
drwxrwxr-x. 2 mysql mysql 4096 Aug 20 10:46 test
-rw-rw----. 1 mysql mysql 21 Aug 20 09:59 xtrabackup_binlog_info
-rw-rw----. 1 mysql mysql 113 Aug 20 10:50 xtrabackup_checkpoints
-rw-rw----. 1 mysql mysql 581 Aug 20 09:59 xtrabackup_info
-rw-rw----. 1 mysql mysql 8388608 Aug 20 10:50 xtrabackup_logfile
[root@MySQL5518-Slave mysql]# ll binlog
total 28
-rw-rw----. 1 mysql mysql 126 Aug 20 11:05 mysql-bin.000001
-rw-rw----. 1 mysql mysql 126 Aug 20 11:06 mysql-bin.000002
-rw-rw----. 1 mysql mysql 126 Aug 20 13:36 mysql-bin.000003
-rw-rw----. 1 mysql mysql 126 Aug 20 13:36 mysql-bin.000004
-rw-rw----. 1 mysql mysql 126 Aug 20 13:39 mysql-bin.000005
-rw-rw----. 1 mysql mysql 107 Aug 20 13:39 mysql-bin.000006
-rw-rw----. 1 mysql mysql 210 Aug 20 13:39 mysql-bin.index
说明：如上从库中继日志及binlog文件并未增加.
1.2.5、主库删未应用的binlog
[mysql@MySQL5518-Master ~]$ cd /var/mysql/data
[mysql@MySQL5518-Master data]$ ls -ltr
total 28704
drwx------. 2 mysql root 4096 Aug 19 19:06 test
drwx------. 2 mysql mysql 4096 Aug 19 19:06 performance_schema
drwx------. 2 mysql root 4096 Aug 19 19:06 mysql
-rw-rw----. 1 mysql mysql 5242880 Aug 19 19:08 ib_logfile1
drwx------. 2 mysql mysql 4096 Aug 20 11:08 fruitsDB
-rw-rw----. 1 mysql mysql 18874368 Aug 20 13:40 ibdata1
-rw-rw----. 1 mysql mysql 5242880 Aug 20 13:40 ib_logfile0
-rw-rw----. 1 mysql mysql 6 Aug 20 13:40 MySQL5518-Master.pid
-rw-rw----. 1 mysql root 6938 Aug 20 13:41 MySQL5518-Master.err
drwx------. 2 mysql mysql 4096 Aug 20 13:47 booksDB
[mysql@MySQL5518-Master data]$ cd ../binlog
[mysql@MySQL5518-Master binlog]$ ls -ltr
total 16
-rw-rw----. 1 mysql mysql 1404 Aug 20 13:37 mysql-bin.000001
-rw-rw----. 1 mysql mysql 126 Aug 20 13:40 mysql-bin.000002
-rw-rw----. 1 mysql mysql 105 Aug 20 13:40 mysql-bin.index
-rw-rw----. 1 mysql mysql 2067 Aug 20 13:48 mysql-bin.000003
[mysql@MySQL5518-Master binlog]$ rm -rf mysql-bin.000003
特别注意：此处是手动删除的最新binlog文件mysql-bin.000003，mysql-bin.index依然有该binlog文件的信息，此时需手动删除该信息，然后重启主库.
[mysql@MySQL5518-Master binlog]$ vi mysql-bin.index
说明：删除mysql-bin.000003条目.
/var/mysql/binlog/mysql-bin.000001
/var/mysql/binlog/mysql-bin.000002
/var/mysql/binlog/mysql-bin.000003
[root@MySQL5518-Master ~]# service mysqld restart
若未执行特别注意步骤，后续备份产生的备份集是不对的，恢复从库start slave后，会出现如下告警：
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the last event was read from 'mysql-bin.000003' at 2067, the last byte read was read from 'mysql-bin.000003' at 4.'
1.2.6、从库重新开启slave进程
root@MySQL5518-Slave [(none)]> start slave;
root@MySQL5518-Slave [(none)]> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting to reconnect after a failed master event read
Master_Host: 192.168.133.122
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 107
Relay_Log_File: MySQL5518-Slave-relay-bin.000015
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 565
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
告警日志信息：
230820 14:36:58 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.000003' at position 107
230820 14:36:58 [ERROR] Error reading packet from server: File '/var/mysql/binlog/mysql-bin.000003' not found (Errcode: 2) ( server_errno=29)
230820 14:37:58 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.000003' at position 107
230820 14:37:58 [ERROR] Error reading packet from server: File '/var/mysql/binlog/mysql-bin.000003' not found (Errcode: 2) ( server_errno=29)
230820 14:38:58 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'mysql-bin.000003' at position 107
230820 14:38:58 [ERROR] Error reading packet from server: File '/var/mysql/binlog/mysql-bin.000003' not found (Errcode: 2) ( server_errno=29)
说明：此时发现告警信息每隔1分钟发出”File '/var/mysql/binlog/mysql-bin.000003' not found”报错，此处成功模拟出异常.
2、从库恢复
2.1、主库备份
[mysql@MySQL5518-Master ~]$ /usr/bin/innobackupex --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=root --password=mysql_4U --port=3306 --slave-info --stream=tar --tmpdir=/mysql/tmpfile /mysql/bak/ 2>/mysql/log_bak/backup.log |gzip ->/mysql/bak/192.168.133.122_20230819_3306.tar.gz
2.2、scp备份集
[mysql@MySQL5518-Master bak]$ scp 192.168.133.122_20230819_3306.tar.gz [email protected]:/mysql/bak
2.3、查复制用户信息
--在从节点查出复制用户名和密码.
[mysql@MySQL5518-Slave data]$ cat /var/mysql/data/master.info
18
mysql-bin.000003
107
192.168.133.122
repl
repl@12345
3306
60
0
0
1800.000
0
说明：此处可以查看到复制用户的密码和账户.
2.4、关闭从库mysql进程
[mysql@MySQL5518-Slave data]$ ps -ef|grep mysql
root 60934 1 0 13:39 pts/2 00:00:00 /bin/sh /usr/local/webserver/mysql/bin/mysqld_safe --datadir=/var/mysql/data --pid-file=/var/mysql/data/MySQL5518-Slave.pid
mysql 61317 60934 0 13:39 pts/2 00:00:03 /usr/local/webserver/mysql/bin/mysqld --basedir=/usr/local/webserver/mysql --datadir=/var/mysql/data --plugin-dir=/usr/local/webserver/mysql/lib/plugin --user=mysql --log-error=/var/mysql/data/MySQL5518-Slave.err --pid-file=/var/mysql/data/MySQL5518-Slave.pid --socket=/tmp/mysql.sock --port=3306
root 61348 48196 0 13:40 pts/2 00:00:00 su - mysql
mysql 61349 61348 0 13:40 pts/2 00:00:00 -bash
mysql 61567 61349 0 14:48 pts/2 00:00:00 ps -ef
mysql 61568 61349 0 14:48 pts/2 00:00:00 grep mysql
[mysql@MySQL5518-Slave data]$ logout
[root@MySQL5518-Slave mysql]# kill -9 60934
[root@MySQL5518-Slave mysql]# kill -9 61317
[root@MySQL5518-Slave mysql]# ps -ef|grep mysql
root 61572 48196 0 14:49 pts/2 00:00:00 grep mysql
2.5、检查数据文件有无业务库
[root@MySQL5518-Slave data]# cd /var/mysql/data
[root@MySQL5518-Slave data]# ll
total 49232
-rw-rw----. 1 mysql mysql 417 Aug 20 09:59 backup-my.cnf
drwx------. 2 mysql mysql 4096 Aug 20 11:08 fruitsDB
-rw-rw----. 1 mysql mysql 18874368 Aug 20 13:39 ibdata1
-rw-r-----. 1 mysql mysql 5242880 Aug 20 13:39 ib_logfile0
-rw-r-----. 1 mysql mysql 5242880 Aug 20 10:50 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Aug 20 10:50 ibtmp1
-rw-rw----. 1 mysql mysql 86 Aug 20 13:42 master.info
drwxrwxr-x. 2 mysql mysql 4096 Aug 20 10:46 mysql
-rw-rw----. 1 mysql mysql 18237 Aug 20 14:48 MySQL5518-Slave.err
-rw-rw----. 1 mysql mysql 6 Aug 20 13:39 MySQL5518-Slave.pid
-rw-rw----. 1 mysql mysql 312 Aug 20 13:41 MySQL5518-Slave-relay-bin.000014
-rw-rw----. 1 mysql mysql 253 Aug 20 13:41 MySQL5518-Slave-relay-bin.000015
-rw-rw----. 1 mysql mysql 70 Aug 20 13:41 MySQL5518-Slave-relay-bin.index
drwxrwxr-x. 2 mysql mysql 4096 Aug 20 10:46 performance_schema
-rw-rw----. 1 mysql mysql 62 Aug 20 13:42 relay-log.info
drwxrwxr-x. 2 mysql mysql 4096 Aug 20 10:46 test
-rw-rw----. 1 mysql mysql 21 Aug 20 09:59 xtrabackup_binlog_info
-rw-rw----. 1 mysql mysql 113 Aug 20 10:50 xtrabackup_checkpoints
-rw-rw----. 1 mysql mysql 581 Aug 20 09:59 xtrabackup_info
-rw-rw----. 1 mysql mysql 8388608 Aug 20 10:50 xtrabackup_logfile
2.6、查server-id
--确认主从my.cnf文件中server_id
[mysql@MySQL5518-Master mysql]$ cat /etc/my.cnf | grep server-id
server-id = 1
[mysql@MySQL5518-Slave ~]$ cat /etc/my.cnf | grep server-id
server-id = 2
说明：需保证my.cnf中主从节点的server-id不相同.
2.7、删除数据文件、binlog文件
--查看my.cnf中记录的数据文件以及binlog文件目录
[mysql@MySQL5518-Slave ~]$ cat /etc/my.cnf | grep datadir
datadir=/var/mysql/data
[mysql@MySQL5518-Slave ~]$ cat /etc/my.cnf | grep log_bin
log_bin="/var/mysql/binlog/mysql-bin"
--删除数据文件以及binlog文件
[mysql@MySQL5518-Slave ~]$ cd /var/mysql/data
[mysql@MySQL5518-Slave data]$ rm -rf *
[mysql@MySQL5518-Slave data]$ cd /var/mysql/binlog/
[mysql@MySQL5518-Slave binlog]$ rm -rf *
2.8、从节点准备数据
--解压备份集
[mysql@MySQL5518-Slave binlog]$ cd /mysql/bak
[mysql@MySQL5518-Slave bak]$ ll
total 212
-rw-rw-r--. 1 mysql mysql 216745 Aug 20 14:44 192.168.133.122_20230819_3306.tar.gz
[mysql@MySQL5518-Slave bak]$ tar -zxivf 192.168.133.122_20230819_3306.tar.gz -C /var/mysql/data
[mysql@MySQL5518-Slave bak]$ ll /var/mysql/data
total 18472
-rw-rw----. 1 mysql mysql 417 Aug 20 14:42 backup-my.cnf
drwxrwxr-x. 2 mysql mysql 4096 Aug 20 17:09 booksDB
drwxrwxr-x. 2 mysql mysql 4096 Aug 20 17:09 fruitsDB
-rw-rw----. 1 mysql mysql 18874368 Aug 20 13:40 ibdata1
drwxrwxr-x. 2 mysql mysql 4096 Aug 20 17:09 mysql
drwxrwxr-x. 2 mysql mysql 4096 Aug 20 17:09 performance_schema
drwxrwxr-x. 2 mysql mysql 4096 Aug 20 17:09 test
-rw-rw----. 1 mysql mysql 22 Aug 20 14:42 xtrabackup_binlog_info
-rw-rw----. 1 mysql mysql 113 Aug 20 14:42 xtrabackup_checkpoints
-rw-rw----. 1 mysql mysql 582 Aug 20 14:42 xtrabackup_info
-rw-rw----. 1 mysql mysql 2560 Aug 20 14:42 xtrabackup_logfile
--对全量备份做准备
[mysql@MySQL5518-Slave ~]$ /usr/bin/innobackupex --defaults-file=/etc/my.cnf --apply-log /var/mysql/data
检查方法
——最后一句是不是 completed OK!
--查备份集信息
[mysql@MySQL5518-Slave data]$ pwd
/var/mysql/data
[mysql@MySQL5518-Slave data]$ cat xtrabackup_binlog_info
mysql-bin.000003 107
3、重建主从关系
[root@MySQL5518-Slave mysql]# service mysqld start
Starting MySQL.. [ OK ]
[root@MySQL5518-Slave mysql]# su - mysql
[mysql@MySQL5518-Slave ~]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.18-log Source distribution
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@MySQL5518-Slave [(none)]> reset master;
Query OK, 0 rows affected (0.01 sec)
root@MySQL5518-Slave [(none)]> change master to
master_host='192.168.133.122',
master_user='repl',
master_password='repl@12345',
master_log_file='mysql-bin.000003',
master_log_pos=107;
root@MySQL5518-Slave [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
root@MySQL5518-Slave [(none)]> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.133.122
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 107
Relay_Log_File: MySQL5518-Slave-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 107
Relay_Log_Space: 419
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
1 row in set (0.00 sec)
root@MySQL5518-Slave [(none)]> show variables like '%read_only%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | OFF |
+---------------+-------+
1 row in set (0.00 sec)
root@MySQL5518-Slave [(none)]> set global read_only=on;
Query OK, 0 rows affected (0.00 sec)
root@MySQL5518-Slave [(none)]> show variables like '%read_only%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | ON |
+---------------+-------+
1 row in set (0.00 sec)
4、验证同步关系
4.1、从库查询
root@MySQL5518-Slave [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| booksDB |
| fruitsDB |
| mysql |
| performance_schema |
| test |
+--------------------+
6 rows in set (0.00 sec)
root@MySQL5518-Slave [(none)]> use booksDB;
Database changed
root@MySQL5518-Slave [booksDB]> show tables;
+-------------------+
| Tables_in_booksDB |
+-------------------+
| authorbook |
| authors |
| books |
+-------------------+
3 rows in set (0.00 sec)
4.2、主库删除booksDB
root@MySQL5518-Master [(none)]> drop database booksDB;
Query OK, 3 rows affected (0.00 sec)
root@MySQL5518-Master [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| fruitsDB |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
4.3、从库查询
root@MySQL5518-Slave [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| fruitsDB |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.00 sec)
说明：如上所示，主从复制同步恢复成功.
标签：rw,
20,
5.5,
Aug,
MySQL,
6.5,
Master,
mysql,
MySQL5518
From： https://blog.51cto.com/u_12991611/7163194