Select Page

資料庫-每日自動備份-Ubuntu-MariaDB

今天來實做一個備份任務,要在 Ubuntu 上設定每日凌晨 4:00 自動備份 MariaDB 中的所有資料庫,我們可以使用 cron 和一個自定義的 Shell 腳本來完成這個任務,以下是詳細步驟和代碼:

步驟 1:創建備份腳本

先創建一個備份 Shell Script,這個腳本將備份所有資料庫並分開儲存。

1.在 /usr/local/bin 目錄下創建一個新的 Shell 腳本:

    sudo nano /usr/local/bin/backup_mariadb.sh

    2. Script 的內容指令如下

    #!/bin/bash
    
    # 設定資料庫的用戶名和密碼
    DB_USER="your_db_user"
    DB_PASSWORD="your_db_password"
    DB_HOST="10.0.0.1"
    
    # 設定備份儲存目錄
    BACKUP_DIR="/path/to/backup/dir"
    mkdir -p $BACKUP_DIR
    
    # 獲取當前日期和時間
    CURRENT_DATE=$(date +%Y-%m-%d-%H-%M-%S)
    
    # 獲取所有資料庫名稱
    DATABASES=$(mysql -h$DB_HOST -u$DB_USER -p$DB_PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database)
    
    # 備份每個資料庫
    for DB in $DATABASES; do
      if [[ "$DB" != "information_schema" && "$DB" != "performance_schema" && "$DB" != "mysql" && "$DB" != "sys" ]]; then
        BACKUP_FILE="$BACKUP_DIR/$CURRENT_DATE-$DB.sql"
        mysqldump  -h$DB_HOST -u$DB_USER -p$DB_PASSWORD --databases $DB > $BACKUP_FILE
      fi
    done

    其中要修改的有

    • DB_HOST : 要改成自己的
    • your_db_user : 資料庫中有備份權限的使用者 ID
    • your_db_password : 密碼
    • BACKUP_DIR=”/path/to/backup/dir” : 要改成你要存放的路徑,像是 /var/backup_db/

    3.將 .sh 變成可執行擋

    sudo chmod +x /usr/local/bin/backup_mariadb.sh

    步驟 2:設置 Cron 任務

    1.打開 cron

    crontab -e

    2.加入設定內容,要注意的是實間是主機時間,通常主機是 UTC+0 的時區,要注意轉換,才會是正確的當地時間,可以參考這篇

    0 4 * * * /usr/local/bin/backup_mariadb.sh

    這樣就會在每天的早上四點去備份資料庫了

    加入每一個SQL檔案都可以被壓縮的功能

    程式碼區塊要改成下面這一個

    # 備份每個資料庫並壓縮
    for DB in $DATABASES; do
      if [[ "$DB" != "information_schema" && "$DB" != "performance_schema" && "$DB" != "mysql" && "$DB" != "sys" ]]; then
        BACKUP_FILE="$BACKUP_DIR/$CURRENT_DATE-$DB.sql"
        ZIP_FILE="$BACKUP_DIR/$CURRENT_DATE-$DB.zip"
        mysqldump -h$DB_HOST -u$DB_USER -p$DB_PASSWORD --databases $DB > $BACKUP_FILE
        zip $ZIP_FILE $BACKUP_FILE
        rm $BACKUP_FILE
      fi
    done

    刪除舊資料

    可以利用下面的指令,放在程式碼的最後面

    # 刪除兩天前的備份文件
    find $BACKUP_DIR -type f -name "*.zip" -mtime +2 -exec rm {} \;

    需要改時間的話,只要修正 -mtime +2 ,把+2改成自己需要的時間

    參考資料

    https://help.ubuntu.com/community/CronHowto

    MySQL, MariaDB, 整個資料庫的複製方法

    如果你想一次複製所有的資料,包括用戶數據和結構,你可以使用以下方法:

    1.使用 mysqldump 進行完整備份:

    mysqldump 可以用來備份整個 MariaDB 數據庫,包括資料表結構和數據。以下是備份和還原的步驟:

    首先到原始主機上

    mysqldump -u root -p --all-databases > full_backup.sql

    這時候你會取得 full_backup.sql 檔案,將此檔案複製到目標主機上頭

    並且到目標主機上做還原的動作

    mysql -u root -p < full_backup.sql

    2.用外部程式直接複製 MariaDB 資料庫

    可以用 Percona XtraBackup , 安裝方法可以看 https://docs.percona.com/percona-xtradb-cluster/8.0/apt.html#prerequisites

    備份指令如下:

    innobackupex --user=root --password=your_password /path/to/backup_directory

    還原指令如下

    innobackupex --copy-back /path/to/backup_directory

    3.複製用戶資訊

    備份以及還原完資料後,要記得把用戶資料也都一起複製過來,首先用 root 或是管理員等級的帳號登入,並且下面的執行指令,把用戶資訊匯出

    mysqldump -u root -pYourPassword -R --databases mysql > user_backup.sql

    注意 -p 後面直接跟著密碼,中間不能有空格。

    還原用戶資訊

    SOURCE /path/to/user_backup.sql;

    結論

    這將在新主機上創建與原始主機相同的用戶帳號、密碼和許可權,以及把之前的所有資料複製到新主機上面。請確保新主機上的 MariaDB 版本與原始主機上的版本相容,並且新主機的網絡設置和防火牆規則允許用戶連接。

    參考資訊

    Windows 設定 MariaDB Master to Master 備援:全面指南

    Windows 設定 MariaDB Master to Master 備援:全面指南

    1. 瞭解 MariaDB Master to Master 備援的重要性

    Master to Master (M2M) 備援在 MariaDB 中是非常重要的一種設定。它允許兩個 MariaDB 伺服器雙向地同步資料,確保其中一台伺服器發生問題時,另一台可以立即接手,確保資料的持續可用性。對於那些尋求高可用性和資料完整性的應用程式來說,這是一個非常有用的設定。

    2. Windows 環境下的 MariaDB 安裝步驟

    要在 Windows 上設定 M2M,首先需要正確地安裝 MariaDB。下載適用於 Windows 的 MariaDB 版本並執行安裝程式。選擇適當的安裝選項,例如伺服器位置、資料庫大小等。並且確保在安裝過程中設定一個強大的 root 密碼,以確保資料庫的安全。

    Windows MariaDB Server Download

    目前MariaDB長期支援版本為 MariaDB 10.11 版本,11版本則是有最佳化過的,兩者都可以用,可以依照自己的需求做選擇

    假設兩台主機如下

    • Hostname: Master01 ,IP:192.168.0.10
    • Hostname: Master02 ,IP:192.168.0.11

    3. 配置 Master to Master 備援

    設定 M2M 備援涉及許多步驟:

    修改 my.ini(Windows) 或 my.cnf(Linux) 設定檔,設定 server-id 和 log-bin 參數

    可以在開始功能表中找到my.ini,或是到你安裝時候填寫的資料庫路徑中可以看到my.ini檔案

    每個 master 伺服器應該有一個唯一的 server-id,所以要在my.ini設定檔案中加入 server-id、log-bin、log-basename

    第一台主機 id 設定為 1

    [mysqld]
    datadir=C:/Mariadb
    port=3306
    innodb_buffer_pool_size=2559M
    character-set-server=utf8mb4
    
    server-id = 1
    log-bin
    log-basename = master01

    第二台主機 id 設定為 2

    [mysqld]
    datadir=C:/Mariadb
    port=3306
    innodb_buffer_pool_size=2559M
    character-set-server=utf8mb4
    
    server-id = 2
    log-bin
    log-basename = master02

    在每台伺服器上建立用於備援的使用者帳號,並授予該使用者適當的權限。

    在第一台主機 master01 上,新增一個使用者,讓 slave 主機可以透過192.168.0.X的網段連進來

    grant replication slave on *.* to 'replica_user'@192.168.0.% identified by '123456';
    flush privileges;

    在第二台主機 master02 上,新增一個使用者,並且要讓 master01 主機的 IP 可以連進來

    grant replication slave on *.* to 'replica_user'@192.168.0.% identified by '123456';
    flush privileges;

    設定 replication filters,確保只有需要的資料被同步。

    啟動二進制日誌(binary logging)並確認每台伺服器都可以互相連接。

    4. 測試 Master to Master 備援

    在完成設定後,你應該測試 M2M 備援以確保它正常工作。首先,在一台伺服器上添加或修改一些資料,然後檢查另一台伺服器是否已同步這些變更。同樣地,從另一台伺服器修改資料並檢查第一台伺服器。此過程應確保資料在兩個伺服器之間正確同步。

    在 Master01 中,查詢 File 和 Position,並且把值記錄下來,等等要填到 master02

    show master status;

    在 master02 中,把 master01 的數值填入,並且建立連結

    change master to master_host='192.168.0.10',
        -> master_user='replica_user',
        -> master_password='123456',
        -> master_log_file='master01-bin.000001',
        -> master_log_pos=5099;
    
    start slave;

    接下來檢查是否有設定成功

    show slave status;

    接下來回到 master01 去設定跟 master02 的連接

    change master to master_host='192.168.0.11',
        -> master_user='replica_user',
        -> master_password='123456',
        -> master_log_file='master02-bin.000002',
        -> master_log_pos=5079;
    
    start slave;

    5. 維護和監控 Master to Master 備援

    一旦 M2M 備援設定完畢並經過測試,你需要持續監控以確保它正常運作。使用如 MariaDB 的 SHOW SLAVE STATUS 命令來檢查備援的狀態。此外,考慮使用監控工具,如 Nagios 或 Zabbix,來自動檢查伺服器的健康狀態。

    相關文章

    如何讓 MariaDB 跟外部主機連線

    在數據庫管理和開發過程中,經常需要讓 MariaDB 數據庫與外部主機進行連線,尤其是主機需要擴展或是效能調教時候,不能在把WEB和SQL DB放在同一台主機上,這時候就會有需求把SQL主機開放出來,記得在沒加密的情況下不要放到外部網路上。

    第一步 : 配置防火牆

    將 Port 3306 開放外面存取,較安全的做法是限定 IP 可以存取。

    sudo ufw allow in 3306

    第二步 : 修改 MariaDB 設定檔

    找到 my.cnf 或 mariadb.cnf 或 50-server.cnf,會在 /etc/mysql/mariadb.conf.d/ 下,進入檔案編輯,找到 bind-address = 127.0.0.1 ,將這行的最前面加入 #

    #bind-address = 127.0.0.1

    第三步 : 將外部存取的權限授予使用者

    使用 root 帳戶登錄到 MariaDB,然後為外部連接創建一個新的使用者或賦予現有使用者許可權。使用 GRANT 語句來設定使用者的權限和訪問控制

    mysql -u root -p
    GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED by 'yourpassword' WITH GRANT OPTION;
    flush privileges;
    exit

    第四步 : 重啟 MariaDB

    sudo systemctl restart mariadb

    相關文章

    Windows IIS Server (2022) 安裝 PHP 的方法以及設定

    Windows IIS Server (2022) 安裝 PHP 的方法以及設定

    我用 Windows Server 2022,已經沒法用Microsoft® Web Platform Installer (Web PI) 了,所以我就純手動安裝 PHP 8,這篇文章會假設你已經安裝好 IIS 了,並且提供下載PHP,安裝PHP,設定PHP,調教PHP,加速PHP等功能去說明

    下載 Windows 版本的 PHP

    https://www.php.net/downloads.php

    在上面的畫面中選擇 Windows downloads,我們採用的是 IIS 所以在下面的畫面中選擇下載 Non Thread Safe 的 zip

    下載完畢後,自行建立一個目錄把ZIP解壓縮後放在該目錄下,我是安裝了多個 PHP 版本,所以我建立了 PHP8 的目錄

    C:\php8

    Windows Server 主機啟用 FastCGI

    [> 伺服器管理員]> [新增角色服務],在 [應用程式開發] 底下,選取 [CGI] 核取方塊

    修改 PHP 組態的設定 (php.ini)

    修改 C:\php8\php.ini ,將設定改成 Windows Server 適用的參數,官方建立設定如下

    ;IS 下的 FastCGI 支援模擬呼叫方用戶端之安全性權杖的能力。 這可讓 IIS 定義要求執行的安全性內容。
    fastcgi.impersonate = 1
    
    cgi.force_redirect = 0
    
    ;這可讓 PHP 遵循 CGI 規格存取實際路徑資訊。 IIS FastCGI 實作需要這個延伸模組設定。
    cgi.fix_pathinfo = 1
    
    ;將 extension_dir 設定為指向 PHP 延伸模組所在的位置
    extension_dir = "./ext"
    

    我個人常用的設定如下

    max_execution_time = 300
    
    memory_limit = 256M
    
    upload_max_filesize = 10M
    post_max_size = 10M
    
    log_errors = On
    error_log = syslog
    
    default_socket_timeout = 300
    
    extension=curl
    extension=fileinfo
    extension=gd
    extension=intl
    extension=mbstring
    extension=exif      ; Must be after mbstring as it depends on it
    extension=mysqli
    extension=openssl
    extension=pdo_mysql
    extension=php_imagick.dll
    
    [opcache]
    ; Determines if Zend OPCache is enabled
    opcache.enable=1
    
    ; Determines if Zend OPCache is enabled for the CLI version of PHP
    opcache.enable_cli=1
    
    ; The OPcache shared memory storage size.
    opcache.memory_consumption=256
    
    ; The amount of memory for interned strings in Mbytes.
    opcache.interned_strings_buffer=8
    
    ; The maximum number of keys (scripts) in the OPcache hash table.
    ; Only numbers between 200 and 1000000 are allowed.
    opcache.max_accelerated_files=4000
    
    ; The maximum percentage of "wasted" memory until a restart is scheduled.
    ;opcache.max_wasted_percentage=5
    
    ; When this directive is enabled, the OPcache appends the current working
    ; directory to the script key, thus eliminating possible collisions between
    ; files with the same name (basename). Disabling the directive improves
    ; performance, but may break existing applications.
    ;opcache.use_cwd=1
    
    ; When disabled, you must reset the OPcache manually or restart the
    ; webserver for changes to the filesystem to take effect.
    opcache.validate_timestamps=1
    
    ; How often (in seconds) to check file timestamps for changes to the shared
    ; memory storage allocation. ("1" means validate once per second, but only
    ; once per request. "0" means always validate)
    opcache.revalidate_freq=2
    
    ; Enables or disables file search in include_path optimization
    ;opcache.revalidate_path=0
    
    ; If disabled, all PHPDoc comments are dropped from the code to reduce the
    ; size of the optimized code.
    ;opcache.save_comments=1
    
    ; If enabled, compilation warnings (including notices and deprecations) will
    ; be recorded and replayed each time a file is included. Otherwise, compilation
    ; warnings will only be emitted when the file is first cached.
    ;opcache.record_warnings=0
    
    ; Allow file existence override (file_exists, etc.) performance feature.
    ;opcache.enable_file_override=0
    
    ; A bitmask, where each bit enables or disables the appropriate OPcache
    ; passes
    ;opcache.optimization_level=0x7FFFBFFF
    
    ;opcache.dups_fix=0
    
    ; The location of the OPcache blacklist file (wildcards allowed).
    ; Each OPcache blacklist file is a text file that holds the names of files
    ; that should not be accelerated. The file format is to add each filename
    ; to a new line. The filename may be a full path or just a file prefix
    ; (i.e., /var/www/x  blacklists all the files and directories in /var/www
    ; that start with 'x'). Line starting with a ; are ignored (comments).
    ;opcache.blacklist_filename=
    
    ; Allows exclusion of large files from being cached. By default all files
    ; are cached.
    opcache.max_file_size=0
    
    ; Check the cache checksum each N requests.
    ; The default value of "0" means that the checks are disabled.
    ;opcache.consistency_checks=0
    
    ; How long to wait (in seconds) for a scheduled restart to begin if the cache
    ; is not being accessed.
    ;opcache.force_restart_timeout=180
    
    ; OPcache error_log file name. Empty string assumes "stderr".
    ;opcache.error_log=
    
    ; All OPcache errors go to the Web server log.
    ; By default, only fatal errors (level 0) or errors (level 1) are logged.
    ; You can also enable warnings (level 2), info messages (level 3) or
    ; debug messages (level 4).
    ;opcache.log_verbosity_level=1
    
    ; Preferred Shared Memory back-end. Leave empty and let the system decide.
    ;opcache.preferred_memory_model=
    
    ; Protect the shared memory from unexpected writing during script execution.
    ; Useful for internal debugging only.
    ;opcache.protect_memory=0
    
    ; Allows calling OPcache API functions only from PHP scripts which path is
    ; started from specified string. The default "" means no restriction
    ;opcache.restrict_api=
    
    ; Mapping base of shared memory segments (for Windows only). All the PHP
    ; processes have to map shared memory into the same address space. This
    ; directive allows to manually fix the "Unable to reattach to base address"
    ; errors.
    ;opcache.mmap_base=
    
    ; Facilitates multiple OPcache instances per user (for Windows only). All PHP
    ; processes with the same cache ID and user share an OPcache instance.
    ;opcache.cache_id=
    
    ; Enables and sets the second level cache directory.
    ; It should improve performance when SHM memory is full, at server restart or
    ; SHM reset. The default "" disables file based caching.
    ;opcache.file_cache=
    
    ; Enables or disables opcode caching in shared memory.
    ;opcache.file_cache_only=0
    
    ; Enables or disables checksum validation when script loaded from file cache.
    ;opcache.file_cache_consistency_checks=1
    
    ; Implies opcache.file_cache_only=1 for a certain process that failed to
    ; reattach to the shared memory (for Windows only). Explicitly enabled file
    ; cache is required.
    ;opcache.file_cache_fallback=1
    
    ; Enables or disables copying of PHP code (text segment) into HUGE PAGES.
    ; Under certain circumstances (if only a single global PHP process is
    ; started from which all others fork), this can increase performance
    ; by a tiny amount because TLB misses are reduced.  On the other hand, this
    ; delays PHP startup, increases memory usage and degrades performance
    ; under memory pressure - use with care.
    ; Requires appropriate OS configuration.
    ;opcache.huge_code_pages=0
    
    ; Validate cached file permissions.
    ;opcache.validate_permission=0
    
    ; Prevent name collisions in chroot'ed environment.
    ;opcache.validate_root=0
    
    ; If specified, it produces opcode dumps for debugging different stages of
    ; optimizations.
    ;opcache.opt_debug_level=0
    
    ; Specifies a PHP script that is going to be compiled and executed at server
    ; start-up.
    ; https://php.net/opcache.preload
    ;opcache.preload=
    
    ; Preloading code as root is not allowed for security reasons. This directive
    ; facilitates to let the preloading to be run as another user.
    ; https://php.net/opcache.preload_user
    ;opcache.preload_user=
    
    ; Prevents caching files that are less than this number of seconds old. It
    ; protects from caching of incompletely updated files. In case all file updates
    ; on your site are atomic, you may increase performance by setting it to "0".
    ;opcache.file_update_protection=2
    
    ; Absolute path used to store shared lockfiles (for *nix only).
    ;opcache.lockfile_path=/tmp

    要注意的延伸模組

    • 資料庫延伸模組(php_mysql) — 大部分開放原始碼應用程式會針對資料庫引擎使用 MySQL,請使用 php_mysql 或 php_mysqli 延伸模組。 針對新的開發工作,這些延伸模組都能正常運作,或考慮使用 MySQL 驅動程式的 PDO 版本 (PDO 是 PHP 擴充功能,可提供資料存取抽象層,可與各種資料庫搭配使用) ;這個額外的抽象層提供一組更豐富的物件資料庫功能和控制項。 如果 Microsoft® SQL Server ® (或快速版本,例如 Microsoft SQL Server 2008 Express 或 Microsoft®® SQL Server ® ® 2005 Express Edition) 是資料庫引擎,請使用開放原始碼應用程式的php_mssql擴充功能。 針對新的開發工作,請使用 SQL 驅動程式的 PDO 版本。
    • 影像處理延伸模組(imagick) — 許多可讓映射使用 GD2 擴充功能的開放原始碼應用程式 – php_gd2,其具有許多良好的基本映射操作應用程式開發介面, (API) 。 某些應用程式會使用 ImageMagick 應用程式和程式庫。 另外還有一個 php_exif 程式庫,可用來處理新式數位相機儲存在影像中的擴充資訊。
    • 國際化和當地語系化延伸模組– i18n 和 l10n 最常使用的兩個延伸模組是php_mbstring (多位元組字元串) 和php_gettext (原生語言支援) 。 許多開放原始碼應用程式都使用其中一個或兩者。
    • Web 服務延伸模組 – 根據所需的服務選擇 Web 服務延伸模組。 以 PHP 來說,會廣泛使用 SOAP 延伸模組。 XML-RPC 擴充功能通常與 SOAP 和其他服務搭配使用。

    將 PHP 目錄加入環境變數中

    IIS管理員中的設定

    在管理員中找到「處理常式對應」

    新增PHP對應

    FastCGI PHP 對應參數

    • 要求路徑: *.php
    • 模組 :FastCGImodule
    • 可執行檔: C:\php8\php-cgi.exe
    • 名稱: FastCGI

    參考資料

    https://learn.microsoft.com/zh-tw/iis/application-frameworks/install-and-configure-php-on-iis/enable-fastcgi-support-in-iis-7-on-windows-server-2008-windows-server-2008-r2-windows-vista-or-windows-7?source=recommendations

    https://learn.microsoft.com/zh-tw/iis/application-frameworks/install-and-configure-php-on-iis/install-and-configure-php?source=recommendations

    http://www.imagemagick.org/script/index.php