repair mysql replication

master :

my.cnf

[mysqld]

log-bin = mysql-bin

server-id=1

binlog-ignore-db=mysql

 

start_master.sh

 

mysql  -u root -e "grant all on *.* to slave@'slave_ip' identified by'123';flush privileges;"

mysql  -u root -e "grant all privileges on *.* to slave@'slave_ip' identified by'123';flush privileges;"

/usr/bin/mysqladmin shutdown

/usr/bin/mysqld_safe --defaults-file=/etc/my.cnf -u mysql &

 

 

slave:

my.cnf

 

[myslqd]

 

log-bin = mysql-bin

log-bin = mysql-bin

relay-log = mysql-relay-bin

server-id=3

master-host=master_ip

master-user=slave

master-password=123

master-port=3306

binlog-ignore-db=mysql

slave-skip-errors=all

read-only           = 1

relay-log-purge     = 1

replicate-do-db    = test

start.sh

 

/usr/bin/mysqladmin shutdown

/usr/bin/mysqld_safe  --defaults-file=/etc/my.cnf --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock -u mysql --relay-log  --relay-log-index &

sleep 10

mysql -uroot mysql -e "SET GLOBAL server_id = 2;start slave;"

#mysql -uroot mysql -e "CHANGE MASTER TO MASTER_HOST="110.173.1.234",  MASTER_USER="slave",MASTER_PASSWORD="123",MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=179,MASTER_CONNECT_RETRY=10;"

 

the problem:

 

mysql> show slave status\G;

*************************** 1. row ***************************

             Slave_IO_State: Connecting to master

                Master_Host: 192.168.1.221

                Master_User: reply

                Master_Port: 3306

              Connect_Retry: 60

            Master_Log_File: n01-bin.000149

        Read_Master_Log_Pos: 248972246

             Relay_Log_File: no02-relay-bin.239231

              Relay_Log_Pos: 114506

      Relay_Master_Log_File: n01-bin.000149

           Slave_IO_Running: No

          Slave_SQL_Running: Yes

            Replicate_Do_DB: mydns

        Replicate_Ignore_DB: 

         Replicate_Do_Table: 

     Replicate_Ignore_Table: 

    Replicate_Wild_Do_Table: 

Replicate_Wild_Ignore_Table: 

                 Last_Errno: 0

                 Last_Error: 

               Skip_Counter: 1

        Exec_Master_Log_Pos: 248972246

            Relay_Log_Space: 114506

            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

1 row in set (0.00 sec)

the way:

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

 

mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.221', MASTER_USER='reply', MASTER_PASSWORD='reply123456', MASTER_LOG_FILE='n01-bin.000149', MASTER_LOG_POS=248972246;

############################################################################

the problem:

 

 

mysql> SHOW SLAVE STATUS \G

*************************** 1. row ***************************

             Slave_IO_State: Waiting for master to send event

                Master_Host: 1.2.3.4

                Master_User: slave_user

                Master_Port: 3306

              Connect_Retry: 60

            Master_Log_File: mysql-bin.001079

        Read_Master_Log_Pos: 269214454

             Relay_Log_File: slave-relay.000130

              Relay_Log_Pos: 100125935

      Relay_Master_Log_File: mysql-bin.001079

           Slave_IO_Running: Yes

          Slave_SQL_Running: No

            Replicate_Do_DB: mydb

        Replicate_Ignore_DB:

         Replicate_Do_Table:

     Replicate_Ignore_Table:

    Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

                 Last_Errno: 1146

                 Last_Error: Error 'Table 'mydb.taggregate_temp_1212047760' doesn't exist' on query. Default database: 'mydb'. 

Query: 'UPDATE thread AS thread,taggregate_temp_1212047760 AS aggregate

        SET thread.views = thread.views + aggregate.views

        WHERE thread.threadid = aggregate.threadid'

               Skip_Counter: 0

        Exec_Master_Log_Pos: 203015142

            Relay_Log_Space: 166325247

            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

1 row in set (0.00 sec）

the way:

 

Repairing The Replication

Just to go sure, we stop the slave:

 

mysql> STOP SLAVE;

 

Fixing the problem is actually quite easy. We tell the slave to simply skip the invalid SQL query:

 

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

 

This tells the slave to skip one query (which is the invalid one that caused the replication to stop). If you'd like to skip two queries, you'd use SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2; instead and so on.

 

That's it already. Now we can start the slave again...

 

mysql> START SLAVE;

 

... and check if replication is working again:

 

mysql> SHOW SLAVE STATUS \G

 

mysql> SHOW SLAVE STATUS \G

*************************** 1. row ***************************

             Slave_IO_State: Waiting for master to send event

                Master_Host: 1.2.3.4

                Master_User: slave_user

                Master_Port: 3306

              Connect_Retry: 60

            Master_Log_File: mysql-bin.001079

        Read_Master_Log_Pos: 447560366

             Relay_Log_File: slave-relay.000130

              Relay_Log_Pos: 225644062

      Relay_Master_Log_File: mysql-bin.001079

           Slave_IO_Running: Yes

          Slave_SQL_Running: Yes

            Replicate_Do_DB: mydb

        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: 447560366

            Relay_Log_Space: 225644062

            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

1 row in set (0.00 sec)

 

mysql>

 

As you see, both Slave_IO_Running and Slave_SQL_Running are set to Yes now.

 

Now leave the MySQL shell...

 

mysql> quit;

 

... and check the log again:

 

grep mysql /var/log/syslog

 

server1:/home/admin# grep mysql /var/log/syslog

May 29 09:56:08 http2 mysqld[1380]: 080529 9:56:08 [ERROR] Slave: Error 'Table 'mydb.taggregate_temp_1212047760' doesn't exist' on query. Default database: 'mydb'. Query: 'UPDATE thread AS thread,taggregate_temp_1212047760 AS aggregate

May 29 09:56:08 http2 mysqld[1380]: ^ISET thread.views = thread.views + aggregate.views

May 29 09:56:08 http2 mysqld[1380]: ^IWHERE thread.threadid = aggregate.threadid', Error_code: 1146

May 29 09:56:08 http2 mysqld[1380]: 080529 9:56: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.001079' position 203015142

May 29 11:42:13 http2 mysqld[1380]: 080529 11:42:13 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.001079' at position 203015142, relay log '/var/lib/mysql/slave-relay.000130' position: 100125935

server1:/home/admin#

 

The last line says that replication has started again, and if you see no errors after that line, everything is ok.

转载于:https://my.oschina.net/YcOk14itqTKB/blog/10719