# MariaDB 資料庫

MariaDB 資料庫

# MariaDB 主從架構配置

#### **[![image.png](https://note.homesitetw.com/uploads/images/gallery/2025-07/scaled-1680-/PqNimage.png)](https://note.homesitetw.com/uploads/images/gallery/2025-07/PqNimage.png)**

#### **運行環境**

- **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             |
+--------------------+
```

###