mysql 重启slave,MySQL主从复制配置&一次mysql slave故障的解决过程

MySQL主从复制配置

主服务器节点

重启mysql服务 service mysqld restart

验证是否已经配置成功

show variables like '%server_id%';

能够查询对应配置文件中的server_id 说明已经配置成功

show master status;

能够看到同步的文件，和行数 说明已经配置成功。

从服务器节点

克隆服务器

重启mysql服务 service mysqld restart

验证是否已经配置成功

show variables like '%server_id%';

能够查询对应配置文件中的server_id 说明已经配置成功

从服务器同步主服务器配置

change master to master_host='192.168.150.138',master_user='root',master_password='root',

master_log_file='mysql-bin.000003',master_log_pos=120;

开始同步

start slave

检查从服务器复制功能状态

SHOW SLAVE STATUS

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

解决办法

因为服务器克隆的时候交UUID产生了重复 ，解决办法

Cat  /etc/my.cnf

cd /var/lib/mysql

rm -rf auto.cnf

重启服务器即可

service mysqld restart

一次mysql slave故障的解决过程

mysql> show slave status\G

Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master’s binary log is corrupted (you can check this by running ‘mysqlbinlog’ on the binary log), the slave’s relay log is corrupted (you can check this by running ‘mysqlbinlog’ on the relay log), a network problem, or a bug in the master’s or slave’s MySQL code. If you want to check the master’s binary log or slave’s relay log, you will be able to know their names by issuing ‘SHOW SLAVE STATUS’ on this slave. Error_code: 1594

看样子，应该由于网络原因 slave relay log损坏了

查看 mysql 日志：

170306 12:34:08 [Note] Slave SQL thread initialized, starting replication in log ‘mysql-bin.000033’ at position 77270703, relay log ‘./HK3ASERVER-relay-bin.066281’ position: 10604437

170306 12:34:08 [ERROR] Error in Log_event::read_log_event(): ‘read error’, data_len: 67, event_type: 19

170306 12:34:08 [ERROR] Error reading relay log event: slave SQL thread aborted because of I/O error

170306 12:34:08 [ERROR] Slave SQL: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master’s binary log is corrupted (you can check this by running ‘mysqlbinlog’ on the binary log), the slave’s relay log is corrupted (you can check this by running ‘mysqlbinlog’ on the relay log), a network problem, or a bug in the master’s or slave’s MySQL code. If you want to check the master’s binary log or slave’s relay log, you will be able to know their names by issuing ‘SHOW SLAVE STATUS’ on this slave. Error_code: 1594

170306 12:34:08 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with “SLAVE START”. We stopped at log ‘mysql-bin.000033’ position 77270703

mysql> stop slave;

mysql> change master to Master_Log_File=’mysql-bin.000033’, Master_Log_Pos=77270703;

This operation cannot be performed with a running slave; run STOP SLAVE first

mysql> show slave status\G

发现IO_THREAD线程一直存在停不掉

最后，将mysql/data目录下的 master.info 备走，重新启动 mysql

mysql> show slave status\G

发现 slave 已彻底停掉了

mysql> reset slave;

mysql> change master to Master_Log_File=’mysql-bin.000033’, Master_Log_Pos=77270703;

mysql> start slave;

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

slave 恢复正常