mysql主从配置定义及流程讲析

下文内容主要给大家带来MySQL主从配置定义及流程讲析，这里所讲到的知识，与书籍略有不同，都是创新互联专业技术人员在与用户接触过程中，总结出来的，具有一定的经验分享价值，希望给广大读者带来帮助。

成都创新互联公司是一家专业提供新野企业网站建设,专注与网站设计制作、网站制作、H5场景定制、小程序制作等业务。10年已为新野众多企业、政府机构等服务。创新互联专业网络公司优惠进行中。

Mysql主从同步的配置

主库IP：192.168.1.2

从库IP：192.168.1.3

添加一个用于主从同步的用户：

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY ‘1q2w3e4r’;

如果监控mysql主从的话，请加上一个super权限：

GRANT SUPER, REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY '1q2w3e4r';

1、主库的配置

1.1．mysql5.0以下版本的配置

修改主库mysql配置配置文件，在[mysqld]段添加以下内容：

server-id = 1

log-bin=/home/mysql/logs/binlog/bin-log

max_binlog_size = 500M

binlog_cache_size = 128K

binlog-do-db = adb

binlog-ignore-db = mysql

log-slave-updates

1.2. mysql5.0以上版本的配置

修改主库mysql配置配置文件，在[mysqld]段添加以下内容：

server-id = 1

log-bin=/home/mysql/logs/binlog/bin-log

max_binlog_size = 500M

binlog_cache_size = 128K

binlog-do-db = adb

binlog-ignore-db = mysql

log-slave-updates

expire_logs_day=2

binlog_format="MIXED"

1.3.各个参数的含义和相关注意项：

mysql主从配置定义及流程讲析

server-id = 1 #云服务器标志号，注意在配置文件中不能出现多个这样的标识，如果出现多个的话mysql以第一个为准，一组主从中此标识号不能重复。

log-bin=/home/mysql/logs/binlog/bin-log #开启bin-log，并指定文件目录和文件名前缀。

max_binlog_size = 500M #每个bin-log最大大小，当此大小等于500M时会自动生成一个新的日志文件。一条记录不会写在2个日志文件中，所以有时日志文件会超过此大小。

binlog_cache_size = 128K #日志缓存大小

binlog-do-db = adb #需要同步的数据库名字，如果是多个，就以此格式在写一行即可。

binlog-ignore-db = mysql #不需要同步的数据库名字，如果是多个，就以此格式在写一行即可。

log-slave-updates #当Slave从Master数据库读取日志时更新新写入日志中，如果只启动log-bin 而没有启动log-slave-updates则Slave只记录针对自己数据库操作的更新。

expire_logs_day=2 #设置bin-log日志文件保存的天数，此参数mysql5.0以下版本不支持。

binlog_format="MIXED" #设置bin-log日志文件格式为：MIXED，可以防止主键重复。

2、从库的配置

2.1.mysql5.1.7以前版本

修改从库mysql配置配置文件，在[mysqld]段添加以下内容：

server-id=2

master-host=192.168.1.2

master-user=repl

master-password=1q2w3e4r

master-port=3306

master-connect-retry=30

slave-skip-errors=1062

replicate-do-db = adb

replicate-ignore-db = mysql

slave-skip-errors=1007,1008,1053,1062,1213,1158,1159

master-info-file = /home/mysql/logs/master.info

relay-log = /home/mysql/logs/relay-bin

relay-log-index = /home/mysql/logs/relay-bin.index

relay-log-info-file = /home/mysql/logs/relay-log.info

如果修改了连接主库相关信息，重启之前一定要删除master.info文件，否则重启之后由于连接信息改变从库而不会自动连接主库，造成同步失败。此文件是保存连接主库信息的。

2.2.mysql5.1.7以后版本

Mysql5.1.7版本在丛库上面的配置很少，主要是采用了新的同步信息记录方式，他不在支持在配置文件中配置连接主库的相关信息，而是把连接等相关信息记录在master-info-file = /home/mysql/logs/master.info文件中，如果入库变了，直接在mysql命令行执行连接信息的改变即可生效，比较灵活了，而不用去重启mysql。修改从库mysql配置配置文件，在[mysqld]段添加以下内容：

slave-skip-errors=1007,1008,1053,1062,1213,1158,1159

2.3. 各个参数的含义和相关注意项

这里只讲一下2个参数，其他全部是从库连接主库的信息和中间日志relay-log的设置。

master-connect-retry=30 #这个选项控制重试间隔，默认为60秒。

slave-skip-errors=1007,1008,1053,1062,1213,1158,1159 #这个是在同步过程中忽略掉的错误，这些错误不会影响数据的完整性，有事经常出现的错误，一般设置忽略。其中1062为主键重复错误。

3、实现主从同步

3.1.实现数据库的统一

检查主从数据库的配置文件，查看是否已正确配置。首次实现同步要备份主库上需要同步的数据库，然后完整的导入到从库中。注：mysql5.0之前的版本涉及到mysql本身复制过滤存在问题，需要把所有的数据库都备份导入到丛库，保持。

3.2.查看并记录主库bin-log信息

进入主库mysql中，执行：show master status;显示信息如下：

mysql> show master status;

+-------------+----------+--------------+------------------+

+-------------+----------+--------------+------------------+

| bin-log.003 | 4 | adb | mysql |

+-------------+----------+--------------+------------------+

1 row in set (0.00 sec)

记录File 和Position信息；

3.3.在从库上执行同步语句

进入mysql，执行以下语句：

slave stop;

change master to

master_host='192.168.1.2',

master_user='repl',

master_password='1q2w3e4r',

master_port=3306,

master_log_file='bin-log.003',

master_log_pos=4;

slave start;

3.4.查看主从同步状态

进入mysql，执行show slave status\G;显示如下（mysql版本不同查询的结果不同，但是重要的指标还是一样的）：

Mysql5.0之前的版本如下：

mysql主从配置定义及流程讲析

Mysql5.5之前的版本如下：

mysql主从配置定义及流程讲析

Mysql5.5的版本如下：

mysql主从配置定义及流程讲析

重要的指标为：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Master_Log_File: bin-log.003

Relay_Master_Log_File: bin-log.003

Read_Master_Log_Pos: 4

Exec_master_log_pos: 4

Seconds_Behind_Master: 0（5.0之前版本没有这个选项）

以上选项是两两对应的，只要结果是一致的，就说明主从同步成功。

3.5.同步中的常见的错误和处理

1、现象：在从库上面show slave status\G;出现下列情况，

Slave_IO_Running: Yes

Slave_SQL_Running: No

Seconds_Behind_Master: NULL

原因：

a.程序可能在slave上进行了写操作；

b.也可能是slave机器重起后，事务回滚造成的；

c．有可能是在同步过程中遇到某种错误，这个会在查看从库中状态时看到错误提示，最少见的就是主键重复1062的错误。

解决方法：

进入master

mysql> show master status;

+----------------------+----------+--------------+------------------+

+----------------------+----------+--------------+------------------+

| mysql-bin.000040 | 324 |adb | mysql|

+----------------------+----------+--------------+------------------+

然后到slave服务器上执行手动同步

slave stop;

change master to

master_host='10.14.0.140',

master_user='repl',

master_password='1q2w3e4r',

master_port=3306,

master_log_file='mysql-bin.000040',

master_log_pos=324;

slave start;

show slave status\G;

2、现象：从数据库无法同步，show slave status显示:

Slave_IO_Running: No

Slave_SQL_Running: Yes

Seconds_Behind_Master: NULL

解决：首先查看数据库的err日志，查看是什么错误提示，看从库连接主库的IP、用户、密码等相关信息是否有误，如果有误，重新执行同步；如果确认无误，重启主数据库。

mysql> show master status;

+------------------+----------+--------------+------------------+

+------------------+----------+--------------+------------------+

| mysql-bin.000001 | 98 | adb| mysql|

+------------------+----------+--------------+------------------+

进入从库mysql，执行：

slave stop;

change master to Master_Log_File='mysql-bin.000001',Master_Log_Pos=98;

slave start;

或是这样：

stop slave;

set global sql_slave_skip_counter =1;

start slave;

这个现象主要是master数据库存在问题，由于连接主库信息错误、主库数据库挂掉如果说常见错等原因引起的，我在实际的操作中先重启master后重启slave即可解决这问题，出现此问题，必须要要重启master数据库。

四、mysql主主和主主集群

1、mysql主主的实现

在实际的生产应用中，为了在主库出现崩溃或是主服务器出现严重故障时快速的恢复业务，会直接切换到从库上，当主库故障处理完成后让他直接作为丛库来运行，此时主主就是一个不错的选择。

五、mysql主从的监控

在mysql主从的应用中，只要进行了合理设置，基本上不会出现问题，但是对他的监控是必不可少的，以免由于真的出现问题又不知道而造成不必要的数据损失。

1、mysql主从监控的主要思路

Mysql主从的监控，其主要是监控从库上的一些重要参数：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Master_Log_File: bin-log.003

Relay_Master_Log_File: bin-log.003

Read_Master_Log_Pos: 4

Exec_master_log_pos: 4

Seconds_Behind_Master: 0（5.0之前版本没有这个选项）

通过以上的参数可以反映出主库和从库状态是否正常，从库是否落后于主库等。值得一提的是在mysql5.0以前的版本，Slave_IO_Running这个状态指标不可靠，会在主库直接挂掉的情况下不会变成NO，Seconds_Behind_Master参数也不存在。监控以上参数即可监控mysql主从。

2、mysql主从监控的实现

不管mysql是那个版本，其中的从库上的Exec_master_log_pos、Exec_master_log_pos；主库上的 Master上的Log_File， Position，这四个参数可以判断出当前主从的状态。以下是适用于mysql所有版本的主从监控shell脚本：

#/bin/sh

user=repl

passwd=123415

master_ip="192.168.1.2"

log="/data3/check_repl.log"

value()

{

master=`/usr/local/mysql/bin/mysql -u$user -p$passwd -h$master_ip -e "show master status\G;"|egrep "File|Position"`

#mysql 4.0

slave=`/usr/local/mysql/bin/mysql -u$user -p$passwd -h227.0.0.1 -e "show slave status\G;"|egrep "Relay_Master_Log_File|Exec_master_log_pos"`

#mysql 5.0

#slave=`mysql -u$user -p$passwd -e "show slave status\G;"|egrep "Relay_Master_Log_File|Exec_Master_Log_Pos"`

#取主库上的bin-log号及写入的当前日志位置

Master_Log=`echo $master |awk '{print $2}'|awk -F "." '{print $2}'`

Master_Log_Pos=`echo $master |awk '{print $4}'`

#取从库上当前同步主库的位置

Relay_Master_Log_File=`echo $slave |awk '{print $2}'|awk -F "." '{print $2}'`

Exec_Master_Log_Pos=`echo $slave |awk '{print $4}'`

echo "Master_Log:"$Master_Log>>$log

echo "Master_Log_Pos:"$Master_Log_Pos>>$log

echo "Relay_Master_Log_File:"$Relay_Master_Log_File>>$log

echo "Exec_Master_Log_Pos:"$Exec_Master_Log_Pos>>$log

}

for((i=1;i<=10;i++));

echo "#################################">>$log

value

time=`date +"%Y-%m-%d %H:%M:%S"`

if [ $Master_Log -eq $Relay_Master_Log_File ];then

A=`expr $Master_Log_Pos - $Exec_Master_Log_Pos`

if [ $A -lt 0 ];then

A=`expr 0 - $A`

echo $A>>$log

if [ $A -lt 10000 ];then

echo "$time Master-Slave is OK.">>$log

#echo "$i"

break

else

if [ $i ge 3 ];then

echo "$time Warning:Slave-Master lag $A " >>$log

echo "$i"

sleep 30

continue

else

sleep 60

if [ $i -eq 10 ];then

echo "$i"

echo "$time Error:Slave-Master must be check !" >>$log

done

在mysql5.0以后的版本，mysql主从已经相当的成熟了，可以只监控Slave_IO_Running，Slave_SQL_Running，Seconds_Behind_Master状态就可以了，这里不再做说明。

对于以上关于mysql主从配置定义及流程讲析，如果大家还有更多需要了解的可以持续关注我们创新互联的行业推新，如需获取专业解答，可在官网联系售前售后的，希望该文章可给大家带来一定的知识更新。

标题名称：mysql主从配置定义及流程讲析
转载注明：http://hbruida.cn/article/ipdoso.html

mysql主从配置定义及流程讲析

其他资讯