Mysql主从复制、读写分离工作原理+配置

时间：2022-03-13 22:53

Mysql的 Replication 是一个异步的复制过程，从一个 Mysql instace(我们称之为 Master)复制到另一个 Mysqlinstance(我们称之 Slave)。在 Master 与 Slave 之间的实现整个复制过程主要由三个线程来完成，其中两个线程(Sql线程和IO线程)在 Slave 端，另外一个线程(IO线程)在 Master 端。

MySQL 复制的基本过程如下：

1. Slave 上面的IO线程连接上 Master，并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容;

2. Master 接收到来自 Slave 的 IO 线程的请求后，通过负责复制的 IO 线程根据请求信息读取指定日志指定位置之后的日志信息，返回给 Slave 端的 IO 线程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息在 Master 端的 BinaryLog 文件的名称以及在 Binary Log 中的位置;

3. Slave 的 IO 线程接收到信息后，将接收到的日志内容依次写入到 Slave 端的Relay Log文件(mysql-relay-bin.xxxxxx)的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master- info文件中，以便在下一次读取的时候能够清楚的高速Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”

4. Slave 的 SQL 线程检测到 Relay Log 中新增加了内容后，会马上解析该 Log 文件中的内容成为在 Master 端真实执行时候的那些可执行的 Query 语句，并在自身执行这些 Query。这样，实际上就是在 Master 端和 Slave 端执行了同样的 Query，所以两端的数据是完全一样的。

Mysql主从复制类型（Binlog二进制日志的格式）

①Binlog_format = statement

根据sql语句进行复制，在slave端执行相同的sql语句（如在语句中有时间类型的函数或者有随机函数的时候，slave（从）服务器执行相同语句的时候就会产生问题，因为在进行主从复制的时候，会有延迟，有时候延迟时间长短不一，这个时候在从服务器执行相同的语句，可能会得到不同的值，从而导致主从服务器数据不同步的情况）

②Binlog format = row

日志中记录每一行数据被修改的形式，然后在slave端执行相同的数据修改（row格式只是根据结果来进行更新，这样就避免了出现时间类函数或随机函数的情况，缺点：在进行大部分数据更新的时候，数据的每一次改变都会记录到日志里面，所以日志会变得非常大，导致日志过大的情况）

③ Binlog format = mixed （默认）

根据具体的sql语句来选择日志的记录形式（这种格式的特点：如果没有时间类型的函数和随机函数的时候，一般会选择statement，如果有随机函数和日期类函数的时候，就会选择row格式，所以大部分情况下都会选择灵活的mixed格式）

mysql主从复制实验拓扑图

配置步骤

1、 安装mysql服务器软件

2、 Mysql基本配置

3、 配置mysql主从服务器

1、主（master)

①Vim /etc/my.cnf

[mysql]

socket = /tmp/mysql.sock

[mysqld]

User = mysql

port = 3306

socket = /tmp/mysql.sock

server-id = 1 //主从服务器中服务器id不能相同

log-bin=mysql-bin //日志名字

binlog_format=mixed

datadir= /usr/local/mysql/var

basedir= /usr/local/mysql

slave_skip_errors = off //主从复制的时候是否开启错误跳过的功能，这一项不建议开启，如开启的话，在复制数据的过程中就会忽略错误，可能会造成数据不同步的情况，数据丢失等等

binlog_do_db= sxjy // 复制的数据库

binlog_do_db= test // 复制的数据库

binlog_ignore_db = mysql //忽略的数据库（不进行复制的数据库）这样有种缺陷，进入mysql数据库往数据库（sxjy的t1表）里插入数据 insert into sxjy.t1 values(4,‘blue‘); 将不会更新到日志里（show masterlogs; show binlog events in ‘mysql- bin.000031‘;),所以也不会更新到从服务器，导致数据不同步

log_error= /var/log/mysql/mysql-error.log

注：socket = /tmp/mysql.sock

datadir = /usr/local/mysql/var

log_error =/var/log/mysql/mysql-error.log

这三个目录需要有进行写操作，所以都要执行chown –R mysql：mysql $dir

②service mysqld start（启动数据库）

③登录数据库

Showdatabases；

2、主/从（master/slave)

①Vim /etc/my.cnf

[mysql]

socket =/tmp/mysql.sock

[mysqld]

User =mysql

port = 3306

socket =/tmp/mysql.sock

server-id = 2 //主从服务器中服务器id不能相同

log-bin=mysql-bin //日志名字

binlog_format=mixed

datadir = /usr/local/mysql/var

basedir = /usr/local/mysql

binlog_do_db = sxjy // 复制的数据库

binlog_do_db = test // 复制的数据库

replicate_do_db = sxjy

replicate_do_db = test

replicate_ignore_db = mysql

log_slave_updates = on //当设置log_slave_updates时，你可以让slave扮演其它slave的master。此时，slave把SQL线程执行的事件写进行自己的二进制日志(binary log)，然后，它的slave可以获取这些事件并执行它)。

read_only = yes

log_error = /var/log/mysql/mysql-error.log

②servicemysqld start（启动数据库）

3、从(slave)

Vim /etc/my.cnf

[mysql]

socket =/tmp/mysql.sock

[mysqld]

port            = 3306

socket          = /tmp/mysql.sock

server-id       = 3

log-bin=mysql-bin  //日志名字

binlog_format=mixed

datadir = /usr/local/mysql/var

basedir = /usr/local/mysql

slave_skip_errors = off

replicate_do_db = sxjy

replicate_do_db = test

replicate_ignore_db = mysql

read_only = on

log_error = /var/log/mysql/mysql-error.log

②servicemysqld start（启动数据库）

4、服务器授权及定义主从服务器操作

主（master)

登录mysql服务

①mysql>grant replication slave on *.* to ‘test‘@‘172.16.0.242‘ identified by ‘password‘;   //在master上为slave添加一个同步账号,授权从服务器test帐号密码复制主服务器数据

②mysql> flush privileges; //刷新权限

③mysql>show master status\G //查看主服务器状态

主/从（master/slave）

①mysql>grant replication slave on *.* to ‘test‘@‘172.16.0.242‘ identified by ‘password‘;   //在master上为slave添加一个同步账号,授权从服务器test帐号密码复制主服务器数据

②mysql> flush privileges; //刷新权限

③mysql>change master to master_host=’172.16.0.241’,master_user=’test’,master_password=’123456’,master_log_file=’sxjy-bin.000003’,master_log_pos=120,master_port=3306; //对于从服务器来说需要设置主服务器的信息

④mysql> flush privileges; //刷新权限

⑤mysql>Start slave; //启动从服务

⑥mysql>Show slave satus\G

Slave_IO_Running:YES

Slave_SQL_Running:YES //表示从服务器slave的I/O和SQL线程都已经开始运行

从(slave)

①mysql>change master tomaster_host=’172.16.0.242’,master_user=’test’,master_password=’123456’,master_log_file=’sxjy-bin.000001’,master_log_pos=120,master_port=3306;

②mysql>Start slave; //启动从服务

③mysql>Show slave satus\G

前面配置有种缺陷，进入mysql数据库往数据库（sxjy的t1表）里插入数据 insert into sxjy.t1 values(4,‘blue‘); 将不会更新到日志里（show masterlogs; show binlog events in ‘mysql- bin.000031‘;),所以也不会更新到从服务器，导致数据不同步，方案二会解决这个问题

方案二

主（master)

[mysqld]

port            = 3306

socket          = /tmp/mysql.sock

server-id       = 1

log-bin=mysql-bin  //日志名字

binlog_format=mixed

datadir = /usr/local/mysql/var

basedir = /usr/local/mysql

slave_skip_errors = off

#binlog_do_db = sxjy    //注释掉

#binlog_do_db = test    //  注释

#binlog_ignore_db = mysql    //  注释

log_error = /var/log/mysql/mysql-error.log

[mysql]
socket          =/tmp/mysql.sock
[mysqld]
User                 =mysql
port            = 3306
socket          =/tmp/mysql.sock
server-id       = 2      //主从服务器中服务器id不能相同
log-bin=mysql-bin  //日志名字
binlog_format=mixed
datadir = /usr/local/mysql/var
basedir = /usr/local/mysql
slave_skip_errors = off   
#binlog_do_db = sxjy        //  注释
#binlog_do_db = test       //  注释
#binlog_ignore_db = mysql //  注释
#replicate_do_db = sxjy    //  注释
#replicate_do_db = test    //  注释
#replicate_ignore_db = mysql  //  注释
replicate_wild_do_table =sxjy.%
replicate_wild_do_table =test.% 
replicate_wild_ignore_table =mysql.%
log_slave_updates = on    
read_only = yes
log_error = /var/log/mysql/mysql-error.log

从(slave)

[mysqld]

port            = 3306

socket          = /tmp/mysql.sock

server-id       = 2

log-bin=mysql-bin  //日志名字

binlog_format=mixed

server-id       = 3

datadir = /usr/local/mysql/var

basedir = /usr/local/mysql

slave_skip_errors = off

#replicate_do_db = sxjy         //注释掉

#replicate_do_db = test    //注释掉

#replicate_ignore_db = mysql    //注释掉

replicate_wild_do_table = sxjy.%

replicate_wild_do_table = test.%

replicate_wild_ignore_table = mysql.%

log_slave_updates = on

read_only = on

log_error = /var/log/mysql/mysql-error.log

测试mysql主从架构

主服务器上创建数据库sxjy

Mysql>Create database sxjy default character set utf8；

Mysql>Use sxjyn;

Mysql>create table t1(id int primary key auto_increment,name varchar(20)) auto_incremennt=1;

Mysql>insert into t1 values(1,‘black‘);

Mysql>select * from t1;

从服务器上查看数据库是否创建并同步

已有数据主从复制

导入导出数据方式：

①flush tables with read lock;    //主服务器上进行所标操作，由于导入导出数据时有时间差，防止主服务器写入新的数据，导致主从服务器数据不一致的情况； 这个锁表语句进行锁表只在当前会话下生效，退出则失效，所以需要另外开启连接一个终端进行数据库导出工作

②mysqldump –u root –databaseses sxjy test > /mydata.sql //将sxjy和test两个数据库导入到mydata.sql文件中

③ scp /mydata.sql //将导出的数据传输到从服务器的/目录下，如果更改了ssh的默认端口则需要-P ，如scp –P35612 /mydata.sql

需要主从服务器都要有scp命令，如从服务器没有则报错bash: scp: command notfound ，需要在从服务器yum installopenssh-clients 安装

④ mysql –u root –p </mydata.sql //从服务器上执行数据库导入操作，查看从服务器数据表信息与主服务器是否一致

⑤（主/从）服务器上重新设置一下主服务器的相关信息 mysql> change master tomaster_host=’172.16.0.241’,master_user=’test’,master_password=’123456’,master_log_file=’sxjy-bin.000004’,master_log_pos=1484,master_port=3306;

授权：Mysql>grant replication slave on *.* to ‘test’@’172.16.0.243’identifiedby ‘123456’;

Mysql>flush privileges; //只做从服务器不需要授权其它主机

⑥设置完成后再重新验证主从数据是否会同步

日志文件方式导入数据：在主服务器记录了二进制日志的情况下，可直接在从服务器上设置下就行，优点：不需要锁表，缺点：效率没有导入数据高,特别是数据量非常大的时候，

①从服务器上将mysql服务停掉，删除mysql的数据文件/usr/local/mysql/var/* ；

②/usr/local/mysql/bin/mysql_install_db --user=mysql //重新创建授权表（数据库）

③启动mysql 服务器

Mysql>stop slave;

Mysql> change master to master_host=’172.16.0.242’,master_user=’test’,master_password=’123456’,master_log_file=’sxjy-bin.000001’,master_log_pos=1,master_port=3306; //从第一个日志开始写，位置为1或120 都行

Mysql>start slave;

Mysql> show slave status\G

二进制日志进行数据恢复：

①mysqlbinlog--start-position=120 --stop-position=600 sxjy-bin.000001 //可查看二进制文件内容 , --start-position表示开始位置，--stop-position=600表示结束位置

Mysql>show master logs; //二进制文件信息

Mysql>show binlog events in ‘sxjy-bin.000001’ //文件具体内容，这样查看相对mysqlbinlog查看更加明了

② mysqlbinlog –start-datetime “2014-03-1415:50:00”—stop-datetime “2014-03-14 16:00:00”sxjy-bin.000001 | mysql –u root –p //进行二进制日志数据恢复，前提是导入的数据库和表都要存在

配置mysql读写分离服务器

利用mysql-proxy实现读写分离，已经编译好了的二进制包，不需要进行编译

官网下载：

[root@localhost src]# tar -zxvf mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz

[root@localhost src]#mv mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz/usr/local/mysql-proxy

[root@localhost bin]# cd /usr/local/mysql-proxy/bin

创建一个mysql-proxy的配置文件

[root@localhost bin]# vim /etc/myproxy.cnf

[mysql-proxy] //必须要以这个开头

plugin-dir= /usr/local/mysql-proxy/lib/mysql-proxy/plugins //指定插件的目录

plugins =proxy,admin //插件的名称

proxy-address= 172.16.0.244:3306 //代理服务器的地址和端口号

proxy-backend-addresses=172.16.0.241:3306 //主服务器地址和端口号

proxy-read-only-backend-addresses= 172.16.0.242:3306 //从服务器地址和端口号

proxy-read-only-backend-addresses= 172.16.0.243:3306

proxy-lua-script =/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua //读写分离时的脚本程序的目录

daemon =true //使用守护进程的方式运行mysql-proxy

keepalive = true //当mysql-proxy因某些原因停止的时候，可以重新启动

user = mysql //启动mysql-proxy的用户名称

pid-file = /var/run/myproxy/myproxy.pid //进程文件路径及名称

log-level= warning //指定日志的记录位置

log-file = /var/log/myproxy/mysql-proxy.log

max-open-files= 2048 //可以打开的最大文件数量

event-threads= 16 //mysql打开的线程数量

proxy-connect-timeout= 3 //单位s

proxy-read-timeout= 120 //单位s

proxy-write-timeout= 120 //单位s

admin-username= admin //设置mysql-proxy的管理的用户名

admin-password= lvlan

admin-lua-script= /usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua //mysql-proxy的管理的脚本

注：log-file、pid-file设定的目录要存在，而且所有者设置为mysql

启动mysql-proxy服务

[root@localhostbin]# ./mysql-proxy --defaults-file=/etc/myproxy.cnf

查看端口是否监听

主从服务器授权（172.16.0.241、172.16.0.242、172.16.0.243都要授权）

mysql>grant all on *.* to ‘root‘@‘%‘ identified by ‘123456‘；

mysql>flush privileges；

测试mysql读写分离

测试读：

主从服务器数据设置为不一致

客户端登录代理服务器：[root@station ~]#/usr/local/mysql/bin/mysql -u root -p –h 172.16.0.244

查看数据，开启四个终端查询数据，根据数据显示得知都是从主服务器上获得的，并没有在从服务器上查询，原因如下：

[root@localhost mysql-proxy]#cd /usr/local/mysql-proxy/share/doc/mysql-proxy

[root@localhost mysql-proxy]# vim rw-splitting.lua

min_idle_connections = 4, //最小连接数超过4个的时候才进行读写分离

max_idle_connections = 8, //最大连接数为8个

为了试验测试方便，将设置min_idle_connections = 1、max_idle_connections = 1

[root@localhost mysql-proxy]# killall -9 mysql-proxy //杀掉mysql-proxy所有进程

[root@localhost bin]# ./mysql-proxy --defaults-file=/etc/myproxy.cnf // 重新启动

[root@localhost bin]# lsof –i:3306 //查看监听状态

[root@localhost bin]#lsof –i:4041

测试写：

客户端登录代理服务器插入数据：[root@station ~]# /usr/local/mysql/bin/mysql -u root -p –h172.16.0.244 -e “insert into sxjy .t1values(11,’beautiful’)”

主从服务器将都会有这个数据

登录读写分离服务器的管理账号

[root@localhostbin]# /usr/local/mysql/bin/mysql -u admin -p -h 172.16.0.244 --port=4041 //管理账号及密码都在/etc/myproxy.cnf 里面设置好的

mysql> select *from backends; //查询

之前我在master(linux)机上写insert语句，发现同步是没问题的，后来我在window上用一个mysql客户端插入数据，上面的问题就开始出现了。通过mysqlbinlog查看slave日志发现

[Warning] Slave: Character set ‘#28‘ is not a compiled character set andis not specified in the ‘/usr/local/mysql/share/mysql/charsets/Index.xml‘ fileError_code: 22

应该是mysql字符集的问题，但是在网上查找资料还木有解决 ~~~~(>_<)~~~~

本文出自 “” 博客，请务必保留此出处

Mysql主从复制、读写分离工作原理+配置,布布扣,bubuko.com