- 操作系统为CentOS 7.6 x64
- MySQL为一主一从,版本为5.7
- 主从节点IP分别为:
10.88.36.200(主节点)
10.88.36.201(从节点)
安装MySQL
在两个节点分别安装MySQL 5.7(建议从官网下载rpm包安装)
并进行初始化(如修改root密码等)
修改配置(主节点)
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin //[必须]启用二进制日志
server-id=200 //[必须]服务器唯一ID,默认是1,一般取IP最后一段
修改配置(从节点)
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin //[不是必须]启用二进制日志
server-id=201 //[必须]服务器唯一ID,默认是1,一般取IP最后一段
重启MySQL(两个节点)
service mysqld restart
建立帐户并授权(主节点)
登录主节点MySQL,执行:
mysql> create user "mysql_sync"@'%' identified by "C@p19U#qfo";
mysql> grant replication slave on *.* to 'mysql_sync'@'%' identified by 'C@p19U#qfo';
查询master的状态(主节点)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 688 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
注:执行完此步骤后不要再操作主节点MySQL,防止主节点状态变化
配置slave(从节点)
mysql> change master to master_host='10.88.36.200',master_user='mysql_sync',master_password='C@p19U#qfo',master_log_file='mysql-bin.000001',master_log_pos=688;
mysql> start slave;
检查复制状态(从节点)
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.88.36.200
Master_User: mysql_sync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1520
Relay_Log_File: relay-bin.000002
Relay_Log_Pos: 1152
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes //此状态必须YES
Slave_SQL_Running: Yes //此状态必须YES
...
注:Slave_IO及Slave_SQL进程必须正常运行,即YES状态,否则都是错误的状态(如:其中一个为NO都表示主从同步错误)。
至此,主从同步配置完成。
主从同步测试
在主节点MySQL,建立数据库,并在这个库中建表插入一条数据:
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
Database changed
mysql> create table test(id int);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test values(1);
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
在从节点MySQL查询:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
mysql> use test;
Database changed
mysql> select * from test;
+------+
| id |
+------+
| 1 |
+------+
1 rows in set (0.00 sec)
网友评论