配置MySQL主从复制与读写分离实现高性能数据库架构
<h1>MySQL 主从复制与读写分离配置指南</h1><p>本文将详细介绍如何利用三台服务器（s1、s2、s3）构建一个MySQL的主从复制及读写分离系统。具体来说，s1作为Web服务器，安装了HTTPD、PHP、MySQL以及MySQL-Proxy；s2作为主数据库服务器（Master），只安装MySQL；s3作为从数据库服务器（Slave），也只安装MySQL。我们的目标是让s1上的网站能够将数据库的读写操作分别分摊到s2和s3上，其中s2负责所有的写操作以及部分读操作，而s3则专注于读操作。此外，当s2上的数据库发生数据更新时，这些更改会自动同步到s3的数据库中。</p>
<h2>配置主从复制（涉及的服务器：s2，s3）</h2>
<h3>1. 在主数据库服务器（master）s2上进行配置</h3>
# 编辑MySQL配置文件
vim /etc/my.cnf
# 整体内容如下：
default_character_set=utf8
default_character_set=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
server-id=1 # 重要，需和副数据库的id区分开，主为1，副为2
log-bin=mysql-bin # 定义二进制log文件，从数据库将通过读取二进制log文件来更新
binlog-ignore-db=mysql # 定义忽略的数据库，即不需要同步的数据库
binlog-do-db=testdb # 定义需要同步的数据库，如果没有此，将同步所有数据库(除了上面ignore的数据库)
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
<p>2. 在主数据库服务器（master）s2上的MySQL中创建一个供副数据库（slave）s3连接的账号</p>
# 在主数据库服务器上进入MySQL命令行
mysql -u root -p
(输入密码)
# 成功进入MySQL命令行，新建一个用户
mysql grant replication slave on *.* to '用户名'@'副数据库的IP地址' identified by '密码';
# 查看创建用户是否成功
mysql select user,host from mysql.user;
<p>3. 重启主数据库服务器上的MySQL服务</p>
service mysqld restart
<p>4. 记录主数据库服务器的Master状态，在配置从服务器时需要使用到</p>
# 同样进入MySQL命令行
mysql show master status;
<p>记下File和Position。需要注意的是这两个值在MySQL重启后变化。所以在第一次配置从服务器时，需要注意了！</p>
<p>主数据库的配置完成，现在开始从数据库服务器。</p>
<h3>5. 配置从数据库服务器上的my.cnf文件</h3>
vim /etc/my.cnf
# 整体内容如：
default_character_set=utf8
default_character_set=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
server-id=2 # 重要，与主数据上id不一样
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
<p>6. 重启从数据库服务器的mysqld</p>
service mysqld restart
<p>7. 进入MySQL命令行，执行CHANGE MASTER TO命令</p>
# 进入MySQL
mysql -u root -p
(输入密码)
# 进入MySQL后，先停止slave
mysql slave stop;
mysql change master to
master_host='主数据库的ip地址或者hostname',
master_user='主数据库中允许从数据库连接它的用户名',
master_password='这里是密码',
master_log_file='mysql-bin.000004',
master_log_pos=261;
# master_log_file，master_log_pos即上面使用SHOW MASTER STATUS看到的信息
mysql slave start;
# 查看slave状态
mysql show slave status\G;
<p>在看到的slave状态中，关键信息为：</p>
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
<p>如果这两项都为Yes，说明主从之间复制OK了。</p>
mysql show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: s2
Master_User: userss
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 2308873
Relay_Log_File: mysqld-relay-bin.000288
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 2308873
Relay_Log_Space: 5650
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
<p>最后，可以测试下，在主数据库中创建一个数据库，创建一张表，插入点数据，然后在从数据库中看时候有数据！</p>
<h2>配置读写分离（涉及的服务器s1，s2，s3）</h2>
<p>1. 首先我们想要在Web服务器上安装MySQL-Proxy，并配置好它，</p>
# 进入MySQLProxy的目录
cd /opt/mysql-proxy
# 创建mysqlproxy.cnf文件
vim mysqlproxy.cnf
# 整体内容如下
user=mysql
daemon=true
keepalive=true
log-level=message
log-file=/opt/mysql-proxy/mysqlproxy.log
pid-file=/opt/mysql-proxy/mysqlproxy.pid
proxy-backend-addresses=主数据库服务器IP:端口(默认3306)
proxy-read-only-backend-addresses=从数据库服务器IP:端口(默认3306)
# 编辑环境变量文件
vim /etc/profile
# 在最后加入下列两行内容(路径自行修改)
PATH=/opt/mysql-proxy/bin:/opt/mysql-proxy/share/doc/mysql-proxy:$PATH
export PATH
# 可以创建一个简单的脚本，以便于开机启动
vim mysqlproxy
# 内容如下：
#!/bin/bash
mysql-proxy --defaults-file=/opt/mysql-proxy/mysqlproxy.cnf
# 创建好后，把mysqlproxy文件复制到/opt/mysql-proxy/bin目录下
# 在开机启动项中，可以加入mysqlproxy
vim etc/rc.d/rc.local
mysqlproxy
# 确认mysqlproxy文件是否具有可执行权限
# 最后修改rw-splitting.lua文件
vim /opt/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
# 修改这两个值
min_idle_connections = 1, //默认为4
max_idle_connections = 1, //默认为8
<p>2. 在主数据库服务器上进入MySQL命令行，创建一个用户</p>
mysql -u root -p
# 进入MySQL后
mysql grant all on *.* to '用户名'@'Web服务器地址' identified by '密码';
<p>此时由于主数据库和从数据库是主从复制，从数据库上应该已经有了上面新建的用户了。</p>
<p>如果还没配置主从复制，在从服务器上执行下GRANT就好。</p>
<p>重要配置已经完成了，最后就要确定下防火墙(iptables)了。<br>
Web服务器的MySQL-Proxy默认端口为4040，主从两个数据库3306端口。</p>
<h2>测试读写分离</h2>
<p>在Web服务器上通过MySQL连接4040端口：</p>
# 用户名为上面第二步中新建的用户，hostname为其对应的hostname（即Web服务器地址）
mysql -u 用户名 -p -P 4040 -h hostname
<p>输入密码后，通过Web服务器就能连接到s2，所做的变更将直接在s2上生效，而s3将通过主从复制，实现同步！</p>
<p>注：Web服务器的MySQL的3306端口，不参与读写分离主从复制！Web服务器上的网站程序数据库只需要连接4040端口即可！</p>
页:
[1]