MySQL master-slave replication troubleshooting case One

MySQL master-slave replication troubleshooting case One:

Problem: master/slave cannot sync
Phenomenon:master:mysql> show MASTER status;
Empty Set (0.00 sec)
Slave:mysql> show SLAVE status \g;
Slave_io_running:connecting
Slave_sql_running:yes
Seconds_behind_master:null
last_io_errno:1045
Last_io_error:error Connecting to master '[email protected]: 3306 '
-Retry-time:60 retries:86400
Troubleshooting process:

First, MASTER: [[email protected] ~]# egrep "Log-bin|server"/etc/my.cnf
# The MySQL server
Server-id = 1
#log-bin=mysql-bin//No Open
SLAVE: [[email protected] ~]# egrep "Log-bin|server"/etc/my.cnf
# The MySQL server
Server-id = 3
#log-bin=mysql-bin
Ii.. Turn on the Mastart log-bin log and restart the MySQL database
MASTER: mysql> show Master status;
Mysql> Show master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |107|                  | |
+------------------+----------+--------------+------------------+
SLAVE:Mysql> show slave status \g;
Slave_io_running:connecting
Slave_sql_running:yes
Third, view Master.info and relay-log.info information

SLAVE:[email protected] data]# cat Master.info
18
mysql-bin.000001
348
192.168.254.253
Rep
Oldboy123
3306
60
0

[email protected] data]# cat Relay-log.info
./localhost-relay-bin.000005
4
mysql-bin.000001
348
8

The Pos point on the Log-bin on Master is 107,slave on the Log-bin pos dot 348, which is inconsistent on both sides.
Four. In Master.info, the user name is Rep, and the user registration information is not found on the master side.
Mysql> select Host,user from Mysql.user;
V.. re-registering the user for synchronization
Grant Replication Slave on * * to ' rep ' @ ' 192.168.254.250 ' identified by ' oldboy123 ';
Vi. re-registering change MASTER in slave

Mysql> Change MASTER to
Master_host= ' 192.168.254.253 ',
master_port=3306,
-Master_user= ' rep ',
Master_password= ' oldboy123 ',
Master_log_file= ' mysql-bin.000002 ',
master_log_pos=107;
mysql> start slave;

Seven, .mysql> show slave status \g;
Slave_io_running:yes
Slave_sql_running:yes
Viii.. Test:MASTER: mysql> CREATE Database Mama;
Query OK, 1 row Affected (0.00 sec)
SLAVE: mysql> show databases;
Mama
issue: Because the virtual machine test environment does not make the master database read-only, back up the main library. Direct synchronization to the data from the library, such as consequence loss. Creating a new database is not synchronized until a problem occurs. The main library should be backed up in the production environment first.

This article from the "Technology in hand, the world I have" blog, please be sure to keep this source http://xin521long.blog.51cto.com/11884590/1832143

MySQL master-slave replication troubleshooting case One