文章首发于个人博客,欢迎访问关注:https://www.lin2j.tech
准备工作
- 准备两台主机(一台作为主服务器,一台作为从服务器),使用虚拟机即可
- 为两台主机配置相同版本的 MySQL,这里使用的是 5.7 版本
- 开放主服务器的 3306 端口
说明
- 主服务器IP:192.168.1.198, mysql账号密码:master,master
- 从服务器IP:192.168.2.129, mysql账号密码:slave, slave
一、配置主服务器
- 修改 /etc/mysql/mysql.conf.d/mysqld.cnf 文件,在对应位置添加如下两行
[mysqld]
# 将mysql二进制日志取名为mysql-bin
log-bin = mysql-bin
# 服务器集群的唯一标志 id
server-id = 1
- 启动并登录 mysql
mysql -u root -p
- 授权给从服务器,使之能够连接主服务器(相当于给从服务器一把钥匙,使之能够访问主服务器上的日志文件)
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.2.129' INDENTIFIED BY 'slave'
- 查询主服务器状态(如果没有进行第一步的那两行配置,这里执行后会显示 Empty Set),记录下 FILE 和 Position 的值,接下来会有用
show master status\G;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 3554 | | | |
+------------------+----------+--------------+------------------+-------------------+
二、配置从服务器
- 修改 /etc/mysql/mysql.conf.d/mysqld.cnf 文件,在对应位置添加如下一行
[mysqld]
# 服务器集群的唯一标志 id
server-id = 2
- 启动登录
mysql -u slave -p
- 执行同步语句
change master to master_host='192.168.1.198', master_user='master', master_password='master',master_log_file='mysql-bin.000006', master_log_pos=3554;
- 启动slave进程
start slave;
关闭slave
命令:stop slave;
- 查看配置是否成功
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.198
Master_User: master
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 3554
Relay_Log_File: ubuntu-relay-bin.000002
Relay_Log_Pos: 998
Relay_Master_Log_File: mysql-bin.000006
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: 3554
Relay_Log_Space: 1206
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: 91b21d71-5d14-11e9-b599-80a5897de16d
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)
ERROR:
No query specified
-
Slave_IO_Running: Yes Slave_SQL_Running: Yes
两个都为Yes才算成功
-
Slave_IO_State: Waiting for master to send event
如果这里 Slave_IO_State: Connecting to master,那么有四个可能(愁了我一下午)
1、两台主机的网络不通;
2、第二大点第三小点的账号密码错误或者Position错误;
3、主服务器的 3306 端口没有开放
4、你的mysql不允许使用ip进行访问连接 (即
mysql -h 192.168.1.198 -u master -p
会提示无法连接服务器,可以通过
update user set host='%' where user='master'
进行更改,不过这样不安全)
针对第三、四点可以使用
telnet 192.168.1.198 3306
进行测试,如果不通而端口又有开放,请将/etc/mysql/mysql.conf.d/mysqld.cnf 中下面这一行注释掉,在行首添加 #
bind-address = 127.0.0.1
建议主从服务器都这样设置一下,使集群中别的服务器能通过 ip 进行访问
三、测试
在确认配置成功后,在主服务器的数据库中添加数据库 master_db,并建立数据库 user
create database master_db;
use master_db;
create table user(id int(11) primary key auto_increment, name char(20) not null);
insert into user set name='master';
然后去从服务器查表,会发现,从服务器中已经有了 master_db 数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| master_db "here" |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use master_db;
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 user;
+----+--------+
| id | name |
+----+--------+
| 1 | master |
+----+--------+
1 row in set (0.00 sec)