* Create dabase backup on shutdown

jprzimba · jprzimba · commit 366bc007daf1 · 2025-01-04T07:28:17.000-03:00
Thanks Canary team

This update introduces a refined automatic database backup feature
during the server shutdown process. The main improvements include:

1. Automatic Compression: The database backup is now always
compressed using gzip, reducing disk space usage.

2. Backup Management: The system organizes backup files into folders
named by date and automatically deletes backups older than 7 days. This
ensures that the backup storage remains manageable over time without
manual intervention.

The motivation behind these changes is to create a more efficient and
reliable way of managing database backups, ensuring data safety while
optimizing storage space usage. The feature can be highly useful for
production servers, as it creates backups during shutdown and maintains
them efficiently by automatically removing old backups.
diff --git a/.github/workflows/build-ubuntu.yml b/.github/workflows/build-ubuntu.yml
@@ -52,12 +52,14 @@ jobs:
         run: >
           sudo apt-get update && sudo apt-get install ccache linux-headers-"$(uname -r)"
 
-      - name: Switch to gcc-12 on Ubuntu 22.04
+      - name: Switch to gcc-13 on Ubuntu 22.04
         if: matrix.os == 'ubuntu-22.04'
         run: |
-          sudo apt install gcc-12 g++-12
-          sudo update-alternatives --install /usr/bin/gcc gcc /usr/bin/gcc-12 100 --slave /usr/bin/g++ g++ /usr/bin/g++-12 --slave /usr/bin/gcov gcov /usr/bin/gcov-12
-          sudo update-alternatives --set gcc /usr/bin/gcc-12
+          sudo add-apt-repository ppa:ubuntu-toolchain-r/test -y
+          sudo apt-get update
+          sudo apt install gcc-13 g++-13 -y
+          sudo update-alternatives --install /usr/bin/gcc gcc /usr/bin/gcc-13 100 --slave /usr/bin/g++ g++ /usr/bin/g++-13 --slave /usr/bin/gcov gcov /usr/bin/gcov-12
+          sudo update-alternatives --set gcc /usr/bin/gcc-13
 
       - name: Switch to gcc-14 on Ubuntu 24.04
         if: matrix.os == 'ubuntu-24.04'
diff --git a/.gitignore b/.gitignore
@@ -390,5 +390,8 @@ crystalserver.old
 # VCPKG
 vcpkg_installed
 
+# DB Backups
+database_backup
+
 # CLION
 cmake-build-*
diff --git a/config.lua.dist b/config.lua.dist
@@ -403,6 +403,7 @@ mysqlDatabase = "crystalserver"
 mysqlPort = 3306
 mysqlSock = ""
 passwordType = "sha1"
+mysqlDatabaseBackup = false
 
 -- NOTE: memoryConst: This is the memory cost for the Argon2 hash algorithm. It specifies the amount of memory that the algorithm will use when calculating a hash.
 --The memory cost is measured in units of KiB (1024 bytes). A higher memory cost makes the algorithm more resistant to brute-force and hash-table attacks, but also consumes more memory.
diff --git a/src/config/config_enums.hpp b/src/config/config_enums.hpp
@@ -176,6 +176,7 @@ enum ConfigKey_t : uint16_t {
 	MYSQL_PASS,
 	MYSQL_SOCK,
 	MYSQL_USER,
+	MYSQL_DB_BACKUP,
 	OLD_PROTOCOL,
 	ONE_PLAYER_ON_ACCOUNT,
 	ONLY_INVITED_CAN_MOVE_HOUSE_ITEMS,
diff --git a/src/config/configmanager.cpp b/src/config/configmanager.cpp
@@ -56,6 +56,7 @@ bool ConfigManager::load() {
 		loadBoolConfig(L, RESET_SESSIONS_ON_STARTUP, "resetSessionsOnStartup", false);
 		loadBoolConfig(L, TOGGLE_MAINTAIN_MODE, "toggleMaintainMode", false);
 		loadBoolConfig(L, TOGGLE_MAP_CUSTOM, "toggleMapCustom", true);
+		loadBoolConfig(L, MYSQL_DB_BACKUP, "mysqlDatabaseBackup", false);
 
 		loadFloatConfig(L, HOUSE_PRICE_RENT_MULTIPLIER, "housePriceRentMultiplier", 1.0);
 		loadFloatConfig(L, HOUSE_RENT_RATE, "houseRentRate", 1.0);
diff --git a/src/crystalserver.cpp b/src/crystalserver.cpp
@@ -385,6 +385,7 @@ void CrystalServer::modulesLoadHelper(bool loaded, std::string moduleName) {
 }
 
 void CrystalServer::shutdown() {
+	g_database().createDatabaseBackup(true);
 	g_dispatcher().shutdown();
 	g_metrics().shutdown();
 	inject<ThreadPool>().shutdown();
diff --git a/src/database/database.cpp b/src/database/database.cpp
@@ -20,6 +20,7 @@
 #include "config/configmanager.hpp"
 #include "lib/di/container.hpp"
 #include "lib/metrics/metrics.hpp"
+#include "utils/tools.hpp"
 
 Database::~Database() {
 	if (handle != nullptr) {
@@ -68,6 +69,89 @@ bool Database::connect(const std::string* host, const std::string* user, const s
 	return true;
 }
 
+void Database::createDatabaseBackup(bool compress) const {
+	if (!g_configManager().getBoolean(MYSQL_DB_BACKUP)) {
+		return;
+	}
+	// Get current time for formatting
+	auto now = std::chrono::system_clock::now();
+	std::time_t now_c = std::chrono::system_clock::to_time_t(now);
+	std::string formattedDate = fmt::format("{:%Y-%m-%d}", fmt::localtime(now_c));
+	std::string formattedTime = fmt::format("{:%H-%M-%S}", fmt::localtime(now_c));
+	// Create a backup directory based on the current date
+	std::string backupDir = fmt::format("database_backup/{}/", formattedDate);
+	std::filesystem::create_directories(backupDir);
+	std::string backupFileName = fmt::format("{}backup_{}.sql", backupDir, formattedTime);
+	// Create a temporary configuration file for MySQL credentials
+	std::string tempConfigFile = "database_backup.cnf";
+	std::ofstream configFile(tempConfigFile);
+	if (configFile.is_open()) {
+		configFile << "[client]\n";
+		configFile << "user=" << g_configManager().getString(MYSQL_USER) << "\n";
+		configFile << "password=" << g_configManager().getString(MYSQL_PASS) << "\n";
+		configFile << "host=" << g_configManager().getString(MYSQL_HOST) << "\n";
+		configFile << "port=" << g_configManager().getNumber(SQL_PORT) << "\n";
+		configFile.close();
+	} else {
+		g_logger().error("Failed to create temporary MySQL configuration file.");
+		return;
+	}
+	// Execute mysqldump command to create backup file
+	std::string command = fmt::format(
+		"mysqldump --defaults-extra-file={} {} > {}",
+		tempConfigFile, g_configManager().getString(MYSQL_DB), backupFileName
+	);
+	int result = std::system(command.c_str());
+	std::filesystem::remove(tempConfigFile);
+	if (result != 0) {
+		g_logger().error("Failed to create database backup using mysqldump.");
+		return;
+	}
+	// Compress the backup file if requested
+	std::string compressedFileName;
+	compressedFileName = backupFileName + ".gz";
+	gzFile gzFile = gzopen(compressedFileName.c_str(), "wb9");
+	if (!gzFile) {
+		g_logger().error("Failed to open gzip file for compression.");
+		return;
+	}
+	std::ifstream backupFile(backupFileName, std::ios::binary);
+	if (!backupFile.is_open()) {
+		g_logger().error("Failed to open backup file for compression: {}", backupFileName);
+		gzclose(gzFile);
+		return;
+	}
+	std::string buffer(8192, '\0');
+	while (backupFile.read(&buffer[0], buffer.size()) || backupFile.gcount() > 0) {
+		gzwrite(gzFile, buffer.data(), backupFile.gcount());
+	}
+	backupFile.close();
+	gzclose(gzFile);
+	std::filesystem::remove(backupFileName);
+	g_logger().info("Database backup successfully compressed to: {}", compressedFileName);
+	// Delete backups older than 7 days
+	auto nowTime = std::chrono::system_clock::now();
+	auto sevenDaysAgo = nowTime - std::chrono::hours(7 * 24); // 7 days in hours
+	for (const auto &entry : std::filesystem::directory_iterator("database_backup")) {
+		if (entry.is_directory()) {
+			try {
+				for (const auto &file : std::filesystem::directory_iterator(entry)) {
+					if (file.path().extension() == ".gz") {
+						auto fileTime = std::filesystem::last_write_time(file);
+						auto fileTimeSystemClock = std::chrono::clock_cast<std::chrono::system_clock>(fileTime);
+						if (fileTimeSystemClock < sevenDaysAgo) {
+							std::filesystem::remove(file);
+							g_logger().info("Deleted old backup file: {}", file.path().string());
+						}
+					}
+				}
+			} catch (const std::filesystem::filesystem_error &e) {
+				g_logger().error("Failed to check or delete files in backup directory: {}. Error: {}", entry.path().string(), e.what());
+			}
+		}
+	}
+}
+
 bool Database::beginTransaction() {
 	if (!executeQuery("BEGIN")) {
 		return false;
diff --git a/src/database/database.hpp b/src/database/database.hpp
@@ -45,6 +45,23 @@ class Database {
 
 	bool connect(const std::string* host, const std::string* user, const std::string* password, const std::string* database, uint32_t port, const std::string* sock);
 
+	/**
+	 * @brief Creates a backup of the database.
+	 *
+	 * This function generates a backup of the database, with options for compression.
+	 * The backup can be triggered periodically or during specific events like server loading.
+	 *
+	 * The backup operation will only execute if the configuration option `MYSQL_DB_BACKUP`
+	 * is set to true in the `config.lua` file. If this configuration is disabled, the function
+	 * will return without performing any action.
+	 *
+	 * @param compress Indicates whether the backup should be compressed.
+	 * - If `compress` is true, the backup is created during an interval-based save, which occurs every 2 hours.
+	 *   This helps prevent excessive growth in the number of backup files.
+	 * - If `compress` is false, the backup is created during the global save, which is triggered once a day when the server loads.
+	 */
+	void createDatabaseBackup(bool compress) const;
+
 	bool retryQuery(std::string_view query, int retries);
 	bool executeQuery(std::string_view query);
 

Original file line number	Diff line number	Diff line change
`@@ -385,6 +385,7 @@ void CrystalServer::modulesLoadHelper(bool loaded, std::string moduleName) {`
`385`	`385`	`}`
`386`	`386`
`387`	`387`	`void CrystalServer::shutdown() {`
	`388`	`+ g_database().createDatabaseBackup(true);`
`388`	`389`	`g_dispatcher().shutdown();`
`389`	`390`	`g_metrics().shutdown();`
`390`	`391`	`inject<ThreadPool>().shutdown();`