• MySQL Replication 详解MySQL数据库设置主从同步的方法



    MySQL同步的流程大致如下: 

         1、主服务器(master)将变更事件(更新、删除、表结构改变等等)写入二进制日志(master log)。 

         2、从服务器(slave)的IO线程从主服务器(binlog dump线程)获取二进制日志,并在本地保存一份自己的二进制日志(relay log) 

         3、从服务器的SQL线程读取本地日志(relay log),并重演变更事件。

    MySQL主从同步的作用: 

         1、可以作为一种备份机制,相当于热备份(在从备份,避免备份期间影响主服务器服务) 

         2、可以用来做读写分离,均衡数据库负载(主写从读) 

         3、当主服务器出现问题时,可以切换到从服务器。

    MySQL主从同步的步骤:

    一、准备操作: 

    1、主从数据库版本一致,建议版本5.5以上 

    2、主从数据库数据一致 

    二、主数据库master修改: 

    1、修改MySQL配置:

     1 # /etc/my.cnf 
     2 log-bin = mysql-bin 
     3  
     4 # 主数据库端ID号 
     5 server-id = 1 
     6  
     7 log-bin=/home/mysql/logs/binlog/bin-log
     8 max_binlog_size = 500M
     9 binlog_cache_size = 128K
    10 binlog-do-db = adb
    11 binlog-ignore-db = mysql
    12 log-slave-updates
    13 expire_logs_day=2
    14 binlog_format="MIXED"

    上面配置中各个参数的含义和相关注意项:

     1 #服务器标志号,注意在配置文件中不能出现多个这样的标识,如果出现多个的话mysql以第一个为准,一组主从中此标识号不能重复。
     2 server-id = 1
     3  
     4 log-bin=/home/mysql/logs/binlog/bin-log #开启bin-log,并指定文件目录和文件名前缀。
     5  
     6 #每个bin-log最大大小,当此大小等于500M时会自动生成一个新的日志文件。一条记录不会写在2个日志文件中,所以有时日志文件会超过此大小。
     7 max_binlog_size = 500M 
     8  
     9 binlog_cache_size = 128K #日志缓存大小
    10  
    11 binlog-do-db = adb #需要同步的数据库名字,如果是多个,就以此格式在写一行即可。
    12  
    13 binlog-ignore-db = mysql #不需要同步的数据库名字,如果是多个,就以此格式在写一行即可。
    14  
    15 #当Slave从Master数据库读取日志时更新新写入日志中,如果只启动log-bin 而没有启动log-slave-updates则Slave只记录针对自己数据库操作的更新。
    16 log-slave-updates 
    17  
    18 expire_logs_day=2 #设置bin-log日志文件保存的天数,此参数mysql5.0以下版本不支持。
    19  
    20 binlog_format="MIXED" #设置bin-log日志文件格式为:MIXED,可以防止主键重复。
    2、重启mysql,创建用于同步的账户:
    1 # 创建slave帐号slave,密码111111 
    2 mysql>grant replication slave on *.* to 'slave'@'%' identified by '111111'; 
    3  
    4 # 更新数据库权限 
    5 mysql>flush privileges;

    3、查询master的状态

    1 mysql> show master status; 
    2 +------------------+----------+--------------+------------------+ 
    3 | File    | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
    4 +------------------+----------+--------------+------------------+ 
    5 | mysql-bin.000009 |  196 |    |     | 
    6 +------------------+----------+--------------+------------------+ 
    7 1 row in set

    注:执行完这个步骤后不要再操作主数据库了,防止主数据库状态值变化

    三、从数据库slave修改:

    1、修改MySQL配置:

    1 # 从数据库端ID号 
    2 server-id =2

    2、执行同步命令

    1 # 执行同步命令,设置主数据库ip,同步帐号密码,同步位置 
    2 mysql>change master to master_host='192.168.1.2',master_user='slave',master_password='111111',master_log_file='mysql-bin.000009',master_log_pos=196; 
    3  
    4 # 开启同步功能 
    5 mysql>start slave;

    3、检查从数据库状态:

     1 mysql> show slave statusG; 
     2 *************************** 1. row *************************** 
     3     Slave_IO_State: Waiting for master to send event 
     4      Master_Host: 192.168.1.2 
     5      Master_User: slave_account 
     6      Master_Port: 3306 
     7     Connect_Retry: 60 
     8     Master_Log_File: mysql-bin.000009 
     9    Read_Master_Log_Pos: 196 
    10     Relay_Log_File: vicky-relay-bin.000002 
    11     Relay_Log_Pos: 253 
    12   Relay_Master_Log_File: mysql-bin.000009 
    13     Slave_IO_Running: Yes 
    14    Slave_SQL_Running: Yes 
    15     Replicate_Do_DB: 
    16    Replicate_Ignore_DB: 
    17    ... 
    18    ...

    注:Slave_IO_Running及Slave_SQL_Running进程必须正常运行,即YES状态,否则说明同步失败。可用这两项判断从服务器是否挂掉

    到这里,主从数据库设置工作已经完成,自己可以新建数据库和表,插入和修改数据,测试一下是否成功

    四、其他可能用到的相关参数:

    1、master端:

     1 # 不同步哪些数据库 
     2 binlog-ignore-db = mysql 
     3 binlog-ignore-db = test 
     4 binlog-ignore-db = information_schema 
     5  
     6 # 只同步哪些数据库,除此之外,其他不同步 
     7 binlog-do-db = game 
     8  
     9 # 日志保留时间 
    10 expire_logs_days = 10 
    11  
    12 # 控制binlog的写入频率。每执行多少次事务写入一次 
    13 # 这个参数性能消耗很大,但可减小MySQL崩溃造成的损失 
    14 sync_binlog = 5 
    15  
    16 # 日志格式,建议mixed 
    17 # statement 保存SQL语句 
    18 # row 保存影响记录数据 
    19 # mixed 前面两种的结合 
    20 binlog_format = mixed

    2、slave端

    1 # 停止主从同步 
    2 mysql> stop slave; 
    3  
    4 # 连接断开时,重新连接超时时间 
    5 mysql> change master to master_connect_retry=50; 
    6  
    7 # 开启主从同步 
    8 mysql> start slave;

    以上连接超时设置,类似方式可用于设置主数据库ip,同步帐号密码,同步位置

    判断主从服务器是否正常运行代码

    //在从服务器中执行以下语句
    $sql = "show slave status";
    $result = mysql_query($sql, $slave_link);
    $row = mysql_fetch_assoc($result);
     
    $Slave_IO_Running = $row['Slave_IO_Running'];
    $Slave_SQL_Running = $row['Slave_SQL_Running'];
     
    //下面两项为判断标准
    if ('Yes' == $Slave_IO_Running && 'Yes' == $Slave_SQL_Running) {
     
    } else {
     $content .= "从数据库( $host )挂掉了!!! <br/>";
    }

    总结

    以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作能带来一定的帮助,如果有疑问大家可以留言交流。

  • 相关阅读:
    CDQZ Day5
    CDQZ Day4
    CDQZ Day3
    POJ 2226 Muddy Fields
    【其他】【生成图片】【1】通过phantomjs 进行页面截图
    【Redis】【1】一些报错处理
    【Java】【15】判断url对应的图片是否存在
    【HTML&CSS】【3】点击直接发送邮件
    【微信公众号开发】【16】遇到的问题
    【Oracle】【9】取前N条记录——rownum和row_number() over()的使用
  • 原文地址:https://www.cnblogs.com/sunshine-long/p/8722196.html
Copyright © 2020-2023  润新知