Linux 下 MySQL 多實例部署實現多主一從備份架構

部署前提

• 必須下載 MySQL 原始碼版本 (Source Code)。
• 建立 mysql 使用者與群組。

安裝與多實例初始化

# 解壓至 /usr/local
tar -xzf mysql-standard-5.0.15-linux-i686-glibc23.tar.gz -C /usr/local

# 建立軟連結方便管理
cd /usr/local
ln -s mysql-standard-5.0.15-linux-i686-glibc23 mysql001
cd mysql001

# 初始化系統資料庫
scripts/mysql_install_db

# 權限設定
chown -R root .
chown -R mysql data
chgrp -R mysql .

# 複製 data 目錄供多實例使用
cp -a data data_1
cp -a data data_2

多實例設定檔 (my_multi.cnf)

[mysqld_multi]
mysqld = /usr/local/mysql001/bin/mysqld_safe
mysqladmin = /usr/local/mysql001/bin/mysqladmin
user = root
password = root

[mysqld1]          # 實例 1：同步自 192.168.1.85
socket = /tmp/mysql_001.sock
port = 3301
pid-file = /usr/local/mysql001/data_1/hostname.pid
datadir = /usr/local/mysql001/data_1
log = /usr/local/mysql001/data_1/hostname.log
user = mysql
server-id = 2
master-host = 192.168.1.85
master-port = 3306
master-user = backup
master-password = 123456
replicate-do-db = gs_database
replicate-do-db = gs_log
master-connect-retry = 30

[mysqld2]          # 實例 2：同步自 192.168.1.69
socket = /tmp/mysql_5_2.sock
port = 3302
pid-file = /usr/local/mysql001/data_2/hostname.pid
datadir = /usr/local/mysql001/data_2
log = /usr/local/mysql001/data_2/hostname.log
user = mysql
server-id = 3
master-host = 192.168.1.69
master-port = 3306
master-user = backup
master-password = 123
replicate-do-db = gs_databaes
replicate-do-db = gs_log
master-connect-retry = 30

環境變數與開機自啟

# /etc/profile
export MYSQL_HOME=/usr/local/mysql001/bin
export PATH=$PATH:$MYSQL_HOME

# /etc/rc.local
mysqld_multi --defaults-extra-file=/usr/local/mysql001/bin/my_multi.cnf start 1,2

常用管理指令

# 連線指定實例
mysql -u root -S /tmp/mysql_5_1.sock -p

# 設定實例密碼
mysqladmin -u root password 'root' -S /tmp/mysql_5_1.sock

# 手動啟動/停止
mysqld_multi --defaults-extra-file=/usr/local/mysql001/bin/my_multi.cnf start 1,2
mysqld_multi --defaults-extra-file=/usr/local/mysql001/bin/my_multi.cnf stop 1,2

此架構可在單一伺服器上運行多個 Slave 實例，分別同步不同 Master，實現「多主一從」彙總備份。