MySQL主从复制+mysql-proxy读写分离

主从复制的方式有两种：基于日志、基于GTID(全局事务标识符)，我们使用基于日志的复制

MySQL主从复制原理（A/B）

1）master讲数据改变记录到二进制日志（binary log）中，也就是配置文件log-bin指定的文件，这些记录叫做二进制日志时间（binary log events）;

2)slave通过I/O线程读取master中的binary log events并写入到他的中继日志（relay log）；

3）slave重新操作中继日志中的事件，把中继日志中的事件信息在本地执行一次，完成数据在本地的存储，从而实现将改变反映到它自己的数据（数据重放）

 

开始试验

环境说明

CentOS 6.5

MySQL5.1.73

mysql-proxy

192.168.0.138(master)

192.168.0.161(slave)

192.168.0.198（mysql-proxy）

注意：

1、操作系统一样；

2、数据库版本一样；

3、数据库数据一样；

4、selinux全部为disabled

5、master必须开启二进制日志，master和slave的server_id在局域网中必须唯一，一般使用当前及其的IP最后的值

 

单向主从环境（MySQL A/B复制）搭建

1、主服务器配置

##在[mysqld]中增加以下配置

#设置server_id，一般为IP

server_id=138

## 复制过滤：需要备份的数据库，输出binlog

#binlog-do-db=mydb

## 复制过滤：不需要备份的数据库，不输出（mysql库一般不同步）

binlog-ignore-db=mysql

## 开启二进制日志功能，可以随便取，最好有含义

log-bin=master-bin

## 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存

binlog_cache_size=1M

## 主从复制的格式（mixed,statement,row，默认格式是statement）

binlog_format=mixed

## 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。

expire_logs_days=7

## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致

slave_skip_errors=1062

## 如果需要同步函数或者存储过程

log_bin_trust_function_creators=true

重启master数据库，并赋予权限

#service mysql restart 

#mysql -u root -p 

mysql>create user backup@'%' IDENTIFIED BY 'backup';

mysql>GRANT ALL  on *.* to backup@'%';//这里可根据自己的数据库和权限进行设置

mysql>grant replication slave, replication client on *.* to 'backup'@'192.168.1.161' identified by 'mysql';  

mysql>flush privileges;

mysql>select host,user from mysql.user;  

mysql>show master status;  

记住下面的master-bin.000006和 120,在配置slave时人用到，File_Set和Position每次重新配置都会改变

 

配置从服务器slave

在[mysqld]下面加上  

server_id=158  

binlog-do=db=mydb  

binlog-ignore-db=mysql  

log-bin=slave1-bin //这个可以先不加  

binlog_cache_size=1M  

binlog_format=mixed  

expire_logs_days=7  

slave_skip_errors=1062 

relay_log=mysql-relay-bin  //配置中继日志

log_slave_updates=1  //表示把事件写入自己的二进制文件（和log_bin一起应用）

read_only=1  

如果Slave为其它Slave的Master时，必须设置bin_log。在这里，我们开启了二进制日志，而且显式的命名(默认名称为hostname，但是，如果hostname改变则会出现问题)。
relay_log配置中继日志，log_slave_updates表示slave将复制事件写进自己的二进制日志。
当设置log_slave_updates时，你可以让slave扮演其它slave的master。此时，slave把SQL线程执行的事件写进行自己的二进制日志(binary log)，然后，它的slave可以获取这些事件并执行它

保存后重启MYSQL

#service mysql restart

#mysql -u root -p 

mysql>change master to master_host='192.168.1.177',

>master_user='backup',

>master_password='backup',

>master_port=3306,

>master_log_file='master-bin.000002',

>master_log_pos=391,

>master_connect_retry=30;

//这里的master_log_file和master_log_pos要和show master status中显示的master相关信息对应 

开启主从同步

mysql>start slave;

mysql>show slave status \G  

两项都是yes表示没有问题

可以进行测试进行进一步验证

 

 

下面进行mysql-proxy的配置实现主从数据库的读写分离

1、安装mysql-proxy

实现读写分离是有lua脚本实现的，现在mysql-proxy里面已经集成，不需要在安装，选择官网下载mysql-proxy

地址：http://dev.mysql.com/downloads/mysql-proxy/下载对应版本或高级版本

对tar包进行解压缩

把mysql-proxy对应文件目录进行移动并改名为/usr/local/mysql-proxy

2、配置mysql-proxy，创建主配置文件

cd/usr/local/mysql-proxy

mkdir lua #创建脚本存放目录

mkdir logs #创建日志目录

cpshare/doc/mysql-proxy/rw-splitting.lua ./lua #复制读写分离配置文件

cpshare/doc/mysql-proxy/admin-sql.lua ./lua #复制管理脚本

vi/etc/mysql-proxy.cnf   #创建配置文件

[mysql-proxy]

user=root #运行mysql-proxy用户

admin-username=lin3615#主从mysql共有的用户

admin-password=123456#用户的密码

proxy-address=192.168.179.142:4040#mysql-proxy运行ip和端口，不加端口，默认4040

proxy-read-only-backend-addresses=192.168.179.147#指定后端从slave读取数据

proxy-backend-addresses=192.168.179.146#指定后端主master写入数据

proxy-lua-script=/usr/local/mysql-proxy/lua/rw-splitting.lua#指定读写分离配置文件位置

admin-lua-script=/usr/local/mysql-proxy/lua/admin-sql.lua#指定管理脚本

log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log#日志位置

log-level=info#定义log日志级别，由高到低分别有(error|warning|info|message|debug)

daemon=true    #以守护进程方式运行

keepalive=true#mysql-proxy崩溃时，尝试重启

#保存退出！

chmod 660/etc/mysql-porxy.cnf

 

3、修改读写分离配置文件

vim/usr/local/mysql-proxy/lua/rw-splitting.lua

if notproxy.global.config.rwsplit then

 proxy.global.config.rwsplit = {

  min_idle_connections = 1, #默认超过4个连接数时，才开始读写分离，改为1

  max_idle_connections = 1, #默认8，改为1

  is_debug = false

 }

end

 

4、主数据库授权

(1).在主服务器创建proxy用户用于mysql-proxy使用，从服务器也会同步这个操作

mysql>grant all on *.* to 'proxy'@'192.168.0.198' identified by 'proxy';

 

 

5、启动mysql-proxy

#/usr/local/mysql-prox/bin/mysql-proxy –defaults-file=/etc/mysql-proxy.cnf

#netstat –tulpn| grep 4040

 

远程连接测试

完了！