Select Page

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