首页 » MySQL » 正文

MariaDB主主模式下的版本升级

操作系统:CentOS 7.8
MariaDB版本:5.5.x→10.5.x
节点IP地址数据库版本
节点110.40.200.191MariaDB 5.5.x
节点210.40.200.192MariaDB 5.5.x
VIP10.40.200.190xxx

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

发表评论