• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    MYSQL 完全备份、主从复制、级联复制、半同步小结

    mysql 完全备份

    1,启用二进制日志,并于数据库分离,单独存放

     vim /etc/my.cnf

    添加

    log_bin=/data/bin/mysql-bin

    创建/data/bin文件夹并授权

    chown mysql.mysql /data/bin

    2,完成备份数据库

    mysqldump -A --single-transaction --master-data=2 | xz > /data/all.sql.xz
    

    3,对数据库进行增删改

     INSERT hellodb.students(stuid,name,gender,age) VALUE(27,'Lujunyi','M',30);
    

    4,停止MySQL

     systemctl stop mariadb.service
    

    5,解压备份文件

    unxz /data/all.sql.xz 

    6,查找完全备份时二进制日志的位置

    vim /data/all.sql 
      HANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=468

    7,导出完成备份后的二进制日志

    mysqlbinlog --start-position=468 /data/bin/mysql-bin.000001 > /data/inc.sql
    

    8,还原数据

    mysql -e 'source /data/all.sql'
    mysql -e 'source /data/inc.sql'
    

    9,验证完成。

    误删除的恢复

    1,启用二进制日志并与数据库分开存放

    vim /etc/my.cnf

    添加

     log_bin=/data/bin/mysql-bin
    

    创建/data/bin文件夹并授权    

     chown mysql.mysql /data/bin

    2,对数据库进行完全备份

    mysqldump -A --single-transaction --master-data=2 | xz > /data/all.sql.xz
    

    3,对数据库进行增删改

    mysql -e "drop table hellodb.students"
      mysql -e "insert hellodb.teachers value (5,'wangqi',50,'M')"
    

    4,停止服务

     systemctl stop mariadb.service
    

    5,删除数据库

    rm -rf /var/lib/mysql/*
    

    6,解压备份文件

    unxz /data/all.sql.xz

    7,查看备份文件,查找二进制节点   

     vim /data/all.sql
       -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=521902;
    

    8,导出二进制日志节点数据

    mysqlbinlog --start-position=521902 /data/bin/mysql-bin.000004 >/data/inc.sql
    

    9,删除二进制日志节点数据中误操作的命令
       

     vim /data/inc.sql
      DROP TABLE `hellodb`.`students` /* generated by server */
    

    10,启动服务 

    systemctl start mariadb.service

    11.关闭二进制日志记录

    mysql -e "SET sql_log_bin=off"
    

    12,导入备份数据

     mysql /data/all.sql 
     mysql /data/inc.sql 
    

    13,验证完成。

    主从复制

    # 主服务器

    1,主服务器启用二进制日志,并更改二进制目录

     vim /etc/my.cnf
      log_bin=/data/bin/mysql-bin
      binlog-format=row
      server-id=1(主从服务器必需保证不同)
    

    更改目录见上面

    2,重启服务

    service mysql restart

    3,创建一个账户用来复制数据的账户

    mysql -e 'GRANT REPLICATION SLAVE ON *.* TO "repluser"@"172.22.7.%" IDENTIFIED BY "centos"

    4,查看主服务器正在使用的二进制日志

     show master logs;
      +------------------+-----------+
      | Log_name   | File_size |
      +------------------+-----------+
      | mysql-bin.000001 |  14383 |
      +------------------+-----------+
      1 row in set (0.00 sec)

    # 从服务器

    5,更配置

     vim /etc/my.cnf
      server-id=2
      read-only
      #log-bin=/data/bin/mysql-bin
    

    6,启动服务

    service mysql restart
    

    7,关联主服务

     MariaDB [(none)]>
     CHANGE MASTER TO MASTER_HOST='172.22.7.70', MASTER_USER='repluser',  MASTER_PASSWORD='centos',  MASTER_PORT=3306,  MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=14383;
    

    8,查看从服务器状态

    show slave status\G;
    

    9,启动线程

    start slave;
    

    #测试

    10,增删改主服务器数据,查看从服务器数据是否同步。

    主从复制出错的解决-sql_slave_skip_counter

    #master服务 ip=172.22.7.70

    1,主服务器启用二进制日志,并更改二进制目录

     vim /etc/my.cnf
      log_bin=/data/bin/mysql-bin
      binlog-format=row
      server-id=1(主从服务器必需保证不同)
    

    更改目录见上面

    2,重启服务

    service mysql restart

    3,创建一个账户用来复制数据的账户

    mysql -e 'GRANT REPLICATION SLAVE ON *.* TO "repluser"@"172.22.7.%" IDENTIFIED BY "centos"
    

    4,查看主服务器正在使用的二进制日志

     mysql -e 'show master logs;'
      +------------------+-----------+
      | Log_name   | File_size |
      +------------------+-----------+
      | mysql-bin.000001 |  264 |
      | mysql-bin.000002 |  245 |
      +------------------+-----------+
    

    #slave服务 ip=172.22.7.71

    5,修改配置文件,启动服务

     vim /etc/my.cnf
      [mysqld]
      server-id = 2
      read-only
     systemctl start mariadb
    

    #配置错误master服务信息

    6,配置,change master to

     CHANGE MASTER TO 
     MASTER_HOST='172.22.7.77', 
     MASTER_USER='wang',  
     MASTER_PASSWORD='lodman',
     MASTER_PORT=3306, 
     MASTER_LOG_FILE=log-bin.001', 
     MASTER_LOG_POS=4, 
     MASTER_CONNECT_RETRY=10;
    

    7,查看slave 状态

     mysql -e 'show slave status\G'
      Slave_IO_State: 
        Master_Host: 172.22.7.77
        Master_User: wang
        Master_Port: 3306
       Connect_Retry: 10
       Master_Log_File: log-bin.001
      Read_Master_Log_Pos: 4
       Relay_Log_File: ct7m1-relay-bin.000001
       Relay_Log_Pos: 4
     Relay_Master_Log_File: log-bin.001
       Slave_IO_Running: No
      Slave_SQL_Running: No
    
           ·········略

    8,启动复制线程

    mysql -e 'start slave'
    

    9,再次查看slave状态

     mysql -e 'show slave status\G'
      Slave_IO_State: 
        Master_Host: 172.22.7.77
        Master_User: wang
        Master_Port: 3306
       Connect_Retry: 10
       Master_Log_File: log-bin.001
      Read_Master_Log_Pos: 4
       Relay_Log_File: ct7m1-relay-bin.000001
       Relay_Log_Pos: 4
     Relay_Master_Log_File: log-bin.001
       Slave_IO_Running: Connecting
      Slave_SQL_Running: Yes
    
           ·········略

    10,master服务增删改数据

    11,查看slave服务是否同步,失败!

    #解决错误

    12,停止并重置slave复制线程服务

     mysql -e 'stop slave'
     mysql -e 'reset slave'
    

     13,配置正确的change master to 信息

     CHANGE MASTER TO MASTER_HOST='172.22.7.70',
     MASTER_USER='repluser',
     MASTER_PASSWORD='centos',
     MASTER_PORT=3306,
     MASTER_LOG_FILE='mysql-bin.000002',
     MASTER_LOG_POS=245;
    

    14,查看slave状态    

    show slave status\G;
       Slave_IO_State: Waiting for master to send event
        Master_Host: 172.22.7.70
        Master_User: repluser
        Master_Port: 3306
       Connect_Retry: 10
       Master_Log_File: mysql-bin.000002
      Read_Master_Log_Pos: 7382
       Relay_Log_File: ct7m1-relay-bin.000002
       Relay_Log_Pos: 540
     Relay_Master_Log_File: mysql-bin.000002
       Slave_IO_Running: Yes
      Slave_SQL_Running: No
    

    15,发现slave状态中Slave_SQL_Running: No ,执行下面命令更为为YES

     MariaDB [(none)]> stop slave;
    Query OK, 0 rows affected (0.00 sec)
    
    MariaDB [(none)]> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
    Query OK, 0 rows affected (0.00 sec)
    
    MariaDB [(none)]> start slave;
    Query OK, 0 rows affected (0.03 sec)
    
    MariaDB [(none)]> show slave status\G;
    *************************** 1. row ***************************
       Slave_IO_State: Waiting for master to send event
        Master_Host: 172.22.7.70
        Master_User: repluser
        Master_Port: 3306
        Connect_Retry: 10
       Master_Log_File: mysql-bin.000002
      Read_Master_Log_Pos: 7382
       Relay_Log_File: ct7m1-relay-bin.000003
        Relay_Log_Pos: 540
      Relay_Master_Log_File: mysql-bin.000002
       Slave_IO_Running: Yes
       Slave_SQL_Running: Yes
            ·········略
    
    

    16,查看slave服务数据是否同步

    17,同步完成。

    Mysql 级联复制

    在生产换进中有一种主从复制的方法主节点先将数据同步到一个中间的从节点,然后由从节点给后续的其他从节点来复制数据,这种复制方式称为级联复制。

    级联复制的好处是可以极大的减轻主节点的压力

    级联复制在配置时需要在中间节点上启用log_slave_updates的选项。

    #环境
     服务器 master   slave   slave
     系统 centos7   centos7   centos7
     ip  172.22.7.70  172.22.7.70  172.22.7.71

    #mater

    1,主服务器启用二进制日志,并更改二进制目录

     vim /etc/my.cnf
      log_bin=/data/bin/mysql-bin
      binlog-format=row
      server-id=1(主从服务器必需保证不同)

    更改目录见上面

    2,重启服务

    service mysql restart

    3,创建一个账户用来复制数据的账户 

    mysql -e 'GRANT REPLICATION SLAVE ON *.* TO "repluser"@"172.22.7.%" IDENTIFIED BY "centos"

    4,查看主服务器正在使用的二进制日志

     mysql -e 'show master logs;'
      +------------------+-----------+
      | Log_name   | File_size |
      +------------------+-----------+
      | mysql-bin.000001 |  264 |
      | mysql-bin.000002 |  7488 |
      | mysql-bin.000003 |  402 |
      +------------------+-----------+
    
     
    #slave
    

    5,修改配置文件,并创建二进制日志目录

     vim /etc/my.cnf
      [mysqld]
      log-bin=/data/bin/mysql-bin
      binlog-format=row
      read-only
      log_slave_updates
      server-id=2
    

    更改目录见上

    6,启动服务

     systemctl restart mariadb
    

    7,配置change master to信息

     CHANGE MASTER TO MASTER_HOST='172.22.7.70',MASTER_USER='repluser',MASTER_PASSWORD='centos',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=402;
    

    8,启动slave线程

     mysql -e 'start slave'

    9,查看slave状态

     show slave status\G;
     *************************** 1. row ***************************
       Slave_IO_State: Waiting for master to send event
        Master_Host: 172.22.7.70
        Master_User: repluser
        Master_Port: 3306
       Connect_Retry: 60
       Master_Log_File: mysql-bin.000003
      Read_Master_Log_Pos: 7539
       Relay_Log_File: ct7m1-relay-bin.000002
       Relay_Log_Pos: 7677
     Relay_Master_Log_File: mysql-bin.000003
       Slave_IO_Running: Yes
      Slave_SQL_Running: Yes
       Replicate_Do_DB:
    

    10,在master上增删改数据测试查看

    #slave1

    11,在slave上将slave上的数据拷贝过来

     mysqldump -A --single-transaction -F --master-data=1 > /data/all.sql
     scp /data/all.sql 172.22.7.72:/data

    12,slave创建一个账户用来复制数据的账户

     mysql -e 'GRANT REPLICATION SLAVE ON *.* TO "repluser"@"172.22.7.%" IDENTIFIED BY "centos"
    

    13,修改slave1配置

     vim /etc/my.cnf
      [mysqld]
      read-only
      server-id=3

    14,启动服务

     systemctl start mariadb
    

    15,查看slave服务器的二进制日志

     mysql -e 'show master logs'
      +------------------+-----------+
      | Log_name   | File_size |
      +------------------+-----------+
      | mysql-bin.000001 |  351 |
      | mysql-bin.000002 |  351 |
      | mysql-bin.000003 |  351 |
      | mysql-bin.000004 |  25552 |
      | mysql-bin.000005 |  586 |
      +------------------+-----------+
    

    16,打开all.sql文件对change master on信息加以修改
     

     CHANGE MASTER TO MASTER_HOST='172.22.7.71',MASTER_USER='repluser',MASTER  _PASSWORD='centos',MASTER_PORT=3306,MASTER_LOG_FILE='mysql-bin.000005',  MASTER_LOG_POS=586;  
    

    17,导入slave数据

    mysql  /data/all.sql
    

    18,启动线程

     mysql -e "START SLAVE;"

    19,查看slave状态

    mysql -e "show slave status\G;"
    

    20,对master对象增删改操作,查看是否同步

    21,同步完成。

    MySQL半同步

    异步复制是当用户写入一条记录时,先将数据写入到主节点,然后回复用户一个写入成功的消息,然后慢慢的将数据复制到其背后的其他从节点,这样的好处是效率比较高,但是缺点也是非常明显,主服务器和从服务器的延迟过大并且主服务器突然发生异常,此时就会造成数据的丢失。

    同步复制是当用户写入一条记录时,主节点将数据写入数据库,然后将数据复制给其后面的其他从节点,当所有的从节点返回数据复制成功后,主节点再回复用户数据接入成功的消息,这样做的好处是,确保了数据的安全性,但损失了效率。

    半同步复制是间于同步复制和异步复制之间的一种复制方法,他的工作原理是:当用户执行写操作时,主节点会将数据发送给其后面的其他从节点,只要有一个从节点返回复制成功的消息,主节点就直接返回写入成功,如果主节点背后的从节点迟迟不返回复制成功消息,此时就会有一个超时时长,一旦达到超时时长,主节点就先返回消息告诉用户复制成功,而后将数据继续给从节点复制。

    #配置主从复制   步骤见上。

    #配置半同步

    ##master

    1,修改配置文件启用插件

     vim /etc/my.cnf
      [mysqld]
      log-bin=/data/bin/mariadb-bin
      binlog-format=row
      server-id=1
      rpl_semi_sync_master_enabled 
    

    2,重启服务,

     systemctl restart mariadb
    

    3,查看插件是否启动 

     SHOW GLOBAL VARIABLES LIKE '%semi%';
      +------------------------------------+-------+
      | Variable_name      | Value |
      +------------------------------------+-------+
      | rpl_semi_sync_master_enabled  | ON |
      | rpl_semi_sync_master_timeout  | 3000 |
      | rpl_semi_sync_master_trace_level | 32 |
      | rpl_semi_sync_master_wait_no_slave | ON |
      +------------------------------------+-------+
      4 rows in set (0.00 sec)
    

    4,设置超时时长

     SET GLOBAL rpl_semi_sync_master_timeout=3000;
     Query OK, 0 rows affected (0.00 sec)
    

    ##slave

    5,修改配置文件启用插件

     vim /etc/my.cnf
      [mysqld]
      log-bin=/data/bin/mariadb-bin
      binlog-format=row
      server-id=2
      rpl_semi_sync_master_enabled
    

    6,重启服务,

    systemctl restart mariadb

    7,查看插件是否启动 

     SHOW GLOBAL VARIABLES LIKE '%semi%';
      +------------------------------------+--------------+
      | Variable_name      | Value  |
      +------------------------------------+--------------+
      | rpl_semi_sync_master_enabled  | ON   |
      | rpl_semi_sync_master_timeout  | 1000   |
      | rpl_semi_sync_master_trace_level | 32   |
      | rpl_semi_sync_master_wait_no_slave | ON   |
      | rpl_semi_sync_master_wait_point | AFTER_COMMIT |
      +------------------------------------+--------------+
      5 rows in set (0.00 sec)
    

    8,启动复制线程

    mysql -e "START SLAVE";

    9,测试检查

    10,同步完成

    以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持脚本之家。

    您可能感兴趣的文章:
    • MySQL半同步复制原理配置与介绍详解
    • 简单谈谈MySQL的半同步复制
    • Mysql半同步复制原理及问题排查
    • 深入解析半同步与异步的MySQL主从复制配置
    • 详解MySQL的半同步
    上一篇:mysql删除关联表的实操方法
    下一篇:Mysql中的索引精讲
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    MYSQL 完全备份、主从复制、级联复制、半同步小结 MYSQL,完全,备份,主从,复制,