MySQL主从架构配置

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.indexrelay_log = /var/log/mysql/slave-relay-binreplicate-do-db = userdbreplicate-do-db = osadminreplicate-ignore-db = mysqlreplicate-ignore-db = information_schemareplicate-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 TOMaster_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: 6User: slaveHost: 192.168.0.171:52166db: NULLCommand: Binlog DumpTime: 278State: Master has sent all binlog to slave; waiting for binlog to be updatedInfo: 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