【江苏福彩快三】mysql主从库不同步问题解决方法

作者:江苏福彩快三计算机网络

mysql从库Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'报错管理,gotfatalerror1236

年后回到查看mysql运转景况与备份境况,登陆mysql从库查看主从同步状态

 1 mysql> show slave statusG;
 2 *************************** 1. row ***************************
 3                Slave_IO_State: 
 4                   Master_Host: 101.200.*.*
 5                   Master_User: backup
 6                   Master_Port: 3306
 7                 Connect_Retry: 60
 8               Master_Log_File: master-bin.000113
 9           Read_Master_Log_Pos: 276925387
10                Relay_Log_File: mysql-relay.000001
11                 Relay_Log_Pos: 4
12         Relay_Master_Log_File: master-bin.000113
13              Slave_IO_Running: No
14             Slave_SQL_Running: Yes
15               Replicate_Do_DB: 
16           Replicate_Ignore_DB: 
17            Replicate_Do_Table: 
18        Replicate_Ignore_Table: 
19       Replicate_Wild_Do_Table: 
20   Replicate_Wild_Ignore_Table: 
21                    Last_Errno: 0
22                    Last_Error: 
23                  Skip_Counter: 0
24           Exec_Master_Log_Pos: 276925387
25               Relay_Log_Space: 120
26               Until_Condition: None
27                Until_Log_File: 
28                 Until_Log_Pos: 0
29            Master_SSL_Allowed: No
30            Master_SSL_CA_File: 
31            Master_SSL_CA_Path: 
32               Master_SSL_Cert: 
33             Master_SSL_Cipher: 
34                Master_SSL_Key: 
35         Seconds_Behind_Master: NULL
36 Master_SSL_Verify_Server_Cert: No
37                 Last_IO_Errno: 1236
38                 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
39                Last_SQL_Errno: 0
40                Last_SQL_Error: 
41   Replicate_Ignore_Server_Ids: 
42              Master_Server_Id: 21
43                   Master_UUID: e4a43da7-5b58-11e5-a12f-00163e003632
44              Master_Info_File: /home/data/mysql/master.info
45                     SQL_Delay: 0
46           SQL_Remaining_Delay: NULL
47       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
48            Master_Retry_Count: 86400
49                   Master_Bind: 
50       Last_IO_Error_Timestamp: 170204 10:48:06
51      Last_SQL_Error_Timestamp: 
52                Master_SSL_Crl: 
53            Master_SSL_Crlpath: 
54            Retrieved_Gtid_Set: 
55             Executed_Gtid_Set: 
56                 Auto_Position: 0
57 1 row in set (0.00 sec)
58 
59 ERROR: 
60 No query specified

陡然之间Slave_IO_Running: 状态变为NO了

 

缓解情势。

率先在从库上实行

stop slave;

 

翻看主库master状态

 1 mysql> show master statusG;
 2 *************************** 1. row ***************************
 3              File: mysql-bin.000113
 4          Position: 276925387
 5      Binlog_Do_DB: 
 6  Binlog_Ignore_DB: 
 7 Executed_Gtid_Set: 
 8 1 row in set (0.00 sec)
 9 
10 ERROR: 
11 No query specified
12 
13 mysql> flush logs;
14 Query OK, 0 rows affected (0.11 sec)

刷新binlog日志

flush logs;

刷新后的日志会 1

诸如地方的 File: mysql-bin.000113 会产生 File: mysql-bin.000114

再一次查看master状态

 1 mysql> show master statusG;
 2 *************************** 1. row ***************************
 3              File: mysql-bin.000114
 4          Position: 120
 5      Binlog_Do_DB: 
 6  Binlog_Ignore_DB: 
 7 Executed_Gtid_Set: 
 8 1 row in set (0.00 sec)
 9 
10 ERROR: 
11 No query specified

然后就无需在操作master,切换成从库

输入CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000114',MASTER_LOG_POS=120;

执行start slave;

翻看从库状态

 1 mysql> show slave statusG;
 2 *************************** 1. row ***************************
 3                Slave_IO_State: Waiting for master to send event
 4                   Master_Host: 101.200.*.*
 5                   Master_User: backup
 6                   Master_Port: 3306
 7                 Connect_Retry: 60
 8               Master_Log_File: mysql-bin.000114
 9           Read_Master_Log_Pos: 11314
10                Relay_Log_File: mysql-relay.000002
11                 Relay_Log_Pos: 11477
12         Relay_Master_Log_File: mysql-bin.000114
13              Slave_IO_Running: Yes
14             Slave_SQL_Running: Yes
15               Replicate_Do_DB: 
16           Replicate_Ignore_DB: 
17            Replicate_Do_Table: 
18        Replicate_Ignore_Table: 
19       Replicate_Wild_Do_Table: 
20   Replicate_Wild_Ignore_Table: 
21                    Last_Errno: 0
22                    Last_Error: 
23                  Skip_Counter: 0
24           Exec_Master_Log_Pos: 11314
25               Relay_Log_Space: 11646
26               Until_Condition: None
27                Until_Log_File: 
28                 Until_Log_Pos: 0
29            Master_SSL_Allowed: No
30            Master_SSL_CA_File: 
31            Master_SSL_CA_Path: 
32               Master_SSL_Cert: 
33             Master_SSL_Cipher: 
34                Master_SSL_Key: 
35         Seconds_Behind_Master: 0
36 Master_SSL_Verify_Server_Cert: No
37                 Last_IO_Errno: 0
38                 Last_IO_Error: 
39                Last_SQL_Errno: 0
40                Last_SQL_Error: 
41   Replicate_Ignore_Server_Ids: 
42              Master_Server_Id: 21
43                   Master_UUID: e4a43da7-5b58-11e5-a12f-00163e003632
44              Master_Info_File: /home/data/mysql/master.info
45                     SQL_Delay: 0
46           SQL_Remaining_Delay: NULL
47       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
48            Master_Retry_Count: 86400
49                   Master_Bind: 
50       Last_IO_Error_Timestamp: 
51      Last_SQL_Error_Timestamp: 
52                Master_SSL_Crl: 
53            Master_SSL_Crlpath: 
54            Retrieved_Gtid_Set: 
55             Executed_Gtid_Set: 
56                 Auto_Position: 0
57 1 row in set (0.00 sec)
58 
59 ERROR: 
60 No query specified

 

: Got fatal error 1236 from master when reading data from binary log: Could not find first log file name in binary log index file报错管理,gotfatalerro...

遇上这么的荒诞如:“Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'”等或由于清数据形成主从库不一致步了,扼杀办法如下:

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

接下来在master中实行:"show master status;"查看下主库的图景,首如若日记的公文和position;

1、在Slave服务器查看同步状态 会出来比超多音讯 不过重大看那三个境况就可以了 如若都以 yes 就能够了

下一场回来slave中,实行:"CHANGE MASTEPRADO TO MASTE奥迪Q3_LOG_FILE='log-bin.000004',MASTER_LOG_POS=106;",文件和职位对应master中的;

  Replicate_Ignore_Table:设定能够忽视的Table

先踏入slave中推行:"slave stop;"来终止从库同步;

2、创建一齐用的数据库账户,主库必需提供八个账户让从库通过此账户实行连接并举行合作,步向mysql后输入上边发号布令

再去master中实行:"flush logs;"来清空日志;

mysql>

最后在slave中试行:"slave start;"来运行同步。

      Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Slave can not handle replication events with the checksum that master is configured to log; the first event 'mysql-bin.000011' at 120, the last event read from './mysql-bin.000011' at 120, the last byte read from './mysql-bin.000011' at 120.'

: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'”等或...

4、输入同步复制参数

 

 

master2的数据库换来master1相通版本的

 

1、校订配置文件,vim /etc/my.cnf,然后重启mysqld,/etc/init.d/mysqld restart

祛除办法2:

slave start
slave stop
slave stop IO_THREAD //此线程把master段的日志写到本地
slave start IO_THREAD
slave stop SQL_THREAD //此线程把写到本地的日记应用于数据库
slave start SQL_THREAD
reset slave 实行前一定要停掉slave(stop slave卡塔尔国 。推行reset slave后,会消亡复制相关的装有新闻,包罗:master.info, relay-log.info, 及无条件删除全体的连通日志(relay logs卡塔尔国. 注意是免费的,也正是不治本你Slave SQL线程是或不是把全体的relay log重播完了。瞩目,stop slave后,先保存show slave status新闻,然后再施行reset slave. 平时这一个新闻都会有效的。

三、测量检验与排错

5、备份数据库

在master1中设置binlog_checksum =none;

mysql>show slave statusG;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

  Replicate_Do_Table:设定供给复制的Table

  Replicate_Wild_Ignore_Table:功能同Replicate_Do_Table,功能同Replicate_Ignore_Table,能够带通配符。


  • [mysqld]<要在mysqld中括号下修改>
  • server-id = 6
  • auto_increment_increment = 2
  • auto_increment_offset = 1
  • log-bin=mysql-bin
  • binlog_format=mixed
  • binlog-do-db=db_dns
  • binlog-ignore-db=mysql
  • binlog-ignore-db=data_center

5、复苏数据库 

3、锁住主库表,甘休数据更新

  Replicate_Ignore_DB:设定能够忽视的多寡库.

 

 

  优点是Master端的Binlog记录所推动的Io量裁减,互连网IO裁减,还大概会让slave端的IO线程,SQL线程减弱,进而大幅升高复制品质,

 

 

 

  • slave-skip-errors=1146

 

  Replicate_Do_DB:设定须要复制的数据库,八个DB用逗号分隔

 

2、调治权力
主库复制过来的数据库全部是root权限,须要改善为mysql权限
#chown -R mysql:mysql /var/lib/mysql

本文由江苏福彩快三发布,转载请注明来源

关键词: 江苏福彩快三