centos 7 Mysql5.7 主从复制配置
1、环境
Centos 7
Mysql 5.7
Master 192.168.1.71
Slave01 192.168.1.72
2、分别配置master，slave01
# vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin //[必须]启用二进制日志 ------新增
server-id=71 //[必须]服务器唯一ID，slave01此处为72 ------新增
3、重启master，slave01的mysql服务
#systemctl restart mysqld.service
注意该命令为red hat/centos系统重启服务命令
4、在master服务器上，登陆mysql,并创建复制使用账户，并分配权限给该账户
[root@master ~]# mysql -u root -p
Enter password:
...
mysql> GRANT REPLICATION SLAVE ON *.* to 'copier'@'%' identified by 'copy123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
5、查看master数据库的bin日志文件信息
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 439 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
6、在slave01服务器上，登陆mysql,连接master，启动复制
[root@slave01 ~]# mysql -u root -p
Enter password:
...
mysql> change master to master_host='192.168.1.71',master_user='copier',master_password='copy123',master_log_file='mysql-bin.000001',master_log_pos=439; Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> start slave; Query OK, 0 rows affected (0.00 sec)
mysql>
7、在slave01查看复制状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.1.71
Master_User: copier
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 439
Relay_Log_File: slave01-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
...
1 row in set (0.00 sec)
发现 Slave_IO_State为 Connecting to master，一直在连接Master，检查网络
[root@slave01 ~]# telnet 192.168.1.71 3306
Trying 192.168.1.71...
telnet: connect to address 192.168.1.71: No route to host
说明master禁止对端口3306访问，需要对端口3306开发，开放后
如在master上执行
[root@master ~]# firewall-cmd --add-port=3306/tcp --permanent
[root@master ~]# firewall-cmd --reload
再在slave01上查看状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
...
8、测试
在master服务上，登陆mysql ,测试以下数据
mysql> create database helloDb;
Query OK, 1 row affected (0.01 sec)
mysql> use helloDb;
Database changed
mysql> create table helloword(id int,name varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into helloword values(1,'test');
Query OK, 1 row affected (0.01 sec)
mysql> insert into helloword values(2,'hi');
Query OK, 1 row affected (0.01 sec)
mysql> select * from helloword;
+------+------+
| id | name |
+------+------+
| 1 | test |
| 2 | hi |
+------+------+
2 rows in set (0.00 sec)
在slave01上，登陆mysql ,测试以下数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| helloDb |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.03 sec)
mysql> use helloDb;
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> select * from helloword;
+------+------+
| id | name |
+------+------+
| 1 | test |
| 2 | hi |
+------+------+
2 rows in set (0.00 sec)
至此主从复制配置成功。
