MySQL double-click hot standby +amoeba read/write Detach

MySQL Read and write separation is to improve the access speed of the website, improve the concurrency of the database load capacity.

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

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

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

MySQL master-slave replication structure is based on the MySQL Bin-log log, from the library by opening the IO process to receive the main library Bin-log log increment information, and save to the local relay log, and then by opening the MySQL process from relay Log gets the increment information and translates it into a SQL statement after it is written to the slave database.

Lab Environment:

Master: 10.8.0.150

From: 10.8.0.151

amoeba:10.8.0.160

PS: In order to facilitate the experiment, you can execute LNMP directly with Yum MySQL or direct script.

The topology diagram is as follows:

232123111.jpg

The first step of the experiment:

Log in to master server Operations 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.

Log in from server Operation command

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

2. Log in to 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

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 means that the data from the server to replicate the primary server is normal, and you can specify the replicated libraries, which can be modified in My.ini.

In order to master from replication, that is, from copying the primary and primary copying from.

Continue to operate from the 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 in to the master server

mysql> Change Master to master_host= ' 10.8.0.151 ', master_user= ' zhu150 ', master_password= ' 123456 ', master_log_file= ' mysql-bin.000006 ', master_log_pos=786;

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, MySQL master-slave replication experiment OK.

Installation of Amoeba:

Install the JDK before installing the amoeba, because Amoeba is developed with Java to have JDK support.

Download jdk-6u15-linux-i586.bin (SH jdk-6u15-linux-i586.bin execution program)

Edit/etc/profile 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 VMs (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

So the rest is how to configure the Amoeba (config file in the Conf directory, mainly dbservers.xml and amoeba.xml two files)

Edit the Dbservers.xml file first

235709615.jpg

Note At this time the user name and password for the back-end database user name and password, need to open remote, remote user name and password on both database servers to set themselves, I am for convenience.

000021663.jpg

Add two nodes, Master and slave, and modify the next virtual group to add master and slave. You'll need it later.

In the edit Amoeba.xml file

000319218.jpg

We recommend that you modify the IP of the proxy server under IP amoeba, and do not recommend writing 127.0.0.1 or localhost.

The following user name and password, is the database user name and password provided externally, that is, amoeba login user name and password, do not need to add this user name and password on the master and slave server, I set up this is also for convenience.

000321280.jpg

The default group is master, which means that after logging in with amoeba, it is actually logged on to the primary server.

The Write group is master, which indicates that the data is written to the primary server.

The read group is the virtual group we set up earlier (including Master and Slave, which has the effect of querying two times, one at a time on the main and one from the above)

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

Directly using/usr/local/amoeba/bin/amoeba start to check, if there is a problem, please resolve immediately.

001135717.jpg

If you do not see the problem, like this log, please CTRL + C to close the process. Also use/usr/local/amoeba/bin/amoeba start & to start and put the program in the background.

001138320.jpg

The last thing left is the test.

The first test is to connect to the server via amoeba.

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

001430214.jpg

Second, the direct operation of the database, the new library, new tables, table writing data to see if the data is normal. The main test is how the MySQL master-slave synchronization effect.

Finally, turn off master-slave synchronization, Command: Stop slave; Two backend servers need to operate, so that master-slave replication is stopped, insert data again, view two times, look at the comparison.

If a change is found, then your experiment is OK and no effect is found, please check the amoeba two configuration files.

Pay attention to a little:<--! --This is a comment, and before I did the experiment, I did it because the problem was the first time I didn't succeed.

I hope you have good luck.

If there is a successful person, and actually get the test environment (build website test), please tell how PHP call amoeba, the individual did not take care of. For a sense!

MySQL double-click hot standby +amoeba read/write Detach