mysql主从表配置文件_mysql主从复制配置

1.在Master上：

mysql> show master

status;+------------------+----------+--------------+------------------+|

File            | Position | Binlog_Do_DB

| Binlog_Ignore_DB |+------------------+----------+--------------+------------------+|

mysql-bin.000005 |      189 | clear_iptv_ht      |                  |+------------------+----------+--------------+------------------+1

row in set (0.00 sec)mysql> show slave

status\G*************************** 1. row

***************************Slave_IO_State: Waiting for

master to send eventMaster_Host: 192.168.18.89Master_User: backupMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000005Read_Master_Log_Pos: 207Relay_Log_File:

mysqld-relay-bin.000002Relay_Log_Pos: 344Relay_Master_Log_File: mysql-bin.000005Slave_IO_Running: Yes           Slave_SQL_Running: Yes

Replicate_Do_DB: clear_iptv_htReplicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 207Relay_Log_Space: 344Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 01

row in set (0.00 sec)2.在Slave上：

mysql>

show master status;+------------------+----------+--------------+------------------+|

File            | Position | Binlog_Do_DB

| Binlog_Ignore_DB |+------------------+----------+--------------+------------------+|

mysql-bin.000005 |      207 | clear_iptv_ht     |                  |+------------------+----------+--------------+------------------+1

row in set (0.00 sec)mysql> show slave

status\G*************************** 1. row

***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.18.88Master_User: backupMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000005Read_Master_Log_Pos: 189Relay_Log_File:

mysqld-relay-bin.000002Relay_Log_Pos: 326Relay_Master_Log_File: mysql-bin.000005Slave_IO_Running: Yes          Slave_SQL_Running: Yes

Replicate_Do_DB: clear_iptv_htReplicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 189Relay_Log_Space: 326Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 01

row in set (0.00 sec)在slave服务器上执行show slave status\G;

Waiting for master to send eventSlave_IO_Running: Yes

Slave_SQL_Running:

Yes

如以上二行同时为Yes说明配置成功

注：①主从服务器都添加(slave-skip-errors=all)这个参数是为了跳过所以错误。

为什么要跳过呢？

在主从复制中，难免会遇到一些sql语句错误的问题。这个时候我们需要手动来重新设置中继日志的起始点了，有些麻烦。所以直接跳过错误。

Slave_IO_Running: Yes

Slave_SQL_Running: no

如果有一行为no了，说明主从就不同步了。

②参数如下：

#slave-skip-errors=all             #跳过所有错误

#slave-skip-errors=1062,1053,1146  #跳过指定error no类型的错误

具体的错误代码表现如下：

1007：数据库已存在，创建数据库失败

1008：数据库不存在，删除数据库失败

1050：数据表已存在，创建数据表失败

1050：数据表不存在，删除数据表失败

1054：字段不存在，或程序文件跟数据库有冲突

1060：字段重复，导致无法插入

1061：重复键名

1068：定义了多个主键

1094：位置线程ID1146：数据表缺失，请恢复数据库

3.验证是否配置正确