Mysql double-click Hot Standby + amoeba read/write splitting Experiment

Mysql read/write splitting is used to speed up Website access and improve concurrent database load capabilities.

However, in the actual production environment, a single Mysql database as an independent database cannot meet the actual needs, whether in terms of security, high availability and high concurrency.

Therefore, in general, data is synchronized through Master-Slave replication (Master-Slave), and the database's concurrent load capacity is improved through read/write splitting. This solution is deployed and implemented.

Note: Perform master-slave replication and read/write splitting again.

The MySQL master-slave replication structure is based on the mysql bin-log. The slave database receives the bin-log incremental information of the master database by opening the IO process and saves it to the local relay log, then, the incremental information obtained from the relay log by opening the MYSQL process and translating it into an SQL statement and writing it to the slave database.

Lab environment:

MASTER: 10.8.0.150

From: 10.8.0.151

Amoeba: 10.8.0.160

PS: For the convenience of the experiment, you can directly execute lnmp using yum mysql or a script.

The topology is as follows:

Lab Step 1:

Log on to the master server

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.

Log on to the slave server

1. edit my. ini file, modify server-id = 2 (default value: 1, because of the master-slave replication of mysql, You need to modify the ID and the master server is different, it cannot be the same as other IDs of the mysql server on the Intranet)

2. log on to mysql

Mysql> change master to master_host = '10. 8.0.150 ', master_user = 'cong151', master_password = '000000', master_log_file = 'mysql-bin.000008', master_log_pos = 123456;

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

Relay_Master_Log_File: mysql-bin.000008

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: 5722410

Relay_Log_Space: 3466785

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: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_ SQL _Errno: 0

Last_ SQL _Error:

This indicates that it is normal to copy data from the master server from the server, and you can specify the database to be copied. You can modify it in my. ini.

For master-slave replication, that is, master-slave replication and master-slave replication.

Continue on the slave server.

Mysql> grant replication client, replication slave on *. * TO 'zhu150 '@ '10. 8.0.150' identified by "123456 ";

Mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| Mysql-bin.000006 | 786 |

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

Log on to the master server

Mysql> change master to master_host = '10. 8.0.151 ', master_user = 'zhu150', master_password = '000000', master_log_file = 'mysql-bin.000006 ', master_log_pos = 123456;

Mysql> start slave;

Mysql> show slave status \ G

* *************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.8.0.151

Master_User: zhu150

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos: 786

Relay_Log_File: localhost-relay-bin.000003

Relay_Log_Pos: 251

Relay_Master_Log_File: mysql-bin.000006

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: 786

Relay_Log_Space: 935

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: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_ SQL _Errno: 0

Last_ SQL _Error:

In this way, the master-slave replication experiment of mysql is OK.

Amoeba installation:

Install JDK before installing Amoeba, because Amoeba is developed in java with JDK support.

Download jdk-6u15-linux-i586.bin (sh jdk-6u15-linux-i586.bin executable)

Edit/etc/profile and add the following code:

Export JAVA_HOME =/usr/local/jdk1.6.0 _ 15

Export CLASSPATH =.: $ JAVA_HOME/jre/lib/rt. jar: $ JAVA_HOME/lib/dt. jar: $ JAVA_HOME/lib/tools. jar

Export PATH = $ PATH: $ JAVA_HOME/bin

[Root @ localhost ~] # Source/etc/profile

[Root @ localhost ~] # Java-version

Java version "1.6.0 _ 15"

Java (TM) SE Runtime Environment (build 1.6.0 _ 15-b03)

Java HotSpot (TM) Client VM (build 14.1-b02, mixed mode, sharing)

Download amoeba-mysql-binary-2.0.1-beta.tar.gz

[Root @ localhost ~] # Mkdir/usr/local/amoeba & cd/usr/local/amoeba/& tar/root/amoeba-mysql-binary-2.0.1-BETA.tar.gz

The rest is how to configure amoeba (the configuration file is in the conf directory, mainly dbServers. xml and amoeba. xml files)

Edit the dbServers. xml file first.

Note that the username and password at this time are the username and password of the backend database. You need to enable remote connection on the two database servers. You can set the remote username and password for your convenience.

Add two nodes, master and slave, and modify the next Virtual Group to add master and slave. It will be needed later.

Edit the amoeba. xml file

We recommend that you change the IP address to the IP address of the agent server of AMOEBA. Do not enter 127.0.0.1 or localhost.

The following username and password are the database username and password provided externally, that is, the username and password for AMOEBA login. You do not need to add this username and password to the Master/Slave server, I set this for convenience.

The default group is the master, which indicates that after the amoeba is used to log on to the master server.

If the write group is a master node, data is written to the master server.

The read group is the virtual group we set up previously (including master and slave, the result is that the query is twice, one query on the master and one query from the above)

Above, the amoeba server configuration is OK, and the rest is to check the configuration,

Use/usr/local/amoeba/bin/amoeba start directly to check. If any problem occurs, resolve it immediately.

If no problem is found, log like this, press ctrl + c to close the process. At the same time, use/usr/local/amoeba/bin/amoeba start & to start and put the program into the background.

The last thing left is the test.

First, you need to test the connection to the server through amoeba,

Case command: mysql-uroot-pu2ktgshc # @ dq-h10.8.0.160-P8066

Second, directly operate on the database, create a database, create a table, and write data in the table to check whether the data is normal. It mainly tests the effect of master-slave synchronization in mysql.

Finally, disable master-slave synchronization. Run the command: stop slave. Both backend servers need to perform operations. In this way, Master-slave replication is stopped, data is inserted again, and data is viewed twice for comparison.

If any change is found, your experiment is OK and no effect is found. Check the two configuration files of amoeba.

Note: <--! --> This is a comment. I did my experiments for the first time because of this problem.

Good luck.

If you are successful and are able to get the test environment (Building a website for testing), please inform us how to call amoeba in php. Sense!

In subsequent experiments, we can use two front-end servers for amoeba, the backend mysql server, and the two amoeba using LVS + Keepalived for cluster and hot backup.

This article is from the "young kids shoes" blog, please be sure to keep this http://yangxiaofei.blog.51cto.com/2910082/1309978