前言
菜鸟练手作,该踩的坑都踩了,留下记录以备后用。
版本:
mysql 8.0.24 https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.24-1.el7.x86_64.rpm-bundle.tar
其他版本可以在MySQL :: Download MySQL Community Server (Archived Versions)选择系统和版本后找到下载
bundle即为完整安装包,也可以找到下面的安装项逐个下载,其中资源名‘mysql-8.0.24-1.el8.x86_64.rpm-bundle.tar’内的el8代表支持的系统版本
keepalived 1.2.7 https://www.keepalived.org/software/keepalived-1.2.7.tar.gz
一、安装过程:
将mysql和keepalived安装包移入linux虚拟机,各自解压后以备使用,操作如下:
1.删除冲突前置
因linux系统可能自带mysql的分支mariaDB,与mysql冲突,所以要先查找是否有mariadb并删除
rpm -qa | grep mariadb
#返回查找结果
mariadb-XXX-XXX
#逐个删除
rpm -e --nodeps mariadb-XXX-XXX
rpm -qa | grep mariadb
#无返回结果说明已删除干净
2.安装mysql
进入mysql解压出的文件目录
[root@localhost ~]# cd /XXX/mysql-8.0.24-1.el7.x86_64.rpm-bundle/
#进入解压出的目录
[root@localhost mysql-8.0.24-1.el7.x86_64.rpm-bundle]# ls
mysql-community-client-8.0.24-1.el7.x86_64.rpm
mysql-community-client-plugins-8.0.24-1.el7.x86_64.rpm
mysql-community-common-8.0.24-1.el7.x86_64.rpm
mysql-community-devel-8.0.24-1.el7.x86_64.rpm
mysql-community-embedded-compat-8.0.24-1.el7.x86_64.rpm
mysql-community-libs-8.0.24-1.el7.x86_64.rpm
mysql-community-libs-compat-8.0.24-1.el7.x86_64.rpm
mysql-community-server-8.0.24-1.el7.x86_64.rpm
mysql-community-test-8.0.24-1.el7.x86_64.rpm
#使用rpm工具按顺序安装
rpm -ivh mysql-community-common-8.0.24-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.24-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.24-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.24-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.24-1.el7.x86_64.rpm
#剩余的文件不必要安装
3.安装keepalived
进入keepalived的解压文件夹
# cd /XXX/keepalived-1.2.7
[root@localhost keepalived-1.2.7]# ./configure --prefix=/usr/local/keepalived --sysconf=/etc
安装如遇到问题可参考处理keepalived 安装教程(centOS 7.x)_centos7安装keepalived-CSDN博客
二、配置mysql
1.设置启动
设置mysql开机自启动并启动mysql
[root@localhost /]# systemctl enable mysqld #设置自启动
[root@localhost /]# systemctl start mysqld #启动mysql进程
#查看进程状态
[root@localhost /]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 一 2023-11-06 23:10:03 CST; 26min ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 1666 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 2488 (mysqld)
Status: "Server is operational"
Tasks: 38
CGroup: /system.slice/mysqld.service
└─2488 /usr/sbin/mysqld
11月 06 23:09:45 localhost.localdomain systemd[1]: Starting MySQL Server...
11月 06 23:10:03 localhost.localdomain systemd[1]: Started MySQL Server.
#Active: active (running)证明启动成功
2.进入mysql
因为mysql初始会生成临时密码,需要用临时密码进入再修改密码使用
[root@localhost /]# grep 'temporary password' /var/log/mysqld.log
#查找mysql日志内的临时密码
2023-11-06T15:09:56.187693Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: uss!wk.fk6fN
# 密码为 uss!wk.fk6fN
[root@localhost /]# mysql -uroot -p
Enter password: #弹出本条后可粘贴密码后enter进入mysql,此处无输入反馈
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.24
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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.
接下来修改root用户并赋权
# 修改root账号的密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '1';#
# 创建slave账号以供同步使用
CREATE USER 'slave'@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '1';
# 对USER账号授权
GRANT ALL ON *.* TO 'slave'@'%';
# 刷新权限
FLUSH PRIVILEGES;
为操作简单,设定了非常简单的密码,如密码策略不允许可能返回错误
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
如果需要设定简易密码就需要修改密码策略,可通过以下命令更改后再修改密码
#密码至少要包含的小写字母个数和大写字母个数
mysql>set global validate_password.mixed_case_count=0;
#密码至少要包含的数字个数。
mysql>set global validate_password.number_count=0;
#密码至少要包含的特殊字符数
mysql>set global validate_password.special_char_count=0;
#密码长度
mysql>set global validate_password.length=0;
3.更改数据库配置文件my.cnf
[root@localhost /]# vim /etc/my.cnf #打开mysql配置文件
...
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#这四条为配置文件、错误日志和运行日志各自位置,可根据日志辨别错误类型
# 使用insert键切为插入模式,在后面增加配置参数
# 配置server_id 不可冲突,从机可更改为222
server_id=111
# MySQL的日志文件的名字,从机更改为mysql_slave
log-bin=mysql_master
# 作为从库时 更新操作是否写入日志 on:写入 其他数据库以此数据库做主库时才能进行同步
log-slave-updates=on
# MySQL系统库的数据不需要同步 我们这里写了3个 更加保险
# 同步数据时忽略一下数据库 但是必须在使用use db的情况下才会忽略;如果没有使用use db 比如create user 数据还是会同步的
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
replicate-ignore-db=performance_schema
replicate-ignore-db=sys
# 使用通配符忽略MySQL系统库的表 这样在create user时也不会进行同步了
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=performance_schema.%
replicate_wild_ignore_table=sys.%
# MySQL系统库的日志不计入binlog 这样更加保险了
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
#配置完成后按ESC退出插入模式,使用 :wq! 命令保存并关闭my.cnf
#从机更改以下两条
server_id=222
log-bin=mysql_slave
4.配置mysql主从同步
mysql> show master status;
+---------------------+----------+--------------+-------------------------------------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+-------------------------------------------------+-------------------+
| mysql_master.000001 | 156 | | information_schema,mysql,performance_schema,sys | |
+---------------------+----------+--------------+-------------------------------------------------+-------------------+
1 row in set (0.00 sec)
#log名为mysql_master.000001,position=156,接下来要在从机配置对主机的连接
mysql>CHANGE MASTER TO MASTER_HOST='192.168.10.111',MASTER_PORT=3306,MASTER_USER='repl_master',MASTER_PASSWORD='Lft@2021',MASTER_LOG_FILE='mysql_master.000001',MASTER_LOG_POS=156;
#以上填入主机的IP、数据库端口、数据库账户密码、log名和log位置
# 开启从库
START SLAVE;
#按照以上流程在主机上重复,建立主到从的连接,事实上形成互为主机的同步方式
#关闭防火墙后验证连接
[root@localhost ~]# systemctl restart mysqld
# 查看从库的状态
mysql> show slave status
-> ;
+----------------------------------+----------------+-------------+-------------+---------------+--------------------+---------------------+----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+-------------------------------------------------+--------------------+------------------------+-------------------------+---------------------------------------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | Replicate_Rewrite_DB | Channel_Name | Master_TLS_Version | Master_public_key_path | Get_master_public_key | Network_Namespace |
+----------------------------------+----------------+-------------+-------------+---------------+--------------------+---------------------+----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+-------------------------------------------------+--------------------+------------------------+-------------------------+---------------------------------------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
| Waiting for master to send event | 192.168.10.111 | slave | 3306 | 60 | mysql_slave.000012 | 156 | localhost-relay-bin.000055 | 375 | mysql_slave.000012 | Yes | Yes | | information_schema,mysql,performance_schema,sys | | | | information_schema.%,mysql.%,performance_schema.%,sys.% | 0 | | 0 | 156 | 758 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 222 | 8364f2e1-7988-11ee-a025-000c29f91170 | mysql.slave_master_info | 0 | NULL | Slave has read all relay log; waiting for more updates | 86400 | | | | | | | | 0 | | | | | 0 | |
+----------------------------------+----------------+-------------+-------------+---------------+--------------------+---------------------+----------------------------+---------------+-----------------------+------------------+-------------------+-----------------+-------------------------------------------------+--------------------+------------------------+-------------------------+---------------------------------------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+--------------------------------------+-------------------------+-----------+---------------------+--------------------------------------------------------+--------------------+-------------+-------------------------+--------------------------+----------------+--------------------+--------------------+-------------------+---------------+----------------------+--------------+--------------------+------------------------+-----------------------+-------------------+
1 row in set, 1 warning (0.00 sec)
三、配置keepalived
1.keepalived作用
功能为在数台计算机间创建一个共享的虚拟IP,外部机器可通过虚拟IP访问这个集群中的当前作为虚拟IP载体的计算机,当该计算机触发了虚拟IP的转移规则后,虚拟IP就会更换载体,以保证外部访问不变。
思路为设定虚拟IP跳转规则为定时检测数据库对应端口健康状态,若数据库返回状态不正常,则触发跳转。
2.配置
cd /etc/keepalived/
#将原有keepalived.conf更名为keepalived.conf.bak作为备份
#创建新的配置文件
[root@localhost keepalived]# pwd
/etc/keepalived
[root@localhost keepalived]# vim keepalived.conf
global_defs {
notification_email {
acassen@firewall.loc
failover@firewall.loc
sysadmin@firewall.loc
}
notification_email_from Alexandre.Cassen@firewall.loc
smtp_server 192.168.200.1
smtp_connect_timeout 30
router_id LVS_DEVEL
vrrp_skip_check_adv_addr
#vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}
# 检查mysql服务是否存活的脚本
vrrp_script chk_mysql{
script "/usr/bin/killall -0 mysqld"
}
# vrrp配置虚IP
vrrp_instance VI_1
{
# 状态:state MASTER 另外一台机器为BACKUP
state BACKUP
nopreempt
# 绑定的网卡
interface ens33
# 虚拟路由id 两台机器需保持一致
virtual_router_id 51
# 优先级 MASTER的值要大于BACKUP
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
# 虚拟IP地址 两台keepalived需要一致
virtual_ipaddress {
192.168.10.120
}
# 检查脚本 vrrp_script的名字
# track_script {
# chk_mysql
# }
}
virtual_server 192.168.10.120 3306 {
delay_loop 20
lb_algo wrr
lb_kind DR
persistence_timeout 10
protocol TCP
real_server 192.168.10.111 3306{
weight 1
notify_down /etc/keepalived/closekeepalived.sh
TCP_CHECK {
connect_timeout 1
nb_get_retry 1
delay_before_retry 1
connect_port 3306
}
}
}
3.编写脚本
[root@localhost keepalived]# vim closekeepalived.sh
sudo -S killall keepalived
四、验证
1.查看主机ip
#查看ip是否有虚拟IP确认生效
[root@localhost keepalived]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
link/ether 00:0c:29:a1:6b:43 brd ff:ff:ff:ff:ff:ff
inet 192.168.10.111/24 brd 192.168.10.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet 192.168.10.120/32 scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::b10a:a019:9086:6c08/64 scope link tentative noprefixroute dadfailed
valid_lft forever preferred_lft forever
inet6 fe80::5a7e:dc15:57c8:6b6b/64 scope link tentative noprefixroute dadfailed
valid_lft forever preferred_lft forever
inet6 fe80::cb7:65a3:f0e0:17ad/64 scope link noprefixroute
valid_lft forever preferred_lft forever
3: virbr0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default qlen 1000
link/ether 52:54:00:67:72:bb brd ff:ff:ff:ff:ff:ff
inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
valid_lft forever preferred_lft forever
4: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc fq_codel master virbr0 state DOWN group default qlen 1000
link/ether 52:54:00:67:72:bb brd ff:ff:ff:ff:ff:ff
[root@localhost keepalived]#
2.验证跳转
在虚拟IP载体的主机暂停mysql服务,查看是否虚拟IP跳转至从机10.112
主机
[root@localhost ~]# systemctl stop mysqld
从机
[root@localhost ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
link/ether 00:0c:29:f9:11:70 brd ff:ff:ff:ff:ff:ff
inet 192.168.10.112/24 brd 192.168.10.255 scope global noprefixroute ens33
valid_lft forever preferred_lft forever
inet 192.168.10.120/32 scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::5a7e:dc15:57c8:6b6b/64 scope link noprefixroute
valid_lft forever preferred_lft forever
3: virbr0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default qlen 1000
link/ether 52:54:00:67:72:bb brd ff:ff:ff:ff:ff:ff
inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
valid_lft forever preferred_lft forever
4: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc fq_codel master virbr0 state DOWN group default qlen 1000
link/ether 52:54:00:67:72:bb brd ff:ff:ff:ff:ff:ff
发现已跳转,搭建成功