• MySQL数据库企业级应用实践(主从复制)


    MySQL数据库企业级应用实践(主从复制)

    链接:https://pan.baidu.com/s/1ANGg3Kd_28BzQrA5ya17fQ
    提取码:ekpy
    复制这段内容后打开百度网盘手机App,操作更方便哦

    1.MySQL主从复制简介

    MySQL数据库的主从复制方案,与使用scp/rsync等命令进行的文件级别复制类似,都是数据的远程传输,只不过MySQL的主从复制是其自带的功能,无需借助第三方工具,而且,MySQL的主从复制并不是数据库磁盘上的文件直接拷贝,而是通过逻辑的binlog日志复制到要同步的服务器本地,然后由本地的线程读取日志里面的SQL语句,重新应用到MySQL数据库中。

    2.概述

    • MySQL数据库支持单向,双向,链式级联,环状等不同业务场景的复制。在复制过程中,一台服务器充当主服务器(Master),接收来自用户的内容更新,而一个或多个其他的服务器充当从服务器(Slave),接收来自主服务器binlog文件的日志内容,解析出SQL,重新更新到从服务器,使得主从服务器数据达到一致。
    • 如果设置了链式级联复制,那么,从服务器(Slave)本身除了充当从服务器外,也会同时充当其下面从服务器的主服务器。链式级联复制类似A-->B-->C的复制形式。

    下图为单向主从复制架构逻辑图,此架构只能在Master端进行数据写入

    image_1cl0j2dl916mj17l2181jefr1viqp.png-34.6kB

    image_1cl0j3e5t49e5jgi6p1runcmq29.png-68.6kB

    下图为双向主主复制逻辑架构图,此架构可以在Master1端或Master2端进行数据写入,或者两端同时写入数据(需要特殊设置)

    image_1cl0j5roq19om1kmbgphvob1sd32m.png-37.6kB

    下图为线性级联单向双主复制逻辑架构图,此架构只能在Master1端进行数据写入,工作场景中,Master1和master2作为主主互备,Slave1作为从库,中间的Master2需要做特殊的设置。

    image_1cl0j6viv19hv3q1fh91qtu1ej133.png-54.6kB

    下图为环状级联单向多主同步逻辑架构图,任意一个点都可以写入数据,此架构比较复杂,属于极端环境下的“成品”,一般场景慎用

    image_1cl0j815g1titvaj5eu10ic48q3g.png-69.9kB

    3.小结

    在当前的生产工作中,MySQL主从复制都是异步的复制方式,既不是严格实时的数据同步,但是正常情况下给用户的体验是真实的。

    4.MySQL主从复制的企业应用场景

    4.1从服务器作为主服务器的实时数据备份

    • 主从服务器架构的设置可以大大加强MySQL数据库架构的健壮性。例如:当主服务器出现问题时,我们可以人工或设置自动切换到从服务器继续提供服务,此时从服务器的数据与宕机时的主数据库几乎是一致的。
    • 这类似NFS存储数据通过inotify + rsync同步到备份的NFS服务器,只不过MySQL的复制方案是其自带的工具。
    • 利用MySQL的复制功能进行数据备份时,在硬件故障,软件故障的场景下,该数据备份是有效的,但对于人为地执行drop,delete等语句删除数据的情况,从库的备份功能就没用了,因为从服务器也会执行删除的语句。

    4.2主从服务器实现读写分离,从服务器实现负载均衡

    • 主从服务器架构可通过程序(PHP,java等)或代理软件(mysql-proxy,Amoeba)实现对用户(客户端)的请求读写分离,即让从服务器仅仅处理用户的select查询请求,降低用户查询响应时间,以及同时读写在主服务器上带来的访问压力。对于更新的数据(例如:update,insert,delete语句),则仍然交给主服务器处理,确保主服务器和从服务器保持实时同步。
    • 百度,淘宝,新浪等绝大多数的网站都是用户浏览页面多于用户发布内容,因此通过在从服务器上接收只读请求,就可以很好地减轻主库的读压力,且从服务器可以很容易地扩展为多台,使用LVS做负载均衡效果就非常棒了,这就是传说中的数据库读写分离架构。逻辑架构图如下所示:

    image_1cl0jv1tcjs81icq63rnih1cs93t.png-158.7kB

    4.3把多个从服务器根据业务重要性进行拆分访问

    可以把几个不同的从服务器,根据公司的业务进行拆分。例如:有为外部用户提供查询服务的从服务器,有内部DBA用来数据备份的从服务器,还有为公司内部人员提供访问的后台,脚本,日志分析及供开发人员查询使用的从服务器。这样的拆分除了减轻主服务器的压力外,还可以使数据库对外部用户浏览,内部用户业务处理及DBA人员的备份等互不影响。

    5.实现MySQL主从读写分离的方案

    5.1通过程序实现读写分离(性能和效率最佳,推荐)

    PHP和Java程序都可以通过设置多个连接文件轻松地实现对数据库的读写分离,即当语句关键字为select时,就去连接读库的连接文件,若为update,insert,delete时,则连接写库的连接文件。
    通过程序实现读写分离的缺点就是需要开发人员对程序进行改造,使其对下层不透明,但这种方式更容易开发和实现,适合互联网业务场景。

    根据业务重要性拆分从库方案

    image_1cl0ks6ci1otudn4b501uup1nvi1c.png-181.3kB

    5.2通过开源的软件实现读写分离

    MySQL-proxy,Amoeba等代理软件也可以实现读写分离功能,这些软件的稳定性和功能一般,不建议生产使用。绝大多数公司常用的还是在应用端发程序实现读写分离。

    5.3大型门户独立开发DAL层综合软件

    百度,阿里等大型门户都有开发牛人,会花大力气开发适合自己业务的读写分离,负载均衡,监控报警,自动扩容,自动收缩等一系列功能的DAL层软件。

    image_1cl0kuj5u1hp81o4b1unhfe21mj51p.png-121.4kB

    6.MySQL主从复制原理过程详细描述

    6.1简单描述MySQL Replication的复制原理过程

    • 在Slave服务器上执行start slave命令开启主从复制开关,开始进行主从复制
    • 此时,Slave服务器的I/O线程会通过在Master上已经授权的复制用户权限请求连接Master服务器,并请求从指定binlog日志文件的指定位置(日志文件名和位置就是在配置主从复制服务时执行change master命令指定的)之后开始发送binlog日志内容。
    • Master服务器接收到来自Slave服务器的I/O线程的请求后,其上负责复制的I/O线程会根据Slave服务器的I/O线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog日志信息,然后返回给Slave端的I/O线程。返回的信息中除了binlog日志内容外,还有在Master服务器端记录的新的binlog文件名称,以及在新的binlog中的下一个指定更新位置。
    • 当Slave服务器的I/O线程获取到Master服务器上I/O线程发送的日志内容,日志文件及位置点后,会将binlog日志内容依次写到Slave端自身的Relay Log(即中继日志)文件(MySQL-relay-bin.xxxx)的最末端,并将新的binlog文件名和位置记录到master-info文件中,以便下一次读取Master端新binlog日志时能够告诉Master服务器从新binlog日志的指定文件及位置开始请求新的binlog日志内容。
    • Slave服务器端的SQL线程会实时检测本地Relay Log中I/O线程新增加的日志内容,然后及时地把Relay Log文件中的内容解析成SQL语句,并在自身Slave服务器上按解析SQL语句的位置顺序执行应用这些SQL语句,并在relay-log.info中记录当前应用中继日志的文件名及位置点。

    6.2MySQL Replication的复制原理逻辑图

    image_1cl0l79egkp41lug1u5cotk16hf2m.png-115.8kB

    6.3针对MySQL主从复制原理的重点进行小结

    • 主从复制是异步的逻辑的SQL语句级的复制
    • 复制时,主库有一个I/O线程,从库有两个线程,即I/O和SQL线程
    • 实现主从复制的必要条件是主库要开启记录binlog功能
    • 作为复制的所有MySQL节点的server-id都不能相同。
    • binlog文件只记录对数据库有更改的SQL语句(来自主数据库内容的变更),不记录任何查询(如select,show)语句。

    7.MySQL主从复制实践

    7.1主从复制数据库实战环境准备

    [root@yangwenbo /]# ss -antup | grep 330
    tcp    LISTEN     0      128            *:3307             *:*  users:(("mysqld",2576,11))
    tcp    LISTEN     0      128            *:3306             *:*  users:(("mysqld",4043,11))
    

    7.2修改主库的配置文件(3306为主,3307为从)

    [root@yangwenbo /]# vim /data/3306/my.cnf 
      4 [mysqld]             #下面两个参数必须放在[mysqld]模块下,否则会出错
     53 server-id = 1        #用于同步的每台机器或实例server-id都不能相同
     54 log-bin=mysql-bin    #binlog日志的位置
    [root@yangwenbo /]# vim /data/3307/my.cnf 
      4 [mysqld] 
     53 server-id = 5        #不能与主库相同(可以为任意数值)
    

    7.3重启主库MySQL服务

    [root@yangwenbo /]# /data/3306/mysql restart
    Restarting MySQL...
    Stoping MySQL...
    Starting MySQL....
    

    7.4登陆3306主库,检查参数的更改情况

    [root@yangwenbo /]# mysql -uroot -p971108 -S /data/3306/mysql.sock        #登陆3306实例
    Welcome to the MySQL monitor.  Commands end with ; or g.
    Your MySQL connection id is 1
    Server version: 5.5.22-log Source distribution
    
    Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
    
    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.
    
    Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.
    
    mysql> show variables like 'server_id';        #查看MySQL的系统变量(like类似于grep过滤)
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+                      #配置的server_id为1
    | server_id     | 1     |
    +---------------+-------+
    1 row in set (0.00 sec)
    
    mysql> show variables like 'log_bin';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+                      #binlog功能已开启
    | log_bin       | ON    |
    +---------------+-------+
    1 row in set (0.00 sec)
    
    
    [root@yangwenbo ~]# ls /data/3306/data         #这样,binlog功能就开启了
    ibdata1      ib_logfile1  mysql             mysql-bin.index     test
    ib_logfile0  ib_logfile2  mysql-bin.000001  performance_schema
    

    7.5登陆3306主库,建立用于主从复制的账号

    mysql> grant replication slave on *.* to 'yunjisuan'@'192.168.%' identified by '971108';
    
    Query OK, 0 rows affected (0.00 sec)
    
    #语句说明:
    1)replication slave为mysql同步的必须权限,此处不要授权all权限
    2)*.* 表示所有库所有表,也可以指定具体的库和表进行复制。例如yunjisuan.test中,yunjisuan为库名,test为表名
    3)'yunjisuan'@'192.168.0.%' yunjisuan为同步账号。192.168.%为授权主机网段,使用了%表示允许整个192.168.0.0网段可以用yunjisuan这个用户访问数据库
    4)identified by '971108';  971108为密码,实际环境下设置的复杂些为好
    

    7.6创建完账号并授权后,需要刷新权限,使授权的权限生效

    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    

    7.7检查主库创建的yunjisuan复制账号命令及结果

    mysql> select user,host from mysql.user;
    +-----------+-----------+
    | user      | host      |
    +-----------+-----------+
    | root      | 127.0.0.1 |
    | yunjisuan | 192.168.% |       #出现这行表示复制账号已经配置好了
    | root      | ::1       |
    |           | localhost |
    | root      | localhost |
    |           | yangwenbo |
    | root      | yangwenbo |
    +-----------+-----------+
    7 rows in set (0.00 sec)
    
    #说明:MySQL里的授权用户是以数据表格的形式存储在mysql这个库的user表里。
    
    mysql> show grants for yunjisuan@'192.168.%';
    +-----------------------------------------------------------------------------------------------+
    | Grants for yunjisuan@192.168.%                                                                |
    +-----------------------------------------------------------------------------------------------+
    | GRANT REPLICATION SLAVE ON *.* TO 'yunjisuan'@'192.168.%' IDENTIFIED BY PASSWORD '*2E086B4AB841306370F090F3973AC88BDAA569D3'                                                                   |
    +-----------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    

    注意:设置主从复制之前要注意两台机器内容一致(可以通过备份的方式以及Rsync来实现)

    7.8登陆3307从库,配置复制参数

    mysql> CHANGE MASTER TO 
    MASTER_HOST='192.168.0.102',            #这里是主库的IP
    MASTER_PORT=3306,                       #这里是主库的端口,从库端口可以和主库不同
    MASTER_USER='yunjisuan';                #这里是主库上建立的用于复制的用户yunjisuan
    MASTER_PASSWORD='971108',               #这里是yunjisuan用户的密码
    MASTER_LOG_FILE='mysql-bin.000001',     #这里是show  master status时查看到的二进制日志文件名称
    MASTER_LOG_POS=962;                     #这里是show master status时查看到的二进制日志偏移量
    
    Query OK, 0 rows affected (0.02 sec)
    

    注意:上方的962是参照主库的位置

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

    7.9从库查看授权结果

    mysql> show slave statusG;
    *************************** 1. row ***************************
                   Slave_IO_State: 
                      Master_Host: 192.168.0.102
                      Master_User: yunjisuan
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 962
                   Relay_Log_File: relay-bin.000001
                    Relay_Log_Pos: 4
            Relay_Master_Log_File: mysql-bin.000001
                 Slave_IO_Running: No
                Slave_SQL_Running: No
                 #中间省略。。。
                 Seconds_Behind_Master: 0
                 #以下省略。。。
    

    7.10从库激活主从复制

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

    7.11从库查看主从复制状态

    mysql> show slave statusG;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.0.102
                      Master_User: yunjisuan
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 962
                   Relay_Log_File: relay-bin.000002
                    Relay_Log_Pos: 253
            Relay_Master_Log_File: mysql-bin.000001
                 Slave_IO_Running: Yes        #只要这里两个是`Yes`,
                Slave_SQL_Running: Yes        就说明主从复制状态正常
                   #中间省略。。。
                Seconds_Behind_Master: 0      #0表示已经同步状态
                   #以下省略。。。
    

    7.12主从同步是否成功,最关键的为下面的3项状态参数:

    [root@yangwenbo ~]# mysql -uroot -p971108 -S /data/3307/mysql.sock -e "show slave statusG" | egrep "IO_Running|SQL_Running|Seconds_Behind_Master"
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
            Seconds_Behind_Master: 0
    
    • Slave_IO_Running:Yes,这个时I/O线程状态,I/O线程负责从从库到主库读取binlog日志,并写入从库的中继日志,状态为Yes表示I/O线程工作正常。
    • Slave_SQL_Running:Yes,这个是SQL线程状态,SQL线程负责读取中继日志(relay-log)中的数据并转换为SQL语句应用到从数据库中,状态为Yes表示I/O线程工作正常。
    • Seconds_Behind_Master:0,这个是复制过程中从库比主库延迟的秒数,这个参数极度重要,但我们可以更准确地判断主从延迟的方法为:在主库写时间戳,然后从库读取时间戳,和当前数据库时间进行比较,从而认定是否延迟。

    7.13检验主从复制是否成功

    7.13.1已知查看主库下信息如下

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    +--------------------+
    4 rows in set (0.00 sec)
    

    7.13.2已知查看从库下信息如下

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    +--------------------+
    4 rows in set (0.00 sec)
    

    从以上信息来看,主从数据一致

    7.13.3为检验成果,在主库上创建一个小库

    mysql> create database yangwenbo;
    Query OK, 1 row affected (0.08 sec)
    

    7.13.4检验当下主库内数据

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    | yangwenbo          |
    +--------------------+
    5 rows in set (0.00 sec)
    

    7.13.5检验当下从库内数据

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    | yangwenbo          |
    +--------------------+
    5 rows in set (0.00 sec)
    

    以上信息数据表明,主从复制成功

    7.14重置主从复制记录

    mysql> reset slave all;   #重置主从复制记录
    
    

    8. MySQL主从复制配置完整步骤小结

    • 准备两台数据库环境或单台多实例环境,确定能正常启动和登陆
    • 配置my.cnf文件:主库配置log-binserver-id参数;从库配置server-id,该值不能和主库及其他从库一样,一般不开启从库log-bin功能。注意,配置参数后要重启才能生效。
    • 登陆主库,增加从库连接主库同步的账户,例如:yunjisuan,并授权replication slave同步的权限。
    • 登陆主库,整库锁表flush table with read lock(窗口关闭后即失效,超时参数设置的时间到了,锁表也失效),然后show master status查看binlog的位置状态。
    • 新开窗口,在Linux命令行备份导出原有的数据库数据,并拷贝到从库所在的服务器目录。如果数据库数据量很大,并且允许停机,可以停机打包,而不用mysqldump
    • 导出主库数据后,执行unlock tables解锁主库。
    • 把主库导出的数据恢复到从库
    • 根据主库的show master status查看到的binlog的位置状态,在从库执行change master to....语句。
    • 从库开启复制开关,即执行start slave;
    • 从库show slave statusG,检查同步状态,并在主库进行更新测试。

    9.MySQL主从复制线程状态说明及用途

    9.1MySQL主从复制主库I/O线程状态说明

    9.1.1登陆主数据库查看MySQL线程的同步状态

    mysql> show processlistG;
    *************************** 1. row ***************************
         Id: 1
       User: yunjisuan
       Host: 192.168.0.102:33254
         db: NULL
    Command: Binlog Dump
       Time: 949
      State: Master has sent all binlog to slave; waiting for binlog to be updated
       Info: NULL
    *************************** 2. row ***************************
         Id: 2
       User: root
       Host: localhost
         db: NULL
    Command: Query
       Time: 0
      State: NULL
       Info: show processlist
    2 rows in set (0.00 sec)
    
    ERROR: 
    No query specified
    #提示:上述状态的意思是线程已经从binlog日志读取所有更新,并已经发送到了从数据库服务器。线程目前为空闲状态,等待由主服务器上二进制日志中的新事件更新。
    

    下图中列出了主服务器binlog Dump线程中State列的最常见状态。如果你没有在主服务器上看见任何binlog Dump线程,则说明复制没有运行,二进制binlog日志由各种事件组成,事件通常会为更新添加信息。
    image_1cl17k92artuuq0151u1i4b177g20.png-52.3kB

    9.1.2登陆从数据库查看MySQL线程工作状态

    从库有两个线程,即I/O和SQL线程。从库I/O线程的状态如下:

    mysql> show processlistG;
    *************************** 1. row ***************************
         Id: 1
       User: system user
       Host: 
         db: NULL
    Command: Connect
       Time: 1181
      State: Slave has read all relay log; waiting for the slave I/O thread to update it
       Info: NULL
    *************************** 2. row ***************************
         Id: 2
       User: system user
       Host: 
         db: NULL
    Command: Connect
       Time: 1211
      State: Waiting for master to send event
       Info: NULL
    *************************** 3. row ***************************
         Id: 3
       User: root
       Host: localhost
         db: NULL
    Command: Query
       Time: 0
      State: NULL
       Info: show processlist
    3 rows in set (0.00 sec)
    
    ERROR: 
    No query specified
    

    下图列出了从服务器的I/O线程的State列的最常见的状态。该状态也出现在Slave_IO_State列,由SHOW SLAVE STATUS显示。
    image_1cl18ncf619iquo910d18bl10ff2d.png-71.6kB

    下图列出了从服务器的SQL线程的State列的最常见状态
    image_1cl19dnmt144h241n7tq9mdg02q.png-49.7kB

    9.2查看MySQL线程同步状态的用途

    • 通过MySQL线程同步状态可以看到同步是否正常进行,故障的位置是什么,另外还可查看数据库同步是否完成,可用于主库宕机切换数据库或人工数据库主从切换迁移等。
    • 例如:主库宕机,要选择最快的从库将其提升为主库,就需要查看主从库的线程状态,如果主从复制在正常情况下进行角色切换,也需要查看主从库的线程状态,根据复制状态确定更新是否完成。

    10.模拟MySQL从库停止复制故障案例(先在从库创建一个库,然后去主库创建同名的库来模拟数据冲突)

    10.1已知主库内信息数据

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    +--------------------+
    4 rows in set (0.00 sec)
    

    10.2已知从库内信息数据

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    | yangwenbo          |
    +--------------------+
    5 rows in set (0.00 sec)
    

    已知从库比主库多了个小库yangwenbo

    10.3这时主库创建小库yangwenbo

    mysql> create database yangwenbo;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    | yangwenbo          |
    +--------------------+
    5 rows in set (0.01 sec)
    

    10.4这时查看从库信息数据无任何变化

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    | yangwenbo          |
    +--------------------+
    5 rows in set (0.00 sec)
    

    10.5从库查看主从复制状态

    mysql> show slave statusG;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.0.102
                      Master_User: yunjisuan
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000004
              Read_Master_Log_Pos: 291
                   Relay_Log_File: relay-bin.000011
                    Relay_Log_Pos: 301
            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 'yangwenbo'; database exists' on query. Default database: 'yangwenbo'. Query: 'create database yangwenbo'
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 198
                  Relay_Log_Space: 690
                  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 'yangwenbo'; database exists' on query. Default database: 'yangwenbo'. Query: 'create database yangwenbo'
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 1
    1 row in set (0.00 sec)
    
    ERROR: 
    No query specified
    

    10.6对于该冲突,解决办法如下

    方法一:
    (1)直接将从库的yangwenbo小库删掉(当然如果里面有重要数据,可以先进行备份,找时间再恢复)

    mysql> stop slave;                    #首先要停库;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> drop database yangwenbo;       #再者删掉这个小库
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> start slave;                   #开启库
    Query OK, 0 rows affected (0.01 sec)
    

    (2)查看从库主从复制状态

    mysql> show slave statusG;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.0.102
                      Master_User: yunjisuan
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000004
              Read_Master_Log_Pos: 291
                   Relay_Log_File: relay-bin.000012
                    Relay_Log_Pos: 253
            Relay_Master_Log_File: mysql-bin.000004
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                 #中间省略。。。
                 Seconds_Behind_Master: 0    #0表示已经同步状态
                 #以下省略。。。
    

    提示:

    set global sql_slave_skip_counter=n;   #n取值>0,忽略执行N个更新。
    

    (3)查看主库内信息数据

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    | yangwenbo          |
    +--------------------+
    5 rows in set (0.00 sec)
    

    (4)查看主库内信息数据

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    | yangwenbo          |
    +--------------------+
    5 rows in set (0.00 sec)
    

    主从信息数据已恢复已知,证明已成功

    方法二:
    (1)关闭从同步,调动sql_slave指针

    mysql> stop slave;                               #首先要停库;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> set global sql_slave_skip_counter=1;      #将sql线程同步指针向下移动一个,如果多次不同步,可以重复操作
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> start slave;                              #开启库
    Query OK, 0 rows affected (0.00 sec)
    

    (2)查看从库主从复制状态

    mysql> show slave statusG;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.0.102
                      Master_User: yunjisuan
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000004
              Read_Master_Log_Pos: 291
                   Relay_Log_File: relay-bin.000012
                    Relay_Log_Pos: 253
            Relay_Master_Log_File: mysql-bin.000004
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                 #中间省略。。。
                 Seconds_Behind_Master: 0    #0表示已经同步状态
                 #以下省略。。。
    

    提示:

    set global sql_slave_skip_counter=n;   #n取值>0,忽略执行N个更新。
    

    (3)查看主库内信息数据

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    | yangwenbo          |
    +--------------------+
    5 rows in set (0.00 sec)
    

    (4)查看主库内信息数据

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql              |
    | performance_schema |
    | test               |
    | yangwenbo          |
    +--------------------+
    5 rows in set (0.00 sec)
    

    主从信息数据已恢复已知,证明已成功

    10.7其他可能引起复制故障的原因:

    • MySQL自身的原因及人为重复插入数据。
    • 不同的数据库版本会引起不同步,低版本到高版本可以,但是高版本不能往低版本同步。
    • MySQL的运行错误或程序bug
    • binlog记录模式,例如:row level模式就比默认的语句模式要好。

    11.让MySQL从库记录binlog日志的方法

    • 从库需要记录binlog的应用场景:当前的从库还要作为其他从库的主库,例如级联复制或双主互为主从场景的情况下。下面介绍从库记录binlog日志的方法。
    • 在从库的my.cnf中加入如下参数,然后重启服务生效即可。
     54 
     55 log-slave=updates     #必须要有这个参数
     56 log-bin = /data/3307/mysql-bin
     57 expire_logs_days = 7  #相当于find /data/3307/ -type f -name "mysql-bin.000*" -mtime +7 | xargs rm -f
     58 
    
    [root@yangwenbo ~]# /data/3307/mysql restart
    Restarting MySQL...
    Stoping MySQL...
    Starting MySQL....
    

    12. MySQL主从复制集群架构的数据备份策略

    • 有主从复制了,还需要做定时全量加增量备份么?答案是肯定的!
      因为,如果主库有语句级误操作(例如:drop database yunjisuan;),从库也会执行drop database yunjisuan;,这样MySQL主从库就都删除了该数据。
    • 把从库作为数据库备份服务器时,备份策略如下:高并发业务场景备份时,可以选择在一台从库上备份(Slave5),把从库作为数据库备份服务器时需要在从库开启binlog功能,其逻辑图如下所示

    image_1cl1dsu4f1hr0l9913o1knt7iu37.png-153.3kB

    步骤如下:

    • 选择一个不对外提供服务的从库,这样可以确保和主库更新最接近,专门用于做数据备份。
    • 开启从库的binlog功能

    备份时可以选择只停止SQL线程,停止应用SQL语句到数据库,I/O线程保留工作状态,执行命令为stop slave sql_thread;,备份方式可以采取mysqldump逻辑备份或直接物理备份,例如:使用cp,tar(针对/data目录)工具或xtrabackup(第三方的物理备份软件)进行备份,则逻辑备份和物理备份的选择,一般是根据总的备份数据量的多少进行选择的,数据量低于30G,建议选择mysqldump逻辑备份方法,安全稳定,最后把全备和binlog数据发送到备份服务器上留存。

    13.MySQL主从复制延迟问题的原因及解决方案

    13.1主库的从库太多,导致复制延迟

    从库数量以3~5个为宜,要复制的从节点数量过多,会导致复制延迟。

    13.2从库硬件比主库差,导致复制延迟。

    查看Master和Slave的系统配置,可能会因为机器配置不当,包括磁盘I/O,CPU,内存等各方面因素造成复制的延迟。这一般发生在高并发大数据量写入场景中。

    13.3慢SQL语句太多

    假如一条SQL语句执行时间是20秒,那么从执行完毕到从库上能查到数据至少需要20秒,这样就延迟20秒了。
    一般要把SQL语句的优化作为常规工作,不断的进行监控和优化,如果单个SQL的写入时间长,可以修改后分多次写入。通过查看慢查询日志或show full processlist命令,找出执行时间长的查询语句或大的事务。

    13.4主从复制的设计问题

    例如,主从复制单线程,如果主库写并发太大,来不及传送到从库,就会导致延迟。
    更高版本的MySQL可以支持多线程复制,门户网站则会自己开发多线程同步功能。

    13.5主从库之间的网络延迟

    主从库的网卡,网线,连接的交换机等网络设备都可能成为复制的瓶颈,导致复制延迟,另外,跨公网主从复制很容易导致主从复制延迟。

    13.6主库读写压力大,导致复制延迟。

    主库硬件要搞好一点,架构的前端要加buffer及缓存层。

    14.通过read-only参数让从库只读访问

    read-only参数选项可以让从服务器只允许来自从服务器线程或具有SUPER权限的数据库用户进行更新,确保从服务器不接受来自用户端的非法用户更新。

    14.1read-only参数允许数据库更新的条件为:

    • 具有SUPER权限的用户可以更新,不受read-only参数影响,例如:管理员root。
    • 来自从服务器线程可以更新,不受read-only参数影响,例如:前文的yunjisuan用户。
    • 再生产环境中,可以在从库Slave中使用read-only参数,确保从库数据不被非法更新。

    14.2read-only参数的配置方法如下:

    方法一:直接带 --read-only参数启动或重启数据库,

    使用killall mysqld
    mysqladmin -uroot -p123123 -S /data/3307/mysql.sock shutdown mysqld_safe --defaults-file=/data/3307/my.cnf --read-only &

    方法二:在my.cnf里[mysqld]模块下加read-only参数重启数据库,配置如下:

    [mysqld]
    read-only
    

    15.如何实现对主数据库锁表只读

    • 前言:假设某公司有一主库,一从库(主从复制),由于从库1压力过大,公司决定再搭建一个从库2,写出具体流程
    • 答:可以先把主库设为只读状态,打包备份到从库2,使主从数据一致,这时主库再手动解除只读

    15.1对主数据库锁表只读(当前窗口不要关掉)

    mysql> flush table with read lock;      #对主数据库锁表只读
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> create database yunjisuan;       #已无法写入
    ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
    

    在引擎不同的情况下,这个锁表命令的时间会受下面参数的控制。锁表时,如果超过设置时间不操作会自动解锁。

    15.2默认情况下自动解锁的时长参数值如下:

    mysql> show variables like '%timeout%';
    +----------------------------+----------+
    | Variable_name              | Value    |
    +----------------------------+----------+
    | connect_timeout            | 10       |
    | delayed_insert_timeout     | 300      |
    | innodb_lock_wait_timeout   | 120      |
    | innodb_rollback_on_timeout | OFF      |
    | interactive_timeout        | 28800    | #自动解锁时间受本参数影响
    | lock_wait_timeout          | 31536000 |
    | net_read_timeout           | 30       |
    | net_write_timeout          | 60       |
    | slave_net_timeout          | 3600     |
    | wait_timeout               | 28800    | #自动解锁时间受本参数影响
    +----------------------------+----------+
    10 rows in set (0.00 sec)
    

    15.3锁表后查看主库状态。可通过当前binlog日志文件名和二进制binlog日志偏移量来查看

    注意,show master status;命令显示的信息要记录在案,后面的从库导入全备后,继续和主库复制时就是要从这个位置开始。

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

    15.4锁表后,一定要单开一个新的SSH窗口,导出数据库的所有数据,如果数据量很大(50GB以上),并且允许停机,可以停库直接打包数据文件进行迁移,那样更快

    [root@yangwenbo /]# mkdir beifen    #可以临时创建一个目录
    [root@yangwenbo /]# mysqldump -uroot -p971108 -S /data/3306/mysql.sock --events -A -B | gzip >/beifen/mysql_bak.$(date +%F).sql.gz
    #注意:-A表示备份所有库;-B表示增加use DB和 drop 等(导库时会直接覆盖原有的)
    
    [root@localhost ~]# ll /server/backup/mysql_bak.2017-07-21.sql.gz 
    -rw-r--r--. 1 root root 137344 Jul 21 10:17 /server/backup/mysql_bak.2017-07-21.sql.gz
    

    15.5为了确保导出数据期间,数据库没有数据插入,导库完毕可以再次检查主库状态信息,结果如下:

    [root@yangwenbo /]# mysql -uroot -p971108 -S /data/3306/mysql.sock -e "show master status"
    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000004 |      475 |              |                  |
    +------------------+----------+--------------+------------------+
    #提示:若无特殊情况,binlog文件及位置点和锁表后导出数据前是一致的,即没有变化。
    

    15.6解锁

    mysql> unlock tables;
    Query OK, 0 rows affected (0.00 sec)
    #导出数据完毕后,解锁主库,恢复可写,命令如下.因为主库还要对外提供服务,不能一直锁定不让用户访问
    
    

    15.7把主库导出的MySQL备份数据包迁移到从库(使其保证主从一致),再搭建主从复制,就OK了!

  • 相关阅读:
    Zookeeper 集群安装
    Jexus部署.Net Core项目
    NetCore1.1+Linux部署初体验
    Linux初学
    高可用Redis服务架构分析与搭建
    前端开发JS白板编程题目若干
    Javascript中的Microtask和Macrotask——从一道很少有人能答对的题目说起
    ES6原生Promise的所有方法介绍(附一道应用场景题目)
    HTML的iframe标签妙用
    漫谈PHP代码规范
  • 原文地址:https://www.cnblogs.com/ywb123/p/11206352.html
Copyright © 2020-2023  润新知