MySQL主从复制和读写分离的搭建

① 搭建主从复制前提准备
环境准备：两台服务器 分别安装两台MySQL数据库，分别配置登陆用户密码，配置允许第三台机器访问本机MySQL，编辑/etc/my.inf防止中文乱码
1.0 服务器
server3    192.168.0.130
server4    192.168.0.140
1.1 安装MySQL：yum -y install mysql-server
1.2 启动MySQL服务：service mysqld start
1.3 设置初始密码：mysqladmin -u root password ‘root’
1.4 登陆MySQL客户端：mysql -uroot -proot
1.5 配置远程访问
show databases;
use mysql;
select host,user,password from user;
update user set host=‘%’;
1.6 刷新权限
flush privileges;
1.6 测试两台主机间的数据库访问 mysql -uroot -proot -h otherIP;
1.7 如果不能访问，说明防火墙没有关闭，要关闭防火墙
service iptables stop; #关闭防火墙
chkconfig iptables off; #关闭防火墙自启动
1.8 vim /etc/my.cnf 修改编码为utf8
在[client]下添加
default-character-set=utf8 
在[mysqld]下添加
default-character-set=utf8
② 开始搭建主从复制
2.1 vim /etc/my.cnf 在[mysqld]下添加server-id和log-bin
分别在server3和server4中编辑/etc/my.cnf
[mysqld]
server-id=整数值
log-bin=mysql-bin
......
server-id可以是任意整数值，但是要保证它们配置不同的整数值。
2.2 分别在master和slave上运行SHOW VARIABLES LIKE 'server_id'; 查看server-id是否和自己配置的一样。
2.3 配置后要重启MySQL服务器：service mysqld restart
2.4 在master即server3的命令端输入show master status；
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      106 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql-bin.000001 ：从服务器从哪个日志文件读取操作信息
106：log文件的位置
记录下 FILE 及 Position 的值，在后面进行从服务器操作的时候需要用到。
2.5 配置从服务器(server4)
change master to 
master_host='192.168.0.130’,
master_user=’root',
master_password='root',
master_log_file='mysql-bin.000001’,
master_log_pos=106;
正确执行后启动Slave同步进程
6：启动slave
mysql> start slave;
7：查看slave状态
mysql> show slave status\G
其中Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES，才表明状态正常。
③测试主从复制是否成功
3.1 先确定主，从库上没有任何自定义表
3.2 主服务器上的操作在主服务器上创建数据库test_dbmysql> create database test_db;
在主服务器上创建表test_tbmysql> create table test_tb(id int(3),name char(10));
在主服务器上的表test_tb中插入记录mysql> insert into test_tb values (01, "test01");
3.3 在从服务器上查看是否已经同步，如果没有同步说明配置失败，要检查失败原因：
1> server_id 配置的一样或是配置的没有更新到Mysql数据中来
2> 防火墙拦截了3306端口
3> 用户与密码不正确
4> Mysql不允许其它机器访问
④ 主从复制配置成功后，开始搭建读写分离
环境准备
服务器三台：
安装配置两台主从服务(已安装)
安装一台mysql-proxy
server2  192.168.0.20  ——>调度服务器
server3  192.168.0.30  ——>数据库master所在的主服务器
server4  192.168.0.40  ——>数据库salve所在的从服务器
以下操作均在mysql-proxy所在服务器上进行
4.1 检查系统所需软件包
通过 rpm -qa | grep name 的方式验证以下软件包是否已全部安装。gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig*  libevent* glib* readline*
若缺少相关的软件包，可通过yum -y install方式在线安装，或直接从系统安装光盘中找到并通过rpm -ivh方式安装。
4.2 编译安装lua
MySQL-Proxy的读写分离主要是通过rw-splitting.lua脚本实现的，因此需要安装lua。
其实在Linux中已经自带了Lua 可以使用以下命令
进行查看：rpm –qa|grep lua
如果没有则需要安装lua
这里我们建议采用源码包进行安装cd /opt/installwget http://www.lua.org/ftp/lua-5.1.4.tar.gztar zvfx lua-5.2.3.tar.gzcd lua-5.1.4
vi src/Makefile在 CFLAGS= -O2 -Wall $(MYCFLAGS) 这一行记录里加上-fPIC，更改为 CFLAGS= -O2 -Wall -fPIC $(MYCFLAGS) 来避免在64位系统中编译过程中出现错误。
make linuxmake install
4.3 下载mysql-proxy
MySQL-Proxy可通过以下网址获得：http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/
推荐采用已经编译好的二进制版本，因为采用源码包进行编译时，最新版的MySQL-Proxy对automake，glib以及libevent的版本都有很高的要求，而这些软件包都是系统的基础套件，不建议强行进行更新。并且这些已经编译好的二进制版本在解压后都在统一的目录内，因此建议选择以下版本：32位RHEL5平台：http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.4-linux-rhel5-x86-32bit.tar.gz64位RHEL5平台：http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.4-linux-rhel5-x86-64bit.tar.gz
4.4 安装mysql-proxy
查看系统版本
[root@server3 ~]# uname -a
Linux server3 2.6.32-696.el6.x86_64 #1 SMP Tue Mar 21 19:29:05 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
因此选择64位
makdir /opt/mysql-proxy #指定安装目录
tar -zxvf mysql-proxy-0.8.4-linux-rhel5-x86-64bit.tar.gz #解压安装包
cd mysql-proxy-0.8.4-linux-rhel5-x86-64bit #进入安装包根目录
cp -r ./* /opt/mysql-proxy/  #将安装包内所有内容拷贝到安装目录
mkdir /opt/mysql-proxy/scripts #指定读写分离脚本所在目录
mkdir /opt/mysql-proxy/run 
mkdir /opt/mysql-proxy/log #指定运行日志所在目录
cd /opt/mysql-proxy/ #进入安装根目录
find ./share/ -name rw-*  #找到 ./share/doc/mysql-proxy/rw-splitting.lua
cp ./share/doc/mysql-proxy/rw-splitting.lua ./scripts/ #将读写脚本拷贝至指定目录
./bin/mysql-proxy –help-all #查看帮助
4.5 修改读写分离脚本
修改默认连接，进行快速测试，不修改的话要达到连接数为4时才启用读写分离vim /opt/mysql-proxy/scripts/rw-splitting.lua-- connection pool
if not proxy.global.config.rwsplit then
        proxy.global.config.rwsplit = {
                min_idle_connections = 1, //默认是4
                max_idle_connections = 1, //默认是8


                is_debug = false
        }
end
4.6 启动mysql-proxy
cd /opt/mysql-proxy
./bin/mysql-proxy –help-all #查看帮助
关键说明：
 -P, --proxy-address=<host:port>    #指定代理服务器地址
 -r, --proxy-read-only-backend-addresses=<host:port> #指定读服务器地址
 -b, --proxy-backend-addresses=<host:port> #指定写服务器地址
 -s, --proxy-lua-script=<file> #指定读写分离脚本的位置
启动：
 /opt/mysql-proxy/bin/mysql-proxy  --proxy-address=192.168.0.120:3306  --proxy-read-only-backend-addresses=192.168.0.140:3306  --proxy-backend-addresses=192.168.0.130:3306 --proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua —keepalived
测试读写分离：
首先关闭主从复制 在server4的MYSQL上：stop slave；
然后在192.168.0.120:3306代理地址上连接MYSQL 插入数据。
查看master上数据库内容，再在slave上启动主从复制start slave;
查看salve上的数据库，是否有刚才新添加的内容。
遗留问题：
MySQL-Proxy实际上非常不稳定，在高并发或有错误连接的情况下，进程很容易自动关闭，因此打开--keepalive参数让进程自动恢复是个比较好的办法，但还是不能从根本上解决问题，因此通常最稳妥的做法是在每个App服务器上安装一个MySQL-Proxy供自身使用，虽然比较低效但却能保证稳定性；如下图所示：

双主热备：即是在主从复制的时候 server3和server4互为master即可

server4 上MYSQL
change master to 
master_host='192.168.0.130’,
master_user=’root',
master_password='root',
master_log_file='mysql-bin.000001’,
master_log_pos=106;
server3上MYSQL

change master to 
master_host='192.168.0.140’,
master_user=’root',
master_password='root',
master_log_file='mysql-bin.000001’,
master_log_pos=106;