配置Mysql实现主从复制与读写分离

By | 2013年2月16日
去评论本文

环境说明

主从复制使用mysql自带的master与slave机制；读写分离使用mysql-proxy实现！

有服务器三台：s1,s2,s3。

s1为web服务器，装有httpd，php，mysql，mysql-proxy。

s2为主数据库服务器（Master），仅装mysql。

s3为从数据库服务器（Salve），仅装mysql。

实现目标

s1上的网站的数据库读写操作分摊到s2和s3上，即s2专门进行写操作(也可以读)，s3专门进行读操作；

s2上的数据库中有数据更新时，自动将变化同步到s3上的数据库中。

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

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

#编辑mysql配置文件
vim /etc/my.cnf

#整体内容如下：
[client]
default_character_set=utf8

[mysqld]
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的数据库)

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

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

#在主数据库服务器上进入mysql命令行
mysql -u root  -p
(输入密码)

#成功进入mysql命令行，新建一个用户
mysql>grant replication slave on *.* to '用户名'@'副数据库的IP地址' identified by '密码';

#查看创建用户是否成功
mysql>select user,host from mysql.user;

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

service mysqld restart

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

#同样进入mysql命令行
mysql>show master status;

类似于此内容：

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

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

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

vim /etc/my.cnf

#整体内容如：
[client]
default_character_set=utf8

[mysqld]
default_character_set=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0

server-id=2            #重要，与主数据上id不一样
 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

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

service mysqld restart

7.进入mysql命令行，执行change master to命令

#进入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;

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

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

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

贴上我的slave status： 

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)

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

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

1.首先我们想要在web服务器上安装mysql-proxy，并配置好它，mysql-proxy官网下载 

#进入mysqlproxy的目录
cd /opt/mysql-proxy

#创建mysqlproxy.cnf文件
vim mysqlproxy.cnf

#整体内容如下
[mysql-proxy]
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

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

mysql -u root -p

#进入mysql后
mysql>grant all on *.* to '用户名'@'web服务器地址' identified by '密码';

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

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

重要配置已经完成了，最后就要确定下防火墙(iptables)了。

web服务器的mysql-proxy默认端口为4040，主从两个数据库3306端口。

测试读写分离

在web服务器上通过mysql连接4040端口： 

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

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

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