当前位置: 澳门新濠3559 > 数据库 > 正文

数据库版本,rpm安装方法和碰见的问题

时间:2019-11-09 00:17来源:数据库
使用MySQL5.6,搭建主从复制。关于5.6的安装,可以参考《MySQL 5.6rpm安装方法和碰见的问题》。 1、查看从库数据库状态: mysql show processlist; +----+-------------+-----------------+--------+---------+-

使用MySQL 5.6,搭建主从复制。关于5.6的安装,可以参考《MySQL 5.6 rpm安装方法和碰见的问题》。

1、查看从库数据库状态:
mysql> show processlist;
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
| Id | User        | Host            | db     | Command | Time  | State                                                                       | Info             |
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
|  2 | root        | 127.0.0.1:59012 | sakila | Query   |     0 | NULL                                                                        | show processlist |
|  3 | system user |                 | NULL   | Connect | 17618 | Waiting for master to send event                                            | NULL             |
|  4 | system user |                 | NULL   | Connect | 17305 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

安装环境

3分钟解决MySQL 1032主从错误

 

mysql> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.16.33.143
                  Master_User: master
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql.000003
          Read_Master_Log_Pos: 3296928
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 3296845
        Relay_Master_Log_File: mysql.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: test
           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: 3296928
              Relay_Log_Space: 3297001
              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)

操作系统 :CentOS 6.5

Part1:写在最前
1032错误----现在生产库中好多数据,在从库误删了,生产库更新后找不到了,现在主从不同步了,再跳过错误也没用,因为没这条,再更新还会报错

主库创建slave用户,设置复制权限,

mysql> show processlist;
+----+--------+---------------------+--------+-------------+-------+-----------------------------------------------------------------------+------------------+
| Id | User   | Host                | db     | Command     | Time  | State                                                                 | Info             |
+----+--------+---------------------+--------+-------------+-------+-----------------------------------------------------------------------+------------------+
|  1 | root   | 127.0.0.1:33623     | sakila | Query       |     0 | NULL                                                                  | show processlist |
|  2 | master | 172.16.33.144:48988 | NULL   | Binlog Dump | 17455 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |
+----+--------+---------------------+--------+-------------+-------+-----------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

数据库版本:MySQL 5.6.27

解决方案

 

mysql> show master status;
+--------------+----------+--------------+------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------+----------+--------------+------------------+
| mysql.000003 |  3296928 |              |                  |
+--------------+----------+--------------+------------------+
1 row in set (0.00 sec)

主机A:192.168.1.1 (Master)

Part1:临时方案

mysql> create user 'slave'@'1.1.1.2' identified by 'root';

2、修改配置文件:
主库:read_only = 1
从库:#read_only = 1

主机B:192.168.1.2 (Slave)

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

 

3、操作从库:
mysql> show processlist;
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
| Id | User        | Host            | db     | Command | Time  | State                                                                       | Info             |
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
|  2 | root        | 127.0.0.1:59012 | sakila | Query   |     0 | NULL                                                                        | show processlist |
|  3 | system user |                 | NULL   | Connect | 17770 | Waiting for master to send event                                            | NULL             |
|  4 | system user |                 | NULL   | Connect | 17457 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

Master的配置

mysql> set global sql_slave_skip_counter=1;
Query OK, 0 rows
affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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

在Linux环境下MySQL的配置文件的位置是在 /etc/my.cnf ,在该文件下指定Master的配置如下:

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

 

mysql> show processlist;    
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
| Id | User        | Host            | db     | Command | Time  | State                                                                       | Info             |
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
|  2 | root        | 127.0.0.1:59012 | sakila | Query   |     0 | NULL                                                                        | show processlist |
|  4 | system user |                 | NULL   | Connect | 17526 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

log-bin=mysql-bin

Part2:永久方案

      1. mysql> grant replication slave on *.* to 'slave'@'1.1.1.2' identified by 'root';
      1. Query OK, 0 rows affected (0.00 sec)

确认从库的状态为:Slave has read all relay log

server-id=2

end_log_pos 有了它,根据pos值,直接就能找到,找到delete那条数据,反做(变成insert)

 

mysql> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 172.16.33.143
                  Master_User: master
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql.000003
          Read_Master_Log_Pos: 3296928
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 3296845
        Relay_Master_Log_File: mysql.000003
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: test
           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: 3296928
              Relay_Log_Space: 3297001
              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: 1
1 row in set (0.00 sec)

binlog-ignore-db=information_schema

 

  1. 编辑my.cnf配置文件,设置主库server-id=1,定义需要复制的库为test,忽略mysql数据库

4、查看主库状态:
mysql> show master status;
+--------------+----------+--------------+------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------+----------+--------------+------------------+
| mysql.000003 |  3296928 |              |                  |
+--------------+----------+--------------+------------------+
1 row in set (0.00 sec)

binlog-ignore-db=cluster

 

    1. [root@vm-kvm10000-app mysql]# vi /etc/my.cnf
    1. [mysqld]
    1. server-id=1
    1. log-bin=mysql-bin
    1. binlog_do_db=test
    1. style="font-size: 16px">binlog_ignore_db=mysql

5、主从切换,从库变主库:
mysql> slave stop;
Query OK, 0 rows affected, 1 warning (0.00 sec)

binlog-ignore-db=mysql

故障模拟

 

mysql> reset master;
Query OK, 0 rows affected (0.02 sec)

binlog-do-db=ufind_db

 

  1. 重启主库MySQL服务,

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

这里的server-id用于标识唯一的数据库,这里设置为2,在设置从库的时候就需要设置为其他值。

HE1从库误删

    1. [root@vm-kvm10000-app mysql]# service mysql restart
    1. Shutting down MySQL.. SUCCESS!
    1. Starting MySQL. SUCCESS!

mysql> show master status;
+--------------+----------+--------------+------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------+----------+--------------+------------------+
| mysql.000001 |      107 |              |                  |
+--------------+----------+--------------+------------------+
1 row in set (0.00 sec)

binlog-ignore-db:表示同步的时候ignore的数据库

mysql> delete from helei where id=3;
Query OK, 1 row
affected (0.29 sec)

 

6、主从切换,主库变从库:
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)

binlog-do-db:指定需要同步的数据库

mysql> select * from helei;
+----+------+
| id | text |
+----+------+
| 1 | aa
|
| 2 | bb
|
| 4 | ee
|
| 5 | ff
|
| 6 | gg
|
| 7 | hh
|
+----+------+
6 rows in set (0.00
sec)

  1. 看一下主库状态,

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

1、然后重启mysql:service mysqld restart

mysql> show slave statusG;
***************************

    1. mysql> show master status;
    1. style="font-size: 16px">+------------------+----------+--------------+------------------+-------------------+
    1. | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
    1. style="font-size: 16px">+------------------+----------+--------------+------------------+-------------------+
    1. | mysql-bin.000002 |      120 | test         | mysql            |                   |
    1. style="font-size: 16px">+------------------+----------+--------------+------------------+-------------------+
    1. 1 row in set (0.00 sec)

 

    1. 或者
    1. mysql> show master status G
    1. style="font-size: 16px">*************************** 1. row ***************************
    1. File: mysql-bin.000002
    1. Position: 120
    1. Binlog_Do_DB: test
    1. Binlog_Ignore_DB: mysql
    1. Executed_Gtid_Set:
    1. 1 row in set (0.00 sec)

mysql> change master to master_host='172.16.33.144',master_port=3306,master_user='master',master_password='123456',master_log_file=' mysql-bin.000001',master_log_pos=107;
Query OK, 0 rows affected (0.04 sec)

2、进入mysql:[root@VM_221_4_centos ~]# mysql -u root -p 回车,输入mysql密码进入。

  1. row ***************************
    Slave_IO_State: Waiting for
    master to send event
    Master_Host: 192.168.1.250
    Master_User: mysync
    Master_Port: 2503306
    Connect_Retry: 60
    Master_Log_File: mysql-bin.000005
    Read_Master_Log_Pos: 3711
    Relay_Log_File:
    HE1-relay-bin.000007
    Relay_Log_Pos: 484
    Relay_Master_Log_File: mysql-bin.000005
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    此时从库状态是正常的,但一旦主库对该条记录进行操作

 

7、重启主从:
[root@yoon ~]# service mysqld restart
Shutting down MySQL. SUCCESS! 
Starting MySQL.. SUCCESS! 

3、 赋予从库权限帐号,允许用户在主库上读取日志,赋予192.168.1.2也就是Slave机器有File权限,只赋予Slave机器有File权限还不行,还要给它REPLICATION SLAVE的权限才可以。

HE3主库更新从库刚刚误删的数据

  1. 从库,编辑my.cnf配置,设置server-id=2,区别于主库,

8、查看主从是否正常:
主库:
show processlist;
show master status;

在Master数据库命令行中输入:

mysql> update helei set text='ccc' where id=3;
Query OK, 1 row
affected (0.01 sec)
Rows matched: 1 Changed: 1
Warnings: 0

    1. [root@vm-kvm10001-app mysql]# vi /etc/my.cnf
    1. [mysqld]
    1. server-id=2

从库:
show processlist;
start slave;
show slave statusG

>GRANT FILE ON *.* TO 'root'@'192.168.1.2' IDENTIFIED BY 'mysql password';

mysql> select * from helei;
+----+------+
| id | text |
+----+------+
| 1 | aa
|
| 2 | bb
|
| 3 | ccc
|
| 4 | ee
|
| 5 | ff
|
| 6 | gg
|
| 7 | hh
|
+----+------+
7 rows in set (0.00
sec)
HE1从库报错

 

 

>GRANT REPLICATION SLAVE ON *.* TO 'root'@'192.168.1.2' IDENTIFIED BY 'mysql password';

mysql> show slave statusG;
***************************

  1. 重启MySQL服务,

>FLUSH PRIVILEGES

  1. row ***************************
    Slave_IO_State: Waiting for
    master to send event
    Master_Host: 192.168.1.250
    Master_User: mysync
    Master_Port: 2503306
    Connect_Retry: 60
    Master_Log_File: mysql-bin.000005
    Read_Master_Log_Pos: 3918
    Relay_Log_File:
    HE1-relay-bin.000007
    Relay_Log_Pos: 484
    Relay_Master_Log_File: mysql-bin.000005
    Slave_IO_Running: Yes
    Slave_SQL_Running: No
    Replicate_Do_DB:
    澳门新濠3559,Replicate_Ignore_DB:
    Replicate_Do_Table:
    Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
    Replicate_Wild_Ignore_Table:
    Last_Errno: 1032
    Last_Error: Could not
    execute Update_rows event on table test.helei; Can't find record in 'helei',
    Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log
    mysql-bin.000005, end_log_pos 3887
    Skip_Counter: 0
    Exec_Master_Log_Pos: 3711
    Relay_Log_Space: 1626
    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: 1032
    1. [root@vm-kvm10001-app mysql]# service mysql restart
    1. Shutting down MySQL.. SUCCESS!
    1. Starting MySQL. SUCCESS!

4、重启mysql,登录mysql,显示主库信息

Last_SQL_Error: Could not execute Update_rows event on table test.helei;
Can't find record in 'helei', Error_code: 1032; handler error
HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000005, end_log_pos 3887(这个mysql-bin.000005,end_log_pos
3887是主库的)
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2503306
Master_UUID:
f7c96432-f665-11e5-943f-000c2967a454
Master_Info_File:
/data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 160331 09:25:02
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00
sec)

 

mysql> show master status;

此时主从又不同步了,如果还去执行跳过错误操作,主从恢复同步,而且状态均为yes,但!这并不能解决该问题,如果主库又更新该条记录,那么还是会报相同错误,而且pos号还会变,这就导致了恢复时你不知道前一条的pos号,导致丢失数据。

  1. 设置主库信息,

1

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

    1. mysql> change master to master_host='1.1.1.1',master_user='slave',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=120,master_connect_retry=10;
    1. Query OK, 0 rows affected, 2 warnings (0.03 sec)

1

mysql> set global sql_slave_skip_counter=1;
Query OK, 0 rows
affected (0.00 sec)

 

mysql> show master status;

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

  1. 检索从库状态,

+------------------+----------+--------------+----------------------------------+-------------------+

mysql> select * from helei;
+----+--------+
| id | text |
+----+--------+
| 1 | aa
|
| 2 | bb
|
| 4 | ee
|
| 5 | ff
|
| 6 | gg
|
| 7 | hh
|
| 8 | helei1 |
+----+--------+
7 rows in set (0.00 sec)

    1. mysql> show slave status G;
    1. style="font-size: 16px">*************************** 1. row ***************************
    1. Slave_IO_State:
    1. Master_Host: 10.221.3.129
    1. Master_User: slave
    1. Master_Port: 3306
    1. Connect_Retry: 10
    1. Master_Log_File: mysql-bin.000001
    1. Read_Master_Log_Pos: 120
    1. Relay_Log_File: vm-kvm11853-app-relay-bin.000001
    1. Relay_Log_Pos: 4
    1. Relay_Master_Log_File: mysql-bin.000001
    1. Slave_IO_Running: No
    1. Slave_SQL_Running: No
    1. Replicate_Do_DB:
    1. style="font-size: 16px">Replicate_Ignore_DB:
    1. Replicate_Do_Table:
    1. style="font-size: 16px">Replicate_Ignore_Table:
    1. style="font-size: 16px">Replicate_Wild_Do_Table:
    1. style="font-size: 16px">Replicate_Wild_Ignore_Table:
    1. Last_Errno: 0
    1. Last_Error:
    1. Skip_Counter: 0
    1. Exec_Master_Log_Pos: 120
    1. Relay_Log_Space: 120
    1. Until_Condition: None
    1. Until_Log_File:
    1. Until_Log_Pos: 0
    1. Master_SSL_Allowed: No
    1. style="font-size: 16px">Master_SSL_CA_File:
    1. style="font-size: 16px">Master_SSL_CA_Path:
    1. Master_SSL_Cert:
    1. Master_SSL_Cipher:
    1. Master_SSL_Key:
    1. Seconds_Behind_Master: NULL
    1. style="font-size: 16px">Master_SSL_Verify_Server_Cert: No
    1. Last_IO_Errno: 0
    1. Last_IO_Error:
    1. Last_SQL_Errno: 0
    1. Last_SQL_Error:
    1. style="font-size: 16px">Replicate_Ignore_Server_Ids:
    1. Master_Server_Id: 0
    1. Master_UUID:
    1. Master_Info_File: /var/lib/mysql/master.info
    1. SQL_Delay: 0
    1. SQL_Remaining_Delay: NULL
    1. style="font-size: 16px">Slave_SQL_Running_State:
    1. Master_Retry_Count: 86400
    1. Master_Bind:
    1. style="font-size: 16px">Last_IO_Error_Timestamp:
    1. style="font-size: 16px">Last_SQL_Error_Timestamp:
    1. Master_SSL_Crl:
    1. style="font-size: 16px">Master_SSL_Crlpath:
    1. style="font-size: 16px">Retrieved_Gtid_Set:
    1. style="font-size: 16px">Executed_Gtid_Set:
    1. Auto_Position: 0
    1. 1 row in set (0.00 sec)

| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB                | Executed_Gtid_Set |

mysql> show slave statusG;
***************************

 

+------------------+----------+--------------+----------------------------------+-------------------+

  1. row ***************************
    Slave_IO_State: Waiting for
    master to send event
    Master_Host: 192.168.1.250
    Master_User: mysync
    Master_Port: 2503306
    Connect_Retry: 60
    Master_Log_File: mysql-bin.000005
    Read_Master_Log_Pos: 4119
    Relay_Log_File:
    HE1-relay-bin.000008
    Relay_Log_Pos: 283
    Relay_Master_Log_File: mysql-bin.000005
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
  1. 其中最重要的就是,这两个参数,要求YES,但此处为NO,

| mysql-bin.000004 |    28125 | ufind_db    | information_schema,cluster,mysql |                  |

这里虽然通过跳过错误达到恢复主从同步,但如果主库又对该条记录更新

    1. Slave_IO_Running: No
    1. Slave_SQL_Running: No

+------------------+----------+--------------+----------------------------------+-------------------+

mysql> update helei set text='cccc' where id=3;
Query OK, 1 row
affected (0.00 sec)
mysql> show slave statusG;
***************************

 

1 row in set (0.00 sec)

  1. row ***************************
    Slave_IO_State: Waiting for
    master to send event
    Master_Host: 192.168.1.250
    Master_User: mysync
    Master_Port: 2503306
    Connect_Retry: 60
    Master_Log_File: mysql-bin.000005
    Read_Master_Log_Pos: 4328
    Relay_Log_File:
    HE1-relay-bin.000008
    Relay_Log_Pos: 283
    Relay_Master_Log_File: mysql-bin.000005
    Slave_IO_Running: Yes
  1. 检索错误日志,提示无法找见./performance_schema/cond_instances.frm文件,

Slave的配置

Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:

    1. [root@vm-kvm10001-app mysql]# vm-kvm10001-app.err
    1. 2017-08-29 16:10:37 22933 [ERROR] /usr/sbin/mysqld: Can't find file: './performance_schema/cond_instances.frm' (errno: 13 - Permission denied)

1、从库的配置,首先也是修改配置文件:/etc/my.cnf 如下:

Last_Errno: 1032

 

log-bin=mysql-bin

Last_Error: Could not execute Update_rows event on table test.helei;
Can't find record in 'helei', Error_code: 1032; handler error
HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000005, end_log_pos 4297
Skip_Counter: 0
Exec_Master_Log_Pos: 4119
Relay_Log_Space: 1435
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:

  1. 看一下其目录,发现performance_schema文件夹,是root权限,mysql用户无法访问,因此需要修改一下其权限,

server-id=3

Last_SQL_Errno: 1032

  1. [root@vm-kvm10001-app mysql]# ls -rlht
  2. total 109M
  3. ...
  4. drwx------ 2 root  root  4.0K Aug 29 15:05 performance_schema
  5. ...

 

  1. [root@vm-kvm10001-app mysql]# chown -R mysql:mysql *

 

  1. [root@vm-kvm10001-app mysql]# ls -rlht
  2. total 109M
  3. ...
  4. drwx------ 2 mysql mysql 4.0K Aug 29 15:05 performance_schema

  5. ...

binlog-ignore-db=information_schema

Last_SQL_Error: Could not execute Update_rows event on table test.helei;
Can't find record in 'helei', Error_code: 1032; handler error
HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000005, end_log_pos 4297
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2503306
Master_UUID:
f7c96432-f665-11e5-943f-000c2967a454
Master_Info_File:
/data/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 160331 09:33:34
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0

 

binlog-ignore-db=cluster

如何快速解决MySQL 1032 主从错误

  1. 重启服务,

binlog-ignore-db=mysql

    1. [root@vm-kvm10001-app mysql]# service mysql restart
    1. Shutting down MySQL.... SUCCESS!
    1. Starting MySQL. SUCCESS!

replicate-do-db=ufind_db

 

replicate-ignore-db=mysql

  1. 再看从库状态,

log-slave-updates

    1. mysql> show slave status G
    1. style="font-size: 16px">*************************** 1. row ***************************
    1. ...
    1. Slave_IO_Running: Yes
    1. Slave_SQL_Running: Yes
    1. ...

slave-skip-errors=all

 

slave-net-timeout=60

  1. 主库导出数据,用于导入从库,首先需要设置读锁,避免数据不一致,

2、修改完/etc/my.cnf 文件之后,重启一下MySQL(service mysqld restart)

    1. mysql> flush tables with read lock;

 

    1. mysql> show master logs;
    1. style="font-size: 16px">+------------------+-----------+
    1. | Log_name         | File_size |
    1. style="font-size: 16px">+------------------+-----------+
    1. | mysql-bin.000001 |       143 |
    1. | mysql-bin.000002 |       222 |
    1. | mysql-bin.000003 |       545 |
    1. style="font-size: 16px">+------------------+-----------+
    1. 3 rows in set (0.00 sec)

进入Slave mysql控制台,执行:

 

这里写图片描述

  1. 执行mysqldump将test库,导出test.sql文件,

mysql> stop slave;  #关闭Slave

    1. [root@vm-kvm10000-app mysql]# mysqldump -uroot -p -B test > test.sql

mysql> change master to master_host='192.168.1.1',master_user='root',master_password='123456',master_log_file='mysql-bin.000004', master_log_pos=28125;

 

mysql> start slave;  #开启Slave

  1. 然后解锁表,

在这里指定Master的信息,master_log_file是在配置Master的时候的File选项, master_log_pos是在配置Master的Position 选项,这里要进行对应。

    1. mysql> unlock tables;

然后可以通过mysql> show slave status; 查看配置的信息:

 

mysql> show slave status G;

  1. 从库执行导入,

*************************** 1. row ***************************

    1. [root@vm-kvm10001-app mysql]# mysql -uroot -p < test.sql

Slave_IO_State: Waiting for master to send event

 

Master_Host: 192.167.1.1

  1. 要确保从库,这两个值正确,

Master_User: root

    1. Slave_IO_Running: Yes    
  1. Slave_SQL_Running: Yes

Master_Port: 3306

 

Connect_Retry: 60

  1. 此时就完成了主从复制,向主库插入一条记录,

Master_Log_File: mysql-bin.000004

    1. mysql> INSERT INTO test
  1.     -> (id, name)
  2.     -> VALUES
  3.     -> (1, "a");
  4. Query OK, 1 rows affected (0.00 sec)

Read_Master_Log_Pos: 28125

 

Relay_Log_File: VM_128_194_centos-relay-bin.000004

  1. 从库中可以检索出来,

Relay_Log_Pos: 26111

    1. mysql> select * from test;
    1. +-------+--------+
    1. |     id    |  name  |
    1. +-------+--------+
    1. |      1    |     a      |
    1. +-------+--------+

Relay_Master_Log_File: mysql-bin.000004

 

Slave_IO_Running: Yes

 

Slave_SQL_Running: Yes

  1. 总结:
  2. 1. MySQL相关文件、文件夹需要的权限,可能会因为不正确,例如需要mysql权限,但却是root,导致数据库异常。
  3. 2. 主从复制,需要关注从库,这两个参数值,需要均为YES,出现NO,则可以检索错误日志,进一步定位。
  4. Slave_IO_Running: Yes
  5. Slave_SQL_Running: Yes

Replicate_Do_DB: ufind_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: 28125

Relay_Log_Space: 26296

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: 2

Master_UUID: 8ac3066a-9680-11e5-a2ec-5254007529fd

Master_Info_File: /data/mysqldb/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

(l)、 Master_Log_File,Read_Master_Log_Pos 记录了IO thread读到的当前master binlog文 件和位置, 对应master的binlog文件和位置。

(2)、 Relay_Log_File,Relay_Log_Pos记录了SQL thread执行到relay log的那个文件和位置,对应的是slave上的relay log文件和位置。

(3)、 Relay_Master_Log_File,Exec_Master_Log_Pos记录的是SQL thread执行到master binlog的文件和位置,对应的master上binlog的文件和位置。

可以看到,已经配置成功。


主从不一致处理:

stop slave;

set global sql_slave_skip_counter =1;

start slave;


主从不一致处理:

1.先进入主库,进行锁表,防止数据写入

mysql> flush tables with read lock;

2.进行数据备份

[root@server01 mysql]#mysqldump -uroot -p test > test.sql

3.查看master 状态

mysql> show master status;

+-------------------+----------+--------------+-------------------------------+

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB              |

+-------------------+----------+--------------+-------------------------------+

| mysqld-bin.000001 |    3260 |              | mysql,test,information_schema |

+-------------------+----------+--------------+-------------------------------+

1 row in set (0.00 sec)

4.把mysql备份文件传到从库机器,进行数据恢复

#使用scp命令

[root@server01 mysql]# scp test.sql root@192.168.128.101:/tmp/

5.停止从库的状态

mysql> stop slave;

6.然后到从库执行mysql命令,导入数据备份

mysql> source /tmp/test.sql

7.设置从库同步,注意该处的同步点,就是主库show master status信息里的| File| Position两项

change master to master_host = '192.168.128.100', master_user = 'rsync', master_port=3306, master_password='', master_log_file = 'mysqld-bin.000001', master_log_pos=3260;

8.重新开启从同步

mysql> start slave;

9.查看同步状态

mysql> show slave statusG  查看:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

好了,同步完成啦。

编辑:数据库 本文来源:数据库版本,rpm安装方法和碰见的问题

关键词: