1. Two solutions to slave SQL running: no

Enter the slave server and run:

mysql> show slave status\G

        .......
            Relay_Log_File: localhost-relay-bin.000535
             Relay_Log_Pos: 21795072
     Relay_Master_Log_File: localhost-bin.000094
          Slave_IO_Running: Yes
         Slave_SQL_Running: No
           Replicate_Do_DB: 
       Replicate_Ignore_DB: 
     ......

Solution 1.

Slave_SQL_Running: No 1. The program may be written on the slave.

2. It may also be caused by transaction rollback after the slave machine is restarted.

Generally, it is caused by transaction rollback: terms of settlement:

mysql> stop slave ;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave ;

Solution 2. (recommended)

First stop the Slave service: slave stop To view the host status on the primary server: Record the value corresponding to File and Position

Enter master

mysql> show master status;
+----------------------+----------+--------------+------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------+----------+--------------+------------------+
| localhost-bin.000094 | 33622483 |              |                  | 
+----------------------+----------+--------------+------------------+
row in set (0.00 sec)

Then perform manual synchronization on the slave server:

mysql> change master to 
 master_host='master_ip',
 master_user='user', 
 master_password='pwd', 
 master_port=3306, 
 master_log_file=localhost-bin.000094', 
 master_log_pos=33622483 ;
 
mysql> start slave ;

start slave

mysql> show slave status\G
*************************** 1. row ***************************
........
            Master_Log_File: localhost-bin.000094
        Read_Master_Log_Pos: 33768775
             Relay_Log_File: localhost-relay-bin.000537
              Relay_Log_Pos: 1094034
      Relay_Master_Log_File: localhost-bin.000094
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:

2. Slave failed to initialize relay log info structure from the repository

When MySQL master-slave replication starts slave, the following error is reported: mysql> start slave; ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository Solution: check the log. You can see that the error is reported. The. / server246 relay bin.index file cannot be found. The reason is that I used the instance of cold backup file recovery, and the information of the previous relay log is still retained in the slave log info table of mysql database, so I started the slave error report. mysql provides tools to delete records: slave reset; The slave reset execution waits for the following:
1. Delete the data in the slave master info and slave relay log info tables.
2. Delete all relay log files and re create new relay log files;
3. The value of gtid_executed or gtid_purged will not be changed.

mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)

mysql> change master to ......

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

So the slave can be started.

3. mysql5.7.18.1 ERROR 1054 (42S22): Unknown column 'password' in 'field list' solution

The reason for the error is that there is no password in mysql database under version 5.7, and the password field has been changed to authentication [u string

Update mysql. User set authentication ﹣ string = password ('') where user = ''; password modified successfully