High Availability of read/write splitting using mysqlproxy and mysql-mmm

Mysqlproxy and mysql-mmm test environment for read/write Splitting: mysqlA: 192.168.128.6mastermysqlB: 192.168.128.7mastermysqlproxy + mysql-mmm: 192.168.128.5 database write operation: 192.

Mysqlproxy and mysql-mmm test environment for read/write Splitting: mysqlA: 192.168.128.6mastermysqlB: 192.168.128.7mastermysqlproxy + mysql-mmm: 192.168.128.5 database write operation: 192.

Mysql proxy and mysql-mmm for read/write splitting
Test environment:
Mysql A: 192.168.128.6 master
Mysql B: 192.168.128.7 master
Mysql proxy + mysql-mmm: 192.168.128.5
Database write: 192.168.128.8
Database read operations: 192.168.128.9, 192.168.128.10
(The read/write ip address of the database is a virtual ip address and is not configured on the NIC)
Note: mysqlA and B are mutually master nodes for database replication; mysql-mmm achieves high availability and website space. When one server goes down, the database read/write operations can be automatically switched to the other; mysql proxy for read/write splitting
I. Mysql database installation (skipped here)
Ii. Mysql Database Replication configuration. The two databases are mutually master databases.
1. Operate on database
Log on to mysql
Mysql-u root-p
Authorize the user to synchronize data from server B
Mysql> grant replication slave on *. * to 'slavedb' @ '192. 168.128.7 'identified by '2016 ';
Check the status of the master server, record the red font mark, and configure slave server backup (NOTE: When the database is in the production system, you must first set the table to prevent the database from writing data flush tables with read lock ;)
Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000029 | 106 |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.03 sec)
Modify the mysql configuration file
Vi/etc/my. cnf
Log-bin = mysql-bin
Server-id = 1 # Set server-id to 1, 1 to indicate the master server
Binlog-do-db = # databases to be synchronized. All databases can be synchronized.
Binlog-ignore-db = # databases that do not need to be synchronized
2. Operate on Database B
Log on to mysql
Mysql-u root-p
Authorize the user to synchronize data from server B
Mysql> grant replication slave on *. * to 'slavedb' @ '192. 168.128.6 'identified by '2016 ';
Check the status of the master server, record the red font, and configure slave server backup
Mysql> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000005 | 107 |
+ ------------------ + ---------- + -------------- + ------------------ +
Modify the mysql configuration file
Vi/etc/my. cnf
Log-bin = mysql-bin
Server-di = 2 # Set server-id to 2
Binlog-do-db = # set as needed
Binlog-ignore-db = # set as needed
3. log on to mysql A and mysql B to execute Database Synchronization commands.
Mysql-u root-p
Stop slave Synchronization
Mysql> salve stop;

Database A executes the synchronization command. The master_log_file and master_log_pos options need to be filled in according to the master data.
Mysql> Change master
Master_host = '1970. 168.128.7 ',
Master_user = 'slavedb ',
Master_password = '000000 ',
Master_log_file = 'mysql-bin.000005 ',
Master_log_pos = 107;

Database B executes the synchronization command. The master_log_file and master_log_pos options need to be filled in according to the master data.
Mysql> Change master
Master_host = '1970. 168.128.6 ',
Master_user = 'slavedb ',
Master_password = '000000 ',
Master_log_file = 'mysql-bin.000029 ',
Master_log_pos = 106;

Start slave Synchronization
Mysql> salve start;
Check the synchronization status of A. The values of Slave_IO_Running, virtual host, and Slave_ SQL _Running are both yes, indicating that synchronization is enabled.
Mysql> show slave status \ G
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.128.7
Master_User: slavedb
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 107
Relay_Log_File: mysqld-relay-bin.000026
Relay_Log_Pos: 252
Relay_Master_Log_File: mysql-bin.000005
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
View synchronization status of B
Mysql> show slave status \ G
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.128.6
Master_User: slavedb
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000029
Read_Master_Log_Pos: 106
Relay_Log_File: lamp-relay-bin.000026
Relay_Log_Pos: 252
Relay_Master_Log_File: mysql-bin.000029
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
4. Test master-slave Replication
Create Database db1 in database
Mysql> create database db1;
Create a database db2 in database B
Mysql> create database db2;

Check whether the database is copied in database A and database B.
Mysql> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Db1 |
| Db2 |
| Mysql |
3. Configure mysql-mmm to implement hot standby high-availability Databases
1. Install mysql-mmm on servers 128.5, 128.6, and 128.7 respectively. perl is required.
Install the perl module on the 128.6 and 128.7mysql servers
# Perl-MCPAN-e shell
Cpan> install Algorithm: Diff
Cpan> install DBI
Cpan> install Log: Dispatch
Cpan> install Log: Log4perl
Cpan> install Mail: Send
Cpan> install Net: ARP
Cpan> install Proc: Daemon
Cpan> install Time: HiRes
Cpan> install DBD: mysql
Cpan> install File: stat
Cpan> install File: basename

Install the perl module on server 128.5
# Perl-MCPAN-e shell
Cpan> install Algorithm: Diff
Cpan> install Class: Singleton
Cpan> install Log: Dispatch
Cpan> install Log: Log4perl
Cpan> install Mail: Send
Cpan> install Proc: Daemon
Cpan> install Thread: Queue
Cpan> install Time: HiRes
Cpan> install DBI
Cpan> install DBD: mysql

Download mysql-mmm
Wget: mmm2: mysql-mmm-2.2.1.tar.gz
You can also install the EPEL yum Source

Install mysql-mmm
Tar-zxvf mysql-mmm-2.2.1.tar.gz
Cd mysql-mmm-2.2.1
Make install

Mysql-mmm file location and usage
/Usr/lib/perl5/vendor_perl/5.8.8/perl module used by MMM
/Usr/lib/mysql-mmm MMM script example
/Usr/sbin MMM command save path
/Var/log/mysql-mmm MMM log storage path
/Etc MMM configuration file storage path
/Etc/mysql-mmm MMM configuration file storage path with the highest priority
/Etc/init. d/agentd and monitor enable and disable scripts

2. Modify the mysql-mmm configuration file
Database A configuration file
Vi/etc/mysql-mmm/mmm_agent.conf
Include mmm_common.conf
This db1

Vi/etc/mysql-mmm/mmm_common.conf
Active_master_role writer


Cluster_interface eth0

Pid_path/var/run/mmm_agentd.pid
Bin_path/usr/lib/mysql-mmm/

Replication_user slavedb
Replication_password 123456

Agent_user mmm_agent
Agent_password mmm_agent



Ip address 192.168.128.6
Mode master
Peer db2



Ip 192.168.128.7
Mode master
Peer db1



Hosts db1, db2
Ips 192.168.128.8
Mode exclusive



Hosts db1, db2
Ips 192.168.128.9, 192.168.128.10
Mode balanced


Copy the mmm_common.conf configuration file to database B.
Database B configuration file
Vi/etc/mysql-mmm/mmm_agent.conf
Include mmm_common.conf
This db2

Add authorized users to databases A and B
Grant all privileges on *. * TO "mmm_agent" @ "192.168.128.7" identified by 'mmm _ agent' with grant option;
Grant all privileges on *. * TO "mmm_agent" @ "192.168.128.6" identified by 'mmm _ agent' with grant option;

Start the mysql-mmm client on Database A and database B
[Root @ nagios soft] # service mysql-mmm-agent start
Daemon bin: '/usr/sbin/mmm_agentd'
Daemon pid: '/var/run/mmm_agentd.pid'
Starting MMM Agent daemon... OK


Modify mysql-mmm-monitor on the 128.5 Server
Vi/etc/mysql-mmm/mmm_mon.conf
Include mmm_common.conf

Ip 127.0.0.1
Pid_path/var/run/mmm_mond.pid
Bin_path/usr/lib/mysql-mmm/
Status_path/var/lib/misc/mmm_mond.status
Ping_ips 192.168.128.6, 192.168.128.7



Monitor_user mmm_monitor
Monitor_password mmm_monitor

Debug 0

Vi/etc/mysql-mmm/mmm_common.conf
Active_master_role writer

Ip address 192.168.128.6
Mode master
Peer db2



Ip 192.168.128.7
Mode master
Peer db1




Hosts db1, db2
Ips 192.168.128.8
Mode exclusive



Hosts db1, db2
Ips 192.168.128.9, 192.168.128.10
Mode balanced


Add an authorized account to databases A and B
Grant all privileges on *. * TO "mmm_monitor" @ "192.168.128.5" identified by 'mmm _ monitor' with grant option;

Start mysql-mmm-monitor
[Root @ centos ~] # Service mysql-mmm-monitor start
Daemon bin: '/usr/sbin/mmm_mond'
Daemon pid: '/var/run/mmm_mond.pid'
Starting MMM Monitor daemon: OK

View mysql-mmm status
[Root @ centos ~] # Mmm_control show
Db1 (192.168.128.6) master/ONLINE. Roles: reader (192.168.128.10)
Db2 (192.168.128.7) master/ONLINE. Roles: reader (192.168.128.9), writer (192.168.128.8)

Set the database server to online
[Root @ centos ~] # Mmm_control set_online db1

Test mysql-mmm, write operation on the db2 server, stop the db2 database, read operation will automatically switch to db1
[Root @ centos ~] # Mmm_control show
Db1 (192.168.128.6) master/ONLINE. Roles: reader (192.168.128.10), reader (192.168.128.9), writer (192.168.128.8)
Db2 (192.168.128.7) master/HARD_OFFLINE. Roles:

Connect to the virtual ip128.8, 128.9, and 128.10 respectively on the client for read/write testing.

4. Configure database read/write splitting, which is completed by the mysql proxy
1. Configure mysql proxy and download 32-bit or 64-bit as needed
Wget
Wget
Decompress mysql proxy
Tar-zxvf mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit.tar.gz
Cp-r mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit/usr/local/mysql-proxy
2. Create the mysql proxy configuration file. For details about the parameters, see/usr/local/mysql-proxy/bin/mysql-proxy-help-all.
Vi/etc/mysql-proxy.cnf
[Mysql-proxy]
User = root # specify the mysql-proxy running user
Proxy-address = 192.168.128.5: 4040 # mysql-proxy running ip address and port number
Proxy-read-only-backend-addresses = 192.168.128.9: 3306 # read operation database address
Proxy-read-only-backend-addresses = 192.168.128.10: 3306
Proxy-backend-addresses = 192.168.128.8: 3306 # write operation database address
Proxy-lua-script =/usr/local/mysql-proxy/scripts/rw-splitting.lua # path to the read/write splitting configuration file
Log-file =/usr/local/mysql-proxy/log/mysql-proxy.log # log file storage path
Log-level = debug
Daemon = true # Run as a daemon
Keepalive = true # mysql-proxy automatically restarts after the process is suspended

3. Create a log storage directory
Mkdir/usr/local/mysql-proxy/log
4. Copy the read/write splitting configuration file
Mkdir/usr/local/mysql-proxy/scripts
Cp/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua/usr/local/mysql-proxy/scripts

5. Modify the read/write splitting configuration file. By default, four connections are used for read/write splitting.
Vi/usr/local/mysql-proxy/scripts/rw-splitting.lua
If not proxy. global. config. rwsplit then
Proxy. global. config. rwsplit = {
Min_idle_connections = 1, # default value: 4, changed to 1
Max_idle_connections = 1, # default value: 8, virtual host, changed to 1
Is_debug = false
}
End

6. Modify mysql-proxy.cnf Permissions
Chmod 660/etc/mysql-proxy.cnf

7. Start mysql proxy
/Usr/local/mysql-proxy/bin/mysql-proxy -- defaults-file =/etc/mysql-proxy.cnf

8. Create a database connection account for read/write splitting
Log on to the master server and create a user
Mysql> grant all privileges on *. * TO "proxy" @ "192.168.128.5" identified by 'proxy' with grant option;
9. Test read/write splitting
Log on to the proxy from the client and perform a test.
[Root @ mysql ~] # Mysql-u test-h192.168.128.5-p-P4040
Enter password:
Welcome to the MySQL monitor. Commands end with; or \ g.
Your MySQL connection id is 61
Server version: 5.1.61-log Source distribution
Copyright (c) 2000,201 1, 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> show databases;
+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Db1 |
| Db2 |
| Db3 |
| Mysql |
| Zabbix |
+ -------------------- +
6 rows in set (0.16 sec)
The query is a 128.6 database.
Disable mysql 128.6.
Mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 199
Current database: *** NONE ***

+ -------------------- +
| Database |
+ -------------------- +
| Information_schema |
| Db1 |
| Db2 |
| Db3 |
| Mysql |
| Performance_schema |
| Test |
| Wikidb |
+ -------------------- +
8 rows in set (0.02 sec)

The query content is 128.7 Database

This article is from the "lishiy" blog. Please keep this source