MySQL主从复制读写分离

MySQL简介

MySQL是最流行的开放源码 SQL数据库管理系统,它是由 MySQL AB 公司开发、发布并支持的.它的插入式存储引擎可以让使用者根据实际应用使用分歧的存储.

生产环境中应：

MySQL源码安装+LVM逻辑卷

虚拟机测试环境,添加一块硬盘

重启电脑 创立一个分区

[root@localhost6 ~]# fdisk /dev/sdb

Device Boot Start End Blocks Id System

/dev/sdb1 1 3917 31463271 8e Linux LVM [root@localhost6 ~]# partprobe /dev/sdb #安装法式是parted

创立一个卷组 VG

查看卷组vgdisplay mysqlvg

[root@localhost6 ~]# vgcreate mysqlvg /dev/sdb1

No physical volume label read from /dev/sdb1

Physical volume /dev/sdb1 not found

Physical volume "/dev/sdb1" successfully created

Volume group "mysqlvg" successfully created

创立一个逻辑卷 LV

[root@localhost6 ~]# lvcreate -L 10G -n mysqllv mysqlvg

Logical volume "mysqllv" created

lvdisplay /dev/mysqlvg/mysqllv --查看

[root@localhost6 ~]# lvextend -L +3G /dev/mysqlvg/mysqllv 动态增加3G空间

Extending logical volume mysqllv to 13.00 GiB

Logical volume mysqllv successfully resized

格式化逻辑卷并挂载

[root@localhost6 ~]# mkfs -t ext4 /dev/mysqlvg/mysqllv

[root@localhost6 ~]# mkdir -p /usr/local/mysql/data

[root@localhost6 ~]# mount /dev/mysqlvg/mysqllv /usr/local/mysql/data

实现开机自动挂载

[root@localhost6 ~]# vim /etc/fstab

/dev/mysqlvg/mysqllv /usr/local/mysql/data ext4 defaults 0 0

[root@localhost6 ~]# mount -a

安装MySQL数据库准备

[root@localhost6 ~]# ls

cmake-2.8.12.tar.gz mysql-5.7.2-m12.tar.gz

安装前确认主从服务器时间一致,全部配置时间同步服务器

ntpdate 202.112.31.197 中国教育网的时间

安装cmake

[root@localhost6 ~]# tar xf cmake-2.8.12.tar.gz -C /usr/src/

[root@localhost6 ~]# cd /usr/src/cmake-2.8.12/

[root@localhost6 cmake-2.8.12]# ./configure && gmake && gmake install

安装MySQL

[root@localhost6 ~]# tar xf mysql-5.7.2-m12.tar.gz -C /usr/src/

[root@localhost6 ~]# cd /usr/src/mysql-5.7.2-m12/

[root@localhost6 mysql-5.7.2-m12]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DSYSCONFDIR=/etc && make && make install

[root@localhost6 mysql-5.7.2-m12]# cp support-files/my-default.cnf /etc/my.cnf

cp：是否覆盖"/etc/my.cnf"? y

[root@localhost6 mysql-5.7.2-m12]# cp support-files/mysql.server /etc/init.d/mysqld

[root@localhost6 mysql-5.7.2-m12]# chmod +x /etc/init.d/mysqld

[root@localhost6 mysql-5.7.2-m12]# chkconfig --add mysqld

[root@localhost6 mysql-5.7.2-m12]# chkconfig --list mysqld

mysqld 0:关闭1:关闭2:启用3:启用4:启用5:启用6:关闭

[root@localhost6 mysql-5.7.2-m12]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile

[root@localhost6 mysql-5.7.2-m12]# . /etc/profile

[root@localhost6 mysql-5.7.2-m12]# groupadd mysql

[root@localhost6 mysql-5.7.2-m12]# useradd -M -s /sbin/nologin -g mysql mysql

[root@localhost6 mysql-5.7.2-m12]# chown -R mysql:mysql /usr/local/mysql/

[root@localhost6 mysql-5.7.2-m12]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data --user=mysql

[root@localhost6 mysql-5.7.2-m12]# service mysqld start

Starting MySQL. SUCCESS!

[root@localhost6 mysql-5.7.2-m12]# mysqladmin -u root password '123123'

[root@localhost6 mysql-5.7.2-m12]# mysql -u root -p123123

在实际生产环境中,如果对数据库的读和写都在同一个数据库服务器中操作,无论是在平安性,高可用还是高并发等各个方面都不能完全满足实际需求的,因此一般来说都是通过主从复制(Master-Slave)的方式来同步数据,在通过读写分离来提升数据的高并发负载能力这样的方案来进行部署.

MYISAM 读写操作相互 互斥 (磁盘的IO限制,读与写不克不及同时进行)

MySQL Replication 俗称MySQL AB复制(主从,双机热备),从库以一定的频率去读取主库的二进制日志文件,依照日志中记录对从库进行同样的操作,以达到同步效果.

MySQL 支持的复制类型

基于语句的复制在主服务器上执行的SQL语句,在从服务器上执行同样的语句,MySQL默认采用基于语句的复制,效率比拟高

基于行的复制把改变的内容复制过去,而不是把命令在从服务器上执行一遍

混合类型的复制默认采纳基于语句的复制,一旦发现基于语句无法精确复制时,就会采纳行

mysql 5.6 以后多了一个GTIDS复制

复制的工作过程

1.主库在每个事务更新完数据完成之前,Master在二进制日志(Binary log,binlog)记录这些改变,写入二进制日志完成后,Master通知存储引擎提交事务

2.Slave将Master的Binary log复制到其中中继日志,首先Slave开始一个工作线程-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的缓存中,所以中继日志的开销很小.

复制过程的限制

1.即复制在Slave上是串行化的,也便是说Master上的并行更新不能在Slave上并行操作

2.MYSQL的版本都要高于3.2,还有一个基本的原则便是作为从数据库的数据库版本可以高于主服务器数据库的版本,但是不可以低于主服务器的数据库版本

从库以一定的频率去读取主库的二进制日志文件,依照日志中记录对从库进行同样的操作,以达到同步效果.

MySQL Master IP: 192.168.8.60

MySQL Slave IP: 192.168.8.70

Amoeba IP: 192.168.8.80

每台机器操作：

[root@localhost6 ~]# service iptables stop

iptables：将链设置为政策 ACCEPT：filter [确定]

iptables：清除防火墙规则： [确定]

iptables：正在卸载模块： [确定]

[root@localhost6 ~]# setenforce 0

setenforce: SELinux is disabled

[root@localhost6 ~]# service sshd restart

停止 sshd： [确定]

正在启动 sshd： [确定]

配置mysql master服务器

[root@localhost6 ~]# vim /etc/my.cnf

[mysqld]

server-id=1 依次设置server-id号

log-bin=mysql-binlog

log-slave-updates=true

[root@localhost6 ~]# /etc/init.d/mysqld stop

Shutting down MySQL.. SUCCESS!

[root@localhost6 ~]# /etc/init.d/mysqld start

Starting MySQL. SUCCESS!

创立Replication用户

mysql> grant replication slave on *.* to 'myslave'@'192.168.8.%' identified by '123123';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

replication slave：用于复制型从属服务器(从主服务器中读取二进制日志变乱)

获得Master DB的相关信息

mysql> show master status;

+---------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+---------------------+----------+--------------+------------------+-------------------+

| mysql-binlog.000001 | 428 | | | |

+---------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

供Slave DB连接时使用,记录下File和Position的值

如果一个数据库里面有数据,现在要加一个从服务器需将数据一致后再配置服务器AB复制

mysqldump -u root -p123123 --all-databases > /root/alldbbackup.sql 导出数据

scp /root/alldbbackup.sql 传到从数据库

mysql -u root -p < /root/alldbbackup.sql 导入备份

从服务器slave配置MySQL配置文件

[root@localhost7 ~]# vim /etc/my.cnf

[mysqld]

server-id=2

relay-log=relay-log-bin

relay-log-index=slave-relay-bin.index

[root@localhost7 ~]# /etc/init.d/mysqld stop

Shutting down MySQL.. SUCCESS!

[root@localhost7 ~]# /etc/init.d/mysqld start

Starting MySQL. SUCCESS!

mysql> stop slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST='192.168.8.60', MASTER_USER='myslave', MASTER_PASSWORD='123123', MASTER_LOG_FILE='mysql-binlog.000001', MASTER_LOG_POS=428;

Query OK, 0 rows affected, 2 warnings (0.17 sec)

参数阐明：

mysql> CHANGE MASTER TO

-> MASTER_HOST='master_host_name', //主服务器的IP地址

-> MASTER_USER='replication_user_name', //同步数据库的用户

-> MASTER_PASSWORD='replication_password', //同步数据库的暗码

-> MASTER_LOG_FILE='recorded_log_file_name', //主服务器二进制日志的文件名(前面要求记录的参数)

-> MASTER_LOG_POS=recorded_log_position; //日志文件的开始位置(前面要求记录的参数)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.8.60

Master_User: myslave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-binlog.000001

Read_Master_Log_Pos: 428

Relay_Log_File: relay-log-bin.000002

Relay_Log_Pos: 286

Relay_Master_Log_File: mysql-binlog.000001

Slave_IO_Running: Yes 显示为两个Yes即配置与连接没问题

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 428

Relay_Log_Space: 457

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: 39dc0a9f-96b5-11e7-aeee-000c292a38b6

Master_Info_File: /usr/local/mysql/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp: 如果上面显示的不是两个Yes,这里会显示哪里出了

Last_SQL_Error_Timestamp: 问题,网络、IO&middot;&middot;&middot;

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

在主master数据库上创立一个数据库testdb,在从slave数据库上进行查看

mysql> create database testdb;

Query OK, 1 row affected (0.01 sec)

从数据库有,即主从同步(AB复制)数据库已完成

报错解决办法：

ERROR 1201(HY000):Could now initialize master info structure; more error messages can be found in the MySQL error log

stop slave;

reset slave;

CHANGE MASTER TO MASTER_HOST='192.168.8.60', MASTER_USER='slave', MASTER_PASSWORD='123123',MASTER_LOG_FILE='mysql-binlog.000003', MASTER_LOG_POS=246;

start slave;

停止slave角色：

mysql> stop slave;

重置slave角色：

mysql> reset slave;

启动slave角色：

mysql> start slave;

数据不同步解决方法：

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

mysql> set global sql_slave_skip_counter=1;

Query OK, 0 rows affected (0.00 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

主从数据库相关命令：

slave stop; slave start; 开始停止从数据库.

show slave status\G显示从库状态信息

show master status\G显示主库状态信息

purge master logs to &rsquo;binlog.000004&rsquo;; 此命令非常小心,删除主数据库没用的二进制日志文件.如果误删除,那么从库就没有方法自动更新了.

change master；从服务器上修改参数使用

另外,如果你当前操作的从库以前曾经与其他服务器建立过主从关系,你可能会发现即使你在my.cnf文件中即便变动了主服务器的位置,但是MSQL仍然

在试图连接就旧的主服务器的现象.产生这种问题的时候,我们可以通过清除master.info这个缓存文件或者在mysql中通过命令来进行设置.方式如下：

删除master.info办法

这个文件位于数据文件存放目录里,可以直接将其删除,然后重新启动服务器.

b、mysql命令办法

如果你不方便重新启动服务器的话,那么就只能使用mysql命令来赞助你做到.

首先登录到主服务器上,查看当前服务器状态：

mysql> show master status\G;

+---------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+---------------+----------+--------------+------------------+

| mysql-bin.003 | 73 | test | manual,mysql |

+---------------+----------+--------------+------------------+

1.FLUSH TABLES WITH READ LOCK

这个命令是全局读锁定,执行了命令之后所有库所有表都被锁定只读.一般都是用在数据库联机备份,这个时候数据库的写操作将被阻塞,读操作顺利进行

解锁的语句是unlock tables

2.LOCK TABLES tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}

这个命令是表级其余锁定,可以定制锁定某一个表.例如： lock tables test read; 不影响其他表的写操作.

解锁语句也是unlock tables.

这两个语句在执行的时候都需要注意个特点,在退出mysql终端的时候都会隐式的执行unlock tables.也就是如果要让表锁定生效就必须一直坚持对话

P.S. MYSQL的read lock和wirte lock

read-lock: 允许其他并发的读哀求,但阻塞写哀求,即可以同时读,但不允许任何写.也叫共享锁

write-lock: 不允许其他并发的读和写哀求,是排他的(exclusive).也叫独占锁

3. flush table tablename

目前较为常见的MySQL读写分离有两种：

1.基于法式代码内部实现

在代码中根据select,insert进行路由分类,这类办法也是目前生产环境应用最广泛的,优点是性能好,因为在程序代码中实现,不需要曾加额外的设备作为硬件开支,缺点是需要开发人员来实现,运维人员无从下手.

2.基于中间代理层实现

代理一般位于客户端和服务器之间,代理服务器接到客户端哀求后通过判断后转发到后端数据库,有两个代表性程序.

(1)mysql-proxy 为mysql开源项目,通过其自带的lua脚本进行SQL判断,虽然是mysql的官方产物,但是mysql官方不建议将其应用到生产环境

(2)Amoeba (变形虫)由陈思儒开发,曾就职与阿里巴巴,该法式由java语言进行开发,阿里巴巴将其应用于生成环境,它不支持事物和存储过程通过法式代码实现mysql读写分离自然是一个不错的选择,但是并不是所有的应用都适合在法式代码中实现读写分离,像一些大型复杂的java应用,如果在法式代码中实现读写分离对代码改动就较大,像这种应用一般会考虑使用代理层来实现.

Amoeba (变形虫)项目开源框架于2008年发布一款Amoeba for mysql软件,这个软件致力于mysql的分布式数据库前端代理层,主要为应用层访问mysql的时候充当SQL路由功能,并具有负载均衡,高可用性,SQL过滤,读写分离,可路由到相关的目标数据库,可并发哀求多台数据库,通过Amoeba能够完成多数据源的高可用,负载均衡,数据切片的功能,目前Amoeba已经在很多企业的生产线上使用.

因为Amoeba基于jdk1.5开发的,所以官方保举使用jdk1.5 或者1.6版本,高版本不建议使用

.bin 通用二进制格式

http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html

[root@localhost8 ~]# ls

amoeba-mysql-binary-2.2.0.tar.gz jdk-6u31-linux-x64.bin

[root@localhost8 ~]# chmod +x jdk-6u31-linux-x64.bin

[root@localhost8 ~]# ./jdk-6u31-linux-x64.bin

[root@localhost8 ~]# mv jdk1.6.0_31/ /usr/local/java

[root@localhost8 ~]# vim /etc/profile

export JAVA_HOME=/usr/local/java

export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib

export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$PATH:$HOME/bin

export AMOEBA_HOME=/usr/local/amoeba

export PATH=$PATH:$AMOEBA_HOME/bin

[root@localhost8 ~]# java -version

java version "1.6.0_31"

Java(TM) SE Runtime Environment (build 1.6.0_31-b04)

Java HotSpot(TM) 64-Bit Server VM (build 20.6-b01, mixed mode)

安装Amoeba

https://sourceforge.net/projects/amoeba/

[root@localhost8 ~]# mkdir /usr/local/amoeba

[root@localhost8 ~]# tar xf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/

[root@localhost8 ~]# chmod -R 755 /usr/local/amoeba

[root@localhost8 ~]# /usr/local/amoeba/bin/amoeba

amoeba start|stop 显示此即为安装胜利

配置Amoeba读写分离,写主,读从

Master,Slave中配置放权给Amoeba拜访

mysql> grant all on *.* to 'amoeba'@'192.168.8.%' identified by 'elements123';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

配置文件比较多位于/usr/local/amoeba/conf下,但是仅仅使用读写分离功能,只需配置两个文件即可,分别是dbServers.xml和amoeba.xml,如果需要配置ip拜访控制,还需要修改access_list.conf文件,下面首先介绍dbServers.xml

配置amoeba.xml

[root@localhost8 ~]# cp /usr/local/amoeba/conf/amoeba.xml{,.bak}

[root@localhost8 ~]# vim /usr/local/amoeba/conf/amoeba.xml

11 <property name="port">8066</property>

#设置amoeba监听的端口,默认是8066

15 <property name="ipAddress">127.0.0.1</property>

#配置监听的接口,如果不设置,默认监听所有的IP

30 <property name="user">root</property>

31提供客户端连接amoeba时需要使用这里设定的账号 (这里的账号暗码和amoeba连接后端数据库服务器的暗码无关)

32 <property name="password"> elements </property>

115 <property name="defaultPool">master</property>

116

117 <!-- 将注释去掉

118 <property name="writePool">master</property>

119 <property name="readPool">slave</property>

120 --> 将注释去掉

：wq

[root@localhost8 ~]# cp /usr/local/amoeba/conf/dbServers.xml{,.bak}

[root@localhost8 ~]# vim /usr/local/amoeba/conf/dbServers.xml

20 <property name="port">3306</property>

#设置Amoeba要连接的mysql数据库的端口,默认是3306

23 <property name="schema">test</property>

#设置缺省的数据库,当连接amoeba时,操作表必需显式的指定数据库名,即采用dbname.tablename的方式,不支持 use dbname指定缺省库,因为操作会调度到各个后端dbserver

25 <!-- mysql user -->

26 <property name="user">amoeba</property>

27 #设置amoeba连接后端数据库服务器的账号和暗码,因此需要在所有后端数据库上创建该用户,并授权amoeba服务器可连接 此步骤上面已经做过授权

28 <property name="password">elements123</property>

32 <property name="maxActive">500</property> #最大连接数,默认500

33 <property name="maxIdle">500</property> #最大空闲连接数

34 <property name="minIdle">10</property> #最新空闲连接数

43 <dbServer name="master" parent="abstractServer"> #设置一个后端可写的dbServer,这里必需设置为master,这个名字上面已经声明 写master读slave

46 <property name="ipAddress">192.168.8.60</property> #设置后端可写dbserver

50 <dbServer name="slave" parent="abstractServer"> #设置后端可读dbserver

51 <factoryConfig>

52 <!-- mysql ip --> #后端可读

53 <property name="ipAddress">192.168.8.70</property>

57 <dbServer name="myslave" virtual="true">

#设置定义一个虚拟的dbserver,实际上相当于一个dbserver组,这里将可读的数据库IP统一放到一个组中,这里将可读的数据库IP统一放到一个组中,组名本身定,这里为myslave

58 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">

59 <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->

60 <property name="loadbalance">1</property>

#选择调度算法,1表现复制均衡,2表现权重,3表现HA

61

62 <!-- Separated by commas,such as: server1,server2,server1 -->

63 <property name="poolNames">slave</property> #myslave组成员

启动Amoeba

[root@localhost8 amoeba]# bin/amoeba start

log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml

2017-09-11 16:24:24,524 INFO context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0

log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf

2017-09-11 16:24:24,772 INFO net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066.

2017-09-11 16:24:24,778 INFO net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:2463.

[root@localhost8 ~]# netstat -anpt | grep :8066

tcp 0 0 0.0.0.0:8066 0.0.0.0:* LISTEN 2146/java

想要后台运行：

nohup /usr/local/amoeba/bin/amoeba start >> /var/log/amoeba.file 2>&1 &

查看用ps -ef 进行过滤查看 结束进程kill进程ID号

测试：

[root@localhost8 ~]# yum -y install mysql

[root@localhost8 ~]# mysql -u root -pelements -h 192.168.8.80 -P8066

删除完多余的库后

创立一个库,看从是否同步,从服务器上进行查看,也已经同步