Complete MySQL master and slave configuration method detailed

Source: Internet
Author: User
Tags log log unique id vps aliyun

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.