MySQL8.0读写分离实例-技术圈

MySQL 8.0读写分离

系统建立初期，我们的架构都非常的简单，主要满足业务的正常运行，但是随着访问量的升高，人们对系统的可靠性有了更高的要求，所以，我们为了避免单点故障，对系统应用层进行了横向的扩展，这样，保证了系统应用层的高可用，在发生宕机，或者系统升级时，系统对外还是可用的。而且在访问量升高的时候，系统应用层的压力也会得到分摊，使得每一个单体的系统应用的压力在一个合理的区间范围内。

但是，随着访问量的升高，所有的压力都将集中到数据库这一层。数据库这一层将会成为系统的瓶颈，现在就需要针对数据库这一环节进行扩展，以保证其可靠性，高可用性。

同时，有了上面对应用服务器水平扩展的方案，那么能不能像系统应用层那样水平进行扩展呢？答案是否定的，想象一下，如果数据库层也像系统应用层那样，进行横向扩展，如图：

1.MySQL主从配置

主配置log-bin，指定文件的名字

l 主配置server-id 默认是1

l 从配置server-id，与主不能重复。

2.数据库规划:

主库：192.168.8.100

从库：192.168.8.112

注意：将db112中的数据库删除，方便观察同步数据效果

2-1 db100配置my.cnf

vim /etc/my.cnf

[mysqld]标签下配置

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

log-bin=xiaoyege_mysql

server-id=1

保存后重启db100 mysql服务器

service mysqld restart

2-2 db112配置my.cnf

vim /etc/my.cnf

[mysqld]标签下配置

只需要配置一个server-id=2即可

server-id=2

service mysqld restart

3.创建主从配置账号&&授权

主数据库创建备份账户并授权 replication slave

登录db100

mysql -uroot -p1qaz@WSX;

SELECT DISTINCT User FROM mysql.user;

创建用户

create user 'repl'@'%' IDENTIFIED BY '1qaz@WSX';

授权

GRANT replication slave ON *.* TO 'repl'@'%';

刷新权限

flush privileges;

4.主库锁表

将主库的表锁住，锁住以后进行备份，锁住以后，写操作就不会落到主表，然后将主库数据备份到从库，接着查询一下binlog的位置，记录一下，然后在从库中进行主从的配置，因为主从配置需要指定binlog的位置

然后把主库的请求放开，因为可以读取binlog的位置，锁表之后发生的数据就可以同步从库了;

主库锁表

flush tables with read lock;

在主库插入一条数据进行测试

INSERT user(id,username) VALUES(6,'db_100 主库 xiaoyege');

执行结果:

执行语句一直卡着，因为锁表了;

5.主库binlog位置

主数据库查询log-bin的位置

mysql> show master status;

xiaoyege_mysql.000001

此文件就是在主库设置的文件名称;

p osition = 847;

p osition 之后的数据开始读取日志，进行数据同步;

在这个位置之前的数据，需要使用mysql.dump将之前的数据dump下来，复制到从库当中;

主备份数据

dump数据

需要重新开启一个会话进行操作

mysqldump --all-databases --master-data > dbdump.db

或者：

mysqldump --all-databases --master-data > dbdump.db -uroot -p1qaz@WSX

密码：1qaz@WSX

文件dump完成

6.从库dump文件

将dbdump.db文件复制到从库112这台机器上

scp root@192.168.8.111:~/dbdump.db .

xiaoyege1qaz

将这个文件加载到112的mysql当中

mysql < dbdump.db -uroot -p

1qaz@WSX

查看db112，表里面内容已经存在了; 和之前db100的内容一致;

7.解除主库锁表

mysql -uroot -p1qaz@WSX;

unlock tables;

查询db100之前的insert语句锁住的情况，已经执行成功了;

查询从库还是没有同步过来;

8.从数据库设置主从同步

mysql -uroot -p1qaz@WSX;

mysql>

mysql> change master to

-> master_host='192.168. 8 .1 11 ',

-> master_user='repl',

-> master_password='1qaz@WSX',

-> master_log_file='xiaoyege_mysql.00000 9 ',

-> master_log_pos= 18259

-> get_master_public_key=1

-> ;

Query OK ， 0 rows affected ， 8 warnings (0.05 sec)

mysql>

最后执行

start slave;

查看错误信息;

show slave status;

show slave status \G;

9.常见错误

9-1 错误caching_sha2_password

Last_IO_Error ： error connecting to master 'repl@192.168.8.100:3306' - retry-time ： 60 retries ： 1 message ： Authentication plugin 'caching_sha2_password' reported error ： Authentication requires secure connection.