实现在同一台linux主机上mysql主从复制与读写分离

环境情况：由于资源有限，仅在一台CentOS release 6.6上实现M-S主从复制与读写分离

一、mysql安装与配置

具体安装过程建议参考我的上篇一博客文章

二、mysql主从复制

主从服务器场景如下

主（m）   ：172.30.204.111:3307

从1（s1）：172.30.204.111:3308

从2（s2）：172.30.204.111:3309

[root@master dingmingyi]# netstat-anlpt|grep mysql

tcp       0      0 0.0.0.0:3307                0.0.0.0:*                   LISTEN      19411/mysqld       

tcp       0      0 0.0.0.0:3308                0.0.0.0:*                   LISTEN     19734/mysqld       

tcp       0      0 0.0.0.0:3309                0.0.0.0:*                   LISTEN      20202/mysqld

 

2.1主服务器操作：

[root@master dingmingyi]# cat /usr/local/mysql-m/etc/my.cnf [mysqld] basedir=/usr/local/mysql-m datadir=/opt/database-m socket=/var/run/mysql-m/mysql-m.sock pid-file=/var/run/mysql-m/mysql-m.pid port=3307 user=mysql server-id=1                           log-bin=mysql-bin binlog_ignore_db=mysql character_set_server=utf8 [mysqld_safe] log-error=/var/log/mysql-m/mysql--error.log [mysql] socket = /var/run/mysql-m/mysql-m.sock default-character-set=utf8

只要在my.cnf里面添加内容都要重新启动服务#service mysql-m restart

[root@master dingmingyi]#/usr/local/mysql-m/bin/mysqladmin -uroot password '321321'  -S /var/run/mysql-m/mysql-m.sock               #为M-mysql设置密码；

[root@master dingmingyi]# /usr/local/mysql-m/bin/mysql-uroot -p321321 -S /var/run/mysql-m/mysql-m.sock

 

授权给从服务器

mysql> grant replication slave on *.* tos1@localhost identified by '123123';

mysql> grant replication slave on *.* tos2@localhost identified by '123123';

mysql>flush privileges;

 

查询主数据库状态

mysql>  show master status; +------------------+----------+--------------+------------------+-------------------+ | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 |      670 |              | mysql            |                   | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)

记录file和position的值，在配置从服务器时需要用到

 

2.2从服务器操作

修改从服务器配置文件/usr/local/mysql-s1/etc/my.cnf

将server-id=10 ，确保此id与主服务器不同                                                     #设置多个从服务器必须与M-S和S-S都不同

同样在[mysql]下增加default-character-set=utf8 ，在[mysqld]下增加character_set_server=utf8

重启服务

 

登陆从服务器

/usr/local/mysql-s1/bin/mysql -uroot -p -S/var/run/mysql-s1/mysql-s1.sock              #由于没设密码，可以直接回车进入

 

执行同步语句

>change master to  master_host='localhost',  master_user='s1', master_password='123123', master_log_file='mysql-bin.000001', master_log_pos=670, master_port=3307;                                #一定要指定相应的端口号，不然在查看状态时会出错 >start slave;

 

mysql> show slave status\G *************************** 1. row ***************************                   Slave_IO_State: Waiting for master to send event                        Master_Host: localhost                        Master_User: s1                         Master_Port: 3307                    Connect_Retry: 60                 Master_Log_File: mysql-bin.000001     Read_Master_Log_Pos: 670                      Relay_Log_File: mysql-s1-relay-bin.000002                      Relay_Log_Pos: 283     Relay_Master_Log_File: mysql-bin.000001                Slave_IO_Running: Yes            #以下两排都要为YES才表明状态正常             Slave_SQL_Running: Yes                  Replicate_Do_DB:              …………………………..省略若干…………………………………

 

另一从服务器也做类似以上操作。

 

2.3 验证主从复制效果

主服务器上操作

mysql> create database ding; Query OK, 1 row affected (0.00 sec)   mysql> create table ding.d_tb(id int(3),name char(16),age char(4)); Query OK, 0 rows affected (0.14 sec)   mysql> insert into ding.d_tb values(001,'xixi','23'); Query OK, 1 row affected (0.03 sec)

 

两台从服务器上都可以查看相同的内容

mysql> show databases; +--------------------+ | Database           | +--------------------+ | information_schema | | ding             | | mysql              | | performance_schema | | test               | +--------------------+ 5 rows in set (0.00 sec)   mysql> use ding; Database changed   mysql> show tables; +------------------+ | Tables_in_d_test | +------------------+ | d_tb             | +------------------+ 1 row in set (0.00 sec)   mysql> select * from ding.d_tb; +------+------+------+ | id   | name | age  | +------+------+------+ |    1 | xixi | 23   | +------+------+------+ 1 row in set (0.00 sec)

同步成功！！！

 

三、Mysql-proxy读写分离

 Mysql-proxy代理：将写操作分到mysql-master，读操作分到mysql-slave

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

3.1安装lua

[root@master dingmingyi]# yum -y install gcc gcc-c++ libedit libedit-devel libtermcap-devel ncurses-devel libevent-devel readline-devel [root@master dingmingyi]# wget http://www.lua.org/ftp/lua-5.2.3.tar.gz [root@master dingmingyi]# tar zxvf lua-5.2.3.tar.gz [root@master dingmingyi]#cd lua-5.2.3 [root@master lua-5.2.3]#make linux [root@master lua-5.2.3]#make install

 

3.2 下载mysql-proxy

[root@master dingmingyi]#wget http://mirrors.sohu.com/mysql/MySQL-Proxy/mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit.tar.gz [root@master dingmingyi]# tar zxvf mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit.tar.gz [root@master dingmingyi]# mv mysql-proxy-0.8.4-linux-glibc2.3-x86-64bit /usr/local/mysql_proxy [root@master dingmingyi]# cd /usr/local/mysql_proxy/ [root@master mysql_proxy]# mkdir scripts [root@master mysql_proxy]# cp share/doc/mysql-proxy/rw-splitting.lua scripts/ [root@master mysql_proxy]# vim scripts/rw-splitting.lua   if not proxy.global.config.rwsplit then      proxy.global.config.rwsplit = {               min_idle_connections = 1,           #默认4           改为1，表示最小链接数只有超过1时才会进行读写分离              max_idle_connections = 1,           #默认8            is_debug = false       } end     [root@master mysql_proxy]# vim proxy.conf      #此为手动创建     1 [mysql-proxy]   2 admin-username=proxy   3 admin-password=123123   4   5 admin-lua-script=/usr/local/mysql_proxy/lib/mysql-proxy/lua/admin.lua                       #定义管理脚本路径   6 proxy-read-only-backend-addresses=172.30.204.111:3308,172.30.204.111:3309        #读服务器地址   7 proxy-backend-addresses=172.30.204.111:3307                         #写服务器地址   8 proxy-lua-script=/usr/local/mysql_proxy/scripts/rw-splitting.lua                 #读写分离脚本路径   9 log-file=/var/log/mysql-proxy.log  10 log-level=debug  11 daemon=true  12 keepalive=true ~                   [root@master mysql_proxy]# chmod 660 proxy.conf   [root@master mysql_proxy]# bin/mysql-proxy -P 172.30.204.111:3310 --defaults-file=/usr/local/mysql_proxy/proxy.conf [root@master mysql_proxy]# lsof -i:3310 COMMAND    PID USER   FD   TYPE  DEVICE SIZE/OFF NODE NAME mysql-pro 1416 root   10u  IPv4 1011376      0t0  TCP master.jay.com:dyna-access (LISTEN)

 

登陆主服务器上

mysql> grant all on *.* to proxy@'%'identified by '123123';

mysql>flush privileges;

 

由于配置了主从复制，可以在从数据库服务器上查看到同步的数据

从服务器上 mysql> select host,user,password from mysql.user;      +----------------+-------+-------------------------------------------+ | host           | user  | password                                  | +----------------+-------+-------------------------------------------+ | localhost      | root  |                                           | | master.jay.com | root  |                                           | | 127.0.0.1      | root  |                                           | | ::1            | root  |                                           | | localhost      |       |                                           | | master.jay.com |       |                                           | | %              | proxy | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 | +----------------+-------+-------------------------------------------+ 7 rows in set (0.00 sec)

 

 

为了更显著看到读写分离效果，现将两台slave关闭

mysql> stop slave;

 

注：这里以ding.d_tb为测试数据库，由于前面进行主从复制，在从服务器中也存在ding.d_tb库。

为了看到单点效果，现做如下操作：

mysql> insert into ding.d_tbvalues(004,'master','4');                   ----〉主服务器

mysql> insert into ding.d_tbvalues(005,'slave-1','5');                 -----〉从服务器 s1

mysql> insert into ding.d_tbvalues(006,'slave-2','6');                 ----->从服务器s2

 

测试机已安装mysql，现远程连接mysql-proxy

[root@ldap mysql-p]# bin/mysql -uproxy -p123123 -h172.30.204.111 -P3310 –e” insert into d_tb values(002,'dingding','25');”     #插入数据   [root@ldap mysql-p]# bin/mysql -uproxy -p123123 -h172.30.204.111 -P3310     mysql> select * from ding.d_tb;      #此时看到的是从服务器s1上的数据，上一步添加的数据没有 +------+----------+------+ | id   | name     | age  | +------+----------+------+ |    1 | xixi     | 23   | |    2 | haha     | 24   | |    2 | dingding | 25   | |    5 | slave-1  | 5    | +------+----------+------+ 4 rows in set (0.00 sec)

 

去主库中查询：

mysql>  select * from ding.d_tb; +------+----------+------+ | id   | name     | age  | +------+----------+------+ |    1 | xixi     | 23   | |    2 | haha     | 24   | |    2 | dingding | 25   | |    4 | master   | 4    | |    2 | dingding | 25   | +------+----------+------+   数据已插入

 

最后检查从数据库

S1:

mysql>  select * from ding.d_tb; +------+----------+------+ | id   | name     | age  | +------+----------+------+ |    1 | xixi     | 23   | |    2 | haha     | 24   | |    2 | dingding | 25   | |    5 | slave-1  | 5    | +------+----------+------+ 4 rows in set (0.00 sec)

 

S2:

mysql>  select * from ding.d_tb; +------+----------+------+ | id   | name     | age  | +------+----------+------+ |    1 | xixi     | 23   | |    2 | haha     | 24   | |    2 | dingding | 25   | |    6 | slave-2  | 6    | +------+----------+------+ 4 rows in set (0.00 sec)

 

由于关闭了主从同步，也没有记录。

 

现停掉S1服务   #service mysql-s1 stop

再次登陆测试机

[root@ldap mysql-p]# bin/mysql -uproxy -p123123 -h172.30.204.111 -P3310 Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor.  Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.6.24-debug Source distribution   Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.   Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.   mysql> select * from ding.d_tb;      +------+----------+------+ | id   | name     | age  | +------+----------+------+ |    1 | xixi     | 23   | |    2 | haha     | 24   | |    2 | dingding | 25   | |    6 | slave-2  | 6    | +------+----------+------+ 4 rows in set (0.00 sec)   发现已自动切到S2上读取数据了

 

由此验证，已经实现mysql读写分离，目前写操作都在master主服务器上，用了避免数据不同步；读操作都分摊给其他各个slave从服务器，用来分担数据库压力。

 

===================================================================

配置过程出现的问题及相应的解决方法

1、mysql> showmaster status;

Empty set (0.00 sec)

 

解决：没开启log-bin导致。在主服务器my.cnf中[mysqld]添加log-bin=mysql-bin，重启

 

2、mysql> show slavestatus\G;

*************************** 1. row***************************

               Slave_IO_State: Connecting tomaster

                  Master_Host: localhost

                  Master_User: s1

                  Master_Port: 3308

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

         Read_Master_Log_Pos: 120

               Relay_Log_File:mysql-s1-relay-bin.000001

                Relay_Log_Pos: 4

       Relay_Master_Log_File: mysql-bin.000001

            Slave_IO_Running: Connecting

           Slave_SQL_Running: Yes

 

解决：

对照以下三条：网络不通、密码不对、pos不对

>stop slave;

>change master to

   master_host='localhost',

   master_user='s1',

   master_password='123123',

   master_log_file='mysql-bin.000001',

master_log_pos=120,

master_port=3307;         #一定要指定端口号

>start slave;

 

3、mysql> show slavestatus\G；

……………….省略若干………………….

ERROR:

No query specified

 

解决：mysql> show slave status\G不要后面的；（==》分号）

 

4、# make linux

cd src && make linux

make[1]: Entering directory`/home/dingmy/lua-5.2.4/src'

make allSYSCFLAGS="-DLUA_USE_LINUX" SYSLIBS="-Wl,-E -ldl-lreadline"

make[2]: Entering directory `/home/dingmy/lua-5.2.4/src'

gcc -O2 -Wall -DLUA_COMPAT_ALL-DLUA_USE_LINUX    -c -o lua.o lua.c

lua.c:67:31: error: readline/readline.h: Nosuch file or directory

lua.c:68:30: error: readline/history.h: Nosuch file or directory

lua.c: In function ?.ushline?.

lua.c:265: warning: implicit declaration offunction ?.eadline?

lua.c:265: warning: assignment makespointer from integer without a cast

lua.c: In function ?.oadline?.

lua.c:297: warning: implicit declaration offunction ?.dd_history?

make[2]: *** [lua.o] Error 1

make[2]: Leaving directory`/home/dingmy/lua-5.2.4/src'

make[1]: *** [linux] Error 2

make[1]: Leaving directory`/home/dingmy/lua-5.2.4/src'

make: *** [linux] Error 2

 

解决：yum install readline-devel

 

5、

[root@master dingmingyi]#/usr/local/mysql-m/bin/mysql -uroot -p321321 -S /var/run/mysql-m/mysql-m.sock

Warning: Using a password on the commandline interface can be insecure.

/usr/local/mysql-m/bin/mysql: Unknown OScharacter set 'GB18030'.

/usr/local/mysql-m/bin/mysql: Switching tothe default character set 'latin1'.

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.24-debug-log Sourcedistribution

 

Copyright (c) 2000, 2015, Oracle and/or itsaffiliates. All rights reserved.

 

Oracle is a registered trademark of OracleCorporation and/or its

affiliates. Other names may be trademarksof their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' toclear the current input statement.

 

mysql>

 

解决：

# vim /usr/local/mysql-m/etc/my.cnf

在[mysql]下增加default-character-set=utf8 

在[mysqld]下增加character_set_server=utf8

# service mysql-m restart

 

 

6、[root@ldap mysql-p]#bin/mysql -uproxy -p123123 -P3310 -h172.30.204.111

Warning: Using a password on the commandline interface can be insecure.

ERROR 1045 (28000): Access denied for user'proxy'@'master.jay.com' (using password: YES)

 

解决：进入主服务器中

mysql> select host,user,password frommysql.user;

+----------------+--------+-------------------------------------------+

| host           | user   | password                                  |

+----------------+--------+-------------------------------------------+

| localhost      | root  | *4160291B4C8CC2573CC94951203FFBC858754907 |

| master.jay.com | root   |                                           |

| 127.0.0.1      |root   |                                           |

| ::1            | root   |                                           |

| localhost      |       |                                           |

| master.jay.com |        |                                           |

| localhost      | s1    | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |

| localhost      | s2    | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |

| %              | proxy  | *E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |

| %              | proxy1 |*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1 |

+----------------+--------+-------------------------------------------+

10 rows in set (0.00 sec)

 

mysql> delete from mysql.user wherehost='master.jay.com';      #删除host=master.jay.com

Query OK, 2 rows affected (0.00 sec)

 

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

 

重新连接mysql-proxy成功！！