Use MySQL-Proxy for MySQL database read/write splitting

Use MySQL-Proxy for MySQL database read/write splitting

Experimental System: CentOS 6.6 _ x86_64
Prerequisites: Firewall and selinux are both disabled.
Tutorial Description: In this experiment, there are a total of four hosts, with IP addresses allocated as topology.
Experimental software: mariadb-10.0.20 mysql-proxy-0.8.5-linux-el6-x86-64bit

: Http://pan.baidu.com/s/1o6EFEQm

MySQL Proxy read/write splitting practice

Install the latest MySQL Proxy in CentOS 5.2

Install MySQL Proxy in RHEL5.5

Mysql proxy and mysql-mmm achieve high availability of read/write splitting

Tutorial topology:

　

I. preparations:

1. Change the host name as follows:

2. Add the following content to the hosts file:

3. Install mariadb on the master, slave1, and slave2:

Tar xf mariadb-10.0.20-linux-x86_64.tar.gz-C/usr/local/
Cd/usr/local/
Ln-sv mariadb-10.0.20-linux-x86_64 mysql
Useradd-r mysql
Mkdir-pv/mydata/data
Chown-R mysql. mysql/mydata/data/
Cd mysql/
Chown-R root. mysql.
Scripts/mysql_install_db -- user = mysql -- datadir =/mydata/data/
Cp support-files/my-large.cnf/etc/my. cnf
Cp support-files/mysql. server/etc/init. d/mysqld
Chkconfig -- add mysqld
Chkconfig mysqld on

Ii. Configure master-slave Replication

1. Configure my. cnf on the master:

[Mysqld]
Server-id = 1
Datadir =/mydata/data
Log-bin =/mydata/data/master-bin
Binlog_format = ROW
Sync_binlog = 1 // ensure that binary logs can be synchronized to the disk before each transaction is committed.

2. Configure my. cnf on slave1:

[Mysqld]
# Log-bin = mysql-bin
# Binlog_format = mixed
Server-id = 2
Datadir =/mydata/data
Relay_log =/mydata/data/relay-log
Read_only = 1
Sync_master_info = 1 // synchronize master files in time
Sync_relay_log = 1 // synchronize the relay-log file in time
Sync_relay_log_info = 1 // synchronize the relay-log-info file in time

3. Configure my. cnf on slave2:

[Mysqld]
# Log-bin = mysql-bin
# Binlog_format = mixed
Server-id = 3
Datadir =/mydata/data
Relay_log =/mydata/data/relay-log
Read_only = 1
Sync_master_info = 1
Sync_relay_log = 1
Sync_relay_log_info = 1

4. Create a copy user on the master:

Service mysqld start
/Usr/local/mysql/bin/mysql
------------------------------------------->
Grant replication slave, replication client on *. * TO 'slave '@ '2017. 192. %' identified by '20160301 ';
Flush privileges;

5. view the binary log location on the master:

Show master logs;

6. perform the following operations on two slave servers:

Change master to MASTER_HOST = 'master', MASTER_USER = 'slave ', MASTER_PASSWORD = '000000', MASTER_LOG_FILE = 'master-bin.000001', MASTER_LOG_POS = 123456;
Start slave;
Show slave status \ G

For more details, please continue to read the highlights on the next page:

1 2 Next Page