mysql主库从库在同一台服务器_MySQL主从复制，读写分离(mysql-proxy)及双主结构完整构建过程...

下面介绍MySQL主从复制，读写分离，双主结构完整构建过程，不涉及过多理论，只有实验和配置的过程。

MySQL主从复制

原理是master将改变记录到二进制日志(binary log),slave将master的binary log拷贝到中继日志(relay log),slave通过中继日志同步master的操作。

1，实验环境，实验有2台Ubutu server 14.04都安装了MySQL服务器,在相同的IP段

172.16.34.212(主),

172.16.34.156(从).

2，将这两台IP授权，允许其它IP通过账号密码进行访问(如添加个euht账号允许所有外部IP以密码123456访问)，分别登录进两台机子的mysql执行如下语句

Grant all privileges on . to ‘euht’@’%’ identified by ‘123456’ with grant option;

Flush privileges;

注：

①上述代码的意思是创建一个euht用户，host=%，允许所有IP通过用户名euht进行访问。添加完刷新权限。

②此时两台机子的mysql应该是可以相互访问的，如果不可以，导致的原因有很多，最常见的是防火墙没关，mysql服务器绑定了本地地址。通过如下方法一般可解决问题

关闭防火墙，或开放3306端口

 

更改my.cnf文件，把bind-address注释掉

vi /etc/mysql/my.cnf

 

3,找到主服务器172.16.34.212MySQL安装文件夹修改my.cnf(etc/mysql/my.cnf)文件，在[mysqld]下面增加下面几行代码

server-id=1

log-bin=master-bin

log-bin-index=master-bin.index

保存后重启mysql(service mysql restart;)

注：如果只需要同步特定的库，如上添加代码,binlog-do-db =euht #用于master-slave的具体数据库

4,进入mysql后，查看主服务器mysql master状态,日志File为master-bin.000001，Position为107(记下这两个值，后面进行从服务器操作的时候需要用到)

 

5，配置从服务器(172.16.34.156)

同样配置从服务器允许外部IP访问(参考第2点)

配置日志文件

找到从服务器172.16.34.156MySQL安装文件夹修改my.cnf(etc/mysql/my.cnf)文件，在[mysqld]下面增加下面几行代码(server-id跟主服务器不要相同了)

server-id=10

log-bin=master-bin

log-bin-index=master-bin.index

6，连接到主服务器(连接上156的mysql执行以下语句,更改相应的内容)

change master to

master_host=’172.16.34.212’,

master_user=’euht’,

master_password=’123456’,

master_log_file=’master-bin.000001’,

master_log_pos=107;

7，启动slave

mysql> start slave;

8,查看slave状态

mysql> show slave status\G

其中Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES，才表明状态正常。

 

以上已经可以实现172.16.34.162主服务器的库的所有变化同步到从服务器172.16.34.156

测试如下：

1，如图两台服务器都没自定义数据库

2，创建一个数据库到主库(172.16.34.212)，然后刷新从库观察情况(172.16.34.156)

观察得出212的所有操作都同步到从156从库了。至此主从复制完成。

注意以下几点:

1,做主从复制时，首先确定两台服务器的mysql没任何自定义库(否则只可以配置完后之前的东西没法同步,或者两个库都有完全相同的库应该也是可以同步)

2，server_id必须配置不一样

3，防火墙不能把mysql服务端口给拦截了(默认3306)

4，确保两台mysql可以相互访问(即需要第二步操作)

5，重置master,slave。Reset master;reset slave;开启关闭slave,start slave;stop slave;

Mysql读写分离

以上工作做完后可以开始搭建读写分离，读写分离目前主要的几种方式:

1，MySQL Proxy(中间件)

2，Amoeba for MySQL(中间件)

3，Mycat(中间件)

4，应用层实现

下面介绍用MySQL Proxy实现读写分离。Mysql Proxy一般安装到单独的一台服务器来进行读写调度，以下添加一台IP来安装mysql-proxy调度器，IP为172.16.34.236

1,首先安装mysql-proxy

apt-get install mysql-proxy

2，实现读写分离是有lua脚本实现的，现在mysql-proxy里面已经集成，无需再安装。

3，配置连接数达到多少才读写分离，此处改为1，1个连接就开始读写分离

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

4，启动mysql-proxy,主库用于写172.16.34.212，从库用于读172.16.34.156

sudo mysql-proxy –proxy-read-only-backend-addresses=172.16.34.156:3306 –proxy-backend-addresses=172.16.34.212:3306 –proxy-lua-script=/usr/share/mysql-proxy/rw-splitting.lua –admin-username=euht

–admin-password=123456

–admin-lua-script=/usr/share/mysql-proxy/admin.lua

5，启动后默认占用端口4040和4041。4040用于SQL转发，4041用于管理mysql-proxy。(netstat -tupln|grep mysql-proxy)

6，测试读写分离

用主库172.16.34.212的用户euht在这台mysql-proxy服务器登录进去(也可以单独为这个代理创建一个用户)

现在从这台代理登录进主mysql服务器插入一条数据到testtb，结果为主从两台服务器都有数据了。

为了测试读写是否真的分离了，我们把这两台服服务器的数据差异化

先登录mysql-proxy代理(-P指定端口号，必须指定否则登录进去的将是本地3306端口的那个，当然由于这台219服务器我没配置让外部机子可以访问，所以执行下面的语句缺少端口号直接报错。)

mysql -ueuht -p -h172.16.34.219 -P4040

登录进去我们为了看到读写分离是否生效，先到从服务器156把slave给停掉。(stop slave;)然后在代理服务器219的mysql-proxy执行插入数据。先看原本的数据情况

插入数据和查看数据

发现插入数据成功，再select出来居然没有刚才插入的数据。这时我们分别到两台服务器去查看数据。

172.16.156从服务器的数据还是原来的

 

再看212的数据

看到这里就看到效果了。主服务器已经有了刚才插入的数据，从服务器没有。这是因为从服务器的主从复制已经关闭，所以从mysql-proxy代理端插入数据(实际上是用了主服务器212去插入)没有复制到从服务器。从mysql-proxy读取没看到新插入的数据是因为代理端是去从服务器156读取数据的。

至此，mysql的主从复制和读写分离就结束了。下面简单说一下双主结构。

Mysql双主结构

通过上面的介绍很容易看出，双主结构其实就是两台服务器相互数据复制。那么做到双主结构只需要把212主服务器变成156的从服务器即可。

在212服务器上执行(先到156查看日志位置，改对相应的东西即可show master status;)

change master to

master_host=’172.16.34.212’,

master_user=’euht’,

master_password=’123456’,

master_log_file=’master-bin.000001’,

master_log_pos=294;

开启主从

mysql> start slave;

156，212都start slave,而且配置互为slave，这就是双主结构。

测试，212插入数据156会同步，156插入数据212会同步。经测试全部通过。(记得查看运行状态，Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES，才表明状态正常。Show slave status\G)