|
我们通常说的双机热备是指两台机器都在运行,但并不是两台机器都同时在提供服务。当提供服务的一台出现故障的时候,另外一台会马上自动接管并且提供服务,而且切换的时间非常短
MySQL双主复制,即互为Master-Slave(默认只使用一台MasterA负责数据写入,另一台MasterB备用),可以实现数据库服务器的热备,但是一台Master宕机后不能实现动态切换。
使用Keepalived,可以通过虚拟IP,实现双主对外的统一接口以及自动检查、失败切换机制,从而实现MySQL数据库的高可用方案。
一、Mysql安装、配置和安全启动
把mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz上传到/usr/local/src/
1、mysql安装- cd /usr/local/src/
- tar -zxvf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
- mkdir -p /usr/local/mysql/
- mv mysql-5.7.28-linux-glibc2.12-x86_64 /usr/local/mysql/mysql-5.7.28
复制代码 2、创建mysql用户组和用户- cd /usr/local
- groupadd mysql
- useradd -r -g mysql -s /bin/false mysql
- chown -R mysql:mysql mysql
复制代码 3、初始化数据- cd /usr/local/mysql/mysql-5.7.28
- bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/mysql-5.7.28/ --datadir=/data1/mysql/data --lc_messages_dir=/usr/local/mysql/mysql-5.7.28/share --lc_messages=en_US
- bin/mysql_ssl_rsa_setup --datadir=/data1/mysql/data
复制代码 4、修改配置- vi /etc/my.cnf
- basedir=/usr/local/mysql/mysql-5.7.28
- datadir=/data1/mysql/data
复制代码 5、配置环境变量- #编辑环境变量 .bash_profile表示当前用户个人配置 只对当前用户生效
- vi ~/.bash_profile
-
- #将mysql的bin目录配置到环境变量中
- PATH=$PATH:$HOME/bin:/usr/local/mysql/mysql-5.7.28/bin
- export PATH
-
- #使配置修改后立即生效
- source ~/.bash_profile
复制代码 6、修改密码、配置远程访问和安全启动- #启动前先设置免密登录
- vi /etc/my.cnf
- #添加如下配置并保存 跳过权限验证
- skip-grant-tables=1
-
- #安全启动
- cd /usr/local/mysql/mysql-5.7.28/bin
- ./mysqld_safe --user=mysql &
-
- #修改密码
- #进入mysql
- mysql
- use mysql
- update user set authentication_string=password("123456") where user="root";
- #刷新权限表
- flush privileges;
-
- #配置远程访问
- use mysql
- #允许所有机器访问root用户 %代表所有机器
- update user set host='%' where user='root';
- #授权所有机器的root用户拥有所有数据库的所有权限 远程访问密码root %代表所有机器
- grant all privileges on *.* to root@'%' identified by 'root';
- #刷新权限表
- flush privileges;
-
- #退出mysql
- exit
-
- #安全关闭
- ./mysqladmin -uroot -p shutdown
-
- #删除免密登录配置
- vi /etc/my.cnf
- #删除skip-grant-tables=1并保存
-
- #安全启动
- ./mysqld_safe --user=mysql &
复制代码 二、 两台Mysql配置双主
1、创建主从同步replication用户
2、授权
192.168.0.101- #授权IP192.168.0.102的从数据库replication用户拥有所有数据库的同步权限 远程访问密码replication
- grant replication slave on *.* to 'replication'@'192.168.0.102' identified by 'replication';
- #刷新权限表
- flush privileges;
复制代码 192.168.0.102- #授权IP192.168.0.101的从数据库replication用户拥有所有数据库的同步权限 远程访问密码replication
- grant replication slave on *.* to 'replication'@'192.168.0.101' identified by 'replication';
- #刷新权限表
- flush privileges;
复制代码 3、添加配置文件- [mysqld]
- basedir=/usr/local/mysql/mysql-5.7.28
- datadir=/data1/mysql/data
-
- server-id = 101
-
- skip-external-locking
-
- log_bin = mysql-binlog
- binlog_do_db = sample
- binlog-ignore-db = mysql
- binlog-ignore-db = performance_schema
- binlog-ignore-db = information_schema
-
- binlog_group_commit_sync_delay = 100000 # 单位微妙
- binlog_group_commit_sync_no_delay_count = 20 # 单位每组事物数量
-
- #设置复制类型
- slave-parallel-type=LOGICAL_CLOCK
- #设置并行数量
- slave-parallel-workers=4
-
- #主主需要加的部分
- replicate-do-db = sample
-
- log-slave-updates=true
- #sync_binlog=1
- auto_increment_offset=1
- auto_increment_increment=2
-
- innodb_buffer_pool_size = 12G
- innodb_buffer_pool_instances = 8
- innodb_buffer_pool_chunk_size = 128M
- innodb_log_file_size = 256M
- innodb_log_buffer_size = 32M
- innodb_flush_method = O_DIRECT
- innodb_lock_wait_timeout = 120
-
- slow_query_log = ON
- slow_query_log_file = /usr/local/mysql/log/slow.log
- long_query_time = 5
-
- sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
-
- secure_file_priv=/usr/local/mysqlcsvfiles/
-
- expire_logs_days=7
- max_binlog_size=1024M
-
- !includedir /etc/my.cnf.d
复制代码 4、添加之后- mkdir -p /usr/local/mysql/log
- mkdir -p /usr/local/mysqlcsvfiles
复制代码 5、安全关闭- ./mysqladmin -uroot -p shutdown
复制代码 6、安全启动- ./mysqld_safe --user=mysql &
复制代码 7、进入mysql执行- #进入mysql执行
- show master status\G;
-
- #分别记录主的binlog位置,在101的角度上102为主,在102的角度上,101为主
- File:mysql-bin.000001
- Position: 154
-
- #设置同步关系(两个主都要设置)
- #其中master_log_file和master_log_pos要登录到对方机器执行show master status\G;去查看
- stop slave;
- (在192.168.0.101)
- change master to master_host='192.168.0.102',master_user='replication',master_password='replication',master_log_file='mysql-bin.000001',master_log_pos=154;
- (在192.168.0.102)
- change master to master_host='192.168.0.101',master_user='replication',master_password='replication',master_log_file='mysql-bin.000001',master_log_pos=154;
-
- #分别启动slave
- start slave;
复制代码 三、安装Keepalived
1、安装编译依赖包- yum install -y gcc openssl-devel libnl libnl-devel libnfnetlink-devel
复制代码 2、安装Keepalived- #解压keepalived压缩包放到/usr/local/并重命名为keepalived
- cd /usr/local/src
- tar -zxvf keepalived-2.0.7.tar.gz
- mv keepalived-2.0.7 /usr/local/keepalived
-
- #配置keepalived 得到一个Makefile的文件夹
- #--prefix:keepalived安装目录
- #--sysconf:keepalived的核心配置文件,必须要在/etc目录下面,改为其他位置会导致启动不了,不配置在该目录下的话,启动keepalived时日志文件里面会报错,显示找不到/etc这个文件夹
- cd /usr/local/keepalived
- ./configure --prefix=/usr/local/keepalived/ --sysconf /etc
-
- #编译和安装keepalived
- make && make install
-
- #创建keepalived软链接 /usr/sbin/如果存在keepalived就先删除
- ln -s /usr/local/keepalived/sbin/keepalived /usr/sbin/
-
- #复制keepalived脚本文件到/etc/init.d/目录下
- cd keepalived
- cp /usr/local/keepalived/etc/init.d/keepalived /etc/init.d/
-
- #设置Keepalived开机自启动
- chkconfig --add keepalived
- chkconfig keepalived on
-
- #启动keepalived
- service keepalived start
复制代码 四、配置Keepalived
1、编辑keepalived配置文件- vi /etc/keepalived/keepalived.conf
复制代码 2、主服务器添加对应配置文件- global_defs {
- router_id LVS_LEVEL1 #主服务器名称
- }
-
- vrrp_script check_run {
- script "/etc/keepalived/mysql_check.sh"
- interval 5 #5秒执行一次脚本
- }
-
- vrrp_instance VI_1 {
- state MASTER #主服务器
- interface eth0 #承载VIP地址到物理接口
- virtual_router_id 51 #虚拟路由器ID号,每个热播组保持一致
- priority 100 #优先级,数值越大优先级越高
- advert_int 1 #检查间隔,默认为1s
- authentication { #认证信息,每个热播组保持一致
- auth_type PASS #认证类型
- auth_pass 1111 #密码字串
- }
- virtual_ipaddress {
- 192.168.0.144 #VIP地址(内网地址)
- }
- track_script {
- check_run
- }
- }
复制代码 3、备份服务器添加对应配置文件- global_defs {
- router_id LVS_LEVEL2 #备份服务器名称
- }
- vrrp_script check_run {
- script "/etc/keepalived/mysql_check.sh"
- interval 5 #5秒执行一次脚本
- }
- vrrp_instance VI_1 {
- state BACKUP #备份服务器
- interface eth0 #承载VIP地址到物理接口
- virtual_router_id 51 #虚拟路由器ID号,每个热播组保持一致
- priority 50 #优先级,数值越大优先级越高
- advert_int 1 #检查间隔,默认为1s
- authentication { #认证信息,每个热播组保持一致
- auth_type PASS #认证类型
- auth_pass 1111 #密码字串
- }
- virtual_ipaddress {
- 192.168.0.144 #VIP地址(和主服务器设置一样)
- }
- track_script {
- check_run
- }
- }
复制代码 附mysql_check.sh- #!/bin/bash
- /usr/bin/mysql -uroot -p'你自己的数据库密码' -e "show status" &>/dev/null
- if [ $? -ne 0 ] ;then
- systemctl stop keepalived
- fi
复制代码- chmod +x /etc/keepalived/mysql_check.sh
复制代码 4、重启keepalived
以上为个人经验,希望能给大家一个参考,也希望大家多多支持脚本之家。
来源:https://www.jb51.net/article/266298.htm
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作! |
|