mysql主从复制 读写分离

转载地址：http://blog.51cto.com/mailfile/1272623

MySQL是开源的关系型数据库系统。

复制(Replication)是从一台MySQL数据库服务器（主服务器master）复制数据到另一个服务器（从服务器slave）的一个进程

MySQL Proxy有一项强大功能是实现“读写分离”，基本原理是让主数据库处理写方面事务，让从库处理SELECT查询。

MySQL主从数据库。 

实验环境

CentOS6.8_x64

mysql_master  192.168.1.109 

mysql_slave     192.168.1.111

数据库主从读写分离，mysql_proxy    192.168.1.117

 

实验软件

mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz 

 

数据库主从复制。软件安装

 

sed -i 's/^mirrorlist=https/mirrorlist=http/' /etc/yum.repos.d/*.repo 

yum clean all

yum install -y mysql  mysql-libs  mysql-server  lua

数据库主从分离？ 

service mysqld restart

mysqladmin -uroot password   数据库密码

 

mysql --version

mysql主从和集群的区别，mysql  Ver 14.14 Distrib 5.1.73

 

mysql -uroot -p 数据库密码

mysql>          以上操作master slave端都是一致

 

oracle主从复制？cp /etc/my.cnf /etc/my.cnf.bak

[mysqld]

server-id=1             master端配置

log-bin=mysql-bin  master/slave端配置

service mysqld restart

redis主从复制。 

cp /etc/my.cnf /etc/my.cnf.bak

scp /etc/my.cnf root@192.168.1.111:/etc/my.cnf

sed -i "s/server_id = 1/server_id = 2/g" /etc/my.cnf

service mysqld restart            slave端配置

Mysql主从复制。 

mysql -uroot -p  

mysql> grant replication slave on *.* to 'root'@'192.168.1.111' identified by '123456';   192.168.1.111为slave ip

mysql> grant all on *.* to 'proxy'@'192.168.10.181' identified by '123.com';           创建proxy共享账号

mysql> flush privileges;

mysql分离。mysql> show master status;

| mysql-bin.000001 |      323 |             master端操作 

    

mysql -uroot -p                 slave端操作

mysql> stop slave;

mysql主从，mysql> reset slave;

mysql> change master to

    -> master_host='192.168.1.109',       master端ip

    -> master_user='root',                     master端数据库用户名

    -> master_password='123456',         master端数据库密码

MySQL主从复制原理。    -> master_log_file='mysql-bin.000001',

    -> master_log_pos=323;             这两个文件按照master端实际情况而定,次操作为slave端

mysql> start slave;

mysql> show slave status\G

             Slave_IO_Running: Yes

             Slave_SQL_Running: Yes    这两行一定要是yes才可以实现主从复制

              

netstat -tuplna | grep mysqld

tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN      22233/mysqld        

tcp        0      0 192.168.1.109:3306          192.168.1.111:41406         ESTABLISHED 22233/mysqld   

 

测试主从同步

mysql> create database test_1   master端操作

mysql> show databases;

| test_1             |         master端

   

mysql> show databases;

| test_1             |        slave端

 

tar zxvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz 

mv mysql-proxy-0.8.5-linux-el6-x86-64bit /usr/local/mysql-proxy

mkdir -p /usr/local/mysql-proxy/lua

mkdir -p /usr/local/mysql-proxy/logs

touch /usr/local/mysql-proxy/logs/mysql-proxy.log

cp /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua /etc/lua

cp /usr/local/mysql-proxy/share/doc/mysql-proxy/admin-sql.lua /etc/lua

cp -p /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua /usr/local/mysql-proxy/lua/

cp -p /usr/local/mysql-proxy/share/doc/mysql-proxy/admin-sql.lua /usr/local/mysql-proxy/lua/

 

vim  /etc/mysql-proxy.cnf  默认没有配置文件需要手动创建

[mysql-proxy]

user=root          运行mysql-proxy用户

admin-username=proxy  主从mysql共享用户

admin-password=123.com   mysql共享用户密码

proxy-address=192.168.1.117:4040   mysql-proxy  server ip 端口号默认4040

proxy-read-only-backend-addresses=192.168.1.111   mysql_slave端 ip

proxy-backend-addresses=192.168.1.109        mysql_master端ip

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   以守护进程方式运行

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

chmod 660 /etc/mysql-proxy.cnf

chown -R root:root /usr/local/mysql-proxy/ 

 

cp -p /usr/local/mysql-proxy/lua/rw-splitting.lua /usr/local/mysql-proxy/lua/rw-splitting.lua.bak

sed -i "s/min_idle_connections = 4,/min_idle_connections = 1,/g" /usr/local/mysql-proxy/lua/rw-splitting.lua     

sed -i "s/max_idle_connections = 8,/max_idle_connections = 1,/g" /usr/local/mysql-proxy/lua/rw-splitting.lua

/usr/local/mysql-proxy/bin/mysql-proxy  --defaults-file=/etc/mysql-proxy.cnf &

killall -9 mysql-proxy

 

netstat -tuplna | grep 4040

tcp        0      0 192.168.10.181:4040         0.0.0.0:*                   LISTEN      47402/mysql-proxy   

 

 tail -f /usr/local/mysql-proxy/logs/mysql-proxy.log 

2019-01-22 11:02:24: (critical) plugin proxy 0.8.5 started

2019-01-22 11:02:24: (message) proxy listening on port 192.168.1.117.:4040

2019-01-22 11:02:24: (message) added read/write backend: 192.168.1.111

2019-01-22 11:02:24: (message) added read-only backend: 192.168.10.109

 

mysql -uproxy -h192.168.1.109 -p4040 -p123.com   proxy代理服务器用户名  -h master_mysql ip  -p proxy密码，测试如果可以连接说明配置成功

root用户用于实验，生产环境不可做此操作。如果有3-5台mysql可以使用MHA mysql高可用解决方案