(一)

登录test-mysql-1


[mysqld_multi]

mysqld     = /usr/bin/mysqld_safe

mysqladmin = /usr/bin/mysqladmin

user       = multi_admin

password   = my_password

[mysqld3306]

datadir=/data/3306/

socket=/data/3306/mysql.sock

log-error=/data/3306/mysqld.log

pid-file=/data/3306/mysqld.pid

server-id = 1

log-bin=mysql-bin

replicate-ignore-db=mysql  

replicate-do-db=test

slave-skip-errors=all

auto_increment_offset = 1

auto_increment_increment = 2 

mysqld_multi stop 3306

mysqld_multi start 3306


grant replication slave on *.* to 'rep'@192.168.131.130 identified by 'chenshifei';

flush table with read lock;

show master status;

+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 |      336 |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+



登录test-mysql-2


change master to master_host='192.168.131.129', master_user='rep', master_password='chenshifei', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=336;

start slave;


登录test-mysql-1

unlock tables;


（二）

登录test-mysql-2



[mysqld_multi]

mysqld     = /usr/bin/mysqld_safe

mysqladmin = /usr/bin/mysqladmin

user       = multi_admin

password   = my_password

[mysqld3306]

datadir=/data/3306/

socket=/data/3306/mysql.sock

log-error=/data/3306/mysqld.log

pid-file=/data/3306/mysqld.pid

server-id = 2

log-bin=mysql-bin

replicate-ignore-db=mysql

replicate-do-db=test

slave-skip-errors=all

auto_increment_offset = 2

auto_increment_increment = 2

mysqld_multi stop 3306

mysqld_multi start 3306

grant replication slave on *.* to 'rep'@192.168.131.129 identified by 'chenshifei';

flush table with read lock;

show master status;

+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 |      336 |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+


登录test-mysql-1



change master to master_host='192.168.131.130', master_user='rep', master_password='chenshifei', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos=336;

start slave;


登录test-mysql-2

unlock tables;



（测试）


create table test (id int(4) not null primary key , name char(4) ) engine=myisam default charset=utf8;

insert into test values ('1','chen');


alter table test modify id int(4) not null auto_increment;


在test-mysql-1上测试插入

use chenshifei

insert into test (name) values ('che1');

select * from test;



在test-mysql-2上测试插入

use chenshifei

insert into test (name) values ('che1');

select * from test;



结论，插入后各自的主键自增不同,表的主键要设置为自增，配置文件要设置自增配置，主键要为数字id