Complete MySQL master and slave configuration method detailed

MySQL is divided into two roles

1, master server Master

2, from the server slave

MySQL master-slave replication is the database synchronization between two servers, can also be understood as a backup of the primary server, when the master server data has been changed, then automatically updated from the server, in fact, through the Bin-log log, also said that they are in the middle of the transmission Binlog log. This allows us to make multiple nodes of the data redundant to ensure availability!

The company now has three servers, a Ali cloud, two VPs, now need to the Ali Cloud database synchronization to the other two vps. Aliyun server other two servers do from the server

The operation is as follows:

1, in the Aliyun server to modify the My.cnf file (generally in/etc/my.cnf)

The code is as follows	Copy Code
[Mysqld] Log-bin=mysql-bin//[must] enable binary logging Server-id=1//[must] server unique ID, default is 1, generally take IP last paragraph Binlog-do-db=wordpress//[Optional] Set data that needs to be synchronized, if not set to synchronize all databases Binlog_ignore_db=mysql//[Optional] Ignore MySQL data, because MySQL database is some account of MySQL storage, this does not need to sync

2, came from the service to modify MY.CNF

The code is as follows	Copy Code
[Mysqld] Log-bin=mysql-bin//[must] enable binary logging server-id=2//[must] server unique ID, default is 1, generally take IP last paragraph

3, restart the two servers of MySQL (the second from the server and the first one, here is only the first configuration to write)

4, login to the main server for the authorization from the server

The code is as follows	Copy Code
GRANT REPLICATION SLAVE on *.* to ' sync ' @ '% ' identified by ' sync '; Using root is not recommended

"%" means that all clients may even, as long as the account number, the password is correct, here can be replaced from the server IP, such as 192.168.145.226, enhance security.

5, view the status of the primary server

The code is as follows	Copy Code
Show master status; Mysql> Show master status; +------------------+----------+-----------------------+------------------+ | File | Position | binlog_do_db | binlog_ignore_db | +------------------+----------+-----------------------+------------------+ |      mysql-bin.000004 | 106 | wordpress,wordpress | Mysql,mysql | +------------------+----------+--------------+---------------------------+

Do not take any action when the status is finished, to prevent the state from changing

6, login from the server configuration

The code is as follows	Copy Code
Mysql> Change MASTER to -> master_host = ' primary server address ', -> master_user = ' Newly created account ', -> master_password= ' New user password ', -> master_port=3306, -> master_log_file= ' mysql-bin.000003 ', -> master_log_pos=106;

7, turn on the copy function from the server

The code is as follows	Copy Code
START SLAVE

8, view the status from the server

The code is as follows

Copy Code

Mysql> Show Slave STATUSG 1. Row *************************** Slave_io_state: master_host:117.34.73.175 Master_user:mysqlsync master_port:3306 Connect_retry:60 master_log_file:mysql-bin.000002 read_master_log_pos:106 relay_log_file:ay140429124852508639z-relay-bin.000001 Relay_log_pos:4 relay_master_log_file:mysql-bin.000002 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:106 relay_log_space:106 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:null Master_ssl_verify_server_cert:no last_io_errno:0 Last_io_error: last_sql_errno:0 Last_sql_error: 1 row in Set (0.00 sec) Here's what you need to notice. slave_io_running = Yes slave_sql_running = Yes

These two values must be yes, and if one is no, it means that the master and the slave are problematic.
If the shell or Python can write a test script to detect whether the two values are yes, if there is a no that is the problem of the master and the wrong email to the police!

If one of them is yes, you need to check, I encountered slave_io_running in the process of operation for No, this time you look at the LAST_IO_ERROR here Hint: Error connecting to master Sync@218.244.**.**:3306′–retry-time:60 retries:86400 (ps:** for the code), here prompted and unable to connect to the server, in general may be the account password set up incorrectly, or the server's firewall to intercept, After checking Ali Cloud Cayenne Shield opened 3306, but no or not, this time in the direct login from the server, found that the problem is a space provider, Local is OK but from the server is no way to log in.

9, this time you can be in the main server to create tables insert data and so on, to see if the server has been synchronized

You may have knocked something wrong at the command, you can use the following SQL statement to modify

The code is as follows	Copy Code
Change MASTER to master_host= ' 218.244.136.92 '; From the server, meaning: Modify the address of the primary server, the following meaning is the same Change MASTER to master_log_file= ' mysql-bin.000003 ' master_log_pos=106;

You need to stop synchronizing from the server at the time of execution.

Stop sync

When the modification is complete

code is as follows	copy code
SLAVE STOP Remember to turn on SLAVE START