MySQL(mariadb)多實例應(yīng)用與多實例主從復(fù)制

MySQL多實例

mysql多實例，簡單理解就是在一臺服務(wù)器上，mysql服務(wù)開啟多個不同的端口（如3306、3307，3308），運行多個服務(wù)進程。這些 mysql 服務(wù)進程通過不同的 socket來監(jiān)聽不同的數(shù)據(jù)端口，進而互不干涉的提供各自的服務(wù)。

在同一臺服務(wù)器上，mysql 多實例會去共用一套 mysql 應(yīng)用程序，因此在部署 mysql的時候只需要部署一次 mysql程序即可，無需多次部署。但是，mysql多實例之間會各自使用不同的 my.cnf 配置文件、啟動程序和數(shù)據(jù)文件。在提供服務(wù)方面，mysql多實例在邏輯上看起來是各自獨立，互不干涉的，并且多個實例之間是根據(jù)配置文件的設(shè)定值，來獲取相關(guān)服務(wù)器的硬件資源。

多實例應(yīng)用場景

1. 當(dāng)一個公司業(yè)務(wù)訪問量不太大，又想節(jié)儉成本，并且還希望不同業(yè)務(wù)的數(shù)據(jù)庫服務(wù)能夠各自盡量獨立，提供服務(wù)能夠互相不受影響。另外還需要應(yīng)用主從同步等技術(shù)來提供數(shù)據(jù)庫備份或讀寫分離服務(wù)，以及方便后期業(yè)務(wù)量增大時，數(shù)據(jù)庫架構(gòu)的擴展和遷移。

2. 公司業(yè)務(wù)訪問量不是太大的時候，服務(wù)器的資源基本都是過剩狀態(tài)。此時就很適合 mysql 多實例的應(yīng)用。如果對 SQL語句 優(yōu)化做的比較好，mysql 多實例 是一個很值得去使用的技術(shù)。

3. 測試環(huán)境，公司中會需要數(shù)據(jù)庫環(huán)境來測試業(yè)務(wù)，在單機上部署多實例也能夠節(jié)省成本

MySQL多實例實現(xiàn)方式

mysql 多實例常規(guī)來講，有三種方案可以實現(xiàn)，這三種方案各有利弊，如下：

• 基于多配置文件通過使用多個配置文件來啟動不同的進程，以此來實現(xiàn)多實例。

優(yōu)點：邏輯簡單，配置簡單

缺點：管理起來不方便

• 基于mysqld_multi通過官方自帶的 mysqld_multi 工具，使用單獨配置文件來實現(xiàn)多實例

優(yōu)點： 便于集中管理管理

缺點： 不方便針對每個實例配置進行定制

• 基于IM使用 MySQL 實例管理器（MYSQLMANAGER），這個方法好像比較好不過也有點復(fù)雜

優(yōu)點：便于集中管理

缺點：耦合度高。IM一掛，實例全掛

不方便針對每個實例配置進行定制

本章內(nèi)容采用第一種方式來是實現(xiàn)，(個人傾向于這種方式)，數(shù)據(jù)庫采用mariadb來代替mysql，過程都一樣

系統(tǒng)與安裝版本信息
操作系統(tǒng)：Centos6.9
數(shù)據(jù)庫：mariadb-10.2.15.tar.gz
三個實例：3306，3307，3308

MySQL多實例安裝

安裝mysql

• 安裝依賴包

yum install bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel gcc gcc-c++ cmake libevent-devel gnutls-devel libaio-devel openssl-devel ncurses-devel libxml2-devel 

創(chuàng)建mysql用戶組

useradd -r mysql

創(chuàng)建實例數(shù)據(jù)庫目錄
各實例的數(shù)據(jù)庫目錄，配置文件目錄，啟動程序目錄都存放在單獨的目錄位置，結(jié)構(gòu)如下：

[root@localhost ~]# mkdir -p /data/mysql/{3306,3307,3308}/{data,etc,log,socket,bin}
[root@localhost ~]# tree /data
/data
├── lost+found
└── mysql
    ├── 3306
    │?? ├── bin
    │?? ├── data
    │?? ├── etc
    │?? ├── log
    │?? └── socket
    ├── 3307
    │?? ├── bin
    │?? ├── data
    │?? ├── etc
    │?? ├── log
    │?? └── socket
    ├── 3308
    │?? ├── bin
    │?? ├── data
    │?? ├── etc
    │?? ├── log
    │?? └── socket
    └── mysqld

解壓安裝源碼包

[root@localhost ~]# tar zxvf mariadb-10.2.15
[root@localhost ~]# cd mariadb-10.2.15

#編譯
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql  -DCMAKE-USER=mysql  -DCMAKE-GROUP=mysql -DMYSQL_DATADIR=/data/mysql -DWITHOUT_TOKUDB=1

如果執(zhí)行cmake提示：CMake Error at storage/tokudb/PerconaFT/cmake_modules/TokuSetupCompiler.cmake:183 (message):
添加編譯參數(shù)：-DWITHOUT_TOKUDB=1

make  -j  4
make  install

安裝成功后先不初始化，把配置文件，啟動腳本文件都配置完成后再執(zhí)行初始化

復(fù)制配置文件到各實例的etc目錄下

配置文件在源碼包解壓目錄下

[root@localhost support-files]# cp my-huge.cnf /data/mysql/3306/etc/my.cnf
[root@localhost support-files]# cp my-huge.cnf /data/mysql/3307/etc/my.cnf
[root@localhost support-files]# cp my-huge.cnf /data/mysql/3308/etc/my.cnf

修改配置文件內(nèi)容，3306,3307,3308各實例配置文件修改my.cnf內(nèi)容，修改端口，socket路徑，數(shù)據(jù)存放目錄等

[client]
#password       = your_password
port            = 3306       
socket          = /data/mysql/3306/socket/mysql.sock

[mysqld]
port            = 3306
socket          = /data/mysql/3306/socket/mysql.sock
datadir=/data/mysql/3306

同樣，3307，3308實例也要修改相同的配置

編寫服務(wù)啟動腳本

#!/bin/bash

port=3306
mysql_user="root"
mysql_pwd=""
cmd_path="/usr/local/mysql/bin"
mysql_basedir="/data/mysql"
mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"

function_start_mysql()
{
    if [ ! -e "$mysql_sock" ];then
      printf "Starting MySQL...\n"
      ${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf  &> /dev/null  &
    else
      printf "MySQL is running...\n"
      exit
    fi
}

function_stop_mysql()
{
    if [ ! -e "$mysql_sock" ];then
       printf "MySQL is stopped...\n"
       exit
    else
       printf "Stoping MySQL...\n"
#       ${cmd_path}/mysqladmin -u ${mysql_user} -S ${mysql_sock} shutdown
       ${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown
   fi
}

function_restart_mysql()
{
    printf "Restarting MySQL...\n"
    function_stop_mysql
    sleep 2
    function_start_mysql
}

case $1 in
start)
    function_start_mysql
;;
stop)
    function_stop_mysql
;;
restart)
    function_restart_mysql
;;
*)
    printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
esac

上面的腳本要準(zhǔn)備三份，因為mysql實例是各自獨立管理的，將上面的腳本參數(shù)port變量修改成3307,3308即可，放在 各自實例的bin目錄，服務(wù)啟動是從各自實例的bin目錄下執(zhí)行這腳本

port=3307
mysql_user="root"
mysql_pwd=""
cmd_path="/usr/local/mysql/bin"
mysql_basedir="/data/mysql"
mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"

初始化數(shù)據(jù)庫

[root@localhost mysql]# scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/mysql/3306/data

[root@localhost mysql]# scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/mysql/3307/data

[root@localhost mysql]# scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/data/mysql/3308/data

修改mysql實例目錄屬主

chown -R mysql:mysql /data/mysql

啟動各實例服務(wù)

[root@localhost ~]# /data/mysql/3306/bin/mysqld start
[root@localhost ~]# /data/mysql/3307/bin/mysqld start
[root@localhost ~]# /data/mysql/3308/bin/mysqld start

[root@localhost mysql]# ss -tnl
State      Recv-Q Send-Q                                 Local Address:Port                                   Peer Address:Port 
LISTEN     0      80                                                :::3306                                             :::*     
LISTEN     0      80                                                :::3307                                             :::*     
LISTEN     0      80                                                :::3308                                             :::*     
LISTEN     0      128                                               :::22                                               :::*   

測試登錄

[root@localhost ~]# mysql -S /data/mysql/3306/socket/mysql.sock
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.2.15-MariaDB-log Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

至此mysql的多實例安裝完成，在這種多實例的環(huán)境下，可以單獨使用個實例進行數(shù)據(jù)存儲，也可以將各實例配置成主從架構(gòu)進行使用，下面順便把mysql的主從也介紹使實現(xiàn)一下，關(guān)于mysql的主從架構(gòu)，以后會專門研究各種功能，這里只簡單實現(xiàn)，能夠幫助理清主從復(fù)制的工作過程和配置過程。

MySQL多實例主從復(fù)制

在開始主從配置之前先簡單介紹一下mysql的主從復(fù)制原理，
mysql復(fù)制原理大致如下：
1，mysql主數(shù)據(jù)庫在事物提交時會把數(shù)據(jù)變更作為事件events記錄在二進制日志文件bin-log中，mysql主庫上的sync_binlog參數(shù)控制bin-log日志刷新到磁盤

2，主庫推送二進制日志文件bin-log中的事件到從庫的中繼日志Relay Log ,之后從庫根據(jù)中繼日志Relay log重寫數(shù)據(jù)操作將數(shù)據(jù)寫入從庫，以此達到主庫和從庫的數(shù)據(jù)一致

mysql復(fù)制過程中通過3個線程來完成復(fù)制操作：其中binlog dump線程在主庫上，I/O線程和SQL線程在從庫上，當(dāng)在從庫上啟動復(fù)制（START SLAVE）時，首先會I/O線程連接主庫，（連接主庫用戶用主庫創(chuàng)建），主庫隨后創(chuàng)建binlog dump線程讀取數(shù)據(jù)庫事件（binlog日志）并發(fā)送給I/O線程，I/O獲取到binlog日志數(shù)據(jù)后更新到從庫的中繼日志Relay log中，從庫上的SQL線程讀取中繼日志Relay log 中數(shù)據(jù)寫入本地數(shù)據(jù)庫，最終完成主從復(fù)制。

一主多從的實現(xiàn)

本文實現(xiàn)一主多重的方式，即上面的3個mysql實例，3306實例作為主庫，3307和3308作為從庫，實現(xiàn)主從復(fù)制的最為關(guān)鍵的是日志文件的設(shè)置，主庫必須要開啟二進制日志，從庫要關(guān)閉二進制，并且開啟中繼日志relay log，要點如下：

• 主庫創(chuàng)建提供從庫的同步用戶
• 主庫開啟二進制日志
• 從庫開啟中繼日志relay log，并關(guān)閉二進制日志功能
• 主從之間的server-id不能相同
• 主從時間同步 （由于在同一臺主機，步奏略過）

配置主從庫實例的配置文件

查看mysql是否開啟二進制和中繼日志

MariaDB [(none)]> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> show variables like 'relay_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| relay_log     |       |
+---------------+-------+
1 row in set (0.00 sec)

mysql安裝默認會啟用二進制日志，但么有啟動relay log，所以主庫上無需設(shè)置，從庫需要開啟中繼日志：
編輯實例3307,3308的my.cnf關(guān)閉bin-log，開啟中繼日志，修改server-id

3307實例：

#log-bin=mysql-bin
relay-log=relay-log
server-id       = 2

3308實例：

#log-bin=mysql-bin
relay-log=relay-log
server-id       = 3

修改后要重啟3307,3308實例

[root@localhost ~]# /data/mysql/3307/bin/mysqld restart
Restarting MySQL...
Stoping MySQL...
Enter password: 
Starting MySQL...

[root@localhost ~]# /data/mysql/3308/bin/mysqld restart
Restarting MySQL...
Stoping MySQL...
Enter password:    #這里提示要輸入密碼，因為中定義了要輸入密碼
Starting MySQL...

重啟從庫實例后檢查日志啟用情況

MariaDB [(none)]> show variables like 'relay_log';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| relay_log     | relay-log |
+---------------+-----------+
1 row in set (0.00 sec)

MariaDB [(none)]> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

主庫創(chuàng)建同步復(fù)制的用戶

MariaDB [(none)]> grant replication slave on *.* to yufu@'192.168.%.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

查看主庫的bin-log日志記錄位置節(jié)點
因為在從庫中使用change master to 指令連接主庫時要指定連接時主庫的bin-log日志記錄位置節(jié)點。

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      645 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

從庫連接主庫

登錄到從庫3307實例：

MariaDB [(none)]> change master to master_host='192.168.214.141',master_user='yufu',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=645;
Query OK, 0 rows affected (0.11 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

登錄到從庫3308實例：

MariaDB [(none)]> change master to master_host='192.168.214.141',master_user='yufu',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=645;
Query OK, 0 rows affected (0.11 sec)

MariaDB [(none)]>  flush privileges;
Query OK, 0 rows affected (0.00 sec)

啟動從庫復(fù)制功能

啟動3307實例并查看復(fù)制狀態(tài)

MariaDB [(none)]> START SLAVE;      #
Query OK, 0 rows affected (0.80 sec)

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.214.141
                  Master_User: yufu
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 645
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 555
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes         #同時為yes才能成功同步數(shù)據(jù)
            Slave_SQL_Running: Yes

啟動3308實例并查看復(fù)制狀態(tài)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.214.141
                  Master_User: yufu
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 645
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 555
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

至此，配置啟動都沒有問題，下面從主庫實例3306中寫入一些數(shù)據(jù)看看看看3307,3308實例能不能正常同步數(shù)據(jù)

測試同步

登錄主庫3306實例寫一些數(shù)據(jù)：

MariaDB [(none)]> create database yufu;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use yufu;
Database changed
MariaDB [yufu]> create table test(
    -> id varchar(10),
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.84 sec)

MariaDB [yufu]> insert into test values ('2323','yufuname');
Query OK, 1 row affected (0.00 sec)

登錄從庫3307實例查看數(shù)據(jù)：

MariaDB [(none)]> select * from yufu.test;
+------+----------+
| id   | name     |
+------+----------+
| 2323 | yufuname |
+------+----------+
1 row in set (0.00 sec)

登錄從庫3308實例查看數(shù)據(jù)：

MariaDB [(none)]>  select * from yufu.test;
+------+----------+
| id   | name     |
+------+----------+
| 2323 | yufuname |
+------+----------+
1 row in set (0.01 sec)

經(jīng)過測試同步功能正常，文章最后再次總結(jié)一下mysql主從復(fù)制的要點：

1. 主庫必須開啟bin-log二進制日志
2. 從庫必須開啟中繼日志Relay-log，關(guān)閉bin-log
3. 主從的server-id 必須不能相同
4. 主庫創(chuàng)建同步用戶
5. 確保上面設(shè)置后，登錄從庫執(zhí)行 change master to 指令
6. 主從時間同步

在后面的文章里，會比較詳細介紹使用mysql的主從復(fù)制功能

個人博客站點www.gudaoyufu.com