The steps of realizing master-slave replication in mysql
This article mainly introduces the steps of mysql to achieve master-slave replication, the contents of the article are carefully selected and edited by the author, with a certain pertinence, the reference significance for everyone is still relatively great, the following with the author to understand the steps of mysql to achieve master-slave replication.
Mysql master-slave principle:
1) at least 2 database CVMs, one master and one slave, are required. Master enables bin-log feature. (bin-log function users record statements that master add, delete, modify, and update SQL.)
2) the process of asynchronous replication, with latency, millisecond level (latency is related to your network and performance, the order of magnitude of the database), open 3 threads. Master opens io thread, slave opens io thread and SQL thread.
3) after launching salve start from the library and connecting to the master,master through the io thread, username and password to receive the request, the master io thread is responsible for sending the bin-log content position location point data to the bin-log side.
4) after receiving the data, the slave io thread will append the content to the local relay-log relay log and generate the master.info file (from which machine to synchronize this time, user name, password, bin-log file name, position location).
5) the slave SQL thread monitors the relay-log in real time. If the log content is updated, parse the SQL statement in the file and execute it locally.
Actual combat:
1) enable bin-log and set server-id in master configuration file
2) authorize synchronization users and passwords
3) slave executes change master binding to the main library
192.168.1.155 (master)
192.168.1.156 (salve)
Note: turn off the firewall and setenforce 0 of the CVM
Master library configuration:
1. Add to the mysql configuration file
Log-bin=mysql-bin (enable bin-log)
Server-id=1 (distinguishing between master and slave)
two。 Restart the database
/ etc/init.d/mysql restart
3. Enter the database, create a new user, and authorize synchronization
Create user 'tongbu'@'192.168.1.156' identified by' 123456customers; (create user)
Grant replication slave on *. * to 'tongbu'@'192.168.1.156' identified by' 123456license; (authorized to slave library)
4. View the status of the main library
Mysql > show master status
+-+
| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |
+-+
| | mysql-bin.000001 | 990 |
+-+
1 row in set (0.01 sec)
From the library configuration:
5. Modify the slave configuration file
Server-id = 2
6. Restart the database
/ etc/init.d/mysql restart
7. Specify master IP and synchronized pos points on the slave CVM
Enter the database:
Change master to master_host='192.168.1.155', master_user='tongbu', master_password='123456', master_log_file='mysql-bin.000001', master_log_pos=990
Note: if you report an error
ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL''first
Indicates that slave is running, stop slave; (turn off slave)
7. Start slave
Start slave
8. Test, create a database in the main library to see if the slave library is synchronized.
(this experiment is successful)
After reading the above steps to achieve master-slave replication in mysql, many readers must have some understanding. If you need more industry knowledge and information, you can continue to follow our industry information column.
