MySQL主从架构配置
扫描二维码
随时随地手机看文章
MySQL主从架构配置
有两台MySQL数据库服务器master和slave，master为主服务器，slave为从服务器，初始状态时，master和slave中的数据信息相同，当master中的数据发生变化时，slave也跟着发生相应的变化，使得master和slave的数据信息同步，达到备份的目的。
MySQL复制的基本过程
Slave上面的IO线程连接上Master，并请求从指定日志文件的指定位置（或者从
最开始的日志）之后的日志内容；Master接收到来自Slave的IO线程的请求后，通过负责复制的IO线程根据请
求信息读取指定日志指定位置之后的日志信息，返回给Slave端的IO线程。返回信
息中除了日志所包含的信息之外，还包括本次返回的信息在Master端的Binary Log
文件的名称以及在Binary Log中的位置；Slave的IO线程接收到信息后，将接收到的日志内容依次写入到Slave端的
Relay Log文件(mysql-relay-bin.xxxxxx)的最末端，并将读取到的Master端的binlog的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的
告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”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 STATUSG 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 STATUSG ... Slave_IO_Running: Yes Slave_SQL_Running: Yes ...
如果都是Yes，说明slave的I/O和SQL线程都已经开始运行，配置成功。
master:
mysql> SHOW PROCESSLISTG ... 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或者指定路径错误，注意空格。解决方法：
停止slave mysql>STOP SLAVE;在my.cnf中添加 relay_log=/var/log/mysql/relay_log relay_log_index=/var/log/mysql/relay_log.index重置slave日志并连接master mysql>RESET SLAVE; 启动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;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 statusG 查看File并在slave上重新连接master。配置项下划线和中杠要分清，比如：log-bin和log_binslave跟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
下载文档
