配置MySQL主从复制与读写分离实现高性能数据库架构

人亦已歌

MySQL 主从复制与读写分离配置指南

本文将详细介绍如何利用三台服务器（s1、s2、s3）构建一个MySQL的主从复制及读写分离系统。具体来说，s1作为Web服务器，安装了HTTPD、PHP、MySQL以及MySQL-Proxy；s2作为主数据库服务器（Master），只安装MySQL；s3作为从数据库服务器（Slave），也只安装MySQL。我们的目标是让s1上的网站能够将数据库的读写操作分别分摊到s2和s3上，其中s2负责所有的写操作以及部分读操作，而s3则专注于读操作。此外，当s2上的数据库发生数据更新时，这些更改会自动同步到s3的数据库中。

配置主从复制（涉及的服务器：s2，s3）

1. 在主数据库服务器（master）s2上进行配置

2. # 编辑MySQL配置文件
3. vim /etc/my.cnf
4. # 整体内容如下：
5. [client]
6. default_character_set=utf8
7. [mysqld]
8. default_character_set=utf8
9. datadir=/var/lib/mysql
10. socket=/var/lib/mysql/mysql.sock
11. user=mysql
12. symbolic-links=0
13. server-id=1 # 重要，需和副数据库的id区分开，主为1，副为2
14. log-bin=mysql-bin # 定义二进制log文件，从数据库将通过读取二进制log文件来更新
15. binlog-ignore-db=mysql # 定义忽略的数据库，即不需要同步的数据库
16. binlog-do-db=testdb # 定义需要同步的数据库，如果没有此，将同步所有数据库(除了上面ignore的数据库)
17. [mysqld_safe]
18. log-error=/var/log/mysqld.log
19. pid-file=/var/run/mysqld/mysqld.pid

复制代码

2. 在主数据库服务器（master）s2上的MySQL中创建一个供副数据库（slave）s3连接的账号

2. # 在主数据库服务器上进入MySQL命令行
3. mysql -u root -p
4. (输入密码)
5. # 成功进入MySQL命令行，新建一个用户
6. mysql grant replication slave on *.* to '用户名'@'副数据库的IP地址' identified by '密码';
7. # 查看创建用户是否成功
8. mysql select user,host from mysql.user;

复制代码

3. 重启主数据库服务器上的MySQL服务

2. service mysqld restart

复制代码

4. 记录主数据库服务器的Master状态，在配置从服务器时需要使用到

2. # 同样进入MySQL命令行
3. mysql show master status;

复制代码

记下File和Position。需要注意的是这两个值在MySQL重启后变化。所以在第一次配置从服务器时，需要注意了！

主数据库的配置完成，现在开始从数据库服务器。

5. 配置从数据库服务器上的my.cnf文件

2. vim /etc/my.cnf
3. # 整体内容如：
4. [client]
5. default_character_set=utf8
6. [mysqld]
7. default_character_set=utf8
8. datadir=/var/lib/mysql
9. socket=/var/lib/mysql/mysql.sock
10. user=mysql
11. symbolic-links=0
12. server-id=2 # 重要，与主数据上id不一样
13. [mysqld_safe]
14. log-error=/var/log/mysqld.log
15. pid-file=/var/run/mysqld/mysqld.pid

复制代码

6. 重启从数据库服务器的mysqld

2. service mysqld restart

复制代码

7. 进入MySQL命令行，执行CHANGE MASTER TO命令

2. # 进入MySQL
3. mysql -u root -p
4. (输入密码)
5. # 进入MySQL后，先停止slave
6. mysql slave stop;
7. mysql change master to
8. master_host='主数据库的ip地址或者hostname',
9. master_user='主数据库中允许从数据库连接它的用户名',
10. master_password='这里是密码',
11. master_log_file='mysql-bin.000004',
12. master_log_pos=261;
13. # master_log_file，master_log_pos即上面使用SHOW MASTER STATUS看到的信息
14. mysql slave start;
15. # 查看slave状态
16. mysql show slave status\G;

复制代码

在看到的slave状态中，关键信息为：

2. Slave_IO_Running: Yes
3. Slave_SQL_Running: Yes

复制代码

如果这两项都为Yes，说明主从之间复制OK了。

2. mysql show slave status\G;
3. *************************** 1. row ***************************
4. Slave_IO_State: Waiting for master to send event
5. Master_Host: s2
6. Master_User: userss
7. Master_Port: 3306
8. Connect_Retry: 60
9. Master_Log_File: mysql-bin.000004
10. Read_Master_Log_Pos: 2308873
11. Relay_Log_File: mysqld-relay-bin.000288
12. Relay_Log_Pos: 251
13. Relay_Master_Log_File: mysql-bin.000004
14. Slave_IO_Running: Yes
15. Slave_SQL_Running: Yes
16. Replicate_Do_DB:
17. Replicate_Ignore_DB:
18. Replicate_Do_Table:
19. Replicate_Ignore_Table:
20. Replicate_Wild_Do_Table:
21. Replicate_Wild_Ignore_Table:
22. Last_Errno: 0
23. Last_Error:
24. Skip_Counter: 0
25. Exec_Master_Log_Pos: 2308873
26. Relay_Log_Space: 5650
27. Until_Condition: None
28. Until_Log_File:
29. Until_Log_Pos: 0
30. Master_SSL_Allowed: No
31. Master_SSL_CA_File:
32. Master_SSL_CA_Path:
33. Master_SSL_Cert:
34. Master_SSL_Cipher:
35. Master_SSL_Key:
36. Seconds_Behind_Master: 0
37. Master_SSL_Verify_Server_Cert: No
38. Last_IO_Errno: 0
39. Last_IO_Error:
40. Last_SQL_Errno: 0
41. Last_SQL_Error:
42. 1 row in set (0.00 sec)

复制代码

最后，可以测试下，在主数据库中创建一个数据库，创建一张表，插入点数据，然后在从数据库中看时候有数据！

配置读写分离（涉及的服务器s1，s2，s3）

1. 首先我们想要在Web服务器上安装MySQL-Proxy，并配置好它，

2. # 进入MySQLProxy的目录
3. cd /opt/mysql-proxy
4. # 创建mysqlproxy.cnf文件
5. vim mysqlproxy.cnf
6. # 整体内容如下
7. [mysql-proxy]
8. user=mysql
9. daemon=true
10. keepalive=true
11. log-level=message
12. log-file=/opt/mysql-proxy/mysqlproxy.log
13. pid-file=/opt/mysql-proxy/mysqlproxy.pid
14. proxy-backend-addresses=主数据库服务器IP:端口(默认3306)
15. proxy-read-only-backend-addresses=从数据库服务器IP:端口(默认3306)
16. # 编辑环境变量文件
17. vim /etc/profile
18. # 在最后加入下列两行内容(路径自行修改)
19. PATH=/opt/mysql-proxy/bin:/opt/mysql-proxy/share/doc/mysql-proxy:$PATH
20. export PATH
21. # 可以创建一个简单的脚本，以便于开机启动
22. vim mysqlproxy
23. # 内容如下：
24. #!/bin/bash
25. mysql-proxy --defaults-file=/opt/mysql-proxy/mysqlproxy.cnf
26. # 创建好后，把mysqlproxy文件复制到/opt/mysql-proxy/bin目录下
27. # 在开机启动项中，可以加入mysqlproxy
28. vim etc/rc.d/rc.local
29. mysqlproxy
30. # 确认mysqlproxy文件是否具有可执行权限
31. # 最后修改rw-splitting.lua文件
32. vim /opt/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
33. # 修改这两个值
34. min_idle_connections = 1, //默认为4
35. max_idle_connections = 1, //默认为8

复制代码

2. 在主数据库服务器上进入MySQL命令行，创建一个用户

2. mysql -u root -p
3. # 进入MySQL后
4. mysql grant all on *.* to '用户名'@'Web服务器地址' identified by '密码';

复制代码

此时由于主数据库和从数据库是主从复制，从数据库上应该已经有了上面新建的用户了。

如果还没配置主从复制，在从服务器上执行下GRANT就好。

重要配置已经完成了，最后就要确定下防火墙(iptables)了。
Web服务器的MySQL-Proxy默认端口为4040，主从两个数据库3306端口。

测试读写分离

在Web服务器上通过MySQL连接4040端口：

2. # 用户名为上面第二步中新建的用户，hostname为其对应的hostname（即Web服务器地址）
3. mysql -u 用户名 -p -P 4040 -h hostname

复制代码

输入密码后，通过Web服务器就能连接到s2，所做的变更将直接在s2上生效，而s3将通过主从复制，实现同步！

注：Web服务器的MySQL的3306端口，不参与读写分离主从复制！Web服务器上的网站程序数据库只需要连接4040端口即可！