MySQL 主从复制和读写分离实现

来源：互联网发布：如何退出linux命令编辑：程序博客网时间：2024/05/22 01:46

环境：

主机IP：172.16.20.120

从机IP：172.16.20.233

1）、修改主机/usr/my.cnf配置：

log_bin = mysql-bin // 主机一定要开启 log-bin 日志记录

server-id = 1 // 设置为1，该ID值是唯一的

[root@gshen ~]#service mysql restart 或者 /etc/init.d/mysqld restart // 修改配置文件后一定要重启MySQL

2）、创建slave端连到master端的用户,重启master；

[root@gshen ~]#mysql -uroot -p

mysql> grant replication slave on *.* to 'root'@'172.16.20.233' identified by '123456'; // 登陆数据库，并添加授权用户

mysql> flush privileges;

mysql> show master status;

3）、SLAVE从机的配置：

vim /usr/my.cnf

#log_bin = mysql-bin // 如果该从服务器还作为其他从服务器时要开启

server_id=10
将server-id参数修改与MASTER主机不同，建议修改至10以上的数字,

mysql -uroot -p

进入mysql后进行如下设置 mysql>change master to
-->master_host='master主机IP地址'，
-->master_user='master主机授权时所创建的用户名'，

-->master_password='用户密码'

-->master_log_file='File值'，

-->master_log_pos=Position值; // 此值不带引号，是数字

或者也可通过修改/usr/my.cnf来设置：

# 从服务器ID

server-id=10
# 主服务器的IP地址或者域名

master-host=172.16.20.120

# 主数据库的端口号

master-port=3306

# 同步数据库的用户

master-user=root

  # 同步数据库的密码
  master-password=12345
  # 如果从服务器发现主服务器断掉，重新连接的时间差

master-connect-retry=60

# 需要备份的库

# replicate-do-db=rogerdb

# 忽略的数据库

replicate-ignore-db=mysql

replicate-ignore-db=test

启动同步：
mysql>start slave;
mysql>show slave status\G; 出现以下显示即为成功：
*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.47.129

Master_User: gangdan0083

Master_Port: 3306

Connect_Retry: 60
Master_Log_File: mysql-bin.000005

Read_Master_Log_Pos: 106
Relay_Log_File: localhost-relay-bin.000002

Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000005

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Last_Errno: 0
注：Slave_IO_Running和Slave_SQL_Running的状态一定要全为Yes并且Last_Errno为0时才算成功。

4）、验证mysql主从

1、在主服务器上创建数据库gang：

mysql> create database gang;
Query OK, 1 row affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

2、在从服务器上看是否存在刚：

如果主从机都关机重启后，发现slave报错：
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

mysql> stop slave;

mysql>start slave;
mysql>show slave status\G; // 查看slave状态

如果一开始主数据库中就有数据做法：

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

mysql> flush tables with read lock; // 进入MySQL进行表锁定操作，不让数据再进行写入动作

mysql> show master status; // 记录下File和Position的值

+------------------+ | File | Position |

+------------------+ |mysql-bin.000001 | 369845 |

[root@gshen ~]# mysqldump -u root -p 123456 > backup.sql //将需要复制到slave的数据库backup.sql复制出来

mysql> unlock tables; // 取消主数据库锁定

[root@gshen ~]# scp backup.sql root@172.16.20.233:/home/backup

然后进入从数据库通过 mysqladmin 执行 backup.sql;

下面的步骤一样。

MySQL主从复制原理：

主服务器上面的任何修改会保存在二进制日志文件Binary log（mysql-bin.000001）里，slave服务器上启动一个I/O thread进程与master服务器的I/O联系，并请求从指定日志文件的位置之后的内容。当主服务器接收到Slave服务器I/O线程请求后，通过I/O线程根据请求信息读取指定日志指定位置之后的日志信息，返回给Slave服务器的I/O线程。在返回的信息中除了日志所包含的信息之外，还有master端的binary log文件的名称和binary log中的位置。然后把读取到的二进制日志内容写到本地的一个Realy log里面，将读取到的master端的bin-log文件名和位置记录到master-info文件中。slave服务器上面开启一个SQL thread定时检查Realy log，如果发现有更改立即把更改的内容在本机上面执行一遍。这样就实现了在主服务器上操作，从服务器上实时也跟着操作。

File的文件是不变的，而Position是跟着master的数据库变化而变化的。
========================================================================================================

MySQL读写分离实现

主服务器：172.16.20.120

从服务器：172.16.20.233

MySQL-Proxy 调度服务器：172.16.20.120(在同一台机器上，强烈不建议这么做，最好分离 )

MySQL-Proxy的读写分离主要是通过rw-splitting.lua脚本实现的，因此需要安装lua：

1、安装libreadline 库： sudo yum install readline-devel.i686

2、下载源码：wget http://www.lua.org/ftp/lua-5.1.4.tar.gz

3、解压编译：

tar -xzvf lua-5.1.4.tar.gz

cd lua-5.1.4

make linux

sudo cp src/lua /usr/local/bin/

sudo cp src/luac /usr/local/bin/

4、测试lua是否安装成功，直接运行lua看能否进入编译模式

安装配置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.3-linux-rhel5-x86-32bit.tar.gz
64位RHEL5平台：
http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.3-linux-rhel5-x86-64bit.tar.gz

tar xzvf mysql-proxy-0.8.1-linux-rhel5-x86-32bit.tar.gz
mv mysql-proxy-0.8.1-linux-rhel5-x86-32bit /opt/mysql-proxy

#创建mysql-proxy服务管理脚本
mkdir /opt/mysql-proxy/init.d/

cd /opt/mysql-proxy/init.d/

touch mysql-proxy

vim mysql-proxy

#!/bin/sh# mysql-proxy This script starts and stops the mysql-proxy daemon# chkconfig: - 78 30# processname: mysql-proxy# description: mysql-proxy is a proxy daemon to mysql# Source function library.  源函数库. /etc/rc.d/init.d/functions#PROXY_PATH 定义mysql-proxy服务二进制文件路径PROXY_PATH=/opt/mysql-proxy/bin prog="mysql-proxy"# Source networking configuration. 源网络配置. /etc/sysconfig/network# Check that networking is up.[ ${NETWORKING} = "no" ] && exit 0# Set default mysql-proxy configuration.# PROXY_OPTIONS="--daemon"# --admin-username                        定义内部管理服务器账号# --admin-password                        定义内部管理服务器密码# --proxy-read-only-backend-addresses     定义只读从服务器地址# --proxy-backend-addresses               定义主服务器地址# --admin-lua-script                      定义lua管理脚本路径# --proxy-lua-script                      定义lua读写分离脚本路径# PROXY_OPTIONS="--admin-username=root --admin-password=password --proxy-read-only-backend-addresses=172.16.20.233:3306 --proxy-backend-addresses=172.16.20.120:3306  --admin-lua-script=/opt/mysql-proxy/lib/mysql-proxy/lua/admin.lua --proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua"# 定义mysql-proxy PID文件路径PROXY_PID=/opt/mysql-proxy/run/mysql-proxy.pid  # Source mysql-proxy configuration.if [ -f /etc/sysconfig/mysql-proxy ]; then . /etc/sysconfig/mysql-proxy fiPATH=$PATH:/usr/bin:/usr/local/bin:$PROXY_PATH# By default it's all goodRETVAL=0# See how we were called.case "$1" instart) # Start daemon. echo -n $"Starting $prog: "# --daemon        定义以守护进程模式启动# --keepalive   使进程在异常关闭后能够自动恢复# --pid-file=$PROXY_PID  定义mysql-proxy PID文件路径# --user=mysql   以mysql用户身份启动服务# --log-level   定义log日志级别，由高到低分别有(error|warning|info|message|debug)# --log-file  定义log日志文件路径  $NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS --daemon --pid-file=$PROXY_PID --user=mysql --log-level=warning --log-file=/opt/mysql-proxy/log/mysql-proxy.log RETVAL=$? echoif [ $RETVAL = 0 ]; thentouch /var/lock/subsys/mysql-proxyfi;; stop)# Stop daemons. echo -n $"Stopping $prog: "killproc $progRETVAL=$?echo if [ $RETVAL = 0 ]; then rm -f /var/lock/subsys/mysql-proxy rm -f $PROXY_PID fi ;; restart)  $0 stop  sleep 3   $0 start    ;; condrestart) [ -e /var/lock/subsys/mysql-proxy ] && $0 restart  ;; status) status mysql-proxy RETVAL=$? ;; *) echo "Usage: $0 {start|stop|restart|status|condrestart}" RETVAL=1 ;; esac exit $RETVAL

chmod +x /opt/mysql-proxy/init.d/mysql-proxy

mkdir /opt/mysql-proxy/run
mkdir /opt/mysql-proxy/log

mkdir /opt/mysql-proxy/scripts

配置并使用rw-splitting.lua读写分离脚本
最新的脚本我们可以从最新的mysql-proxy源码包中获取，位于share/doc/mysql-proxy/rw-splitting.lua，或者通过find ./ -name rw-splitting.lua查找
将该脚本复制到scripts下:

cp share/doc/mysql-proxy/rw-splitting.lua /opt/mysql-proxy/scripts

修改读写分离脚本rw-splitting.lua
修改默认连接，进行快速测试，不修改的话要达到连接数为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
=========================================

修改完成后，启动mysql-proxy

/opt/mysql-proxy/init.d/mysql-proxy start

测试读写分离效果

创建用于读写分离的数据库连接用户
登陆主数据库服务器172.16.20.120，通过命令行登录管理MySQL服务器

mysql> GRANT ALL ON *.* TO 'proxy1'@'192.168.10.132' IDENTIFIED BY 'password';

由于我们配置了主从复制功能，因此从数据库服务器172.16.20.233上已经同步了此操作。

为了清晰的看到读写分离的效果，需要暂时关闭MySQL主从复制功能
登陆从数据库服务器172.16.20.233，通过命令行登录管理MySQL服务器

关闭Slave同步进程
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

连接MySQL-Proxy调度服务器
mysql -uproxy1 -p'password' -P4040 -h172.16.20.120

登陆成功后，在first_db数据的first_tb表中插入两条记录
mysql> use first_db;
Database changed
mysql> insert into first_tb values (007,’first’);
Query Ok, 1 row affected (0.00 sec)
mysql> insert into first_tb values (110,’second’);
Query Ok, 1 row affected (0.00 sec)

查询记录
mysql> select * from first_tb;
=============================
+------+------+
| id | name |
+------+------+
| 1 | myself |
+------+------+
1 rows in set (0.00 sec)
=============================
通过读操作并没有看到新记录

mysql> quit
退出MySQL-Proxy

下面，分别登陆到主从数据库服务器，对比记录信息
首先，检查主数据库服务器
mysql> select * from first_tb;
=============================
+------+------+
| id | name |
+------+------+
| 1 | myself |
+------+------+
| 007 | first |
+------+------+
| 110 | second |
+------+------+
3 rows in set (0.00 sec)
=============================
两条新记录都已经存在

然后，检查从数据库服务器
mysql> select * from first_tb;
=============================
+------+------+
| id | name |
+------+------+
| 1 | myself |
+------+------+
1 rows in set (0.00 sec)
=============================
没有新记录存在

由此验证，我们已经实现了MySQL读写分离，目前所有的写操作都全部在Master主服务器上，用来避免数据的不同步；
另外，所有的读操作都分摊给了其它各个Slave从服务器上，用来分担数据库压力。

经验分享：
1.当MySQL主从复制在 show slave status\G 时出现Slave_IO_Running或Slave_SQL_Running 的值不为YES时，需要首先通过 stop slave 来停止从服务器，然后再执行一次。MySQL主从复制的原理其实就是从服务器读取主服务器的binlog，然后根据binlog的记录来更新数据库。

2.MySQL-Proxy的rw-splitting.lua脚本在网上有很多版本，但是最准确无误的版本仍然是源码包中所附带的lib/rw-splitting.lua脚本，如果有lua脚本编程基础的话，可以在这个脚本的基础上再进行优化；

3.MySQL-Proxy实际上非常不稳定，在高并发或有错误连接的情况下，进程很容易自动关闭，因此打开--keepalive参数让进程自动恢复是个比较好的办法，但还是不能从根本上解决问题，因此通常最稳妥的做法是在每个从服务器上安装一个MySQL-Proxy供自身使用，虽然比较低效但却能保证稳定性；

4.一主多从的架构并不是最好的架构，通常比较优的做法是通过程序代码和中间件等方面，来规划，比如设置对表数据的自增id值差异增长等方式来实现两个或多个主服务器，但一定要注意保证好这些主服务器数据的完整性，否则效果会比多个一主多从的架构还要差；

5.MySQL-Cluster 的稳定性也不是太好；

6.Amoeba for MySQL 是一款优秀的中间件软件，同样可以实现读写分离，负载均衡等功能，并且稳定性要大大超过MySQL-Proxy，建议大家用来替代MySQL-Proxy，甚至MySQL-Cluster。

使用Amoeba（变形虫）实现MySQL读写分离

参考阿里开源的Amoeba官方文章: http://docs.hexnova.com/amoeba/

0 0