Complete MySQL master-slave configuration method

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

[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

[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

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

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

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

START SLAVE

8, view the status from the server

The code is as follows

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

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

The code is as follows

SLAVE STOP

After the modification is complete, remember to open

SLAVE START