MYSQL主主复制

正文

实际上，MySQL并不支持其它一些DBMS支持的多主服务器复制(Multimaster Replication)，这是MySQL的复制功能很大的一个限制(多主服务器的难点在于解决更新冲突)，但是，如果你实在有这种需求，你可以采用MySQL Cluster，以及将Cluster和Replication结合起来，可以建立强大的高性能的数据库平台。但是，可以通过其它一些方式来模拟这种多主服务器的复制。

192.168.30.200  VIP 

192.168.30.77  DB1 master 
192.168.30.78   DB2  slave 

192.168.30.73 slave2 


1.修改mysql配置文件 

#on master 
server-id = 1
log-bin = /data/apps/mysql/binlog/mysql-bin
relay-log = /data/apps/mysql/binlog/mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%


#on slave 
server-id = 2
log-bin = /data/apps/mysql/binlog/mysql-bin
relay-log = /data/apps/mysql/binlog/mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%


2.手动同步数据库
mysql > FLUSH TABLES WITH READ LOCK;

然后在另一个窗口mysqldump 导出数据 


3.创建复制用户并授权

on db1 
mysql> grant replication slave on *.* to 'repl_user'@'192.168.30.78' identified  by 'repl_passwd';

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      335 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+



在db2中把db1设为自己的主服务器
mysql> change master to  master_host = '192.168.30.77', master_user='repl_user', master_password='repl_passwd', master_log_file='mysql-bin.000004', master_log_pos=335;

mysql> start slave;

mysql> show slave status\G;

配置从db2 到db1的mysql 主从复制 和上面的过程完全一样

创建复制用户并授权

mysql> grant replication slave on *.* to 'repl_user'@'192.168.30.77' identified  by 'repl_passwd';

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      335 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

在db1中把db2设为自己的主服务器
on db1

change master to  master_host = '192.168.30.78', master_user='repl_user', master_password='repl_passwd', master_log_file='mysql-bin.000004', master_log_pos=335;

mysql> start slave;

mysql> show slave status\G;

测试

主从同步

1.在远程客户端通过vip 登录测试 
mysql> show variables like "%hostname%";
+---------------+-----------------------+
| Variable_name | Value                 |
+---------------+-----------------------+
| hostname      | localhost.localdomain |
+---------------+-----------------------+

mysql> show variables like "server_id%";
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| server_id      | 1     |
| server_id_bits | 32    |
+----------------+-------+

从上面可见 通过vip 登录，此时登录了db1服务器 



2.数据复制功能测试 

mysql> create database repldb;

mysql> use repldb;
Database changed

mysql> create table repl_table(id int ,email varchar(80), password varchar(40) not null);

mysql> show tables;
+------------------+
| Tables_in_repldb |
+------------------+
| repl_table       |
+------------------+
mysql> insert into repl_table (id,email,password) values(1,"[email protected]","password");


然后登录db2 看是不是有这个数据 和这个表 

mysql> use repldb;
Database changed
mysql> show tables;
+------------------+
| Tables_in_repldb |
+------------------+
| repl_table       |
+------------------+

mysql> select * from repl_table;
+------+-----------+----------+
| id   | email     | password |
+------+-----------+----------+
|    1 | [email protected] | password |
+------+-----------+----------+


或者 直接登录db2 邂逅执行数据库的读，学操作，看数据能不能 迅速同步到db1中 

参考

Linux下的MYSQL主主复制 MySQL主主数据同步