Mysql主从同步问题汇总

最新推荐文章于 2024-02-22 17:35:45 发布
最新推荐文章于 2024-02-22 17:35:45 发布
阅读量224 收藏
点赞数
分类专栏： 数据库

Last_IO_Errno: 1236

slave查看show slave status\G;

显示Last_IO_Errno: 1236

模拟方法：master上执行reset master

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
[root@data-1-1 ~] # tail -3 /data/3306/mysql-bin.index
/data/3306/mysql-bin .000020
/data/3306/mysql-bin .000021
/data/3306/mysql-bin .000022
[root@data-1-1 ~] # mysql -uroot -poldboy123  -S  /data/3306/mysql.sock  -e   "reset master;"
[root@data-1-1 ~] # tail -3 /data/3306/mysql-bin.index
/data/3306/mysql-bin .000001
[root@data-1-1 ~] # ll /data/3306/
total 36
drwxr-xr-x 10 mysql mysql  4096 Jan 16 22:03 data
-rw-r--r--  1 mysql mysql  1899 Oct 29  2013 my.cnf
-rwx------  1 mysql mysql  1310 Jan 10 19:14 mysql
-rw-rw----  1 mysql mysql   107 Jan 17 16:55 mysql-bin.000001
-rw-rw----  1 mysql mysql    28 Jan 17 16:55 mysql-bin.index
-rw-rw----  1 mysql mysql     6 Jan 11 00:04 mysqld.pid
-rw-r-----  1 mysql root  10264 Jan 14 00:10 mysql_oldboy3306.err
srwxrwxrwx  1 mysql mysql     0 Jan 11 00:04 mysql.sock
[root@data-1-1 ~] # mysql -uroot -poldboy123  -S  /data/3306/mysql.sock  -e   "create database t99;"

slave上检查同步状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
mysql> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State:
                   Master_Host: 10.0.1.81
                   Master_User: rep
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000022
           Read_Master_Log_Pos: 107
                Relay_Log_File: data-1-2-relay-bin.000010
                 Relay_Log_Pos: 253
         Relay_Master_Log_File: mysql-bin.000022
              Slave_IO_Running: No
             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: 107
               Relay_Log_Space: 455
               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: 1236
                 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log:  'could not find next log; the first event ' mysql-bin.000019 ' at 556,<br>the last event read from ' /data/3306/mysql-bin .000022 ' at 107, the last byte read from ' /data/3306/mysql-bin .000022 ' at 107.'
                Last_SQL_Errno: 0
                Last_SQL_Error:
   Replicate_Ignore_Server_Ids:
              Master_Server_Id: 1
1 row  in  set  (0.00 sec)
 
ERROR:
No query specified

问题原因：

根据主从同步的原理，slave向master请求binlog时，master不仅把log发送过来，还会发送日志文件是哪个，以及下一次读取的位置信息。

master上执行了reset master命令后，导致日志文件从1开始了，这样从库根据之前获取的位置信息就不存在了，自然报错

解决方法：

master上找出当前日志文件最接近上一次slave同步的位置。下面可以找到是at 4，也可以使用at 107

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
[root@data-1-1 ~] # mysqlbinlog /data/3306/mysql-bin.000001 >bin-1.log
[root@data-1-1 ~] # less bin-1.log
[root@data-1-1 ~] # cat bin-1.log
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170117 16:55:09 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.32-log created 170117 16:55:09 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
7dt9WA8BAAAAZwAAAGsAAAABAAQANS41LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADt231YEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#170117 16:56:18 server id 1  end_log_pos 188   Query   thread_id=150   exec_time=0 error_code=0
SET TIMESTAMP=1484643378/*!*/;
SET @@session.pseudo_thread_id=150/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 * // *!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database t99
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@data-1-1 ~] #

slave执行如下过程

stop slave;

change master to master_log_file='mysql-bin.000001',master_log_pos=107;

start slave;

show slave status\G;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
 
mysql> change master to master_log_file= 'mysql-bin.000001' ,master_log_pos=107;
Query OK, 0 rows affected (0.03 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting  for  master to send event
                   Master_Host: 10.0.1.81
                   Master_User: rep
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000001
           Read_Master_Log_Pos: 188
                Relay_Log_File: data-1-2-relay-bin.000002
                 Relay_Log_Pos: 334
         Relay_Master_Log_File: mysql-bin.000001
              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: 188
               Relay_Log_Space: 493
               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: 0
Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error:
                Last_SQL_Errno: 0
                Last_SQL_Error:
   Replicate_Ignore_Server_Ids:
              Master_Server_Id: 1
1 row  in  set  (0.00 sec)
 
ERROR:
No query specified
 
mysql>

额外补充点：

这个reset master 会影响和它直接连接的slave，而不是影响slave级联的slave。

比如下面主从架构，master上执行reset  master，slave1会被影响到，报错，slave2不出报错，但是slave2和slave1数据库里数据都是一样的。

master--->slave1---->slave2

因为slave1作为级联的中继端，会产生binlog，而slave2获取的是slave1的binlog

 

 

Last_SQL_Errno: 1007

模拟方法：

slave2从库执行reset slave

（master--->slave1---->slave2）此处我是在slave2上执行的reset slave操作，slave2在data-1-1机器上，slave1在data-1-2机器上

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
mysql> reset slave;
ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
 
mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)
 
mysql> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State:
                   Master_Host: 10.0.1.82
                   Master_User: rep
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File:
           Read_Master_Log_Pos: 4
                Relay_Log_File: relay-bin.000001
                 Relay_Log_Pos: 4
         Relay_Master_Log_File:
              Slave_IO_Running: No
             Slave_SQL_Running: No
               Replicate_Do_DB:
           Replicate_Ignore_DB: mysql
            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: 0
               Relay_Log_Space: 126
               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:
   Replicate_Ignore_Server_Ids:
              Master_Server_Id: 12
1 row  in  set  (0.00 sec)
 
ERROR:
No query specified

 

继续同步，出现1007报错

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
 
mysql> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting  for  master to send event
                   Master_Host: 10.0.1.82
                   Master_User: rep
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000004
           Read_Master_Log_Pos: 348
                Relay_Log_File: relay-bin.000006
                 Relay_Log_Pos: 253
         Relay_Master_Log_File: mysql-bin.000004
              Slave_IO_Running: Yes
             Slave_SQL_Running: No
               Replicate_Do_DB:
           Replicate_Ignore_DB: mysql
            Replicate_Do_Table:
        Replicate_Ignore_Table:
       Replicate_Wild_Do_Table:
   Replicate_Wild_Ignore_Table:
                    Last_Errno: 1007
                    Last_Error: Error  'Can' t create database  'tt' ; database exists ' on query. Default database: ' tt '. Query: ' create database tt'
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 107
               Relay_Log_Space: 541385
               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: 1007
                Last_SQL_Error: Error  'Can' t create database  'tt' ; database exists ' on query. Default database: ' tt '. Query: ' create database tt'
   Replicate_Ignore_Server_Ids:
              Master_Server_Id: 12
1 row  in  set  (0.00 sec)
 
ERROR:
No query specified

解决方法：找到master点的最新位置信息at 348

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
[root@data-1-2 ~] # mysqlbinlog /application/mysql/data/mysql-bin.000004 >bin4.log
[root@data-1-2 ~] # tail -10 bin4.log
# at 267
#170117 16:56:18 server id 1  end_log_pos 348   Query   thread_id=150   exec_time=618   error_code=0
SET TIMESTAMP=1484643378/*!*/;
create database t99
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@data-1-2 ~] # mysqlbinlog /application/mysql/data/mysql-bin.000004 >bin4.log
[root@data-1-2 ~] # tail -10 bin4.log
# at 348
#170117 17:43:17 server id 1  end_log_pos 429   Query   thread_id=152   exec_time=3 error_code=0
SET TIMESTAMP=1484646197/*!*/;
create database t10
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@data-1-2 ~] #

slave2上执行如下，主从同步恢复正常

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
mysql>  change master to master_log_file= 'mysql-bin.000004' ,master_log_pos=348;
Query OK, 0 rows affected (0.01 sec)
 
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting  for  master to send event
                   Master_Host: 10.0.1.82
                   Master_User: rep
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000004
           Read_Master_Log_Pos: 429
                Relay_Log_File: relay-bin.000002
                 Relay_Log_Pos: 334
         Relay_Master_Log_File: mysql-bin.000004
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               Replicate_Do_DB:
           Replicate_Ignore_DB: mysql
            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: 429
               Relay_Log_Space: 484
               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: 0
Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error:
                Last_SQL_Errno: 0
                Last_SQL_Error:
   Replicate_Ignore_Server_Ids:
              Master_Server_Id: 12
1 row  in  set  (0.00 sec)
 
ERROR:
No query specified
 
mysql>

　　

 

 Last_Errno: 1008

1008：数据库不存在，删除数据库失败

master-->slave1-->slave2

data-1-1上有master（端口是3306），data-1-2机器是slave1（端口3306），data-1-1上还有个slave2（端口是3307）

模拟1008错误，在slave2上把一部分库删除。由于slave1还master和slave2，这里拿slave2做实验

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
[root@data-1-1 ~] # mysql -uroot -poldboy123  -S  /data/3307/mysql.sock  -e   "drop database d19;"
[root@data-1-1 ~] # mysql -uroot -poldboy123  -S  /data/3307/mysql.sock  -e   "drop database d29;"
[root@data-1-1 ~] # mysql -uroot -poldboy123  -S  /data/3307/mysql.sock  -e   "drop database dd10;"
[root@data-1-1 ~] # mysql -uroot -poldboy123  -S  /data/3307/mysql.sock  -e   "drop database t16;"
[root@data-1-1 ~] # mysql -uroot -poldboy123  -S  /data/3307/mysql.sock  -e   "drop database t10;"
[root@data-1-1 ~] # mysql -uroot -poldboy123  -S  /data/3307/mysql.sock  -e   "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| t1                 |
|  test                |
+--------------------+
[root@data-1-1 ~] # mysql -uroot -poldboy123  -S  /data/3306/mysql.sock  -e   "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| d19                |
| d29                |
| dd10               |
| mysql              |
| performance_schema |
| t1                 |
| t10                |
| t16                |
|  test                |
| uu                 |
| zyx                |
+--------------------+
[root@data-1-1 ~] #  mysql -uroot -poldboy123  -S  /data/3306/mysql.sock  -e   "drop database d19;"
[root@data-1-1 ~] #  mysql -uroot -poldboy123  -S  /data/3306/mysql.sock  -e   "drop database d29;"
[root@data-1-1 ~] #  mysql -uroot -poldboy123  -S  /data/3306/mysql.sock  -e   "drop database dd10;"
[root@data-1-1 ~] #  mysql -uroot -poldboy123  -S  /data/3306/mysql.sock  -e   "drop database t16;"
[root@data-1-1 ~] #  mysql -uroot -poldboy123  -S  /data/3306/mysql.sock  -e   "drop database t10;"
[root@data-1-1 ~] #  mysql -uroot -poldboy123  -S  /data/3306/mysql.sock  -e   "show databases;"

slave2出现错误1008错误

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
[root@data-1-1 ~] #  mysql -uroot -poldboy123  -S  /data/3307/mysql.sock  -e   "show  slave status\G;"
*************************** 1. row ***************************
                Slave_IO_State: Waiting  for  master to send event
                   Master_Host: 10.0.1.82
                   Master_User: rep
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000004
           Read_Master_Log_Pos: 2367
                Relay_Log_File: relay-bin.000005
                 Relay_Log_Pos: 1055
         Relay_Master_Log_File: mysql-bin.000004
              Slave_IO_Running: Yes
             Slave_SQL_Running: No
               Replicate_Do_DB:
           Replicate_Ignore_DB: mysql
            Replicate_Do_Table:
        Replicate_Ignore_Table:
       Replicate_Wild_Do_Table:
   Replicate_Wild_Ignore_Table:
                    Last_Errno: 1008
                    Last_Error: Error  'Can' t drop database  'd19' ; database doesn 't exist'  on query. Default database:  'd19' . Query:  'drop database d19'
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 1970
               Relay_Log_Space: 1602
               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: 1008
                Last_SQL_Error: Error  'Can' t drop database  'd19' ; database doesn 't exist'  on query. Default database:  'd19' . Query:  'drop database d19'
   Replicate_Ignore_Server_Ids:
              Master_Server_Id: 12
[root@data-1-1 ~] #

解决过程：

其实也就3步：1：停止slave。  2：跳过错误。3：开启slave

stop slave
set global sql_slave_skip_counter=5
start slave

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
[root@data-1-1 ~] #  mysql -uroot -poldboy123  -S  /data/3307/mysql.sock  -e   "stop slave;"
[root@data-1-1 ~] #  mysql -uroot -poldboy123  -S  /data/3307/mysql.sock  -e   "set global sql_slave_skip_counter=5;"
[root@data-1-1 ~] #  mysql -uroot -poldboy123  -S  /data/3307/mysql.sock  -e   "start slave;"
[root@data-1-1 ~] #  mysql -uroot -poldboy123  -S  /data/3307/mysql.sock  -e   "show  slave status\G;"  | grep Last_SQL_Errno
                Last_SQL_Errno: 0
[root@data-1-1 ~] #  mysql -uroot -poldboy123  -S  /data/3307/mysql.sock  -e   "show  slave status\G;"
*************************** 1. row ***************************
                Slave_IO_State: Waiting  for  master to send event
                   Master_Host: 10.0.1.82
                   Master_User: rep
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000004
           Read_Master_Log_Pos: 2367
                Relay_Log_File: relay-bin.000006
                 Relay_Log_Pos: 253
         Relay_Master_Log_File: mysql-bin.000004
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               Replicate_Do_DB:
           Replicate_Ignore_DB: mysql
            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: 2367
               Relay_Log_Space: 1748
               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: 0
Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error:
                Last_SQL_Errno: 0
                Last_SQL_Error:
   Replicate_Ignore_Server_Ids:
              Master_Server_Id: 12
[root@data-1-1 ~] #
symonstar
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql主备同步问题总结
weixin_44692574的博客
07-04 2619
mysql主备同步问题总结 一、能用命令修复的几种报错： 基础知识：mysql主备同步模式有三种：半同步、异步和双通道. 其中,半同步和异步同步模式在备库上执行show slave status\G的结果只有row1, 若结果里除了row1还有row2,同步模式则为双通道。 1.1 last_errno:1305错误 last_error:error ‘savepoint trans5 does...
Mysql主从同步
YogurtP的博客
11-05 155
1.主机配置 找到主机Mysql的配置文件my.cnf(windows为my.ini)，一般在/etc/my.cnf路径下。 这里以centos7为例（vim为编辑打开文件命令）： 点击INSET进入编辑模式，修改配置文件; server-id=1（唯一id）； log-bin=mysql-bin(开启mysql日志文件)； binlog-do-db=test (指定需要日志的数据库)； binlog_format=MIXED(指定记录日志模式为MIXED)； 重启主机MySQL服务； 在主机MySQL
could not find next log； the first event ‘‘ at 4, the last event read from
小白
11-09 682
MySQL双主同步异常背景29库执行 show slave status\G30库执行show slave status\G解决方法 背景 29和30两个MySQL实例互为主备，但是29作为从库同步来自于30的数据出现异常，具体如下： 29库执行 show slave status\G mysql> show slave status\G; *************************** 1. row *************************** Sla
MySQL验证主从同步报‘Could not find first log file name in binary log index file‘错误的解决办法
进阶的蜗牛
12-16 4546
‘Could not find first log file name in binary log index file’ 解决办法 1、背景 在做mysql主从的时候，查看从库状态(show slave status\G)，一直提示如下报错 ‘ Could not find first log file name in binary log index file’ 2、错误截图 3、解决办法 1）在主库执行： mysql> flush logs; Query OK, 0 rows affected
[MySQL]-主从同步实战-主从搭建
liangsena的博客
12-14 1020
本文主要介绍了，当主从同步发生故障时，要去如何发现问题所在，如何去重新进行主从的搭建。
Canal报错：Could not find first log file name in binary log index file
IntialJ的博客
11-21 1261
2019-11-21 10:22:31.206 [destination = example , address = /127.0.0.1:3306 , EventParser] ERROR com.alibaba.otter.canal.common.alarm.LogAlarmHandler - destination:example[java.io.IOException: Received...
MySQL面试题汇总.zip
09-26
这份"MySQL面试题汇总.zip"压缩包显然包含了关于MySQL的面试问题和可能的答案，旨在帮助求职者准备数据库相关的面试。以下是一些核心的MySQL知识点： 1. **基本概念**： - 数据库（Database）：存储数据的系统。 ...
MySQL后端面试题汇总.zip
08-06
- **主从复制**：理解复制的工作原理，包括异步和半同步复制。 - **组复制**：了解MySQL Group Replication，提高高可用性和容错性。 7. **分区与分表** - **分区策略**：ROW, RANGE, LIST, HASH分区的区别和...
MySQL经典面试题汇总
05-16
MySQL是世界上最受欢迎的关系型数据库管理系统之一，广泛...以上只是一部分MySQL的经典面试题，实际面试中还可能涉及分区、复制、主从同步、性能监控等更深入的话题。熟练掌握这些知识点，将有助于在面试中表现出色。
mysql常见面试题汇总
02-04
- **复制技术**：MySQL支持主从复制，可以在多个服务器之间同步数据，提高可用性和容错性。 7. **高级话题** - **触发器**：在特定事件发生时自动执行的存储过程，常用于业务规则的实施。 - **视图**：虚拟表，...
有用的Mysql面试题汇总.rar
09-08
- 主从复制：理解复制的工作流程，以及半同步复制和全同步复制的区别。 - 高可用解决方案：如MMM（MySQL Master-Master Replication Manager）和Galera Cluster。 7. **分区与分片**： - 表分区：提高大数据量表...
mysql面试题汇总一共100+道经典mysql面试题
04-23
复制与主从同步** - MySQL复制的原理是什么？如何配置主从复制？ - 如何处理主从延迟问题？ **12. 性能优化** - 介绍`EXPLAIN EXTENDED`和`SHOW WARNINGS`在优化查询时的作用。 - 如何使用`OPTIMIZE TABLE`命令...
Mysql学习笔记汇总.zip
02-22
这份"Mysql学习笔记汇总"压缩包文件，很可能包含了一系列有关MySQL的基础知识、进阶技巧、最佳实践以及常见问题的解答。下面，我们将对MySQL的一些关键知识点进行详细讲解。 1. **MySQL基础** - **数据类型**：...
MySQL最新面试题及答案整理汇总版，MySQL常见面试题及答案汇总
02-20
- MySQL的复制技术实现主从同步，提高可用性和数据冗余。配置包括基于语句的复制、基于行的复制和混合模式复制。 14. **性能监控** - `SHOW STATUS`查看服务器状态，`SHOW VARIABLES`查看系统变量，`SHOW ...
设置主从复制时发生报错Could not find first log file name in binary log index file‘；解决方案
最新发布
杜小帅的博客
02-22 768
如图所示，slave_io_runnind:no,slave_sql_running:yes。此时，主从配置错误，我们可以查看Last_IO_Error:来查看报错信息。此时，我们需要停止从服务器的主从服务，必要时使用flush log;我们到从服务器进行修改。
mysql空间暴涨_mysql主库磁盘空间爆满导致从库错误
weixin_39631951的博客
02-08 156
2020-06-30 19:21:03 2901 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: ‘binlog truncated in the middle of event; consider out of disk space on master; the firs...
mysql fabric 性能_Mysql Fabric高可用集群分片功能测试
weixin_29529733的博客
02-26 96
分片后，group_id-1组插入的数据行数如下：mysql -P 3306 -h 192.168.1.70 -u root -e "select count(*) from test.subscribers"返回结果：+----------+| count(*) |+----------+| 794287 |+----------+分片后，group_id-2组插入的数据行数如下：mysql -...
【canal系】canal集群异常Could not find first log file name in binary log index file
run_boy_2022的博客
09-08 1105
canal-adapter：相当于canal的客户端，会从canal-server中获取数据，然后对数据进行同步，可以同步到MySQL、Elasticsearch和HBase等存储中去。canal-server（canal-deploy）：直接监听MySQL的binlog，把自己伪装成MySQL的从库，只负责接收数据，并不做处理。canal-admin：为canal提供整体配置管理、节点运维等面向运维的功能，提供相对友好的WebUI操作界面，方便更多用户快速和安全的操作。canal环境的几个组件。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值