MySQL主从分离实现_vlambda技术博客

vlambda
2020-07-25

MySQL主从分离实现

前言

大型网站为了减轻服务器处理海量的并发访问，所产生的性能问题，采用了很多解决方案，其中最主流的解决方案就是读写分离，即将读操作和写操作分别导流到不同的服务器集群执行，到了数据业务层，数据访问层，如果还是传统的单靠一台服务器去扛，面对如此多的数据库连接操作，必然会崩溃，如果造成数据的丢失，是不堪设想的。本文将利用mysql的主从配置，实现读写分离。

主从库理论知识

主从同步如何实现？
同步工作主要又三步，第一步就是主服务器（master）将对数据的操作记录到二进制日志文件（Binary log）中，也就是说，在每个事务更新数据完成之前，mster在日志里记录这些改变，mysql将事务串行地写入二进制文件中，在事件写入二进制日志完成后，master通知存储引擎提交事务，提交好事务后，就会进入第二步，需要补充一点的是，我们对数据的一次操作就称为一次二进制日志事件，也就是Binary log event。在第二步呢，Slave将master的Binary log拷贝到它的中继日志（Relay log）中，也就是Slave会首先开启一个工作线程（I/O thread）,I/O线程在master上打开一个普通的连接，做Binary log的拷贝，从master的二进制日志文件中读取事件，如果已经跟上了master，就会睡眠并等待master产生新的事件，I/O线程然后将这些事件写入中继日志中；第三步就是Slave重做中继日志事件，SQL线程从中继日志中读取事件，并重放其中的事件，更新Slave中的数据，使其与Master中的数据一致。 MySQL主从分离实现

主从库配置

我在自己VMware虚拟机上装了两个centOs系统，使用命令：# cat /etc/redhat-release 可以看到我自己的CentOs版本为7.6.1810的 MySQL主从分离实现

CentOS7.6安装mysql5.7.31

1. 解压到/usr/local目录
# tar -zxvf mysql-5.7.31.tar.gz -C /usr/local
2. mysql-5.7.31-linux-glibc2.5-i686文件夹重命名为mysql
# cd /usr/local
# mv mysql-5.7.18-linux-glibc2.5-i686/ mysql
3. 新建mysql用户组和mysql用户
# groupadd mysql
# useradd -r -g mysql mysql
4. 新建数据目录
# cd /usr/local/mysql
# mkdir data
5. 更改所有者以及授权755
# cd /usr/local
# chown -R mysql:mysql mysql/
# chmod -R 755 mysql/
6. 初始化mysqld
# cd /usr/local/mysql
# ./bin/mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --initialize
如果执行初始化时出现报错：./bin/mysqld: error while loading shared libraries: libnuma.so.1: cannot open shared object file: No such file or directory，就是少东西，centos使用yum装yum -y install numactl，装了后在执行初始化命令就可以了。
如果出现错误：initialize specified but the data directory has files in it. Aborting.大意是提示data目录有文件，把mysql安装目录\data文件夹里的内容清空（建议读者备份到其他地方）。再次执行"mysqld --initialize"，没有报错了！ MySQL主从分离实现记录下root的初始密码: Wg;iqpl_P5os

7. 将mysqld添加成服务，并启动它
# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
建立mysql默认的配置文件/etc/my.cnf，并添加图片中内容
# vim /etc/my.cnf MySQL主从分离实现

按esc，输入 :wq，保存并退出my.cnf，启动mysqld # service mysqld start 查看mysql是否启动成功 # ps -ef|grep mysql 出现如下信息表示启动成功 MySQL主从分离实现

当然也可以查看mysqld的状态 # service mysqld status
8. 登录mysql并修改root密码
# cd /usr/local/mysql
# ./bin/mysql -uroot –p
输入初始密码，步骤6中有生成，出现如下信息表示登录成功 MySQL主从分离实现

修改root密码
mysql> SET PASSWORD = PASSWORD('123456');
mysql> FLUSH PRIVILEGES;
初次登录没有修改root的密码，操作数据库会出现如下错误提示，那么需要修改root用户的密码 ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
9. 设置mysql远程可访问（前提是防火墙必须关闭，chkconfig iptables off:设置自动启动为关闭，service iptables stop:关闭防火墙）
先登录到mysql
mysql> use mysql
mysql> update user set host = '192.168.0.148' where user = 'root';
mysql> FLUSH PRIVILEGES;
192.168.0.148即是可远程访问本地mysql的远程ip，若想任意ip都能访问本地mysql，那么只需要将192.168.0.148换成%即可 mysql> update user set host = '%' where user = 'root'; MySQL主从分离实现

MySQL主从库配置

本文将ip为192.168.0.188的主机作为master，ip为192.168.0.152的主机作为slave，我用的mysql版本为：5.7.31。当然我们也可以在本机下载两套mysql软件，设置不同端口后进行启动，也是可以配置出主从同步来。
1. master上配置bin-log日志
修改mysql的配置文件：
# vim /etc/my.cnf，加上如下内容，修改配置后需要重启mysql服务器

log-bin= master-bin  #[必须]启用二进制日志
log-bin-index=master-bin.index
server-id         = 4          #[必须]服务器唯一ID，默认是1，最好取ip的后3位
expire-logs-days  = 7           #只保留7天的二进制日志，以防磁盘被日志占满
binlog-ignore-db  = mysql       #不备份的数据库
binlog-ignore-db  = information_schema
binlog-ignore-db  = performation_schema
binlog-ignore-db  = sys
binlog-do-db=o2o #需要做复制的数据库名

测试log_bin是否成功开启 mysql> show variables like '%log_bin%'; 出现下图，log_bin为ON则表示开启成功，OFF表示开启失败 MySQL主从分离实现

配置好后，查看主服务器状态：show master status MySQL主从分离实现

2. slave上配置relay-log配置从服务器:vim /etc/my.cnf，点击 i 进入编辑模式，添加如下配置：

relay-log-index=slave-relay-bin.index
relay-log=slave-bin
server-id=152 #只要和master的server-id不一样就可以了

master的数据库中建立备份账号 repl为用户名，192.168.0.152代表从数据库的主机地址，如下表示远程地址为密码为123的，用户名是repl就可以连接到master主机，并为其授予 replication slave 权限。123为master的连接密码。mysql> create user repl; # 创建用户名为repl的用户 mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.152' IDENTIFIED BY '123'; mysql> FLUSH PRIVILEGES; mysql> use mysql; mysql> select user,authentication_string,host from user; 可看到我们刚创建的备份账号：

重启MySQL服务并设置读取锁定:登录mysql]，mysql> flush tables with read lock;读取锁定的意思是只能读取，不能更新，以便获得一个一致性的快照查看主服务器上当前的二进制日志名和偏移量值： MySQL主从分离实现

4. 我们已经在主库配置好了允许从库访问主库的用户名和密码，下面我们对slave进行操作：mysql> change master to master_host='192.168.0.188',master_port=3306,master_user='repl',master_password='123',master_log_file='master-bin.000001',master_log_pos=154; 其中的master_log_file，就是主库中file，在主库中使用 show master status；就可以看到了。 MySQL主从分离实现

启动slave: mysql> start slave; 查看slave从机的状态(\G表示竖着展示): mysql> show slave status \G MySQL主从分离实现

5. 关闭掉主数据库的读取锁定mysql> unlock tables;6. 测试才开始的时候，我的主库和从库中的数据库是相同的，使用show databases查看，如下: MySQL主从分离实现我现在主库执行创建库语句，执行：create database o2o; 我们去从库查询，会发现该数据库会自动同步到从库，如下:

通过测试验证，我们的mysql主从同步就配置好了，这里需要注意的是主库的mysql版本不能高于从库的mysql版本，因为MySQL总的语法是高版本兼容底版本的，如果从库的版本要比主库的版本低，可能主库执行的sql语句，从库执行就会报错，导致主从同步失败；从库只能读数据，不能在从库写数据，主库既可以读数据，也可以写数据。

vlambda博客
学习文章列表