ssh to the slave
mysql -uroot -pPASSWORD -P3306 -hlocalhost
show slave status\G
If the Slave IO is NOT Running, but SQL is, then simply try to restart the
slave...
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.10.10.45
Master_User: slave
Master_Port: 3306
...
Slave_IO_Running: No
Slave_SQL_Running: Yes
start slave; show slave status\G
Otherwise if it's a replication issue such as this, then you must skip over
the bad SQL queries one at a time till the slave syncs.
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.10.41
Master_User: slave
Master_Port: 3306
...
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: agis_core_2008
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1061
Last_Error: Error 'Duplicate key name 'id_operator'' on
query. ....
The SQL statement will give you an idea of where the master and slave went
askew. If these are recent commands you did you can guess as to how much to
increment the SKIP_COUNTER below, otherwise, you just have to do it one at
a time until they sync again.
mysql> stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show
slave status\G
Repeat the above statements over and over until you see two YES rows.
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
> -----Original Message-----
> From: Norman Khine [mailto:[email protected]]
> Sent: Thursday, August 26, 2010 6:05 AM
> To: [email protected]
> Subject: master-slave replication sync problems.
>
> hello,
> i have a working master-slave replication, the problem i find is that
> if i restart the MASTER there is a difference in the MASTER_LOG_FILE
> and MASTER_LOG_POS on the SLAVE.
>
> what is the correct way to keep the two slaves in sync even after i
> restart the server. the way i do it now is to:
>
> [MASTER]
> mysql> show master status;
>
> +------------------+----------+----------------------+--------
> ------------------+
> | mysql-bin.000010 | 13405429 | upgrade,tracker,bugs |
> mysql,information_schema |
> +------------------+----------+----------------------+--------
> ------------------+
> 1 row in set (0.00 sec)
>
> [SLAVE]
> mysql> stop slave;
> mysql> change master to MASTER_HOST='master.domain.com',
> MASTER_USER='replicator', MASTER_PASSWORD='xxx',
> MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=13301215;
> mysql> start slave;
>
> is this correct or is there a better way to do this?
>
> thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[email protected]
