一、前提
准备两台机器,关闭防火墙和selinux。—两台机器环境必须一致。时间也得一致
192.168.246.135 mysql-master
192.168.246.136 mysql-slave
两台机器配置hosts解析
192.168.246.135 mysql-master
192.168.246.136 mysql-slave
两台机器开启MySQL
systemctl start mysqld
systemctl enable mysqld
二、配置主节点
(1)在主服务器上,必须启用二进制日志记录并配置唯一的服务器ID。需要重启服务器。
编辑主服务器的配置文件 my.cnf
,添加如下内容
添加配置
[mysqld]
log-bin=/var/log/mysql/mysql-bin
server-id=1
(2)创建日志目录并赋予权限
mkdir /var/log/mysql
chown mysql.mysql /var/log/mysql
(3)重启服务
systemctl restart mysqld
(4)登陆mysql,创建主从同步的用户:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' identified by 'Qf@12345!';
mysql> flush privileges;
(5)在主服务器上面操作
mysql> show master status\G
三、配置从节点
(1)编辑从服务器的配置文件 my.cnf
[mysqld]
server-id=2
重启服务
systemctl restart mysqld
(2)slave端登录数据库连接master
mysql> \e
CHANGE MASTER TO
MASTER_HOST='mysql-master',
MASTER_USER='repl',
MASTER_PASSWORD='Qf@12345!',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=849;
-> ;
mysql> start slave;
mysql> show slave status\G
效果图如下
参数解释:
CHANGE MASTER TO
MASTER_HOST='master2.example.com', 主服务器ip
MASTER_USER='replication', 主服务器用户
MASTER_PASSWORD='password', 用户密码
MASTER_PORT=3306, 端口
MASTER_LOG_FILE='master2-bin.001', binlog日志文件名称
MASTER_LOG_POS=4, 日志位置
四、测试
(1)在master上面执行:
mysql> create database testdb; 创建一个库
(2)查看slave上是否有testdb
mysql> show databases;
五、拓展:故障排错
UUID一致,导致主从复制I/O线程不是yes
> Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work
致命错误:由于master和slave具有相同的mysql服务器uuid,导致I/O线程不进行;这些uuid必须不同才能使复制工作。
问题提示主从使用了相同的server UUID,一个个的检查:
检查主从server_id
主库:
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
1 row in set (0.01 sec)
从库:
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 2 |
+---------------+-------+
1 row in set (0.01 sec)
server_id不一样,排除。
检查主从状态:
主库:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
从库:
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 306 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
File一样,排除。
最后检查发现他们的auto.cnf中的server-uuid是一样的。。。
[root@localhost ~]# vim /var/lib/mysql/auto.cnf
[auto]
server-uuid=4f37a731-9b79-11e8-8013-000c29f0700f
修改uuid并重启服务