MySQL master-slave replication and data synchronization slave_io_running error

MySQL master copy

How to install MySQL database, here do not say, only to say that its master-slave copy, the steps are as follows:

1, the master-slave server for the following operations:
1.1, consistent version
1.2, initialization of the table, and in the background to start MySQL
1.3, modify the password of the root

2. Modify Master server Master:
#vi/etc/my.cnf
[Mysqld]
Log-bin=mysql-bin//[must] enable binary logging
server-id=108//[must] server unique ID, default is 1, generally take IP last paragraph

3, modify from the server slave:
#vi/etc/my.cnf
[Mysqld]
Log-bin=mysql-bin//[must] enable binary logging
Server-id=3//[must] server unique ID, default is 1, generally take IP last paragraph

4. Restart MySQL for two servers
/etc/init.d/mysql restart

5, establish the account on the primary server and authorize slave:
#/usr/local/mysql/bin/mysql-uroot-pmttang
Mysql>grant REPLICATION SLAVE on *.* to ' mysync ' @ '% ' identified by ' q123456 '; Generally do not use the root account, "%" means that all clients may even, as long as the account number, the correct password, here can be specific client IP instead, such as 192.168.1.3, enhance security.

6, login to the main server MySQL, query master's status
Mysql>show Master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
|      mysql-bin.000001 |              251 |                  | |
+------------------+----------+--------------+------------------+
1 row in Set (0.00 sec)
Note: Do not operate the master server MySQL after this step to prevent the master server state value from changing

7, configuration from the server slave:
Mysql>change Master to aster_host= ' 192.168.145.222 ', master_user= ' Mysync ', master_password= ' q123456 ',
Master_log_file= ' mysql-bin.000001 ', master_log_pos=251; Be careful not to disconnect, "251" without single quotes.

Mysql>start slave; Start replication from server

8. Check the copy function status from the server:

Mysql> Show Slave STATUSG

1. Row ***************************

Slave_io_state:waiting for Master to send event

master_host:192.168.2.222//Primary server address

Master_user:mysync//Authorized account name, try to avoid using root

master_port:3306//Database port, partial version does not have this row

Connect_retry:60

master_log_file:mysql-bin.000001

read_master_log_pos:600//#同步读取二进制日志的位置, 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.000001

Slave_io_running:yes//This state must be Yes

Slave_sql_running:yes//This state must be Yes
......

Note: The slave_io and slave_sql processes must run normally, that is, the Yes state, otherwise all are in the wrong state (for example, one of the No is an error (: Error solution)).

The above operation process, master-Slave server configuration complete.

9, master-Slave server testing:

master server MySQL, set up a database, and create a table in this library to insert a piece of data:

mysql> CREATE DATABASE hi_db;
Query OK, 1 row Affected (0.00 sec)

mysql> use hi_db;
Database changed

Mysql> CREATE TABLE HI_TB (ID int (3), name char (10));
Query OK, 0 rows Affected (0.00 sec)

mysql> INSERT INTO HI_TB values (001, ' WF ');
Query OK, 1 row Affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| hi_db |
| MySQL |
| Test |
+--------------------+
4 rows in Set (0.00 sec)

From the server MySQL query:

mysql> show databases;

+--------------------+
| Database |
+--------------------+
| Information_schema |
|          hi_db | I ' M here, you see?
| MySQL |
| Test |
+--------------------+
4 rows in Set (0.00 sec)

Mysql> Use hi_db
Database changed
Mysql> select * from HI_TB; You can see the specific data that was added to the primary server
+------+------+
| ID | name |
+------+------+
| 1 | WF |
+------+------+
1 row in Set (0.00 sec)
10. Complete:
Write a shell script, with Nagios monitor slave Two "yes", if found only one or 0 "yes", it indicates that the master and subordinate have a problem, send SMS alert.

a summary of the solution to the problem of Slave_io_running:no in MySQL data synchronization

MySQL replication in the slave machine has two key processes, dead one is not good, one is slave_sql_running, one is slave_io_running, one is responsible for the IO communication with the host, a responsible own slave MySQL process.
Write below, if the two have no, how to recover.

If it is slave_io_running no, then I personally see that there are three kinds of situations, one is the network has problems, not connected, like once I use a virtual machine to build replication, using the network structure of NAT, is not even dead, The second is that there may be my.cnf problems, the configuration file how to write not to say, the internet too much, the last one is the issue of authorization, replication slave and file permissions are required. If you're not afraid to die, all of you.

Once IO is no the first to see the Err log, to see what is wrong, it is likely the network, but also may be too big to receive the package, this time the main preparation on the change max_allowed_packet this parameter.

If it's slave_sql_running no, then there are two possibilities, one is the slave machine on this table there are other write operations, that is, the program is written, this is going to be a problem, today I want to reproduce, but sometimes there are problems, sometimes there is no problem, now is not too clear, Later update, there is a majority of possible is the slave process restart, transaction rollback caused, this is a self-protection of MySQL measures, like the key time read-only.

If you want to recover this time, just stop Slave,set global sql_slave_skip_counter=1, and then open the SLAVE, and this global variable assignment to n means:

This is statement skips the next N events from the master. This is useful to recovering from replication stops caused by a statement.

This statement is valid the slave thread is not running. Otherwise, it produces an error.

Oh, speak more than I know.

MySQL mirror server recovery from error stopped

PM main server, due to a number of reasons, resulting in a panic, restart, found data from the server did not keep up.
With good MySQL master and subordinate also just a few days ago, not much experience, the first encounter this problem, a little anxious. However, I have tried, but also solved:

From the server
master_log_file:mysqlhxmaster.000007
read_master_log_pos:84285377

Look at the main server: mysqlhxmaster.000007 | 84450528 |
It's been a lot, and it's really not following.

Show Slave STATUSG
Slave_io_running:yes
Slave_sql_running:no

If there is a problem, slave_sql_running should be yes.

Looking down again, there is the wrong hint:

last_errno:1053
Last_error:query partially completed on the master (Error on master:1053) and is aborted. There is a chance the your master is inconsistent in this point. If you are are sure that your master are OK, run this query manually on the slave and then restart the slave with SET GLOBAL SQ L_slave_skip_counter=1; START SLAVE;. Query: ' INSERT into Hx_stat_record ... (a SQL statement) '

Here is a description of how to operate:

Stop slave First, then execute the prompt statement, then set GLOBAL sql_slave_skip_counter=1; START SLAVE;

Show Slave STATUSG

Slave_io_running:yes
Slave_sql_running:yes

OK, from the server also within a few minutes of the accumulated log processing, both sides are synchronized:

Solution from MySQL server Slave_io_running:no 2

The Morning machine room unexpectedly power off, resulting in the discovery of MySQL from the server synchronization exception. The workaround for no with the previously encountered slave_sql_running is not valid and still cannot be synchronized.

Take a look at state show slave status
master_log_file:mysqlmaster.000079
read_master_log_pos:183913228
relay_log_file:hx-relay-bin.002934
relay_log_pos:183913371
relay_master_log_file:mysqlmaster.000079
Slave_io_running:no
Slave_sql_running:yes

Primary Server Show Master STATUSG
file:mysqlmaster.000080
position:13818288
binlog_do_db:
Binlog_ignore_db:mysql,test

MySQL error log:
100512 9:13:17 [note] Slave SQL thread initialized, starting replication into log ' mysqlmaster.000079 ' at position 183913228 , relay log './hx-relay-bin.002934 ' position:183913371
100512 9:13:17 [note] Slave I/O thread:connected to master ' replicuser@192.168.1.21:3306 ', replication started into log ' my sqlmaster.000079 ' at position 183913228
100512 9:13:17 [ERROR] Error reading packet from Server:client requested master to start replication from impossible tion (server_errno=1236)
100512 9:13:17 [ERROR] Got fatal ERROR 1236: ' Client requested Master to start replication from impossible ' Master when reading the data from binary log
100512 9:13:17 [note] Slave I/O thread exiting, read up to log ' mysqlmaster.000079 ', position 183913228

This is slave_io_running for no, from the log point of view, the server read mysqlmaster.000079 This log 183913228 error occurred, this location does not exist, so can not sync.

Check out the last few lines of this log:
/*!40019 SET @ @session. max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE =@ @COMPLETION_TYPE, completion_type=0*/;
# at 4
#100511 9:35:15 Server ID 1 end_log_pos start:binlog v 4, Server v 5.0.27-standard-log created 100511 9:35:15
# Warning:this Binlog is not closed properly. Most probably mysqld crashed writing it.

Try to start at the location before the damage
SLAVE STOP;
Change MASTER to master_log_file= ' mysqlcncnmaster.000079 ', master_log_pos=183913220;
SLAVE START;
Invalid!
I had to start with a new log.
SLAVE STOP;
Change MASTER to master_log_file= ' mysqlcncnmaster.000080 ', master_log_pos=0;
SLAVE START;
At this point slave_io_running restore to Yes, synchronized! Observed for a while, without any sign of error, problem solved.

In addition, there is also a reason for the presence of slave_io_running:no that there is no permission to read data on master on slave.