mysql部署主从复制中遇到的问题汇总

ゝ一世哀愁。 2022-12-26 10:09 137阅读 0赞

### 前提简介 ###

事情是这样的，公司的数据目前的量已经算是比较大的了，对于安全问题和mysql的性能问题逐步开始重视起来，因为之前的mysql出问题基本都是我在处理，所以自然就接手了后面的mysql运维工作。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg3MDY0Ng_size_16_color_FFFFFF_t_70]

### 资源分析 ###

之前我们的数据库和业务组的数据库放在一个服务器上，目前需要将两个组（数据组，业务组）的数据分开，于是又购买了一台阿里云的内网服务器，公司内服还有两台本地的物理机，其中一台业务组在使用，所以目前手上能使用的服务器有两台：阿里云内网服务器，本地内网物理机服务器  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg3MDY0Ng_size_16_color_FFFFFF_t_70 1]

### 需求分析 ###

根据数据安全321原则，我们至少要保存三份数据，两个不同媒介的数据备份，一份异地数据备份。

目前异地备份可以先不考录，两种媒介的数据备份，一种放服务器，一种放nas，那么现在就能满足数据安全的前两个要求。

数据既然要保存多份，那么就可以将mysql做成高可用，对数据库读写优化，进行读写分离，那么就要设计数据库的主从复制或者MMM，MHA框架等，就目前拥有的服务器性能而言，可以优先实现数据库的主从复制。

### 部署问题分析 ###

阿里云的内网服务器的性能优于本地物理机，所以选择阿里云的内网服务器作为主节点（后面简称为186），本地物理机为从节点（后面简称为174），186在后面的使用中就作为‘写’服务器，174就作为‘读’服务器。

#### 一、两台内网服务器之间相互通讯问题 ####

但是因为两台服务器都是内网，所以无法直接相互通讯，必须要在186的外网搭建一个通道，让174能直接访问到，使用ssh服务，创建一个33308的端口通道，这样就能实现两个内网服务器之间的相互访问了。  
![在这里插入图片描述][20201204145632176.png]

如：ssh -fN -g -L 33308:localhost:3306 root@xxxxxx

在外网上进行配置，以上命令成功后，访问外网的33308端口，就等同于访问内网的3306端口。

#### 二、数据同步备份问题 ####

现在186上时有数据的，且数据量很大，必须要保证两台服务器上的数据以及数据结构相同，在部署的时候才能实现数据同步不出错。

现在要做的就是先将186的数据进行备份，我们可以使用mysqldump将数据迁移出来，在还原到174上去，但是这样有个问题就是，在数据备份和还原的这段时间里，服务器上如果还在运行爬虫，那么数据就不是最新的，同时186和174的数据不一样。

从服务器上拉数据  
![在这里插入图片描述][20201204145821877.png]

好巧不巧，因为今天阿里的redis服务到期，线上的分布式爬虫只能全停了。于是借着这个时间，将186的数据库全部mysqldump备份到服务器上，然后删除了数据库的所有数据，现在186和174的数据库就全部空的，用空数据库进行部署，就不会出现数据不同的问题。同步部署完成之后，在对186的数据进行恢复，这样数据也会同步还原到174.

恢复数据  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg3MDY0Ng_size_16_color_FFFFFF_t_70 2]

#### 三、Waiting for global read lock ####

在进行数据库同步部署的时候会使用到表锁的命令来停止log日志的写入，  
mysql> FLUSH TABLES WITH READ LOCK;

所有在主从复制部署完成之后，一定要将表锁释放，不然所有的数据库操作就都无法进行。

mysql> UNLOCK TABLES;

#### 四、error connecting to master、 ####

show slave status显示：Last\_IO\_Error: error connecting to master

如果前面在部署的时候出错或者因为其他的原因导致主从复制不成功，想要重新进行部署，那么可能就要对slave进行重置。  
mysql>stop slave;  
mysql>reset slave;  
然后重新添加change master to…  
mysql>start slave;  
因为前面部署导致的文件错就可以解决。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg3MDY0Ng_size_16_color_FFFFFF_t_70 3]

还有可能是因为部署的时候在主节点上忘记了创建用户和授权，这个就比较简单了。  
mysql> grant replication slave on *.* to ‘slave’@‘主服务器’ identified by ‘数据库密码’;  
mysql> FLUSH PRIVILEGES;  
这样就搞定了授权问题。

#### 五、ERROR 1819 (HY000): Your password does not satisfy the current policy requirements ####

在服务器进行授权时可能会出现以上的问题。  
mysql> set global validate\_password\_policy=0;  
mysql> set global validate\_password\_length=1;  
使用以上命令进行修改全局变量既可，  
如果出现Unknown system variable 'validate\_password\_policy’无法成功，  
想要解决也很简单，  
刷新权限  
mysql> flush privileges;  
再执行即可。  
![在这里插入图片描述][20201204150112570.png]

#### 六、The MySQL server is running with the --skip-grant-tables option so it cannot ####

这个命名出现的大概率是因为在/etc/my.cnf目录下开启了skip-grant-tables服务，这个命令可以跳过mysql的密码验证直接登录mysql，但是这样直接登录再进行修改密码的时候就会报这个错误。  
解决办法：  
刷新权限  
mysql> flush privileges;  
修改成功后，记得去将my.cnf的skip-grant-tables注释掉。  
这个命令是不是似曾相识，没错上一个错误的解决办法也是这个命令，就是这么神奇。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg3MDY0Ng_size_16_color_FFFFFF_t_70 4]

#### 七、mysql主从同步部署成功，但是数据未同步 ####

如果再部署结束后，Slave\_IO\_Running: Yes,Slave\_SQL\_Running: Yes，但是在进行数据同步测试时，数据为能同步到从数据库，  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg3MDY0Ng_size_16_color_FFFFFF_t_70 5]  
出现这样情况的大概率原因是两个数据库在部署的时候数据库数据存在不一致，在进行同步的过程中失败了，建议还是同步的时候保证两个数据库数据一致，两个空数据库同步的成功几率会大很多。

#### 关注收藏不迷路，持续更新 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg3MDY0Ng_size_16_color_FFFFFF_t_70 6]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg3MDY0Ng_size_16_color_FFFFFF_t_70]: /images/20221120/5abeb0f1562a4da6ab87bd335d327fd9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg3MDY0Ng_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20201204145533382.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg3MDY0Ng==,size_16,color_FFFFFF,t_70
[20201204145632176.png]: /images/20221120/35904a86694b4eb2b208bf59ce3d5c6f.png
[20201204145821877.png]: /images/20221120/741616b223c246ffae3ccc96751c398b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg3MDY0Ng_size_16_color_FFFFFF_t_70 2]: /images/20221120/1555c6fad107408d9372a5c5ddd7f5ab.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg3MDY0Ng_size_16_color_FFFFFF_t_70 3]: /images/20221120/2a99409a4539470d9acde84303f45125.png
[20201204150112570.png]: /images/20221120/0c9f84c868fa4a84b7460122bc67950f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg3MDY0Ng_size_16_color_FFFFFF_t_70 4]: /images/20221120/dddbcd49d2e54d879f7555eab646d8f0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg3MDY0Ng_size_16_color_FFFFFF_t_70 5]: /images/20221120/3d01a0a593a441dd909b0d6b936d9075.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg3MDY0Ng_size_16_color_FFFFFF_t_70 6]: /images/20221120/d84e14c5e6cc497392d54b80576a745c.png