mysql主从_mysql主从复制 - CSDN
精华内容
参与话题
  • mysql 主从原理以及配置

    千次阅读 2019-10-28 18:51:42
    MySQL主从同步与主主同步 MySQL复制: MySQL内建的复制功能是构建大型,高性能应用程序的基础。将MySQL的数据分布到多个系统上去,这种分布的机制,是通过将mysql的某一台主机的数据复制到其它主机(slave)上,...

    MySQL主从同步与主主同步

    MySQL复制:

         MySQL内建的复制功能是构建大型,高性能应用程序的基础。将MySQL的数据分布到多个系统上去,这种分布的机制,是通过将mysql的某一台主机的数据复制到其它主机(slave)上,并重新执行一遍来实现。

    复制过程中一个服务器充当主服务器,而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件,并维护文件的一个索引以跟踪日志循坏,这些日志可以记录发送到从服务器的更新。当一个从服务器

    连接主服务器时,它通知主服务器从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新,然后封锁并等待主服务器通知的更新。

    需注意的是:

        在进行mysql复制时,所有对复制中的表的更新必须在主服务器上进行。否则必须要小心,以避免用户对主服器上的表进行更新与对从服务器上的表所进行更新之间的冲突。

     

    (1)mysql支持哪些复制

         a.基于语句的复制:在主服务器上执行的sql语句,在从服务器上执行同样的语句。mysql默认采用基于语句的复制,效率边角高。一旦发现没法精确复制时,会自动选着基于行的复制。

         b.基于行的复制:把改变的内容复制过去,而不是把命令在从服务器上执行一遍。从mysql 5.0开始支持

         c.混合类型的复制:默认采用基于语句的复制,一旦发现基于语句的无法精确复制时,就会采用基于行的复制。

     (2)mysql复制解决的问题

         a.数据分布(data distribution)

         b.负载平衡(load balancing)

         c.数据备份(backup),保证数据安全

         d.高可用性与容错行(high availability and failover)

         e.实现读写分离,缓解数据库压力

       (3)mysql主从复制原理

            master服务器将数据的改变记录二进制binlog日志,当master上的数据发生改变时,则将其改变写入二进制日志中;slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变,

       如果发生改变,则开始一个I/O Thread请求master二进制事件,同时主节点为每个I/O线程启动一个dump线程,用于向其发送二进制事件,并保存至从节点本地的中继日志中,从节点将启动SQL线程从中继日志

      中读取二进制日志,在本地重放,使得其数据和主节点的保持一致,最后I/O Thread和SQL Thread将进入睡眠状态,等待下一次被唤醒。

        注意几点:
         1--master将操作语句记录到binlog日志中,然后授予slave远程连接的权限(master一定要开启binlog二进制日志功能;通常为了数据安全考虑,slave也开启binlog功能)。
         2--slave开启两个线程:IO线程和SQL线程。其中:IO线程负责读取master的binlog内容到中继日志relay log里;SQL线程负责从relay log日志里读出binlog内容,并更新到slave的数据库里,这样就能保证slave数据和 master数据保持一致了。
         3--Mysql复制至少需要两个Mysql的服务,当然Mysql服务可以分布在不同的服务器上,也可以在一台服务器上启动多个服务。
         4--Mysql复制最好确保master和slave服务器上的Mysql版本相同(如果不能满足版本一致,那么要保证master主节点的版本低于slave从节点的版本)
         5--master和slave两节点间时间需同步

     

    Mysql复制的流程图如下:

     

    如上图所示:
         Mysql复制过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前,master在二日志记录这些改变。MySQL将事务串行的写入二进制日志,即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后,master通知存储引擎提交事务。
        第二部分就是slave将master的binary log拷贝到它自己的中继日志。首先,slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接,然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件,如果已经跟上master,它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。
        SQL slave thread(SQL从线程)处理该过程的最后一步。SQL线程从中继日志读取事件,并重放其中的事件而更新slave的数据,使其与master中的数据一致。只要该线程与I/O线程保持一致,中继日志通常会位于OS的缓存中,所以中继日志的开销很小。
       此外,在master中也有一个工作线程:和其它MySQL的连接一样,slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的,也就是说master上的并行更新操作不能在slave上并行操作。

     

    (4)mysql复制的模式

         1--主从复制:主库授权从库远程连接,读取binlog日志并更新到本地数据库的过程;主库写数据后,从库会自动同步过来(从库跟着主库变);
         2--主主复制:主从相互授权连接,读取对方binlog日志并更新到本地数据库的过程;只要对方数据改变,自己就跟着改变;

    (5)mysql主从复制优点

         1--在从服务器可以执行查询工作(即我们常说的读功能),降低主服务器压力;(主库写,从库读,降压)
         2--在从主服务器进行备份,避免备份期间影响主服务器服务;(确保数据安全)
         3--当主服务器出现问题时,可以切换到从服务器。(提升性能)

    (6)mysql主从复制工作流程细节

     

         a. MySQL支持单向、异步复制,复制过程中一个服务器充当主服务器,而一个或多个其它服务器充当从服务器。MySQL复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)。因此,要进行复制,必须在主服务器上启用二进制日志。每个从服务器从主服务器接收主服务器上已经记录到其二进制日志的保存的更新。当一个从服务器连接主服务器时,它通知主服务器定位到从服务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新,并在本机上执行相同的更新。然后封锁并等待主服务器通知新的更新。从服务器执行备份不会干扰主服务器,在备份过程中主服务器可以继续处理更新。

         b. MySQL使用3个线程来执行复制功能,其中两个线程(Sql线程和IO线程)在从服务器,另外一个线程(IO线程)在主服务器。
    当发出START SLAVE时,从服务器创建一个I/O线程,以连接主服务器并让它发送记录在其二进制日志中的语句。主服务器创建一个线程将二进制日志中的内容发送到从服务器。该线程可以即为主服务器上SHOW PROCESSLIST的输出中的Binlog Dump线程。从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中,即中继日志。第3个线程是SQL线程,由从服务器创建,用于读取中继日志并执行日志中包含的更新。在从服务器上,读取和执行更新语句被分成两个独立的任务。当从服务器启动时,其I/O线程可以很快地从主服务器索取所有二进制日志内容,即使SQL线程执行更新的远远滞后。

     

     (7)总结:

    主从数据完成同步的过程:

    1)在Slave 服务器上执行sart slave命令开启主从复制开关,开始进行主从复制。

    2)此时,Slave服务器的IO线程会通过在master上已经授权的复制用户权限请求连接master服务器,并请求从执行binlog日志文件的指定位置(日志文件名和位置就是在配置主从

      复制服务时执行change master命令指定的)之后开始发送binlog日志内容

    3)Master服务器接收到来自Slave服务器的IO线程的请求后,其上负责复制的IO线程会根据Slave服务器的IO线程请求的信息分批读取指定binlog日志文件指定位置之后的binlog

      日志信息,然后返回给Slave端的IO线程。返回的信息中除了binlog日志内容外,还有在Master服务器端记录的IO线程。返回的信息中除了binlog中的下一个指定更新位置。

      

    4)当Slave服务器的IO线程获取到Master服务器上IO线程发送的日志内容、日志文件及位置点后,会将binlog日志内容依次写到Slave端自身的Relay Log(即中继日志)文件

     (Mysql-relay-bin.xxx)的最末端,并将新的binlog文件名和位置记录到master-info文件中,以便下一次读取master端新binlog日志时能告诉Master服务器从新binlog

      日志的指定文件及位置开始读取新的binlog日志内容

      

    5)Slave服务器端的SQL线程会实时检测本地Relay Log 中IO线程新增的日志内容,然后及时把Relay LOG 文件中的内容解析成sql语句,并在自身Slave服务器上按解析SQL语句

      的位置顺序执行应用这样sql语句,并在relay-log.info中记录当前应用中继日志的文件名和位置点

      

    主从复制条件

    1)开启Binlog功能

    2)主库要建立账号

    3)从库要配置master.info(CHANGE MASTER to...相当于配置密码文件和Master的相关信息)

    4)start slave 开启复制功能

      

    需要了解的:

    1)3个线程,主库IO,从库IO和SQL及作用

    2)master.info(从库)作用

    3)relay-log 作用

    4)异步复制

    5)binlog作用(如果需要级联需要开启Binlog)

      

    需要注意:

    1)主从复制是异步的逻辑的SQL语句级的复制

    2)复制时,主库有一个I/O线程,从库有两个线程,I/O和SQL线程

    3)实现主从复制的必要条件是主库要开启记录binlog功能

    4)作为复制的所有Mysql节点的server-id都不能相同

    5)binlog文件只记录对数据库有更改的SQL语句(来自主库内容的变更),不记录任何查询(select,show)语句

    ...................................................................................................

    彻底解除主从复制关系

    1)stop slave;

    2)reset slave; 或直接删除master.info和relay-log.info这两个文件;

    3)修改my.cnf删除主从相关配置参数。

     

    让slave不随MySQL自动启动

    修改my.cnf 在[mysqld]中增加 skip-slave-start 选项。

     

    做了MySQL主从复制以后,使用mysqldump对数据备份时,一定要注意按照如下方式:

    mysqldump --master-data --single-transaction --user=username --password=password dbname> dumpfilename

    这样就可以保留 file 和 position 的信息,在新搭建一个slave的时候,还原完数据库, file 和 position 的信息也随之更新,接着再start slave 就可以很迅速

    的完成增量同步!

     

    需要限定同步哪些数据库,有3个思路:

    1)在执行grant授权的时候就限定数据库;

    2)在主服务器上限定binlog_do_db = 数据库名;

    3)主服务器上不限定数据库,在从服务器上限定replicate-do-db = 数据库名;

     

    如果想实现 主-从(主)-从 这样的链条式结构,需要设置:

    log-slave-updates      只有加上它,从前一台机器上同步过来的数据才能同步到下一台机器。

    当然,二进制日志也是必须开启的:

    log-bin=/opt/mysql/binlogs/bin-log

    log-bin-index=/opt/mysql/binlogs/bin-log.index

    还可以设置一个log保存周期:

    expire_logs_days=14

     

    --------------------------下面记录下mysql主从/主主同步环境的实施过程-------------------------

    1.环境描述

    mysql 的安装可以参考本人名下的博客或  http://www.cnblogs.com/kevingrace/p/6109679.html

    centos 7.4

    master:192.168.0.103

    slave: 192.168.0.104

    注意下面几点:

    1)要保证同步服务期间之间的网络联通。即能相互ping通,能使用对方授权信息连接到对方数据库(防火墙开放3306端口)。

    2)关闭selinux。

    3)同步前,双方数据库中需要同步的数据要保持一致。这样,同步环境实现后,再次更新的数据就会如期同步了。

     

    2.主从复制实现过程

    (1)设置master数据库的my.cnf文件(my.cnf 查找顺序 /etc/my.cnf ---> $basedir/my.cnf,在[mysqld]配置区域添加下面内容)

     [root@master ~]# vim /etc/my.cnf

         ..........

        [mysqld] 

     server-id=1        #数据库唯一ID,主从的标识号绝对不能重复。

     log-bin=mysql-bin    #开启bin-log,并指定文件目录和文件名前缀

     binlog-do-db=liting   #需要同步liting数据库。如果是多个同步库,就以此格式另写几行即可。如果不指明对某个具体库同步,就去掉此行,表示同步所有库(除了ignore忽略的库)。

     binlog-ignore-db=mysql  #不同步mysql系统数据库。如果是多个不同步库,就以此格式另写几行;也可以在一行,中间逗号隔开。

     sync_binlog = 1      #确保binlog日志写入后与硬盘同步

     binlog_checksum = none  #跳过现有的采用checksum的事件,mysql5.6.5以后的版本中binlog_checksum=crc32,而低版本都是binlog_checksum=none

     binlog_format = mixed   #bin-log日志文件格式,设置为MIXED可以防止主键重复。

     

    温馨提示:在主服务器上最重要的二进制日志设置是sync_binlog,这使得mysql在每次提交事务的时候把二进制日志的内容同步到磁盘上,即使服务器崩溃也会把事件写入日志中。

    sync_binlog这个参数是对于MySQL系统来说是至关重要的,他不仅影响到Binlog对MySQL所带来的性能损耗,而且还影响到MySQL中数据的完整性。对于"sync_binlog"参数的各种设置的说明如下:

    sync_binlog=0,当事务提交之后,MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘,而让Filesystem自行决定什么时候来做同步,或者cache满了之后才同步到磁盘。

    sync_binlog=n,当每进行n次事务提交之后,MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

         

    在MySQL中系统默认的设置是sync_binlog=0,也就是不做任何强制性的磁盘刷新指令,这时候的性能是最好的,但是风险也是最大的。因为一旦系统Crash,在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候,是最安全但是性能损耗最大的设置。因为当设置为1的时候,即使系统Crash,也最多丢失binlog_cache中未完成的一个事务,对实际数据没有任何实质性影响。

         

    从以往经验和相关测试来看,对于高并发事务的系统来说,“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

     

    (2)导出master数据库多余slave数据库中的数据,然后导入到slave数据库中。保证双方在同步环境实现前的数据一致。[新建环境可忽略次步骤]

      

       导出数据库之前先锁定数据库

       mysql> flush tables with read lock;    #数据库只读锁定命令,防止导出数据库的时候有数据写入。unlock tables命令解除锁定

        

      导出master数据库中需要同步的库(master数据库的root用户登陆密码:123456)

      [root@master ~]#mysqldump -uroot liting -p123456 >/opt/liting.sql

      [root@master ~]#rsync  -e "ssh -p22" -avpgolr /opt/liting.sql 192.168.0.104:/opt/   #将导出的sql文件上传到slave机器上

     

    (3)在master上设置数据同步权限

          mysql> grant replication slave,replication client on *.* to repl@'192.168.0.104' identified by "repl123";  #只允许192.168.0.104使用repl,且密码为"repl123"连接主库做数据同步

      Query OK, 0 rows affected (0.02 sec)                                    #若要所有网段则设置repl@'%' ;部分网段:repl@'192.168.0.%'

      mysql> flush privileges;

      Query OK, 0 rows affected (0.00 sec)

    --------------------------------------------------------------------------------

    温馨提示:

    权限查看方式

    mysql> show grants;

    mysql> show grants for repl@'192.168.0.104';

    --------------------------------------------------------------------------------

    (4)查看主服务器master状态(注意File与Position项,从服务器需要这两项参数)

    mysql> show master status;

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

    | File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

    | mysql-bin.000007 | 120    |  liting   |     mysql   |          |

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

    1 row in set (0.00 sec)

     

     

    下面是slave数据库上的操作:

     

    (1)设置slave数据库的my.cnf配置文件

    [root@master ~]# vim /etc/my.cnf

    .......

    [mysqld]

    server-id=2   #设置从服务器id,必须于主服务器不同

    log-bin=mysql-bin   #启动MySQ二进制日志系统

    replicate-do-db=liting  #需要同步的数据库名。如果不指明同步哪些库,就去掉这行,表示所有库的同步(除了ignore忽略的库)。

    replicate-ignore-db=mysql  #不同步test数据库

    slave-skip-errors = all   #跳过所有的错误,继续执行复制操作

    -----------------------------------------------------------------------------------------------

    温馨提示:

    当只针对某些库的某张表进行同步时,如下,只同步liting库的haha表和test库的heihei表:

    replicate-do-db = liting

    replicate-wild-do-table = liting.haha       //当只同步几个或少数表时,可以这样设置。注意这要跟上面的库指定配合使用;

    replicate-do-db = test

    replicate-wild-do-table = test.heihei      //如果同步的库的表比较多时,就不能这样一一指定了,就把这个选项配置去掉,直接根据指定的库进行同步。

    -----------------------------------------------------------------------------------------------

     

    (2)在slave数据库中导入从master传过来的数据。

    mysql> CREATE DATABASE liting CHARACTER SET utf8 COLLATE utf8_general_ci;   #先创建一个liting空库,否则下面导入数据时会报错说此库不存在。

    mysql> use liting;

    mysql> source /opt/liting.sql;   #导入master中多余的数据。

    .......

     

    (3)配置主从同步指令

    mysql> stop slave;   #执行同步前,要先关闭slave

    mysql> change master to master_host='192.168.0.103',master_user='repl',master_password='repl123',master_log_file='mysql-bin.000007',master_log_pos=120;

        

    mysql> start slave;

    mysql> show slave status \G;

    .......

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

            Slave_IO_State: Waiting for master to send event

             Master_Host: 192.168.0.103

             Master_User: repl

             Master_Port: 3306

            Connect_Retry: 60

           Master_Log_File: mysql-bin.000007

         Read_Master_Log_Pos: 120

           Relay_Log_File: mysql-relay-bin.000002

           Relay_Log_Pos: 279

       Relay_Master_Log_File: mysql-bin.000007

         Slave_IO_Running: Yes

         Slave_SQL_Running: Yes

          Replicate_Do_DB: liting

        Replicate_Ignore_DB: mysql

         .............

       Seconds_Behind_Master: 0

     

    如上,当IO和SQL线程的状态均为Yes,则表示主从已实现同步了!

     

    下面测试下Mysql主从同步的效果

    在master主数据库上写入新数据

    mysql> use liting;    

    mysql>create table if not exists haha (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);

    Query OK, 0 rows affected (0.02 sec)

    mysql> insert into huanqiu.haha values(100,"anhui");

    Query OK, 1 row affected (0.00 sec)

     

    然后在slave数据库上查看,发现master上新写入的数据已经同步过来了

    mysql> select * from liting.haha;

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

    id   | name    |

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

    | 100 | anhui   |

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

    1 rows in set (0.00 sec)

        

    至此,主从同步环境已经实现!

     

    注意:
    Mysql主从环境部署一段时间后,发现主从不同步时,如何进行数据同步至一致?
    有以下两种做法:
    1)参考:mysql主从同步(2)-问题梳理 中的第(4)步的第二种方法
    2)参考:mysql主从同步(3)-percona-toolkit工具(数据一致性监测、延迟监控)使用梳理

     

     

     ********主主复制实现过程*********

     

    根据上面的主从环境部署,master和slave已经实现同步,即在master上写入新数据,自动同步到slave。而从库只能读不能写,一旦从库有写入数据,就会造成主从数据不一致!

    下面就说下Mysql主主复制环境,在slave上更新数据时,master也能自动同步过来。

     ---------------------------------------------------------------------------------------------------------------------------------

    温馨提示:

    在做主主同步前,提醒下需要特别注意的一个问题:

    主主复制和主从复制有一些区别,因为多主中都可以对服务器有写权限,所以设计到自增长重复问题,例如:

    出现的问题(多主自增长ID重复)

    1)首先在A和B两个库上创建test表结构;

    2)停掉A,在B上对数据表test(存在自增长属性的ID字段)执行插入操作,返回插入ID为1;

    3)然后停掉B,在A上对数据表test(存在自增长属性的ID字段)执行插入操作,返回的插入ID也是1;

    4)然后 同时启动A,B,就会出现主键ID重复

     

    解决方法:

    只要保证两台服务器上的数据库里插入的自增长数据不同就可以了

    如:A插入奇数ID,B插入偶数ID,当然如果服务器多的话,还可以自定义算法,只要不同就可以了

    在下面例子中,在两台主主服务器上加入参数,以实现奇偶插入!

    记住:在做主主同步时需要设置自增长的两个相关配置,如下:

    auto_increment_offset     表示自增长字段从那个数开始,取值范围是1 .. 65535。这个就是序号。如果有n台mysql机器,则从第一台开始分为设1,2...n

    auto_increment_increment    表示自增长字段每次递增的量,其默认值是1,取值范围是1 .. 65535。如果有n台mysql机器,这个值就设置为n。

     

    在主主同步配置时,需要将两台服务器的:

    auto_increment_increment     增长量都配置为2

    auto_increment_offset        分别配置为1和2。这是序号,第一台从1开始,第二台就是2,以此类推.....

    这样才可以避免两台服务器同时做更新时自增长字段的值之间发生冲突。(针对的是有自增长属性的字段)

     

    ------------------------------------------------------------------------------------------------------------------------------------

     

     

    主主同步实现操作过程:

    1)在master上的my.cnf配置:

    [root@master ~]# vim /etc/my.cnf

    server-id = 1        

    log-bin = mysql-bin  

    binlog-ignore-db = mysql,information_schema

    sync_binlog = 1

    binlog_checksum = none

    binlog_format = mixed

    auto-increment-increment = 2    

    auto-increment-offset = 1   

    slave-skip-errors = all     

       

    [root@master ~]# /etc/init.d/mysql restart

    Shutting down MySQL. SUCCESS!

    Starting MySQL.. SUCCESS!

     

    数据同步授权(iptables防火墙开启3306端口,要确保对方机器能使用下面权限连接到本机mysql)

    mysql> grant replication slave,replication client on *.* to repl@'192.168.0.104' identified by "repl123";

    mysql> flush privileges;

     

    最好将库锁住,仅仅允许读,以保证数据一致性;待主主同步环境部署后再解锁;锁住后,就不能往表里写数据,但是重启mysql服务后就会自动解锁!

    mysql> FLUSH TABLES WITH READ LOCK;    //注意该参数设置后,如果自己同步对方数据,同步前一定要记得先解锁!

    Query OK, 0 rows affected (0.00 sec)

     

    mysql> show master status;

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

    | File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

    | mysql-bin.000001 |   158 |        |         |          |

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

    1 row in set (0.00 sec)

     

    2)slave数据库上

    [root@slave ~]# vim /etc/my.cnf

    server-id = 2       

    log-bin = mysql-bin  

    binlog-ignore-db = mysql,information_schema

    sync_binlog = 1

    binlog_checksum = none

    binlog_format = mixed

    auto-increment-increment = 2    

    auto-increment-offset = 2   

    slave-skip-errors = all

     

    [root@slave ~]# /etc/init.d/mysql restart

    Shutting down MySQL. SUCCESS!

    Starting MySQL.. SUCCESS!

     

    数据同步授权(iptables防火墙开启3306端口,要确保对方机器能使用下面权限连接到本机mysql)

    同理,slave也要授权给master机器远程同步数据的权限

    mysql> grant replication slave ,replication client on *.* to repl@'192.168.0.103' identified by "repl123";  

    mysql> flush privileges;

     

    mysql> FLUSH TABLES WITH READ LOCK;

    mysql> show master status;

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

    | File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

    | mysql-bin.000001 |   256 |       |          |          |

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

    1 row in set (0.00 sec)

     

    3)执行主张同步操作

    先在slave数据库上做同步master的设置。(确保slave上要同步的数据,提前在master上存在。最好双方数据保持一致)

    mysql> unlock tables;     //先解锁,将对方数据同步到自己的数据库中

    mysql> slave stop;

    mysql> change master to master_host='192.168.0.103',master_user='repl',master_password='repl123',master_log_file='master-bin.000001',master_log_pos=158;

     

    mysql> start slave;

    mysql> show slave status \G;

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

                 Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.0.103

                  Master_User: repl

                  Master_Port: 3306

                 Connect_Retry: 60

                Master_Log_File: mysql-bin.000001

              Read_Master_Log_Pos: 158

               nelay_Log_File: mysql-relay-bin.000003

                 Relay_Log_Pos: 750

            Relay_Master_Log_File: mysql-bin.000001

                Slave_IO_Running: Yes

                Slave_SQL_Running: Yes

                ..................

     

    这样就实现了slave->master的同步环境。

     

    再在master数据库上做同步slave的设置。(确保slave上要同步的数据,提前在master上存在。最好双方数据保持一致)

    mysql> unlock tables;

    mysql> slave stop;

    mysql> change master to master_host='192.168.0.104',master_user='repl',master_password='repl123',master_log_file='master-bin.000001',master_log_pos=256;

     

    mysql> start slave;

    mysql> show slave status \G;

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

               Slave_IO_State: Waiting for master to send event

                 Master_Host: 192.168.0.103

                 Master_User: repl

                 Master_Port: 3306

                Connect_Retry: 60

               Master_Log_File: mysql-bin.000001

              Read_Master_Log_Pos: 256

               Relay_Log_File: mysql-relay-bin.000003

               Relay_Log_Pos: 750

            Relay_Master_Log_File: mysql-bin.000001

                Slave_IO_Running: Yes

                Slave_SQL_Running: Yes

                ..................

     

    这样就实现了master->slave的同步环境。至此,主主双向同步环境已经实现!

     

     

    (4)最后测试下Mysql主主同步的效果

    在master上写入新数据

    mysql> select * from liting.haha;

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

    id   | name    |

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

    | 100 | anhui   |

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

    1 rows in set (0.00 sec)

     

    mysql> insert into huanqiu.haha values(10,"beijing");

     

     

    在slave数据库中查看,发现master新写入的数据已经同步过来了

    mysql> select * from liting.haha;

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

    id  | name    |

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

    |  10| beijing  |

    | 100 | anhui   |

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

    2 rows in set (0.00 sec)

     

    在slave上删除数据

    mysql> delete from liting.haha where id=100;

     

    在master数据库中查看

    mysql> select * from liting.haha;

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

    id  | name    |

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

    |  10 | beijing  |

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

    3 rows in set (0.00 sec)

     

    以上,主主同步实现

    展开全文
  • MySQL主从复制半小时学会

    千人学习 2019-03-11 10:14:22
    学习MySQL在Linux中如何安装,怎样配置MySQL主从读写分离, 对于数据库学习者,是一门很好的课,能让你快速掌握MySQL的读写分离配置
  • MySQL主备、主从、读写分离详解

    万次阅读 多人点赞 2020-02-28 16:58:17
    一、MySQL主备的基本原理 在状态1中,客户端的读写都直接访问节点A,而节点B是A的备库,只是将A的更新都同步过来,到本地执行。这样可以保持节点B和A的数据是相同的。当需要切换的时候,就切成状态2。这时候客户端...

    一、MySQL主备的基本原理

    在这里插入图片描述
    在状态1中,客户端的读写都直接访问节点A,而节点B是A的备库,只是将A的更新都同步过来,到本地执行。这样可以保持节点B和A的数据是相同的。当需要切换的时候,就切成状态2。这时候客户端读写访问的都是节点B,而节点A是B的备库

    在状态1中,虽然节点B没有被直接访问,但是建议把备库节点B,设置成只读模式。有以下几个原因:

    1.有时候一些运营类的查询语句会被放到备库上去查,设置为只读可以防止误操作

    2.防止切换逻辑有bug

    3.可以用readonly状态,来判断节点的角色

    把备库设置成只读,还怎么跟主库保持同步更新?

    readonly设置对超级权限用户是无效的,而用于同步更新的线程,就拥有超级权限

    下图是一个update语句在节点A执行,然后同步到节点B的完整流程图:
    在这里插入图片描述
    备库B和主库A之间维持了一个长连接。主库A内部有一个线程,专门用于服务备库B的这个长连接。一个事务日志同步的完整过程如下:

    1.在备库B上通过change master命令,设置主库A的IP、端口、用户名、密码,以及要从哪个位置开始请求binlog,这个位置包含文件名和日志偏移量

    2.在备库B上执行start slave命令,这时备库会启动两个线程,就是图中的io_thread和sql_thread。其中io_thread负责与主库建立连接

    3.主库A校验完用户名、密码后,开始按照备库B传过来的位置,从本地读取binlog,发给B

    4.备库B拿到binlog后,写到本地文件,称为中转日志

    5.sql_thread读取中转日志,解析出日志里的命令,并执行

    由于多线程复制方案的引入,sql_thread演化成了多个线程

    二、循环复制问题

    双M结构:
    在这里插入图片描述
    节点A和节点B互为主备关系。这样在切换的时候就不用再修改主备关系

    双M结构有一个问题要解决,业务逻辑在节点A上更新了一条语句,然后再把生成的binlog发给节点B,节点B执行完这条更新语句后也会生成binlog。那么,如果节点A同时是节点B的备库,相当于又把节点B新生成的binlog拿过来执行了一次,然后节点A和B间,会不断地循环执行这个更新语句,也就是循环复制

    MySQL在binlog中记录了这个命令第一次执行时所在实例的server id。因此,可以用下面的逻辑,来解决两个节点间的循环复制问题:

    1.规定两个库的server id必须不同,如果相同,则它们之间不能设定为主备关系

    2.一个备库接到binlog并在重放的过程中,生成与原binlog的server id相同的新的binlog

    3.每个库在收到从自己的主库发过来的日志后,先判断server id,如果跟自己的相同,表示这个日志是自己生成的,就直接丢弃这个日志

    双M结构日志的执行流如下:

    1.从节点A更新的事务,binlog里面记的都是A的server id

    2.传到节点B执行一次以后,节点B生成的binlog的server id也是A的server id

    3.再传回给节点A,A判断这个server id与自己的相同,就不会再处理这个日志。所以,死循环在这里就断掉了

    三、主备延迟

    在这里插入图片描述

    1、什么是主备延迟?

    与数据同步有关的时间点主要包括以下三个:

    1.主库A执行完成一个事务,写入binlog,这个时刻记为T1

    2.之后传给备库B,备库B接收完这个binlog的时刻记为T2

    3.备库B执行完这个事务,把这个时刻记为T3

    所谓主备延迟,就是同一个事务,在备库执行完成的时间和主库执行完成的时间之间的差值,也就是T3-T1

    可以在备库上执行show slave status命令,它的返回结果里面会显示seconds_behind_master,用于表示当前备库延迟了多少秒

    seconds_behind_master的计算方法是这样的:

    1.每个事务的binlog里面都有一个时间字段,用于记录主库上写入的时间

    2.备库取出当前正在执行的事务的时间字段的值,计算它与当前系统时间的差值,得到seconds_behind_master

    如果主备库机器的系统时间设置不一致,不会导致主备延迟的值不准。备库连接到主库的时候,会通过SELECTUNIX_TIMESTAMP()函数来获得当前主库的系统时间。如果这时候发现主库的系统时间与自己不一致,备库在执行seconds_behind_master计算的时候会自动扣掉这个差值

    网络正常情况下,主备延迟的主要来源是备库接收完binlog和执行完这个事务之间的时间差

    主备延迟最直接的表现是,备库消费中转日志的速度,比主库生产binlog的速度要慢

    2、主备延迟的原来

    1.有些部署条件下,备库所在机器的性能要比主库所在的机器性能差

    2.备库的压力大。主库提供写能力,备库提供一些读能力。忽略了备库的压力控制,导致备库上的查询耗费了大量的CPU资源,影响了同步速度,造成主备延迟

    可以做以下处理:

    • 一主多从。除了备库外,可以多接几个从库,让这些从库来分担读的压力
    • 通过binlog输出到外部系统,比如Hadoop这类系统,让外部系统提供统计类查询的能力

    3.大事务。因为主库上必须等事务执行完才会写入binlog,再传给备库。所以,如果一个主库上的语句执行10分钟,那这个事务很可能会导致从库延迟10分钟

    典型的大事务场景:一次性地用delete语句删除太多数据和大表的DDL

    四、主备切换策略

    1、可靠性优先策略

    双M结构下,从状态1到状态2切换的详细过程如下:

    1.判断备库B现在的seconds_behind_master,如果小于某个值继续下一步,否则持续重试这一步

    2.把主库A改成只读状态,即把readonly设置为true

    3.判断备库B的seconds_behind_master的值,直到这个值变成0为止

    4.把备库B改成可读写状态,也就是把readonly设置为false

    5.把业务请求切到备库B
    在这里插入图片描述
    这个切换流程中是有不可用的时间的。在步骤2之后,主库A和备库B都处于readonly状态,也就是说这时系统处于不可写状态,直到步骤5完成后才能恢复。在这个不可用状态中,比较耗时的是步骤3,可能需要耗费好几秒的时间。也是为什么需要在步骤1先做判断,确保seconds_behind_master的值足够小

    系统的不可用时间是由这个数据可靠性优先的策略决定的

    2、可用性优先策略

    可用性优先策略:如果强行把可靠性优先策略的步骤4、5调整到最开始执行,也就是说不等主备数据同步,直接把连接切到备库B,并且让备库B可以读写,那么系统几乎没有不可用时间。这个切换流程的代价,就是可能出现数据不一致的情况

    mysql> CREATE TABLE `t` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `c` int(11) unsigned DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB;
    
    insert into t(c) values(1),(2),(3);
    

    表t定义了一个自增主键id,初始化数据后,主库和备库上都是3行数据。继续在表t上执行两条插入语句的命令,依次是:

    insert into t(c) values(4);
    insert into t(c) values(5);
    

    假设,现在主库上其他的数据表有大量的更新,导致主备延迟达到5秒。在插入一条c=4的语句后,发起了主备切换

    下图是可用性优先策略,且binlog_format=mixed时的切换流程和数据结果
    在这里插入图片描述
    1.步骤2中,主库A执行完insert语句,插入了一行数据(4,4),之后开始进行主备切换

    2.步骤3中,由于主备之间有5秒的延迟,所以备库B还没来得及应用插入c=4这个中转日志,就开始接收客户端插入c=5的命令

    3.步骤4中,备库B插入了一行数据(4,5),并且把这个binlog发给主库A

    4.步骤5中,备库B执行插入c=4这个中转日志,插入了一行数据(5,4)。而直接在备库B执行的插入c=5这个语句,传到主库A,就插入了一行新数据(5,5)

    最后的结果就是,主库A和备库B上出现了两行不一致的数据

    可用性优先策略,设置binlog_format=row
    在这里插入图片描述
    因此row格式在记录binlog的时候,会记录新插入的行的所有字段值,所以最后只会有一行不一致。而且,两边的主备同步的应用线程会报错duplicate key error并停止。也就是说,这种情况下,备库B的(5,4)和主库A的(5,5)这两行数据都不会被对方执行

    3、小结

    1.使用row格式的binlog时,数据不一致问题更容易被发现。而使用mixed或者statement格式的binlog时,可能过了很久才发现数据不一致的问题

    2.主备切换的可用性优先策略会导致数据不一致。因此,大多数情况下,建议采用可靠性优先策略

    五、MySQL的并行复制策略

    在这里插入图片描述
    主备的并行复制能力,要关注的就是上图中黑色的两个箭头。一个代表客户端写入主库,另一个代表备库上sql_thread执行中转日志

    在MySQL5.6版本之前,MySQL只支持单线程复制,由此在主库并发高、TPS高时就会出现严重的主备延迟问题

    多线程复制机制都是把只有一个线程的sql_thread拆成多个线程,都符合下面这个模型:
    在这里插入图片描述
    coordinator就是原来的sql_thread,不过现在它不再直接更新数据了,只负责读取中转日志和分发事务。真正更新日志的,变成了worker线程。而worker线程的个数就是由参数slave_parallel_workers决定的

    coordinator在分发的时候,需要满足以下两个基本要求:

    • 不能造成更新覆盖。这就要求更新同一行的两个事务,必须被分发到同一个worker中
    • 同一个事务不能被拆开,必须放到同一个worker中

    1、MySQL5.6版本的并行复制策略

    MySQL5.6版本支持了并行复制,只是支持的粒度是按库并行。用于决定分发策略的hash表里,key是数据库名

    这个策略的并行效果取决于压力模型。如果在主库上有多个DB,并且各个DB的压力均衡,使用这个策略的效果会很好

    这个策略的两个优势:

    • 构造hash值的时候很快,只需要库名
    • 不要求binlog的格式,因为statement格式的binlog也可以很容易拿到库名

    可以创建不同的DB,把相同热度的表均匀分到这些不同的DB中,强行使用这个策略

    2、MariaDB的并行复制策略

    redo log组提交优化,而MariaDB的并行复制策略利用的就是这个特性:

    • 能够在同一个组里提交的事务,一定不会修改同一行
    • 主库上可以并行执行的事务,备库上也一定是可以并行执行的

    在实现上,MariaDB是这么做的:

    1.在一组里面一起提交的事务,有一个相同的commit_id,下一组就是commit_id+1

    2.commit_id直接写到binlog里面

    3.传到备库应用的时候,相同commit_id的事务分发到多个worker执行

    4.这一组全部执行完成后,coordinator再去取下一批

    下图中假设三组事务在主库的执行情况,trx1、trx2和trx3提交的时候,trx4、trx5和trx6是在执行的。这样,在第一组事务提交完成的时候,下一组事务很快就会进入commit状态
    在这里插入图片描述
    按照MariaDB的并行复制策略,备库上的执行效果如下图:
    在这里插入图片描述
    在备库上执行的时候,要等第一组事务完全执行完成后,第二组事务才能开始执行,这样系统的吞吐量就不够

    另外,这个方案容易被大事务拖后腿。假设trx2是一个超大事务,那么在备库应用的时候,trx1和trx3执行完成后,下一组才能开始执行。只有一个worker线程在工作,是对资源的浪费

    3、MySQL5.7版本的并行复制策略

    MySQL5.7版本由参数slave-parallel-type来控制并行复制策略:

    • 配置为DATABASE,表示使用MySQL5.6版本的按库并行策略
    • 配置为LOGICAL_CLOCK,表示的就是类似MariaDB的策略。MySQL在此基础上做了优化

    同时处于执行状态的所有事务,是不是可以并行?

    不可以,因为这里面可能有由于锁冲突而处于锁等待状态的事务。如果这些事务在备库上被分配到不同的worker,就会出现备库跟主库不一致的情况

    而MariaDB这个策略的核心是所有处于commit状态的事务可以并行。事务处于commit状态表示已经通过了锁冲突的检验了
    在这里插入图片描述
    其实只要能够达到redo log prepare阶段就表示事务已经通过锁冲突的检验了

    因此,MySQL5.7并行复制策略的思想是:

    1.同时处于prepare状态的事务,在备库执行时是可以并行的

    2.处于prepare状态的事务,与处于commit状态的事务之间,在备库执行时也是可以并行的

    binlog组提交的时候有两个参数:

    • binlog_group_commit_sync_delay参数表示延迟多少微妙后才调用fsync
    • binlog_group_commit_sync_no_delay_count参数表示基类多少次以后才调用fsync

    这两个参数是用于故意拉长binlog从write到fsync的时间,以此减少binlog的写盘次数。在MySQL5.7的并行复制策略里,它们可以用来制造更多的同时处于prepare阶段的事务。这样就增加了备库复制的并行度。也就是说,这两个参数既可以故意让主库提交得慢些,又可以让备库执行得快些

    4、MySQL5.7.22的并行复制策略

    MySQL5.7.22增加了一个新的并行复制策略,基于WRITESET的并行复制,新增了一个参数binlog-transaction-dependency-tracking用来控制是否启用这个新策略。这个参数的可选值有以下三种:

    • COMMIT_ORDER,根据同时进入prepare和commit来判断是否可以并行的策略
    • WRITESET,表示的是对于事务涉及更新的每一行,计算出这一行的hash值,组成集合writeset。如果两个事务没有操作相同的行,也就是说它们的writeset没有交集,就可以并行
    • WRITESET_SESSION,是在WRITESET的基础上多了一个约束,即在主库上同一个线程先后执行的两个事务,在备库执行的时候,要保证相同的先后顺序

    为了唯一标识,hash值是通过库名+表名+索引名+值计算出来的。如果一个表上除了有主键索引外,还有其他唯一索引,那么对于每个唯一索引,insert语句对应的writeset就要多增加一个hash值

    1.writeset是在主库生成后直接写入到binlog里面的,这样在备库执行的时候不需要解析binlog内容

    2.不需要把整个事务的binlog都扫一遍才能决定分发到哪个worker,更省内存

    3.由于备库的分发策略不依赖于binlog内容,索引binlog是statement格式也是可以的

    对于表上没主键和外键约束的场景,WRITESET策略也是没法并行的,会暂时退化为单线程模型

    六、主库出问题了,从库怎么办?

    下图是一个基本的一主多从结构
    在这里插入图片描述
    图中,虚线箭头表示的是主备关系,也就是A和A’互为主备,从库B、C、D指向的是主库A。一主多从的设置,一般用于读写分离,主库负责所有的写入和一部分读,其他的读请求则由从库分担
    在这里插入图片描述
    一主多从结构在切换完成后,A’会成为新的主库,从库B、C、D也要改接到A’

    1、基于位点的主备切换

    当我们把节点B设置成节点A’的从库的时候,需要执行一条change master命令:

    CHANGE MASTER TO 
    MASTER_HOST=$host_name 
    MASTER_PORT=$port 
    MASTER_USER=$user_name 
    MASTER_PASSWORD=$password 
    MASTER_LOG_FILE=$master_log_name 
    MASTER_LOG_POS=$master_log_pos  
    
    • MASTER_HOST、MASTER_PORT、MASTER_USER和MASTER_PASSWORD四个参数,分别代表了主库A’的IP、端口、用户名和密码
    • 最后两个参数MASTER_LOG_FILE和MASTER_LOG_POS表示,要从主库的master_log_name文件的master_log_pos这个位置的日志继续同步。而这个位置就是所说的同步位点,也就是主库对应的文件名和日志偏移量

    找同步位点很难精确取到,只能取一个大概位置。一种去同步位点的方法是这样的:

    1.等待新主库A’把中转日志全部同步完成

    2.在A’上执行show master status命令,得到当前A’上最新的File和Position

    3.取原主库A故障的时刻T

    4.用mysqlbinlog工具解析A’的File,得到T时刻的位点,这个值就可以作为$master_log_pos

    这个值并不精确,有这么一种情况,假设在T这个时刻,主库A已经执行完成了一个insert语句插入了一行数据R,并且已经将binlog传给了A’和B,然后在传完的瞬间主库A的主机就掉电了。那么,这时候系统的状态是这样的:

    1.在从库B上,由于同步了binlog,R这一行已经存在

    2.在新主库A’上,R这一行也已经存在,日志是写在master_log_pos这个位置之后的

    3.在从库B上执行change master命令,指向A’的File文件的master_log_pos位置,就会把插入R这一行数据的binlog又同步到从库B去执行,造成主键冲突,然后停止tongue

    通常情况下,切换任务的时候,要先主动跳过这些错误,有两种常用的方法

    一种是,主动跳过一个事务

    set global sql_slave_skip_counter=1;
    start slave;
    

    另一种方式是,通过设置slave_skip_errors参数,直接设置跳过指定的错误。这个背景是,我们很清楚在主备切换过程中,直接跳过这些错误是无损的,所以才可以设置slave_skip_errors参数。等到主备间的同步关系建立完成,并稳定执行一段时间之后,还需要把这个参数设置为空,以免之后真的出现了主从数据不一致,也跳过了

    2、GTID

    MySQL5.6引入了GTID,是一个全局事务ID,是一个事务提交的时候生成的,是这个事务的唯一标识。它的格式是:

    GTID=source_id:transaction_id
    
    • source_id是一个实例第一次启动时自动生成的,是一个全局唯一的值
    • transaction_id是一个整数,初始值是1,每次提交事务的时候分配给这个事务,并加1

    GTID模式的启动只需要在启动一个MySQL实例的时候,加上参数gtid_mode=on和enforce_gtid_consistency=on就可以了

    在GTID模式下,每个事务都会跟一个GTID一一对应。这个GTID有两种生成方式,而使用哪种方式取决于session变量gtid_next的值

    1.如果gtid_next=automatic,代表使用默认值。这时,MySQL就把GTID分配给这个事务。记录binlog的时候,先记录一行SET@@SESSION.GTID_NEXT=‘GTID’。把这个GTID加入本实例的GTID集合

    2.如果gtid_next是一个指定的GTID的值,比如通过set gtid_next=‘current_gtid’,那么就有两种可能:

    • 如果current_gtid已经存在于实例的GTID集合中,接下里执行的这个事务会直接被系统忽略
    • 如果current_gtid没有存在于实例的GTID集合中,就将这个current_gtid分配给接下来要执行的事务,也就是说系统不需要给这个事务生成新的GTID,因此transaction_id也不需要加1

    一个current_gtid只能给一个事务使用。这个事务提交后,如果要执行下一个事务,就要执行set命令,把gtid_next设置成另外一个gtid或者automatic

    这样每个MySQL实例都维护了一个GTID集合,用来对应这个实例执行过的所有事务

    3、基于GTID的主备切换

    在GTID模式下,备库B要设置为新主库A’的从库的语法如下:

    CHANGE MASTER TO 
    MASTER_HOST=$host_name 
    MASTER_PORT=$port 
    MASTER_USER=$user_name 
    MASTER_PASSWORD=$password 
    master_auto_position=1 
    

    其中master_auto_position=1就表示这个主备关系使用的是GTID协议

    实例A’的GTID集合记为set_a,实例B的GTID集合记为set_b。我们在实例B上执行start slave命令,取binlog的逻辑是这样的:

    1.实例B指定主库A’,基于主备协议建立连接

    2.实例B把set_b发给主库A’

    3.实例A’算出set_a与set_b的差集,也就是所有存在于set_a,但是不存在于set_b的GTID的集合,判断A’本地是否包含了这个差集需要的所有binlog事务

    • 如果不包含,表示A’已经把实例B需要的binlog给删掉了,直接返回错误
    • 如果确认全部包含,A’从自己的binlog文件里面,找出第一个不在set_b的事务,发给B

    4.之后从这个事务开始,往后读文件,按顺序取binlog发给B去执行

    4、GTID和在线DDL

    如果是由于索引缺失引起的性能问题,可以在线加索引来解决。但是,考虑到要避免新增索引对主库性能造成的影响,可以先在备库加索引,然后再切换,在双M结构下,备库执行的DDL语句也会传给主库,为了避免传回后对主库造成影响,要通过set sql_log_bin=off关掉binlog,但是操作可能会导致数据和日志不一致

    两个互为主备关系的库实例X和实例Y,且当前主库是X,并且都打开了GTID模式。这时的主备切换流程可以变成下面这样:

    • 在实例X上执行stop slave
    • 在实例Y上执行DDL语句。这里不需要关闭binlog
    • 执行完成后,查出这个DDL语句对应的GTID,记为source_id_of_Y:transaction_id
    • 到实例X上执行一下语句序列:
    set GTID_NEXT="source_id_of_Y:transaction_id";
    begin;
    commit;
    set gtid_next=automatic;
    start slave;
    

    这样做的目的在于,既可以让实例Y的更新有binlog记录,同时也可以确保不会在实例X上执行这条更新

    七、MySQL读写分离

    读写分离的基本结构如下图:
    在这里插入图片描述
    读写分离的主要目的就是分摊主库的压力。上图中的结构是客户端主动做负载均衡,这种模式下一般会把数据库的连接信息放在客户端的连接层。由客户端来选择后端数据库进行查询

    还有一种架构就是在MySQL和客户端之间有一个中间代理层proxy,客户端只连接proxy,由proxy根据请求类型和上下文决定请求的分发路由
    在这里插入图片描述
    1.客户端直连方案,因此少了一层proxy转发,所以查询性能稍微好一点,并且整体架构简单,排查问题更方便。但是这种方案,由于要了解后端部署细节,所以在出现主备切换、库迁移等操作的时候,客户端都会感知到,并且需要调整数据库连接信息。一般采用这样的架构,一定会伴随一个负责管理后端的组件,比如Zookeeper,尽量让业务端只专注于业务逻辑开发

    2.带proxy的架构,对客户端比较友好。客户端不需要关注后端细节,连接维护、后端信息维护等工作,都是由proxy完成的。但这样的话,对后端维护团队的要求会更高,而且proxy也需要有高可用架构

    在从库上会读到系统的一个过期状态的现象称为过期读

    1、强制走主库方案

    强制走主库方案其实就是将查询请求做分类。通常情况下,可以分为这么两类:

    1.对于必须要拿到最新结果的请求,强制将其发到主库上

    2.对于可以读到旧数据的请求,才将其发到从库上

    这个方案最大的问题在于,有时候可能会遇到所有查询都不能是过期读的需求,比如一些金融类的业务。这样的话,就需要放弃读写分离,所有读写压力都在主库,等同于放弃了扩展性

    2、Sleep方案

    主库更新后,读从库之前先sleep一下。具体的方案就是,类似于执行一条select sleep(1)命令。这个方案的假设是,大多数情况下主备延迟在1秒之内,做一个sleep可以很大概率拿到最新的数据

    以买家发布商品为例,商品发布后,用Ajax直接把客户端输入的内容作为最新商品显示在页面上,而不是真正地去数据库做查询。这样,卖家就可以通过这个显示,来确认产品已经发布成功了。等到卖家再刷新页面,去查看商品的时候,其实已经过了一段时间,也就达到了sleep的目的,进而也就解决了过期读的问题

    但这个方案并不精确:

    1.如果这个查询请求本来0.5秒就可以在从库上拿到正确结果,也会等1秒

    2.如果延迟超过1秒,还是会出现过期读

    3、判断主备无延迟方案

    show slave status结果里的seconds_behind_master参数的值,可以用来衡量主备延迟时间的长短

    1.第一种确保主备无延迟的方法是,每次从库执行查询请求前,先判断seconds_behind_master是否已经等于0。如果还不等于0,那就必须等到这个参数变为0才能执行查询请求

    show slave status结果的部分截图如下:
    在这里插入图片描述
    2.第二种方法,对比位点确保主备无延迟:

    • Master_Log_File和Read_Master_Log_Pos表示的是读到的主库的最新位点
    • Relay_Master_Log_File和Exec_Master_Log_Pos表示的是备库执行的最新位点

    如果Master_Log_File和Read_Master_Log_Pos和Relay_Master_Log_File和Exec_Master_Log_Pos这两组值完全相同,就表示接收到的日志已经同步完成

    3.第三种方法,对比GTID集合确保主备无延迟:

    • Auto_Position=1表示这堆主备关系使用了GTID协议
    • Retrieved_Gitid_Set是备库收到的所有日志的GTID集合
    • Executed_Gitid_Set是备库所有已经执行完成的GTID集合

    如果这两个集合相同,也表示备库接收到的日志都已经同步完成

    4.一个事务的binlog在主备库之间的状态:

    1)主库执行完成,写入binlog,并反馈给客户端

    2)binlog被从主库发送给备库,备库收到

    3)在备库执行binlog完成

    上面判断主备无延迟的逻辑是备库收到的日志都执行完成了。但是,从binlog在主备之间状态的分析中,有一部分日志,处于客户端已经收到提交确认,而备库还没收到日志的状态
    在这里插入图片描述
    这时,主库上执行完成了三个事务trx1、trx2和trx3,其中:

    • trx1和trx2已经传到从库,并且已经执行完成了
    • trx3在主库执行完成,并且已经回复给客户端,但是还没有传到从库中

    如果这时候在从库B上执行查询请求,按照上面的逻辑,从库认为已经没有同步延迟,但还是查不到trx3的

    4、配合semi-sync

    要解决上面的问题,就要引入半同步复制。semi-sync做了这样的设计:

    1.事务提交的时候,主库把binlog发送给从库

    2.从库收到binlog以后,发回给主库一个ack,表示收到了

    3.主库收到这个ack以后,才能给客户端返回事务完成的确认

    如果启用了semi-sync,就表示所有给客户端发送过确认的事务,都确保了备库已经收到了这个日志

    semi-sync+位点判断的方案,只对一主一备的场景是成立的。在一主多从场景中,主库只要等到一个从库的ack,就开始给客户端返回确认。这时,在从库上执行查询请求,就有两种情况:

    1.如果查询是落在这个响应了ack的从库上,是能够确保读到最新数据

    2.但如果查询落到其他从库上,它们可能还没有收到最新的日志,就会产生过期读的问题

    判断同步位点的方案还有另外一个潜在的问题,即:如果在业务更新的高峰期,主库的位点或者GTID集合更新很快,那么上面的两个位点等值判断就会一直不成立,很有可能出现从库上迟迟无法响应查询请求的情况
    在这里插入图片描述
    上图从状态1到状态4,一直处于延迟一个事务的状态。但是,其实客户端是在发完trx1更新后发起的select语句,我们只需要确保trx1已经执行完成就可以执行select语句了。也就是说,如果在状态3执行查询请求,得到的就是预期结果了

    semi-sync配合主备无延迟的方案,存在两个问题:

    1.一主多从的时候,在某些从库执行查询请求会存在过期读的现象

    2.在持续延迟的情况下,可能出现过度等待的问题

    5、等主库位点方案

    select master_pos_wait(file, pos[, timeout]);
    

    这条命令的逻辑如下:

    1.它是在从库执行的

    2.参数file和pos指的是主库上的文件名和位置

    3.timeout可选,设置为正整数N表示这个函数最多等待N秒

    这个命令正常返回的结果是一个正整数M,表示从命令开始执行,到应用完file和pos表示的binlog位置,执行了多少事务

    1.如果执行期间,备库同步线程发生异常,则返回NULL

    2.如果等待超过N秒,就返回-1

    3.如果刚开始执行的时候,就发现已经执行过这个位置了,则返回0
    在这里插入图片描述
    对于上图中先执行trx1,再执行一个查询请求的逻辑,要保证能够查到正确的数据,可以使用这个逻辑:

    1.trx1事务更新完成后,马上执行show master status得到当前主库执行到的File和Position

    2.选定一个从库执行查询语句

    3.在从库上执行select master_pos_wait(file, pos, 1)

    4.如果返回值是>=0的正整数,则在这个从库执行查询语句

    5.否则,到主库执行查询语句

    流程如下:
    在这里插入图片描述

    6、GTID方案

     select wait_for_executed_gtid_set(gtid_set, 1);
    

    这条命令的逻辑如下:

    1.等待,直到这个库执行的事务中包含传入的gtid_set,返回0

    2.超时返回1

    等主库位点方案中,执行完事务后,还要主动去主库执行show master status。而MySQL5.7.6版本开始,允许在执行完更新类事务后,把这个事务的GTID返回给客户端,这样等GTID的方案可以减少一次查询

    等GTID的流程如下:

    1.trx1事务更新完成后,从返回包直接获取这个事务的GTID,记为gtid1

    2.选定一个从库执行查询语句

    3.在从库上执行 select wait_for_executed_gtid_set(gtid1, 1);

    4.如果返回值是0,则在这个从库执行查询语句

    5.否则,到主库执行查询语句
    在这里插入图片描述

    展开全文
  • MySQL 主从 个人理解

    千次阅读 2018-11-19 08:09:15
    mysql 主从这里指的是: mysql的主服务器和从服务器 为什么要有主从之分,mysql服务器是存放重要的数据的位置,用一台数据库来存放数据,若此数据库宕机了导致数据丢失了,严重的会直接影响业务,用户无法访问等...

    MySQL主 从 简介

    mysql 主从这里指的是: mysql的主服务器和从服务器
    为什么要有主从之分,mysql服务器是存放重要的数据的位置,用一台数据库来存放数据,若此数据库宕机了导致数据丢失了,严重的会直接影响业务,用户无法访问等问题,造成灾难性的后果,这之类问题都是安全隐患
    还有个问题,业务量大了,访问量增大了、产生的数据多了,一台服务器读取的速度就会受到影响、这样的问题就需要增加备用的mysql服务器

    主从的作用

    • 实时灾备:一台主数据库宕机了,启用从数据库,用于故障切换
    • 读写分离:主服务器可以只用于写操作,从服务器只用于读取,用于查询服务
    • 备份:这个问题可以很好的解决数据丢失的问题,避免影响业务

    主从的形式

    • 一主多从 表示只有一台主服务器,多台从服务器
    • 主主复制 表示互为主服务器,同时也互为从服务器
    • 一主多从 ----扩展系统读取的性能,因为读是在从库读取的
    • 多主一从 ----5.7开始支持
    • 联级复制

    主从复制原理

    这里写图片描述
    用户将写入的数据保存到mysql主服务器(master)上,主库将所有写的操作记录到binlog 日志中,并且生成一个log dump的线程,将 binlog 日志传给从库的I/O线程,在从服务器(slave)上生成两个线程,一个I/O线程,一个SQL线程,I/O线程去请求主库的binlog,并将得到的binlog 日志写到relay log(中继日志)文件中,然后SQL线程,会读取relay log文件的日志,并解析成具体的操作,来实现主从的操作一致,达到最终数据一致的目的

    主从复制配置的步骤:
    1.确保从数据库与主数据库里的数据一样
    2.在主数据库里创建一个同步账号授权给从数据库使用
    3.配置主数据库(修改配置文件)
    4.配置从数据库(修改配置文件)

    需求:
    搭建两台mysql 服务器,一台为主服务器,一台为从服务器,主服务器进行写操作,从服务器进行读操作
    环境说明:

    数据库角色 IP 应用 版本
    主数据库(master) 192.168.169.20 CentOS7/redhat7 mysql-5.7
    从数据库(slave) 192.168.169.30 CentOS7/redhat7 mysql-5.7

    mysql的安装
    分别在主从上安装mysql-5.7 版本,此处略过安装步骤,可以参考 lnmp 或者 lnmt 里面安装mysql 的步骤

    Mysql 主从的配置
    全备主库,全备主库时需要另开一台终端给数据库加上读锁,避免在备份期间有其他人在写入导致数据不一致

    mysql> flush tables with read lock;   //此表的终端必须备份完了之后才能退出,退出即为解锁
    Query OK, 0 rows affected (0.00 sec)   
    [root@master-server ~]# mysqldump -uroot -pchen --all-databases > /opt/all-20180907.sql
    [root@master-server opt]# ls
    all-20180907.sql  data
    [root@master-server opt]# scp all-20180907.sql root@192.168.169.30:/opt/
    root@192.168.169.30's password: 
    all-20180907.sql                               100%  782KB  12.2MB/s   00:00   
    

    解锁主库的锁定状态,直接退出即可

    mysql> quit
    Bye
    

    在从库上恢复主库的备份,确保与主库一致

    [root@slave-server ~]# mysql -uroot -pchen < /opt/all-20180907.sql 
    mysql: [Warning] Using a password on the command line interface can be insecure.
    

    在主数据库里创建一个同步账号授权给从数据库使用

    mysql> create user 'chen'@'192.168.169.30' identified by 'chen';     //创建一个账号指定在从服务器上登录
    Query OK, 0 rows affected (0.00 sec)
    mysql> grant replication slave on *.* to 'chen'@'192.168.169.30';     //对这个账号做授权处理
    Query OK, 0 rows affected (0.00 sec)
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)
    

    配置主数据库

    [root@master-server ~]# vim /etc/my.cnf
    [mysqld]
    basedir = /usr/local/mysql
    datadir = /opt/data/
    socket = /tmp/mysql.sock
    port = 3306
    pid-file = /tmp/data/mysql.pid
    user = mysql
    skip-name-resolve
    log-bin = mysql-bin    //启用binlog日志
    server-id = 1		//数据库服务器唯一表示符,主库的server-id值必须比从库大
    symbolic-links=0
    log-error=/var/log/mysqld.log
    

    重新启动 mysql 服务

    [root@master-server ~]# service mysqld restart 
    Shutting down MySQL.. SUCCESS! 
    Starting MySQL. SUCCESS! 
    

    查看数据库的状态

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

    配置从数据库

    [mysqld]
    basedir = /usr/local/mysql
    datadir = /opt/data/
    socket = /tmp/mysql.sock
    port = 3306
    pid-file = /tmp/data/mysql.pid
    user = mysql
    skip-name-resolve
    server-id = 2       //设置从库的唯一标识符,从库的server-id值必须小于主库的该值
    relay-log = mysql-relay-bin   //启用中继日志relay-log
    symbolic-links=0
    log-error=/var/log/mysqld.log
    

    重启从库的mysql服务

    [root@slave-server ~]# service mysqld restart 
    Shutting down MySQL.. SUCCESS! 
    Starting MySQL. SUCCESS! 
    

    配置并启动主从复制

    mysql> change master to master_host='192.168.169.20',master_user='chen',master_password='chen',master_log_file='mysql-bin.000001',master_log_pos=154;
    Query OK, 0 rows affected, 2 warnings (0.07 sec)
    mysql> start slave;
    Query OK, 0 rows affected (0.01 sec)
    

    查看从服务器的状态

    mysql> show slave status \G
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 192.168.169.20
                      Master_User: chen
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000001
              Read_Master_Log_Pos: 154
                   Relay_Log_File: mysql-relay-bin.000002
                    Relay_Log_Pos: 320
            Relay_Master_Log_File: mysql-bin.000001
                 Slave_IO_Running: Yes       //从服务器上产生的线程,必须是yes
                Slave_SQL_Running: Yes       //从服务器上产生的线程,必须是yes 
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               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: 154
                  Relay_Log_Space: 527
    .... 以下省略
    

    测试验证
    在主服务器上插入一张新表,并写入数据

    mysql> create database chens;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> use chens;
    Database changed
    
    mysql> create table  cs(id int not null,name varchar(100)not null,age tinyint);
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> insert cs (id,name,age)values(1,'tom',10),(2,'jack',30);
    Query OK, 2 rows affected (0.02 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    mysql> select * from cs;
    +----+------+------+
    | id | name | age  |
    +----+------+------+
    |  1 | tom  |   10 |
    |  2 | jack |   30 |
    +----+------+------+
    2 rows in set (0.00 sec)
    

    在从数据库中查看是否同步

    [root@slave-server ~]# mysql -uroot -p
    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | chens              |
    | mysql              |
    | performance_schema |
    | sys                |
    +--------------------+
    5 rows in set (0.00 sec)
    
    mysql> use chens;
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    mysql> show tables;
    +-----------------+
    | Tables_in_chens |
    +-----------------+
    | cs              |
    +-----------------+
    1 row in set (0.00 sec)
    
    mysql> select * from cs;
    +----+------+------+
    | id | name | age  |
    +----+------+------+
    |  1 | tom  |   10 |
    |  2 | jack |   30 |
    +----+------+------+
    2 rows in set (0.00 sec)
    

    同步成功!

    msyql 的 备份与恢复

    [root@localhost opt]# mysqldump -uchenshuo -pchen wordpress > /opt/wordpress.sql   //备份数据到/opt/ 下面叫wordpress.sql
    [root@localhost opt]# ls
    data  wordpress  wordpress.sql
    

    数据的恢复
    进入到数据库创建一个数据库wordpress

    [root@localhost opt]# mysql -uchenshuo -p  wordpress < /opt/wordpress.sql         
    Enter password: 
    
    展开全文
  • MySQL主从结构搭建

    千次阅读 2018-09-10 09:12:01
    昨天按公司要求搭建了一套MySQL主从结构,趁现在还没有忘记,做个笔记巩固学习;   由于保密协议,本文所涉及的参数都是自己本机的测试参数: 配置环境: 两台新的服务器主机,本次系统是centos7.4; ip1:172...

     

    昨天按公司要求搭建了一套MySQL主从结构,趁现在还没有忘记,做个笔记巩固学习;

     

    由于保密协议,本文所涉及的参数都是自己本机的测试参数:

    配置环境:

    两台新的服务器主机,本次系统是centos7.4;

    ip1:172.168.0.40   ip2:172.168.0.41

    主从的概念不做说明:简单结构是一个主机做MySQL服务的主机,另一个主机来做MySQL服务的从机,从机目的的容灾;服务正常情况都在主机上跑;

     

    简单搭建原理:

    1、两个主机上都安装好MySQL服务

    2、配置主机的MySQL配置文件、配置从机的MySQL配置文件 使得他们相关的配置文件信息能够在链接的时候对接上;

    3、确保主机ip和从机ip在同一网段,即在局域网内;

    4、在从机上执行链接信息命令,让从机主动连接主机,由于配置文件的信息校验通过,主机允许从机访问主机MySQL服务从而实现主从结构;

     

    具体步骤:

    1、分别在两个主机上安装相同版本的MySQL,本次安装版本是MySQL5.7;

    方法一,我用的是本地安装,确保安装包一致;步骤是先把Linux版本的MySQL安装包现在到电脑,通过ftp软件将文件上传到主机中自己新建的/data/package文件夹,执行命令:yum localinstall +安装包名进行安装;两个机器操作方法一样;

    方法二,通过yum安装

    2、两个机器安装完后,启动MySQL服务,设置开机自动重启,查看MySQL状态,命令是:

    启动:systemctl start mysqld
    加入开机启动:systemctl enable mysqld 
                systemctl daemon-reload
    查看状态:systemctl status mysqld 

    查看状态后,status如下图代表在正常运行:

     

    2、进入MySQL修改密码和相关配置:

    MySQL安装完自带root密码,这个root只允许当前本机进行登录;密码是系统自动生成的,放在了日志文件中,我们需要去找到root密码登入MySQL进行相关调整:

    日志存放在:/var/log/mysqld.log   

    查看日志命令:more /var/log/mysqld.log

    截图就是默认的密码,复制密码后通过命令:

    mysql -uroot -p

    进入MySQL

    修改登录密码:

    ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

    我修改为123456,访问root账户还是只用本机访问,因为企业对于数据库链接一般都会有跳板机/堡垒机这种中间件去链接服务器,为了安全,root账户我也只设定到跳板机上才能使用,远程本地机器要用的账户,我们可以再新建一个角色账户并授权所有权限即可;

    具体create user 和grant 授权命令不做介绍,这里我新建了一个admin账户授权all权限;

    以上的步骤两台服务器操作相同;

    接下来开始配置主从信息,这里开始两个机器的差异产生;

    两个机器的配置文件都是放在/etc/my.cnf

    所以编辑配置文件命令都是 :vi /etc/my.cnf

    主机和从机配置字符格式都是utf-8这个是一样的,所有的配置信息也都放在 [mysqld] 以下;

    character_set_server=utf8
    init_connect='SET NAMES utf8'
    这两条是设置utf-8字符格式,两个主机配置相同

    不同点:

    主机(master)添加配置文件信息:

    #要给从机同步的库(如果不写,默认全部同步)
    binlog-do-db=db01
    #不给从机同步的库(多个写多行)
    binlog-ignore-db=mysql
    binlog-ignore-db=information_schema
    binlog-ignore-db=performance_schema
    binlog-ignore-db=performance_schema
    binlog-ignore-db=sys
    #自动清理 7 天前的log文件,可根据需要修改
    expire_logs_days=7
    # 启用二进制日志
    log-bin=master-bin
    # 服务器唯一ID,一般取IP最后一段
    server-id=40
    
    log_bin_index = master-bin.index

    从机添加的配置信息:

    server-id = 41
    #加上以下参数可以避免更新不及时,SLAVE 重启后导致的主从复制出错
    read_only = 1
    master_info_repository=TABLE
    relay_log_info_repository=TABLE
    relay-log = slave-relay-bin
    
    #主服务器I/O日志读取、记录及存放
    relay-log-index = slave-relay-bin.index

    以上就是主从结构搭建的配置,配置完成后保存退出,运行并进入各个主机的MySQL;


    9.4更新:修改完配置文件后需要重启服务才生效,不管是MySQL还是其他Linux应用程序,重启的方式有两种:

    1、进入服务启动文件夹进行启动

    2、service命令:如MySQL的命令是:service mysqld restart


    master服务器查看MySQL状态:

    show master status;

     

    这里查看到的信息即是主服务器准许接入的信息,我们要在从服务器MySQL语句中执行接入语句需要用到主服务器允许接入参数,这里的file和position非常重要;

     

    运行slave服务器MySQL(从服务器):执行接入master服务器MySQL服务语句:

    change master to master_host='主服务器ip地址',
    master_port=3306,master_user='链接主服务器账户',
    master_password='链接主服务器角色账户密码',
    master_log_file='主服务器file参数',
    master_log_pos=主服务器positon参数

    注意以上语句不要断开,我这里隔行是为了方便观看,执行的时候请一行无空格敲完;除了端口和pos其他参数都要有引号;

     

    以上我们就映射完成,并在理论上完成了搭建;

    核实工作:

    slave上运行sql语句:

    show slave status\G;

    结果如下:

    代表从slave运行正常。

     

    测试:

    分别用navicat链接master和slave;在master上创建数据库和表并插入数据最后删除库,每一步观察slave上是否同步,经测试数据库同步无异常,搭建完成:

     

    遇见问题:

    1、修改mater配置文件的时候文件信息改错了导致restart mysql服务报错:

    (code=exited, status=1/FAILURE)

    解决方案:不知道改动了配置文件哪里,后来直接替换了一份配置文件就可以启动成功;

    教训,更改配置文件前最好先备份一下;

     


    9.4更新:

    MySQL服务报错,可以去错误日志里面查看报错原因,大多数情况在错误日志中能看得到,所有相关的日志配置,都可以在配置文件中查看得到;

     


    2、配置好slave后没有成功同步,经查询slave状态:show slave status\G;显示:Slave_SQL_Running: No

    产生原因:

    1、如果mater服务器和slave服务器有数据差异,且相关数据库还有同步服务的时候,mater上一旦操作了差异数据的调整,slave无法找到数据,slave会自动关闭running。

    2、slave上进行了写入操作使得master和slave产生差异;

     

    长期解决方案:还在学习中;

    临时解决方案:

    Slave_SQL_Running: No
    1.程序可能在slave上进行了写操作   2.也可能是slave机器重起后,事务回滚造成的.
    一般是master差异且进行了差异数据执行造成的:
    解决办法:
    mysql> stop slave ;
    mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
    mysql> start slave ;

    反思:是否应该使salve库不允许写入操作?


    9.4更新:

    如果master的允许接入参数file和position进行了变更,则slave上的接入配置信息需要重置,否则会导致数据不同步,且在start slave语句时会报错无法启动slave;

    产生原因:

    接入参数变更

    解决方案:

    重新执行接入语句,步骤必须是

    1、stop slave (停止slave同步)

    2、reset slave (重置slave同步)

    3、执行更新slave参数语句

    change master to
    master_host='',
    master_user='',
    master_password='slave',
    master_port=3306,
    master_log_file='',
    master_log_pos=;

       4、start slave

     

    大多数修改完配置文件后无法启动MySQL服务的原因都是配置文件修改信息不完整,如少打一个字母,误修改其他参数,或者是文件夹MySQL没有权限;

    解决方案:

    1、建议在修改配置文件前先进行备份,或直接修改备份确认后再替换原文件

    2、授权问题,如果我们在变更MySQL默认配置的文件夹,如我想把默认的数据存放文件指定到自己的文件夹,那么在我新建自己的文件夹的时候,只有当前用户或是root账户有该文件夹操作权限,一般作为root账户去新建文件夹时,只有root角色能修改文件夹,MySQL没有权限去读写文件夹时会启动报错,这时候我们要对MySQL进行文件夹权限授权,命令是:

    chown -R MySQL:MySQL +文件夹目录;
    chown -R MYSQL:MYSQL /data/mysql/dir 代表把dir文件夹读写权限给到MySQL,-R是代表文件夹操作

     


    9.5更新:

    问题:MySQL变更文件夹后启动报错:

    ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'

    报错信息告诉我无法链接到MySQL,因为找不到sock文件;额外笔记中有介绍sock文件作用,详情去后文查看;

    这里要说明的一点是,一般我们改配置文件的时候都是改:[mysqld] 下面的信息,里面有socket指定到我更改后的目录但是通过报错发现还是在默认目录去调取sock文件。本身这是个坑的,这里做些讲解避免新人懵逼;

    原因:

    [mysqld]有指定路径,重启服务后不去指定路径找sock,而是去默认路径找sock;

    原理:

    [mysqld] 字段是服务端的信息字段,配置客户端信息字段不是在这下面,客户端信息想要生效,需要配置到 [client] 字段下面;

    解决方案:

    在[client] 字段下面添加socket = /目录/mysql.sock 信息,重启服务

    如果在my.cnf中没有找到[client]字段,直接在[mysqld]字段信息后手工添加一个[client]字段即可

     


    额外笔记,MySQL配置文件参数代表意义:

    以下文件夹路径是我举例路径,z默认安装路径可以自行百度,这里主要描述参数意义。

    [mysqld]
    
    ###以下参数配置都可以按需求修改
    
    #服务名称,不做介绍....
    user=mysql 
    
    #端口,不做介绍.....
    port=3306
    
    #MySQL启动的配置文件,这是不可缺少的文件,跟随数据存放目录,变更数据存放目录也要变更socket目录
    socket=/data/mysql/mysql.sock
    
    #MySQL数据库及数据存放目录,默认是在/var/lib/mysql中,可自行变更
    datadir=/data/mysql
    
    #记录当前MySQL进程的process id目录,启动MySQL时会去检查pid,如果存在且占用状态则会启动报错,如果存#在不占用会删除该pid,没有pid后启动MySQL会新建一个pid记录MySQL进程,主要目的是:数据文件同一份的情#况下不同端口,防止同一个数据库被启动多次
    pid-file=/data/mysql/mysql.pid
    
    
    #MySQL的安装路径,不做多余解释....
    basedir=/data/mysql/dase
    
    
    #MySQL的错误日志存放路径,非常重要,查看错误日志进该文件夹读取日志文件
    log-error=/data/mysql/errorlog
    
    #非常重要的MySQL日志文件,该日志记录除了select以外的操作记录,如update/delete/create/等待,如果要#求回滚数据,通过特定工具读取该日志进行逆向操作进行回滚
    log-bin=/data/mysql/log/mysql-bin
    
    #MySQL服务id,不做过多解释.....
    server-id=40
    
    #MySQL集群时主允许同步的数据库,如果不设置,则默认全部同步
    binlog-do-db=db01,db02.......
    
    #MySQL集群时不允许同步的数据库
    binlog-ignore-db=db01,db02....

    9.10更新:

    slave上写入数据会导致主从关系中断的情况,目前决定把slave机器上的权限改为只读;

    上锁方式:

    --查看全局只读状态是否开打
    show global variables like “%read_only%”; 
    
    --锁定表用户更新,限定普通账户不解锁情况下不能写入数据
    flush tables with read lock; 
    
    --打开全局只读开关
    set global read_only=1;
    
    --再次查看开关是否打开
    show global variables like “%read_only%”;
    
    

     

    如果特殊需求要解锁写入,方法是:

    --解除表锁定
     unlock tables; 
    
    --关闭只读开关
    set global read_only=0;

    注意点:

    1、锁定账户命令只对普通用户生效,如果是超极用户,如root等,不起作用的;

    2、只读不会影响主从同步,所以查看slave同步状态,开关都是打开的,无需担心主从同步会中断;

    展开全文
  • mysql主从复制+主备切换

    万次阅读 2018-01-05 11:17:56
    使用mycat进行主从切换,当一台mysql服务器宕机之后,mycat会切换至另一台进行连接,两台mysql互为主从,这样可以使两台mysql服务器互相备份,使其数据一致。   1. 服务器分配 Mycat 192.168.1.100:8066 ...
  • Mysql集群搭建(多实例、主从

    万次阅读 多人点赞 2018-08-09 08:53:31
    1 MySQL多实例 一 、MySQL多实例介绍 1、什么是MySQL多实例 2、MySQL多实例的特点有以下几点 3、部署mysql多实例的两种方式 4、同一开发环境下安装多个数据库,必须处理以下问题 2 mysql多实例搭建 一、...
  • Mysql实现主从同步

    千次阅读 2018-05-11 11:24:28
    因为线上需要做一个数据库备份,所以本地先测试一下。...修改 my.cnf文件,之后重启mysql 同样的,进入从服务器,配置从服务器的my.cnf, 注意更换server-id即可。 ps:修改完数据库配置之后都需要重启服务 第...
  • mysql 主从复制原理及步骤。

    万次阅读 多人点赞 2018-07-21 21:20:35
    mysql是现在普遍使用的数据库,但是如果宕机了必然会造成...mysql主从是异步复制过程 master开启bin-log功能,日志文件用于记录数据库的读写增删 需要开启3个线程,master IO线程,slave开启 IO线程 SQL线程, ...
  • Mysql实现主从复制(一主双从)

    万次阅读 2018-07-17 22:35:53
    LNMP(centos7,mysql5.6) vmware workstation pro配置了3个虚拟机,均安装了LNMP环境: master: 192.168.0.105  slave: 192.168.0.106 、192.168.0.107   二、原理 (1)主数据库进行增删改操作后,...
  • Mysql主从复制(详细)

    2019-08-19 20:30:55
    Mysql主从配置
  • mysql 从服务器取消主从复制

    千次阅读 2018-03-19 23:22:03
    mysql&gt;change master to master_host=''mysql&gt;stop slave;reset slave;
  • mysql数据库主从判断

    万次阅读 2016-12-23 15:15:06
    在上一家公司做项目的时候遇到的一个问题,如何判断mysql主从数据库是否同步?当时我还没有接触到mysql主从服务的使用,于是就在网上搜索各种资料,例如什么如何查看mysql主从状态信息之类的问题。到最后有了一个...
  • MySQL 8.0主从(Master-Slave)配置

    万次阅读 2018-06-04 16:43:02
    MySQL 主从复制的方式有多种,本文主要演示基于基于日志(binlog)的主从复制方式。 MySQL 主从复制(也称 A/B 复制) 的原理 Master将数据改变记录到二进制日志(binary log)中,也就是配置文件log-bin指定的文件...
  • mysql主从同步的方法进行读写分离,减轻主服务器的压力的做法现在在业内做的非常普遍。 主从同步基本上能做到实时同步。我从别的网站借用了主从同步的原理图。   在配置好了, 主从同步以后, 主...
  • MYSQL主从同步原理

    千次阅读 2019-05-22 16:20:38
    MYSQL主从同步原理: 1) MYSQL主从同步是异步复制的过程,整个同步需要开启3线程,master上开启bin-log日志(记录数据库增、删除、修改、更新操作); 2) Slave开启I/O线程来请求master服务器,请求指定bin-log中...
  • 今天继续讨论,MySQL主从复制什么原因会造成不一致,如何预防及解决?1.人为原因导致从库与主库数据不一致(从库写入)2.主从复制过程中,主库异常宕机3.设置了ignore/do/rewrite等replication等规则4.binlog非row...
  • Mysql主从复制重启后失效问题解决

    千次阅读 2019-07-20 09:24:27
    配置完Mysql主从复制以后,发现将主机重启后,Mysql主从复制功能失效 Slave_IO_Running: Yes Slave_SQL_Running: NO 2.查找问题 通过查找/etc/mysql/data/error.log发现,mysql库的user表损坏了,如图: ...
  • MySQL主从复制面试之作用和原理

    万次阅读 多人点赞 2018-04-19 14:15:06
    一、什么是主从复制? 主从复制,是用来建立一个和主数据库完全一样的数据库环境,称为从数据库;主数据库一般是准实时的业务数据库。 二、主从复制的作用(好处,或者说为什么要做主从)重点! 1、做数据的...
  • Mysql“集群”和”主从“两者的区别

    万次阅读 2016-02-17 19:49:34
    所以研究了一下mysql的集群(cluster)和主从(master/slave)这两个概念。两者非常容易混淆,特别是对于菜鸟来讲。 Mysql cluster: share-nothing,分布式节点架构的存储方案,以便于提供容错性和高性能。需要用到...
1 2 3 4 5 ... 20
收藏数 97,906
精华内容 39,162
关键字:

mysql主从