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

相關文章

開啟 MS SQL Server 的防火牆

開啟 MS SQL Server 的防火牆

正常情況安裝完 MS SQL SERVER 後,防火牆都還需要手動打開,SQL SERVER要去開放網路連線協定,我這邊提供一次搞定的批次檔,只要下載到電腦中,並且用管理員權限執行就可以了,以下示範一下如何打開以及注意事項

示範用的環境

  • Windows server 2022
  • SQL Server 2022

SQL Server Configuration Manager的設定

要先打開 SQL 的服務,所以先到 SQL Configuration Manager 中,把 TCP/IP 的協定打開

SQL Server Configuration Manager Network Protocols
SQL Server Configuration Manager Network Protocols TCP/IP
SQL Server Configuration Manager Network Protocols TCP/IP Enabled

防火牆設定

MS SQL SERVER 預設的 PORT 是 1433,打開這一個就可以正常連線

使用 Script 將所有的防火牆設定一次搞定

@echo =========  SQL Server Ports  ===================
@echo Enabling SQLServer default instance port 1433
netsh firewall set portopening TCP 1433 "SQLServer" 
@echo Enabling Dedicated Admin Connection port 1434
netsh firewall set portopening TCP 1434 "SQL Admin Connection" 
@echo Enabling conventional SQL Server Service Broker port 4022  
netsh firewall set portopening TCP 4022 "SQL Service Broker" 
@echo Enabling Transact-SQL Debugger/RPC port 135 
netsh firewall set portopening TCP 135 "SQL Debugger/RPC" 
@echo =========  Analysis Services Ports  ==============
@echo Enabling SSAS Default Instance port 2383
netsh firewall set portopening TCP 2383 "Analysis Services" 
@echo Enabling SQL Server Browser Service port 2382
netsh firewall set portopening TCP 2382 "SQL Browser" 
@echo =========  Misc Applications  ==============
@echo Enabling HTTP port 80 
netsh firewall set portopening TCP 80 "HTTP" 
@echo Enabling SSL port 443
netsh firewall set portopening TCP 443 "SSL" 
@echo Enabling port for SQL Server Browser Service's 'Browse' Button
netsh firewall set portopening UDP 1434 "SQL Browser" 
@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh firewall set multicastbroadcastresponse ENABLE 

下載已經寫好的bat檔案

參考資料

http://support.microsoft.com/kb/968872/zh-tw