GNU/Linux >> Linux の 問題 >  >> Linux

OracleLinuxでMySQLマルチマスターレプリケーションを構成する方法

このチュートリアルでは、OracleLinuxでMySQLマルチマスターレプリケーションをセットアップおよび構成する方法について説明します。ご存知かもしれませんが、MySQLは、エンタープライズ対応であることが証明されている、よく知られた一流のデータベース製品です。データはすべての組織にとって重要であるため、ほとんどのデータベース管理者は、ユーザーが24時間年中無休でデータにアクセスできるように高可用性を設定するための適切なソリューションを探しています。 MySQLレプリケーションは、高可用性ポリシーを保証できるソリューションです。さらに、MySQLレプリケーションは、データベース管理者がREADリクエストとWRITEリクエストの負荷を分散することで、複数のデータベースサーバーに負荷を分散するのに役立ちます。残念ながら、基本的なレプリケーションでは、READリクエストに対してのみメリットがあります。そのため、MySQLマルチマスターレプリケーションが導入され、WRITEリクエストのレプリケーションも提供されました。


1。予備的注意

このチュートリアルでは、32ビットバージョンのOracleLinux6.8を使用しています。構成はOracleLinuxで行われていますが、手順と構成は主にCentOSとRedHatLinuxで同じであることに注意してください。このチュートリアルでは、2台のサーバーを使用します。それぞれに、MySQLデータベースをセットアップし、マルチマスターレプリケーション用に構成します。このチュートリアルの最後に、DDL（データ定義言語）およびDML（データ操作言語）要求を含むすべてのREADまたはWRITE要求が両方のサーバーで実行されることがわかります。


2。インストールフェーズ

インストールフェーズでは、構成フェーズ用のMySQLサーバーパッケージとデータベース環境にアクセスするためのMySQLクライアントのみが必要です。どちらのパッケージにも、いくつかの依存関係をインストールする必要があります。まず、オペレーティングシステムのバージョンを確認し、事前設定用のIPアドレスを書き留めておきましょう。


[[email protected] ~]# lsb_release -a 
LSB Version: :base-4.0-ia32:base-4.0-noarch:core-4.0-ia32:core-4.0-noarch:graphics-4.0-ia32:graphics-4.0-noarch:printing-4.0-ia32:printing-4.0-noarch 
Distributor ID: OracleServer 
Description: Oracle Linux Server release 6.8 
Release: 6.8 
Codename: n/a 
 
[[email protected] ~]# ifconfig 
eth0 Link encap:Ethernet HWaddr 08:00:27:42:C0:4C 
inet addr:192.168.43.11 Bcast:192.168.43.255 Mask:255.255.255.0 
inet6 addr: fe80::a00:27ff:fe42:c04c/64 Scope:Link 
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 
RX packets:544 errors:0 dropped:0 overruns:0 frame:0 
TX packets:79 errors:0 dropped:0 overruns:0 carrier:0 
collisions:0 txqueuelen:1000 
RX bytes:51274 (50.0 KiB) TX bytes:9474 (9.2 KiB) 
 
lo Link encap:Local Loopback 
inet addr:127.0.0.1 Mask:255.0.0.0 
inet6 addr: ::1/128 Scope:Host 
UP LOOPBACK RUNNING MTU:65536 Metric:1 
RX packets:4 errors:0 dropped:0 overruns:0 frame:0 
TX packets:4 errors:0 dropped:0 overruns:0 carrier:0 
collisions:0 txqueuelen:0 
RX bytes:240 (240.0 b) TX bytes:240 (240.0 b)

次に、他のサーバーでも同じことを行います。


[[email protected] ~]# lsb_release -a 
LSB Version: :base-4.0-ia32:base-4.0-noarch:core-4.0-ia32:core-4.0-noarch:graphics-4.0-ia32:graphics-4.0-noarch:printing-4.0-ia32:printing-4.0-noarch 
Distributor ID: OracleServer 
Description: Oracle Linux Server release 6.8 
Release: 6.8 
Codename: n/a 
 
[[email protected] ~]# ifconfig 
eth0 Link encap:Ethernet HWaddr 09:00:30:42:C1:5D 
inet addr:192.168.43.12 Bcast:192.168.43.255 Mask:255.255.255.0 
inet6 addr: fe80::a00:27ff:fe42:c04c/64 Scope:Link 
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 
RX packets:544 errors:0 dropped:0 overruns:0 frame:0 
TX packets:79 errors:0 dropped:0 overruns:0 carrier:0 
collisions:0 txqueuelen:1000 
RX bytes:51274 (50.0 KiB) TX bytes:9474 (9.2 KiB) 
 
lo Link encap:Local Loopback 
inet addr:127.0.0.1 Mask:255.0.0.0 
inet6 addr: ::1/128 Scope:Host 
UP LOOPBACK RUNNING MTU:65536 Metric:1 
RX packets:4 errors:0 dropped:0 overruns:0 frame:0 
TX packets:4 errors:0 dropped:0 overruns:0 carrier:0 
collisions:0 txqueuelen:0 
RX bytes:240 (240.0 b) TX bytes:240 (240.0 b)

次に、IPアドレスをサーバーのhostsファイルに追加します。以下のように、両方のサーバーで同じことを行います。


[[email protected] ~]# vi /etc/hosts 
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4 
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6 
 
192.168.43.11 DB1 
192.168.43.12 DB2

次に、yumユーティリティを介してMySQLサーバーとMySQLクライアントパッケージをインストールするための新しいリポジトリを構成します。両方のサーバーでこれを行ってください。


[[email protected] ~]# cd /etc/yum.repos.d/ 
[[email protected] yum.repos.d]# ls 
OEL6.repo 
 
[[email protected] yum.repos.d]# wget http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm 
--2017-05-22 09:43:59-- http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm 
Resolving repo.mysql.com... 23.8.231.210 
Connecting to repo.mysql.com|23.8.231.210|:80... connected. 
HTTP request sent, awaiting response... 200 OK 
Length: 5824 (5.7K) [application/x-redhat-package-manager] 
Saving to: "mysql-community-release-el6-5.noarch.rpm" 
 
100%[==================================================>] 5,824 --.-K/s in 0s 
 
2017-05-22 09:44:00 (264 MB/s) - "mysql-community-release-el6-5.noarch.rpm" saved [5824/5824] 
 
[[email protected] yum.repos.d]# ls 
OEL6.repo mysql-community-release-el6-5.noarch.rpm 
 
[[email protected] yum.repos.d]# rpm -Uvh mysql-community-release-el6-5.noarch.rpm 
Preparing... ########################################### [100%] 
1:mysql-community-release########################################### [100%] 
 
[[email protected] yum.repos.d]# ls 
CentOS.repo mysql-community.repo 
mysql-community-release-el6-5.noarch.rpm mysql-community-source.repo

最新のMySQLバージョンの新しいリポジトリがインストールされました。それらを有効にしましょう。


 
[[email protected] yum.repos.d]# vi mysql-community.repo 
# Enable to use MySQL 5.6 
[mysql56-community] 
name=MySQL 5.6 Community Server 
baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/6/$basearch/ 
enabled=1 
gpgcheck=1 
gpgkey=file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

完了したら、MySQLパッケージが使用可能であることを確認しましょう。


 
[[email protected] yum.repos.d]# rpm -qa|grep -i mysql 
mysql-community-release-el6-5.noarch

これで、途中で完了しました。現在のサーバーにはMySQLパッケージがインストールされていないため、パッケージのインストールを開始しましょう。手順は次のとおりです。

[[email protected] yum.repos.d]# 
[[email protected] yum.repos.d]# yum install mysql-server mysql-client 
Loaded plugins: fastestmirror, refresh-packagekit, security 
Setting up Install Process 
Repository 'OEL' is missing name in configuration, using id 
Determining fastest mirrors 
epel/metalink | 6.2 kB 00:00 
* epel: epel.mirror.angkasa.id 
* remi-php56: remi.mirror.wearetriple.com 
* remi-safe: remi.mirror.wearetriple.com 
OEL | 3.7 kB 00:00 
epel | 4.3 kB 00:00 
epel/primary_db | 5.9 MB 00:00 
mysql-connectors-community | 2.5 kB 00:00 
mysql-connectors-community/primary_db | 15 kB 00:00 
mysql-tools-community | 2.5 kB 00:00 
mysql-tools-community/primary_db | 35 kB 00:00 
mysql56-community | 2.5 kB 00:00 
mysql56-community/primary_db | 183 kB 00:00 
remi-php56 | 2.9 kB 00:00 
remi-php56/primary_db | 218 kB 00:01 
remi-safe | 2.9 kB 00:00 
remi-safe/primary_db | 725 kB 00:02 
Package mysql-server is obsoleted by mysql-community-server, trying to install mysql-community-server-5.6.36-2.el6.i686 instead 
No package mysql-client available. 
Resolving Dependencies 
--> Running transaction check 
---> Package mysql-community-server.i686 0:5.6.36-2.el6 will be installed 
--> Processing Dependency: mysql-community-common(i686) = 5.6.36-2.el6 for package: mysql-community-server-5.6.36-2.el6.i686 
--> Processing Dependency: mysql-community-client(i686) >= 5.6.10 for package: mysql-community-server-5.6.36-2.el6.i686 
--> Processing Dependency: perl(DBI) for package: mysql-community-server-5.6.36-2.el6.i686 
--> Running transaction check 
---> Package mysql-community-client.i686 0:5.6.36-2.el6 will be installed 
--> Processing Dependency: mysql-community-libs(i686) >= 5.6.10 for package: mysql-community-client-5.6.36-2.el6.i686 
---> Package mysql-community-common.i686 0:5.6.36-2.el6 will be installed 
---> Package perl-DBI.i686 0:1.609-4.el6 will be installed 
--> Running transaction check 
---> Package mysql-community-libs.i686 0:5.6.36-2.el6 will be installed 
--> Finished Dependency Resolution 
 
Dependencies Resolved 
 
============================================================================================ 
Package Arch Version Repository Size 
============================================================================================ 
Installing: 
mysql-community-server i686 5.6.36-2.el6 mysql56-community 55 M 
Installing for dependencies: 
mysql-community-client i686 5.6.36-2.el6 mysql56-community 18 M 
mysql-community-common i686 5.6.36-2.el6 mysql56-community 308 k 
mysql-community-libs i686 5.6.36-2.el6 mysql56-community 1.9 M 
perl-DBI i686 1.609-4.el6 CentOS 705 k 
 
Transaction Summary 
============================================================================================ 
Install 5 Package(s) 
 
Total download size: 76 M 
Installed size: 338 M 
Is this ok [y/N]: y 
Downloading Packages: 
(1/5): mysql-community-client-5.6.36-2.el6.i686.rpm | 18 MB 00:01 
(2/5): mysql-community-common-5.6.36-2.el6.i686.rpm | 308 kB 00:00 
(3/5): mysql-community-libs-5.6.36-2.el6.i686.rpm | 1.9 MB 00:00 
(4/5): mysql-community-server-5.6.36-2.el6.i686.rpm | 55 MB 00:02 
(5/5): perl-DBI-1.609-4.el6.i686.rpm | 705 kB 00:00 
-------------------------------------------------------------------------------------------- 
Total 18 MB/s | 76 MB 00:04 
warning: rpmts_HdrFromFdno: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY 
Retrieving key from file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql 
Importing GPG key 0x5072E1F5: 
Userid : MySQL Release Engineering <[email protected]> 
Package: mysql-community-release-el6-5.noarch (installed) 
From : file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql 
Is this ok [y/N]: y 
Running rpm_check_debug 
Running Transaction Test 
Transaction Test Succeeded 
Running Transaction 
Warning: RPMDB altered outside of yum. 
Installing : mysql-community-common-5.6.36-2.el6.i686 1/5 
Installing : mysql-community-libs-5.6.36-2.el6.i686 2/5 
Installing : mysql-community-client-5.6.36-2.el6.i686 3/5 
Installing : perl-DBI-1.609-4.el6.i686 4/5 
Installing : mysql-community-server-5.6.36-2.el6.i686 5/5 
Verifying : perl-DBI-1.609-4.el6.i686 1/5 
Verifying : mysql-community-server-5.6.36-2.el6.i686 2/5 
Verifying : mysql-community-libs-5.6.36-2.el6.i686 3/5 
Verifying : mysql-community-common-5.6.36-2.el6.i686 4/5 
Verifying : mysql-community-client-5.6.36-2.el6.i686 5/5 
 
Installed: 
mysql-community-server.i686 0:5.6.36-2.el6 
 
Dependency Installed: 
mysql-community-client.i686 0:5.6.36-2.el6 mysql-community-common.i686 0:5.6.36-2.el6 
mysql-community-libs.i686 0:5.6.36-2.el6 perl-DBI.i686 0:1.609-4.el6 
 
Complete!

これでインストールは完了です。 MySQLデーモンを初めて起動します。


[[email protected] yum.repos.d]# service mysqld restart 
Stopping mysqld: [ OK ] 
Initializing MySQL database: 2017-05-22 09:55:53 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 
2017-05-22 09:55:53 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap. 
2017-05-22 09:55:53 0 [Note] /usr/sbin/mysqld (mysqld 5.6.36) starting as process 18645 ... 
2017-05-22 09:55:53 18645 [Note] InnoDB: Using atomics to ref count buffer pool pages 
2017-05-22 09:55:53 18645 [Note] InnoDB: The InnoDB memory heap is disabled 
2017-05-22 09:55:53 18645 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 
2017-05-22 09:55:53 18645 [Note] InnoDB: Memory barrier is not used 
2017-05-22 09:55:53 18645 [Note] InnoDB: Compressed tables use zlib 1.2.3 
2017-05-22 09:55:53 18645 [Note] InnoDB: Using Linux native AIO 
2017-05-22 09:55:53 18645 [Note] InnoDB: Using CPU crc32 instructions 
2017-05-22 09:55:53 18645 [Note] InnoDB: Initializing buffer pool, size = 128.0M 
2017-05-22 09:55:53 18645 [Note] InnoDB: Completed initialization of buffer pool 
2017-05-22 09:55:53 18645 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created! 
2017-05-22 09:55:53 18645 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB 
2017-05-22 09:55:53 18645 [Note] InnoDB: Database physically writes the file full: wait... 
2017-05-22 09:55:53 18645 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB 
2017-05-22 09:55:53 18645 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB 
2017-05-22 09:55:53 18645 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 
2017-05-22 09:55:53 18645 [Warning] InnoDB: New log files created, LSN=45781 
2017-05-22 09:55:53 18645 [Note] InnoDB: Doublewrite buffer not found: creating new 
2017-05-22 09:55:53 18645 [Note] InnoDB: Doublewrite buffer created 
2017-05-22 09:55:53 18645 [Note] InnoDB: 128 rollback segment(s) are active. 
2017-05-22 09:55:53 18645 [Warning] InnoDB: Creating foreign key constraint system tables. 
2017-05-22 09:55:53 18645 [Note] InnoDB: Foreign key constraint system tables created 
2017-05-22 09:55:53 18645 [Note] InnoDB: Creating tablespace and datafile system tables. 
2017-05-22 09:55:53 18645 [Note] InnoDB: Tablespace and datafile system tables created. 
2017-05-22 09:55:53 18645 [Note] InnoDB: Waiting for purge to start 
2017-05-22 09:55:53 18645 [Note] InnoDB: 5.6.36 started; log sequence number 0 
2017-05-22 09:55:54 18645 [Note] Binlog end 
2017-05-22 09:55:54 18645 [Note] InnoDB: FTS optimize thread exiting. 
2017-05-22 09:55:54 18645 [Note] InnoDB: Starting shutdown... 
2017-05-22 09:55:55 18645 [Note] InnoDB: Shutdown completed; log sequence number 1625977 
 
 
2017-05-22 09:55:55 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 
2017-05-22 09:55:55 0 [Note] Ignoring --secure-file-priv value as server is running with --bootstrap. 
2017-05-22 09:55:55 0 [Note] /usr/sbin/mysqld (mysqld 5.6.36) starting as process 18667 ... 
2017-05-22 09:55:55 18667 [Note] InnoDB: Using atomics to ref count buffer pool pages 
2017-05-22 09:55:55 18667 [Note] InnoDB: The InnoDB memory heap is disabled 
2017-05-22 09:55:55 18667 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 
2017-05-22 09:55:55 18667 [Note] InnoDB: Memory barrier is not used 
2017-05-22 09:55:55 18667 [Note] InnoDB: Compressed tables use zlib 1.2.3 
2017-05-22 09:55:55 18667 [Note] InnoDB: Using Linux native AIO 
2017-05-22 09:55:55 18667 [Note] InnoDB: Using CPU crc32 instructions 
2017-05-22 09:55:55 18667 [Note] InnoDB: Initializing buffer pool, size = 128.0M 
2017-05-22 09:55:55 18667 [Note] InnoDB: Completed initialization of buffer pool 
2017-05-22 09:55:55 18667 [Note] InnoDB: Highest supported file format is Barracuda. 
2017-05-22 09:55:55 18667 [Note] InnoDB: 128 rollback segment(s) are active. 
2017-05-22 09:55:55 18667 [Note] InnoDB: Waiting for purge to start 
2017-05-22 09:55:55 18667 [Note] InnoDB: 5.6.36 started; log sequence number 1625977 
2017-05-22 09:55:55 18667 [Note] Binlog end 
2017-05-22 09:55:55 18667 [Note] InnoDB: FTS optimize thread exiting. 
2017-05-22 09:55:55 18667 [Note] InnoDB: Starting shutdown... 
2017-05-22 09:55:57 18667 [Note] InnoDB: Shutdown completed; log sequence number 1625987 
 
 
 
 
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! 
To do so, start the server, then issue the following commands: 
 
/usr/bin/mysqladmin -u root password 'new-password' 
/usr/bin/mysqladmin -u root -h vdevknime1 password 'new-password' 
 
Alternatively you can run: 
 
/usr/bin/mysql_secure_installation 
 
which will also give you the option of removing the test 
databases and anonymous user created by default. This is 
strongly recommended for production servers. 
 
See the manual for more instructions. 
 
Please report any problems at http://bugs.mysql.com/ 
 
The latest information about MySQL is available on the web at 
 
http://www.mysql.com 
 
Support MySQL by buying support/licenses at http://shop.mysql.com 
 
Note: new default config file not created. 
Please make sure your config file is current 
 
WARNING: Default config file /etc/my.cnf exists on the system 
This file will be read by default by the MySQL server 
If you do not want to use this, either remove it, or use the 
#NAME? 
 
[ OK ] 
Starting mysqld: [ OK ]

これで、MySQLサーバーサービスが稼働しました。 MySQLサービスが使用するポートをリストして確認しましょう。デフォルトでは、MySQLはサービスの起動時にポート3306を使用します。コマンドは次のとおりです。


[[email protected] yum.repos.d]# netstat -apn|grep -i mysql 
tcp 0 0 :::3306 :::* LISTEN 2139/mysqld 
unix 2 [ ACC ] STREAM LISTENING 16018 2139/mysqld /var/lib/mysql/mysql.sock 

それでは、MySQLサーバーの基本的なセキュリティを逃さないように、MySQLrootユーザーの初期パスワードを設定しましょう。


[[email protected] yum.repos.d]# mysqladmin -u root password "Pass1234" 
Warning: Using a password on the command line interface can be insecure. 
 
[[email protected] yum.repos.d]# mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 5 
Server version: 5.6.36 MySQL Community Server (GPL) 
 
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. 
 
Oracle is a registered trademark of Oracle Corporation and/or its 
affiliates. Other names may be trademarks of their respective 
owners. 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
 
mysql> select user(); 
+----------------+ 
| user() | 
+----------------+ 
| [email protected] | 
+----------------+ 
1 row in set (0.00 sec)

インストールフェーズで完了。マルチマスターレプリケーションの設定に移りましょう。

3。構成フェーズ

MySQL my.cnf構成ファイルの内部に入り、サーバーDB1で以下のような変更を加えましょう。


[[email protected] ~]# vi /etc/my.cnf 
[mysqld] 
datadir=/var/lib/mysql 
socket=/var/lib/mysql/mysql.sock 
port=3306 
innodb_file_per_table=ON 
pid-file=/var/lib/mysql/mysqld.pid 
 
server-id = 11 
log_bin = /var/log/mysql/mysql-bin.log 
binlog_do_db = test_rep

以下は構成の説明です：

  • server-id==>レプリケーションID
  • log_bin==>レプリケーションアクティビティに使用されるログファイル
  • binlog_do_db==>レプリケーションプロセスに関連するデータベース

完了したら、MySQLサーバー環境にアクセスして、関連するデータベースを作成し、レプリケーションプロセスにユーザーを割り当てましょう。

[[email protected] ~]# mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 5 
Server version: 5.6.36-log MySQL Community Server (GPL) 
 
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. 
 
Oracle is a registered trademark of Oracle Corporation and/or its 
affiliates. Other names may be trademarks of their respective 
owners. 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
 
mysql> show databases; 
+--------------------+ 
| Database | 
+--------------------+ 
| information_schema | 
| mysql | 
| performance_schema | 
| test | 
+--------------------+ 
4 rows in set (0.01 sec) 
 
mysql> create database test_rep; 
Query OK, 1 row affected (0.01 sec) 
 
mysql> create user 'replicator'@'DB2' identified by 'Rep1234'; 
Query OK, 0 rows affected (0.01 sec) 
 
mysql> grant replication slave on *.* to 'replicator'@'DB2'; 
Query OK, 0 rows affected (0.00 sec) 

完了したら、MySQLサーバーを再起動して、構成がアクティブ化されているかどうかを確認します。手順は次のとおりです。

[[email protected] yum.repos.d]# service mysqld restart 
Stopping mysqld: [ OK ] 
Starting mysqld: [ OK ] 
 
[[email protected] ~]# mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 5 
Server version: 5.6.36-log MySQL Community Server (GPL) 
 
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. 
 
Oracle is a registered trademark of Oracle Corporation and/or its 
affiliates. Other names may be trademarks of their respective 
owners. 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
 
mysql> show master status; 
+-------------------+----------+--------------+------------------+-------------------+ 
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 
+-------------------+----------+--------------+------------------+-------------------+ 
| mysql-bin.000001 | 854 | test_rep | | | 
+-------------------+----------+--------------+------------------+-------------------+ 
1 row in set (0.00 sec) 

では、サーバーDB2をDB1レプリケーションマスターのスレーブサーバーとして設定し、さらにサーバーDB2をDB1サーバーのマスターとして設定しましょう。手順は次のとおりです。

[[email protected] ~]# vi /etc/my.cnf 
[mysqld] 
datadir=/var/lib/mysql 
socket=/var/lib/mysql/mysql.sock 
port=3306 
innodb_file_per_table=ON 
pid-file=/var/lib/mysql/mysqld.pid 
 
server-id = 12 
log_bin = /var/log/mysql/mysql-bin.log 
binlog_do_db = test_rep 

DB1の構成と同じように、MySQLサーバー環境にアクセスして、関連するデータベースを作成し、レプリケーションプロセスにユーザーを割り当てましょう。

[[email protected] ~]# mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 5 
Server version: 5.6.36-log MySQL Community Server (GPL) 
 
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. 
 
Oracle is a registered trademark of Oracle Corporation and/or its 
affiliates. Other names may be trademarks of their respective 
owners. 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
 
mysql> show databases; 
+--------------------+ 
| Database | 
+--------------------+ 
| information_schema | 
| mysql | 
| performance_schema | 
| test | 
+--------------------+ 
4 rows in set (0.01 sec) 
 
mysql> create database test_rep; 
Query OK, 1 row affected (0.01 sec) 
 
mysql> create user 'replicator'@'DB1' identified by 'Rep1234'; 
Query OK, 0 rows affected (0.01 sec) 
 
mysql> grant replication slave on *.* to 'replicator'@'DB1'; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> show slave status; 
Empty set (0.01 sec)

これで、DB2 MySQLサーバーを再起動して、構成がアクティブ化されているかどうかを確認しましょう。はいの場合は、引き続きDB1サーバーのスレーブの作成を続行します。


[[email protected] ~]# service mysqld restart 
Stopping mysqld: [ OK ] 
Starting mysqld: [ OK ] 
 
[[email protected] ~]# mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 5 
Server version: 5.6.36-log MySQL Community Server (GPL) 
 
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. 
 
Oracle is a registered trademark of Oracle Corporation and/or its 
affiliates. Other names may be trademarks of their respective 
owners. 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
 
mysql> show master status; 
+-------------------+----------+--------------+------------------+-------------------+ 
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 
+-------------------+----------+--------------+------------------+-------------------+ 
| mysql-bin.000001 | 553 | test_rep | | | 
+-------------------+----------+--------------+------------------+-------------------+ 
1 row in set (0.00 sec) 
 
mysql> stop slave; 
Query OK, 0 rows affected, 1 warning (0.00 sec) 
 
mysql> CHANGE MASTER TO MASTER_HOST = 'DB1', MASTER_PORT = 3306, MASTER_USER = 'replicator', MASTER_PASSWORD = 'Rep1234', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 854; 
Query OK, 0 rows affected, 2 warnings (0.07 sec) 
 
mysql> start slave; 
Query OK, 0 rows affected (0.04 sec) 
 
mysql> show slave status\G; 
*************************** 1. row *************************** 
Slave_IO_State: Waiting for master to send event 
Master_Host: DB1 
Master_User: replicator 
Master_Port: 3306 
Connect_Retry: 60 
Master_Log_File: mysql-bin.000001 
Read_Master_Log_Pos: 854 
Relay_Log_File: mysqld-relay-bin.000002 
Relay_Log_Pos: 284 
Relay_Master_Log_File: mysql-bin.000001 
Slave_IO_Running: Yes 
Slave_SQL_Running: Yes 
Replicate_Do_DB: 
Replicate_Ignore_DB: 
Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
Last_Errno: 0 
Last_Error: 
Skip_Counter: 0 
Exec_Master_Log_Pos: 854 
Relay_Log_Space: 459 
Until_Condition: None 
Until_Log_File: 
Until_Log_Pos: 0 
Master_SSL_Allowed: No 
Master_SSL_CA_File: 
Master_SSL_CA_Path: 
Master_SSL_Cert: 
Master_SSL_Cipher: 
Master_SSL_Key: 
Seconds_Behind_Master: 0 
Master_SSL_Verify_Server_Cert: No 
Last_IO_Errno: 0 
Last_IO_Error: 
Last_SQL_Errno: 0 
Last_SQL_Error: 
Replicate_Ignore_Server_Ids: 
Master_Server_Id: 11 
Master_UUID: 6e143d91-3635-11e7-b9ad-08002742c04c 
Master_Info_File: /var/lib/mysql/master.info 
SQL_Delay: 0 
SQL_Remaining_Delay: NULL 
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it 
Master_Retry_Count: 86400 
Master_Bind: 
Last_IO_Error_Timestamp: 
Last_SQL_Error_Timestamp: 
Master_SSL_Crl: 
Master_SSL_Crlpath: 
Retrieved_Gtid_Set: 
Executed_Gtid_Set: 
Auto_Position: 0 
1 row in set (0.00 sec) 
 
ERROR: 
No query specified 
 
 
mysql> show slave status; 
+----------------------------------+---------------+-------------+-------------+---------------+-------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+----------- 
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | 
+----------------------------------+---------------+-------------+-------------+---------------+-------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+----------- 
| Waiting for master to send event | DB1 | replicator | 3306 | 60 | mysql-bin.000001 | 854 | mysqld-relay-bin.000002 | 284 | mysql-bin.000001 | Yes | Yes | | | | | | | 0 | | 0 | 854 | 459 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 11 | 6e143d91-3635-11e7-b9ad-08002742c04c | /var/lib/mysql/master.info | 0 | NULL | Slave has read all relay log; waiting for the slave I/O thread to update it | 86400 | | | | | | | | 0 | 
+----------------------------------+---------------+-------------+-------------+---------------+-------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+----------- 
1 row in set (0.00 sec) 

素晴らしい！ DB2サーバー用にすべてがセットアップされたので、DB1サーバーに戻って、DB2サーバー用のスレーブ構成を作成します。

[[email protected] ~]# mysql -u root -p 
Enter password: 
Warning: Using a password on the command line interface can be insecure. 
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 11 
Server version: 5.6.36-log MySQL Community Server (GPL) 
 
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. 
 
Oracle is a registered trademark of Oracle Corporation and/or its 
affiliates. Other names may be trademarks of their respective 
owners. 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
 
mysql> show master status; 
+-------------------+----------+--------------+------------------+-------------------+ 
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 
+-------------------+----------+--------------+------------------+-------------------+ 
| mysql-bin.000001 | 854 | test_rep | | | 
+-------------------+----------+--------------+------------------+-------------------+ 
1 row in set (0.01 sec) 
 
mysql> stop slave; 
Query OK, 0 rows affected, 1 warning (0.00 sec) 
 
mysql> CHANGE MASTER TO MASTER_HOST = 'DB2', MASTER_PORT = 3306, MASTER_USER = 'replicator', MASTER_PASSWORD = 'Rep1234', MASTER_LOG_FILE = 'mysql-bin.000001', MASTER_LOG_POS = 553; 
Query OK, 0 rows affected, 2 warnings (0.25 sec) 
 
mysql> start slave; 
Query OK, 0 rows affected (0.03 sec) 
 
mysql> show slave status; 
+----------------------------------+---------------+-------------+-------------+---------------+-------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+----------- 
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id | Master_UUID | Master_Info_File | SQL_Delay | SQL_Remaining_Delay | Slave_SQL_Running_State | Master_Retry_Count | Master_Bind | Last_IO_Error_Timestamp | Last_SQL_Error_Timestamp | Master_SSL_Crl | Master_SSL_Crlpath | Retrieved_Gtid_Set | Executed_Gtid_Set | Auto_Position | 
+----------------------------------+---------------+-------------+-------------+---------------+-------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+----------- 
| Waiting for master to send event | DB2 | replicator | 3306 | 60 | mysql-bin.000001 | 553 | mysqld-relay-bin.000002 | 284 | mysql-bin.000001 | Yes | Yes | | | | | | | 0 | | 0 | 553 | 459 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 12 | 14f5ab41-3c7b-11e7-a293-08002742c04c | /var/lib/mysql/master.info | 0 | NULL | Slave has read all relay log; waiting for the slave I/O thread to update it | 86400 | | | | | | | | 0 | 
+----------------------------------+---------------+-------------+-------------+---------------+-------------------+---------------------+--------------------------+---------------+-----------------------+------------------+-------------------+----------- 
1 row in set (0.01 sec) 
 
mysql> show slave status\G; 
*************************** 1. row *************************** 
Slave_IO_State: Waiting for master to send event 
Master_Host: DB2 
Master_User: replicator 
Master_Port: 3306 
Connect_Retry: 60 
Master_Log_File: mysql-bin.000001 
Read_Master_Log_Pos: 553 
Relay_Log_File: mysqld-relay-bin.000002 
Relay_Log_Pos: 284 
Relay_Master_Log_File: mysql-bin.000001 
Slave_IO_Running: Yes 
Slave_SQL_Running: Yes 
Replicate_Do_DB: 
Replicate_Ignore_DB: 
Replicate_Do_Table: 
Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
Last_Errno: 0 
Last_Error: 
Skip_Counter: 0 
Exec_Master_Log_Pos: 553 
Relay_Log_Space: 459 
Until_Condition: None 
Until_Log_File: 
Until_Log_Pos: 0 
Master_SSL_Allowed: No 
Master_SSL_CA_File: 
Master_SSL_CA_Path: 
Master_SSL_Cert: 
Master_SSL_Cipher: 
Master_SSL_Key: 
Seconds_Behind_Master: 0 
Master_SSL_Verify_Server_Cert: No 
Last_IO_Errno: 0 
Last_IO_Error: 
Last_SQL_Errno: 0 
Last_SQL_Error: 
Replicate_Ignore_Server_Ids: 
Master_Server_Id: 12 
Master_UUID: 14f5ab41-3c7b-11e7-a293-08002742c04c 
Master_Info_File: /var/lib/mysql/master.info 
SQL_Delay: 0 
SQL_Remaining_Delay: NULL 
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it 
Master_Retry_Count: 86400 
Master_Bind: 
Last_IO_Error_Timestamp: 
Last_SQL_Error_Timestamp: 
Master_SSL_Crl: 
Master_SSL_Crlpath: 
Retrieved_Gtid_Set: 
Executed_Gtid_Set: 
Auto_Position: 0 
1 row in set (0.00 sec) 
 
ERROR: 
No query specified 

Nicely done, now as everything is ready in place, let's proceed with the testing phase to conclude all configuration was made correctly.

4. Testing Phase

Before we start the test, let's make the assumptions for the final result expectations. For this test, we will create a table on DB1 MySQL server then on DB2 we will check if the table automatically exists or not. If yes then we will add a new data row into it and check again in DB1 server if new data is available on both servers.


[[email protected] ~]# mysql -u root -p test_rep 
Enter password: 
Warning: Using a password on the command line interface can be insecure. 
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 16 
Server version: 5.6.36-log MySQL Community Server (GPL) 
 
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. 
 
Oracle is a registered trademark of Oracle Corporation and/or its 
affiliates. Other names may be trademarks of their respective 
owners. 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
 
mysql> select database(); 
+------------+ 
| database() | 
+------------+ 
| test_rep | 
+------------+ 
1 row in set (0.00 sec) 
 
mysql> create table tbl1( id int(11) primary key auto_increment, fullname varchar(30)); 
Query OK, 0 rows affected (0.22 sec) 
 
mysql> show tables in test_rep; 
+--------------------+ 
| Tables_in_test_rep | 
+--------------------+ 
| tbl1 | 
+--------------------+ 
1 row in set (0.01 sec) 

Done, as the table creation is a DDL (Data Definition Language) statement, there's no need to enter a commit command. Now let's go inside DB2 MySQL server and see if the newly table created exists.

[[email protected] ~]# mysql -u root -p test_rep 
Enter password: 
Reading table information for completion of table and column names 
You can turn off this feature to get a quicker startup with -A 
 
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 8 
Server version: 5.6.36-log MySQL Community Server (GPL) 
 
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. 
 
Oracle is a registered trademark of Oracle Corporation and/or its 
affiliates. Other names may be trademarks of their respective 
owners. 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
 
mysql> show tables in test_rep; 
+--------------------+ 
| Tables_in_test_rep | 
+--------------------+ 
| tbl1 | 
+--------------------+ 
1 row in set (0.00 sec) 
 
mysql> insert into tbl1 ( fullname ) values ('Shahril'), ('mark'), ('Allen'), ('Suzy'), ('Adam') ; 
Query OK, 5 rows affected (0.05 sec) 
Records: 5 Duplicates: 0 Warnings: 0 
 
mysql> select * from tbl1; 
+----+----------+ 
| id | fullname | 
+----+----------+ 
| 1 | Shahril | 
| 2 | mark | 
| 3 | Allen | 
| 4 | Suzy | 
| 5 | Adam | 
+----+----------+ 
5 rows in set (0.00 sec) 
 
mysql> commit; 
Query OK, 0 rows affected (0.00 sec) 

Excellent, notice that the newly table created table in DB1 server now automatically exists in DB2 MySQL server. Then we also managed to insert 5 rows of data into the table. For the final check, let's see if the updated rows in table TBL1 can be seen in DB1 server as well.


[[email protected] ~]# mysql -u root -p test_rep 
Enter password: 
Reading table information for completion of table and column names 
You can turn off this feature to get a quicker startup with -A 
 
Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 8 
Server version: 5.6.36-log MySQL Community Server (GPL) 
 
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. 
 
Oracle is a registered trademark of Oracle Corporation and/or its 
affiliates. Other names may be trademarks of their respective 
owners. 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
 
mysql> select * from tbl1; 
+----+----------+ 
| id | fullname | 
+----+----------+ 
| 1 | Shahril | 
| 2 | mark | 
| 3 | Allen | 
| 4 | Suzy | 
| 5 | Adam | 
+----+----------+ 
5 rows in set (0.01 sec) 
 
mysql> delete from tbl1 where fullname like 'A%'; 
Query OK, 2 rows affected (0.07 sec) 
 
mysql> select * from tbl1; 
+----+----------+ 
| id | fullname | 
+----+----------+ 
| 1 | Shahril | 
| 2 | mark | 
| 4 | Suzy | 
+----+----------+ 
3 rows in set (0.00 sec) 

いいぞ！ We've successfully created a MySQL multi-master replication between 2 servers.


Linux

  1. LinuxでVimをデフォルトのエディターとして設定する方法

  2. OracleLinux8にAnsibleをインストールする方法

  3. MySQLソース-レプリカレプリケーションを構成する

  1. MySQLソース-ソースレプリケーションを構成する

  2. Oracle Linux 6でVNCサーバーを構成する方法

  3. Oracle Linux 6でインタフェース・ボンディング(NICチーミング)を構成する方法

  1. Ubuntu18.04でMySQLマスタースレーブレプリケーションを構成する方法

  2. Oracle Enterprise Linux (OEL 5,6) で kdump を構成する方法

  3. Linux で実行されている MySQL サーバーのリソース グループを構成する方法