Use MySQL-Proxy for MySQL database read/write splitting

Source: Internet
Author: User
Experimental System: CentOS6.6 _ x86_64 experimental premise: Firewall and selinux are closed experiment Description: This experiment has a total of 4 hosts, IP allocation such as topology experimental software: mariadb-10.

Experimental System: CentOS 6.6 _ x86_64 experimental premise: Firewall and selinux are closed experiment Description: This experiment has a total of 4 hosts, IP allocation such as topology experimental software: mariadb-10.

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

:

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:

,

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.