mysql-01 | 192.168.1.153 |
mysql-02 | 192.168.1.154 |
os | rhel 7.2 |
1、mysql-01 修改配置文件
# vi /etc/my.cnf
[mysqld] server-id=153 log-bin=mysqlmaster-bin auto-increment-increment=2 # 该值为整个结构中服务器的总数 auto-increment-offset=1 # 避免主键冲突,需要设置不同 |
# systemctl restart mysqld #重启服务
2、mysql-02 修改配置文件
# vi /etc/my.cnf
[mysqld] server-id=154 log-bin=mysqlmaster-bin auto-increment-increment=2 auto-increment-offset=2 |
# systemctl restart mysqld
3、mysql-01 新建授权账户 mysync1
mysql> GRANT REPLICATION SLAVE ON *.* TO 'mysync1'@'192.168.1.%' IDENTIFIED BY 'Ma991218##';
mysql> show master status; # 查看状态,结果在后面有用
+------------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------------+----------+--------------+------------------+-------------------+ | mysqlmaster-bin.000001 | 755 | | | | +------------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) |
4、mysql-02 新建授权账户 mysync2
mysql> GRANT REPLICATION SLAVE ON *.* TO 'mysync2'@'192.168.1.%' IDENTIFIED BY 'Ma991218##';
mysql> show master status;
+------------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------------+----------+--------------+------------------+-------------------+ | mysqlmaster-bin.000001 | 455 | | | | +------------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) |
5、配置 mysql-01
mysql> change master to master_host='192.168.1.154',master_user='mysync2',master_password='Ma991218##',master_log_file='mysqlmaster-bin.000001',master_log_pos=455;
mysql> start slave; # 开启复制功能
mysql> show slave status\G # # 查看状态
*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.154 Master_User: mysync2 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysqlmaster-bin.000001 Read_Master_Log_Pos: 455 Relay_Log_File: mysql-01-relay-bin.000002 Relay_Log_Pos: 326 Relay_Master_Log_File: mysqlmaster-bin.000001 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: 455 Relay_Log_Space: 536 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: 154 Master_UUID: 4f1e30f3-ad54-11e7-94f6-005056a77979 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 more updates 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 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) |
6、配置mysql-02
mysql> change master to master_host='192.168.1.153',master_user='mysync1',master_password='Ma991218##',master_log_file='mysqlmaster-bin.000001',master_log_pos=755;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.153 Master_User: mysync1 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysqlmaster-bin.000001 Read_Master_Log_Pos: 755 Relay_Log_File: mysql-02-relay-bin.000002 Relay_Log_Pos: 326 Relay_Master_Log_File: mysqlmaster-bin.000001 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: 755 Relay_Log_Space: 536 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: 153 Master_UUID: 49f92156-ad54-11e7-9389-005056a72600 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 more updates 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 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) |
7、测试
# mysql-02 新建表,插入内容
mysql> create database ceshi;
mysql> use ceshi;
mysql> create table student(id int(10) primary keyauto_increment,name varchar(30),age tinyint(2));
mysql> insert into student (id,name,age) value(321281,"mzh",18);
# mysql-01 查看有无同步,并插入新数据
mysql> show databases;
+--------------------+ | Database | +--------------------+ | information_schema | | ceshi | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.17 sec) |
mysql> use ceshi;
mysql> show tables;
+-----------------+ | Tables_in_ceshi | +-----------------+ | student | +-----------------+ 1 row in set (0.01 sec) |
mysql> select * from student;
+--------+------+------+ | id | name | age | +--------+------+------+ | 321281 | mzh | 18 | +--------+------+------+ 1 row in set (0.03 sec) |
mysql> insert into student (id,name,age) value(2828,"hp",19);
# mysql-02 查看是否同步刚更新的数据
mysql> select * from student;
+--------+------+------+ | id | name | age | +--------+------+------+ | 2828 | hp | 19 | | 321281 | mzh | 18 | +--------+------+------+ 2 rows in set (0.00 sec) |
# 数据已可以正常互相同步
Over