本教程介绍如何在Oracle Linux上设置和配置MySQL多主机复制。 您可能已经知道，MySQL是一个众所周知的顶尖数据库产品，已被证明是企业准备的。 由于数据对于每个组织都至关重要，因此大多数数据库管理员正在寻找适合的解决方案来设置高可用性，以确保用户能够全天候访问其数据。 MySQL复制是一种可以确保高可用性策略的解决方案。 此外，MySQL复制还能够通过负载平衡READ和WRITE请求来帮助数据库管理员在多个数据库服务器上分发负载。 不幸的是，基本复制只能为READ请求带来好处。 因此，引入了MySQL多主复制，为WRITE请求提供复制。
初步说明
在本教程中，我正在使用32位版本的Oracle Linux 6.8。 请注意，即使配置是在Oracle Linux下进行的，但步骤和配置与CentOS和Red Hat Linux相同。 在本教程中，我们将使用2台服务器。 在他们中的每一个上，我们将设置一个MySQL数据库并将其配置为多主复制。 在本教程末尾，我们将看到任何READ或WRITE请求都将在两个服务器上运行，包括DDL（数据定义语言）和DML（数据操作语言）请求。
2.安装阶段
对于安装阶段，我们只需要配置阶段的MySQL服务器包和MySQL客户端来访问数据库环境。 两个软件包都需要安装一些依赖项。 首先，我们确认我们的操作系统的版本，并记下预配置的IP地址。
[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)
现在在其他服务器上执行相同操作。
[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)
接下来将IP地址添加到服务器的主机文件。 在两个服务器上执行相同操作，如下所示。
[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
接下来，我将配置一个新的存储库，通过yum实用程序安装MySQL服务器和MySQL客户端软件包。 请在BOTH服务器上执行此操作。
[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
最新的MySQL版本的新版本已经安装。 让我们来吧
[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
一旦完成，我们确保MySQL包可用。
[root@DB1 yum.repos.d]# rpm -qa|grep -i mysql
mysql-community-release-el6-5.noarch
太好了，现在我们做了一半了。 由于当前服务器中没有安装MySQL软件包，因此我们开始安装软件包。 以下是步骤。
[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!
非常好，现在安装完成了。 首次启动MySQL守护程序。
[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 ]
好的，现在我们的MySQL服务器服务已经启动。 通过列出MySQL服务使用的端口来确认它。 默认情况下，MySQL将在启动服务时使用端口3306。 以下是命令：
[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
现在，我们为MySQL root用户设置一个初始密码，以确保我们没有错过MySQL服务器的基本安全性。
[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)
完成安装阶段。 我们继续进行多主复制设置的配置。
配置阶段
我们进入MySQL my.cnf配置文件，并在服务器DB1上进行如下更改。
[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
以下是配置说明：
- server-id ==>复制ID
- log_bin ==>要用于复制活动的日志文件
- binlog_do_db ==>与复制过程相关的数据库
一旦完成，我们进入MySQL服务器环境，并创建相关数据库并为复制过程分配一个用户。
[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)
完成后，现在重新启动MySQL服务器，看看配置是否已被激活。 以下步骤如下：
[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)
非常好，现在让我们将服务器DB2设置为DB1复制主服务器的从服务器，并且还将服务器DB2设置为DB1服务器的主服务器。 以下步骤如下：
[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
与DB1中的配置一样，让我们进入MySQL服务器环境，并创建相关数据库并为复制过程分配一个用户。
[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)
完成，现在让我们重新启动DB2 MySQL服务器，看看配置是否被激活。 如果是，那么我们继续继续创建DB1服务器的从站。
[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)
大！ 随着DB2服务器的一切设置，放回到DB1服务器并为DB2服务器进行从配置。
[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
做得很好，现在随着一切准备就绪，我们继续进行测试阶段，总结出所有的配置是否正确。
4.测试阶段
在我们开始测试之前，我们来做出最终结果预期的假设。 对于此测试，我们将在DB1 MySQL服务器上创建一个表，然后在DB2上，我们将检查表是否自动存在。 如果是，那么我们将添加一个新的数据行，并在DB1服务器中再次检查，如果两个服务器上都有新数据。
[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)
完成，因为表创建是一个DDL（数据定义语言）语句，所以不需要输入提交命令。 现在让我们进入DB2 MySQL服务器，看看新创建的表是否存在。
[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)
非常好，请注意，DB1服务器中新创建的表现在自动存在于DB2 MySQL服务器中。 然后我们还设法在表中插入5行数据。 对于最终检查，我们来看看表TBL1中更新的行是否也可以在DB1服务器中看到。
[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)
竖起大拇指！ 我们已经在两台服务器之间成功创建了一个MySQL多主机复制。
