MySQL Master-slave configuration

MySQL Master-slave Introduction:

MySQL master-slave is also called replication, AB replication. Simply speaking is a and b two machines from the back, write the data on a, the other B will follow the writing data, both data real-time synchronization

MySQL master-slave is based on Binlog, the Lord must open Binlog to carry out master and slave.

The master-slave process has a roughly 3-step

1) The change operation is recorded in Binlog.

2) from synchronizing the main binlog event (SQL statement) to the machine and recording it in Relaylog

3) Execute sequentially from the SQL statements inside the Relaylog

The Lord has a log dump thread that is used to communicate with the I/O thread from Binlog

There are two threads from the top, where I/O threads are used to synchronize the main binlog and generate Relaylog, and another SQL thread is used to execute the SQL statements inside the Relaylog

Preparatory work:

To prepare two machines, both machines need to have MySQL installed and start the MySQL service. To install MySQL, please check the article in front of Bo master!

Configuration master:

① Modify the configuration file/etc/my.cnf, add server-id=129 and log_bin=centos7-1; After the modification is complete, restart the MySQL service.

② creates a test Zrlog library, backs up the test library, and needs to be imported from the top.

③ creating a user to use to synchronize data

④ locks the master from the configured database for the purpose of stopping read and write operations during configuration.

⑤ view the state of the master and configure the file and position data used here when synchronizing.

The Lord configuration is complete, then come and go from the top configuration.

Configuration from:

① Modify the configuration file/etc/my.cnf, add server-id=128 (not the same as the master); After the modification is complete, restart the MySQL service.

② The Lord's Test library Zrlog, the Lord uses rsync to sync to the top. Then import from the top

③ then go to MySQL to configure

④ See if the configuration is successful, see the following parameters.

After the configuration succeeds, do not forget to the Lord to unlock the operation: Unlock tables;

Test master-Slave synchronization:

① changes the data in the Lord's MySQL and then goes from the top to see if it is synchronized.

View from top:

Configuration failure Condition:

1. Slave_io_running:connecting

   Troubleshooting steps: ① network problems, using the ping command and the telnet command test, such as ping IP to see if you can ping, Telnet IP 3306 to see if 3306 port is open ② password right ③file and POS have no correspondence (in Lord Show master S Tatus;) ④ also pay attention to whether the firewall is off and iptables rules, and so on.

2. mysql-urepl-p123456-h192.168.139.129 remote connection failed, view MySQL log:

   Modify the configuration file/etc/my.cn and add skip-name-resolve under [mysqld]. Then restart the service!

MySQL Master-slave configuration