mysql读写分离实战教程-定位原创
1、MySQL的安装与配置具体的安装过程与上文相同。
2、检查系统所需软件包
通过 rpm -qa | grep name 的方式验证以下软件包是否已全部安装。
gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig*
libevent* glib*
若缺少相关的软件包，可通过yum -y install方式在线安装，或直接从系统安装光盘中找到并通过rpm -ivh方式安装。
编译安装lua
3、MySQL-Proxy的读写分离主要是通过rw-splitting.lua脚本实现的，因此需要安装lua。
这里我们建议采用源码包进行安装
wget http://www.lua.org/ftp/lua-5.1.4.tar.gz
tar zvfx lua-5.1.4.tar.gz
cd lua-5.1.4
vi src/Makefile
在 CFLAGS= -O2 -Wall $(MYCFLAGS) 这一行记录里加上-fPIC，更改为 CFLAGS= -O2 -Wall -fPIC $(MYCFLAGS) 来避免编译过程中出现错误。
Yum install readline* #编译lua的时候需要
make linux
make install
cp etc/lua.pc /usr/lib/pkgconfig/
export PKG_CONFIG_PATH=$PKG_CONFIG_PATH:/usr/lib/pkgconfig
# useradd -r mysql-proxy
# tar xf mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz -C /usr/local
# cd /usr/local
# ls
bin include libexec mysql share
etc lib lua-5.1.4 mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit src
games lib64 man sbin
# ln -sv mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit mysql-proxy
`mysql-proxy' -> `mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit'
# cd mysql-proxy
# ls ./bin/
mysql-binlog-dump mysql-myisam-dump mysql-proxy
# vim /etc/profile
export PATH=$PATH:/usr/local/mysql-proxy/bin
# source /etc/profile
# pwd
/usr/local/mysql-proxy
# mysql-proxy --daemon --log-level=debug --log-file=/usr/local/mysql-proxy/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="192.168.3.135:3306" --proxy-read-only-backend-addresses="192.168.3.136:3306"
在主服务器上添加一个远程连接数据库的用户
mysql> grant all privileges on *.* to 'root'@'%' identified by 'redhat';
Query OK, 0 rows affected (0.08 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.06 sec)
在从服务器上测试
# mysql -uroot -p -h192.168.3.137 --port=4040
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 210
Server version: 5.6.22-log Source distribution
Copyright (c) 2000, 2014, 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>
mysql> create database hellodb;
Query OK, 1 row affected (0.08 sec)
创建数据库，来判断写入数据库是哪一个数据库
如：写入的是从数据库，主数据库不会有该新建的数据库；写入的是主数据库，从数据库也会有相应的数据库。
结果：写入的是主数据库，这在mysql中不一定是正确的。必须要在代理服务器上指定读写分离脚本
所以......
在代理服务器上kill掉mysql-proxy
# tar xf mysql-proxy-0.8.4.tar.gz
# cd mysql-proxy-0.8.4
# cp ./lib/admin.lua /usr/local/mysql-proxy/share/doc/mysql-proxy/
# killall mysql-proxy
# mysql-proxy --daemon --log-level=debug --log-file=/usr/local/mysql-proxy/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="192.168.3.135:3306" --proxy-read-only-backend-addresses="192.168.3.136:3306" --proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" --plugins=admin --admin-username="admin" --admin-password="admin" --admin-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"
测试：
在从服务器上用admin登录
# mysql -uadmin -h192.168.3.137 --port=4041 -padmin
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 1
Server version: 5.0.99-agent-admin
Copyright (c) 2000, 2014, 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 backends;
+-------------+--------------------+---------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+--------------------+---------+------+------+-------------------+
| 1 | 192.168.3.135:3306 | unknown | rw | NULL | 0 |
| 2 | 192.168.3.136:3306 | unknown | ro | NULL | 0 |
+-------------+--------------------+---------+------+------+-------------------+
2 rows in set (0.00 sec)
在开一个窗口，也是在从服务器上
# mysql -uroot -p -h192.168.3.137 --port=4040 -e 'create database cnblackhatcom;'
Enter password: redhat
执行写入操作
再次回到之前的窗口：
mysql> select * from backends;
+-------------+--------------------+---------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+--------------------+---------+------+------+-------------------+
| 1 | 192.168.3.135:3306 | up | rw | NULL | 0 |
| 2 | 192.168.3.136:3306 | unknown | ro | NULL | 0 |
+-------------+--------------------+---------+------+------+-------------------+
2 rows in set (0.00 sec)
192.168.3.135这台服务器上的state是up，可知写入数据库是主数据库
下面测试查询数据库，即读取信息
继续在从服务器另外一个窗口下执行如下语句
# mysql -uroot -p -h192.168.3.137 --port=4040 -e 'select user,password from mysql.user;'
Enter password:
+---------+-------------------------------------------+
| user | password |
+---------+-------------------------------------------+
| root | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root | |
| root | |
| root | |
| | |
| | |
| cbhroot | *5020968F0C09425DC5C61E765457A929230FF958 |
| root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
+---------+-------------------------------------------+
第一次可能还是在主服务器上进行读取，因为主服务器及支持读，又
支持写
换一条查询语句：
# mysql -uroot -p -h192.168.3.137 --port=4040 -e 'show tables from cnblackhatcom;'
Enter password: redhat
再次查询回到原始的窗口下查看从服务器的状态：
mysql> select * from backends;
+-------------+--------------------+-------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+--------------------+-------+------+------+-------------------+
| 1 | 192.168.3.135:3306 | up | rw | NULL | 0 |
| 2 | 192.168.3.136:3306 | up | ro | NULL | 0 |
+-------------+--------------------+-------+------+------+-------------------+
2 rows in set (0.00 sec)
上面的mysql-proxy都是用手启动，一大堆代码，很繁琐，可以用配置文件来解决
# vim /etc/mysql-proxy.cnf
log-level=debug
log-file=/usr/local/mysql-proxy/log/mysql-proxy.log
plugins="proxy"
proxy-backend-addresses="192.168.3.135:3306"
proxy-read-only-backend-addresses="192.168.3.136:3306"
proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua"plugins=admin --admin-username="admin" --admin-password="admin"
admin-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"
# mysql-proxy --daemon --defaults-file="/etc/mysql-proxy.cnf"
2015-03-04 05:02:25: (critical) mysql-proxy-cli.c:326: loading config from '/etc/mysql-proxy.cnf' failed: permissions of /etc/mysql-proxy.cnf aren't secure (0660 or stricter required)
2015-03-04 05:02:25: (message) Initiating shutdown, requested from mysql-proxy-cli.c:328
2015-03-04 05:02:25: (message) shutting down normally, exit code is: 1
配置文件权限设置为660就可以了，安全性问题
# chmod 660 /etc/mysql-proxy.cnf
# mysql-proxy --daemon --defaults-file="/etc/mysql-proxy.cnf"
#
这里我没有启动成功，我直接把它写到开机启动文件中了
Echo “/usr/localmysql-proxy/bin/mysql-proxy --daemon --log-level=debug --log-file=/usr/local/mysql-proxy/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="192.168.3.135:3306" --proxy-read-only-backend-addresses="192.168.3.136:3306" --proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" --plugins=admin --admin-username="admin" --admin-password="admin" --admin-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/admin.lua"” >> /etc/rc.local
页:
[1]