Mysql master-slave replication, read/write splitting (mysql-proxy), full dual-master structure construction process, mysqlmysql-proxy

Mysql master-slave replication, read/write splitting (mysql-proxy), full dual-master structure construction process, mysqlmysql-proxy

The following describes the complete construction process of MySQL master-slave replication, read/write splitting, and dual-master structure. It does not involve too many theories, but only involves experiments and configuration.
Mysql master-slave replication (for reprint, please indicate the source, blog address :)
The principle is that the master will record the changes to the binary log (binary log). slave will copy the binary log of the master to the relay log, and slave will synchronize the operations of the master through the relay log.
1. In the experiment environment, two Ubutu servers 14 have been installed with the mysql server, which is in the same IP address segment.
172.16.34.212 (master ),
172.16.34.156 (from ).

2. authorize these two IP addresses to allow other IP addresses to access through the account and password (for example, add an euht Account to allow access from all external IP addresses with a password of 123456 ), log on to the mysql database of the two machines and execute the following statement:
Grant all privileges on.To 'euht '@' % 'identified by '000000' with grant option;
Flush privileges;

Note:
① The code above indicates creating an euht user, host = %, which allows all IP addresses to access through the username euht. The refresh permission is added.
② At this time, the mysql of the two machines should be accessible to each other. If not, there are many causes. The most common reason is that the firewall is disabled and the mysql server is bound with a local address. You can solve the problem by using the following methods:
Disable the firewall or open port 3306.
 
Change the my. cnf file and comment out bind-address.
Vi/etc/mysql/my. cnf
 
3. Find the master server 172.16.34.212MySQL installation folder and modify the my. cnf (etc/mysql/my. cnf) file. Add the following lines of code under [mysqld ]:
Server-id = 1
Log-bin = master-bin
Log-bin-index = master-bin.index
Restart mysql (service mysql restart;) after saving ;)
Note: If you only need to synchronize a specific database, add the code above, binlog-do-db = euht, for the specific database of master-slave
4, enter mysql, view the master server mysql master Status, log File is master-bin.000001, Position is 107 (write down these two values, need to be used later when operating from the server)
 
5. Configure slave server (172.16.34.156)
Configure the slave server to allow access from external IP addresses (refer)

Configuration Log File

Find the server 172.16.34.156MySQL installation folder and modify my. cnf (etc/mysql/my. cnf) file, add the following lines of code under [mysqld] (server-id and master server should not be the same)
Server-id = 10
Log-bin = master-bin
Log-bin-index = master-bin.index

6. Connect to the master server (connect to mysql 156 and execute the following statement to change the content)
Change master
Master_host = '1970. 16.34.212 ',
Master_user = 'euht ',
Master_password = '000000 ',
Master_log_file = 'master-bin.000001 ',
Master_log_pos = 107;

7. Start slave
Mysql> start slave;

8. view the slave status
Mysql> show slave status \ G
The values of Slave_IO_Running and Slave_ SQL _Running must be YES to indicate that the status is normal.
 
All the changes in the database of the master server 172.16.34.162 can be synchronized to the slave server 172.16.34.156.

The test is as follows:
1. No custom database is set for both servers.

2. Create a database to the master database (172.16.34.212), and refresh the slave database observation (172.16.34.156)

We can see that 212 of all operations are synchronized to the slave database from 156. Now the master-slave replication is complete.
Note the following:
1. During master-slave replication, first make sure that the mysql of the two servers does not have any user-defined libraries (otherwise, the previous items cannot be synchronized after configuration is complete, or the two databases have identical databases, and they can be synchronized)
2, server_id must be configured differently
3. The firewall cannot intercept the mysql service port (3306 by default)
4. Ensure that the two mysql Databases can access each other (that is, the second step is required)
5. reset master and slave. Reset master; reset slave; Enable and disable slave, start slave; stop slave;
Mysql read/write splitting

After completing the above work, you can start to set up read/write splitting. There are currently several main methods of read/write Splitting:
1. MySQL Proxy (middleware)
2. Amoeba for MySQL (middleware)
3. Mycat (middleware)
4. Application Layer implementation
The following describes how to use MySQL Proxy to implement read/write splitting. Mysql Proxy is generally installed on a separate server for read/write scheduling. Add an IP address to install the mysql-proxy scheduler. The IP address is 172.16.34.236.
1. First install mysql-proxy
Apt-get install mysql-proxy

2. The lua script is used to implement read/write splitting. Now mysql-proxy has been integrated and no installation is required.
3. Configure the number of connections for read/write splitting. If the number of connections is set to 1, the read/write splitting starts.
Vim/usr/share/mysql-proxy/rw-splitting.lua

4. Start mysql-proxy. The master database is used to write 172.16.34.212 and the slave database is used to read 172.16.34.156.
Sudo mysql-proxy-read-only-backend-addresses = 172.16.34.156: 3306-proxy-backend-addresses = 172.16.34.212: 3306-proxy-lua-script =/usr/share/mysql-proxy/rw-splitting.lua-admin-username = euht
-Admin-password = 123456
-Admin-lua-script =/usr/share/mysql-proxy/admin. lua
5. Ports 4040 and 4041 are used by default after startup. 4040 for SQL forwarding and 4041 for mysql-proxy management. (Netstat-tupln | grep mysql-proxy)

6. Test read/write splitting
Use the user euht of the master database 172.16.34.212 to log on to this mysql-proxy Server (you can also create a user for this proxy separately)
Now, log on from this proxy to the master mysql server and insert a piece of data to testtb. As a result, the master server has data.

To test whether the read/write splitting is true, we differentiate the data of these two servers.

Log on to the mysql-proxy first (-P specifies the port number, which must be specified; otherwise, the login will be the local port 3306. Of course, because this 219 server is not configured to allow access by external hosts, therefore, if the following statement is executed and the port number is missing, an error is reported .)
Mysql-ueuht-p-h172.16.34.219-P4040
Log on to the server. to see whether the read/write splitting takes effect, stop slave on the slave server 156 first. (Stop slave;) Then insert data in mysql-proxy of proxy server 219. First look at the original data

Insert data and view Data

If the data is successfully inserted, the data just inserted is not displayed in the select statement. In this case, we go to the two servers to view the data.
172 .. 34.156 data from the server is still original
 
Let's look at 212 of the data.

You can see the effect here. The master server has the data just inserted, but not the slave server. This is because the master-slave replication of the slave server has been disabled, so data is inserted from the mysql-proxy (in fact, the master server 212 is used for data insertion) and not copied to the slave server. The newly inserted data is not read from mysql-proxy because the proxy reads data from server 156.
So far, mysql's master-slave replication and read/write splitting have ended. The following describes the dual-master structure.

Mysql dual-master Structure

It is easy to see from the above introduction that the dual-master structure is actually the data replication between two servers. To achieve the dual-master architecture, you only need to change the 212 master server to the 156 slave server.

Run the command on the 212 server (first go to 156 to view the log location, and change the corresponding items to show master status ;)
Change master
Master_host = '1970. 16.34.212 ',
Master_user = 'euht ',
Master_password = '000000 ',
Master_log_file = 'master-bin.000001 ',
Master_log_pos = 294;

Enable Master/Slave
Mysql> start slave;

156,212 all start slave and the configurations are slave. This is the dual-master structure.

Test: 212 of data inserted is synchronized, 156 of data inserted is synchronized, and 156 of data inserted is synchronized. All tests passed. (Remember to check the running status. The values of Slave_IO_Running and Slave_ SQL _Running must both be YES to indicate that the running status is normal. Show slave status \ G)