"20180202" using iptables to do MySQL port forwarding

Guide: Move the MySQL request above one instance to another MySQL instance.

SOURCE Service: 172.16.3.6:3306 Main Library

Target service: 172.16.3.7:3306 from library

Access account: [email protected]

Old and new instances and building master-Slave

1. Turn on the port forwarding service above the source service:

shell>> sudo vim/etc/sysctl.confvim>> net.ipv4.ip_forward=1 # #在文件中修改这个选项为1shell >> sudo sysctl-  pshell>> sudo/etc/ini.d/iptables startshell>> sudo iptables-t nat-a prerouting-d 172.16.3.6-p TCP--dport 3306-j DNAT--to-destination 172.16.3.7:3306shell>> sudo iptables-t nat-a postrouting-j MASQUERADE or sudo i Ptables-t nat-a postrouting-d 172.16.3.7-p tcp--dport 3306-j snat-to-source 172.16.3.6shell>> sudo service IP Tables saveshell>> sudo service iptables restart

2. Open the 3306 service and create an access account on the target service:

shell>> sudo iptables-a input-s 172.16.3.0/24-m State--state new-m tcp-p TCP--dport 3306-j ACCEPTshell>& Gt sudo service iptables saveshell>> sudo service iptables restart

Mysql>> Grant Select,update,insert,delete on * * to ' test_01 ' @ ' 172.16.3.6 ' identified by ' New_password ';mysql> > Flush privileges;mysql>> \q

Remember: After the port forwarding setting is complete, use the account on 172.16.3.15 [email protected] To connect 172.16.3.6, this time 172.16.3.6 Iptables will forward the request to 172.16.3.7 above the MySQL service above, this time the connection account from [email protected] into the [email Protected], so I will be in the second step in the 172.16.3.7 above the creation of the account [email protected], and the password must be the account [email protected] This account password consistent. In fact, in the target service 172.16.3.7:3306 can not create the [email protected] This account, and in the source service 172.16.3.6:3306 this service after the port forwarding can be done to turn off this instance.

3. Summary

Throughout the business migration process, there are the following scenarios:

A. There are multiple businesses on the old MySQL instance.

B. There is a long connection above the old MySQL instance.

C. Different business there is a modification operation for the same table.

D. The connection usage of all services is exact match, that is, [email protected] This is the case.

E.binlog_format is the statement format, but there is a write SQL that uses a function similar to now (), causing the master-slave data to be inconsistent with some table time field data.

Use iptables for forwarding purposes and defects (old and new instances and build Master and slave):

Objective: To avoid inconsistent data on the same table by avoiding different business changes. For example, both business A and business B can have modified permissions for the table table_01, and the primary key for TEST_01 is the self-increment primary key, which is not displayed when the business is inserted into the primary key key value. When business a switches past, business B has not switched, and this time the business first inserts data into table_01, then business B inserts data into table_01 after a period of time. This time there will be 1062 primary key conflicts, and this time is based on the old MySQL instance or the new MySQL instance as the main.

Defects: 1. However, if business B is a long connection, and if business B is still connected to the old MySQL instance without restarting Business B, then the case of the appeal may still occur; 2. Because all of the business accounts are exact matches, there are some business username on the old instance. , but the IP may not be the same, then their permissions and accounts are not the same. However, after using iptables forwarding, all account matching IP addresses are changed to 172.16.3.6, so some of the business they finally connect to the 172.16.3.7:3306 instance of the account is the same, but another problem arises, Is that all business account password must have a different situation, this time they initiate the request of the password is not the same, but they finally connected to the 172.16.3.7:3306 account is a touch, an account can only correspond to one account password information, Then there will be all the business only the password is the same to the normal request access, the password inconsistent request will be rejected.

Solution: Restart the new MySQL instance and add--skip-grant-tables when restarting. Restart the new MySQL instance after all the business switches.

"20180202" using iptables to do MySQL port forwarding