搭建mariadb基于keepaived高可用的主从复制-读写分离

1.测试环境

ip	role
192.168.159.137	主
192.168.159.134	从

yum install -y mariadb mariadb-server
#在配置文件加上本地socket配置
[mysql]
socket=/var/lib/mysql/mysql.sock


#修改密码方式1
mysql -uroot -p
set password=password("123456");

#设置密码方式2
mysql_secure_installation
回车以后提示：
enter current password for root (enter for none):  ## 如果是初次运行直接回车，因为没有密码
set root password?[Y/n]   #是否设置root用户密码，输入y回车
new password:  #设置root用户密码
re-enter new password:   #国际惯例再输入一次密码
remove anonymous users?[Y/n]  #是否删除匿名用户
disallow root login remotely?[Y/n]  #是否禁止root用户远程登录
remove test database and access to it?[Y/n]   #是否删除test数据库
reload privilege tables now?[Y/n]   # 是否重新加载权限表

#退出重启服务
quit
systemctl restart mariadb
#设置开机自启
systemctl enable mariadb


>假如有数据
>reset master;  //重置binlog日志
>quit;
mysqldump -u root -p --all-databases > /root/mysql.sql   //备份主服务器库
scp /root/mysql.sql root@192.168.159.134:/root/  //将备份文件拷贝到从服务器
mysql -u root -p < /root/mysql.sql　//将备份数据库导入从服务器MariaDB

2.修改配置文件

vim /etc/my.cnf

主服务器配置

从服务器配置

进入主服务器mariadb界面

#查看配置是否生效
 show variables like 'server_id' ;
 show master status;
#授权给从服务器
grant replication slave on *.* to 'root'@'%' identified by '123456';

进入从服务器mariadb界面

#查看配置是否生效
 show variables like 'server_id' ;
 show slave status;
#连接到主服务器
 stop slave;
change master to master_host='192.168.159.137',master_user='root',master_password='123456';
start slave;

3.验证主从复制

show slave status\G
出现下图,则表明部署成功

4.实现读写分离

从服务器作为主服务器的备份, 避免写入冲突, 在采用主、从复制结构时, 为保持主、从数据库的一致性, 用户不能在从服务器上执行数据库写入操作.
可以通过my.cnf将从服务器配置为只读模式. 在只读模式下, 只有super权限的用户和slave同步线程才能写入.

vim /etc/my.cnf
[mysql]
read-only=on  //设置从服务器为只读模式
systemctl restart mariadb   //重启服务

5.使用mysql-router实现读写分离

准备一台布置中间件的主机:192.168.159.138

安装mysql-router

wget http://dev.mysql.com/get/Downloads/MySQL-Router/mysql-router-2.0.3-1.el7.x86_64.rpm
rpm -ivh mysql-router-2.0.3-1.el7.x86_64.rpm

配置

[DEFAULT]
logging_folder = /opt/mysqlrouter/logs

[logger]
level = INFO

[routing:master]
bind_address = 192.168.159.138:7001
destinations = 192.168.159.137:3306
mode = read-write
max_connections = 1024
max_connect_errors = 100 
client_connect_timeout = 9

[routing:slave]
bind_address = 192.168.159.138:7002
destinations = 192.168.159.134:3306
mode = read-only
max_connections = 65535
max_connect_errors = 100 
client_connect_timeout = 9

启动

systemctl start mysqlrouter
netstat -antupl |grep mysqlrouter

测试

在布置中间件的主机进入mariadb
grant all on *.* to 'root'@'%' identified by '123456';
 flush privileges;
退出后,在命令行输入
 mysql -uroot -h192.168.159.138 -P7001 -p123456 -e 'select @@server_id;'
 ![在这里插入图片描述](https://img-blog.csdnimg.cn/20200227213341519.png)
 >如果想远程连接中间件主机,修改配置文件vim /etc/my.cnf
 >[mysqld]
 >...
 >bind_address = 0.0.0.0
 >...

搭建基于keepalived的高可用集群

需要再安装一台mariadb服务器(可不安装mariadb,但不能进行远程连接了),小编使用的是192.168.159.136,并且安装上mysql-router,并且配置一样

安装keepalived

yum install keepalived -y

修改配置文件

> 主:

! Configuration File for keepalived

global_defs {
  notification_email {
       root@localhost
  }
  notification_email_from keepalived01@localhost
  smtp_server 127.0.0.1
  smtp_connect_timeout 30
  router_id keepalived01
  vrrp_skip_check_adv_addr
  #vrrp_strict #一定要注释掉
  vrrp_garp_interval 0
  vrrp_gna_interval 0
}

vrrp_instance VI_1 {
   state MASTER
   interface ens33
   virtual_router_id 51
   priority 100
   advert_int 1
   authentication {
       auth_type PASS
       auth_pass 1111
   }
   virtual_ipaddress {
       192.168.159.200 #要同一局域网上
   }
}

>备:

! Configuration File for keepalived

global_defs {
  notification_email {
       root@localhost
  }
  notification_email_from keepalived02@localhost
  smtp_server 127.0.0.1
  smtp_connect_timeout 30
  router_id keepalived02
  vrrp_skip_check_adv_addr
  #vrrp_strict
  vrrp_garp_interval 0
  vrrp_gna_interval 0
}

vrrp_instance VI_1 {
   state BACKUP
   interface ens33
   virtual_router_id 51
   priority 99
   advert_int 1
   authentication {
       auth_type PASS
       auth_pass 1111
   }
   virtual_ipaddress {
       192.168.159.200
   }
}

修改mysqlrouter配置

把bind_address地址改为VIP的地址或者0.0.0.0
之后重启服务