MariaDB 資料庫

• MariaDB 主從架構配置

MariaDB 主從架構配置

運行環境

• PVE
• Debian 12

安裝MariaDB

apt install -y mariadb-server

初始化設定

mysql_secure_installation

Master Server 設定

編輯/etc/mysql/mariadb.conf.d/50-server.cnf

[mysqld]
bind-address           = 10.0.0.4
server-id              = 1
log_bin                = /var/log/mysql/mysql-bin.log
expire_logs_days = 5
max_binlog_size = 10M



bind-address Master主機IP
server-id    不與Slave重複
log_bin      紀錄Mysql資訊
expire_logs_days log_bin保留天數
max_binlog_size log_bin檔案大小

重啟mariadb
systemctl restart mariadb

建立Slave使用者

mysql -u root -p

建立使用者
%為任意主機或改成指定IP       
MariaDB [(none)]> grant replication slave on *.* to testuser@'%' identified by 'testpswd'; 

MariaDB [(none)]> flush privileges;

複製資料庫檔案

資料表鎖住
MariaDB [(none)]> flush tables with read lock; 

記住File和Position
MariaDB [(none)]> show master status; 

MariaDB [(none)]> exit;

 

匯出資料庫

mysqldump -u root -p --all-databases --lock-all-tables --events > mysql_dump.sql 

mysql -u root -p
MariaDB [(none)]> unlock tables; 
MariaDB [(none)]> exit; 

Slave Server 設定

編輯/etc/mysql/mariadb.conf.d/50-server.cnf

[mysqld]
bind-address           = 10.0.0.100
server-id              = 2

bind-address Slave主機IP
server-id    不與Masterr重複

重啟mariadb
systemctl restart mariadb

匯入資料庫

mysql -u root -p < /root/mysql_dump.sql

若有 MariaDB – MySQL – ALL-DB Import – Table ‘user’ already exists 訊息

nano mysql_dump.sql

頂端加入後再執行匯入
DROP TABLE IF EXISTS `mysql`.`global_priv`;
DROP VIEW IF EXISTS `mysql`.`user`;

設定Master連線資訊

mysql -u root -p

關閉slave
MariaDB [(none)]> stop slave;

設定master資訊
MariaDB [(none)]> change master to 
    -> master_host='10.0.0.4',               # Master IP
    -> master_user='testuser',               # 剛剛設定的帳號
    -> master_password='testpswd',           # 剛剛設定的密碼
    -> master_log_file='mysql-bin.000001',   # 上面得到Master的 File
    -> master_log_pos=311;                   # 上面得到Master的 Position

啟動 slave
MariaDB [(none)]> start slave;

檢查Replication狀態

MariaDB [(none)]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 10.0.0.4
                   Master_User: testuser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000014
           Read_Master_Log_Pos: 342
                Relay_Log_File: mysqld-relay-bin.000014
                 Relay_Log_Pos: 555
         Relay_Master_Log_File: mysql-bin.000014
              Slave_IO_Running: Yes # 需Yes
             Slave_SQL_Running: Yes # 需Yes
               Replicate_Do_DB: 
           Replicate_Ignore_DB: 

驗證同步

Master Server確認是否有Slave hosts

mysql -u root -p

MariaDB [(none)]> show slave hosts;
+-----------+------+------+-----------+
| Server_id | Host | Port | Master_id |
+-----------+------+------+-----------+
|         2 |      | 3306 |         1 |
+-----------+------+------+-----------+
1 row in set (0.000 sec)

Master Server新增DB

MariaDB [(none)]> create database testdb;

 

確認Slave DB同步

MariaDB [(none)]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+