So konfigurieren Sie die MySQL Multi-Master-Replikation auf Oracle Linux

Dieses Tutorial erklärt, wie Sie die MySQL-Multi-Master-Replikation unter Oracle Linux einrichten und konfigurieren. Wie Sie alle vielleicht schon wissen, ist MySQL ein bekanntes erstklassiges Datenbankprodukt, das sich als unternehmensreif erwiesen hat. Da Daten für jedes Unternehmen von entscheidender Bedeutung sind, suchen die meisten Datenbankadministratoren nach einer geeigneten Lösung, um eine hohe Verfügbarkeit einzurichten, um sicherzustellen, dass die Benutzer rund um die Uhr auf ihre Daten zugreifen können. Die MySQL-Replikation ist eine Lösung, die eine Hochverfügbarkeitspolitik gewährleisten kann. Darüber hinaus kann die MySQL-Replikation Datenbankadministratoren auch dabei unterstützen, die Last auf mehrere Datenbankserver zu verteilen, indem sie die READ- und WRITE-Anfragen lastverteilt. Leider kann die einfache Replikation nur Vorteile bei READ-Anfragen bieten. Aus diesem Grund wurde die MySQL-Multi-Master-Replikation eingeführt, um die Replikation auch für WRITE-Anfragen anzubieten.

1. Vorbemerkung

Für dieses Tutorial verwende ich Oracle Linux 6.8 in der 32-Bit-Version. Bitte beachten Sie, dass die Konfiguration zwar unter Oracle Linux erfolgt, die Schritte und die Konfiguration jedoch im Wesentlichen die gleichen wie bei CentOS und Red Hat Linux sind. In diesem Tutorial werden wir 2 Server verwenden. Auf jedem von ihnen werden wir eine MySQL-Datenbank einrichten und sie für die Multi-Master-Replikation konfigurieren. Am Ende dieses Tutorials werden wir sehen, dass alle READ- oder WRITE-Anfragen einschließlich DDL (Data Definition Language) und DML (Data Manipulation Language) auf beiden Servern ausgeführt werden.

2. Installationsphase

Für die Installationsphase benötigen wir nur das MySQL-Serverpaket für die Konfigurationsphase und den MySQL-Client für den Zugriff auf die Datenbankumgebung. Beide Pakete erfordern die Installation einiger Abhängigkeiten. Zuerst bestätigen wir die Version unseres Betriebssystems und notieren uns die IP-Adresse zur Vorkonfiguration.

[root@DB1 ~]# 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
[root@DB1 ~]# 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)

Führen Sie nun das gleiche auf dem anderen Server durch.

[root@DB2 ~]# 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
[root@DB2 ~]# 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)

Als nächstes fügen Sie die IP-Adresse der Hosts-Datei des Servers hinzu. Mach das Gleiche auf beiden Servern wie unten.

[root@DB1 ~]# 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

Als nächstes werde ich ein neues Repository konfigurieren, um den MySQL-Server und die MySQL-Client-Pakete über das Yum-Utility zu installieren. Bitte tun Sie dies auf beiden Servern.

[root@DB1 ~]# cd /etc/yum.repos.d/ 
[root@DB1 yum.repos.d]# ls 
OEL6.repo
[root@DB1 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]

[root@DB1 yum.repos.d]# ls
OEL6.repo mysql-community-release-el6-5.noarch.rpm

[root@DB1 yum.repos.d]# rpm -Uvh mysql-community-release-el6-5.noarch.rpm
Preparing... ########################################### [100%]
1:mysql-community-release########################################### [100%]

[root@DB1 yum.repos.d]# ls
CentOS.repo mysql-community.repo
mysql-community-release-el6-5.noarch.rpm mysql-community-source.repo

Das neue Repository für die neueste MySQL-Version wurde installiert. Lassen Sie uns sie aktivieren.

[root@DB1 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

Sobald dies erledigt ist, stellen wir sicher, dass die MySQL-Pakete verfügbar sind.

[root@DB1 yum.repos.d]# rpm -qa|grep -i mysql
mysql-community-release-el6-5.noarch

Großartig, jetzt sind wir auf halbem Weg. Da auf dem aktuellen Server keine MySQL-Pakete installiert sind, starten wir die Paketinstallation. Nachfolgend finden Sie die Schritte.

[root@DB1 yum.repos.d]# 
[root@DB1 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 <mysql-build@oss.oracle.com>
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!

Ausgezeichnet, jetzt ist die Installation abgeschlossen. Starten Sie den MySQL-Daemon zum ersten Mal.

[root@DB1 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 ]

Großartig, jetzt ist unser MySQL-Serverdienst verfügbar. Lassen Sie uns das bestätigen, indem wir den Port auflisten, der vom MySQL-Dienst verwendet wird. Standardmäßig verwendet MySQL beim Starten des Dienstes den Port 3306. Nachfolgend finden Sie die Befehle:

[root@DB1 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

Nun, lassen Sie uns ein Initialpasswort für den MySQL-Root-Benutzer einrichten, um sicherzustellen, dass wir die grundlegende Sicherheit für unseren MySQL-Server nicht verpassen.

[root@DB1 yum.repos.d]# mysqladmin -u root password "Pass1234" 
Warning: Using a password on the command line interface can be insecure.

[root@DB1 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() |
+—————-+
| root@localhost |
+—————-+
1 row in set (0.00 sec)

Erledigt mit der Installationsphase. Kommen wir nun zur Konfiguration des Multi-Master-Replikationsaufbaus.

3. Konfigurationsphase

Gehen wir in die MySQL my.cnf-Konfigurationsdatei und nehmen die Änderungen wie unten auf dem Server DB1 vor.

[root@DB1 ~]# 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

Nachfolgend finden Sie die Erklärung für die Konfiguration:

  • server-id ==> Die Replikations-ID
  • log_bin ==> Die Protokolldatei, die für die Replikationsaktivität verwendet werden soll.
  • binlog_do_db ==> Die Datenbank, die sich auf den Replikationsprozess bezieht.

Sobald dies erledigt ist, gehen wir in die MySQL-Serverumgebung und erstellen die zugehörige Datenbank und weisen einen Benutzer für den Replikationsprozess zu.

[root@DB1 ~]# 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)

Erledigt, starten Sie nun den MySQL-Server neu und prüfen Sie, ob die Konfiguration aktiviert wurde oder nicht. Nachfolgend finden Sie die Schritte:

[root@DB1 yum.repos.d]# service mysqld restart 
Stopping mysqld: [ OK ] 
Starting mysqld: [ OK ]

[root@DB1 ~]# 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)

Ausgezeichnet, nun lassen Sie uns den Server DB2 als Slave-Server für den DB1-Replikationsmaster einrichten und darüber hinaus den Server DB2 auch als Master für den DB1-Server einrichten. Nachfolgend finden Sie die Schritte:

[root@DB2 ~]# 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

Wie bei der Konfiguration in DB1 gehen wir in die MySQL-Serverumgebung und erstellen die zugehörige Datenbank und weisen einen Benutzer für den Replikationsprozess zu.

[root@DB2 ~]# 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)

Erledigt, nun lassen Sie uns den DB2 MySQL-Server neu starten und sehen, ob die Konfiguration aktiviert wurde oder nicht. Wenn ja, dann fahren wir mit der Erstellung des Slaves für den DB1-Server fort.

[root@DB2 ~]# service mysqld restart 
Stopping mysqld: [ OK ] 
Starting mysqld: [ OK ]

[root@DB2 ~]# 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)

Großartig! Da alles für den DB2-Server eingerichtet wurde, gehen Sie zurück zum DB1-Server und nehmen Sie die Slave-Konfiguration für den DB2-Server vor.

[root@DB1 ~]# 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

Gut gemacht, jetzt, da alles bereit ist, lassen Sie uns mit der Testphase fortfahren, um zu schließen, dass alle Konfigurationen korrekt vorgenommen wurden.

4. Testphase

Bevor wir mit dem Test beginnen, lassen Sie uns die Annahmen für die Erwartungen an das Endergebnis treffen. Für diesen Test erstellen wir eine Tabelle auf dem DB1 MySQL-Server und prüfen dann auf DB2, ob die Tabelle automatisch existiert oder nicht. Wenn ja, dann fügen wir eine neue Datenzeile hinzu und überprüfen im DB1-Server erneut, ob neue Daten auf beiden Servern verfügbar sind.

[root@DB1 ~]# 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)

Da die Tabellenerstellung eine DDL-Anweisung (Data Definition Language) ist, ist es nicht erforderlich, einen Commit-Befehl einzugeben. Nun gehen wir in den DB2 MySQL-Server und sehen, ob die neu erstellte Tabelle existiert.

[root@DB2 ~]# 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)

Ausgezeichnet, beachten Sie, dass die neu erstellte Tabelle im DB1-Server jetzt automatisch im DB2 MySQL-Server existiert. Dann ist es uns auch gelungen, 5 Datenzeilen in die Tabelle einzufügen. Für die abschließende Prüfung sehen wir, ob die aktualisierten Zeilen der Tabelle TBL1 auch im DB1-Server zu sehen sind.

[root@DB1 ~]# 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)

Daumen hoch! Wir haben erfolgreich eine MySQL-Multi-Master-Replikation zwischen 2 Servern erstellt.

Das könnte Dich auch interessieren …