GNU/Linux >> Znalost Linux >  >> Linux

Jak nakonfigurovat replikaci MySQL Multi-Master Replication na Oracle Linux

Tento výukový program vysvětluje, jak nastavit a nakonfigurovat replikaci MySQL s více hlavními servery v systému Oracle Linux. Jak už možná všichni víte, MySQL je dobře známý špičkový databázový produkt, který se osvědčil jako podnikový. Vzhledem k tomu, že data jsou pro každou organizaci klíčová, většina správců databází hledá vhodné řešení pro nastavení vysoké dostupnosti, aby uživatelé měli k datům přístup 24 hodin denně, 7 dní v týdnu. Replikace MySQL je řešení, které může zajistit zásady vysoké dostupnosti. Kromě toho je replikace MySQL také schopna pomoci správcům databází rozložit zátěž na více databázových serverů tím, že vyrovnává zatížení požadavků READ a WRITE. Bohužel základní replikace může nabídnout výhody pouze u požadavků READ. Kvůli tomu byla zavedena replikace MySQL s více hlavními servery, která nabízí replikaci i pro požadavky WRITE.


1. Předběžná poznámka

Pro tento tutoriál používám Oracle Linux 6.8 v 32bitové verzi. Vezměte prosím na vědomí, že i když je konfigurace provedena pod Oracle Linux, přesto jsou kroky a konfigurace převážně stejné jako u CentOS a Red Hat Linux. V tomto tutoriálu použijeme 2 servery. Na každém z nich nastavíme databázi MySQL a nakonfigurujeme ji pro multi-master replikaci. Na konci tohoto tutoriálu uvidíme, že na obou serverech budou spuštěny všechny požadavky READ nebo WRITE včetně požadavků DDL (Data Definition Language) a DML (Data Manipulation Language).


2. Fáze instalace

Pro fázi instalace vyžadujeme pouze balíček serveru MySQL pro fázi konfigurace a klienta MySQL pro přístup k databázovému prostředí. Oba balíčky vyžadují instalaci některých závislostí. Nejprve potvrďte verzi našeho operačního systému a poznamenejte si IP adresu pro předběžnou konfiguraci.


[[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)

Nyní udělejte totéž na druhém serveru.


[[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)

Poté přidejte IP adresu do souboru hosts serveru. Proveďte totéž na obou serverech, jak je uvedeno níže.


[[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

Dále nakonfiguruji nové úložiště pro instalaci serverů MySQL a klientských balíčků MySQL pomocí nástroje yum. Udělejte to prosím na OBOU serverech.


[[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

Byl nainstalován nový repozitář pro nejnovější verzi MySQL. Pojďme je povolit.


 
[[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

Až budete hotovi, ujistěte se, že jsou balíčky MySQL dostupné.


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

Skvělé, teď máme polovinu za sebou. Protože na aktuálním serveru nejsou nainstalovány žádné balíčky MySQL, začněme instalaci balíčku. Níže jsou uvedeny kroky.

[[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!

Výborně, nyní je instalace hotová. Poprvé spusťte démona 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 ]

Skvělé, nyní je spuštěna naše služba serveru MySQL. Potvrďme to výpisem portu používaného službou MySQL. Ve výchozím nastavení použije MySQL při spouštění služby port 3306. Níže jsou uvedeny příkazy:


[[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 

Nyní nastavíme počáteční heslo pro uživatele root MySQL, abychom se ujistili, že jsme nepřišli o základní zabezpečení našeho serveru MySQL.


[[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)

Hotovo s fází instalace. Přejděme ke konfiguraci nastavení multimaster replikace.

3. Konfigurační fáze

Pojďme do konfiguračního souboru MySQL my.cnf a proveďte změny jako níže na serveru 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

Níže je vysvětlení konfigurace:

  • server-id ==> ID replikace
  • log_bin ==> Soubor protokolu, který se má použít pro aktivitu replikace
  • binlog_do_db ==> Databáze související s procesem replikace

Až budete hotovi, pojďme do prostředí serveru MySQL a vytvořte související databázi a přiřaďte uživatele pro proces replikace.

[[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) 

Hotovo, nyní restartujte server MySQL a zjistěte, zda byla konfigurace aktivována nebo ne. Níže jsou kroky:

[[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) 

Výborně, nyní nastavíme server DB2 jako slave server pro hlavní replikační server DB1 a navíc nastavíme server DB2 také jako hlavní server pro server DB1. Níže jsou kroky:

[[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 

Stejně jako konfigurace v DB1, pojďme do prostředí serveru MySQL a vytvořte související databázi a přiřaďte uživatele pro proces replikace.

[[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)

Hotovo, nyní restartujeme server DB2 MySQL a uvidíme, zda byla konfigurace aktivována nebo ne. Pokud ano, pokračujeme ve vytváření slave serveru pro 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) 

Skvělý! Protože bylo vše nastaveno pro server DB2, vraťte se zpět na server DB1 a proveďte konfiguraci slave pro server 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) 

Thumbs up! We've successfully created a MySQL multi-master replication between 2 servers.


Linux

  1. Nakonfigurujte replikaci zdrojové repliky MySQL

  2. Nakonfigurujte replikaci zdroje MySQL

  3. Jak nakonfigurovat VNC Server na Oracle Linux 6

  1. Jak nakonfiguruji Vim jako svůj výchozí editor v Linuxu

  2. Jak nakonfigurovat propojení rozhraní (NIC Teaming) na Oracle Linux 6

  3. Jak nakonfigurovat kdump v Oracle Enterprise Linux (OEL 5,6)

  1. Jak nakonfigurovat replikaci MySQL Master-Slave na Ubuntu 18.04

  2. Jak nainstalovat Ansible na Oracle Linux 8

  3. Jak nakonfigurovat skupiny prostředků pro MySQL Server běžící na Linuxu