记录deepin系统安装MySQL5.7+Keepalived实现双主热备的配置信息

最新推荐文章于 2023-10-16 11:46:44 发布

wang_bin__

最新推荐文章于 2023-10-16 11:46:44 发布

阅读量285

点赞数

分类专栏： MySQL 文章标签： mysql linux deepin

本文链接：https://blog.csdn.net/chuckall/article/details/106109803

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

记录deepin系统安装MySQL5.7+Keepalived实现双主热备的配置信息

mysql主主备份原理：

复制过程中一个服务器充当主服务器，另一个或多个充当从服务器，主服务器将更新写入二进制日志，并维护日志文件的一个索引以跟踪日志循环。当一个从服务器连接到主服务器时，它通知主服务器上次更新的位置，从服务器就接收主服务器从那时起发生的变更，然后封锁并等待主服务器通知下一次更新。

双主热备原理：

MySQL都对外提供服务。但是当一台MySQL宕机后，另一台会马上接管宕机的MySQL的访问。当宕机的MySQL修好了之后又对重新将业务还给他。

环境准备：
虚拟机中安装两台deepin-20Beta-desktop-amd64版本系统，

网络适配器选择NAT模式，IP选择手动模式设置成静态IP，

deepin-20Beta-desktop-amd64

文章目录

记录deepin系统安装MySQL5.7+Keepalived实现双主热备的配置信息
@[toc]
一.安装MySQL5.7
二.修改MySQL配置文件配置主主备份
三.安装并配置Keepalived
主机deepin1上的文件：
从机deepin2 上的文件：

一.安装MySQL5.7

进入etc/apt/目录中，备份sources.list文件（可复制到桌面）

执行命令修改文件内容：

（按键o是进入编辑，修改完后输入 :wq 保存并退出）

sudo vim /etc/apt/sources.list
#删除内容，并添加以下内容：
## Generated by deepin-installer
deb [by-hash=force] https://mirrors.tuna.tsinghua.edu.cn/deepin panda main contrib non-free

执行命令：

sudo apt-get update
sudo apt-get upgrade

更换源成功！

修改过源过后，在终端中输入：sudo apt-get install mysql-server即可安装成功(中途提示选择，输入y即可)。

安装完成过后，需要进入mysql中，修改原本的密码，由于LZ的实例中使用的是清华的源，那么需要在以下文件中找到对应的用户名和密码。

输入命令 sudo cat /etc/mysql/debian.cnf可查看到用户名和密码,显示如下：

master@master-PC:~/Desktop$ sudo cat /etc/mysql/debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = z6uKUZ3XgL5YHIaj
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = z6uKUZ3XgL5YHIaj
socket   = /var/run/mysqld/mysqld.sock

输入上面查看到的用户名和密码:

mysql -udebian-sys-maint -pz6uKUZ3XgL5YHIaj

运行显示如下：

master@master-PC:~/Desktop$ mysql -udebian-sys-maint -pz6uKUZ3XgL5YHIaj
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.21-1 (Debian)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

修改MySQL用户名密码：

use mysql;
update user set plugin="mysql_native_password",authentication_string=password('root') where user="root";

刷新缓存：

FLUSH PRIVILEGES;

修改MySQL默认字符编码：

set character_set_database=utf8;
set character_set_server=utf8;
set global character_set_database=utf8;
set global character_set_server=utf8;

查看当前字符编码：

show variables like 'character_set_%';

退出MySQL：exit

mysql> exit
Bye
master@master-PC:~/Desktop$

查看MySQL配置文件：

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

重启MySQL：

sudo systemctl restart mysql.service

然后使用修改后的密码登录MySQL：

mysql -uroot -p123456

修改MySQL允许远程链接：

select user,host from user;
update user set host="%" where user="root";

然后使用navicat 测试链接成功！

sudo apt-get install keepalived;

二.修改MySQL配置文件配置主主备份

deepin1： 192.168.89.101

deepin2： 192.168.89.102

进入要修改的MySQL配置文件：

sudo vim /etc/mysql/my.cnf

修改 192.168.89.101 的MySQL配置信息：

[mysqld]
datadir=/var/lib/mysql
user=mysql
port = 3306
socket=/var/run/mysqld/mysqld.sock
log-bin=mysql-bin
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
report-port=3306
report-host=192.168.89.101
#replicate-same-server-id #主要用于同时写的情况
server_id = 1      #从写 server_id = 2
log-slave-updates
slave-skip-errors=all
auto_increment_increment=2
auto_increment_offset=1     #从写 auto_increment_offset = 2

修改 192.168.89.102 的MySQL配置信息：

[mysqld]
datadir=/var/lib/mysql
user=mysql
port = 3306
socket=/var/run/mysqld/mysqld.sock
log-bin=mysql-bin
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
report-port=3306
report-host=192.168.89.102
#replicate-same-server-id #主要用于同时写的情况
server_id =2     
log-slave-updates
slave-skip-errors=all
auto_increment_increment=2
auto_increment_offset=2

分别重启 MySQL：

sudo systemctl restart mysql.service

使用root账号登录：

mysql -uroot -proot

在 192.168.89.101 上运行：

grant replication slave on *.* to 'slave'@'192.168.89.102' identified by 'root';
show master status\G;

在 192.168.89.102 上运行：

grant replication slave on *.* to 'slave'@'192.168.89.101' identified by 'root';
show master status\G;

在 192.168.89.101 上运行：

（master_log_file=‘mysql-bin.000001’,master_log_pos=448;）这两条对应的信息为另一台主机上执行show master status\G;后所显示的信息

change master to master_host='192.168.89.135',master_user='slave',
master_password='root',master_log_file='mysql-bin.000001',
master_log_pos=448;
start slave;
show slave status\G;

在 192.168.89.102 上运行：

change master to master_host='192.168.89.134',master_user='slave',
master_password='root',master_log_file='mysql-bin.000001',
master_log_pos=448;
start slave;
show slave status\G;

三.安装并配置Keepalived

安装命令：

sudo apt-get install keepalived;

用xshell将三个文件分别传到两台服务器上

获取 etc/keepalived文件夹的权限，把check_mysql.sh和keepalived.conf放到keepalived下，

mysql.sh为启动运行脚本，因为必须先启动MySQL再启动keepalived。

获取root权限：

su root

密码

权限更改，777相当于完全控制权限：

更改一个文件夹或文件的权限：chmod 777 文件路径/文件名称

更改文件夹下所有文件的权限：sudo chmod -R 777 文件夹

sudo chmod -R 644 恢复

主机deepin1上的文件：

check_mysql.sh

#!/bin/bash

counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)

if [ "${counter}" -eq 0 ]; then
  echo 123456 | sudo systemctl stop keepalived.service
  echo -e "\e[1;31m mysql 3306 端口失效,已关闭keepalived服务 \e[0m"
fi

keepalived.conf

global_defs {

        router_id MASTER-HA

}
vrrp_script check_mysql_port {            

    script "/etc/keepalived/check_mysql.sh"        

    interval 2                       

    weight -5                       

    fall 2

    rise 1

}

vrrp_instance VI_1 {

    state MASTER

    interface ens33     #指定虚拟ip的网卡接口
    virtual_router_id 51      

    priority 101  #定义优先级，数字越大，优先级越高主机的优先级必须大于从机的优先级

    advert_int 1         

    authentication {    

        auth_type PASS

        auth_pass java

    }

    virtual_ipaddress {  

        192.168.32.100    #这里为对外映射的IP

    }

    track_script {      

        check_mysql_port             

    }

}

mysql.sh

#! /bin/sh
#
#/bin/bash
#init
Port=3306
Pass="123456"   #系统密码
CmdPath="/etc/init.d"

#startup fnction
start()
{
	if [ `netstat -lnt|grep "$Port"|wc -l` -eq 0 ]
		then
			printf "Starting Mysql...\n"
			echo ${Pass} | sudo -S ${CmdPath}/mysql start
			echo -e "\e[1;31m mysql服务启动 in... \e[0m"
             sleep 5
			startKeepalived

	else
		printf "Mysql is running...\n"
	fi
}

#stop function
stop()
{
	if [ !  `netstat -lnt|grep "$Port"|wc -l` -eq 0 ]
		then
			printf "Stopping Mysql...\n"
			echo ${Pass} | sudo -S ${CmdPath}/mysql stop
	else
		printf "Mysql is stopped...\n"
	fi
}

#restart funcation
restart()
{
	printf "Restarting Mysql...\n"
	stop
	sleep 2
	start
}
startKeepalived()
{
	echo ${Pass} | sudo -S ${CmdPath}/keepalived start
	echo -e "\e[1;31m Keepalived 服务启动  \e[0m"
}

case "$1" in
start)
	start
	;;
stop)
	stop
	;;
restart)
	restart
	;;
*)
	printf "Usage: $0 {start|stop|restart}\n"
esac

exit 0

从机deepin2 上的文件：

check_mysql.sh

#!/bin/bash

counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)

if [ "${counter}" -eq 0 ]; then

        echo 123456 | sudo systemctl stop keepalived.service
        echo -e "\e[1;31m mysql 3306 端口失效,已关闭keepalived服务 \e[0m"
fi

keepalived.conf

global_defs {

        router_id MASTER-HA

}

 

vrrp_script check_mysql_port {            

    script "/etc/keepalived/check_mysql.sh"        

    interval 2                       

    weight -5                       

    fall 2

    rise 1

}

 

vrrp_instance VI_1 {

    state BACKUP

    interface ens33  #指定虚拟ip的网卡接口

    virtual_router_id 51      

    priority 10         #定义优先级，数字越大，优先级越高主机的优先级必须大于从机的优先级

    advert_int 1         

    authentication {    

        auth_type PASS

        auth_pass java

    }

    virtual_ipaddress {  

        192.168.32.100   #这里为对外映射的IP

    }

    track_script {      

        check_mysql_port             

    }

}

my_mysql.sh

#! /bin/sh
#
#/bin/bash
#init
Port=3306
Pass="123456" #系统密码
CmdPath="/etc/init.d"

#startup fnction
start()
{
	if [ `netstat -lnt|grep "$Port"|wc -l` -eq 0 ]
		then
			printf "Starting Mysql...\n"
			echo ${Pass} | sudo -S ${CmdPath}/mysql start
			echo -e "\e[1;31m mysql服务启动 in... \e[0m"
             sleep 5
			startKeepalived

	else
		printf "Mysql is running...\n"
	fi
}

#stop function
stop()
{
	if [ !  `netstat -lnt|grep "$Port"|wc -l` -eq 0 ]
		then
			printf "Stopping Mysql...\n"
			echo ${Pass} | sudo -S ${CmdPath}/mysql stop
	else
		printf "Mysql is stopped...\n"
	fi
}

#restart funcation
restart()
{
	printf "Restarting Mysql...\n"
	stop
	sleep 2
	start
}
startKeepalived()
{
	echo ${Pass} | sudo -S ${CmdPath}/keepalived start
	echo -e "\e[1;31m Keepalived 服务启动  \e[0m"
}

case "$1" in
start)
	start
	;;
stop)
	stop
	;;
restart)
	restart
	;;
*)
	printf "Usage: $0 {start|stop|restart}\n"
esac

exit 0

配置完毕。