The "Night-Up" Level MySQL Documentation for Database Deploy, Upgrade, Backup and Restore

Published: February 24, 2026 by Rain

Categories:

The "Night-Up" Level MySQL Documentation for Database Deploy, Upgrade, Backup and Restore | 「起夜」級 MySQL 數據庫部署、升級、備份及恢復文檔

⚠️ WARNING: THE COMMANDS/SCRIPTS IN THIS PASSAGE ARE ALMOST RUN UNDER root PRIVILEDGES. PLEASE BE CAREFUL AND MAKE SURE WHAT YOU ARE DOING BEFORE RUNNING THOSE COMMANDS/SCRIPTS IN REAL ENVIRONMENTS.

⚠️ 警告：本文所包含之命令/腳本幾乎全部都使用 root 權限執行。請務必小心並且確保你知道自己喺度做緊乜嘢之後再於真實環境之中執行命令。

ℹ Tips: The terms "master" and "slave" used in this article are only used to indicate the architectural relationship between database servers and do not refer to any group or have any derogatory connotation.

ℹ 提示：本文中所出現之「主」「從」等字眼只係用於表示數據庫服務器之架構關係，並無指代任何群體且無任何貶意。

1. Self Introduction of Documentation | 文檔自述

This documentation is for the installation, deployment, upgrading, main/duplicated database, backup and restoration.

本文檔適用於 MySQL 喺 Linux 服務器環境之下嘅安裝部署、系統升級、主備數據庫搭建以及備份恢復之操作。

Under the Linux system environment, there are mainly 3 ways to install MySQL: install via rpm, install via binary packages and build from source code. Installing via rpm is simple, but it is not flexible enough, and one machine can only install one copy of MySQL database. The way building from source code is complex and may take a long time, but it can be configured by hand, so we do not take this way in general. This documentation recommends the second method to complete the installation.

喺 Linux 系統環境下， MySQL 嘅安裝主要有三種方式：通過 rpm 安裝、使用二進制軟件包安裝以及使用源碼編譯安裝。採用 rpm 包安裝嘅方式操作簡單，但其缺點係唔夠靈活，而且一台機器只可以安裝一個 MySQL 數據庫；採用二進制軟件包安裝嘅方式操作較為繁瑣且耗時較長，但可以根據實際情況手動優化編譯選項，一般情況下唔需要使用呢種方法進行安裝部署。本文檔描述採用 MySQL 二進制軟件包進行安裝部署之操作步驟。

For version upgrade operations in MySQL, this document stipulates that the number before the first decimal point from left to right in the post-MySQL version number is the large version number, and the number after the decimal point is the small version number. For example, in MySQL 8.0.36, 8 is the major version and 0.36 is the minor version; Small version upgrades adopt direct upgrade scheme, large version upgrade adopts logical data migration upgrade scheme. It should be noted that in the event of a MySQL version upgrade involving changes to configuration items in the MySQL configuration file and scrapping, the operation should be treated as a major version upgrade regardless of the version number size.

對於 MySQL 嘅版本升級操作，本文檔規定 MySQL 後版本數字中由左至右第一個小數點前嘅數字係大版本，小數點後嘅數字係小版本。例如 MySQL 8.0.36 中 8 係大版本，0.36 係小版本； MySQL 8.1 中 8 係大版本，1 係小版本。小版本升級採用直接升級方案，大版本升級採用邏輯數據遷移升級方案。需要注意嘅係，若涉及到 MySQL 配置文件中嘅配置項嘅更改及廢棄嘅 MySQL 版本升級，無論版本數字大小，都應該當做大版本升級嚟進行操作。

The MySQL main and duplicated database construction described in this document contains two architectures, a main-duplicated architecture (data is synchronized from the main to the duplicate) and a two-host architecture (two mains synchronize data with each other and are each other's duplicates). Building a main-duplicated library generally adopts a main-duplicated architecture, in which there must be a main node, the main library, and one or more duplicated nodes, the duplicated library. All data is written to the main node first, and then other nodes copy the incremental data on the main node, thus guaranteeing the consistency of the data. Building a main-duplicated database using a main-duplicated replication scheme will improve the availability and performance of the database. In a one-main-duplicated architecture, all data from the duplicated node (duplicated library) comes from the main node, and in general, the use of read-write separation model on this architecture is able to adequately resource, sending the client's read data request to the duplicated, the client's write request to the host, and then the duplicated synchronizes the newly written data from the main. In this case the throughput of the database can be greatly improved since the read-write requests are distributed to the duplicated and main processing respectively.

本文檔描述之 MySQL 主備數據庫搭建包含兩種架構，分別係一個主一從架構（數據從主機同步到從機）同兩個主機架構（兩台主機互相同步數據，互相係對方嘅從機）。搭建主備庫一般採用主從架構，其中必須有一個主節點，即主庫，以及一個或者多個嘅從節點，即備庫。所有數據都會先寫入到主節點，然後其他節點會複製主節點上嘅增量數據，從而保證數據嘅一致性。使用主從複製方案搭建主備庫將提升數據庫嘅可用性及性能。喺一主一從嘅架構中，從節點（備庫）嘅所有數據都係來自主節點，一般而言，喺呢個架構上使用讀寫分離嘅模式能夠充分資源，將客戶端嘅讀數據請求發送畀備機，將客戶端嘅寫數據請求發送畀主機，然後備機從主機處同步新寫入嘅數據。呢種情況下由於讀寫請求分別分攤到咗備機同主機上處理，所以可以極大提升數據庫嘅吞吐量。

Database backup derives and stores the data and structure of the database in an external location so that the database can be recovered in the event of data loss or corruption. There are two types of MySQL database backups described in this document: physical backup (raw backup) and logical backup. Among them, physical backups (logical backups) directly copy database files, including but not limited to data table files and log files, and logical backups backup SQL statement files, deriving all SQL statements that record data and structure operations.

數據庫備份將數據庫嘅數據同結構導出並儲存喺外部位置，令數據庫喺數據丟失或者損壞之時可以恢復。本文件描述嘅 MySQL 數據庫備份方式有物理備份（原始備份）同邏輯備份兩種。其中，物理備份（邏輯備份）直接複製數據庫文件，包括但唔限於數據表文件同日誌文件；邏輯備份則備份 SQL 語句文件，將記錄數據同結構操作嘅所有 SQL 語句導出。

Finally, this document contains some of the problems encountered during the testing process during MySQL installation, deployment, upgrade, backup, etc., and provides a problem analysis and solution for reference when actually deploying MySQL.

最後，本文件收錄咗一啲喺測試過程中喺 MySQL 安裝、部署、升級、備份等過程中遇到嘅問題，並提供咗問題分析及解決方法，以供實際部署 MySQL 時參考。

2. Command Line Prompt Examples | 命令提示示例

The text in this document using isowidth font with dark underprint is the command line raw text in the terminal. where the command that needs to be entered is preceded by a one-character permission prompt. The # symbol indicates that the next command was entered under the test machine's root privileges, the $ symbol indicates that the next command was entered under the test machine's ordinary user privileges, and the > symbol indicates that the next command requires a login to the MySQL command line console for operation. The permission prompt is automatically displayed by the computer terminal when the command is actually entered, eliminating the need for the user to manually enter this permission prompt as well as the space immediately following the symbol.

本文中使用等寬字體並帶有深色底紋嘅文本係終端中嘅命令行原始文本。其中需要輸入嘅命令前帶有一個字符嘅權限提示符。# 符號表示接下來嘅一條命令喺測試機嘅 root 權限下輸入；$ 符號表示接下來嘅一條命令喺測試機嘅普通用戶權限下輸入；> 符號表示接下來嘅命令需要登入 MySQL 命令行控制台進行操作。實際輸入命令時權限提示符號由電腦終端自動顯示，唔需要用戶手動輸入呢個權限提示符號以及符號後緊跟嘅空格。

3. Installation and Deployment | 安裝部署

3.1. Check the current environment of the machine | 檢查機器當前嘅環境

Check the current system information about the computer, and make sure they are matched with the MySQL for installation. Use uname tool can check the current system and architecture information easily.

檢查當前機器嘅系統信息，確保呢啲信息同需要安裝嘅 MySQL 數據庫軟件相匹配。使用 uname 工具可以方便地查看當前嘅系統和架構信息。

# uname -a
Linux clz-db-t2 3.10.0-693.el7.x86_64 #1 SMP Thu Jul 6 19:56:57 EDT 2017 x86_64 x86_64 x86_64 GNU/Linux

Use ldd tool to check the version of system's GNU libc (Glibc).

使用 ldd 工具來檢查系統嘅 GNU libc (Glibc) 版本。

# ldd --version
ldd (GNU libc) 2.17
Copyright (C) 2012 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
Written by Roland McGrath and Ulrich Drepper.

The compatibility of the current machine with the required MySQL version is determined based on the system version, architecture, and glibc version, thus deciding which MySQL version to use. For example, the test machine in this case runs Red Hat Enterprise Linux 7, an x86_64 architecture, and uses GNU libc 2.17. Therefore, when selecting the MySQL binary package, a version compatible with el7, an x86_64 architecture, and based on glibc 2.17 should be chosen.

兼容性要求當前呢台機器需要對應嘅 glibc 版本，因此亦都會決定邊啲 MySQL 版本可以使用。例如，依家情況下有一台測試機運行著 Red Hat Enterprise Linux，並且使用 GNU libc 2.17。因此，當選擇 MySQL 二進制包版本嘅時候就應該選擇一個兼容 el7，x86_64 架構，並且基於 glibc 2.17 嘅 MySQL 版本。

If the system architecture is displayed as aarch64, then an aarch64 or arm64 package should be selected; if the system architecture is displayed as x86_64, then an x86_64, x64, or amd64 package should be selected. The specific naming of the architecture used by the package depends on the software distributor.

如果系統架構顯示為 aarch64，咁應該選擇 aarch64 或者 arm64 包；如果系統架構顯示為 x86_64，咁應該選擇 x86_64、 x64 或者 amd64 包。包所用嘅架構嘅具體命名取決於軟件分銷商。

3.2. Download MySQL package | 下載 MySQL 軟件包

This document describes how to deploy MySQL using a binary package installation. From the official MySQL archive download page (https://downloads.mysql.com/archives/community/), select the required MySQL package and copy the link to that binary archive. On the machine where you want to deploy MySQL, use the wget tool to pull the MySQL binary package from that link and place it in the /usr/local directory. On the machine without an internet connection, use the ssh tool to upload the MySQL binary package from your local machine to the server.

呢份文檔會講解點樣用二進制包安裝部署 MySQL。喺官方嘅 MySQL 封存檔案下載頁面（https://downloads.mysql.com/archives/community/），選擇所需嘅 MySQL 包，然後將連結複製去嗰個二進制封存檔案。喺你想部署 MySQL 嘅機器上面，用 wget 工具喺嗰個連結度拉出 MySQL 二進制包，然後將佢放入 /usr/local 目錄。喺冇互聯網連線嘅機器上面，用 ssh 工具將 MySQL 二進制包由你嘅本地機器上載去伺服器。

# cd /usr/local
# wget "https://cdn.mysql.com/archives/mysql-8.0/mysql-8.1.0-linux-glibc2.17-x86_64.tar.xz"

You can use the ls command in the /usr/local directory to view the files and directories in the current directory, ensuring that the MySQL binary package has been downloaded and placed in the specified location.

你可以用 /usr/local 目錄入面嘅 ls 命令嚟睇返當前目錄入面嘅檔案同目錄，確保 MySQL 二進制包已經下載好，然後放喺指定位置。

# ls -lah
total 428M
drwxr-xr-x. 16 root root 4.0K Jul 31 17:27 .
drwxr-xr-x. 14 root root  166 Feb 27  2019 ..
drwxr-xr-x.  2 root root   37 Aug 10  2020 bin
drwxr-xr-x.  2 root root    6 Mar 10  2016 etc
drwxr-xr-x.  2 root root    6 Mar 10  2016 games
drwxr-xr-x.  3 root root   21 Aug 10  2020 include
drwxr-xr-x.  2 root root    6 Mar 10  2016 lib
drwxr-xr-x.  4 root root  159 Aug 10  2020 lib64
drwxr-xr-x.  2 root root    6 Mar 10  2016 libexec
-rw-r--r--   1 root root 428M Jun 28  2023 mysql-8.1.0-linux-glibc2.17-x86_64.tar.xz
drwxr-xr-x   5 root root   44 Mar 26  2018 openssh.bak
drwxr-xr-x.  2 root root    6 Mar 10  2016 sbin
drwxr-xr-x.  6 root root   60 Aug 10  2020 share
drwxr-xr-x.  4 root root  106 Feb 27  2019 src
drwxr-xr-x   9 root root  188 Aug 10  2020 ssl

Use the tar tool to extract the package (in this example, "mysql-8.1.0-linux-glibc2.17-x86_64.tar.xz") and place it in the same directory.

用「tar」工具去解壓縮個包（喺呢個例子入面，係「mysql-8.1.0-linux-glibc2.17-x86_64.tar.xz」），然後將佢放入同一個目錄。

# tar -xvf mysql-8.1.0-linux-glibc2.17-x86_64.tar.xz

The directory /usr/local/mysql-8.1.0-linux-glibc2.17-x86_64 is obtained.

得到咗個目錄「/usr/local/mysql-8.1.0-linux-glibc2.17-x86_64」。

3.3. Setup users and groups | 建立用戶和組

Use the useradd command to create the mysql user group, and then create a new mysql user within that group.

用 useradd 命令去建立 mysql 用戶群組，然後喺嗰個群組入面建立一個新嘅 mysql 用戶。

# groupadd mysql
# useradd -r -g mysql -s /bin/false mysql

3.4. Setup configuration files and directories | 建立配置文件和相關目錄

Configure the my.cnf configuration file as follows and save it in the /etc directory. You can open and paste it using the Vim editor.

配置 my.cnf 配置文件，然後將佢放入 /etc 目錄之中。你可以喺 Vim 編輯器之中打開並且貼上內容。

# vim /etc/my.cnf

Paste the following contents.

貼上下面呢啲內容。

[client]
#ssl-mode=REQUIRED
port = 3366
socket = /tmp/mysql.sock

[mysql]
prompt="\\u@\\h:\p \\R:\\m:\\s [\\d]>"
#tee=/data/mysql/mysql_3366/data/query.log
no-auto-rehash
default-character-set=utf8
socket = /tmp/mysql.sock

[mysqld]
#time_zone='+8:00'
require_secure_transport=off
basedir = /usr/local/mysql

#misc
user = mysql
datadir =/data/mysql/data/
port = 3366
socket = /tmp/mysql.sock
event_scheduler = off
#tmp
tmpdir=/tmp

#timeout
interactive_timeout = 1800
wait_timeout = 1800

#character set
character-set-server = utf8
lower_case_table_names=1
#character-set-server = utf8mb4
#sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

open_files_limit = 10240
max_connections =1000
max_connect_errors = 100

#monitor
#userstat=1

skip-name-resolve = on
#logs
log-output=file
slow_query_log = 1
slow_query_log_file = /data/mysql/logs/mysqlslow.log
log-error = /data/mysql/logs/mysqlerror.log
#log_error_verbosity =
pid-file = /tmp/mysql.pid
long_query_time = 0.1
#log-slow-admin-statements =
#log-queries-not-using-indexes =
log-slow-slave-statements = off

#binlog
binlog_checksum = CRC32
binlog_format = row
server-id = 22567
log-bin = /data/mysql/binlog/mysql-bin
log-bin-index = /data/mysql/binlog/mysql-bin.index
binlog_cache_size = 32M
max_binlog_size = 1G
max_binlog_cache_size = 1G
sync_binlog = 1
expire_logs_days = 7
sync_master_info=10000
#skip-log-bin

#relay log
relay-log = /data/mysql/binlog/mysql-relay-bin
skip_slave_start =on
max_relay_log_size = 1G
relay_log_purge =on
relay_log_recovery =on
log_slave_updates = on
#slave-skip-errors=,,
sync_relay_log_info=10000

slave_rows_search_algorithms=INDEX_SCAN,HASH_SCAN

explicit_defaults_for_timestamp=off
#buffers & cache
table_open_cache = 10000
table_definition_cache = 10000
max_allowed_packet = 1073741824 
max_heap_table_size = 256M
sort_buffer_size = 16M
join_buffer_size = 16M
#thread_cache_size = 0
#query_cache_size = 0
#query_cache_type = 0
#query_cache_limit = 256K
thread_stack = 192K
tmp_table_size = 96M
key_buffer_size = 8M
read_buffer_size = 16M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M

#myisam
#myisam_sort_buffer_size = 128M
#myisam_max_sort_file_size = 10G
#myisam_repair_threads = 

#innodb
innodb_adaptive_hash_index=off
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 43
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 1G
innodb_log_file_size = 2G
innodb_log_files_in_group = 3
#innodb_max_dirty_pages_pct =
innodb_file_per_table = on
#innodb_rollback_on_timeout
#innodb_status_file =
#innodb_io_capacity = 50000
innodb_io_capacity=200
innodb_io_capacity_max=400
transaction_isolation = REPEATABLE-READ

#innodb_flush_method = O_DIRECT

gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay-log-info-repository = TABLE
innodb_undo_tablespaces=0
innodb_undo_log_truncate = on
innodb_lock_wait_timeout=10

# replication
#rpl_semi_sync_master_enabled = 0;
#rpl_semi_sync_master_timeout = 3600;

# Two-Master configure
#server-
#auto-increment-offset =
#auto-increment-increment = 

#server-
#auto-increment-offset =
#auto-increment-increment = 

slave_preserve_commit_order = on
slave_transaction_retries = 10
log_timestamps = system
#show_compatibility_56 = on
slave_parallel_workers = 4
slave_parallel_type = LOGICAL_CLOCK

interactive_timeout=3600
wait_timeout=3600
lock_wait_timeout=60
max_user_connections=800
#log_warnings=0
#log_error_verbosity=0
#optimizer_switch='index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=off,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,favor_range_scan=off'

[mysqld_safe]
nice=-19
open-files-limit=10240

Save this configuration file after pasting. Create the MySQL-related directories to store the database's data files and logs.

貼上之後儲存呢個設定檔案。建立 MySQL 相關目錄嚟儲存資料庫嘅數據檔案同記錄。

# mkdir -p /data/mysql/data
# mkdir -p /data/mysql/binlog
# mkdir -p /data/mysql/logs

After creating the directory, switch the current directory to /data and grant ownership of the directory to the mysql user and group.

建立目錄之後，將當前目錄切換為 /data，並將目錄嘅擁有權授予 mysql 用戶同群組。

# cd /data
# chown -R mysql:mysql /data/mysql/

3.5. Install | 安裝

Switch to the directory where the MySQL package is located, /usr/local.

切入去 MySQL 包嘅目錄，即係 /usr/local。

# cd /usr/local

To facilitate subsequent deployment and use, create a symbolic link to the MySQL package: /usr/local/mysl.

為咗方便部署及使用，創建一個軟鏈接，連接到 MySQL 嘅軟件包 /usr/local/mysl。

# ln -s /usr/local/mysql-8.1.0-linux-glibc2.17-x86_64 /usr/local/mysql

Check if the symbolic link was created successfully.

檢查此軟鏈接係咪已經成功創建。

# ls -l | grep mysql
lrwxrwxrwx  1 root root        45 Jul 24 13:50 mysql -> /usr/local/mysql-8.1.0-linux-glibc2.17-x86_64
drwxr-xr-x  9 root root       129 Jul 24 13:40 mysql-8.1.0-linux-glibc2.17-x86_64
-rw-r--r--  1 root root 448377800 Jun 28  2023 mysql-8.1.0-linux-glibc2.17-x86_64.tar.xz

3.6. Initialization | 初始化

Use the mysqld program to initialize the MySQL server.

使用 mysqld 程式來初始化 MySQL 伺服器。

# /usr/local/mysql/bin/mysqld --initialize --user=mysql

Copy the startup script that comes with the MySQL support files and place it in the /etc/init.d directory.

複製 MySQL 支援檔案隨附嘅啟動指令腳本，然後將佢放入 /etc/init.d 目錄。

# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql.server

Use service tool to start the MySQL services.

使用 service 工具來啟動 MySQL 服務。

# service mysql.server start

When you see the message "Starting MySQL... SUCCESS!", it means the MySQL database has started successfully. The temporary password generated upon initial startup can be found in the MySQL logs. This password will be required for the first login, and you should change your account password immediately after logging into MySQL. According to the configuration file my.cnf, the MySQL error log is stored in /data/mysql/logs/mysqlerror.log. You can directly use the cat and grep tools to search for the keyword "password" to find the temporary password generated by MySQL.

當你見到「Starting MySQL... SUCCESS!」嘅訊息，即係 MySQL 資料庫已經成功啟動。初次啟動時產生嘅臨時密碼可以喺 MySQL 記錄入面搵到。第一次登入需要呢個密碼，而你應該喺登入 MySQL 之後即刻更改你嘅帳戶密碼。根據配置檔案 my.cnf， MySQL 錯誤記錄儲存喺 /data/mysql/logs/mysqlerror.log。你可以直接用 cat 同 grep 工具去搜尋關鍵字「password」嚟搵 MySQL 生成嘅臨時密碼。

# cat /data/mysql/logs/mysqlerror.log | grep password
2025-07-21T17:10:07.293097+08:00 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: jnPW*+Lse52Z

"jnPW*+Lse52Z" is the temporary password generated in this example. It's important to note that the temporary password initialized after each MySQL deployment is different; therefore, you must check the logs for this password after each deployment and initialization. Use this password to log in to the MySQL server.

「jnPW*+Lse52Z」係呢個例子入面生成嘅臨時密碼。重要嘅係要留意，每次 MySQL 部署之後初始化嘅臨時密碼都唔同；所以，你必須喺每次部署同初始化之後檢查呢個密碼嘅記錄。用呢個密碼登入 MySQL 伺服器。

# /usr/local/mysql/bin/mysql -uroot -p

Follow the prompts to enter your password. The appearance of the ">" sign indicates successful login to the MySQL command-line interface. Change the password for the root account.

跟住啲提示輸入你嘅密碼。出現「>」號表示成功登入 MySQL 命令行介面。更改 root 帳戶嘅密碼。

> ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';

3.7. Create application account | 創建應用帳戶

Using the MySQL root user for application connections is insecure; therefore, a dedicated account should be created for the application. Following the principle of least privilege, the application-specific account should be granted the necessary permissions. The application account name should be related to the current database ID or the application itself to avoid confusion. For example, on a server with server-id 13, create an account for the application, name it "apn_13", and grant it read and write permissions. The CREATE, DELETE, and DROP permissions should be granted cautiously based on the specific application requirements.

用 MySQL 根用戶嚟連接應用程式係唔安全嘅；所以，應該為個應用程式建立一個專用嘅帳戶。根據最低特權原則，應用程式專用帳戶應該獲得必要嘅權限。應用程式帳戶名稱應該同當前嘅資料庫 ID 或者應用程式本身有關，以免混淆。例如，喺伺服器 ID 為 13 嘅伺服器上面，為應用程式建立一個帳戶，將佢命名為「apn_13」，然後授予佢讀寫權限。應該根據特定嘅應用程式要求，謹慎噉授予 CREATE、DELETE 同 DROP 權限。

> CREATE USER 'apn_13'@'localhost' IDENTIFIED BY 'newpassword';
> GRANT SELECT, INSERT, UPDATE ON database_name.database_table TO 'apn_13'@'localhost';

In the command to create a user, the "newpassword" field needs to be set to a new password that meets the security requirements and is different from the root user's password. In the command to grant permissions, the "database_name" and "database_table" fields should be filled in according to the application's usage. If the application uses all databases and tables in the database, you can fill in "*.*" to represent all databases and tables, for example:

喺建立用戶嘅命令入面，「newpassword」欄位需要設定為一個符合安全要求嘅新密碼，同埋同 root 用戶嘅密碼唔同。喺授予權限嘅命令入面，應該根據應用程式嘅用途填寫「database_name」同「database_table」欄位。如果應用程式用咗資料庫入面嘅所有資料庫同表，你可以填「*.*」嚟表示所有資料庫同表，例如：

> GRANT SELECT, INSERT, UPDATE ON *.* TO 'apn_13'@'localhost';

4. Upgrade | 版本升級

4.1. Small Version Numbers: Upgrade Directly

4.1.1. Check environment

Check the current system environment. We can query for the system information and its MySQL version information from terminal commands. For example the test machine T2:

- IP: 10.91.134.xxx
- OS: Red Hat Enterprise Linux 7
- CPU Architecture: x86_64
- MySQL: 8.0.36 MySQL Community Server
- GLIBC: GNU libc 2.17

From the information above, we can confirmed that the running MySQL version is MySQL 8.0.36.

Then we confirm the service status.

# service mysql.service status

Log into MySQL command line interface and query for current MySQL information in service.

# /usr/local/mysql/bin/mysql -u root -p

Check the current version information.

> SELECT Version();

And then get the version.

+-----------+
| version() |
+-----------+
| 8.0.36    |
+-----------+
1 row in set (0.00 sec)

4.1.2. Download new version of MySQL binary package

Copy download link from official MySQL website and pull down to machine via wget tool. Put the newly downloaded package under the directory /usr/local. In this example we need to upgrade MySQL 8.0.36 to MySQL 8.0.42 so we just wget the package of MySQL 8.0.42.

# cd /usr/local
# wget "https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.42-linux-glibc2.28-x86_64.tar.xz"

Wait until the progress bar finished, then check the /usr/local directory to see whether the package mysql-8.0.42-linux-glibc2.28-x86_64.tar.xz exists. Then use tar to extract the package.

# tar -xvf mysql-8.0.42-linux-glibc2.17-x86_64.tar.xz

Get the directory for new version.

4.1.3. Stop the applications

Release the connection to MySQL database to prevent the read/write problem. This step should be done according to the actual situation.

4.1.4. Stop the service of database

Log into MySQL and run dirty-data writing operations.

> SET GLOBAL innodb_fast_shutdown = 0;

Log out MySQL and shut down MySQL services.

# /usr/local/mysql/bin/mysqladmin -u root -p shutdown

The services will be closed after the password is entered. Check the launch daemons to make sure the services are stopped.

# service mysqld status

If "ERROR! MySQL is not running, but lock file (/var/lock/subsys/mysql) exists" shows up, the MySQL services are shutted down correctly.

4.1.5. Remove old version

Remove the soft link of old version MySQL, and create a new soft link for new version.

# rm -f /usr/local/usr
# ln -s /usr/local/mysql-8.0.42-linux-glibc2.28-x86_64 /usr/local/mysql

4.1.6. Compatibility checking

Use mysqld_safe launch the new version MySQL temporary, then use mysql_upgrade tool attached to MySQL to check the compatibility.

# /usr/local/mysql/bin/mysql_safe --user=mysql
# /usr/local/mysql/bin/mysql_upgrade -u root -p

Then we will get the hints.

The mysql_upgrade client is now deprecated. The actions executed by the upgrade client are now done by the server.
To upgrade, please start the new MySQL binary with the older data directory. Repairing user tables is done automatically. Restart is not required after upgrade.
The upgrade process automatically starts on running a new MySQL binary with an older data directory. To avoid accidental upgrades, please use the --upgrade=NONE option with the MySQL binary. The option --upgrade=FORCE is also provided to run the server upgrade sequence on demand.
It may be possible that the server upgrade fails due to a number of reasons. In that case, the upgrade sequence will run again during the next MySQL server start. If the server upgrade fails repeatedly, the server can be started with the --upgrade=MINIMAL option to start the server without executing the upgrade sequence, thus allowing users to manually rectify the problem.

If there is no compatibility problem we can restart the database. Copy one new version launch daemon from support-files directory to /etc/init.d.

# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
# service mysqld start

Then log into the MySQL and verify the current version.

> SELECT Version();

4.1.7. Roll back

If we run into any compatibility related problems in previous steps, we need to roll back. Restore the MySQL soft link to previous status and restart. Stop MySQL service first if there is MySQL service running.

# service mysqld stop

# /usr/local/mysql/support-files/mysql.server stop

remove the current version soft link.

# rm -f /usr/local/mysql

and create soft link of previous version.

# ln -s /usr/local/mysql-8.0.36-linux-glibc2.28-x86_64 /usr/local/mysql

start previous version's services.

# /usr/local/mysql/support-files/mysql.server start

Log into MySQL to check the version is rolled back to the previous status. If everything is fine, remove the launch daemon in /etc/init.d, and copy one prevous launch daemon to the /etc/init.d directory.

# rm -f /etc/init.d/mysqld
# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

4.2. Large Version Numbers: Logical Data Migration

4.2.1. Check the environment

Confirm the current environment of the machine. From the terminal we can get the current system and MySQL information. In this example, they are:

- IP: 10.91.134.136
- OS: Red Hat Enterprise Linux 7
- CPU Architecture: x86_64
- MySQL: 8.0.36 MySQL Community Server
- GLIBC: GNU libc 2.17

The currently running MySQL is version 8.0.36, and we can easily check the status of MySQL services.

# service mysqld status

Log into MySQL and query for the current MySQL version which is serving.

# /usr/local/mysql/bin/mysql -u root -p

Confirm the current version.

> SELECT Version();

We can get the version is 8.0.36.

+-----------+
| version() |
+-----------+
| 8.0.36    |
+-----------+
1 row in set (0.00 sec)

4.2.2. Download new version of MySQL binary package

Download MySQL from the official MySQL archive website. Copy the target link and use wget tool to download package to machine. Put the newly downloaded MySQL package in /usr/local directory. In this example case, we need to upgrade MySQL from 8.0.42 to MySQL 9.3.0, so we wget the MySQL 9.3.0 package directly.

# cd /usr/local
# wget "https://downloads.mysql.com/archives/get/p/23/file/mysql-9.3.0-linux-glibc2.17-x86_64.tar.xz"

Wait for the progress bar and check the /usr/local directory to see whether there is a mysql-9.3.0-linux-glibc2.17-x86_64.tar.xz package. Then extract the package via tar tool.

# tar -xvf mysql-9.3.0-linux-glibc2.17-x86_64.tar.xz

Now we get the new version MySQL directory. In this case it is mysql-9.3.0-linux-glibc2.17-x86_64.

4.2.3. Export all the data from whole database

# /usr/local/mysql/bin/mysqldump -u root -p --add-drop-table --routines --events --all-databases --force > backup_file.sql

The exported data is contained in the sql file.

4.2.4. Stop applications

Release the connection to database.

4.2.5. Stop database services

Run the write dirty data operation. This operation may need a longer time, and it depends on the amount of dirty data and the speed of the disks.

> SET GLOBAL innodb_fast_shutdown = 0;

4.2.6. Soft link to new MySQL

Remove old MySQL soft link first, and then create a new MySQL soft link for new version.

# rm -f /usr/local/mysql
# ln -s /usr/local/mysql-9.3.0-linux-glibc2.28-x86_64 /usr/local/mysql

4.2.7. Prepare a configuration file

Backup old MySQL configurations my.cnf and move the file to a backup directory.

# mv /etc/my.cnf /data/backup/backup_8.0.42.cnf

Check the configuration file for new MySQL to make sure it is matched with the update descriptions of newer MySQL versions. For those changed configurations items and deprecated items, new alternatives should be applied to the configuration file. For issues made by changes of the configurations, see details in Chapter Diagnose. Put the new version's configuration file in /etc directory and name it as my.cnf.

4.2.8. Initialize the new database

Change to /data directory. Backup old MySQL related directories as mysql_bak.

# cd /data
# mv mysql mysql_bak

Create new MySQL directory under /data directory.

# mkdir -p /data/mysql/data
# mkdir -p /data/mysql/binlog
# mkdir -p /data/mysql/logs

And its structure should be like this.

mysql
├── binlog
├── data
└── logs

Change the owner and privileges of the directory and subdirectories to mysql:mysql.

# chown -R mysql:mysql /data/mysql

Use ls -lah command to see detailed privilege information and make sure the change is successful.

# ls -lah | grep mysql
drwxr-xr-x   5 mysql mysql   44 Jul 22 15:33 mysql

Initialize the MySQL database.

# /usr/local/mysql/bin/mysql --initialize --user=mysql

4.2.9. Start the database

If the initialization is successful, we can start the database. Copy one launch daemon to /etc/init.d first.

# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
# service mysqld start

5. Data Importing | 數據導入

When we finish the construction of the database and start services, we may need to migrate the original data from the previous database to new database. In this part we describe the operations that imports previous data to new database.

Create new database. According to the data scripts (usually sql files), setup databases with same names as previous databases. Generally, the names of the databases should be same of names contained in exported sql scripts. Additionally, exported backup scripts also contain exporting time and other information.

> CREATE DATABASE original_data;

Change into the newly created database original_data after the creation, and the original_data can be any name according to the actual situation.

> USE original_data;

Then upload the sql import script to the server. Log into the MySQL command line interface, import the data in original_data database. The position where the script is located should be an absolute path.

> SOURCE /path/to/original_data_backup_time.sql;

Wait for the importing process to finish. If there are several sql backup scripts need to import, we should import them by time order, from early to the latest.

6. Main and Duplicate Database Preparing | 主備數據庫搭建

6.1. Prepare and Check

To set up a MySQL master-slave database setup, at least one device is needed for the master database and one device for the slave database. Both devices should have the same MySQL database software version installed. In this example, both test machines are running MySQL version 8.1.0. The system environments of test machines t1 and t2 are as follows. For simplicity, the two machines used to set up the master-slave database setup will be referred to as t1 and t2.

Test Machine t1:
Linux clz-db-t1 3.10.0-693.el7.x86_64 #1 SMP Thu Jul 6 19:56:57 EDT 2017 x86_64 x86_64 x86_64 GNU/Linux

Test Machine t2:
Linux clz-db-t2 3.10.0-693.el7.x86_64 #1 SMP Thu Jul 6 19:56:57 EDT 2017 x86_64 x86_64 x86_64 GNU/Linux

The two test machine have the same configuration and system environments, and they both have MySQL 8.1.0 database server installed. Start these two test machine, login and verify their MySQL version.

6.2. Master-slave Architecture

6.2.1. Setup `mysql.server` services

Managing the MySQL service directly through mysql.server: When not using the service utility to manage mysqld, starting the MySQL service requires using /mysqld/support-files/mysql.server. This method is suitable for MySQL software that was not installed using RPM.

Start MySQL services:

# /usr/local/mysql/support-files/mysql.server start

Stop MySQL services:

# /usr/local/mysql/support-files/mysql.server stop

Check MySQL status:

# /usr/local/mysql/support-files/mysql.server status

Managing MySQL services using systemctl: If the MySQL database software was installed via RPM, you can use systemctl to manage the MySQL service.

Enable MySQL services:

# systemctl enable mysqld.service

Start MySQL services:

# systemctl start mysqld.service

Check MySQL status:

# systemctl status mysqld.service

Stop MySQL services:

# systemctl stop mysqld.service

Disable MySQL services:

# systemctl disable mysqld.service

Restart MySQL services:

# systemctl restart mysqld.service

Managing the MySQL service using a service: For easier testing and management, you can use the mysqld service to control the MySQL service. Copy the mysql.server script from the support-files directory under your MySQL software installation directory to the /etc/init.d directory and rename it to mysqld. This method is suitable for MySQL server software installed using binary packages.

# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql.server

Perform this process on both machines, and then you can use the service command to manage the MySQL service status.

Stop MySQL services:

# service mysql.server stop

Check MySQL status:

# service mysql.server status

Start MySQL services:

# service mysql.server start

6.2.2. MySQL database host settings

Check the machine t1 that hosts the MySQL master database, log in and open the MySQL configuration file my.cnf. This configuration file should be located in the /etc directory.

# vim /etc/my.cnf

Check that the configuration items bin-log and server-id in the configuration file are set and not empty. The server-id can be set according to the actual situation; the test machine's server-id is set to 22567. Note that the server-id of the MySQL database master server and the database standby (slave) server must not be the same; otherwise, the database backup machine will be unable to connect to the master.

Create a backup account for master-slave replication on the MySQL master database server. First, list all accounts currently in MySQL for verification and comparison.

> SELECT user FROM mysql.user;

Then create a new backup user dedicated to master-slave replication, named replica_user.

> CREATE USER 'replica_user'@'%' IDENTIFIED BY 'newpassword';

Listing all users in MySQL again reveals that the newly created user, replica_user, has been added to the MySQL database.

> SELECT user FROM mysql.user;
+------------------+
| user             |
+------------------+
| replica_user     |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+
5 rows in set (0.00 sec)

Check the host settings to ensure that the log_bin binary log is enabled, and check the current host's master status. Find the status of the log_bin variable in the configuration; this status should be ON. If this status is OFF, modify this value in the configuration file my.cnf and restart the MySQL service.

> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

Check the current MySQL host status. Use the following command in MySQL 8.0.

> SHOW MASTER STATUS \G
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 157
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

Use the following command in MySQL version 8.4.

> SHOW BINARY LOG STATUS \G
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 798
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: b6a6daa5-71a5-11f0-91cb-005056994dcc:1-2
1 row in set (0.00 sec)

Grant replica_user master-slave replication permissions.

> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replica_user'@'%';

6.2.3. Check the MySQL database standby (slave) settings.

Check that the configuration items bin-log and server-id in my.cnf are enabled and their values are not empty. The server-id of the MySQL backup machine in this test is set to 25566. In actual deployment, the setting can be adjusted according to the situation; generally, server-id is set to the last part of the server IP address. It is important to note that the server-id of the MySQL database host and the database backup machine (slave) must not be the same; otherwise, the database backup machine will be unable to connect to the host. If the slave node is only used as a database backup and there is no intention to switch it to the master, then bin-log logging is not required; otherwise, bin-log logging should be enabled for both the master and slave. Log in to the MySQL server on the slave machine. First, stop all master-slave replication operations. For MySQL 8.0 and above, use the following statement.

> STOP REPLICA;

For versions of MySQL below 8.0, use the following statement.

> STOP SLAVE;

Then, configure the MySQL master database address and account settings on the standby MySQL database. Use the following command in MySQL 8.1. The arrow "->" indicates a newline prompt; you don't need to copy it while typing, as it will appear automatically when you press Enter.

> CHANGE MASTER TO master_host='10.91.134.135',
-> master_port=3366,
-> master_user='replica_user',
-> master_password='newpassword',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=157,
-> get_master_public_key=1;

In this configuration, master_host should be set to the IP address of the machine hosting the primary database, and the secondary database should be able to access this address. master_port should be set to the port used by the primary MySQL database. master_user should be set to the username created in the previous section for master-slave replication; in this example, it's replica_user. master_password should be set to the password for the master-slave replication account. master_log_file and master_log_pos should be filled in according to the host's status output. In MySQL 8.0 and later versions, the get_master_public_key option needs to be enabled; see the Troubleshooting section for details. After completion, restart master-slave replication. For MySQL 8.4 and later versions, the following command should be used.

> CHANGE REPLICATION SOURCE TO
-> source_host='10.91.134.135',
-> source_port=3366,
-> source_user='replica_user',
-> source_password='newpassword',
-> source_log_file='mysql-bin.000001',
-> source_log_pos=1046,
-> get_source_public_key=1;

In MySQL 8.0 and above:

> START REPLICA;

Or in versions of MySQL below 8.0:

> START SLAVE;

Check the primary/standby connection status by entering the following information on the standby machine.

> SHOW REPLICA STATUS \G
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: 10.91.134.135
Source_User: replica_user
Source_Port: 3366
Connect_Retry: 60
Source_Log_File: mysql-bin.000001
Read_Source_Log_Pos: 351
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 520
Relay_Source_Log_File: mysql-bin.000001
Replica_IO_Running: Yes
Replica_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_Source_Log_Pos: 351
Relay_Log_Space: 730
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 22567
Source_UUID: 4f6e1086-6852-11f0-aa5f-005056994dcc
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
Source_Retry_Count: 10
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set: 4f6e1086-6852-11f0-aa5f-005056994dcc:1
Executed_Gtid_Set: 4f6e1086-6852-11f0-aa5f-005056994dcc:1,
87ce2877-692d-11f0-8354-0050569982d7:1-3
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 1
Network_Namespace:
1 row in set (0.00 sec)

If both Replica_IO_Running and Replica_SQL_Running show as Yes, it means that MySQL master-slave replication is running normally.

6.2.4. Testing master-slave replication function

Check if the databases in the current primary and standby databases are consistent, and view the current database.

> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

Create a new test database in the main database, named dbtest1 in this example.

> CREATE DATABASE dbtest1;

Check if the database is included in the primary database.

> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| dbtest1            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

Check if the backup database is synchronized.

> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| dbtest1            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

It can be confirmed that the newly created database has been replicated from the primary database to the standby database. After confirming that the primary-standby synchronization and replication are normal, remove the test database and tables.

6.3. Two-host Architecture

To achieve a master-slave architecture where the two MySQL servers act as each other's master and backup, and operations on either MySQL server are synchronized across both servers, master-slave replication needs to be configured on both MySQL servers.

For use in MySQL 8.4 and above:

> SHOW BINARY LOG STATUS \G

Used in MySQL versions 8.1 and below:

> SHOW MASTER STATUS \G

The following information can be obtained.

*************************** 1. row ***************************
File: mysql-bin.000003
Position: 4006
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: b6a6daa5-71a5-11f0-91cb-005056994dcc:8-18,
e3cbf8a0-71a6-11f0-8381-0050569982d7:1-4
1 row in set (0.00 sec)

Create a new account on t2 specifically for master-slave replication on t1, following the same procedure as creating a replication-dedicated account on t1. Log in to the MySQL console on t2.

> CREATE USER 'replica_user_2'@'%' IDENTIFIED BY 'newpassword';

Then grant the new user replica_user_2 the permissions related to master-slave replication.

> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replica_user_2'@'%';

Then log in to MySQL on t1 and first stop master-slave replication on t1.

> STOP REPLICA;

Then, configure the information for t2 in t1. Enter the IP address of t2 in source_host; enter the MySQL service port of t2 in source_port; enter the username created on t2 for master-slave replication (in this example, replica_user_2); enter the password corresponding to source_user; enter the name of the corresponding binary log file on t2 (in this example, mysql-bin.000003); enter the position of the binary log (e.g., 3012 in this example); finally, set get_source_public_key to 1 to retrieve the public key from the host.

> CHANGE REPLICATION SOURCE TO
-> source_host='10.91.134.136',
-> source_port=3366,
-> source_user='replica_user_2',
-> source_password='newpassword',
-> source_log_file='mysql-bin.000003',
-> source_log_pos=3012,
-> get_source_public_key=1;

Once completed, start master-slave replication on t1.

> START REPLICA;

Check the master-slave replication status of t1.

> SHOW REPLICA STATUS \G
...
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
...

Master-slave replication is working normally. When a write operation is performed on machine t2, machine t1 can synchronize and modify the data normally.

7. Backup and Restore | 備份及恢復

7.1. Physical Backup (Original Backup)

Physical backups consist of original copies of the directories and files storing the database content. This type of backup is suitable for large or critical databases that require rapid recovery in the event of problems. Physical backups are faster than logical backups because they involve only file copying operations and do not require database transformation. In addition to the database itself, physical backups can include any related files such as logs or configuration files. Physical backup files can only be migrated to other machines with the same or similar hardware characteristics. The granularity of physical backups ranges from the entire data directory level to the individual file level, but may not necessarily offer table-level granularity, depending on the storage engine used.

7.1.1. Cold backup

Perform backups while the database is completely shut down. The advantages of this method are high data consistency and fast backup and recovery speeds. However, the disadvantage is that it requires system downtime, which can significantly impact business operations. Alternatively, if conditions allow for a cold backup, disconnect the application from the database, stop reading and writing data, and shut down the MySQL database.

# service mysqld stop

A properly functioning MySQL database stores data in the /data/mysql directory and its configuration file in /etc/my.cnf. Therefore, a cold backup only needs to back up these two directories. Create a new directory /backup/offline under /data to store the backup files for the cold backup.

# mkdir -p /data/backup/offline

Copy the entire /data/mysql directory to the /data/backup/offline directory, and rename it, adding the backup time. For example:

# cp -r /data/mysql /data/backup/offline/backup_data_2025_08_04

Next, copy the entire my.cnf configuration file to the mysql_backup_offline directory, rename it, and add the backup time. For example:

# cp /etc/my.cnf /data/backup/offline/backup_config_2025_08_04.cnf

For specific, recurring database backup operations, a script named mysql_backup_offline.sh can be created to perform a cold backup of the entire database. The shell script is as follows.

backup_date=$(date "+%Y_%m_%d")
backup_folder=/data/backup/offline
data_dir_name="backup_data_${backup_date}"
config_name="backup_config_${backup_date}"
echo "[ INFO ] Starting offline backup process..."
echo "[ INFO ] Stoping MySQL service..."
service mysqld stop
service mysqld status
echo "[ INFO ] MySQL service stopped."
echo "[ INFO ] Backup databases..."
cp -r /data/mysql ${backup_folder}/backup_data_${backup_date}
tar -zcvPf ${backup_folder}/backup_data_${backup_date}.tar.gz ${backup_folder}/backup_data_${backup_date}
echo "[ INFO ] Backup configuration..."
cp /etc/my.cnf ${backup_folder}/backup_data_${backup_date}
echo "[ INFO ] Finished."

When a cold backup of the database is required, the script will automatically perform a cold backup of the database and name the backup file.

# cd /usr/local
# sh mysql_backup_offline.sh

Restart the MySQL service after the cold backup is complete.

# service mysqld start

7.1.2. Hot backup

Performing backups while the database is running will not affect its normal operation. However, using a hot backup strategy on a MySQL database requires additional backup tools, such as the open-source database backup tool Percona XtraBackup. Use wget to pull the corresponding version of the XtraBackup binary package (rpm format) from the XtraBackup website and place it in the /usr/local directory.

# cd /usr/local
# wget "https://downloads.percona.com/downloads/Percona-XtraBackup-8.4/Percona-XtraBackup-8.4.0-1/binary/redhat/7/x86_64/percona-xtrabackup-84-8.4.0-1.1.el7.x86_64.rpm"

Install the XtraBackup package.

# yum localinstall percona-xtrabackup-80-8.0.32-26.1.el7.x86_64.rpm

If you are prompted that libev.so.4 is missing, you need to install libev, a dependency required by XtraBackup.

# yum localinstall libev-4.04-2.el6.x86_64.rpm

If you are prompted that openssl is missing, you need to install the OpenSSL dependency required by XtraBackup.

# yum install openssl

If you are prompted that zstd is missing, you need to install Zstandard (usually abbreviated as zstd), a dependency required by XtraBackup. If zstd is available in the package manager's repository, you can install it directly through the package manager.

# yum install zstd

If the zstd package is not included in the yum repository, you can pull the source package from the zstd project website and compile it for installation, or pull the RPM package from another reliable mirror server to your local machine for installation. Place the zstd RPM package in the /usr/local directory.

# rpm -ivh zstd-1.4.0-1.el6.x86_64.rpm

Alternatively, you can use yum to install locally.

# yum localinstall zstd-1.4.0-1.el6.x86_64.rpm

After installing XtraBackup, use the command to verify that the version of the current backup tool matches the version of the running MySQL service.

# xtrabackup --version
xtrabackup version 8.4.0-1 based on MySQL server 8.4.0 Linux (x86_64) (revision id: da6e1abd)

For databases that require regular hot backups, the following script can be used to simplify the commands entered each time, automatically name the backup files according to a predetermined method, and avoid backup file name conflicts.

echo "[ INFO ] Starting XtraBackup..."
current_date=$(date "+%Y_%m_%d")
backup_dir=/data/backup/xtrabackup
xtrabackup --backup --slave-info -u root -H localhost -P3366 -p --target-dir=${backup_dir}/backup_${current_date} 2> ${backup_dir}/backup_${current_date}.log
echo "[ INFO ] Compressing..."
tar -zcvPf ${backup_dir}/backup_${current_date}.tar.gz ${backup_dir}/backup_${current_date}/
echo "[ INFO ] XtraBackup done."

7.2. Logical Backup

The mysqldump tool can be used to back up an entire database or a single table. For a MySQL server installed in the /usr/local/mysql directory, use the following command to start the mysqldump backup.

# /usr/local/mysql/bin/mysqldump -u root -p --lock-all-tables --flush-logs --set-gtid-purged=off database_name > backup_name.sql

Set the backup location for the MySQL database to the dump folder under the /data/backup directory. Name the backup file as follows: dump_database_name_yyyymmddHHMM.sql, for example, dump_dbtest1_202507311541.sql indicates that this backup is for the database dbtest1, created on July 31, 2025. Include the backup creation date in the filename to prevent duplicate backup file names. For regular logical backups of a specific database, the following script can simplify the commands entered each time.

current_date=$(date "+%Y%m%d%H%M")
backup_folder=/data/backup/dump
username="root"
db_name="db1"
file_name="dump_${db_name}_${current_date}.sql"
backup_dir_file_name="${backup_folder}/${file_name}"
echo "[ INFO ] Starting backup MySQL database ${db_name} at ${current_date}..."
/usr/local/mysql/bin/mysqldump -u${username} -p --lock-all-tables --flush-logs --set-gtid-purged=off ${db_name} > ${backup_dir_file_name}
cd ${backup_folder}
tar -zcvPf ${file_name}.tar.gz ${file_name}
finish_date=$(date "+%Y%m%d%H%M")
echo "[ INFO ] Finish backup MySQL database ${db_name} at ${finish_date}."

8. Diagnose | 問題排查及解決方法

つづく…

The "Night-Up" Level MySQL Documentation for Database Deploy, Upgrade, Backup and Restore | 「起夜」級 MySQL 數據庫部署、升級、備份及恢復文檔

1. Self Introduction of Documentation | 文檔自述

2. Command Line Prompt Examples | 命令提示示例

3. Installation and Deployment | 安裝部署

3.1. Check the current environment of the machine | 檢查機器當前嘅環境

3.2. Download MySQL package | 下載 MySQL 軟件包

3.3. Setup users and groups | 建立用戶和組

3.4. Setup configuration files and directories | 建立配置文件和相關目錄

3.5. Install | 安裝

3.6. Initialization | 初始化

3.7. Create application account | 創建應用帳戶

4. Upgrade | 版本升級

4.1. Small Version Numbers: Upgrade Directly

4.1.1. Check environment

4.1.2. Download new version of MySQL binary package

4.1.3. Stop the applications

4.1.4. Stop the service of database

4.1.5. Remove old version

4.1.6. Compatibility checking

4.1.7. Roll back

4.2. Large Version Numbers: Logical Data Migration

4.2.1. Check the environment

4.2.2. Download new version of MySQL binary package

4.2.3. Export all the data from whole database

4.2.4. Stop applications

4.2.5. Stop database services

4.2.6. Soft link to new MySQL

4.2.7. Prepare a configuration file

4.2.8. Initialize the new database

4.2.9. Start the database

5. Data Importing | 數據導入

6. Main and Duplicate Database Preparing | 主備數據庫搭建

6.1. Prepare and Check

6.2. Master-slave Architecture

6.2.1. Setup mysql.server services

6.2.2. MySQL database host settings

6.2.3. Check the MySQL database standby (slave) settings.

6.2.4. Testing master-slave replication function

6.3. Two-host Architecture

7. Backup and Restore | 備份及恢復

7.1. Physical Backup (Original Backup)

7.1.1. Cold backup

7.1.2. Hot backup

7.2. Logical Backup

8. Diagnose | 問題排查及解決方法

6.2.1. Setup `mysql.server` services