mysql 主从复制实验，主主高可用

异步复制（默认复制）

主数据库在完成后会立即返回给客户端，不管从数据库有没有同步上，这也就导致如果主数据库在返回后down掉时，从数据库有可能没有数据，导致从数据库替代后，没有上一条数据

• 每个事务在更新完成数据之前，master会在二进制日志（binary log，binlog）中记录sql语句，slave开启i/o线程，将sql语句写入自己的日志，并重新识别

• 所有的从数据库版本都要高于主数据库

• 状态：show slave status;

实验

MASTER
两台虚拟机10,11
关闭防火墙三件套
yum -y install ntp  #同步时间
vim /etc/ntp.conf
server 127.127.1.0
fudge 127.127.1.0 stratum 8

systemctl enable ntpd  #开机自启
systemctl start ntopd

yum -y install ntpdate  #时间模块
/user/sbin/ntpdate 192.168.200.11  #同步从数据库时间

vim /etc/my.cnf  #修改配置文件
server-id=1
log-bin=mysql-binlog
log-slave-updates=true

systemctl start mariadb  #启动

mysql
#打开主数据库授权方便从连接
grant replication slave on *.* to 'myslave'@'192.168.200.%' identified by '123123';
flush privileges;  #刷新授权表

show master status;  #查看master状态

slave

mysql -uroot -p123123 -h 192.168.200.10  #测试连接主数据库

vim /etc/my.cnf  #修改配置文件
server-id=2  
relay-log=relay-log-bin
relay-log-index=slaver-relay-bin.index

systemctl restat mariadb
stop slave;  #停止slave
#授权用户
change master to master_host='192.168.200.10',master_user='myslave',master_password='123123',master_log_file='mysql-binlog.0.000003',master_log_pos=522;
start slave;
show slave status\G

配置解析

change master to
master_host  #主服务器IP地址
master_user  #主服务器授权用户
master_password  #主服务器授权密码
master_log_file  #主服务器二进制日志文件名
master_log_pos  #日志文件开始位置

• 如果连接后没有同步数据：先暂停slave set global_slave_skip_counter=1 在启动

mysql 主主+keepalived

思路

• 两台mysql都可以读写，互为主备，默认使用一台masterA，负责数据的写入，另一台备用
• masterA是masterB的主库，B又是A的主库
• 使用Keepalived的VIP作为条件连接

缺点

• masterB会一直处于空闲的状态
• 从库要等masterB完成之后再去复制，会有一定时间的延迟

实验

三台机器10,11,12(10,12互为主)
关闭防火墙三件套
vim /etc/my.cnf  #配置文件

mysql
grant replication slave on *.* to 'repl'@'192.168.200.11' identified by '123123';  #授权
show master status;    #查看点
+---------------------+----------+--------------+------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| mysql-binlog.000005 |      548 |              |                  |
+---------------------+----------+--------------+------------------+

##配置11
vim /etc/my.cnf

grant replication slave on *.* to 'repl'@'192.168.200.11' identified by '123123';
MariaDB [(none)]> show master status;
+---------------------+----------+--------------+------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------------+----------+--------------+------------------+
| mysql-binlog.000001 |      786 |               |                  |
+---------------------+----------+--------------+------------------+

stop slave;
change master to master_host='192.168.200.11',master_port=3306,master_user='repl',master_password='123123',master_log_file='mysql-binlog.000001',master_log_pos=786;
show slave status \G

#masterB
grant replication slave on *.* to 'repl'@'192.168.200.14' identified by '123123';
MariaDB [(none)]> show master status;
stop slave;
change master to master_host='192.168.200.14',master_port=3306,master_user='repl',master_password='123123',master_log_file='mysql-binlog.000001',master_log_pos=786;
show slave status \G

##当两台的id，sql线程为yes时成功

测试

masterA配置代码

[mysqld]
server-id=1
log-bin=mysql-binlog				#打开二进制功能
log-slave-updates=true
max_binlog_size=1024M			#binlog单文件最大值	
auto_increment_offset = 1
auto_increment_increment = 2		#奇数ID

replicate-ignore-db = information_schema	#忽略不同步主从的数据库
replicate-ignore-db = performance_schema
replicate-ignore-db = test
replicate-ignore-db = mysql

max_connections = 3000
max_connect_errors = 30

skip-character-set-client-handshake	#忽略应用程序想要设置的其他字符集
init-connect='SET NAMES utf8'		#连接时执行的SQL
character-set-server=utf8			#服务端默认字符集
wait_timeout=1800				#请求的最大连接时间
interactive_timeout=1800			#和上一参数同时修改才会生效
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES	#sql模式

relay-log=relay-log-bin				#开启中继日志
relay-log-index=slave-relay-bin.index

masterB配置代码

[mysqld]
server-id=2
log-bin=mysql-binlog
log-slave-updates=true
max_binlog_size=1024M
auto_increment_offset = 2
auto_increment_increment = 2

replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = test
replicate-ignore-db = mysql

max_connections = 3000
max_connect_errors = 30

skip-character-set-client-handshake
init-connect='SET NAMES utf8'
character-set-server=utf8
wait_timeout=1800
interactive_timeout=1800
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index

Keepalived高可用

yum -y install keepalived  #下载Keepalived
vim /etc/keeplived/keeplived.conf  #配置文件

vim /opt/mysql.sh  #写入脚本
chmod +X /opt/mysql.sh  #给脚本授权
systemctl start keepalived  
ip a | grep 254

#masterB同时写入mysql.sh与my.cnf配置文件的优先级改为99，id改为2


#测试：重启mariadb，Keepalived

slave：mysql -uroot -p123123 -h 192.168.200.254
关闭masterA的mysql查看masterB的254

Keepalived配置文件

! Configuration File for keepalived

global_defs {
   router_id LVS_MASTER-A
}

vrrp_script mysql {
    script "/opt/mysql.sh"
    interval 2
    weight -5                 
    fall 2                 
    rise 1
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens32
    virtual_router_id 51
priority 100
nopreempt
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script {
        mysql
    }
    virtual_ipaddress {
        192.168.200.254
    }
}

mysql.sh脚本

#!/bin/bash
counter=$(netstat -na|grep 'LISTEN'|grep '3306' |wc-l)
if ['${counter}' -eq 0 ]; then
        systemctl stop keepalived
fi