1、机器A aaa.users
aaa.users 含 id,name,passwd,status,aaa,org,log id是主键
2、机器B bbb.users
bbb.users 含 id,name,passwd,bbb,status,org id是主键
方案：
主从+触发器
1、配置MYSQL主从
主(机器A)：
[mysqld]
server-id = 18
log-bin=mysql-bin
binlog_format=mixed
binlog-do-db=aaa
重启MYSQL
建立用户
mysql>GRANT REPLICATION SLAVE ON *.* to 'tongbu'@'%'
identified by '123456';
mysql>show master
status;
+------------------+----------+--------------+------------------+
|
File |
Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002| 107
|
|
|
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
从（机器B）
建立aaa库，建立aaa.users表
修改my.cnf
[mysqld]
log-bin=mysql-bin
binlog_format=mixed
server-id = 187
binlog-do-db = aaa
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
replicate_wild_do_table= aaa.users
expire_logs_day = 3
保存后重启MYSQL
用 root 用户 命令行连接B服务器 MYSQL
mysql> change
master to master_host=' 15.0.17.18 ',master_port=3306,master_user='tongbu',master_password='123456',master_log_pos=107, master_log_file='mysql-bin.000002', master_connect_retry=5;
成功后继续：
Mysql>start slave;
检查 B 服务器复制状态
mysql>
show slave status\G
2、配置触发器
此步骤在PVD(交通) 的MYSQL 上操作
连接MYSQL
Mysql>use aaa;
Msyql> create trigger tg_users_del after delete on aaa.users
for each row
delete from bbb.users where id not in(select id from aaa.users);
Msyql> create trigger tg_users_ins after insert on aaa.users
for each row
replace into bbb.users(id,name,login_name,password,identity_card,gender,phone,office,email,organ_id,type,level,start_time,end_time,status) select id,name,loginName,password,idCardNumber,gender,phoneNo,officeNo,email,unitId,userTypeId,userLevel,begineTime,endTime,status from aaa.users;
Msyql> create trigger tg_users_update after update on aaa.users
for each row
replace into bbb.users(id,name,login_name,password,identity_card,gender,phone,office,email,organ_id,type,level,start_time,end_time,status) select id,name,loginName,password,idCardNumber,gender,phoneNo,officeNo,email,unitId,userTypeId,userLevel,begineTime,endTime,status from aaa.users;
检查下触发器是否创建正确
Mysql> show triggers;
完成！
