MySQL主从复制的配置
单向主从环境的搭建
环境配置
- Master(192.168.80.132)
- Slave(192.168.80.133)
一、Master的相关配置
1.1 关闭selinux
两台数据库服务器的selinux都要disabled,配置方式如下:
vim /etc/selinux/config
(将SELINUX设置为disabled)
1.2 修改Master的配置文件my.cnf
vim /etc/my.cnf
在[mysqld]下增加以下配置项
server_id=132
# binlog-do-db=user
binlog-ignore-db=mysql
log-bin=trey-mysql-bin
binlog_cache_size=1M
binlog_format=mixed
expire_logs_days=7
slave_skip_errors=1062
代表的意义分别如下:server_id=132
:设置server_id,唯一即可,一般设置为IP。binlog-do-db=user
:表示需要备份的数据库,这里表示只备份user数据库,输出binlog。binlog-ignore-db=mysql
:表示不需要备份的数据库,不输出。
1.3 创建数据同步用户,并授予相应的权限
service mysql restart
打印出以下日志,表示重启成功:
Shutting down MySQL..... SUCCESS!
Starting MySQL....... SUCCESS!
创建数据同步用户,并授予相应的权限:
grant replication slave, replication client on . to 'repl'@'192.168.80.133' identified by '123456';
flush privileges;
查看position号,记下position号,因为从节点
上要知道备份开始的位置以及现在的日志文件:
show master status;
会出现下面信息:
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 154 | | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1.4 创建数据库,并写入测试数据
创建数据库ms-mysql
create database if not exists ms-mysql default charset utf8mb4 collate utf8mb4_bin;
创建表user并插入数据
CREATE TABLE `user` (
`id` int(11) NOT NULL,
`username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;
INSERT INTO `user` VALUES (1, 'zhangsan', '123456', '张三');
INSERT INTO `user` VALUES (2, 'lisi', '123456', '李四');
INSERT INTO `user` VALUES (3, 'wangwu', '123456', '王五');
备份主库数据
为了保证Master和Slave的数据一致,我们采用主备份,从还原
来实现初始数据一致。
先临时锁表:
mysql> flush tabled with read lock;
导出Master库的脚本,导出的脚本临时存放在/home
目录下:
mysqldump -p3306 -uroot -p -add-drop-table ms-mysql > /home/master-user.sql
解锁表:
unlock tables;
发送Master备份数据到Slave上
scp /home/master-user.sql root@192.168.80.133:/home/
二、Slave相关配置
2.1 修改Slave的配置文件my.cnf
vim /etc/my.cnf
在[mysqld]下增加以下配置项
server_id=133
binlog-ignore-db=mysql
log-bin=mysql-slave-bin
binlog_cache_size=1M
binlog_format=mixed
expire_logs_days=7
slave_skip_errors=1062
relay_log=mysql-relay-bin
log_slave_updates=1
read_only=1
2.2 将之前Master生成的备份脚本还原到Slave中
由于修改了my.cnf
,所以需要重新MySQL:
service mysql restart
打印出以下日志,表示重启成功:
Shutting down MySQL.... SUCCESS!
Starting MySQL......... SUCCESS!
Slave上创建和Master上相同的数据库:
创建数据库ms-mysql
create database if not exists ms-mysql default charset utf8mb4 collate utf8mb4_bin;
导入数据:
mysql -u root -p ms-mysql < /home/master-user.sql
2.3 登录Slave数据库,添加相关参数
mysql -u root -p
change master to master_host='192.168.80.132',master_user='repl',master_password='123456',master_port=3306,master_log_file='mysql-bin.000002', master_log_pos=2886, master_connect_retry=30;
2.4 开启主从同步
查看主从同步状态:
show slave status\G;
出现以下信息:
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.80.132
Master_User: repl
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 2886
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: No
Slave_SQL_Running: No
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: 2886
Relay_Log_Space: 154
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: NULL
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: 0
Master_UUID:
Master_Info_File: /home/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
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:
可以看到Slave_IO_State
为空,Slave_IO_Running
和Slave_SQL_Running
是No,表明Slave还没有开始复制过程。
开启主从同步:
start slave;
这个时候会报如下错误信息:
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这台服务器时,MySQL数据文件/home/mysql/data/auto.cnf
也同样拷贝过来了,而这个文件里面包含着server-uuid,如下所示:
[auto]
server-uuid=c53e320a-0a67-11e9-a541-000c294123c2
而这个server-uuid
在两个机器的MySQL中是不允许重复的,这个时候只需要删除auto.cnf
这个文件即可,再重新启动mysql,会自动再次生成auto.cnf
。
查看Slave的主从同步状态:
show slave status;
显示如下信息:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.80.132
Master_User: repl
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 2886
Relay_Log_File: mysql-relay-bin.000003
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
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: 2886
Relay_Log_Space: 527
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: 132
Master_UUID: 2de93fce-09a0-11e9-b481-000c29abb1e8
Master_Info_File: /home/mysql/data/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:
关注其中的三个状态:
- Slave_IO_State: Waiting for master to send event
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
可以看到,我们配置的主从同步是正常的。
三、 主从数据复制同步测试
3.1 数据表的数据同步测试
为了测试我们的主从数据复制情况,我们可以做以下测试:
insert into user values (4, 'zhaoliu','123456','赵六');
这个时候我们在从库Slave上去查看user
表,看主库中新增的数据是否同步到从库中:
select * from user
3.2 数据库的同步测试
在主库Master中新增一个数据库test
:
create database if not exists test;
同样,我们在从库Slave中可以查看数据库:
show databases;
可以看到,主库Master中的test
库已经同步到从库Slave中了。
在测试过程中,如果遇到同步出错,可在Slave上重置主从复制设置:
reset slave;
master_host='192.168.80.132',master_user='repl',master_password='123456',master_port=3306,master_log_file='mysql-bin.000002', master_log_pos=2886, master_connect_retry=30;
注意,这个时候需要重新在主库Master上获取master_log_file和master_log_pos的值,表示从哪个文件的那个位置开始获取。
另外,如果在Slave没做只读控制的情况下,千万不要在Slave中手动插入数据,那样数据就会不一致,主从就会断开,就需要重新配置了。