以下教程仅适用于个人用户
Prerequisite
mysql Ver 8.4.2 for Linux on aarch64 (MySQL Community Server - GPL)
使用mysqldump命令进行备份
mysqldump全量备份
mysqldump --all-databases > all_databases_backup.sqlmysqldump打包分别备份特定数据库信息
查看 root 用户下的所有数据库
root@hetzner-fsn1-002:~# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 20018
Server version: 8.4.2 MySQL Community Server - GPL
Copyright (c) 2000, 2024, 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 |
+--------------------+
| e5subbot |
| grafana |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.01 sec)现若分别导出 e5subbot 和 grafana 两个数据库
#mysqldump --databases e5subbot grafana > dump.sql
mysqldump --databases e5subbot > e5subbot.sql
mysqldump --databases grafana > grafana.sql升级MySQL
停止数据库
第一步已备份完毕，确保备份文件无误后先进行停止数据库的操作
systemctl stop mysql.service二进制安装
mv /usr/local/mysql /usr/local/mysql_old通过备份文件恢复MySQL数据库
从全量备份数据恢复
mysql < all_databases_backup.sql查看恢复情况，可以看到所有数据库已恢复完成
root@hetzner-fsn1-002:~# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 57
Server version: 8.4.3 MySQL Community Server - GPL
Copyright (c) 2000, 2024, 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 |
+--------------------+
| e5subbot |
| grafana |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.01 sec)
mysql> SELECT user, host FROM mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| root | 10.0.0.2 |
| e5subbot | localhost |
| grafana | localhost |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
7 rows in set (0.00 sec)再通过 phpMyAdmin 登录实例 10.0.0.3 的 MySQL，出现权限错误
1130: Host '10.0.0.2' is not allowed to connect to this MySQL server此时刷新 MySQL 系统权限
FLUSH PRIVILEGES;root@hetzner-fsn1-002:~# mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 120
Server version: 8.4.3 MySQL Community Server - GPL
Copyright (c) 2000, 2024, 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> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)再登录 MySQL 实例即可
参考资料
使用Mysqldump备份和还原MySQL数据库-腾讯云开发者社区-腾讯云