masterslave模式 mysql_Mysql Master/Slave模式实战

1.master上授权给slavemysql>grant all on *.* to repadmin@’218.6.67.75′ identified by ‘backup’;

mysql>flush privileges;

mysql>use abs;

mysql>create table mysqlslave (status char(8));

mysql>insert into mysqlslave values (‘aaaa’);

2.shutdown master

mysqladmin -u root shutdown

3.拷贝数据文件

直接把数据文件夹打包拷贝到slave去。

4.修改Master的my.cnf文件，在[mysqld]处增加

master /etc/my.cnf:

log-bin

server-id = 1

sql-bin-update-same

binlog-do-db = abs

5.修改Slave的my.cnf文件

server-id       = 2

master-host     = 218.6.67.68

master-user     = backup

master-password = backup

master-port     = 3306

master-connect-retry    = 60

replicate-wild-do-table= ads.%

6.启动slave

7.启动master

8.测试

向其中的测试表里插入一条记录,如

use ads;

insert into mysqlslave values  ((CURDATE() + 0));

再在slave里查看是否有此记录

9.问题

a)ERROR1062  Duplicate entry

mysql> slave stop;

mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

mysql> slave start;

Use the value 1 for any SQL statement that does not use AUTO_INCREMENT or LAST_INSERT_ID(), otherwise you will need to use the value 2. Statements that use AUTO_INCREMENT or LAST_INSERT_ID() take up 2 events in the binary log.

b)调试命令

show processlist;

slave stop;

show slave status;

show master status;

flush master;

flush slave;

reset slave;

reset master;

slave start;

set global sql_slave_skip_counter=1;

参考资料

介绍几个管理Replication的命令：

1. PURGE MASTER LOG

Replication需要生成大量的二进制文件，用以记录Client在Master上的操作，日积月累，这些文件会占据相当大的空间，可以用PURGE MASTER LOG命令来删除它们。

mysql> SHOW MASTER LOGS;

+—————-+

| Log_name |

+—————-+

| binary-log.001 |

| binary-log.002 |

| binary-log.003 |

| binary-log.004 |

+—————-+

4 rows in set (0.02 sec)

mysql> PURGE MASTER LOGS TO ‘binary-log.004’;

之后binary-log.001至binary-log.003三个文件都将被删除。

2. SQL_SLAVE_SKIP_COUNTER

如果Replication在Slave上出现错误而停止，一般都期望Slave能忽略这个错误，继续进行同步，而不是重新启动Slave。

In MySQL 3.23.xx:

mysql> SET SQL_SLAVE_SKIP_COUNTER=1

mysql> SLAVE START

In Versions 4.0.0-4.0.2:

mysql> SET SQL_SLAVE_SKIP_COUNTER=1

mysql> SLAVE START SQL_THREAD

In Version 4.0.3 and beyond:

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1

mysql> SLAVE START SQL_THREAD