MySQL(mariadb)多實例應用與多實例主從複製

来源:https://www.cnblogs.com/anay/archive/2018/06/05/9140659.html
-Advertisement-
Play Games

MySQL多實例 mysql多實例，簡單理解就是在一臺伺服器上，mysql服務開啟多個不同的埠（如3306、3307，3308），運行多個服務進程。這些 mysql 服務進程通過不同的 socket來監聽不同的數據埠，進而互不幹涉的提供各自的服務。 在同一臺伺服器上，mysql 多實例會去共用一 ...


MySQL多實例

mysql多實例，簡單理解就是在一臺伺服器上，mysql服務開啟多個不同的埠（如3306、3307，3308），運行多個服務進程。這些 mysql 服務進程通過不同的 socket來監聽不同的數據埠，進而互不幹涉的提供各自的服務。

在同一臺伺服器上，mysql 多實例會去共用一套 mysql 應用程式，因此在部署 mysql的時候只需要部署一次 mysql程式即可，無需多次部署。但是，mysql多實例之間會各自使用不同的 my.cnf 配置文件、啟動程式和數據文件。在提供服務方面，mysql多實例在邏輯上看起來是各自獨立，互不幹涉的，並且多個實例之間是根據配置文件的設定值，來獲取相關伺服器的硬體資源。

多實例應用場景

  1. 當一個公司業務訪問量不太大，又想節儉成本，並且還希望不同業務的資料庫服務能夠各自儘量獨立，提供服務能夠互相不受影響。另外還需要應用主從同步等技術來提供資料庫備份或讀寫分離服務，以及方便後期業務量增大時，資料庫架構的擴展和遷移。

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

  3. 測試環境，公司中會需要資料庫環境來測試業務，在單機上部署多實例也能夠節省成本

MySQL多實例實現方式

mysql 多實例常規來講，有三種方案可以實現，這三種方案各有利弊，如下：

  • 基於多配置文件通過使用多個配置文件來啟動不同的進程，以此來實現多實例。

優點：邏輯簡單，配置簡單

缺點：管理起來不方便

  • 基於mysqld_multi通過官方自帶的 mysqld_multi 工具，使用單獨配置文件來實現多實例

優點： 便於集中管理管理

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

  • 基於IM使用 MySQL 實例管理器（MYSQLMANAGER），這個方法好像比較好不過也有點複雜

優點：便於集中管理

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

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

本章內容採用第一種方式來是實現，(個人傾向於這種方式)，資料庫採用mariadb來代替mysql，過程都一樣

系統與安裝版本信息
操作系統：Centos6.9
資料庫：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

創建mysql用戶組

useradd -r mysql

創建實例資料庫目錄
各實例的資料庫目錄，配置文件目錄，啟動程式目錄都存放在單獨的目錄位置，結構如下：

[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

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

make  -j  4
make  install

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

複製配置文件到各實例的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

修改配置文件內容，3306,3307,3308各實例配置文件修改my.cnf內容，修改埠，socket路徑，數據存放目錄等

[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實例也要修改相同的配置

編寫服務啟動腳本

#!/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

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

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"

初始化資料庫

[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

啟動各實例服務

[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的多實例安裝完成，在這種多實例的環境下，可以單獨使用個實例進行數據存儲，也可以將各實例配置成主從架構進行使用，下麵順便把mysql的主從也介紹使實現一下，關於mysql的主從架構，以後會專門研究各種功能，這裡只簡單實現，能夠幫助理清主從複製的工作過程和配置過程。

MySQL多實例主從複製

在開始主從配置之前先簡單介紹一下mysql的主從複製原理，
mysql複製原理大致如下：
1，mysql主資料庫在事物提交時會把數據變更作為事件events記錄在二進位日誌文件bin-log中，mysql主庫上的sync_binlog參數控制bin-log日誌刷新到磁碟

2，主庫推送二進位日誌文件bin-log中的事件到從庫的中繼日誌Relay Log ,之後從庫根據中繼日誌Relay log重寫數據操作將數據寫入從庫，以此達到主庫和從庫的數據一致

mysql複製過程中通過3個線程來完成複製操作：其中binlog dump線程在主庫上，I/O線程和SQL線程在從庫上，當在從庫上啟動複製（START SLAVE）時，首先會I/O線程連接主庫，（連接主庫用戶用主庫創建），主庫隨後創建binlog dump線程讀取資料庫事件（binlog日誌）併發送給I/O線程，I/O獲取到binlog日誌數據後更新到從庫的中繼日誌Relay log中，從庫上的SQL線程讀取中繼日誌Relay log 中數據寫入本地資料庫，最終完成主從複製。

一主多從的實現

本文實現一主多重的方式，即上面的3個mysql實例，3306實例作為主庫，3307和3308作為從庫，實現主從複製的最為關鍵的是日誌文件的設置，主庫必須要開啟二進位日誌，從庫要關閉二進位，並且開啟中繼日誌relay log，要點如下：

  • 主庫創建提供從庫的同步用戶
  • 主庫開啟二進位日誌
  • 從庫開啟中繼日誌relay log，並關閉二進位日誌功能
  • 主從之間的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，所以主庫上無需設置，從庫需要開啟中繼日誌：
編輯實例3307,3308的my.cnf關閉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)

主庫創建同步複製的用戶

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日誌記錄位置節點
因為在從庫中使用change master to 指令連接主庫時要指定連接時主庫的bin-log日誌記錄位置節點。

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)

啟動從庫複製功能

啟動3307實例並查看複製狀態

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才能成功同步數據
            Slave_SQL_Running: Yes

啟動3308實例並查看複製狀態

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中寫入一些數據看看看看3307,3308實例能不能正常同步數據

測試同步

登錄主庫3306實例寫一些數據：

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實例查看數據：

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

登錄從庫3308實例查看數據：

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

經過測試同步功能正常，文章最後再次總結一下mysql主從複製的要點：

1. 主庫必須開啟bin-log二進位日誌
2. 從庫必須開啟中繼日誌Relay-log，關閉bin-log
3. 主從的server-id 必須不能相同
4. 主庫創建同步用戶
5. 確保上面設置後，登錄從庫執行 change master to 指令
6. 主從時間同步

在後面的文章里，會比較詳細介紹使用mysql的主從複製功能

歡迎訪問個人博客站點www.gudaoyufu.com


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • emWin及StemWin使用中關於菜單欄的應用與問題
    在我看來，emWin和StemWin就是基本相同的庫文件，關於這個庫文件的移植，網路上有很多教材，比如“ALIENTEK emWin開發手冊”，他們家提供了各種STM32系列的開發手冊，我這裡記錄的是關於使用“視窗對象（小工具）”中的“MENU（菜單）”這類小工具在應用過程中，遇到的問題進行記錄，並... ...
  • Snapshot origin volumes can be resized only while inactive: try lvchange -an
    事件現象： 最近同事在擴展VG時遇到了“Snapshot origin volumes can be resized only while inactive: try lvchange -an”錯誤 # partprobe # mkfs -t ext3 /dev/sdd3 # pvcreate /d... ...
  • SQL server 資料庫的索引和視圖、存儲過程和觸發器
    1、索引：數據排序的方法，快速查詢數據 分類： 唯一索引：不允許有相同值 主鍵索引：自動創建的主鍵對應的索引，命令方式不可刪 聚集索引：物理順序與索引順序一致，只能創建一個 非聚集索引：物理順序與索引順序不一致，可創建多個 複合索引：多列組成 全文索引：特殊功能索引 命令： 創建索引：create ...
  • SQL Server 分頁編號的另一種方式
    今天看書講T-SQL，看到了UNBOUNDED PRECEDING，就想比對下ROW_NUMBER()的運行速度。 sql及相關的結果如下，資料庫中的數據有5W+。 ROW_NUMBER()： 運行結果 UNBOUNDED PRECEDING 運行結果 通過運行之後，看到結果，使用微軟官方提供的方法 ...
  • 深入學習Redis（2）：持久化
    前言 在上一篇文章中，介紹了Redis的記憶體模型，從這篇文章開始，將依次介紹Redis高可用相關的知識——持久化、複製(及讀寫分離)、哨兵、以及集群。 本文將先說明上述幾種技術分別解決了Redis高可用的什麼問題；然後詳細介紹Redis的持久化技術，主要是RDB和AOF兩種持久化方案；在介紹RDB和 ...
  • Oracle ddl 和 dml 操作
    ddl 操作 視窗設置用戶許可權的方法 Oracle的數據類型 按住Ctrl點擊表名 ,可以滑鼠操作 插入的數據需要滿足創建表的檢查 主表clazz刪除數據從表設置級聯也會一同刪除 有約束也強制刪除 Oracle dml操作 簡化分頁查詢 創建百萬條記錄的表 Oracle 資料庫的備份 導入導出 ...
  • 六項約束
    非空約束 create table 表名( id int not null ); 唯一約束 create table 表名( id int unique key, name varchar(20) ); 主鍵約束 primary key 主鍵的作用：可以唯一標示一條數據，每張表裡只有一個主鍵。 主鍵 ...
  • hadoop 提交程式並監控運行
    程式編寫及打包 1. 使用maven導入第三方jar pom.xml ~~~xml 4.0.0 hadoop study HadoopTest 4.0 UTF 8 2.7.5 org.apache.hadoop hadoop client ${hadoop.version} junit junit ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類