Shulou(Shulou.com)06/01 Report--

This article mainly tells you briefly about the pits often encountered in configuring mysql master-slave replication. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope that configuring the pits often encountered in mysql master-slave replication can bring you some practical help.

1. Master and slave CVM perform the following operations:

1.1, version consistent

Initialize the table and start mysql in the background

1.3.Moving the password of root

2. Modify the main CVM master:

# vi / etc/my.cnf

[mysqld]

Log-bin=mysql-bin / / [must] enable binary logging

Server-id=222 / / [required] the server has a unique ID. The default is 1. Generally, the last segment of IP is taken.

3. Modify the slave slave:

# vi / etc/my.cnf

[mysqld]

Log-bin=mysql-bin / / [not required] enable binary logging

Server-id=226 / / [required] the only ID of the CVM. The default is 1. The last segment of the IP is usually taken.

4. Restart the mysql of two CVM

/ etc/init.d/mysql restart

5. Establish an account on the primary CVM and authorize slave:

# / usr/local/mysql/bin/mysql-uroot-pmttang

Mysql > GRANT REPLICATION SLAVE ON *. * to 'mysync'@'%' identified by' q123456accounts; / / generally, no root account is used. "%" means that all clients may be connected. As long as the account number and password are correct, you can use specific client IP here, such as 192.168.145.226, to enhance security.

6. Log in to the mysql of the main CVM and query the status of master

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000004 | 308 |

+-+

1 row in set (0.00 sec)

Note: do not operate the primary CVM MYSQL after performing this step to prevent the status value of the primary CVM from changing

7. Configure Slave from CVM:

Mysql > change master to master_host='192.168.145.222',master_user='mysync',master_password='q123456'

Be careful not to disconnect master_log_file='mysql-bin.000004',master_log_pos=308; / /. There are no single quotation marks around the number 308.

Mysql > start slave; / / enable replication from CVM

8. Check the status of replication from the CVM:

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.2.222 / / address of the primary CVM

Master_User: mysync / / authorized account name, avoid using root as much as possible

Master_Port: 3306 / / database port, which is not available in some versions

Connect_Retry: 60

Master_Log_File: mysql-bin.000004

Read_Master_Log_Pos: 600 / / # the location where binary logs are read synchronously, which is greater than or equal to Exec_Master_Log_Pos

Relay_Log_File: ddte-relay-bin.000003

Relay_Log_Pos: 251

Relay_Master_Log_File: mysql-bin.000004

Slave_IO_Running: Yes / / this status must be YES

Slave_SQL_Running: Yes / / this status must be YES

.

Note: the Slave_IO and Slave_SQL processes must be running normally, that is, the YES state, otherwise they are all in the wrong state (for example, one of the NO is wrong).

The above is reproduced, and the master-slave CVM configuration is completed.

Then we have a problem.

Relay_Log_Pos: 4

Relay_Master_Log_File: mysql-bin.000009

Slave_IO_Running: Connecting

Slave_SQL_Running: Yes

My ubuntu16.04 system, mysql5.5, has a Slave_IO_Running status of connection at this stage. It is obvious that the slave CVM is not connected to the database of the master CVM. There are three possibilities for this:

1. The network is not available.

2, the password is incorrect

3The pos are not right.

1. Directly exclude, the vm virtual machine network I use is configured, just in case you can ping it.

PING 192.168.1.50 (192.168.1.50) 56 (84) bytes of data.

64 bytes from 192.168.1.50: icmp_seq=1 ttl=64 time=0.177 ms

64 bytes from 192.168.1.50: icmp_seq=2 ttl=64 time=0.257 ms

64 bytes from 192.168.1.50: icmp_seq=3 ttl=64 time=0.202 ms

2. Find the original change master command:

Change master to master_host='192.168.1.50',master_user='*',master_password='123456',master_log_file='mysql-bin.000009',master_log_pos=326

After a careful look at the relevant information, there are no errors. If you can't find the command here, you can check the mysql error log: vim / usr/local/mysql/var/*-server-4.err

Turn to the place where the bottom log begins.

171012 0:04:46 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port='3306', master_log_file='', master_log_pos='4'. New state master_host='192.168.1.50', master_port='3306', master_log_file='mysql-bin.000009', master_log_pos='326'.

171012 0:04:56 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000009' at position 326, relay log'. / *-server-4-relay-bin.000001' position: 4

171012 0:07:03 [ERROR] Slave Iamp O: error connecting to master'* @ 192.168.1.50 retry-time 3306'- retry-time: 60 retries: 86400, Error_code: 2003

More details are shown here, including change master configuration information and connection error information (the above * number is the mysql user name, please replace it yourself).

3. Find the result of the original master database show master status command (if you can't find it, you can't find it):

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000009 | 326 | |

Finally, try cmd to connect to the main CVM mysql.

C:\ Users\ Administrator > mysql-h 192.168.1.50-P 3306-u *-p

Enter password: *

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.50' (10060 "Unknown error")

As expected, you can basically determine what the problem is. Check the firewall iptables-L-n.

DROP tcp-0.0.0.0Universe 0 0.0.0.0Universe 0 tcp dpt:3306

External connection port 3306 is discarded directly. What a pervert!

Check the firewall rule number: iptables-L-n-- line-number

6 DROP tcp-- 0.0.0.0Universe 0 0.0.0.0Uniplex 0 tcp dpt:3306

Now let's delete this rule: iptables-D INPUT 6

Add another rule: iptables-An INPUT-p tcp-- dport 3306-j ACCEPT

View status:

ACCEPT tcp-0.0.0.0Universe 0 0.0.0.0Universe 0 tcp dpt:3306

After modification, restart mysql from CVM and view the database

Before:

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

+-+

After that:

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | test |

+-+

Synchronized!

Configure mysql master-slave replication often encountered holes to tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.