MariaDB 数据库的数据备份、版本更新
数据备份：
数据备份是为了防止数据丢失或发生意外故障而进行的关键任务。在备份过程中，将数据库的结构和数据以一定的格式保存在文件中，以便在需要时进行还原。对于MariaDB数据库，可以使用工具如mysqldump来进行数据备份。通过定期备份数据库，可以保护数据的完整性和可恢复性，以应对可能发生的数据损坏、误操作或灾难性事件。
[root@long ~]# mkdir mysql
[root@long ~]# cd mysql
# 备份服务器所有数据
[root@long mysql]# mysqldump -uroot -p13579@ -A > allbak.sql
# 备份一个数据库数据
[root@long mysql]# mysqldump -uroot -p13579@ -B wordpress > wordpressbak.sql
[root@long mysql]# ls
allbak.sql wordpressbak.sql版本更新
版本更新是为了获得新功能、修复错误和提高性能而进行的数据库软件的升级过程。MariaDB作为一个开源的关系型数据库管理系统，每个新版本都会带来一系列的改进和更新。版本更新可能包括数据库引擎的改进、新功能的添加、性能优化等。在进行版本更新前，应该先备份数据库，以防止升级过程中出现问题导致数据丢失。然后，根据更新文档或官方指南，按照指定的步骤进行版本更新。
获取最新 MariaDB 版本 yum 源网址： https://mariadb.org/download/?t=mariadb&p=mariadb&r=11.2.2&os=windows&cpu=x86_64&pkg=msi&m=xtom_hk
# 之前版本 5.5
[root@long ~]# mysql --version
mysql Ver 15.1 Distrib 5.5.68-MariaDB, for Linux (x86_64) using readline 5.1
# 更新版本 10.6
# 配置 yum 源
[root@long ~]# vim /etc/yum.repos.d/mariadb.repo
#riaDB 10.6 CentOS repository list - created 2023-11-26 08:55 UTC
# https://mariadb.org/download/
[mariadb]
name = MariaDB
# rpm.mariadb.org is a dynamic mirror if your preferred mirror goes offline. See https://mariadb.org/mirrorbits/ for details.
# baseurl = https://rpm.mariadb.org/10.6/centos/$releasever/$basearch
baseurl = https://mirrors.aliyun.com/mariadb/yum/10.6/centos/$releasever/$basearch
module_hotfixes = 1
# gpgkey = https://rpm.mariadb.org/RPM-GPG-KEY-MariaDB
gpgkey = https://mirrors.aliyun.com/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck = 1
# 安装 10.6 版本
[root@long ~]# yum install MariaDB-server MariaDB-client
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* remi-php72: mirrors.tuna.tsinghua.edu.cn
* remi-php82: mirrors.tuna.tsinghua.edu.cn
* remi-safe: mirrors.tuna.tsinghua.edu.cn
base | 3.6 kB 00:00:00
epel | 4.7 kB 00:00:00
extras | 2.9 kB 00:00:00
mariadb | 3.4 kB 00:00:00
remi-php72 | 3.0 kB 00:00:00
remi-php82 | 3.0 kB 00:00:00
remi-safe | 3.0 kB 00:00:00
updates | 2.9 kB 00:00:00
(1/2): mariadb/7/x86_64/updateinfo | 5.6 kB 00:00:00
(2/2): mariadb/7/x86_64/primary_db | 73 kB 00:00:00
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-client.x86_64 0:10.6.16-1.el7.centos will be obsoleting
--> Processing Dependency: MariaDB-common for package: MariaDB-client-10.6.16-1.el7.centos.x86_64
--> Processing Dependency: libpmem.so.1(LIBPMEM_1.0)(64bit) for package: MariaDB-client-10.6.16-1.el7.centos.x86_64
--> Processing Dependency: libpcre2-8.so.0()(64bit) for package: MariaDB-client-10.6.16-1.el7.centos.x86_64
--> Processing Dependency: libpmem.so.1()(64bit) for package: MariaDB-client-10.6.16-1.el7.centos.x86_64
---> Package MariaDB-server.x86_64 0:10.6.16-1.el7.centos will be obsoleting
--> Processing Dependency: galera-4 for package: MariaDB-server-10.6.16-1.el7.centos.x86_64
--> Processing Dependency: lsof for package: MariaDB-server-10.6.16-1.el7.centos.x86_64
---> Package mariadb.x86_64 1:5.5.68-1.el7 will be obsoleted
---> Package mariadb-server.x86_64 1:5.5.68-1.el7 will be obsoleted
--> Running transaction check
---> Package MariaDB-common.x86_64 0:10.6.16-1.el7.centos will be installed
--> Processing Dependency: MariaDB-compat for package: MariaDB-common-10.6.16-1.el7.centos.x86_64
---> Package galera-4.x86_64 0:26.4.16-1.el7.centos will be installed
--> Processing Dependency: libboost_program_options-mt.so.1.53.0()(64bit) for package: galera-4-26.4.16-1.el7.centos.x86_64
---> Package libpmem.x86_64 0:1.5.1-2.1.el7 will be installed
---> Package lsof.x86_64 0:4.87-6.el7 will be installed
---> Package pcre2.x86_64 0:10.23-2.el7 will be installed
--> Running transaction check
---> Package MariaDB-compat.x86_64 0:10.6.16-1.el7.centos will be obsoleting
---> Package boost-program-options.x86_64 0:1.53.0-28.el7 will be installed
---> Package mariadb-libs.x86_64 1:5.5.68-1.el7 will be obsoleted
--> Finished Dependency Resolution
Dependencies Resolved
=============================================================================================================================================================================
Package Arch Version Repository Size
=============================================================================================================================================================================
Installing:
MariaDB-client x86_64 10.6.16-1.el7.centos mariadb 14 M
replacing mariadb.x86_64 1:5.5.68-1.el7
MariaDB-compat x86_64 10.6.16-1.el7.centos mariadb 2.2 M
replacing mariadb-libs.x86_64 1:5.5.68-1.el7
MariaDB-server x86_64 10.6.16-1.el7.centos mariadb 25 M
replacing mariadb-server.x86_64 1:5.5.68-1.el7
Installing for dependencies:
MariaDB-common x86_64 10.6.16-1.el7.centos mariadb 82 k
boost-program-options x86_64 1.53.0-28.el7 base 156 k
galera-4 x86_64 26.4.16-1.el7.centos mariadb 10 M
libpmem x86_64 1.5.1-2.1.el7 base 59 k
lsof x86_64 4.87-6.el7 base 331 k
pcre2 x86_64 10.23-2.el7 base 201 k
Transaction Summary
=============================================================================================================================================================================
Install 3 Packages (+6 Dependent packages)
Total download size: 53 M
Is this ok [y/d/N]: y
Downloading packages:
warning: /var/cache/yum/x86_64/7/mariadb/packages/MariaDB-common-10.6.16-1.el7.centos.x86_64.rpm: Header V4 RSA/SHA512 Signature, key ID c74cd1d8: NOKEY 0 B --:--:-- ETA
Public key for MariaDB-common-10.6.16-1.el7.centos.x86_64.rpm is not installed
(1/9): MariaDB-common-10.6.16-1.el7.centos.x86_64.rpm | 82 kB 00:00:00
(2/9): MariaDB-compat-10.6.16-1.el7.centos.x86_64.rpm | 2.2 MB 00:00:01
(3/9): boost-program-options-1.53.0-28.el7.x86_64.rpm | 156 kB 00:00:00
(4/9): MariaDB-client-10.6.16-1.el7.centos.x86_64.rpm | 14 MB 00:00:07
(5/9): lsof-4.87-6.el7.x86_64.rpm | 331 kB 00:00:00
(6/9): pcre2-10.23-2.el7.x86_64.rpm | 201 kB 00:00:00
(7/9): libpmem-1.5.1-2.1.el7.x86_64.rpm | 59 kB 00:00:00
(8/9): galera-4-26.4.16-1.el7.centos.x86_64.rpm | 10 MB 00:00:04
(9/9): MariaDB-server-10.6.16-1.el7.centos.x86_64.rpm | 25 MB 00:00:12
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 3.7 MB/s | 53 MB 00:00:14
Retrieving key from https://mirrors.aliyun.com/mariadb/yum/RPM-GPG-KEY-MariaDB
Importing GPG key 0x1BB943DB:
Userid : "MariaDB Package Signing Key <package-signing-key@mariadb.org>"
Fingerprint: 1993 69e5 404b d5fc 7d2f e43b cbcb 082a 1bb9 43db
From : https://mirrors.aliyun.com/mariadb/yum/RPM-GPG-KEY-MariaDB
Is this ok [y/N]: y
Importing GPG key 0xC74CD1D8:
Userid : "MariaDB Signing Key <signing-key@mariadb.org>"
Fingerprint: 177f 4010 fe56 ca33 3630 0305 f165 6f24 c74c d1d8
From : https://mirrors.aliyun.com/mariadb/yum/RPM-GPG-KEY-MariaDB
Is this ok [y/N]: y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : MariaDB-compat-10.6.16-1.el7.centos.x86_64 1/12
Installing : MariaDB-common-10.6.16-1.el7.centos.x86_64 2/12
Installing : libpmem-1.5.1-2.1.el7.x86_64 3/12
Installing : lsof-4.87-6.el7.x86_64 4/12
Installing : pcre2-10.23-2.el7.x86_64 5/12
Installing : MariaDB-client-10.6.16-1.el7.centos.x86_64 6/12
Installing : boost-program-options-1.53.0-28.el7.x86_64 7/12
Installing : galera-4-26.4.16-1.el7.centos.x86_64 8/12
Installing : MariaDB-server-10.6.16-1.el7.centos.x86_64 9/12
Erasing : 1:mariadb-server-5.5.68-1.el7.x86_64 10/12
warning: /var/log/mariadb/mariadb.log saved as /var/log/mariadb/mariadb.log.rpmsave
Erasing : 1:mariadb-5.5.68-1.el7.x86_64 11/12
Erasing : 1:mariadb-libs-5.5.68-1.el7.x86_64 12/12
Verifying : pcre2-10.23-2.el7.x86_64 1/12
Verifying : galera-4-26.4.16-1.el7.centos.x86_64 2/12
Verifying : MariaDB-server-10.6.16-1.el7.centos.x86_64 3/12
Verifying : MariaDB-client-10.6.16-1.el7.centos.x86_64 4/12
Verifying : MariaDB-common-10.6.16-1.el7.centos.x86_64 5/12
Verifying : lsof-4.87-6.el7.x86_64 6/12
Verifying : MariaDB-compat-10.6.16-1.el7.centos.x86_64 7/12
Verifying : boost-program-options-1.53.0-28.el7.x86_64 8/12
Verifying : libpmem-1.5.1-2.1.el7.x86_64 9/12
Verifying : 1:mariadb-server-5.5.68-1.el7.x86_64 10/12
Verifying : 1:mariadb-5.5.68-1.el7.x86_64 11/12
Verifying : 1:mariadb-libs-5.5.68-1.el7.x86_64 12/12
Installed:
MariaDB-client.x86_64 0:10.6.16-1.el7.centos MariaDB-compat.x86_64 0:10.6.16-1.el7.centos MariaDB-server.x86_64 0:10.6.16-1.el7.centos
Dependency Installed:
MariaDB-common.x86_64 0:10.6.16-1.el7.centos boost-program-options.x86_64 0:1.53.0-28.el7 galera-4.x86_64 0:26.4.16-1.el7.centos libpmem.x86_64 0:1.5.1-2.1.el7
lsof.x86_64 0:4.87-6.el7 pcre2.x86_64 0:10.23-2.el7
Replaced:
mariadb.x86_64 1:5.5.68-1.el7 mariadb-libs.x86_64 1:5.5.68-1.el7 mariadb-server.x86_64 1:5.5.68-1.el7
Complete!
# 检查跟新后的版本
[root@long ~]# mysql --version
mysql Ver 15.1 Distrib 10.6.16-MariaDB, for Linux (x86_64) using readline 5.1
# 重启服务
[root@long ~]# systemctl restart mysql
[root@long ~]# systemctl status mysql
● mariadb.service - MariaDB 10.6.16 database server
Loaded: loaded (/usr/lib/systemd/system/./mariadb.service; disabled; vendor preset: disabled)
Drop-In: /etc/systemd/system/mariadb.service.d
└─migrated-from-my.cnf-settings.conf
Active: active (running) since Sun 2023-11-26 17:02:24 CST; 2s ago
Docs: man:mariadbd(8)
https://mariadb.com/kb/en/library/systemd/
Process: 14158 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Process: 14137 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
Process: 14134 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
Main PID: 14148 (mariadbd)
Status: "Taking your SQL requests now..."
CGroup: /system.slice/mariadb.service
└─14148 /usr/sbin/mariadbd
Nov 26 17:02:24 long mariadbd[14148]: 2023-11-26 17:02:24 0 [Note] Server socket created on IP: '0.0.0.0'.
Nov 26 17:02:24 long mariadbd[14148]: 2023-11-26 17:02:24 0 [Note] Server socket created on IP: '::'.
Nov 26 17:02:24 long mariadbd[14148]: 2023-11-26 17:02:24 0 [ERROR] Missing system table mysql.roles_mapping; please run mysql_upgrade to create it
Nov 26 17:02:24 long mariadbd[14148]: 2023-11-26 17:02:24 0 [ERROR] Incorrect definition of table mysql.event: expected column 'definer' at position 3 to have ...e char(77).
Nov 26 17:02:24 long mariadbd[14148]: 2023-11-26 17:02:24 0 [ERROR] Incorrect definition of table mysql.event: expected column 'sql_mode' at position 14 to hav...EATE','POST
Nov 26 17:02:24 long mariadbd[14148]: 2023-11-26 17:02:24 0 [ERROR] mariadbd: Event Scheduler: An error occurred when initializing system tables. Disabling the... Scheduler.
Nov 26 17:02:24 long mariadbd[14148]: 2023-11-26 17:02:24 1 [Warning] Failed to load slave replication state from table mysql.gtid_slave_pos: 1146: Table 'mysq...esn't exist
Nov 26 17:02:24 long mariadbd[14148]: 2023-11-26 17:02:24 0 [Note] /usr/sbin/mariadbd: ready for connections.
Nov 26 17:02:24 long mariadbd[14148]: Version: '10.6.16-MariaDB' socket: '/var/lib/mysql/mysql.sock' port: 3306 MariaDB Server
Nov 26 17:02:24 long systemd[1]: Started MariaDB 10.6.16 database server.
Hint: Some lines were ellipsized, use -l to show in full.