操作系统:CentOS 7.8
MariaDB版本:5.5.x→10.5.x
节点 | IP地址 | 数据库版本 |
---|---|---|
节点1 | 10.40.200.191 | MariaDB 5.5.x |
节点2 | 10.40.200.192 | MariaDB 5.5.x |
VIP | 10.40.200.190 | xxx |
1、查看VIP所在节点
节点1:
[root@linux191 ~]# ip a show dev eth0
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether xx:xx:xx:xx:xx:xx brd ff:ff:ff:ff:ff:ff
inet 10.40.200.191/24 brd 10.40.200.255 scope global noprefixroute eth0
valid_lft forever preferred_lft forever
inet 10.40.200.190/24 scope global secondary eth0:0
valid_lft forever preferred_lft forever
inet6xxxx::xxxx:xxxx:xxxx:xxxx/64 scope link noprefixroute
valid_lft forever preferred_lft forever
节点2:
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether xx:xx:xx:xx:xx:xx brd ff:ff:ff:ff:ff:ff
inet 10.40.200.192/24 brd 10.40.200.255 scope global noprefixroute eth0
valid_lft forever preferred_lft forever
inet6 xxxx::xxxx:xxxx:xxxx:xxxx/64 scope link noprefixroute
valid_lft forever preferred_lft forever
从上可知VIP所在节点在“节点1”上
2、停止MariaDB主从同步
节点1:
###登陆MariaDB数据库
mysql -uroot -p
###停止主从同步
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)
###重置主从同步
MariaDB [(none)]> reset slave;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.40.200.191
Master_User: sync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
...................
1 row in set (0.00 sec)
节点2:
###登陆MariaDB数据库
mysql -uroot -p
###停止主从同步
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)
###重置主从同步
MariaDB [(none)]> reset slave;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.40.200.192
Master_User: sync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:
Read_Master_Log_Pos: 4
Relay_Log_File: mariadb-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File:
Slave_IO_Running: No
Slave_SQL_Running: No
...................
1 row in set (0.00 sec)
3、升级数据库版本
节点2:
mkdir /data
cd /data
###备份数据库数据
mysqldump -uroot -p --all-databases > all_databases.sql
###停止数据库运行
systemctl stop mariadb
###备份数据库文件
tar -cJvf mariadb.tar.xz /var/lib/mysql/
cp /etc/my.cnf .
###卸载旧版本数据库程序
yum autoremove `rpm -qa|grep mariadb`
###升级数据库程序
###清华开源镜像源
touch /etc/yum.repos.d/mariadb.repo
cat >> /etc/yum.repos.d/mariadb.repo << EOF
[mariadb]
name=mariadb
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/10.5/centos7-amd64
enabled=1
gpgcheck=0
EOF
yum install mariadb-server mariadb-devel mariadb -y
rm -f /etc/my.cnf
cp /data/my.cnf /etc
systemctl start mariadb
systemctl enable mariadb
####升级已有数据库
mysql_upgrade -uroot -p
mysql
mysql.columns_priv OK
mysql.db OK
mysql.event OK
mysql.func OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.servers OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
Upgrading from a version before MariaDB-10.1
.....................................................
Phase 7/7: Running 'FLUSH PRIVILEGES'
OK
节点1:
###登陆数据库
mysql -uroot -p
###锁定数据表只读
MariaDB [(none)]> flush tables with read lock;
Query OK, 0 rows affected (0.000 sec)
###备份数据库
mysqldump -uroot -p --all-databases > all_databases.sql
传送到节点2恢复数据库
节点2:
mysql -uroot -p < all_databases.sql
节点1:
###解锁数据表只读
unlock tables;
###停止keepalived程序使VIP漂移到节点2
systemctl stop keepalived
验证业务是否运行正常,若正常则重复上面升级步骤升级节点1的数据库版本
4、重新开启主主同步
节点2:
###创建数据同步账号
grant replication slave,replication client on *.* to 'sync'@'%' identified by '123456';
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 | 664 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
###锁定数据表只读
MariaDB [(none)]> flush tables with read lock;
Query OK, 0 rows affected (0.000 sec)
###备份数据库并传送到节点1并恢复数据库数据
节点1:
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 664 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
###开启数据同步
MariaDB [(none)]> change master to \
master_host='10.40.200.192',\
master_user='sync',\
master_password='123456',\
master_log_file='mysql-bin.000004',\
master_log_pos=664;
Query OK, 0 rows affected (0.010 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.000 sec)
###查看同步状态
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.40.200.192
Master_User: sync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 664
Relay_Log_File: linux191-relay-bin.000002
Relay_Log_Pos: 555
Relay_Master_Log_File: mysql-bin.000004
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: 664
Relay_Log_Space: 867
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: 192
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: optimistic
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
节点2:
###解锁数据表只读
unlock tables;
###重复以上步骤,开启数据库同步
MariaDB [(none)]> change master to \
master_host='10.40.200.191',\
master_user='sync',\
master_password='123456',\
master_log_file='mysql-bin.000004',\
master_log_pos=664;
Query OK, 0 rows affected (0.010 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.000 sec)
###查看数据库同步状态
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.40.200.191
Master_User: sync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 664
Relay_Log_File: linux192-relay-bin.000002
Relay_Log_Pos: 555
Relay_Master_Log_File: mysql-bin.000003
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: 664
Relay_Log_Space: 867
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: 191
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: optimistic
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
1 row in set (0.000 sec)
5、验证数据同步
节点1:
MariaDB [(none)]> create database test191
-> ;
Query OK, 1 row affected (0.000 sec)
节点2:
MariaDB [(none)]> create database test192;
Query OK, 1 row affected (0.001 sec)
节点1:
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| gitea |
| information_schema |
| mysql |
| performance_schema |
| test191 |
| test192 |
+--------------------+
7 rows in set (0.001 sec)
节点2:
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| gitea |
| information_schema |
| mysql |
| performance_schema |
| test191 |
| test192 |
+--------------------+
7 rows in set (0.001 sec)
6、验证数据库版本
节点1:
MariaDB [(none)]> status
--------------
mysql Ver 15.1 Distrib 10.5.4-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 4
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.5.4-MariaDB-log MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 31 min 40 sec
Threads: 7 Questions: 26 Slow queries: 0 Opens: 19 Open tables: 13 Queries per second avg: 0.013
--------------
节点2:
MariaDB [(none)]> status
--------------
mysql Ver 15.1 Distrib 10.5.4-MariaDB, for Linux (x86_64) using readline 5.1
Connection id: 4
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.5.4-MariaDB-log MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 32 min 14 sec
Threads: 6 Questions: 27 Slow queries: 0 Opens: 28 Open tables: 9 Queries per second avg: 0.013
--------------
MariaDB 10.5的进程名改为了mariadbd了,需要修改数据库的检测脚本 节点1和节点2上的MariaDB的检测脚本修改成如下内容
#!/bin/bash
if [ $(ps -C mariadbd --no-header | wc -l) -eq 0 ]; then
systemctl stop keepalived
fi
节点1和节点2分别重新启动keepalived程序
节点1:
systemctl start keepalived
节点2:
systemctl restart keepalived