mysql 主从复制配置文件修改了不生效

转载

梦里忧郁 2024-10-11 19:52:26

文章标签 数据库操作系统 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 主从复制配置文件修改了不生效_服务器

可以看出已经产生了二进制的日志文件信息，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 for master to send event
                  Master_Host: 192.168.1.109
                  Master_User: username
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 602
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000003
             Slave_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 主从复制配置文件修改了不生效_操作系统_02

然后在主服务器做相应的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的同步，要不就看是否是因为事务问题导致的数据不一致。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。