同一台機器上的 MySQL 主從復制

已發表: 2022-03-11

MySQL 複製是一個過程，它可以將來自一個 MySQL 數據庫服務器（主）的數據自動複製到一個或多個 MySQL 數據庫服務器（從）。它通常用於在多個服務器上分散讀取訪問以實現可伸縮性，儘管它也可用於其他目的，例如故障轉移或分析從屬服務器上的數據以免使主服務器過載。

由於主從復制是一種單向複製（從主到從），寫操作只使用主庫，而讀操作可能分佈在多個從庫上。這意味著如果使用主從復製作為橫向擴展解決方案，您至少需要定義兩個數據源，一個用於寫入操作，第二個用於讀取操作。

MySQL 開發人員通常只在一台機器上工作，並且傾向於在這台機器上擁有整個開發環境，邏輯上他們不依賴於網絡或 Internet 連接。如果需要主從復制，例如，他們需要在開發環境中測試複製，然後在其他地方部署更改，他們必須在同一台機器上創建它。雖然單個 MySQL 實例的設置相當簡單，但我們需要付出一些額外的努力來設置第二個，然後是主從復制。

在本分步教程中，我選擇了 Ubuntu Linux 作為主機操作系統，並且提供的命令適用於該操作系統。如果要在其他操作系統上設置 MySQL 主從復制，則需要對其特定命令進行修改。但是，在同一台機器上設置 MySQL 主從復制的一般原則對於所有操作系統都是相同的。

安裝第一個 MySQL 實例

如果您的機器上已經安裝了一個 MySQL 數據庫實例，則可以跳過此步驟。

在 Ubuntu 上安裝 MySQL 的最簡單方法是從終端提示符運行以下命令：

 sudo apt-get install mysql-server

在安裝過程中，系統會提示您為 MySQL root用戶設置密碼。

設置`mysqld_multi`

為了有效地管理同一台機器上的兩個 MySQL 實例，我們需要使用mysqld_multi 。

設置mysqld_multi的第一步是在現有的my.cnf文件中創建兩個單獨的[mysqld]組。 Ubuntu 上my.cnf文件的默認位置是/etc/mysql/ 。因此，使用您喜歡的文本編輯器打開my.cnf文件，並將現有[mysqld]組重命名為[mysqld1] 。這個重命名的組將用於第一個 MySQL 實例的配置，也將被配置為主實例。在 MySQL 主從復制中，每個實例都必須有自己唯一的server-id ，在[mysqld1]組中添加以下行：

 server-id = 1

由於我們需要一個單獨的[mysqld]組用於第二個 MySQL 實例，因此復制具有所有當前配置的[mysqld1]組，並將其粘貼到同一個my.cnf文件中。現在，將復制的組重命名為[mysqld2] ，並在從站的配置中進行以下更改：

 server-id = 2 port = 3307 socket = /var/run/mysqld/mysqld_slave.sock pid-file = /var/run/mysqld/mysqld_slave.pid datadir = /var/lib/mysql_slave log_error = /var/log/mysql_slave/error_slave.log relay-log = /var/log/mysql_slave/relay-bin relay-log-index = /var/log/mysql_slave/relay-bin.index master-info-file = /var/log/mysql_slave/master.info relay-log-info-file = /var/log/mysql_slave/relay-log.info read_only = 1

要將第二個 MySQL 實例設置為從屬，請將server-id設置為 2，因為它必須與主服務器的 server-id 不同。

由於兩個實例將在同一台機器上運行，請將第二個實例的port設置為3307 ，因為它必須與第一個實例使用的端口不同，默認情況下為3306 。

為了使第二個實例能夠使用相同的 MySQL 二進製文件，我們需要為socket 、 pid-file 、 datadir和log_error設置不同的值。

我們還需要啟用relay-log以將第二個實例用作從屬（參數relay-log 、 relay-log-index和relay-log-info-file ），以及設置master-info-file 。

最後，為了使從屬實例只讀，參數read_only設置為1 。您應該小心使用此選項，因為它不能完全阻止從站上的更改。即使read_only設置為1 ，也只允許擁有SUPER權限的用戶進行更新。 MySQL 最近引入了新參數super_read_only以防止SUPER用戶進行更改。此選項在版本 5.7.8 中可用。

除了[mysqld1]和[mysqld2]組之外，我們還需要在my.cnf文件中添加一個新組[mysqld_multi] ：

 [mysqld_multi] mysqld = /usr/bin/mysqld_safe mysqladmin = /usr/bin/mysqladmin user = multi_admin password = multipass

一旦我們安裝了第二個 MySQL 實例並啟動了這兩個實例，我們將為multi_admin用戶提供適當的權限，以便能夠關閉 MySQL 實例。

為第二個 MySQL 實例創建新文件夾

在上一步中，我們為第二個 MySQL 實例準備了配置文件。在該配置文件中，使用了兩個新文件夾。應使用以下 Linux 命令來創建具有適當權限的文件夾：

 mkdir -p /var/lib/mysql_slave chmod --reference /var/lib/mysql /var/lib/mysql_slave chown --reference /var/lib/mysql /var/lib/mysql_slave mkdir -p /var/log/mysql_slave chmod --reference /var/log/mysql /var/log/mysql_slave chown --reference /var/log/mysql /var/log/mysql_slave

AppArmor 中的其他安全設置

在某些 Linux 環境中，需要 AppArmor 安全設置才能運行第二個 MySQL 實例。至少，它們在 Ubuntu 上是必需的。

要正確設置 AppArmor，請使用您喜歡的文本編輯器編輯/etc/apparmor.d/usr.sbin.mysqld文件，添加以下行：

 /var/lib/mysql_slave/ r, /var/lib/mysql_slave/** rwk, /var/log/mysql_slave/ r, /var/log/mysql_slave/* rw, /var/run/mysqld/mysqld_slave.pid rw, /var/run/mysqld/mysqld_slave.sock w, /run/mysqld/mysqld_slave.pid rw, /run/mysqld/mysqld_slave.sock w,

保存文件後，重新啟動計算機以使這些更改生效。

安裝第二個 MySQL 實例

安裝第二個 MySQL 實例可以採用幾種不同的方法。本教程中介紹的方法使用與第一個相同的 MySQL 二進製文件，第二個安裝需要單獨的數據文件。

由於我們在前面的步驟中已經準備好配置文件和必要的文件夾和安全更改，所以第二個 MySQL 實例的最後安裝步驟是 MySQL 數據目錄的初始化。

執行以下命令以初始化新的 MySQL 數據目錄：

 mysql_install_db --user=mysql --datadir=/var/lib/mysql_slave

初始化 MySQL 數據目錄後，您可以使用mysqld_multi服務啟動兩個 MySQL 實例：

 mysqld_multi start

使用mysqladmin和適當的主機和端口設置第二個 MySQL 實例的root密碼。請記住，如果未指定主機和端口， mysqladmin將默認連接到第一個 MySQL 實例：

 mysqladmin --host=127.0.0.1 --port=3307 -u root password rootpwd

在上面的示例中，我將密碼設置為“rootpwd”，但建議使用更安全的密碼。

`mysqld_multi`的附加配置

在“設置 mysqld_multi”部分的最後，我寫道，稍後我們將為multi_admin用戶提供適當的權限，所以現在是時候了。我們需要在這兩個實例中給這個用戶適當的權限，所以讓我們首先連接到第一個實例：

 mysql --host=127.0.0.1 --port=3306 -uroot -p

登錄後，執行以下兩條命令：

 mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass'; mysql> FLUSH PRIVILEGES;

從 MySQL 客戶端退出，並連接到第二個實例：

 mysql --host=127.0.0.1 --port=3307 -uroot -p

登錄後，執行與上面相同的兩個命令：

 mysql> GRANT SHUTDOWN ON *.* TO 'multi_admin'@'localhost' IDENTIFIED BY 'multipass'; mysql> FLUSH PRIVILEGES;

退出 MySQL 客戶端。

啟動時自動啟動兩個 MySQL 實例

設置mysqld_multi的最後一步是在init.d中安裝自動引導腳本。

為此，請在/etc/init.d中創建名為mysqld_multi的新文件，並為其賦予適當的權限：

 cd /etc/init.d touch mysqld_multi chmod +x /etc/init.d/mysqld_multi

使用您喜歡的文本編輯器打開這個新文件，然後復制以下腳本：

 #!/bin/sh ### BEGIN INIT INFO # Provides: scriptname # Required-Start: $remote_fs $syslog # Required-Stop: $remote_fs $syslog # Default-Start: 2 3 4 5 # Default-Stop: 0 1 6 # Short-Description: Start daemon at boot time # Description: Enable service provided by daemon. ### END INIT INFO bindir=/usr/bin if test -x $bindir/mysqld_multi then mysqld_multi="$bindir/mysqld_multi"; else echo "Can't execute $bindir/mysqld_multi"; exit; fi case "$1" in 'start' ) "$mysqld_multi" start $2 ;; 'stop' ) "$mysqld_multi" stop $2 ;; 'report' ) "$mysqld_multi" report $2 ;; 'restart' ) "$mysqld_multi" stop $2 "$mysqld_multi" start $2 ;; *) echo "Usage: $0 {start|stop|report|restart}" >&2 ;; esac

使用以下命令將mysqld_multi服務添加到默認runlevels ：

 update-rc.d mysqld_multi defaults

重新啟動您的機器，並使用以下命令檢查兩個 MySQL 實例是否正在運行：

 mysqld_multi report

設置主從復制

現在，當我們在同一台機器上運行兩個 MySQL 實例時，我們將第一個實例設置為主實例，將第二個實例設置為從屬。

部分配置已在“設置 mysqld_multi”一章中執行。 my.cnf文件中唯一剩下的更改是在主服務器上設置二進制日誌記錄。為此，請編輯my.cnf文件，在[mysqld1]組中進行以下更改和添加：

 log_bin = /var/log/mysql/mysql-bin.log innodb_flush_log_at_trx_commit = 1 sync_binlog = 1 binlog-format = ROW

重新啟動主 MySQL 實例以使這些更改生效：

 mysqld_multi stop 1 mysqld_multi start 1

為了讓從服務器以正確的複制權限連接到主服務器，應該在主服務器上創建一個新用戶。使用具有適當主機和端口的 MySQL 客戶端連接到主實例：

 mysql -uroot -p --host=127.0.0.1 --port=3306

為複制創建一個新用戶：

 mysql> CREATE USER 'replication'@'%' IDENTIFIED BY 'replication'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';

退出 MySQL 客戶端。

執行以下命令以創建主數據的轉儲：

 mysqldump -uroot -p --host=127.0.0.1 --port=3306 --all-databases --master-data=2 > replicationdump.sql

在這裡，我們使用選項--master-data=2以便在備份文件中添加包含CHANGE MASTER語句的註釋。該註釋表示備份時的複制坐標，稍後我們將需要這些坐標來更新從屬實例中的主信息。這是該評論的示例：

 -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=349;

將您在上一步中創建的轉儲導入從屬實例：

 mysql -uroot -p --host=127.0.0.1 --port=3307 < replicationdump.sql

最後，為了從屬實例連接到主實例，需要使用適當的連接參數更新從屬實例的主信息。

使用具有適當主機和端口的 MySQL 客戶端連接到從屬實例：

 mysql -uroot -p --host=127.0.0.1 --port=3307

執行以下命令以更新主信息（從轉儲文件replicationdump.sql中獲取複製坐標，如上所述）：

 mysql> CHANGE MASTER TO -> MASTER_HOST='127.0.0.1', -> MASTER_USER='replication', -> MASTER_PASSWORD='replication', -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=349;

執行以下命令以啟動從站：

 mysql> START SLAVE;

執行以下命令以驗證複製是否已啟動並正在運行：

 mysql> SHOW SLAVE STATUS \G

恭喜。您的 MySQL 主從復制現在已成功設置在同一台機器上。

包起來

如果您需要將主從復制用於生產環境中的橫向擴展解決方案，則在開發環境中配置主從復制非常有用。這樣，您還將為寫入和讀取操作配置單獨的數據源，以便在進一步部署之前在本地測試一切是否按預期工作。

此外，您可能希望在同一台機器上配置多個從屬實例，以測試將讀取操作分配給多個從屬的負載均衡器。在這種情況下，您可以使用同一手冊通過重複所有相同的步驟來設置其他從屬實例。