MySQL数据库主从复制与读写分离
一 应用场景
在实际的生产环境中，如果对数据库的读和写都在同一个数据库服务器中操作，无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，一般来说都是通过主从复制（Master-Slave）来同步数据，再通过读写分离来提升数据库并发负载能力的方案来进行部署与实施。
在企业网站中，如果后端MySQL数据库只有一台时，会有以下问题：
1.单点故障，服务不可用
2.无法处理大量的并发数据请求
3.数据丢失
改造方法
增加MySQL数据库服务器，对数据进行备份，形成主备
确保主备MySQL数据库服务器数据是一样的
主服务器宕机了，备份服务器继续工作，数据有保障
更高级解决方案——主从复制
通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。
二 MySQL主从复制原理
主从复制和读写分离两者有着紧密联系，首先要部署主从复制，只有主从复制完成了，才能在此基础上进行数据的读写分离。
2.1 MySQL支持的复制类型
1.基于语句的复制。在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。 MySQL 默认采用基于语句的复制，效率比较高。
2.基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍。
3.混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。
2.2 复制的工作过程
1.在每个事务更新数据完成之前，Master将这些改变记录进二进制日志。写入二进制日志完成后，Master通知存储引擎提交事务。
2.Slave将Master的Binary log复制到其中继日志（Relay log）。首先，Slave 开始一个工作线程——I/O 线程，I/O 线程在Master上打开一个普通的连接，然后开始 Binlog dump process。Binlog dump process从 Master的二进制日志中读取事件，如果已经跟上Master，它会睡眠并等待Master产生新的事件。I/O线程将这些事件写入中继日志。
3.SQL slave thread（SQL 从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新Slave 数据，使其与Master中的数据保持一致。只要该线程与 I/O 线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。
三 案例实施
实验环境：
使用一台主服务器（20.0.0.11），一台从服务器（20.0.0.12）
3.1 建立时间同步环境
1.在主机Master搭建时间同步服务器NTP (20.0.0.11)
[root@localhost ~]# yum -y install ntp
[root@localhost ~]# vi/etc/ntp.conf
插入下面两行内容
server 127.127.1.0
fudge 127.127.1.0 stratum 8
重启服务
[root@localhost ~]# service ntpd restart
[root@localhost ~]# systemctl restart ntpd
[root@localhost ~]# systemctl enable ntpd
2.在从服务器上配置NTP同步（20.0.0.12）
[root@localhost ~]# yum -y install ntpdate
[root@localhost ~]# ntpdate 20.0.0.11
3.从服务器（20.0.0.12）添加计划任务
[root@localhost ~]# crontab -e
*/2 * * * * /usr/sbin/ntpdate 20.0.0.11 >>/var/log/ntpdate.log
[root@localhost ~]# systemctl restart crond
[root@localhost ~]# systemctl enable crond
动态查看更新日志文件
[root@localhost~]# tail -f/var/log/ntpdate.log
3.2 编译安装MySQL
1.安装MySQL环境依赖包
yum -y install \
gcc \
gcc-c++ \
make \
ncurses \
ncurses-devel \
bison \
cmake
ncurses：字符终端函数库
bison：c++数据库解析器
2.创建运行用户
useradd -s /sbin/nologin mysql
3.编译安装
上传mysql-boost-5.7.20.tar.gz到opt目录下
cd /opt
tar xf mysql-boost-5.7.20.tar.gz
cd /opt/mysql-5.7.20/
cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DSYSCONFDIR=/etc \
-DSYSTEMD_PID_DIR=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DWITH_BOOST=boost \
-DWITH_SYSTEMD=1
make &&make install
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql ：指定安装路径
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock ：socket通讯文件
-DSYSCONFDIR=/etc ：配置文件目录
-DSYSTEMD_PID_DIR=/usr/local/mysql ：指定PID进程目录位置
-DDEFAULT_CHARSET=utf8 ：指定字符集
-DDEFAULT_COLLATION=utf8_general_ci ：字符集规则
-DWITH_INNOBASE_STORAGE_ENGINE=1 ：指定存储引擎
-DWITH_ARCHIVE_STORAGE_ENGINE=1 ：不支持任何索引，但使用查询缓存的存储引擎
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 ：黑洞引擎，写入的任何数据都会消失，用于记录binlog做复制的中继存储
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 ：mysql的运行时状态记录的存储引擎
-DMYSQL_DATADIR=/usr/local/mysql/data ：指定数据放置目录
-DWITH_BOOST=boost ：C++程序库的总称
-DWITH_SYSTEMD=1 ：ID号
4.改属主和属组
chown -R mysql:mysql /usr/local/mysql/
5.调整配置文件
vi /etc/my.cnf
[client]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock
[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES
6.改属主和属组
chown mysql:mysql /etc/my.cnf
7.设置环境变量
echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile
echo 'export PATH' >> /etc/profile
刷新一下环境变量，使其生效
[root@localhost ~]# source /etc/profile
8.初始化数据库
cd /usr/local/mysql/
bin/mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data
cp usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/
9.数据库开机自启、开启、状态
systemctl enable mysqld
systemctl start mysqld
systemctl status mysqld
10.查看服务是否开启
netstat -anpt | grep 3306
若无此条命令，可执行 yum -y install net-tools命令安装
11.设置数据库密码
mysqladmin -u root -p password
3.3 配置主服务器
1.把server-id = 1改成11
vi /etc/my.cnf
server-id = 11
log_bin = master-bin
log-slave-updates = true
:wq
重启数据库
systemctl restart mysqld
2.登录master数据库，给从服务器授权
mysql> grant replication slave on *.* to 'myslave'@'20.0.0.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.06 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 599 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
3.4 配置从服务器
1.安装MySQL数据库，步骤同上。
2.登录从服务器（20.0.0.12），修改/etc/my.cnf配置文件
server-id = 22
relay-log=relay-log-bin
relay-log-index = slave-relay-bin.index
:wq
重启数据库
systemctl restart mysqld
3.登录slave数据库，配置同步(注意要和刚才查看的master数据库信息一致)
mysql> change master to master_host='20.0.0.11',master_user='myslave',master_password='123',master_log_file='mysql-bin.000005',master_log_pos=599;
Query OK, 0 rows affected, 2 warnings (0.07 sec)
mysql> start slave;
确保以下两个值为Yes
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
3.5 验证主从复制效果
1.在主、从服务器上登录MySQL；在主服务器上新建数据库luffy
mysql> create database luffy;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| luffy |
| mysql |
| performance_schema |
| sys |
+--------------------+
2.登录从服务器MySQL进行查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| luffy |
| mysql |
| performance_schema |
| sys |
+--------------------+
实验成功。
四 MySQL读写分离
4.1 原理
主服务器写，从服务器读，基本的原理是让主数据库处理事务性查询，从数据库处理Select查询。数据库复制被用来把事务性查询导致的变更同步到群集中的从数据库。
4.2 实现方式
1.基于程序代码内部实现 在代码中根据 select、insert 进行路由分类，这类方法也是目前生产环境应用最广泛的。
优点是性能较好，因为在程序代码中实现，不需要增加额外的设备作为硬件开支；缺点是需要开发人员来实现，运维人员无从下手。
2.基于中间代理层实现 代理一般位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后 端数据库，有两个代表性程序
（1）MySQL-Proxy。MySQL-Proxy 为 MySQL 开源项目，通过其自带的 lua 脚本进行 SQL 判断，虽然是 MySQL 官方产品，但是 MySQL 官方并不建议将 MySQL-Proxy 用到生产环境。
（2）Amoeba。由陈思儒开发，作者曾就职于阿里巴巴。该程序由 Java 语言进行开发， 阿里巴巴将其用于生产环境。它不支持事务和存储过程。
4.3 案例环境
主机 操作系统 IP地址 主要软件
amoeba centos-7.4-X86_64 20.0.0.13 amoeba-mysql-3.0.5-RC-distribution.zip
jdk-8u144-linux-x64.tar.gz
Master centos-7.4-X86_64 20.0.0.11 mysql-boost-5.7.20.tar.gz ntp
Slave1 centos-7.4-X86_64 20.0.0.12 mysql-boost-5.7.20.tar.gz ntpdate
客户机 centos-7.4-X86_64 20.0.0.100 mysql-boost-5.7.20.tar.gz
注意
master、slave1关闭防火墙与核心防护
amoeba服务器所需软件包
jdk-6u14-linux-x64.bin
amoeba-mysql-binary-2.2.0.tar.gz
4.4 amoeba服务器搭建
4.4.1 卸载原有java环境
[root@localhost ~]# java -version #确定java版本 1.8.0_181的 有点高
openjdk version "1.8.0_181"
OpenJDK Runtime Environment (build 1.8.0_181-b13)
OpenJDK 64-Bit Server VM (build 25.181-b13, mixed mode)
[root@localhost ~]# rpm -qa |grep java
查出openjdk相关的文件并且删除它
[root@localhost ~]# rpm -e --nodeps java-1.8.0-openjdk-headless-1.8.0.181-7.b13.el7.x86_64
[root@localhost ~]# rpm -e --nodeps java-1.8.0-openjdk-1.8.0.181-7.b13.el7.x86_64
[root@localhost ~]# rpm -e --nodeps java-1.7.0-openjdk-1.7.0.191-2.6.15.5.el7.x86_64
[root@localhost ~]# rpm -e --nodeps java-1.7.0-openjdk-headless-1.7.0.191-2.6.15.5.el7.x86_64
4.4.2 安装jdk
上传JDK到opt目录
[root@localhost ~]# cd /opt
[root@localhost opt]# tar xzvf jdk-8u144-linux-x64.tar.gz
[root@localhost opt]# cp -rv jdk1.8.0_144/ /usr/local/java
[root@localhost opt]# vi /etc/profile
export JAVA_HOME=/usr/local/java
export JRE_HOME=/usr/local/java/jre
export PATH=$PATH:/usr/local/java/bin
export CLASSPATH=./:/usr/local/java/lib:/usr/local/java/jre/lib
[root@localhost opt]# source /etc/profile
[root@localhost opt]# java -version java环境变成1.8.0_144
4.4.3 安装amoeba
资源获取地址：（https://sourceforge.net/projects/amoeba/files/）
[root@localhost opt]# yum -y install unzip #如果unzip命令没有，必须装下
[root@localhost opt]# unzip amoeba-mysql-3.0.5-RC-distribution.zip -d /usr/local/
[root@localhost opt]# mv /usr/local/amoeba-mysql-3.0.5-RC/ /usr/local/amoeba
[root@localhost opt]# chmod -R 755 /usr/local/amoeba/
[root@localhost opt]#vi /usr/local/amoeba/jvm.properties
JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize=16m -XX:MaxPermSize=96m" #32行修改成如下
JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k"
4.4.4 制作amoeba管理脚本
[root@localhost opt]# vi /etc/init.d/amoeba
#!/bin/bash
#chkconfig: 35 62 62
#
export JAVA_HOME=/usr/local/java
export PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH
NAME=Amoeba
AMOEBA_BIN=/usr/local/amoeba/bin/launcher
SHUTDOWN_BIN=/usr/local/amoeba/bin/shutdown
PIDFILE=/usr/local/amoeba/Amoeba-MySQL.pid
SCRIPTNAME=/etc/init.d/amoeba
case "$1" in
start)
echo -n "Starting $NAME... "
$AMOEBA_BIN
echo " done"
;;
stop)
echo -n "Stoping $NAME... "
$SHUTDOWN_BIN
echo " done"
;;
restart)
$SHUTDOWN_BIN
sleep 1
$AMOEBA_BIN
;;
*)
echo "Usage: $SCRIPTNAME {start|stop|restart}"
exit 1
;;
esac
:wq
[root@localhost opt]# chmod +x /etc/init.d/amoeba
[root@localhost opt]# chkconfig --add amoeba
[root@localhost opt]# service amoeba start
ctrl + c
[root@localhost opt]# netstat -anpt | grep 8066 //默认监听在8066端口
4.4.5 在两台MySQL数据库中为amoeba授权
登录20.0.0.11终端
[root@localhost ~]# mysql -u root -p #输入密码
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
mysql> GRANT ALL ON *.* TO test@'20.0.0.%' IDENTIFIED BY '123';
mysql> FLUSH PRIVILEGES;
mysql> quit
登录20.0.0.12终端
[root@localhost ~]# mysql -u root -p #输入密码
mysql> GRANT ALL ON *.* TO test@'20.0.0.%' IDENTIFIED BY '123';
mysql> FLUSH PRIVILEGES;
mysql> quit
4.4.6 修改amoeba的两个配置文件
- amoeba.xml
- dbServers.xml
amoeba.xml：用来控制读写分离
[root@localhost opt]# cd /usr/local/amoeba
[root@localhost amoeba]# vi conf/amoeba.xml
#修改如下参数
28 <property name="user">amoeba</property>
29
30 <property name="password">123</property>
--83-去掉注释-同时把默认的server1改成master,把默认的servers改成 slaves
83 <property name="defaultPool">master</property>
84
85
86 <property name="writePool">master</property>
87 <property name="readPool">slaves</property>
dbServers.xml：用来跳转数据库
[root@localhost amoeba]# vi conf/dbServers.xml
23 <property name="schema">test</property>
26 <property name="user">test</property>
27
28 <property name="password">123</property>
43 <dbServer name="master" parent="abstractServer">
44 <factoryConfig>
45 <!-- mysql ip -->
46 <property name="ipAddress">20.0.0.11</property>
47 </factoryConfig>
48 </dbServer>
49
50 <dbServer name="slave1" parent="abstractServer">
51 <factoryConfig>
52 <!-- mysql ip -->
53 <property name="ipAddress">20.0.0.12</property>
54 </factoryConfig>
55 </dbServer>
63 <property name="poolNames">slave1</property>
重启服务
[root@localhost amoeba]# service amoeba restart
[root@localhost amoeba]# netstat -anpt | grep java
4.5 登录客户端测试
测试用客户端 20.0.0.100
装上数据库
[root@localhost ~]# mysql -u amoeba -p123 -h 20.0.0.13 -P8066
在主mysql(20.0.0.11)上创建数据库
[root@localhost ~]# mysql -u root -p
mysql> use test;
Database changed
mysql> create table zhang1 (id int(10),name varchar(10),address varchar(20));
在从mysql(20.0.0.12)上关闭同步
[root@localhost ~]# mysql -u root -p
mysql> stop slave;
mysql> use test;
Database changed
mysql> insert into zhang1 values('2','zhang','this_is_slave1');
在主mysql(20.0.0.11)上插入数据
[root@localhost ~]# mysql -u root -p
mysql> use test;
Database changed
mysql> insert into zhang1 values('1','zhang','this_is_master');
登录客户端(20.0.0.100)
[root@localhost ~]# mysql -u amoeba -p123 -h 20.0.0.13 -P8066
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db_test |
| mysql |
| performance_schema |
| sys |
| test | 发现数据库test
+--------------------+
在客户机上20.0.0.100继续写数据，按照正常的规则应该是slave1看不见的
MySQL [(none)]> use test;
Database changed
MySQL [test]> insert into zhang1 values('4','zhang','write_test');
Query OK, 1 row affected (0.01 sec)
登录mastar(20.0.0.11)
[root@localhost ~]# mysql -u root -p
mysql> use test;
Database changed
mysql> select * from zhang1;
+------+-------+----------------+
| id | name | address |
+------+-------+----------------+
| 1 | zhang | this_is_master |
| 4 | zhang | write_test |
+------+-------+----------------+
2 rows in set (0.00 sec)
登录slave1(20.0.0.12)
[root@localhost ~]# mysql -u root -p
mysql> use test;
Database changed
mysql> select * from zhang1;
+------+-------+----------------+
| id | name | address |
+------+-------+----------------+
| 2 | zhang | this_is_slave1 |
+------+-------+----------------+
1 row in set (0.00 sec)
验证结果:
在客户端（20.0.0.100）上操作写入数据，数据是往master上写的，在salve1上看不到写的数据
扫一扫
1406
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
