MySQL double hot standby and amoeba read and write separation experiment

MySQL read-write separation is to improve the speed of Web site access, improve the database of concurrent load capacity.

However, in the actual production environment, the single MySQL as a stand-alone database is completely unable to meet the actual needs, whether in security, high availability and high concurrency and other aspects.

Therefore, it is generally through the master-slave Replication (Master-slave) to synchronize the data, and then read-write separation to enhance the database's concurrent load capacity, such a scheme for deployment and implementation.

Note: The first decision from the copy, again read and write separation.

The MySQL master-slave copy structure is based on the MySQL Bin-log log, from the library by opening the IO process to receive the main library's Bin-log log increment information, and save to the local relay log, and then by opening the MySQL process from relay The increment information obtained on log is translated into the SQL statement and written to the database.

Experimental environment:

Main: 10.8.0.150

From: 10.8.0.151

amoeba:10.8.0.160

PS: In order to facilitate the experiment, can directly yum MySQL or direct script execution Lnmp.

The topology map is as follows:

The first step of the experiment:

Log on to the master server Operation command

mysql> GRANT REPLICATION client,replication SLAVE on *.* to ' cong151 ' @ ' 10.8.0.151 ' identified by ' 123456 ';

Mysql> Show master status;

+------------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+------------------+----------+--------------+------------------+

| mysql-bin.000008 |  5722410 |   | |

+------------------+----------+--------------+------------------+

Remember file and position.

Login from server Operation command

1, edit My.ini file, modify Server-id = 2 (default is 1, the master copy of MySQL, need to modify the ID and the main server is not the same, and can not be the same as the intranet MySQL server with other IDs)

2, log into MySQL

mysql> Change Master to master_host= ' 10.8.0.150 ', master_user= ' cong151 ', master_password= ' 123456 ', master_log_file = ' mysql-bin.000008 ', master_log_pos=5722410;

mysql> start slave;

Mysql> Show Slave Status\g

1. Row ***************************

Slave_io_state:waiting for Master to send event

master_host:10.8.0.150

master_user:cong151

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000008

read_master_log_pos:5722410

relay_log_file:localhost-relay-bin.000061

relay_log_pos:251