Linux 安装Mysql8.0的读写分离,以及常用报错解决方式

Centos7.2 ,Mysql8.0主从配置和读写分离。主写从读

环境准备

系统： Centos7.2
Mysql版本： 8.0

搭建两台服务器，一台作为主服务器，一台作为从服务器。主服务器进行写操作。从服务器进行读操作

之前已经博文已经写过如何安装Mysql 8.0了。不会的可以去查看之前的博客

一、主从配置步骤

1. 确保主数据库和从数据库的数据保持一样

二、主服务器上的操作

修改mysql配置，找到主服务器的配置文件my.conf，一半在etc/这个文件夹下

vim /etc/my.conf

配置文件修改

[mysqld]
log-bin=mysql-bin     //  启动二进制日志log-bin
server-id=119       // 给数据库服务的唯一标识，一般设置为服务器ip的末尾三位

启动mysql服务

service mysql start

通过命令行登陆mysql， user是用户名，password是密码。你们自己配置的

mysql -uuser -ppassword

创建一个新用户，real，用户名随意，但是必须要具有REPLICATION SLAVE权限，除此之外没有必要添加多余的权限，密码设置自己记住就行，ip% 这个配置意思是指明repl用户所在登陆的服务器，%是通配符，表示192.168.0.0 - 192.168.0.255的Server 都可以使用repl用户登陆主服务器。也可以指定固定的ip

create user ‘gssnb’@’%’ identified with ‘mysql_native_password’ by ‘password’

赋予权限 BY 后面就是设置的密码

GRANT REPLICATION SLAVE ON . TO ‘repl’@192.168.0.%’

查看日志

show master status;

File	Position	Binlog_Do_DB
mysql-bin.000001	155

只需要记住这个file 和 position 后面要用

配置从服务器

找到Mysql安装的配置文件，和主Mysql的my.cnf文件夹在一个位置 /etc

vim /etc/my.cnf

修改my.cnf文件，在mysqld下方增加几行代码

[mysqld]
server-id=192
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin

启动mysql服务

systemctl start mysqld.service

连接Master服务器

 change master to master_host='ip', // 你的Master服务器ip
 master_port=3306, // 端口号
 master_user='user', // 登陆的用户名
 master_password='password' // 设置的密码
 master_log-file='master-bin.000008',  // 之前让你记住的file
 master_log-pos=32559  // 之前让你记住的position

启动slave

start slave;

查看slave 状态

show slave status\G

只要 Slave_IO_Runging和Slave_SQL_Runing为Yse 就代表连接成功了；

常见错误

安装完成后，有的会出现一个错误

Last_IO_Error: Fatal error: The slave I/O thread stops because master
and slave have equal MySQL server ids; these ids must be different
for replication to work (or the –replicate-same-server-id option
must be used on slave but this does not always make sense; please
check the manual before using it).

这个错误忘记截图了，反正是在中间报这个错误

解决方案：

原因： 首先确认server-id是否唯一，有可能是mysql并没有加载my.cnf文件中的server-id

解决办法：
① 修改server-id 在my.cnf文件中
② SQL 语句

set global server-id=119; # 这个地方的id应该和my.cnf中的值一样
start slave 重启slave

错误信息：
主从配置读写不同步：
错误原因：
查看slave状态

show slave status\G

有可能是因为
Slave_IO_Running:No 和 Slave_SQL_Running：NO 其中又一个为NO

解决方案：

首先要保证两台Mysql服务器数据保持一致并且同步

登陆主服务器

flush logs； 刷新log

show master status; 查看状态 记住 file 和 position 的值

登陆从服务器
关掉slave

stop slave;

重新连接主服务器

 change master to master_host='ip', // 你的Master服务器ip
 master_port=3306, // 端口号
 master_user='user' // 登陆的用户名
 master_password='password' // 设置的密码
 master_log_file='master-bin.000008',  // 之前让你记住的file
 master_log_pos=32559  // 之前让你记住的position

启动slave

start slave;

还有个问题，暂时没有解决遇到了。不过不影响大局
问题描述：
读写分离如果你的数据库在遇到高并发，写操作很多的时候，这个时候你的从服务器的数据同步可能会有延迟，

解决方案

找了很久的解决方案，有人说在两边设置一个表。只有一个字段，用来记录两边的写操作，然后每次比较重要的查询前都进行一次读取这个字段，再用来比较。如果值不一样，就直接从主服务器中读取，如果一样，就可以直接可以在从服务器中取出来。

新遇见的错误补充

Mysql 8.0 版本错误信息

 Last_IO_Errno: 2061
                Last_IO_Error: error connecting to master 'repl@192.168.40.55:3306' - retry-time: 60  retries: 12
               Last_SQL_Errno: 0

检查ip,端口密码之后发现没有错误。 在一篇博客里面看到这么说的
MySQL8.0默认指定使用需要SSL的身份验证插件caching_sha2_password，而我们在创建同步复制账号时候没有指定REQUIRE SSL。为了降低这件事情的复杂性，我们选择了社区的解决方法，选择绕过SSL插件的验证，改为mysql_native_password验证来做同步复制。

解决

重新创建MySQL复制账号，创建账号时指定身份验证方法

CREATE USER 'gssznb'@'%' IDENTIFIED WITH 'mysql_native_password' BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'gssznb'@'%';
FLUSH PRIVILEGES;

重启之后 设置同步

stop slave;
reset slave;
start slave;

错误

mysql8.0版本

'Operation CREATE USER failed for 'repl'@'%'' on query. Default database: ''. 
Query: 'CREATE USER 'repl'@'%' IDENTIFIED WITH 'caching_sha2_password' AS 
'$A$005$gmjMeTacDH/#WqOU50WeHvY6Eg5lM07QYxFTnGgF5U1W/NTwrVq5bI.''

解决

别人说这个是 mysql 8.0 的一个bug ，其实如果你刚开始绕过了ssl加密，使用mysql_native_password 方式创建验证方法，其实就不会出现这个错误，我出现这个错误是因为刚开始创建从库登录主库的用户 通过caching_sha2_password plugin方式来创建。是无法连接到主库的。会出现IO_Thread一直显示为 Connecting 中。 这个时候只需要把这几个 用户在主库中全部删除就好了。

DELETE FROM mysql.user WHERE user='repl';
// 刷新表格
flush tables;

错误

The slave I/O thread stops because master and slave have equal MySQL server UUIDs

出现错的原因，因为我是VMWARE 安装了一台centos7 的系统，装好mysql 又克隆了一份，所以导致mysql的 配置都是一模一样的。然后 UUID 也是一样的所以报错了

解决方式

vim /var/lib/mysql/auto.cnf 
// 查看uuid 的值两台机器都要查看 如果一致 就把其中的一个删除。重启服务就可以了
rm /var/lib/mysql/auto.cnf
// 重启mysql 服务
systemctl restart mysqld.service

OK