Fork me on GitHub

Mysql 数据库主从同步(Master-Slave)

原理图

主从复制的原理

分为同步复制和异步复制,实际复制架构中大部分为异步复制。 复制的基本过程如下:
1).Slave上面的IO进程连接上Master,并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容;
2).Master接收到来自Slave的IO进程的请求后,通过负责复制的IO进程根据请求信息读取制定日志指定位置之后的日志信息,返回给Slave
的IO进程。返回信息中除了日志所包含的信息之外,还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置;
3).Slave的IO进程接收到信息后,将接收到的日志内容依次添加到Slave端的relay-log文件的最末端,并将读取到的Master端的
bin-log的文件名和位置记录到master-info文件中,以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容,请发给我”;
4).Slave的Sql进程检测到relay-log中新增加了内容后,会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容,并在自身执行。

环境描述

  • OS:CentOS
  • 主服务器master:192.168.11.120
  • 从服务器slave:192.168.11.139

mysql主从配置

mysql主服务器配置(master)

master服务器配置:
vim /etc/my.cnf

[mysqld]
server-id=120
log-bin=/var/lib/mysql/mysql-bin # 启用二进制日志
binlog-do-db=jasontest #指定数据库,如果不指定就是全部数据库
#binlog-ignore-db = mysql,information_schema #忽略写入binlog的库

重启服务器:

service mysqld  restart #systemctl restart mysqld.service --- centos7.x重启mysql

在主服务器上建立帐户并授权slave:

#创建slave帐号,密码123456
mysql> GRANT REPLICATION SLAVE ON *.* to 'slave'@'192.168.11.%' identified by '123456' WITH GRANT OPTION; 
#更新数据库权限
mysql>flush privileges;

查询master的状态:

mysql> show master status;

显示结果:
slave status

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

mysql从服务器配置(slave)

slave服务器配置:
vim /etc/my.cnf

[mysqld]
server-id=139
log-bin=/var/lib/mysql/mysql-bin # 启用二进制日志
binlog-do-db=jasontest #指定数据库,如果不指定就是全部数据库

重启服务器:

service mysqld  restart #systemctl restart mysqld.service --- centos7.x重启mysql

设置同步源:

mysql> CHANGE MASTER TO MASTER_HOST='192.168.11.120', #主服务器master的ip
    MASTER_USER='slave',
    MASTER_PASSWORD='123456',
    MASTER_LOG_FILE='mysql-bin.000022',  #对应show master status中File
    MASTER_LOG_POS=245;                     #对应show master status中Position

启用同步:

mysql>start slave

停止同步:

mysql>stop slave

检查从服务器状态:

mysql> SHOW SLAVE STATUS\G;

显示结果:
slave status

注意:Slave_IO_Running及Slave_SQL_Running进程必须正常运行,即YES状态,否则说明同步失败

下面介绍几个比较关键的参数,全面的说明可阅读参考手册

  • Slave_IO_Running: IO线程是否正常运行
  • Slave_SQL_Running: SQL线程是否正常运行
  • Master_Log_File: IO线程正在读取的主服务器日志文件
  • Read_Master_Log_Pos: IO线程正在读取的主服务器日志文件的位置
  • Relay_Log_File: SQL线程正在读取和执行的本地日志文件
  • Relay_Log_Pos: SQL线程正在读取和执行的本地日志文件的位置
  • Relay_Master_Log_File: SQL线程正在重演的事件所在的主服务器日志文件
  • Exec_Master_Log_Pos: SQL线程正在重演的事件在主服务器日志文件中的位置
  • Seconds_Behind_Master:如果网络没有明显的延迟,该参数标志着SQL线程的事件重演速度。如果该值较大且不断递增,调整innodb_flush_log_at_trx_commit会有比较明显的效果,但是可能会在MySQL进程崩溃时丢失数据。

mysql数据库同步验证

主从数据库设置工作已经完成,可以在master新建数据库和表,插入和修改数据,查看slave是否获得同步,测试一下是否成功。

相关问题

1、Slave_IO_Running: No Slave_SQL_Running: No

需要开启slave,即:start slave

2、Slave_IO_Running: No Slave_SQL_Running: Yes

出现error,如Last_IO_Error: error connecting to master 'slave@192.168.11.120:3306' - retry-time: 60  retries: 86400

解决方案:

1).mysql -h192.168.11.120 -uslave  -p
如果出现ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.11.120' (113) 说明服务器不允许远程访问

2).netstat -an | grep 3306
查看是否开启远程访问的端口3306

3).centos下配置开启3306端口:
centos7.x下:
vim /etc/firewalld/zones/public.xml 添加3306端口:

<zone>
    <short>Public</short>
    <description>For use in public areas. You do not trust the other computers on networks 
        to not harm your computer. Only selected incoming connections are accepted.</description>
    <service name="dhcpv6-client"/>
    <service name="ssh"/>
    <service name="http"/>
    <port protocol="tcp" port="3306"/>
</zone>
重启firewalld:systemctl restart firewalld.service

centos6.x下:
vim /etc/sysconfig/iptables 添加3306端口:

-A INPUT -m state --state NEW -m tcp -p tcp --dport 80 -j ACCEPT   #允许80端口通过防火墙
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT   #允许3306端口通过防火墙
-A INPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT

如果/etc/my.cnf或者/etc/mysql/my.cnf配置文件中有bind-address,则需要修改配置:
(1)注释掉 即:#bind-address=127.0.0.1
(2)修改为:bind-addess=192.168.11.120 即本机的ip
(3)修改为:bind-address=0.0.0.0
轻轻的我走了,正如我轻轻的来