1.在原来单实例的基础上修改配置/usr/my.cnf
[MysqLd_multi]
MysqLd=/usr/bin/MysqLd_safe
MysqLadmin=/usr/bin/MysqLadmin
user=root
[MysqLd3306]
port=3306
socket=/var/lib/MysqL/MysqL.sock
pid-file=/var/lib/MysqL/localhost.localdomain.pid
datadir=/var/lib/MysqL/
user=root
character_set_server=utf8
lower_case_table_names=1
max_connections=300
server-id=1
binlog-do-db=test
log-bin=master-bin
log-bin-index=master-bin.index
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
auto-increment-increment = 2
auto-increment-offset = 1
[MysqLd3307]
port=3307
socket=/home/fuyouling/mutl_MysqL/MysqL_3307/MysqL/MysqL.sock
pid-file=/home/fuyouling/mutl_MysqL/MysqL_3307/MysqL/localhost.localdomain.pid
datadir=/home/fuyouling/mutl_MysqL/MysqL_3307/MysqL/
user=root
character_set_server=utf8
lower_case_table_names=1
max_connections=300
server_id=2
log-bin=master-bin
log-bin-index=master-bin.index
replicate-do-db=test
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
auto-increment-increment = 2
auto-increment-offset = 2
2.在3306服务器建立复制账户
MysqLd_multi --defaults-extra-file=/usr/my.cnf start 3306 #开启3306服务
MysqL -uroot -p123456 -S /var/lib/MysqL/MysqL.sock #登录MysqL
MysqL>grant replication slave,replication client on *.* to repl1@'localhost' identified by 'repl1'; #在主服务器上建立复制用户
MysqL>flush privileges; #刷新权限
3.在3307服务器上建立复制账户
MysqLd_multi --defaults-extra-file=/usr/my.cnf start 3307 #开启3307服务
MysqL -uroot -p654321 -S /home/fuyouling/mutl_MysqL/MysqL_3307/MysqL/MysqL.sock #登录MysqL
MysqL>grant replication slave,replication client on *.* to repl2@'localhost' identified by 'repl2'; #在主服务器上建立复制用户
MysqL>flush privileges; #刷新权限
4.在3306服务器上建立slave 连接master的信息
Change Master to Master_host='127.0.0.1',Master_port=3307,Master_user='repl2',Master_Password='repl2';
5.在3307从服务器上建立slave 连接master的信息
Change Master to Master_host='127.0.0.1',Master_port=3306,Master_user='repl1',Master_Password='repl1';
6.在3306上检查主从信息：
MysqL> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_Do_DB | binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000003 | 120 | test | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
MysqL> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: repl2
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 120
Relay_Log_File: slave-relay-bin.000008
Relay_Log_Pos: 284
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: Yes
Slave_sql_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 120
Relay_Log_Space: 621
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_sql_Errno: 0
Last_sql_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: 04793bcc-3e99-11e6-ab0c-000c29569f32
Master_Info_File: /var/lib/MysqL/master.info
sql_Delay: 0
sql_Remaining_Delay: NULL
Slave_sql_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_sql_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
7.在3307上检查主从信息
MysqL> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_Do_DB | binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000003 | 120 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
MysqL> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 127.0.0.1
Master_User: repl1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000003
Read_Master_Log_Pos: 120
Relay_Log_File: slave-relay-bin.000007
Relay_Log_Pos: 284
Relay_Master_Log_File: master-bin.000003
Slave_IO_Running: Yes
Slave_sql_Running: Yes
Replicate_Do_DB: test
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 120
Relay_Log_Space: 621
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_sql_Errno: 0
Last_sql_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: be707b89-20cf-11e6-a8d0-000c29569f32
Master_Info_File: /home/fuyouling/mutl_MysqL/MysqL_3307/MysqL/master.info
sql_Delay: 0
sql_Remaining_Delay: NULL
Slave_sql_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_sql_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
8.测试
(1)
在3306上新建表test_table4
MysqL> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MysqL> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test_table1 |
| test_table2 |
| test_table3 |
+----------------+
3 rows in set (0.00 sec)
MysqL> create table test_table4(id int,str varchar(100));
Query OK,0 rows affected (0.03 sec)
登录3307上查看
MysqL> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MysqL> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test_table1 |
| test_table2 |
| test_table3 |
| test_table4 |
+----------------+
4 rows in set (0.00 sec)
(2)
在3307上创建新表test_table5
MysqL> create table test_table5(id int,0 rows affected (0.05 sec)
在3306上查看
MysqL> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test_table1 |
| test_table2 |
| test_table3 |
| test_table4 |
| test_table5 |
+----------------+
5 rows in set (0.00 sec)
备注：这个其实是主主复制下的主动-主动模式。