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

时间:2024-08-26 20:07:44

本人亲测,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,以为是三个库。结果无论什么操作都没有binlo*生
原因
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,密码是))

(解释一下: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主从复制亲测,以及注意事项

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