MySQL Simple master-slave setup and problem solving

mysql Simple master-slave setup and problem solving

This week, I need to understand how MySQL sets the master-slave relationship because of my job needs, so I started to try this new thing. In fact, there are a lot of articles on this topic on the Internet, I also in Baidu a lot of articles, finally successfully set up successfully. Below, we will share a short and easy-to-practice process.

I. software and Environment

1. software : MySql Server 5.6.21 (the master-slave version is as consistent as possible );

2. Environment :

1. host ip:192.168.10.34; host port:3306

2. slave machine ip:192.168.10.35 slave port:3306

Second, the configuration process

1. To Configure the host environment :

1. In the host environment , locate the My.ini file and add the following configuration under [mysqld]:

server-id=1# server ID, unique

bog-bin=mysql-bin# log Files

2. Restart the host and log in , set up users and Permissions

Mysql>grantreplication SLAVE on * * to ' ① ' @ ' ② ' identified by ' ③ ';

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/59/AF/wKioL1TcQmWyIN6tAAB-XwVp_Kg456.jpg "style=" float: none; "title=" 1.png "alt=" Wkiol1tcqmwyin6taab-xwvp_kg456.jpg "/>

Where: ① for the login user, ② for slave ip,③ for login password

3. View host information and record file and Position for configuring The slave environment

Mysql>showmaster STATUS;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/59/B3/wKiom1TcQW_iYXHwAAD5BhQz3-Y911.jpg "style=" float: none; "title=" 2.png "alt=" Wkiom1tcqw_iyxhwaad5bhqz3-y911.jpg "/>

B. Configure the slave environment :

1. in the slave environment , locate the My.ini file and Add the following configuration under [mysqld]:

server-id=2

Log-bin=mysql-bin

2. Restart the slave and log in , set the host parameters to the slave :

Mysql>change MASTER to master_host= ' 192.168.10.34 ', master_user= ' cp_test ', master_password= ' 111111 ', Master_log_ File= ' mysql-bin.000001 ', master_log_pos=333;

3. start the slave service

Mysql>start SLAVE;

4. View slave status

Mysql>show SLAVE Status\g;

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/59/AF/wKioL1TcQmXjcDRBAAFM6U3kusw159.jpg "style=" float: none; "title=" 3.png "alt=" Wkiol1tcqmxjcdrbaafm6u3kusw159.jpg "/>

when Both slave_io_running and slave_sql_running are "Yes", indicating master-slave connectivity.

Third, the problems encountered and solutions:

• Host service issues, such as:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/59/B3/wKiom1TcQW-Sp7mKAAGksfgmV44272.jpg "style=" float: none; "title=" 4.png "alt=" Wkiom1tcqw-sp7mkaagksfgmv44272.jpg "/>

when when slave_io_running:connection, the following conditions need to be checked:

• Whether the host service is stopped;

• when the host service requires a reboot, the slave needs to reset the host's file and Position, as follows:

• 3rd 1, step 3) .

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/59/AF/wKioL1TcQmaSneseAAFHYKPX3mQ951.jpg "style=" float: none; "title=" 5.png "alt=" Wkiol1tcqmasneseaafhykpx3mq951.jpg "/>

1. stop slave:mysql>stop slave;

2. 3rd 2, step 2) , Step 3), step 4) .

1, after the completion of the following:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/59/B3/wKiom1TcQW_x1QvtAAFNgpy6_88659.jpg "style=" float: none; "title=" 6.png "alt=" Wkiom1tcqw_x1qvtaafngpy6_88659.jpg "/>

2. Error when setting host parameters from the slave machine:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/59/AF/wKioL1TcQmaw3PqLAADWDg43oeg313.jpg "style=" float: none; "title=" 7.png "alt=" Wkiol1tcqmaw3pqlaadwdg43oeg313.jpg "/>

When this problem occurs, you need to stop slave before setting the parameters.

3,slave_io_running:no, and slave_sql_running:no :

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/59/AF/wKioL1TcQmbS60UdAAF3dxi87o4432.jpg "style=" float: none; "title=" 8.png "alt=" Wkiol1tcqmbs60udaaf3dxi87o4432.jpg "/>

in this case, it is very likely that the slave service is stopped.

Of course, this is only a simple master-slave setup, the problem is only a small part of the solution. After the setting succeeds, after the operation in the host, it can see the activity in the host machine, and the data backup is well realized.

This article is from the "Program Ape" blog, please be sure to keep this source http://cai0524.blog.51cto.com/7718508/1614027

MySQL Simple master-slave setup and problem solving