mysql主从注意事项_mysql主从复制亲测，以及注意事项

本人亲测，windows作为mysql主服务器，linux作为从服务器，使用两个linux配置步骤都一样，测一遍而已；区别配置文件在于windwos是my.ini。linux在/etc/my.cof

主从复制的原理是b+tree,具体的去百度；主服务器master，启用二进制日志，所有操作在这个日志中记录着，从服务器slave从主服务器中读取二进制日志，然后将所有主服务器的操作全部执行一遍，以此来实现同步内容；

主从配置：为了将mysql的数据库分不到多个系统中，最简单的实现方式就是复制了。而mysql内建有一种复制方式，即指定一台或多台服务器为主机master，另外一台或多台服务器作为从机slave。当master存在更新的时候，master将更新写入二进制文件，并维护了一个索引文件跟踪日志。当slave连接到主服务器之后，slave会通知master进行同步，master通过索引找日志文件上一次同步的位置，然后将这段时间内的更新数据发送给slave进行同步。

为了方便演示，采用单主单从的主从配置，单主多从的配置原理一样。双主配置能很好地避免主服务器的宕机情况，配置比较复杂，下次有空演示。

Windows配置

测试环境

windows 7，mysql 5.6.42，为了避免因为版本不一致带来的麻烦，主从数据库用同一版本数据库库

主机ip：192.168.1.109　　从机ip：192.168.1.169

准备条件

(1)关闭防火墙

(2)保证两台主机的mysql远程连接开启(必须)

(3)3306端口(数据库端口号，不固定，按自己的配置去确定)是否公开

(4)在主机和从机上创建test数据库

主机配置

(1)在mysql的安装文件夹中找到mysql.ini文件，在该文件的mysqld标签下添加以下属性(还有其他很多属性可以自行了解)：

#=========主从复制关键配置=====================

server_id=1 #主库和从库需要不一致,配一个唯一的ID编号，1至32。 手动设定

log-bin=D:\phpStudy\PHPTutorial\MySQL\mysql-bin #二进制文件存放路径,存放在根目录data

#binlog-do-db=test #需要复制的库,多个库用逗号隔开,如果此项不配置所有主库都参与复制

#binlog-ignore-db=mysql #不需要复制的库，和上项同理；也可以直接设置哪些数据库不同步

#=========主从复制关键配置=====================

注意：mysql binlog_do_db参数设置的坑

(

在配置文件中想当然地配置成binlog_do_db=test,xx,jj，以为是三个库。结果无论什么操作都没有binlog产生

原因

mysql内部将“test,xx,jj”当成一个数据库了，结果因为我们没有这个db，自然就啥binlog都没写入了。

处理方法

正确的配置方法应该是这样

binlog_do_db=test

binlog_do_db=xx

binlog_do_db=jj

两种设置方法在show master status结果显示是完全一致的，也不报错，其他的过滤参数设置方法应该差不多，这真是太坑了！

)

配置完成后重启mysql，这时候会在这个时候在D:\phpStudy\PHPTutorial\MySQL\下会看到mysql-bin.index和mysql-bin.00001两个文件，很明显前面的文件时索引文件，用以记录第二个文件，第二个是二进制的日志文件，该文件每次重启数据库均会产生新的日志文件，这些日志文件的索引被以文件名为索引的索引文件所维护，在索引文件中可以看到所有日志文件的坐标。

保存，重启主库

进入mysql；mysql-u root -proot

登录主服务器创建从服务器用到的账户和权限：

grant replication slave on *.* to 'username' @'192.168.1.%' identified by '123456';flush privileges; //刷新权限

(192.168.0.%通配符(这里是写从库的ip地址)，表示0-255的IP都可访问主服务器，正式环境请配置指定从服务器IP,username/pwd自己设置(设置从库的用户名username，密码是123456))

(解释一下：grant replication slave,reload,super on *.*  to congku@192.168.1.244  identified by '123456';

这条语句意思是给ip为192.168.1.244(从机ip)的远程服务器配置登录名是congku，密码是123456，并赋予slave,super,reload权限)

退出mysql，重启mysql： service mysqld restart

show master status; # 查出主机的file和postin在从机配置中需要使用，记住即可。

可以看出已经产生了二进制的日志文件信息，mysql的同步就是通过这个二进制日志文件进行同步，主数据库把    对数据库的操作的指令都记录到该日志文件下，从数据库通过读取该文件，来对从数据库中的数据进行修改，    从而达到主从同步的效果。

致此，主机配置完成。

从库配置

同样编辑/etc/my.cnf文件，把server-id修改一下

#========主从复制关键配置======================

server_id=6 #主库和从库需要不一致

#binlog-do-db=test #和主库说明一致

#binlog-ignore-db=mysql #和主库说明一致

relay_log = mysql-relay-bin

理解：relay log很多方面都跟binary log差不多，区别是：从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后SQL线程会读取relay-log日志的内容并应用到从服务器。

#========主从复制关键配置======================

重启数据库

进入从库的mysql：mysql -uroot -p 执行以下命令

stop slave;

MySQL [(none)]> change master to

-> master_host='192.168.1.109',

-> master_port=3306,

-> master_user='username',

-> master_password='123456',

-> master_log_file='mysql-bin.000005',

-> master_log_pos=602;

MySQL [(none)]> start slave; //OK没问题的话，开启监听。

解释：

change master to

master_host='192.168.0.109', #主库地址

master_port=3306, #端口

master_user='username', #在上面 grant replication 设置的从库的用户名

master_password='123456', #在上面 grant replication 设置的从库的密码

master_log_file='master-bin.000005', #从主库查出来的二进制文件名 (上面主库状态，图片中的File)

master_log_pos=602; #从主库查出来的Position(上面主库状态，图片中的Position)

MySQL [(none)]> show slave status\G; //查看状态

mysql>show slave status\G;*************************** 1. row ***************************Slave_IO_State: Waiting formaster to send event

Master_Host: 192.168.1.109Master_User:username

Master_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000005Read_Master_Log_Pos: 602Relay_Log_File: mysql-relay-bin.000002Relay_Log_Pos: 283Relay_Master_Log_File: mysql-bin.000003Slave_IO_Running: Yes

Slave_SQL_Running:yes //标红的这两项是yes才算是成功

Replicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 1146

然后在主服务器做相应的Mysql增删改，同样再查看一下从服务器的数据，都发生了相应的变化~

主从配置完成；

因为主库在原来的表中进行了添加操作，结果从库没有反应，后来查看slave状态发现Slave_SQL_Running: No了；

主从复制-从机Slave_SQL_Running: No

我们可以明显的发现，我们的sql线程已经挂掉了。

导致这个问题的原因是因为由于数据的不同步，导致我们在执行主从同步的时候出现了执行不了的sql，导致从机事务回滚了，所以sql的进程就被关闭了，常见的这个问题的出现方式：

在从机上进行了写操作(非双机热备情况下)。

slave机器重启，事务回滚。

各种原因导致的数据不同步。

解决办法1：

mysql> stop slave;

mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;

mysql> start slave;

这个解决的是因为事务的原因导致的sql进程停止。

解决办法2：

停止从机服务：stop slave;

然后在主机上查看binlog文件的位置：show master status;

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

| File                      | Position   | Binlog_Do_DB | Binlog_Ignore_DB |

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

| XXX-bin.000004 | 33622483 |                   |                      |

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

然后重新手动同步：

mysql> change master to

> master_host='master_ip',

> master_user='user',

> master_password='pwd',

> master_port=3306,

> master_log_file=‘XXX-bin.000004’,

> master_log_pos=33622483 ;

然后启动从服务：start slave;

总结

要不就查看是否和主服务器断开了binlog的同步，要不就看是否是因为事务问题导致的数据不一致。