IT虾米网

MySQL主从架构配置详解

xmjava 2018年07月03日 程序员 342 0

MySQL主从架构配置

有两台MySQL数据库服务器master和slave,master为主服务器,slave为从服务器,初始状态时,master和slave中的数据信息相同,当master中的数据发生变化时,slave也跟着发生相应的变化,使得master和slave的数据信息同步,达到备份的目的。

MySQL复制的基本过程

  1. Slave上面的IO线程连接上Master,并请求从指定日志文件的指定位置(或者从 
    最开始的日志)之后的日志内容;
  2. Master接收到来自Slave的IO线程的请求后,通过负责复制的IO线程根据请 
    求信息读取指定日志指定位置之后的日志信息,返回给Slave端的IO线程。返回信 
    息中除了日志所包含的信息之外,还包括本次返回的信息在Master端的Binary Log 
    文件的名称以及在Binary Log中的位置;
  3. Slave的IO线程接收到信息后,将接收到的日志内容依次写入到Slave端的 
    Relay Log文件(mysql-relay-bin.xxxxxx)的最末端,并将读取到的Master端的binlog的文件名和位置记录到master-info文件中,以便在下一次读取的时候能够清楚的 
    告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容,请发给我”
  4. Slave的SQL线程检测到Relay Log中新增加了内容后,会马上解析该Log文 
    件中的内容成为在Master端真实执行时候的那些可执行的Query语句,并在自身执 
    行这些Query。这样,实际上就是在Master端和Slave端执行了同样的Query,所 
    以两端的数据是完全一样的。

应用场景

  • 数据分布
  • 负载均衡
  • 备份
  • 高可用和容错

创建同步帐号

在master的数据库中建立一个备份帐户:每个slave使用标准的mysql用户名和密码连接master。进行复制操作的用户会授予REPLICATION SLAVE权限。 
命令如下: 
mysql > GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO 'slave'@'192.168.0.171' IDENTIFIED BY '123456'; 
建立一个帐户slave,并且只能允许从192.168.0.171这个地址上来登陆。添加用户后,可在从服务器上用mysql -h192.168.0.151 -uslave -p123456; 来测试是否有权限访问主数据库。

手动同步数据

如果要为已经有数据的主服务器添加从服务器,要先把主库已经存在的数据先手动同步迁移到从库上面去。搭建过程中,禁止在主库从库上进行任何对数据库的ddl、dml等数据操作。 
这里可以用mysqldump也可以用xtrabackup导出主库上面的数据,还可以直接用可视化管理工具(如navicat)把数据传输到从库上面。

配置master库

[mysqld] 
server-id = 151     #唯一   
log-bin = /data/mysql/binlog/mysql-bin   

配置slave库

[mysqld] 
server-id = 171     #唯一 
log-bin = /var/log/mysql/mysql-bin   
log_slave_updates = 1     #将复制事件写进自己的二进制日志 
relay_log_index = /var/log/mysql/slave-relay-bin.index 
relay_log = /var/log/mysql/slave-relay-bin 
replicate-do-db = userdb 
replicate-do-db = osadmin 
replicate-ignore-db = mysql 
replicate-ignore-db = information_schema 
replicate-ignore-db = performance_schema 
到主服务器上查看主机状态,记录File和Position对应的值。
mysql> SHOW MASTER STATUS\G   
File: mysql-bin.000001   
Position: 120   
Binlog_Do_DB:   
Binlog_Ignore_DB:   
Executed_Gtid_Set:   

File在binlog.000001 Position是120 
在slave机器上连接主库并启动线程:

mysql> CHANGE MASTER TO 
Master_Host='192.168.0.151', 
Master_Port=3306,  
Master_User='slave', 
Master_Password='123456',  
Master_Log_File='mysql-bin.000001', 
Master_Log_Pos=120, 
Master_Connect_Retry=60; 
mysql> START SLAVE; 

测试

slave:

mysql> SHOW SLAVE STATUS\G   
... 
Slave_IO_Running: Yes   
Slave_SQL_Running: Yes 
...  

如果都是Yes,说明slave的I/O和SQL线程都已经开始运行,配置成功。

master:

mysql> SHOW PROCESSLIST\G 
... 
Id: 6 
User: slave 
Host: 192.168.0.171:52166 
db: NULL 
Command: Binlog Dump 
Time: 278 
State: Master has sent all binlog to slave; waiting for binlog to be updated 
Info: NULL 

可以看到slave的线程正在运行。如果我们在数据库中新建表和添加数据,在slave中也会同步。


踩过的坑

  • MySQL无法关闭或重启: 
    Warning: World-writable config file '/etc/my.cnf' is ignored

    • 原因是my.cnf配置文件权限全局可写。mysql担心这种文件被其他用户恶意修改,所以忽略掉这个配置文件。这样mysql无法关闭。
    • 解决办法:修改my.cnf权限 chmod 644 /etc/my.cnf
  • start slave出错: 
    [Err] 1872 - Slave failed to initialize relay log info structure from the repository

    • 可能原因:my.cnf配置文件没指定relay_log或者指定路径错误,注意空格。
    • 解决方法: 
      1. 停止slave 
        mysql>STOP SLAVE;
      2. 在my.cnf中添加 
        relay_log=/var/log/mysql/relay_log 
        relay_log_index=/var/log/mysql/relay_log.index
      3. 重置slave日志并连接master 
        mysql>RESET SLAVE; 
        mysql>CHANGE MASTER TO 
        Master_Host='192.168.0.151', 
        Master_Port=3306, 
        Master_User='slave', 
        Master_Password='123456', 
        Master_Log_File='mysql-bin.000009', 
        Master_Log_Pos=159;
      4. 启动slave 
        mysql>START SLAVE;
  • 无法同步,error.log: 
    [ERROR] Error reading packet from server: Misconfigured master - server_id was not set ( server_errno=1236) 
    [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Misconfigured master - server_id was not set', Error_code: 1236

    • 原因很明显,master没有设置server-id,这里要注意,server-id一定要写在[mysqld]下面,如果装的是wamp集成环境的话这个server-id默认在[wampmysqld]下面了,这是不行的。
    • 解决办法:添加配置 
      [mysqld] 
      server-id = 1
  • 无法同步,[SQL]SHOW SLAVE STATUS; 
    Last_Error:Error 'Unknown database 'userdb'' on query. Default database: 'userdb'... 
    或者 
    Last_Error:Error 'Table 'userdb.table' doesn't exist' on query. Default database: 'userdb'... 
    • 可能原因:master库中有userdb数据库和table表而slave中没有,而且同步之前没有把master库中已有的数据传到slave中。忘了哪里看到的好像数据库引擎不一样也可能会出现这个问题…
    • 解决办法:手动将userdb数据库传到slave中并重新设置同步。
  • 无法同步,[SQL]SHOW SLAVE STATUS; 
    Last_Error:Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
    • 原因:master_log_file文件名指定错误。
    • 解决办法:在master上使用mysql> show master status\G 查看File并在slave上重新连接master。
  • 配置项下划线和中杠要分清,比如:log-bin和log_bin
  • slave跟master的MySQL版本一致,是官方推荐的方式;至少版本前两个号相同,可高于master;版本不一致,可能出现的问题就是同步的不稳定(兼容性的老问题),因为会在某些函数处理、日志读取、日志的解析重演等上发生异常,导致同步报错而需手工处理。
  • 注意检查防火墙

多说几句

  • 官方文档推荐的是,在master端不指定binlog-do-db,在slave端用replication-do-db来过滤。
  • 不要在my.cnf/my.ini中配制master_host等选项,而应该使用CHANGE MASTER TO命令来动态设置!Mysql版本从5.1.7以后开始就不支持”master-host”类似的参数。详情可以查看http://dev.mysql.com/doc/refman/5.5/en/replication-howto.html
发布评论

分享到:

IT虾米网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

Java消息队列--ActiveMq 实战详解
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。