表已存在的问题,6双主模式

Centos6.5搭建Mysql5.6双主模式

环境:centos 6.5 mysql 5.6

service DB1: 192.168.235.134

service DB2: 192.168.235.149

1.设置同步账号密码及权限

Service DB1

mysql>grantallprivilegeson*.*to``sync``@192.168.235.149identifiedby``'123'``withgrantoption;``QueryOK,0rowsaffected(0.02sec)``mysql>flushprivileges;``QueryOK,0rowsaffected(0.02sec)

Service DB2

mysql>grantallprivilegeson*.*to``sync``@192.168.235.134identifiedby``'123'``withgrantoption;``QueryOK,0rowsaffected(0.02sec)``mysql>flushprivileges;``QueryOK,0rowsaffected(0.02sec)

测试:

ServiceDB1:mysql-h192.168.235.149-usync-p123````ServiceDB2:mysql-h192.168.235.134-usync-p123

2.修改mysql的配置文件:

以root用户登录ServDB1,修改ServDB1的my.cnf文件

vi /etc/my.cnf

在[mysqld]的配置项中增加如下配置:

[client]

port = 3306

socket = /tmp/mysql.sock

[mysqld]

port = 3306

socket = /tmp/mysql.sock

basedir=/usr/local/mysql

datadir=/data/mydata

skip-external-locking

key_buffer_size = 256M

max_allowed_packet = 1M

table_open_cache = 256

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size= 16M

thread_concurrency = 2

log-bin=mysql-bin

relay-log=mysql-relay-log

binlog_format=mixed

server-id = 1

auto-increment-increment=10

auto-increment-offset=1

以root用户登录ServDB2,修改ServDB2的my.cnf文件

[client]

port = 3306

socket = /tmp/mysql.sock

[mysqld]

port = 3306

socket = /tmp/mysql.sock

basedir=/usr/local/mysql

datadir=/data/mydata

skip-external-locking

key_buffer_size = 256M

max_allowed_packet = 1M

table_open_cache = 256

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size= 16M

thread_concurrency = 2

log-bin=mysql-bin

binlog_format=mixed

server-id = 2

auto-increment-increment=10

auto-increment-offset=2

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 128M

sort_buffer_size = 128M

read_buffer = 2M

write_buffer = 2M

~

分别启动数据库

12[[email protected]]``#servicemysqldstart``StartingMySQL.......

[ OK ]

3.分别查看binlog日志位置:

Server DB1 上:

mysql>showmasterstatus;``+------------------+----------+--------------+------------------+-------------------+``|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|``+------------------+----------+--------------+------------------+-------------------+``|mysql-bin.000004|120||||``+------------------+----------+--------------+------------------+-------------------+``1row``in``set``(0.00sec)````ServerDB2上:``mysql>showmasterstatus;``+------------------+----------+--------------+------------------+-------------------+``|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|``+------------------+----------+--------------+------------------+-------------------+``|mysql-bin.000008|120||||``+------------------+----------+--------------+------------------+-------------------+``1row``in``set``(0.00sec)

4.两台数据库设置连接

Server DB1:

CHANGEMASTERTO``MASTER_HOST=``'192.168.235.149'``,MASTER_USER=``'sync'``,MASTER_PASSWORD=``'123'``,MASTER_LOG_FILE=``'mysql-bin.000004'``,MASTER_LOG_POS=120;

mysql> start slave; 启动从服务器

mysql> SHOW SLAVE STATUS\G;

查看这两项是否为YES,yes为正常。

Slave_IO_Running:Yes

Slave_SQL_Running:Yes

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.235.149

Master_User: sync

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000008

Read_Master_Log_Pos: 219

Relay_Log_File: mysql-relay-log.000002

Relay_Log_Pos: 382

Relay_Master_Log_File: mysql-bin.000008

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

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

Relay_Log_Space: 555

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

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 2

Master_UUID: 44d1d14b-884e-11e5-865a-000c29c70f2e

Master_Info_File: /data/mydata/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for
the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

ERROR:

No query specified

Server DB2:

CHANGE MASTER TO

MASTER_HOST=’192.168.235.134′,MASTER_USER=’sync’,MASTER_PASSWORD=’123′,MASTER_LOG_FILE=’mysql-bin.000008′,MASTER_LOG_POS=120;

mysql> start slave; 启动从服务器

mysql> SHOW SLAVE STATUS\G; 查看状态

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.235.134

Master_User: sync

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 120

Relay_Log_File: test2-relay-bin.000002

Relay_Log_Pos: 283

Relay_Master_Log_File: mysql-bin.000004

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

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

Relay_Log_Space: 456

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

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: 15c66da4-a39b-11e5-b85e-000c297221e8

Master_Info_File: /data/mydata/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for
the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

4.验证双主:

在Server DB1上创建数据库

mysql> create database mjie;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

+——————–+

| Database |

+——————–+

| information_schema |

| mjie |

| mysql |

| performance_schema |

| test |

+——————–+

5 rows in set (0.00 sec)

登录Server DB2 查看是否同步

mysql> show databases;

+——————–+

| Database |

+——————–+

| information_schema |

| mjie |

| mysql |

| performance_schema |

| test |

+——————–+

5 rows in set (0.00 sec)

OK 两台数据库数据一致。成功!!!!!

说明:

server-id必须唯一

binlog-do-db和replicate-do-db表示需要同步的数据库

binlog-ignore-db和replicate-ignore-db表示不需要同步的数据库

环境:centos 6.5
mysql 5.6 service DB1: 192.168.235.134 service DB2: 192.168.235.149
1.设置同步账号密码及权限 Service DB1 mysqlgra…

版本:5.7.9

(1)错误情况

用5.7.9的版本搭建MySQL多源复制测试环境

在slave上已经有数据表test,而master上并没有这张表,现在在master上新建test表,则slave上的复制过程会出错。

开发说复制出现问题,上去看了一下:

MySQL的log记录中相关信息如下:

mysql> show slave status\G
*************************** 1. row
***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 182.2.2.11
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 95459627
               Relay_Log_File: relay-bin-finance.000002
                Relay_Log_Pos: 1479683
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: A.%,B.%,C.%,D.%,E.%,F.%
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1061
                   Last_Error: Error ‘Duplicate key name
‘index_ShippingId_WareId” on query. Default database: ‘B’. Query:
‘alter table `swc_relation` add index `index_ShippingId_WareId`
(`ShippingId`, `WareId`)’
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 95092986
              Relay_Log_Space: 1848783
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1061
               Last_SQL_Error: Error ‘Duplicate key name
‘index_ShippingId_WareId” on query. Default database: ‘B’. Query:
‘alter table `swc_relation` add index `index_ShippingId_WareId`
(`ShippingId`, `WareId`)’
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2213306
                  Master_UUID: 67b146d5-7958-11e5-a908-f8bc123d566c
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 151124 13:33:41
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: (A,B)
                 Channel_Name: A
*************************** 2. row
***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.178.9.69
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 992559812
               Relay_Log_File: relay-bin-jxorder.000012
                Relay_Log_Pos: 152738
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: A.%,B.%,C.%,D.%,E.%,F.%
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 992559812
              Relay_Log_Space: 460320
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 569
                  Master_UUID: d0b6e95c-65ae-11e5-a494-52540064d608
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting
for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: (A,B)
                 Channel_Name: C
*************************** 3. row
***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.178.9.79
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000024
          Read_Master_Log_Pos: 731827753
               Relay_Log_File: relay-bin-jxtms.000055
                Relay_Log_Pos: 3017204
        Relay_Master_Log_File: mysql-bin.000024
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: A.%,B.%,C.%,D.%,E.%,F.%
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 731827753
              Relay_Log_Space: 11134306
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 579
                  Master_UUID: bc039305-66c9-11e5-abc9-5254007ce9e7
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting
for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: (A,B)
                 Channel_Name: D
*************************** 4. row
***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.178.9.71
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 520645176
               Relay_Log_File: relay-bin-jxuser.000012
                Relay_Log_Pos: 4905
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: A.%,B.%,C.%,D.%,E.%,F.%
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 520645176
              Relay_Log_Space: 13313
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 571
                  Master_UUID: 7e03aa04-6263-11e5-8f1a-52540017c59d
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting
for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: (F,B)
                 Channel_Name: E
*************************** 5. row
***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.178.9.75
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 138975499
               Relay_Log_File: relay-bin-zntg.000012
                Relay_Log_Pos: 317
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: A.%,B.%,C.%,D.%,E.%,F.%
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 138975499
              Relay_Log_Space: 686
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 575
                  Master_UUID: 8fb63b55-65bb-11e5-a4e8-5254007637de
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting
for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: (A,B)
                 Channel_Name: ZN
5 rows in set (0.00 sec)

2017-08-15T04:24:30.337730Z 11 [ERROR] Slave SQL for channel ”: Error
‘Table ‘test’ already exists’ on query. Default database: ‘test’. Query:
‘create table test(name2 varchar(100))’, Error_code: 1050

由于存在重复的索引名,导致报错主从失效,通过sql_slave_skip_counter直接进行过滤

2017-08-15T04:24:30.337809Z 11 [Warning] Slave: Table ‘test’ already
exists Error_code: 1050

按照常规方法进行发现报错
mysql> set  global  sql_slave_skip_counter = 1;

2017-08-15T04:24:30.337819Z 11 [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.000007’ position 1289

mysql>stop  slave ;

 

mysql>start slave;
ERROR 3086 (HY000): When sql_slave_skip_counter > 0, it is not
allowed to start more than one SQL thread by using ‘START SLAVE
[SQL_THREAD]’. Value of sql_slave_skip_counter can only be used by
one SQL thread at a time. Please use ‘START SLAVE [SQL_THREAD] FOR
CHANNEL’ to start the SQL thread which will use the value of
sql_slave_skip_counter.

 

mysql> set global sql_slave_skip_counter = 0;

(2)重现出错场景

mysql> start slave;

(a)在slave上提前建立数据表test。

mysql>show slave status for channel ‘A’\G       ## channel = ‘A’  
不能用“=”
             Slave_IO_State: Waiting for master to send event
                  Master_Host: 182.2.2.11
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 95459627
               Relay_Log_File: relay-bin-finance.000002
                Relay_Log_Pos: 1479683
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: A.%,B.%,C.%,D.%,E.%,F.%
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1061
                   Last_Error: Error ‘Duplicate key name
‘index_ShippingId_WareId” on query. Default database: ‘B’. Query:
‘alter table `swc_relation` add index `index_ShippingId_WareId`
(`ShippingId`, `WareId`)’
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 95092986
              Relay_Log_Space: 1848783
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1061
               Last_SQL_Error: Error ‘Duplicate key name
‘index_ShippingId_WareId” on query. Default database: ‘B’. Query:
‘alter table `swc_relation` add index `index_ShippingId_WareId`
(`ShippingId`, `WareId`)’
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2213306
                  Master_UUID: 67b146d5-7958-11e5-a908-f8bc123d566c
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 151124 13:33:41
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: (A,B)
                 Channel_Name: A

mysql> create table test (name varchar(100));

mysql> set  global  sql_slave_skip_counter = 1;

Query OK, 0 rows affected (0.02 sec)

mysql> start  slave for  channel ‘A’;

 

mysql> show slave status for channel ‘A’\G
Slave_IO_State: Waiting for master to send event
                  Master_Host: 182.2.2.11
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 95533635
               Relay_Log_File: relay-bin-finance.000009
                Relay_Log_Pos: 65660
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: A.%,B.%,C.%,D.%,E.%,F.%
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 95533635
              Relay_Log_Space: 109705
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2213306
                  Master_UUID: 67b146d5-7958-11e5-a908-f8bc123d566c
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting
for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: (A,B)
                 Channel_Name: A
1 row in set (0.00 sec)

mysql> show tables;

主从修复

+—————-+

| Tables_in_test |

+—————-+

| data           |

| data2          |

| t1             |

| tablename      |

| test           |

+—————-+

5 rows in set (0.00 sec)

 

(b)在master上建立数据表test。

mysql> create table test(name2 varchar(100));

Query OK, 0 rows affected (0.02 sec)

 

mysql> show tables;

+—————-+

| Tables_in_test |

+—————-+

| data           |

| data2          |

| t1             |

| tablename      |

| test           |

+—————-+

5 rows in set (0.00 sec)

 

mysql> insert into test (name2) values (‘001’), (‘002’) , (‘003’);

Query OK, 3 rows affected (0.01 sec)

Records: 3  Duplicates: 0  Warnings: 0

 

 

(c)查看slave上的数据表。

mysql> show create table test;

+——-+————————————————————————————————-+

| Table | Create Table
                                                                                   |

+——-+————————————————————————————————-+

| test  | CREATE TABLE `test` (

  `name` varchar(100) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+——-+————————————————————————————————-+

1 row in set (0.00 sec)

 

mysql> select * from test;

Empty set (0.00 sec)

 

说明master上的数据表test并没有复制成功,包括数据表结构和数据表的记录集。

 

 

(d)查看slave复制状态。

mysql> show slave status\G

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: mysql101.coe2coe.me

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000007

          Read_Master_Log_Pos: 1465

               Relay_Log_File: mysql103-relay-bin.000016

                Relay_Log_Pos: 502

        Relay_Master_Log_File: mysql-bin.000007

             Slave_IO_Running: Yes

            Slave_SQL_Running: No

              Replicate_Do_DB:

          Replicate_Ignore_DB:

           Replicate_Do_Table:

       Replicate_Ignore_Table:

      Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table:
mysql.%,information_schema.%,performance_schema.%,sys.%

                   Last_Errno: 1050

                   Last_Error: Error ‘Table ‘test’ already exists’ on
query. Default database: ‘test’. Query: ‘create table test(name2
varchar(100))’

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 1289

              Relay_Log_Space: 1568

              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

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 1050

               Last_SQL_Error: Error ‘Table ‘test’ already exists’ on
query. Default database: ‘test’. Query: ‘create table test(name2
varchar(100))’

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 101

                  Master_UUID: a2392929-6dfb-11e7-b294-000c29b1c101

             Master_Info_File: /opt/mysql/data/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State:

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp: 170815 12:24:30

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set:

            Executed_Gtid_Set:

                Auto_Position: 0

         Replicate_Rewrite_DB:

                 Channel_Name:

           Master_TLS_Version:

1 row in set (0.00 sec)

 

可以看到Last_SQL_Error的错误代码1050和错误信息:表已存在。

 

 

(3)解决办法。

对于数据表已经存在导致的复制错误,可以直接在slave上手工删除该数据表,然后重新启动复制。

mysql> drop table test;

Query OK, 0 rows affected (0.01 sec)

 

mysql> show tables;

+—————-+

| Tables_in_test |

+—————-+

| data           |

| data2          |

| t1             |

| tablename      |

+—————-+

4 rows in set (0.00 sec)

 

mysql> show slave status\G

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: mysql101.coe2coe.me

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000007

          Read_Master_Log_Pos: 1732

               Relay_Log_File: mysql103-relay-bin.000016

                Relay_Log_Pos: 502

        Relay_Master_Log_File: mysql-bin.000007

             Slave_IO_Running: Yes

            Slave_SQL_Running: No

              Replicate_Do_DB:

          Replicate_Ignore_DB:

           Replicate_Do_Table:

       Replicate_Ignore_Table:

      Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table:
mysql.%,information_schema.%,performance_schema.%,sys.%

                   Last_Errno: 1050

                   Last_Error: Error ‘Table ‘test’ already exists’ on
query. Default database: ‘test’. Query: ‘create table test(name2
varchar(100))’

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 1289

              Relay_Log_Space: 1835

              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

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 1050

               Last_SQL_Error: Error ‘Table ‘test’ already exists’ on
query. Default database: ‘test’. Query: ‘create table test(name2
varchar(100))’

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 101

                  Master_UUID: a2392929-6dfb-11e7-b294-000c29b1c101

             Master_Info_File: /opt/mysql/data/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State:

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp: 170815 12:24:30

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set:

            Executed_Gtid_Set:

                Auto_Position: 0

         Replicate_Rewrite_DB:

                 Channel_Name:

           Master_TLS_Version:

1 row in set (0.00 sec)

 

在手工删除数据表test后,导致错误的原因已经解除了,但是复制过程是不知道的。重新启动slave上的复制过程,即可复制成功。

 

mysql> stop slave;

Query OK, 0 rows affected (0.01 sec)

 

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show slave status\G

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

               Slave_IO_State: Waiting for master to send event

                  Master_Host: mysql101.coe2coe.me

                  Master_User: repl

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000007

          Read_Master_Log_Pos: 1732

               Relay_Log_File: mysql103-relay-bin.000017

                Relay_Log_Pos: 320

        Relay_Master_Log_File: mysql-bin.000007

             Slave_IO_Running: Yes

            Slave_SQL_Running: Yes

              Replicate_Do_DB:

          Replicate_Ignore_DB:

           Replicate_Do_Table:

       Replicate_Ignore_Table:

      Replicate_Wild_Do_Table:

  Replicate_Wild_Ignore_Table:
mysql.%,information_schema.%,performance_schema.%,sys.%

                   Last_Errno: 0

                   Last_Error:

                 Skip_Counter: 0

          Exec_Master_Log_Pos: 1732

              Relay_Log_Space: 1321

              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

Master_SSL_Verify_Server_Cert: No

                Last_IO_Errno: 0

                Last_IO_Error:

               Last_SQL_Errno: 0

               Last_SQL_Error:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 101

                  Master_UUID: a2392929-6dfb-11e7-b294-000c29b1c101

             Master_Info_File: /opt/mysql/data/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting
for more updates

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set:

            Executed_Gtid_Set:

                Auto_Position: 0

         Replicate_Rewrite_DB:

                 Channel_Name:

           Master_TLS_Version:

1 row in set (0.00 sec)

 

此时的复制状态是正常状态。查看数据表的结构和内容,可以看到跟master上的数据相同。

mysql> show create table test;

+——-+————————————————————————————————–+

| Table | Create Table
                                                                                    |

+——-+————————————————————————————————–+

| test  | CREATE TABLE `test` (

  `name2` varchar(100) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+——-+————————————————————————————————–+

1 row in set (0.00 sec)

 

mysql> select * from test;

+——-+

| name2 |

+——-+

| 001   |

| 002   |

| 003   |

+——-+

3 rows in set (0.00 sec)

 

至此,表已存在导致的复制错误已经被成功排除掉了。