keepalived+mysql

最新推荐文章于 2024-07-20 16:33:27 发布

晴湖今夜小雨

最新推荐文章于 2024-07-20 16:33:27 发布

阅读量322

点赞数 11

文章标签： mysql 运维

本文链接：https://blog.csdn.net/m0_62874702/article/details/136564946

版权

mysql高可用

环境说明：

角色	IP	应用和系统版本	备注
master01	192.168.100.240、192.168.100.250（vip）	rockylinux9.3、mysql8.0.35、Keepalived v2.2.8	主数据库（主）
master02	192.168.100.230、192.168.100.250（vip）	rockylinux9.3、mysql8.0.35、Keepalived v2.2.8	主数据库（备）
slave	192.168.100.220	rockylinux9.3、mysql8.0.35	从数据库
nfsserver	192.168.100.200	rockylinux9.3、nfs	NFS服务器

前期准备

均关闭防火墙和selinux

mysql8.0.35压缩包上传master01、master02、slave。

NFS

安装和配置

nfsserver

[root@nfsserver ~]# yum -y install nfs-utils

//共享目录给master01和master02

[root@nfsserver ~]# mkdir /data
[root@nfsserver ~]# chmod 777 /data/
[root@nfsserver ~]# vim /etc/exports
[root@nfsserver ~]# cat /etc/exports
/data 192.168.100.240(rw)
/data 192.168.100.230(rw)

[root@nfsserver ~]# systemctl enable --now nfs-server.service 
Created symlink /etc/systemd/system/multi-user.target.wants/nfs-server.service → /usr/lib/systemd/system/nfs-server.service.
[root@nfsserver ~]#

master01

nfs不用启动

[root@master01 ~]# yum -y install nfs-utils

master02

nfs不用启动

[root@master02 ~]# yum -y install nfs-utils

测试

[root@master01 ~]# showmount -e 192.168.100.200
Export list for 192.168.100.200:
/data 192.168.100.230,192.168.100.240
[root@master01 ~]#

永久挂载

主数据库的主备需要挂载，从数据库不用

master01

[root@master01 ~]# mkdir /opt/data      //mysql的安装目录
[root@master01 ~]# vim /etc/fstab 
[root@master01 ~]# tail -1 /etc/fstab 
192.168.100.200:/data    /opt/data    nfs     defaults,_netdev 0 0
[root@master01 ~]# mount -a 
mount: (hint) your fstab has been modified, but systemd still uses
       the old version; use 'systemctl daemon-reload' to reload.
[root@master01 ~]# systemctl daemon-reload
[root@master01 ~]# df -Th
Filesystem            Type      Size  Used Avail Use% Mounted on
devtmpfs              devtmpfs  4.0M     0  4.0M   0% /dev
tmpfs                 tmpfs     3.8G     0  3.8G   0% /dev/shm
tmpfs                 tmpfs     1.5G   12M  1.5G   1% /run
/dev/mapper/rl-root   xfs        36G  4.6G   32G  13% /
/dev/mapper/rl-home   xfs        18G  157M   18G   1% /home
/dev/sda1             xfs       960M  301M  660M  32% /boot
tmpfs                 tmpfs     765M  116K  765M   1% /run/user/0
/dev/sr0              iso9660   9.8G  9.8G     0 100% /run/media/root/Rocky-9-3-x86_64-dvd
192.168.100.200:/data nfs4       39G  4.7G   34G  13% /opt/data
[root@master01 ~]#

master02

[root@master02 ~]# mkdir /opt/data 
[root@master02 ~]# vim /etc/fstab
[root@master02 ~]# tail -1 /etc/fstab
192.168.100.200:/data    /opt/data    nfs     defaults,_netdev 0 0
[root@master02 ~]# mount -a
mount: (hint) your fstab has been modified, but systemd still uses
       the old version; use 'systemctl daemon-reload' to reload.
[root@master02 ~]# systemctl daemon-reload 
[root@master02 ~]# df -Th
Filesystem            Type      Size  Used Avail Use% Mounted on
devtmpfs              devtmpfs  4.0M     0  4.0M   0% /dev
tmpfs                 tmpfs     867M     0  867M   0% /dev/shm
tmpfs                 tmpfs     347M  7.6M  339M   3% /run
/dev/mapper/rl-root   xfs        17G  4.9G   13G  29% /
/dev/sda1             xfs       960M  301M  660M  32% /boot
tmpfs                 tmpfs     174M  116K  174M   1% /run/user/1001
/dev/sr0              iso9660   9.8G  9.8G     0 100% /run/media/ysy/Rocky-9-3-x86_64-dvd
tmpfs                 tmpfs     174M   36K  174M   1% /run/user/0
192.168.100.200:/data nfs4       39G  4.7G   34G  13% /opt/data
[root@master02 ~]#

mysql

master01

准备

[root@master01 ~]# groupadd -r -g 900 mysql
[root@master01 ~]# useradd -r -M -s /sbin/nologin -g 900 -u 900 mysql

[root@master01 ~]# tar xf  mysql-8.0.35-linux-glibc2.28-x86_64.tar.xz -C /usr/local/

[root@master01 ~]# cd /usr/local/
[root@master01 local]# mv mysql-8.0.35-linux-glibc2.28-x86_64/ mysql
[root@master01 local]# chown -R mysql.mysql mysql
[root@master01 local]# id mysql
uid=900(mysql) gid=900(mysql) groups=900(mysql)
[root@master01 local]# vim /etc/my.cnf
[root@master01 local]# cat /etc/my.cnf 
[mysqld]
basedir=/usr/local/mysql
datadir=/opt/data
pid-file=/opt/data/mysql.pid
port=3306
skip-name-resolve
user=mysql
socket = /tmp/mysql.sock

[root@master01 local]# 
[root@master01 local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@master01 local]# source /etc/profile.d/mysql.sh 
[root@master01 local]# ln -s /usr/local/mysql/include/ /usr/include/mysql
[root@master01 local]# echo  '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf
[root@master01 local]#

初始化

//master01和master02用的是共享目录,其中一个初始化就可以

[root@master01 local]# mysqld --initialize --user mysql --datadir /opt/data
2024-03-05T08:54:54.923715Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.35) initializing of server in progress as process 39592
2024-03-05T08:54:54.953201Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-03-05T08:55:07.197602Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-03-05T08:55:10.412334Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: kEC(uT12yPlU
[root@master01 local]# echo 'kEC(uT12yPlU'  > /tmp/pass
[root@master01 local]# cat /tmp/pass 
kEC(uT12yPlU
[root@master01 local]# 

[root@master01 local]# cd /usr/local/mysql/support-files/
[root@master01 support-files]# vim mysql.server 

basedir=/usr/local/mysql
datadir=/opt/data

开启自启

[root@master01 support-files]# cp /usr/lib/systemd/system/sshd.service /usr/lib/systemd/system/mysqld.service
[root@master01 support-files]# vim /usr/lib/systemd/system/mysqld.service 
[root@master01 support-files]# cat /usr/lib/systemd/system/mysqld.service 
[Unit]
Description=mysqld server daemon
After=network.target
[Service]
Type=forking
ExecStart=/usr/local/mysql/support-files/mysql.server start
ExecStop=/usr/local/mysql/support-files/mysql.server stop
ExecReload=/bin/kill -HUP $MAINPID
[Install]
WantedBy=multi-user.target

[root@master01 support-files]# 
[root@master01 ~]# systemctl enable --now mysqld.service 
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.
[root@master01 ~]# ss -antl
State          Recv-Q         Send-Q                 Local Address:Port                    Peer Address:Port         Process         
LISTEN         0              128                        127.0.0.1:6010                         0.0.0.0:*                            
LISTEN         0              128                        127.0.0.1:6011                         0.0.0.0:*                            
LISTEN         0              4096                       127.0.0.1:631                          0.0.0.0:*                            
LISTEN         0              4096                         0.0.0.0:111                          0.0.0.0:*                            
LISTEN         0              128                          0.0.0.0:22                           0.0.0.0:*                            
LISTEN         0              151                                *:3306                               *:*                            
LISTEN         0              70                                 *:33060                              *:*                            
LISTEN         0              4096                            [::]:111                             [::]:*                            
LISTEN         0              4096                           [::1]:631                             [::]:*                            
LISTEN         0              128                             [::]:22                              [::]:*                            
LISTEN         0              128                            [::1]:6011                            [::]:*                            
LISTEN         0              128                            [::1]:6010                            [::]:*    

[root@master01 ~]# systemctl status mysqld
● mysqld.service - mysqld server daemon
     Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; preset: disabled)
     Active: active (running) since Tue 2024-03-05 17:06:57 CST; 19min ago
   Main PID: 39848 (mysqld_safe)
      Tasks: 39 (limit: 48510)
     Memory: 496.6M
        CPU: 9.657s
     CGroup: /system.slice/mysqld.service
             ├─39848 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=/opt/data/mysql.pid
             └─40036 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/data --plugin-dir=/usr/local/mysql/lib/pl>

Mar 05 17:06:51 master01 systemd[1]: Starting mysqld server daemon...
Mar 05 17:06:52 master01 mysql.server[39835]: Starting MySQL.
Mar 05 17:06:52 master01 mysql.server[39848]: Logging to '/opt/data/master01.err'.
Mar 05 17:06:57 master01 mysql.server[39835]: .... SUCCESS!
Mar 05 17:06:57 master01 systemd[1]: Started mysqld server daemon.
lines 1-16/16 (END)

改密码


[root@master01 ~]# cat pass
cat: pass: No such file or directory
[root@master01 ~]# cat /tmp/pass
kEC(uT12yPlU
[root@master01 ~]# mysql -uroot -p'kEC(uT12yPlU'
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 8
Server version: 8.0.35

Copyright (c) 2000, 2023, 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.

mysql> alter user root@'localhost' identified with mysql_native_password by
    -> 'Passw0rd@_';
Query OK, 0 rows affected (0.00 sec)

mysql>

传配置文件

文件传过去就不用在写了（更快）

给主数据库(备)传

[root@master01 ~]# scp /usr/local/mysql/support-files/mysql.server 192.168.100.230:/usr/local/mysql/support-files/
The authenticity of host '192.168.100.230 (192.168.100.230)' can't be established.
ED25519 key fingerprint is SHA256:YAjG0yoPfihopvhl1clD3AgVfaGEcICGiNumwnugD0w.
This key is not known by any other names
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.100.230' (ED25519) to the list of known hosts.
root@192.168.100.230's password: 
mysql.server                                                                                       100%   10KB   3.8MB/s   00:00    
[root@master01 ~]# 
[root@master01 ~]# scp /usr/lib/systemd/system/mysqld.service 192.168.100.230:/usr/lib/systemd/system/
root@192.168.100.230's password: 
mysqld.service                                                                                     100%  275   218.9KB/s   00:00    
[root@master01 ~]#

给从数据库传

[root@master01 ~]# scp /usr/local/mysql/support-files/mysql.server 192.168.100.220:/usr/local/mysql/support-files/
The authenticity of host '192.168.100.220 (192.168.100.220)' can't be established.
ED25519 key fingerprint is SHA256:mS5+XGqwDWpId27CLPnc2c13wj4xoCvv/HYz/GR6HN0.
This key is not known by any other names
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.100.220' (ED25519) to the list of known hosts.
root@192.168.100.220's password: 
mysql.server                                                                                       100%   10KB   6.0MB/s   00:00    
[root@master01 ~]# scp /usr/lib/systemd/system/mysqld.service 192.168.100.220:/usr/lib/systemd/system/
root@192.168.100.220's password: 
mysqld.service                                                                                     100%  275   360.6KB/s   00:00    
[root@master01 ~]#

master02

准备

[root@master02 ~]# groupadd -r -g 900 mysql
[root@master02 ~]# useradd -r -M -s /sbin/nologin -g 900 -u 900 mysql
[root@master02 ~]# tar xf  mysql-8.0.35-linux-glibc2.28-x86_64.tar.xz -C /usr/local/
[root@master02 ~]# cd /usr/local/
[root@master02 local]#  mv mysql-8.0.35-linux-glibc2.28-x86_64/ mysql
[root@master02 local]# chown -R mysql.mysql mysql
[root@master02 local]# vim /etc/my.cnf
[root@master02 local]# cat /etc/my.cnf 
[mysqld]
basedir=/usr/local/mysql
datadir=/opt/data
pid-file=/opt/data/mysql.pid
port=3306
skip-name-resolve
user=mysql
socket = /tmp/mysql.sock

[root@master02 local]# 
[root@master02 local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@master02 local]# source /etc/profile.d/mysql.sh 
[root@master02 local]# ln -s /usr/local/mysql/include/ /usr/include/mysql
[root@master02 local]# echo  '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf
[root@master02 local]#

状态

master01传文件之后在进行这后面步骤
master01和master02共用，只能有一个在工作

[root@master02 local]# systemctl daemon-reload 
[root@master02 local]# systemctl status mysqld.service 
○ mysqld.service - mysqld server daemon
     Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; preset: disabled)
     Active: inactive (dead)
[root@master02 local]#

slave

准备

[root@slave ~]# groupadd -r -g 900 mysql
[root@slave ~]# useradd -r -M -s /sbin/nologin -g 900 -u 900 mysql
[root@slave ~]# tar xf  mysql-8.0.35-linux-glibc2.28-x86_64.tar.xz -C /usr/local/
[root@slave ~]# cd /usr/local/
[root@slave local]#  mv mysql-8.0.35-linux-glibc2.28-x86_64/ mysql
[root@slave local]# chown -R mysql.mysql mysql
[root@slave local]# 
[root@slave local]# vim /etc/my.cnf
[root@slave local]# cat /etc/my.cnf 
[mysqld]
basedir=/usr/local/mysql
datadir=/opt/data
pid-file=/opt/data/mysql.pid
port=3306
skip-name-resolve
user=mysql
socket = /tmp/mysql.sock

[root@slave local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@slave local]# source /etc/profile.d/mysql.sh
[root@slave local]# ln -s /usr/local/mysql/include/ /usr/include/mysql
[root@slave local]# echo  '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf
[root@slave local]#

初始化

[root@slave local]# mysqld --initialize --user mysql --datadir /opt/data
2024-03-05T09:22:17.254261Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.35) initializing of server in progress as process 3608
2024-03-05T09:22:17.334353Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-03-05T09:22:18.433311Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-03-05T09:22:20.084655Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: toG_IIWVw1hI
[root@slave local]# echo 'toG_IIWVw1hI'  >  /tmp/pass

开机自启

配置文件由master01传

[root@slave local]#  systemctl daemon-reload 
[root@slave local]# systemctl enable --now mysqld.service 
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.
[root@slave local]# ss -antl
State          Recv-Q         Send-Q                 Local Address:Port                    Peer Address:Port         Process         
LISTEN         0              128                          0.0.0.0:22                           0.0.0.0:*                            
LISTEN         0              128                        127.0.0.1:6011                         0.0.0.0:*                            
LISTEN         0              4096                       127.0.0.1:631                          0.0.0.0:*                            
LISTEN         0              70                                 *:33060                              *:*                            
LISTEN         0              128                             [::]:22                              [::]:*                            
LISTEN         0              151                                *:3306                               *:*                            
LISTEN         0              128                            [::1]:6011                            [::]:*                            
LISTEN         0              4096                           [::1]:631                             [::]:*                            
[root@slave local]# 

[root@slave local]# systemctl status mysqld.service 
● mysqld.service - mysqld server daemon
     Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; preset: disabled)
     Active: active (running) since Tue 2024-03-05 04:27:00 EST; 53s ago
    Process: 3857 ExecStart=/usr/local/mysql/support-files/mysql.server start (code=exited, status=0/SUCCESS)
   Main PID: 3870 (mysqld_safe)
      Tasks: 39 (limit: 10685)
     Memory: 371.8M
        CPU: 2.090s
     CGroup: /system.slice/mysqld.service
             ├─3870 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=/opt/data/mysql.pid
             └─4058 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/data --plugin-dir=/usr/local/mysql/lib/plu>

Mar 05 04:26:58 slave systemd[1]: Starting mysqld server daemon...
Mar 05 04:26:58 slave mysql.server[3857]: Starting MySQL.
Mar 05 04:26:58 slave mysql.server[3870]: Logging to '/opt/data/slave.err'.
Mar 05 04:27:00 slave mysql.server[3857]: . SUCCESS!
Mar 05 04:27:00 slave systemd[1]: Started mysqld server daemon.
lines 1-17/17 (END)

改密码

[root@slave local]# cat /tmp/pass 
toG_IIWVw1hI
[root@slave local]# mysql -uroot -p'toG_IIWVw1hI'
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 8
Server version: 8.0.35

Copyright (c) 2000, 2023, 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.

mysql> alter user root@'localhost' identified with mysql_native_password by '123456789';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@slave local]#

nfsserver

准备

nfs上的mysql用户id要与MySQL数据库上的mysql用户id保持一致

[root@nfsserver ~]# groupadd -r -g 900 mysql
[root@nfsserver ~]# useradd -r -M -s /sbin/nologin -g 900 -u 900 mysql
[root@nfsserver ~]# id mysql
uid=900(mysql) gid=900(mysql) groups=900(mysql)
[root@nfsserver ~]# chown -R mysql.mysql /data

keepalibed高可用

master01

安装keepalived

[root@master01 ~]# yum -y install keepalived

让keepalived监控mysql

[root@master01 ~]# mkdir /scripts
[root@master01 ~]# cd /scripts/
[root@master01 scripts]# vim check_mysql.sh 
[root@master01 scripts]# 
[root@master01 scripts]# cat check_mysql.sh 
#!/bin/bash
mysql_status=$(ps -ef|grep -Ev "grep|$0"|grep '\bmysql\b'|wc -l)
if [ $mysql_status -lt 1 ];then
    systemctl stop keepalived
fi
[root@master01 scripts]# 
[root@master01 scripts]# chmod +x check_mysql.sh

配置keepalived加入监控脚本的配置

[root@master01 ~]# mv /etc/keepalived/keepalived.conf /opt/
[root@master01 ~]# vim /etc/keepalived/keepalived.conf
[root@master01 ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {
   router_id db01      
}

vrrp_script mysql_check {
    script "/scripts/check_mysql.sh"    
    interval 1
    weight -20
}

vrrp_instance VI_1 {
    state MASTER                   
    interface ens33                
    virtual_router_id 51           
    priority 100                   
    advert_int 1
    authentication {
        auth_type PASS            
        auth_pass ysy             
    }
    virtual_ipaddress {
        192.168.100.250
    }
    track_script {               
        mysql_check
    }
}

virtual_server 192.168.100.250 3306 {  
    delay_loop 6
    lb_algo rr
    lb_kind DR
    persistence_timeout 50
    protocol TCP

    real_server 192.168.100.240 3306 { 
        weight 1
        TCP_CHECK {
            connect_port 3306
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }

    real_server 192.168.100.230 3306 {  
        weight 1
        TCP_CHECK {
            connect_port 3306
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }
}
[root@master01 ~]#

master02

安装keepalived

[root@master02 ~]# yum -y install keepalived

让keepalived监控mysql

[root@master02 ~]# mkdir /scripts
[root@master02 ~]# cd /scripts/
[root@master02 scripts]# vim notify.sh
[root@master02 scripts]# cat notify.sh
#!/bin/bash
case "$1" in
  master)
        mysql_status=$(ps -ef|grep -Ev "grep|$0"|grep '\bmysql\b'|wc -l)
        if [ $mysql_status -lt 1 ];then
            systemctl start mysqld
        fi
        sendmail
  ;;
  backup)
        mysql_status=$(ps -ef|grep -Ev "grep|$0"|grep '\bmysql\b'|wc -l)
        if [ $mysql_status -gt 0 ];then
            systemctl stop mysqld
        fi
  ;;
  *)
        echo "Usage:$0 master|backup VIP"
  ;;
esac

[root@master02 scripts]# chmod +x notify.sh 
[root@master02 scripts]#

配置keepalived加入监控脚本的配置

[root@master02 ~]# mv /etc/keepalived/keepalived.conf /opt/
[root@master02 ~]# vim /etc/keepalived/keepalived.conf
[root@master02 ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived

global_defs {
   router_id db02
}

vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 50
    nopreempt
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass ysy
    }
    virtual_ipaddress {
        192.168.100.250
    }
    notify_master "/scripts/notify.sh master 192.168.100.250"
    notify_backup "/scripts/notify.sh backup 192.168.100.250"
}

virtual_server 192.168.100.250 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    persistence_timeout 50
    protocol TCP

    real_server 192.168.100.240 3306 {
        weight 1
        TCP_CHECK {
            connect_port 3306
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }

    real_server 192.168.100.230 3306 {
        weight 1
        TCP_CHECK {
            connect_port 3306
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
        }
    }
}
[root@master02 ~]#

验证

master01

[root@master01 ~]# systemctl enable --now keepalived.service 
Created symlink /etc/systemd/system/multi-user.target.wants/keepalived.service → /usr/lib/systemd/system/keepalived.service.
[root@master01 ~]# 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:55:ab:78 brd ff:ff:ff:ff:ff:ff
    altname enp2s1
    inet 192.168.100.240/24 brd 192.168.100.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.100.250/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe55:ab78/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
[root@master01 ~]#

master02

[root@master02 scripts]# chmod +x notify.sh 
[root@master02 scripts]# systemctl enable --now keepalived.service 
Created symlink /etc/systemd/system/multi-user.target.wants/keepalived.service → /usr/lib/systemd/system/keepalived.service.
[root@master02 scripts]# 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:47:01:b3 brd ff:ff:ff:ff:ff:ff
    altname enp2s1
    inet 192.168.100.230/24 brd 192.168.100.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe47:1b3/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
[root@master02 scripts]#

mysql授权

master01

[root@master01 data]# mysql -uroot -pPassw0rd@_
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 10
Server version: 8.0.35 MySQL Community Server - GPL

Copyright (c) 2000, 2023, 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.

mysql> create user 'root'@'%' identified with mysql_native_password by 'Passw0rd@_';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all on *.* to 'root'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
[root@master01 data]#

验证

[root@slave ~]# mysql -uroot -pPassw0rd@_ -h192.168.100.250
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 31
Server version: 8.0.35 MySQL Community Server - GPL

Copyright (c) 2000, 2023, 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.

mysql>

一主一从

配置一主一从，主ip为vip192.168.100.250

授权

master01

mysql>  create user 'repl'@'192.168.100.220' identified with mysql_native_password by 'Passw0rd@_';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'repl'@'192.168.100.220';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

主从配置

master01

mysql> 

[root@master01 ~]# vim /etc/my.cnf
[root@master01 ~]# cat /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/opt/data
pid-file=/opt/data/mysql.pid
port=3306
skip-name-resolve
user=mysql
socket = /tmp/mysql.sock

log-bin = mysql_bin
server-id = 10

default-authentication-plugin=mysql_native_password

[root@master01 ~]# systemctl restart mysqld.service 

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000002 |     2833 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

master02

[root@master02 ~]# vim /etc/my.cnf
[root@master02 ~]# cat /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/opt/data
pid-file=/opt/data/mysql.pid
port=3306
skip-name-resolve
user=mysql
socket = /tmp/mysql.sock
log-bin = mysql_bin
server-id = 10

default-authentication-plugin=mysql_native_password

[root@master02 ~]#

slave

[root@slave ~]# vim /etc/my.cnf 
[root@slave ~]# cat /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/opt/data
pid-file=/opt/data/mysql.pid
port=3306
skip-name-resolve
user=mysql
socket = /tmp/mysql.sock

server-id = 20
relay-log = my_relay

default-authentication-plugin=mysql_native_password

[root@slave ~]# 
[root@slave ~]# systemctl restart mysqld.service 

mysql> change master to
    -> master_host='192.168.100.250',
    -> master_user='repl',
    -> master_password='Passw0rd@_',
    -> master_log_file='mysql_bin.000002',
    -> master_log_pos=2833;
Query OK, 0 rows affected, 8 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.100.250
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000002
          Read_Master_Log_Pos: 2833
               Relay_Log_File: my_relay.000002
                Relay_Log_Pos: 326
        Relay_Master_Log_File: mysql_bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2833
              Relay_Log_Space: 529
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10
                  Master_UUID: e0907af3-db52-11ee-a1d7-000c2955ab78
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
            Network_Namespace: 
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

mysql>

主从验证

master01

mysql> create database test;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

mysql>

slave

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

mysql>

模拟故障

停止master01，看master02能否自启，
master02创建库
slave能否与master02同步

master01

[root@master01 ~]# systemctl stop mysqld.service 
[root@master01 ~]#

master02

[root@master02 ~]# 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:47:01:b3 brd ff:ff:ff:ff:ff:ff
    altname enp2s1
    inet 192.168.100.230/24 brd 192.168.100.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 192.168.100.250/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::20c:29ff:fe47:1b3/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever
[root@master02 ~]# mysql -uroot -pPassw0rd@_
\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 32
Server version: 8.0.35 MySQL Community Server - GPL

Copyright (c) 2000, 2023, 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.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

mysql> create database test2;   //测试slave能否与master02能否同步
Query OK, 1 row affected (0.01 sec)

mysql> 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test2              |
+--------------------+
6 rows in set (0.00 sec)

mysql>

slave

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test2              |
+--------------------+
6 rows in set (0.00 sec)

mysql>

晴湖今夜小雨

关注

11
点赞
踩
10

收藏

觉得还不错? 一键收藏
1
评论
keepalived+mysql

mysql8.0.35压缩包上传master01、master02、slave。配置一主一从，主ip为vip192.168.100.250。master01和master02共用，只能有一个在工作。停止master01，看master02能否自启，master01传文件之后在进行这后面步骤。主数据库的主备需要挂载，从数据库不用。slave能否与master02同步。文件传过去就不用在写了（更快）均关闭防火墙和selinux。master02创建库。
复制链接

扫一扫