十四、mysql主从同步结构模式------一主多从(放射式复制)
配置mysql一主多从结构
主库 192.168.1.10 从库 192.168.4.11 、 192.168.4.12
要求:客户端访问主库51 时 创建库表记录 在52 和53 数据库服务器都可以看到
1.配置主库(192.168.1.10)
1.1修改配置文件,启动启用binlog日志
[root@master ~]# vim /etc/my.cnf
[mysqld]
server_id=10 #指定server_id
log_bin=master10
binlog_format="mixed"
[root@Mysql-master ~]# systemctl restart mysqld
1.2,登录数据库创建用户并设置权限
[root@Mysql-master ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to repluser@"192.168.1.11" identified by "123456";
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> grant replication slave on *.* to repluser@"192.168.1.12" identified by "123456";
Query OK, 0 rows affected, 1 warning (0.00 sec)
查看用户授权
mysql> select user,host from mysql.user where user="repluser";
+----------+-------------------+
| user | host |
+----------+-------------------+
| repluser | 192.168.1.11 |
| repluser | 192.168.1.12 |
+----------+-------------------+
1.2查看主库状态信息
mysql> show master status\G
*************************** 1. row ***************************
File: master10.000002 #日志名
Position: 1961 #偏移量
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
2.配置从库
2.1修改配置文件,启动启用binlog日志
[root@slave ~]# vim /etc/my.cnf
[mysqld]
server_id=11 #指定server_id
[root@slave~]# systemctl restart mysqld
修改完成后登录数据库
[root@slave]# mysql -uroot -p123456
mysql> change master to master_host="192.168.1.10",master_user="repluser",master_password="123456",master_log_file="master10.000002",master_log_pos=1204;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> start slave ; #启动slave 进程
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.10
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master10.000002
Read_Master_Log_Pos: 1805
Relay_Log_File: slave2-relay-bin.000005
Relay_Log_Pos: 319
Relay_Master_Log_File: master10.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> start stop ; #启动slave 进程
注从库2与从库1配置一样