欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MySql主从同步

程序员文章站 2024-03-21 08:02:33
...

一. 背景介绍


  • 2017-09-25日,早上上班,发现公司数据库被黑客绑票,要赎金。我们用的MySql 5.6.37,放在XX云上。给客服打电话,建议重装系统,重装MySql。干脆,顺手装了主从同步。
    MySql主从同步
    MySql主从同步
  • 安装环境: Linux version 3.10.0-514.el7.x86_64 (aaa@qq.com) (gcc version 4.8.5 20150623 (Red Hat 4.8.5-11) (GCC) ) #1 SMP Tue Nov 22 16:42:41 UTC 2016;Mysql5.6.37
  • 说明:【192.168.1.203(主库) 192.168.1.202(从库)】分别放在两个服务器上
  • 环境安装好了,再往下面看。谢谢

二. 大致步骤


  • 【主库】

    1. 修改MySql配置文件,在里面加一些配置(我这里设置了两个数据库的主从同步)

      sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
      
      log-bin=mysql-bin
      
      # 用于标识唯一的数据库,在设置从库的时候就需要设置为其他值
      
      server-id=1
      
      # 表示同步的时候忽略的数据库
      
      binlog-ignore-db=information_schema
      binlog-ignore-db=cluster
      binlog-ignore-db=mysql
      
      
      # 指定需要同步的数据库
      
      binlog-do-db=mydb
      binlog-do-db=mydb_store
      
    2. 【进入主库,执行sql】以这里为例,创建了一个用户叫masterslave,密码是123,来自192.168.1.202(也就是从库),允许该用户在主库上读取日志,赋予File权限,只赋予File权限还不行,还要给它REPLICATION SLAVE的权限才可以

      GRANT FILE ON *.* TO 'masterslave'@'192.168.1.202' IDENTIFIED BY '123';
      GRANT REPLICATION SLAVE ON *.* TO 'masterslave'@'192.168.1.202' IDENTIFIED BY '123';
      FLUSH PRIVILEGES;
    3. 重启mysql,查询主库信息

      service mysql restart;
      show master status;

      查询到的结果如下(记住File和Position的值):

      mysql> show master status ;
      +------------------+----------+-----------------+----------------------------------+-------------------+
      | File             | Position | Binlog_Do_DB    | Binlog_Ignore_DB                 | Executed_Gtid_Set |
      +------------------+----------+-----------------+----------------------------------+-------------------+
      | mysql-bin.000027 |      896 | mydb,mydb_store | information_schema,cluster,mysql |                   |
      +------------------+----------+-----------------+----------------------------------+-------------------+
      1 row in set (0.00 sec)
      

  • 从库

    1. 修改MySql配置文件,在里面加一些配置

      sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
      log-bin=mysql-bin
      server-id=2 # 用于标识唯一的数据库,在设置从库的时候就需要设置为其他值
      binlog-ignore-db=information_schema # 表示同步的时候忽略的数据库
      binlog-ignore-db=cluster # 表示同步的时候忽略的数据库
      binlog-ignore-db=mysql # 表示同步的时候忽略的数据库
      replicate-ignore-db=mysql
      replicate-do-db=mydb # 指定需要同步的数据库
      replicate-do-db=mydb_store # 指定需要同步的数据库
      log-slave-updates
      slave-skip-errors=all
      slave-net-timeout=60
    2. 重启MySql

      service mysql restart;
    3. 进入mysql。关闭slave,设置参数,开启slave

      stop slave;
      
      # 【master_log_file 为上面记着的File,master_log_pos为上面记着的position】
      
      change master to master_host='192.168.1.203',master_user='masterslave',master_password='123',master_log_file='mysql-bin.000027', master_log_pos=896;
      show slave status \G;
    4. 如果不出意外,会看到如下信息:

      mysql> show slave status \G;
      
                 Slave_IO_State: Waiting for master to send event
                    Master_Host: 192.168.1.203
                    Master_User: masterslave
                    Master_Port: 3306
                  Connect_Retry: 60
                Master_Log_File: mysql-bin.000027
              Read_Master_Log_Pos: 896
                 Relay_Log_File: ecs-749d-0005-relay-bin.000002
                  Relay_Log_Pos: 1059
                Relay_Master_Log_File: mysql-bin.000027
                   Slave_IO_Running: Yes
                  Slave_SQL_Running: Yes
                    Replicate_Do_DB: mydb,mydb_store
                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: 896
                    Relay_Log_Space: 1240
                    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
                        Master_UUID: 6f9f3a92-a1ce-11e7-b74d-fa163eca40a4
                   Master_Info_File: /usr/local/mysql/data/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.01 sec)
      
      ERROR: 
      No query specified
      
      mysql> 
    5. 如果其中这两行都是YES,那就是成功了

      Slave_IO_Running: Yes
      Slave_SQL_Running: Yes
    6. 如果这两行不是这样,那就说明某个环节配置有问题,常见情况是
      Slave_IO_Running: No
      或者
      Slave_IO_Running: Connecting
      具体信息请看上面slave status里面的字段【 Last_IO_Errno 和 Last_IO_Error】