Mysql-proxy 读写分离
Read/write
replication
read
一、安装Mysql-proxy
[root@server16 ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/
[root@server16 ~]# cd /usr/local/
[root@server16 local]# ls
bin etc games include lib lib64 libexec mysql-proxy-0.8.5-linux-el6-x86-64bit sbin share src
[root@server16 local]# ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy
[root@server16 local]# cd mysql-proxy
[root@server16 mysql-proxy]# cd bin/
[root@server16 bin]# cd ..
[root@server16 mysql-proxy]# cd ..
[root@server16 local]# mysql-proxy -V ##显示安装成功
mysql-proxy 0.8.5
chassis: 0.8.5
glib2: 2.16.6
libevent: 2.0.21-stable
LUA: Lua 5.1.4
package.path: /usr/local/mysql-proxy/lib/mysql-proxy/lua/?.lua;
package.cpath: /usr/local/mysql-proxy/lib/mysql-proxy/lua/?.so;
-- modules
proxy: 0.8.5
二Mysql-proxy配置
[root@server16 init.d]# cd /usr/local/mysql-proxy/bin/
[root@server16 bin]# ./mysql-proxy --proxy-backend-addresses=172.25.60.27:3306 --proxy-read-only-backend-addresses=172.25.60.29:3306 --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua &
[root@server16 bin]# netstat -antlpe
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address ##4040为
Mysql-proxy端口
State User Inode PID/Program name
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 0 8125 906/sshd
tcp 0 0 127.0.0.1:25 0.0.0.0:* LISTEN 0 8722 1354/master
tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 0 10543 1963/mysql-proxy
tcp 0 0 172.25.60.26:22 172.25.60.250:55693 ESTABLISHED 0 8995 1404/sshd
tcp 0 0 172.25.60.26:42010 172.25.60.26:3306 ESTABLISHED 0 10552 1963/mysql-proxy
tcp 0 0 172.25.60.26:42011 172.25.60.26:3306 ESTABLISHED 0 10555 1963/mysql-proxy
tcp 0 0 :::22 :::* LISTEN 0 8127 906/sshd
tcp 0 0 ::1:25 :::* LISTEN 0 8724 1354/master
tcp 0 0 :::3306 :::* LISTEN 27 10293 1900/mysqld
tcp 0 0 ::ffff:172.25.60.26:3306 ::ffff:172.25.60.26:42010 ESTABLISHED 27 10553 1900/mysqld
tcp 0 0 ::ffff:172.25.60.26:3306 ::ffff:172.25.60.29:46734 ESTABLISHED 27 10295 1900/mysqld
tcp 0 0 ::ffff:172.25.60.26:3306 ::ffff:172.25.60.26:42011 ESTABLISHED 27 10556 1900/mysqld
三Mysql-proxy测试
[root@server16 bin]# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 5
Server version: 5.7.17-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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> grant all privileges on *.* to yy@'172.25.60.%' identified by 'Yangying+001';
Query OK, 0 rows affected, 1 warning (0.14 sec)
mysql> grant all privileges on *.* to yy@'172.25.60.26' identified by 'Yangying+001';
Query OK, 0 rows affected, 1 warning (0.39 sec)
mysql> ^DBye
##以下显示server17和19连接成功
[root@server17 mysql]# mysql -uyy -pYangying+001 -h172.25.60.26 -P4040
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 5
Server version: 5.7.17-log MySQL Community Server (GPL)
Copyright (c) 2000, 2013, 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> Bye
[root@server17 mysql]#
[root@server19 mysql]# mysql -uyy -p -h172.25.60.26 -P4040
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '172.25.60.26' (111)
[root@server19 mysql]# mysql -uyy -p -h172.25.60.26 -P4040
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 6
Server version: 5.7.17-log
Copyright (c) 2000, 2016, 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> ^DBye
[root@server19 mysql]#
Mha 数据库高可用
集群环境：
Master_monitor 172.25.60.29 server_id
Master 172.25.60.30 server_id=1
Slave1 172.25.60.31 server_id=2
Slave2 172.25.60.32 server_id=3
Master配置：
[root@server20 ~]# vim /etc/my.cnf
server_id=1
relay-log=relay-bin
log-bin=master-bin
innodb_file_per_table=1
Slave配置：
[root@server21 ~]# vim /etc/my.cnf
server_id=2
relay-log=relay-bin
log-bin=master-bin
relay_log_purge=0
read_only=1
innodb_file_per_table=1
[root@server22 ~]# vim /etc/my.cnf
server_id=3
relay-log=relay-bin
log-bin=master-bin
relay_log_purge=0
read_only=1
innodb_file_per_table=1
启动mysql，添加复制的用户
mysql> grant replication slave,replication client on *.* to 'repluser'@'172.25.60.%' identified by 'replpass';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+-------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000002 | 358 | | |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
配置slave1（）slave2同：启动mysql，并配置主从：
mysql> change master to master_host='172.25.60.20',master_user='repluser',master_password='replpass',master_log_file='master-bin.000002',master_log_pos=358;
Query OK, 0 rows affected (0.27 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave statusG;
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 172.25.60.20
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 358
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: master-bin.000002
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: 358
Relay_Log_Space: 106
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2013
Last_IO_Error: error connecting to master 'repluser@172.25.60.20:3306' - retry-time: 60 retries: 86400
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
ERROR:
No query specified
Master上创建管理帐号：
mysql> grant all on *.* to 'mhauser'@'172.25.60.%' identified by 'mhapass';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privilages;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'privilages' at line 1
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Monitor创建密钥对：
[root@server19 ~]# ssh-keygen -t rsa -P ''
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa):
Created directory '/root/.ssh'.
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
db:2e:65:33:b2:57:38:a2:43:b2:79:fc:78:fa:02:9d root@server19
The key's randomart image is:
+--[ RSA 2048]----+
| |
| |
| |
| |
| . .S . |
| o E ooB . |
| B ..*.= |
| o *.+.. |
| .oBoo. |
+-----------------+
[root@server19 ~]# cat ~/.ssh/id_rsa.pub > ~/.ssh/authorized_keys
[root@server19 ~]# chmod 600 ~/.ssh/authorized_keys
[root@server19 ~]# scp -p ~/.ssh/id_rsa ~/.ssh/authorized_keys 172.25.60.30:/root/.ssh/
The authenticity of host '172.25.60.30 (172.25.60.30)' can't be established.
RSA key fingerprint is 78:a0:b2:52:c2:6a:35:d4:5c:d9:d5:2d:93:49:75:c5.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.25.60.30' (RSA) to the list of known hosts.
root@172.25.60.30's password:
scp: /root/.ssh/: No such file or directory
[root@server19 ~]# scp -p ~/.ssh/id_rsa ~/.ssh/authorized_keys 172.25.60.30:~root/.ssh/
root@172.25.60.30's password:
scp: /root/.ssh/: No such file or directory
[root@server19 ~]# pwd
/root
[root@server19 ~]# cd .ssh/
[root@server19 .ssh]# ls
authorized_keys id_rsa id_rsa.pub known_hosts
[root@server19 .ssh]# cd
[root@server19 ~]# scp -p ~/.ssh/id_rsa ~/.ssh/authorized_keys 172.25.60.30:~root/.ssh/
root@172.25.60.30's password:
id_rsa 100% 1679 1.6KB/s 00:00
authorized_keys 100% 395 0.4KB/s 00:00
[root@server19 ~]# scp -p ~/.ssh/id_rsa ~/.ssh/authorized_keys 172.25.60.31:~root/.ssh/
The authenticity of host '172.25.60.31 (172.25.60.31)' can't be established.
RSA key fingerprint is 5e:08:3e:62:c2:56:0a:bf:1c:13:ab:86:37:4f:de:a5.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.25.60.31' (RSA) to the list of known hosts.
root@172.25.60.31's password:
id_rsa 100% 1679 1.6KB/s 00:00
authorized_keys 100% 395 0.4KB/s 00:00
[root@server19 ~]# scp -p ~/.ssh/id_rsa ~/.ssh/authorized_keys 172.25.60.32:~root/.ssh/
The authenticity of host '172.25.60.32 (172.25.60.32)' can't be established.
RSA key fingerprint is e5:b7:6a:17:42:a5:91:fc:d9:55:76:57:6f:aa:17:bb.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.25.60.32' (RSA) to the list of known hosts.
root@172.25.60.32's password:
id_rsa 100% 1679 1.6KB/s 00:00
authorized_keys 100% 395 0.4KB/s 00:00
测试免密登录：
[root@server19 ~]# ssh server20 'hostname -I'
The authenticity of host 'server20 (172.25.60.30)' can't be established.
RSA key fingerprint is 78:a0:b2:52:c2:6a:35:d4:5c:d9:d5:2d:93:49:75:c5.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'server20' (RSA) to the list of known hosts.
172.25.60.30
[root@server19 ~]# ssh server21 'hostname -I'
The authenticity of host 'server21 (172.25.60.31)' can't be established.
RSA key fingerprint is 5e:08:3e:62:c2:56:0a:bf:1c:13:ab:86:37:4f:de:a5.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'server21' (RSA) to the list of known hosts.
172.25.60.31
[root@server19 ~]# ssh server22 'hostname -I'
The authenticity of host 'server22 (172.25.60.32)' can't be established.
RSA key fingerprint is e5:b7:6a:17:42:a5:91:fc:d9:55:76:57:6f:aa:17:bb.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'server22' (RSA) to the list of known hosts.
172.25.60.32
各节点都要准备mha4mysql-node-0.56-0.el6.noarch.rpm
监控节点另加mha4mysql-manager-0.56-0.el6.noarch.rpm
[root@server19 ~]# ls
mha4mysql-manager-0.55-0.el6.noarch.rpm
mha4mysql-node-0.54-0.el6.noarch.rpm
perl-Config-Tiny-2.12-7.1.el6.noarch.rpm
perl-Email-Date-Format-1.002-5.el6.noarch.rpm
perl-Log-Dispatch-2.27-1.el6.noarch.rpm
perl-Mail-Sender-0.8.16-3.el6.noarch.rpm
perl-Mail-Sendmail-0.79-12.el6.noarch.rpm
perl-MailTools-2.04-4.el6.noarch.rpm
perl-MIME-Lite-3.027-2.el6.noarch.rpm
perl-MIME-Lite-HTML-1.23-2.el6.noarch.rpm
perl-MIME-Types-1.28-2.el6.noarch.rpm
perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm
perl-Params-Validate-0.92-3.el6.x86_64.rpm
perl-TimeDate-1.16-13.el6.noarch.rpm
[root@server19 ~]# yum install -y *.rpm
在监控主机为各application提供默认配置
Application配置：
[root@server19 ~]# mkdir /etc/masterha.cnf
[root@server19 ~]# vim /etc/masterha.cnf/app1.cnf
[server default]
user=mhauser
password=mhapass
manager_workdir=/data/masterha/app1
manager_log=/data/masterha/app1/manager.log
remote_workdir=/data/masterha/app1
ssh_user=root
repl_user=repluser
repl_password=replpass
ping_interval=1
[server20]
hostname=172.25.60.30
[server21]
hostname=172.25.60.31
[server22]
hostname=172.25.60.32
检查ssh状态，如果有如下状态则显示通过，一切正常
[root@server19 ~]# masterha_check_ssh --conf=/etc/masterha.cnf/app1.cnf
Sat Jun 17 00:54:03 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Jun 17 00:54:03 2017 - [info] Reading application default configurations from /etc/masterha.cnf/app1.cnf..
Sat Jun 17 00:54:03 2017 - [info] Reading server configurations from /etc/masterha.cnf/app1.cnf..
Sat Jun 17 00:54:03 2017 - [info] Starting SSH connection tests..
Sat Jun 17 00:54:04 2017 - [debug]
Sat Jun 17 00:54:03 2017 - [debug] Connecting via SSH from root@172.25.60.30(172.25.60.30:22) to root@172.25.60.31(172.25.60.31:22)..
Warning: Permanently added '172.25.60.31' (RSA) to the list of known hosts.
Sat Jun 17 00:54:03 2017 - [debug] ok.
Sat Jun 17 00:54:03 2017 - [debug] Connecting via SSH from root@172.25.60.30(172.25.60.30:22) to root@172.25.60.32(172.25.60.32:22)..
Warning: Permanently added '172.25.60.32' (RSA) to the list of known hosts.
Sat Jun 17 00:54:03 2017 - [debug] ok.
Sat Jun 17 00:54:04 2017 - [debug]
Sat Jun 17 00:54:04 2017 - [debug] Connecting via SSH from root@172.25.60.31(172.25.60.31:22) to root@172.25.60.30(172.25.60.30:22)..
Warning: Permanently added '172.25.60.30' (RSA) to the list of known hosts.
Sat Jun 17 00:54:04 2017 - [debug] ok.
Sat Jun 17 00:54:04 2017 - [debug] Connecting via SSH from root@172.25.60.31(172.25.60.31:22) to root@172.25.60.32(172.25.60.32:22)..
Sat Jun 17 00:54:04 2017 - [debug] ok.
Sat Jun 17 00:54:05 2017 - [debug]
Sat Jun 17 00:54:04 2017 - [debug] Connecting via SSH from root@172.25.60.32(172.25.60.32:22) to root@172.25.60.30(172.25.60.30:22)..
Warning: Permanently added '172.25.60.30' (RSA) to the list of known hosts.
Sat Jun 17 00:54:04 2017 - [debug] ok.
Sat Jun 17 00:54:04 2017 - [debug] Connecting via SSH from root@172.25.60.32(172.25.60.32:22) to root@172.25.60.31(172.25.60.31:22)..
Warning: Permanently added '172.25.60.31' (RSA) to the list of known hosts.
Sat Jun 17 00:54:04 2017 - [debug] ok.
Sat Jun 17 00:54:05 2017 - [info] All SSH connection tests passed successfully.
[root@server19 ~]# vim /etc/masterha.cnf/app1.cnf