mysql5.7主从复制配置——读写分离实现

时间:2023-12-24 18:09:49

为什么使用主从架构?
1、实现服务器负载均衡;
2、通过复制实现数据的异地备份;
3、提高数据库系统的可用性;
4、可以分库【垂直拆分】,分表【水平拆分】;

主从配置的前提条件
1、MySQL版本一致;
2、MySQL中的数据一致;
      不一致就把它们搞一致!
3、操作前停止一切更新操作(写入、更新、删除等);
4、服务器时间一致

主:192.168.1.60
从:192.168.1.35
注意:35、60服务器安装mysql省略,mysql用户名/密码:root/attack。

注意点:
1、一定要保证两台服务器的时间是同步的,如果不同步无法实现mysql主从配置;
2、设置服务器具体时间:date -s 12:23:23,通过xshell客户端实现;

1、配置master(主服务器)

vim /etc/my.cnf
#[必须]启用二进制日志
log-bin=mysql-bin
#[必须]服务器唯一ID,默认是1,一般取IP最后一段
server-id=

查询master(主服务器)的状态

show master status;

mysql5.7主从复制配置——读写分离实现
File列和Position列的值一会儿我们要用到。

在主服务器上创建备份专用帐户

mysql -uroot -pattack -e "GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO 'backup'@'192.168.1.%' IDENTIFIED BY 'Attack@123';" ###推荐使用
mysql -uroot -pattack -e "GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO 'backup'@'192.168.1.160' IDENTIFIED BY 'Attack@123';" mysql -uroot -pattack -e "flush privileges;" ###刷新权限

备份专用帐户及密码:backup/Attack@123

2、配置slave(从服务器)

vim /etc/my.cnf
#[可选]启用二进制日志
log-bin=mysql-bin
#[必须]服务器唯一ID,默认是1,一般取IP最后一段
server-id=35

如果你的从服务器下面再挂从服务器,启用二进制日志就是必选的!

注意:由于此时的mysql已经启动过,会自动保留一个uuid的服务编号,这个编号文件(auto.cnf)如果不删除,无法实现主从;
mysql5.7主从复制配置——读写分离实现

3、重启mysql服务

/etc/init.d/mysqld restart
service mysqld restart

重启的目的是使用刚才的配置生效,主从都需要重启;

注意:服务名是“mysqld”,不是某同学文章中的“mysql”!

4、配置Slave启动主从复制
a、进入到mysql命令窗口之中:mysql -uroot -pattack
b、如果现在你已经启动从主机,先关闭:stop slave
mysql5.7主从复制配置——读写分离实现

c、如果停止之后还想重新启动,则必须清除所有的日志信息:flush logs;
mysql5.7主从复制配置——读写分离实现

d、启动从服务器复制功能

//注意master_log_pos值经常会变化,记得执行如下命令前先执行"show master status;",得到最新的Position列,154数字前后无单引号。
change master to master_host='192.168.1.60',master_user='backup',master_password='Attack@123',master_log_file='mysql-bin.000001',master_log_pos=154; start slave;

mysql5.7主从复制配置——读写分离实现

参数描述:
master_host=主服务器IP
master_user=在主服务器上创建的备份用户名
master_password=备份用户密码
master_log_file=查询master(主服务器)的状态得到的File列的值
master_log_pos=Position列的值
start slave:启动从服务器复制功能

5、检查从服务器复制功能状态

mysql -uroot -pattack -e "show slave status\G;" 

mysql5.7主从复制配置——读写分离实现
主要查看以下两项:

Slave_IO_Running: Yes
lave_SQL_Running: Yes

Slave_IO及Slave_SQL进程必须正常运行,即YES状态,否则都是错误的状态(如:其中一个NO均属错误)。
以上操作过程,主从服务器配置完成。

我在一次配置过程中忘记在主服务器上创建备份用户,Slave_IO_Running 就一直处于“connect”状态;或者防火墙没关闭导致无法连接master服务器。

6、主从服务器测试
在主服务器上执行以下操作:

mysql -uroot -pattack

create database db_test_slave;
use db_test_slave;
create table tb_test(id int(3), name varchar(50));
insert into tb_test values(1,'hello slave');
show databases;

在从服务器上执行以下操作:

mysql -uroot -pattack -e "show databases; use db_test_slave; select * from tb_test;" 

查看输出:

+--------------------+
| Database |
+--------------------+
| information_schema |
| db_test_slave |
| mysql |
| performance_schema |
| sys |
+--------------------+
+------+-------------+
| id | name |
+------+-------------+
| 1 | hello slave |
+------+-------------+

至此,mysql主从配置完成。

一开始讲了主从复制的一些好处,但是单一的主从复制也有其不足:当更新操作增加到一定程度后,主服务器的任务会过分繁重,成为瓶颈,从而使系统性能大幅度下降。另外当主机出现故障时,整个系统都涉及更新的功能都不能正常使用,因此系统的可靠性依然不高。

在主节点对sql进行update、delete操作时,抛出如下异常:
[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
解决办法:
在/etc/my.cnf文件中,新增如下内容,保证35和60的sql-mode配置一致:
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
重启mysql。