下面是Mysql> show slave status \G;的部分输出信息:
Master_Log_File: mysql-bin.000076
Read_Master_Log_Pos: 4
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000076
Slave_IO_Running: No
Slave_SQL_Running: Yes
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1045
Last_IO_Error: error connecting to master '[email protected]:3306' - retry-time: 60 retries: 86400
mysql错误日志描述:
101230 14:11:21 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='192.168.250.20', master_port='3306', master_log_file='mysql-bin.000076', master_log_pos='4'. New s
tate master_host='192.168.250.20', master_port='3306', master_log_file='mysql-bin.000076', master_log_pos='4'.
101230 14:11:26 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000076' at position 4, relay log './relay-bin.000001' position: 4
101230 14:11:26 [ERROR] Slave I/O: error connecting to master '[email protected]:3306' - retry-time: 60 retries: 86400, Error_code: 1045
Master_Log_File: mysql-bin.000076
Read_Master_Log_Pos: 4
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000076
Slave_IO_Running: No
Slave_SQL_Running: Yes
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1045
Last_IO_Error: error connecting to master '[email protected]:3306' - retry-time: 60 retries: 86400
mysql错误日志描述:
101230 14:11:21 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='192.168.250.20', master_port='3306', master_log_file='mysql-bin.000076', master_log_pos='4'. New s
tate master_host='192.168.250.20', master_port='3306', master_log_file='mysql-bin.000076', master_log_pos='4'.
101230 14:11:26 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000076' at position 4, relay log './relay-bin.000001' position: 4
101230 14:11:26 [ERROR] Slave I/O: error connecting to master '[email protected]:3306' - retry-time: 60 retries: 86400, Error_code: 1045
要是主没问题,则看看他的2进制名和偏移值和从上一样不,不一样则重新change mastger 一下即可.
主服务器重启过,新日志为mysql-bin.000076
,问下如何查看mysql-bin.000076日志的最早一个偏移值呢?
要想找最早的,需要看2进制日志吧;(要想change 这个position 给从的话,确保这个position以后的动作在从上没有执行过.)
再change master;也可以通过从的中继日志来查找偏移量,不过需要计算;o(∩_∩)o
我用了show master status \G上的偏移量pos值,还是报一样的错误的。
+------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000077 | 219755439 | | |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)
2: 从机器上面 change master sql代码:change master to master_user='rel',
master_password=' slavepd1012301151' ,
master_host='192.168.250.20',
master_log_file='mysql-bin.000076',
master_log_pos=0 ;
你应该
change master to master_user='rel',
master_password='slavepd1012301151' ,
master_host='192.168.250.20',
master_log_file='mysql-bin.000077',
master_log_pos=219755439;红色部分为住上的日志名称和Position;
不过直接这样change的话,会出现从少的数据会丢失,最好让锁定下主;如:A:使用下面的步骤:1. 在主服务器上,执行这些语句: mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
记录SHOW语句的输出的日志名和偏移量。这些是复制坐标。2. 在从服务器上,发出下面的语句,其中Master_POS_WAIT()函数的参量是前面步骤中的得到的复制坐标值: mysql> SELECT MASTER_POS_WAIT('log_name', log_offset);
SELECT语句阻塞直到从服务器达到指定的日志文件和偏移量。此时,从服务器与主服务器同步,语句返回。3. 在主服务器上,发出下面的语句允许主服务器重新开始处理更新: mysql> UNLOCK TABLES;
master_log_pos=219755439;
注意这个,你change 错了;
另外你可以在备份主的时候加个参数 mysqldump --master-data 这样可以在从上还原,
我的也是主服务器被重启过,重启后就不同步了。然后Slave_IO_Running: No就一直这样。我也重新做了change master to,不过,位置就是原来Read_Master_Log_Pos: 955980485,没有增加位置,但是没有效果。
求助下各位。谢谢!