mysql主从复制,读写分离 详解（问题排除）

mysql主从复制

Master 主

Slaver 从

Replication 复制

官方文档：

https://dev.mysql.com/doc/refman/5.7/en/replication-implementation-details.html

DML操作会产生数据库的变化 --->主master一定要开启二进制日志(只要进行读写操作二进制日志就会发生变化）

 

slave 开启两个进程 -->IO 进程（读写） 到master上面去拿取数据---->master上有binary log dump线程binary log(二进制日志） --> 拿取二进制 binary log 到从（salve)上写入Relay log中-->SQL Thread 会从 relay log上获取上次读取到的位置点 --->SQL thread 再从 Relay log（中继日志） 上读取数据从而保持数据的一致。

 

下图来源于网络，侵删。

 

 

 

1. 项目名称：mysql主从复制
2. 项目环境： 两个linux服务器
3. 项目描述: 搭建2台mysql服务器，实现主从复制，为后面的读写分离做前期准备，构建一个简单的mysql集群。
4. 项目步骤： 1.安装好centos7编译安装mysql，在主服务器启动二进制日志功能，新建并授权用户，并且将主服务器里面的数据导出，在从服务器上导入数据，做好前期两台服务器之间的数据一致性工作。

 

主 ：192.168.0.215

从： 192.168.0.77

show variables like "%version%";
# 查看mysql 版本号

show variables like "%log_bin%"; 
#查看二进制日志是否开启

注： 主从服务器上面的防火墙和selinux需要关闭

1.主服务器开启二进制日志，新建并授权用户

/etc/my.cnf

从服务器上面的也可开启 -->注意： 从服务器上的server_id 不能和master上面的一样，

service mysqld restart --->重启mysql服务

主 ：192.168.0.215 新建授权用户（用于复制二进制日志）

mysql>grant replication slave on *.*   to 'wang'@'%' identified by "wang123";
Query OK, 0 rows affected, 1 warning (0.00 sec)
#授予所有库所有表的复制权利

需要先建立单向免密通道才能进行推送 ，如下有详解：

https://blog.csdn.net/wsy_miao/article/details/107323362

 [root@localhost ~]# mysqldump -uroot -p'Sanchuang123#'  --all-databases >/backup/all_db.sql
# 备份数据库所有文件
  [root@localhost ~]# scp  /backup/all_db.sql  root@192.168.0.77:/root
# 将备份数据推送到 从服务器上

查看mster机器上面的二进制日志文件及其所在位置

从：
#进入从服务器上面的mysql
 /root
 # 将传送过来的数据导入
[root@localhost ~]# mysql -uroot -p'Sanchuang123#'<all_db.sql

# 进入从mysql使用新建用户的用户名、密码，主服务器上日志文件端口号。
mysql>CHANGE MASTER TO MASTER_HOST='192.168.0.215',
    -> MASTER_USER='wang',
    -> MASTER_PASSWORD='wang123',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='localhost-bin.000005',
    -> MASTER_LOG_POS=437;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

 

如果IO没有开启：
 

mysql>stop slave;

mysql> start slave;

# 再次查看

mysql>show slave status\G;

 

验证： 在主master上新建库或表以及修改数据，在从slave服务器上面也能看到变化则成功。

可能出现的错误

1.用户名，密码配错，

2.两台机器数据库不一样（一台mysql，一台mariadb) ,

3.如果两台机器一台是克隆的另一台机器，UUID会一样导致如下错误（Last_IO_Error: 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.）

解决方法：直接修改其中一台机器上面的UUID并刷新服务

 

Master上如何知道有哪些从服务器？

root@(none) 17:43  mysql>SHOW SLAVE HOSTS;

清除从服务器上填写的主服务器的信息？

top  slave;

reset slave all；

  #清除所有的master信息

Show slave status\G;

重新输入master的信息， 然后到从服务器上看，是否存在

如果主服务器挂了，如何让从服务器成为主服务器呢？

答：先在从服务器上停止stop slave，然后让web服务器把数据发送到slave上（修改连接数据库的ip为从服务器ip），如果主服务器好了，就让主服务器做从服务器，去同步从服务器的数据。

 将其他的主机配置为从服务器，从晋级的机器上面拿去二进制日志。

读写分离

读写分离是实现负载均衡

实现读写分离--->中间件：192.168.0.88mysql-rpm安装 --->只需要安装mysqlrouter,不需要安装mysql

mysqlrouter机器上

#官网下载rpm包并安装
[root@localhost ~]# rpm -ivh mysql-router-community-8.0.21-1.el7.x86_64.rpm 

# rpm-qa|grep mysql-router
# 修改配置文件
cd /etc/mysqlrouter/
vim mysqlrouter.conf 

# 在最后面添加
[routing:read_write]  # -->写
bind_address = 192.168.0.88   #--->中间件ip地址 
bind_port = 7001   -->端口
mode = read-write
destinations = 192.168.0.215 ：3306 # -->主ip地址 
max_connections = 65535 # --->最大连接数
max_connect_errors = 100   
client_connect_timeout = 2 # -->超时时间

[routing:read_only]   #-->只读机器
bind_address = 192.168.0.88  
bind_port = 7002
mode = read-only
destinations = 192.168.0.77:3309
max_connections = 65535
max_connect_errors = 100
client_connect_timeout = 2

# 刷新服务

[root@localhost mysqlrouter]# service mysqlrouter start 
Redirecting to /bin/systemctl start mysqlrouter.service

7001 7002端口被打开

# 在主服务器上授权两个用户

# 授予zhang所有权限 
wangmysql>grant all  on *.* to 'zhang'@'%' identified by '123456';

# 只授予ming只读的权限
 wangmysql>grant select on *.* to 'ming'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)

 

# 用其他客户机用授权用户连接主服务器，填写中间件地址(不要直接填写后端地址）

能够进入数据库进行写操作 --->成功

[root@localhost ~]# mysql -u ming -p'123456' -P7001 -h 192.168.0.88

远程进入数据库之后

# 对数据库里的表进行操作，
MySQL> drop table student ;
ERROR 1142 (42000): DROP command denied to user 'ming'@'192.168.0.88' for table 'student'
# ming这个用户只授予了读的权限，不能对数据库数据进行删除与添加
# 验证成功