mysql主从同步与读写分离

为了解决数据库服务的高可用问题以及负载均衡问题，

1正常情况下可以互为主从，均衡分担数据流量，

2防止数据库服务器在宕机的情况下可以顺利切换到正常的数据库服务器，减少公司的客户流量损失故公司需要搭建数据库集群以备不时之需。

一主一从

首先准备两台已安装好数据库的服务器：分别为A为主服务器和B从服务器

第一步初始化数据库：

1，备份A数据库服务器中所有的数据

[root@es1 ~]#mysql -uroot -p

mysql> reset master    #重置binlog日志

mysql> quit           　　#退出数据库

[root@es1 ~]# mysqldump -uroot -p --all-databases >/root/test.sql

[root@es1 ~]# ls         #查看备份结果

2在从库B中导入备份的数据

登录B服务器

[root@es2 ~]#mysql -uroot -p

mysql>drop database text；        　　# 清除所有测试数据

[root@es1 ~]# scp /root/mytest.sql root@192.168.12.118:/root/   #将数据库A中备份的数据上传至B数据库
root@192.168.12.118's password:
mytest.sql 100% 790 418.8KB/s 00:00

mysql -u root -p < /mytest.sql  将数据备份至B数据库，

第二步配置主从数据库

在AB数据库服务器数据一致，binlog还原点一致的情况下进行配置

1：配置主服务器，修改/etc/my.cnf

[root@es1 ~]# vim /etc/my.cnf

[client]
default-character-set=utf8

[mysqld]

character-set-server=utf8
validate_password_policy=0
validate_password_length=6

log-bin=mysql1-bin
server_id=1
binlog_format=MIXED

[root@es1 ~]# systemctl restart mysqld　　#重启数据库

2：新建一个用户授予器复制权限允许其从从服务器slave访问

mysql> grant select replication slave on *.* to 'replicater'@'192.168.12.%' identified by 'pwd123';

mysql> show master statusG  #查看主服务器状态 

注意 ：当在授予权限时出现密码安全问题时

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

这个就是初始设置密码策略的问题：先查看密码策略

mysql> show variables like 'validate_password%';

解决方案：

mysql> set global validate_password_mixed_case_count=2;  

关于 mysql 密码策略相关参数；
1）、validate_password_length  固定密码的总长度；
2）、validate_password_dictionary_file 指定密码验证的文件路径；
3）、validate_password_mixed_case_count  整个密码中至少要包含大/小写字母的总个数；
4）、validate_password_number_count  整个密码中至少要包含阿拉伯数字的个数；
5）、validate_password_policy 指定密码的强度验证等级，默认为 MEDIUM；
关于 validate_password_policy 的取值：
0/LOW：只验证长度；
1/MEDIUM：验证长度、数字、大小写、特殊字符；
2/STRONG：验证长度、数字、大小写、特殊字符、字典文件；
6）、validate_password_special_char_count 整个密码中至少要包含特殊字符的个数；

3 配置从服务器

vi /etc/my.cnf

[client]

[client]
default-character-set=utf8   #设置数据库客户端编码utf8

[mysqld]

character-set-server=utf8     #设置服务端编码utf8
validate_password_policy=0    #密码策略密码复杂度
validate_password_length=6　　#密码长度

log-bin=mysql2-bin            #bin-log日志前缀
server_id=2　　　　　　　　　　　　#数据库服务器主机id
binlog_format=MIXED　　　　　　#日志格式

[root@es2 ~]# systemctl restart mysqld

[root@es2 ~]# mysql -uroot -p

mysql> change master to master_host='192.168.12.119',  　　 #指定主服务器的ip地址
    ->  master_user='replicater', 　　　　　　　　　　　　　　　#指定主库授权用户用户名
    ->  master_password='12345678',　　　　　　　　　　　　 　　#授权用户密码
    ->  master_log_file='mysql1-bin.0000001',　　　　　　　　#主库bin-log日志
    ->  master_log_pos=123;　　　　　　　　　　　　　　　　　　　#指定备份节点
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> START SLAVE; //启动复制

[root@es2 ~]# ls -lh /var/lib/mysql/        注 ：master.info  ，MASTER 主服务器的设置信息自动存为 master.info 文件

mysql> show slave statusG

无论是一主一从，一主多从，互为主从，其原理都是从库指定主库

注意点，就是主从库必须要一致才能同步，否则会受中继日志和bin-log日志中的pos点的影响而无法同步，配置主从同步时4关闭防火墙。

 在配置主从同步可能遇到的问题

 报错一

Last_IO_Errno: 2003

Last_IO_Error: error connecting to master 'coolcloud@XXXX:XX' - retry-time: 60  retries: 86400

 这个就是防火墙的问题了，关闭防火墙即可

systemctl stop firewalld         关闭防火墙

systemctl disable firewalld     开机不启动防火墙

 再重新指定主库

报错二

Last_IO_Errno: 1236                 

Last_IO_Error: Got fatal error 1236 from master when reading dat

 这个问题时因为从库在指定主库配置的时候   master_log_pos=123;　#指定pos节点错误导致需查看

解决方案是：

查看主库中的状态
mysql> show master statusG 

在从库中

mysql>stop slave

change master to master_host='192.168.12.119',
 master_user='replicater',
 master_password='pwd123',
 master_log_file='mysql1-bin.000006',   # 需重新根据主库指定
 master_log_pos=154;  # 需重根据主库新指定

再启动 

 mysql>start slave

 三：总结（更多报错）

1、须在主、从服务器配置文件 /etc/my.cnf指定
server_id (任意，建议指定不易混淆，有规律，有逻辑的)
binlog日志（一般不指定时日志名称默认为主机名-bin.00000x）
binlog-format="mixed" （指定日志格式，一般为混合格式“mixed”，根据需求而定）
注意： 如果以上没有指定，都会报错错；
2、在主库上面授权：允许用户对主库有复制权限 （replication：复制）
grant replication slave on . to 用户名@"服务器地址" identified by "密码";
必须要授权（从库无法指定主库并复制）
3指定组服务器：（缺一不可）
登录数据库
change master to master_host="主服务器地址"
master_user=“主库上的授权用户“
master_password="授权账户密码“
master_log_file= " 主服务器binlog日志名称“
master_log_pos=”binlog日志偏移量”
最后启动主从复制start slave
查看重服务器状态
Slave_IO_Running: （负责与主机的io通信）
Slave_SQL_Running: （负责自己的slave 数据库进程）
如果不出问题的话，主从同步就部署成功了，

但是这世界并不太平，如果IO线程启动失败
以下是我遇到的问题：
1、主服务器的防火墙没关，导致从服务器同步失败
解决方案：关闭防火墙；
2、主从服务器数据库中数据不一致，（部署主从服务时）
先将不同的部分备份到对方的数据库中保证数据的一致
（不建议删库删库删表）
3、binlog日志偏移量不对，从服务器找不到同步节点
打开主服务器binlog日志文件，找到数据偏移量，重新指定就可以了。
如果是SQL线程启动失败：
我碰到的情况如下：
1、Last_SQL_Error: Error 'Operation DROP USER failed for 'yy'@'192.168.4.10'' on query. Default database: 'alldb'. Query: 'drop user yy@192.168.4.10'
就是没有同步之前的的主库授权用户，在部署完之后发现从库上没有之前主库上的授权用户，然后我撤销了，从库的SQL线程就断了，所以要谨慎操作。
解决办法：一般都不是删除、撤销、当然就是在从库上做同样的授权。
2、Slave failed to initialize relay log info structure from the repository
当出现这种报错时：一般原因是默认中继日志relay_log被服务器上另一个mysql slave占用了；
解决方案：

　　1、初始化中继日志， 即删除relay-log.info中继日志文件
　　2、在配置文件/etc/my.cnf 中指定中继日志名称
　　3、当配置高可用集群时，SQL线程启动失败报错如下
3、Master command COM_REGISTER_SLAVE failed: Access denied for user 'monitor'@'%' (using password: YES) (Errno: 1045)
当出现这种报错时：
　　1主服务器的级联复制功能未开启
解决方案：在配置文件中log_slave_updates # 允许级联复制，重起服务，还有是主库必须添加授权用户。
　　2还有就是删除授权用户，（不建议）。
综上所述：部署主从同步时对数据库服务器具有高度的统一性。

第三步 使用keepalived实现数据库集群的故障切换功能，实现数据库的高可用

1下载安装keepalived 

安装依赖  yum install -y pcre-devel openssl-devel popt-devel

 [root@es1 ~]# wget https://www.keepalived.org/software/keepalived-2.0.15.tar.gz

 [root@es1 ~]# tar -axvf  keepalived-2.0.15.tar.gz

 [root@es1 ~]# cd  keepalived-2.0.15

[root@es1 keepalived-2.0.15]# ./configure  --prefix=/opt/keepalived

[root@es1 keepalived-2.0.15]#make  && make install

[root@es1 keepalived-2.0.15]#systemctl start keepalived 

报错如下

journalctl -xe 查看具体原因   如图因为未找到keepalived配置文件导致

 

解决方案

[root@es1 ~]# cp -r /opt/keepalived/etc/keepalived /etc/

2keepalived配置：

keepalived配置手册：https://www.keepalived.org/manpage.html

 cat keepalived.conf  #注意主从均需安装配置keepalived  

vrrp_script chk_mysql_port {     #检测mysql服务是否在运行。有很多方式，比如进程，用脚本检测等等
    script "/opt/chk_mysql.sh"   #这里通过脚本监测
    interval 2                   #脚本执行间隔，每2s检测一次
    weight -5                    #脚本结果导致的优先级变更，检测失败（脚本返回非0）则优先级 -5
    fall 2                    #检测连续2次失败才算确定是真失败。会用weight减少优先级（1-255之间）
    rise 1                    #检测1次成功就算成功。但不修改优先级
}
vrrp_instance VI_1 {
    state MASTER
    interface ens33 #指定虚拟ip的网卡接口,不一定是eth0根据ifconfig确定
    virtual_router_id 51 #路由器标识，MASTER和BACKUP必须是一致的
    priority 100 #定义优先级，数字越大，优先级越高，在同一个vrrp_instance下，MASTER的优先级必须大于BACKUP的优先级。这样MASTER故障恢复后，就可以将VIP资源再次抢回来
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 123456
    }
    virtual_ipaddress {
        192.168.11.25
    }
    track_script {
       chk_mysql_port
    }
}

编写监控脚本

cat /opt/chk_mysql.sh

#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]
then
    /etc/init.d/keepalived stop
else
   echo "running..." >> /opt/keepalived-running-info.log
   sleep 5000
fi

启动keepalived服务

 systemctl start keepalived

2017-12-30 18:51:10