在mysql-n1, mysql-n2, mysql-n3实例上分别安装MySQL。
安装MySQL
xuej@xuej-VirtualBox:~$ cd Downloads/ && wget https://downloads.mysql.com/archives/get/p/23/file/mysql-server_5.7.37-1ubuntu18.04_amd64.deb-bundle.tar
--2022-06-07 21:25:34-- https://downloads.mysql.com/archives/get/p/23/file/mysql-server_5.7.37-1ubuntu18.04_amd64.deb-bundle.tar
Resolving downloads.mysql.com (downloads.mysql.com)... 137.254.60.14
Connecting to downloads.mysql.com (downloads.mysql.com)|137.254.60.14|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://cdn.mysql.com/archives/mysql-5.7/mysql-server_5.7.37-1ubuntu18.04_amd64.deb-bundle.tar [following]
--2022-06-07 21:25:36-- https://cdn.mysql.com/archives/mysql-5.7/mysql-server_5.7.37-1ubuntu18.04_amd64.deb-bundle.tar
Resolving cdn.mysql.com (cdn.mysql.com)... 184.30.152.230
Connecting to cdn.mysql.com (cdn.mysql.com)|184.30.152.230|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 160399360 (153M) [application/x-tar]
Saving to: ‘mysql-server_5.7.37-1ubuntu18.04_amd64.deb-bundle.tar’
mysql-server_5.7.37-1ubuntu18.04_amd64.deb-bundle. 100%[===============================================================================================================>] 152.97M 2.01MB/s in 98s
2022-06-07 21:27:14 (1.56 MB/s) - ‘mysql-server_5.7.37-1ubuntu18.04_amd64.deb-bundle.tar’ saved [160399360/160399360]
xuej@xuej-VirtualBox:~/Downloads$ tar -xvf mysql-server_5.7.37-1ubuntu18.04_amd64.deb-bundle.tar
libmysqlclient20_5.7.37-1ubuntu18.04_amd64.deb
libmysqlclient-dev_5.7.37-1ubuntu18.04_amd64.deb
libmysqld-dev_5.7.37-1ubuntu18.04_amd64.deb
mysql-client_5.7.37-1ubuntu18.04_amd64.deb
mysql-common_5.7.37-1ubuntu18.04_amd64.deb
mysql-community-client_5.7.37-1ubuntu18.04_amd64.deb
mysql-community-server_5.7.37-1ubuntu18.04_amd64.deb
mysql-community-source_5.7.37-1ubuntu18.04_amd64.deb
mysql-community-test_5.7.37-1ubuntu18.04_amd64.deb
mysql-server_5.7.37-1ubuntu18.04_amd64.deb
mysql-testsuite_5.7.37-1ubuntu18.04_amd64.deb
xuej@xuej-VirtualBox:~/Downloads$ sudo apt-get install libaio1
[sudo] password for xuej:
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
fonts-liberation2 fonts-opensymbol gir1.2-gst-plugins-base-1.0 gir1.2-gstreamer-1.0 gir1.2-gudev-1.0 gir1.2-udisks-2.0 grilo-plugins-0.3-base gstreamer1.0-gtk3 libboost-date-time1.65.1
libboost-filesystem1.65.1 libboost-iostreams1.65.1 libboost-locale1.65.1 libcdr-0.1-1 libclucene-contribs1v5 libclucene-core1v5 libcmis-0.5-5v5 libcolamd2 libdazzle-1.0-0 libe-book-0.1-1
libedataserverui-1.2-2 libeot0 libepubgen-0.1-1 libetonyek-0.1-1 libevent-2.1-6 libexiv2-14 libfreerdp-client2-2 libfreerdp2-2 libgc1c2 libgee-0.8-2 libgexiv2-2 libgom-1.0-0 libgpgmepp6
libgpod-common libgpod4 liblangtag-common liblangtag1 liblirc-client0 liblua5.3-0 libmediaart-2.0-0 libmspub-0.1-1 libodfgen-0.1-1 libqqwing2v5 libraw16 librevenge-0.0-0 libsgutils2-2 libssh-4
libsuitesparseconfig5 libvncclient1 libwinpr2-2 libxapian30 libxmlsec1 libxmlsec1-nss lp-solve media-player-info python3-mako python3-markupsafe syslinux syslinux-common syslinux-legacy
usb-creator-common
Use 'sudo apt autoremove' to remove them.
The following NEW packages will be installed:
libaio1
0 upgraded, 1 newly installed, 0 to remove and 179 not upgraded.
Need to get 6,476 B of archives.
After this operation, 30.7 kB of additional disk space will be used.
Get:1 http://cn.archive.ubuntu.com/ubuntu bionic-updates/main amd64 libaio1 amd64 0.3.110-5ubuntu0.1 [6,476 B]
Fetched 6,476 B in 1s (7,385 B/s)
Selecting previously unselected package libaio1:amd64.
(Reading database ... 119203 files and directories currently installed.)
Preparing to unpack .../libaio1_0.3.110-5ubuntu0.1_amd64.deb ...
Unpacking libaio1:amd64 (0.3.110-5ubuntu0.1) ...
Setting up libaio1:amd64 (0.3.110-5ubuntu0.1) ...
Processing triggers for libc-bin (2.27-3ubuntu1.4) ...
xuej@xuej-VirtualBox:~/Downloads$ sudo dpkg-preconfigure mysql-community-
mysql-community-client_5.7.37-1ubuntu18.04_amd64.deb mysql-community-source_5.7.37-1ubuntu18.04_amd64.deb
mysql-community-server_5.7.37-1ubuntu18.04_amd64.deb mysql-community-test_5.7.37-1ubuntu18.04_amd64.deb
xuej@xuej-VirtualBox:~/Downloads$ sudo dpkg-preconfigure mysql-community-server_*.deb
xuej@xuej-VirtualBox:~/Downloads$ sudo dpkg -i mysql-{common,community-client,client,community-server,server}_*.deb
Selecting previously unselected package mysql-common.
(Reading database ... 119210 files and directories currently installed.)
Preparing to unpack mysql-common_5.7.37-1ubuntu18.04_amd64.deb ...
Unpacking mysql-common (5.7.37-1ubuntu18.04) ...
Selecting previously unselected package mysql-community-client.
Preparing to unpack mysql-community-client_5.7.37-1ubuntu18.04_amd64.deb ...
Unpacking mysql-community-client (5.7.37-1ubuntu18.04) ...
Selecting previously unselected package mysql-client.
Preparing to unpack mysql-client_5.7.37-1ubuntu18.04_amd64.deb ...
Unpacking mysql-client (5.7.37-1ubuntu18.04) ...
Selecting previously unselected package mysql-community-server.
Preparing to unpack mysql-community-server_5.7.37-1ubuntu18.04_amd64.deb ...
Unpacking mysql-community-server (5.7.37-1ubuntu18.04) ...
Selecting previously unselected package mysql-server.
Preparing to unpack mysql-server_5.7.37-1ubuntu18.04_amd64.deb ...
Unpacking mysql-server (5.7.37-1ubuntu18.04) ...
Setting up mysql-common (5.7.37-1ubuntu18.04) ...
update-alternatives: using /etc/mysql/my.cnf.fallback to provide /etc/mysql/my.cnf (my.cnf) in auto mode
Setting up mysql-community-client (5.7.37-1ubuntu18.04) ...
Setting up mysql-client (5.7.37-1ubuntu18.04) ...
dpkg: dependency problems prevent configuration of mysql-community-server:
mysql-community-server depends on libmecab2; however:
Package libmecab2 is not installed.
dpkg: error processing package mysql-community-server (--install):
dependency problems - leaving unconfigured
dpkg: dependency problems prevent configuration of mysql-server:
mysql-server depends on mysql-community-server (= 5.7.37-1ubuntu18.04); however:
Package mysql-community-server is not configured yet.
dpkg: error processing package mysql-server (--install):
dependency problems - leaving unconfigured
Processing triggers for man-db (2.8.3-2ubuntu0.1) ...
Processing triggers for ureadahead (0.100.0-21) ...
Processing triggers for systemd (237-3ubuntu10.52) ...
Errors were encountered while processing:
mysql-community-server
mysql-server
xuej@xuej-VirtualBox:~/Downloads$ sudo apt-get -f install
Reading package lists... Done
Building dependency tree
Reading state information... Done
Correcting dependencies... Done
The following packages were automatically installed and are no longer required:
fonts-liberation2 fonts-opensymbol gir1.2-gst-plugins-base-1.0 gir1.2-gstreamer-1.0 gir1.2-gudev-1.0 gir1.2-udisks-2.0 grilo-plugins-0.3-base gstreamer1.0-gtk3 libboost-date-time1.65.1
libboost-filesystem1.65.1 libboost-iostreams1.65.1 libboost-locale1.65.1 libcdr-0.1-1 libclucene-contribs1v5 libclucene-core1v5 libcmis-0.5-5v5 libcolamd2 libdazzle-1.0-0 libe-book-0.1-1
libedataserverui-1.2-2 libeot0 libepubgen-0.1-1 libetonyek-0.1-1 libevent-2.1-6 libexiv2-14 libfreerdp-client2-2 libfreerdp2-2 libgc1c2 libgee-0.8-2 libgexiv2-2 libgom-1.0-0 libgpgmepp6
libgpod-common libgpod4 liblangtag-common liblangtag1 liblirc-client0 liblua5.3-0 libmediaart-2.0-0 libmspub-0.1-1 libodfgen-0.1-1 libqqwing2v5 libraw16 librevenge-0.0-0 libsgutils2-2 libssh-4
libsuitesparseconfig5 libvncclient1 libwinpr2-2 libxapian30 libxmlsec1 libxmlsec1-nss lp-solve media-player-info python3-mako python3-markupsafe syslinux syslinux-common syslinux-legacy
usb-creator-common
Use 'sudo apt autoremove' to remove them.
The following additional packages will be installed:
libmecab2
The following NEW packages will be installed:
libmecab2
0 upgraded, 1 newly installed, 0 to remove and 182 not upgraded.
2 not fully installed or removed.
Need to get 257 kB of archives.
After this operation, 1,905 kB of additional disk space will be used.
Do you want to continue? [Y/n]
Get:1 http://cn.archive.ubuntu.com/ubuntu bionic/universe amd64 libmecab2 amd64 0.996-5 [257 kB]
Fetched 257 kB in 5s (54.1 kB/s)
Selecting previously unselected package libmecab2:amd64.
(Reading database ... 119456 files and directories currently installed.)
Preparing to unpack .../libmecab2_0.996-5_amd64.deb ...
Unpacking libmecab2:amd64 (0.996-5) ...
Setting up libmecab2:amd64 (0.996-5) ...
Setting up mysql-community-server (5.7.37-1ubuntu18.04) ...
update-alternatives: using /etc/mysql/mysql.cnf to provide /etc/mysql/my.cnf (my.cnf) in auto mode
Created symlink /etc/systemd/system/multi-user.target.wants/mysql.service → /lib/systemd/system/mysql.service.
Setting up mysql-server (5.7.37-1ubuntu18.04) ...
Processing triggers for libc-bin (2.27-3ubuntu1.4) ...
Processing triggers for ureadahead (0.100.0-21) ...
Processing triggers for systemd (237-3ubuntu10.52) ...
xuej@xuej-VirtualBox:~/Downloads$ sudo dpkg -i mysql-{common,community-client,client,community-server,server}_*.deb
(Reading database ... 119462 files and directories currently installed.)
Preparing to unpack mysql-common_5.7.37-1ubuntu18.04_amd64.deb ...
Unpacking mysql-common (5.7.37-1ubuntu18.04) over (5.7.37-1ubuntu18.04) ...
Preparing to unpack mysql-community-client_5.7.37-1ubuntu18.04_amd64.deb ...
Unpacking mysql-community-client (5.7.37-1ubuntu18.04) over (5.7.37-1ubuntu18.04) ...
Preparing to unpack mysql-client_5.7.37-1ubuntu18.04_amd64.deb ...
Unpacking mysql-client (5.7.37-1ubuntu18.04) over (5.7.37-1ubuntu18.04) ...
Preparing to unpack mysql-community-server_5.7.37-1ubuntu18.04_amd64.deb ...
.
Unpacking mysql-community-server (5.7.37-1ubuntu18.04) over (5.7.37-1ubuntu18.04) ...
Preparing to unpack mysql-server_5.7.37-1ubuntu18.04_amd64.deb ...
Unpacking mysql-server (5.7.37-1ubuntu18.04) over (5.7.37-1ubuntu18.04) ...
Setting up mysql-common (5.7.37-1ubuntu18.04) ...
Setting up mysql-community-client (5.7.37-1ubuntu18.04) ...
Setting up mysql-client (5.7.37-1ubuntu18.04) ...
Setting up mysql-community-server (5.7.37-1ubuntu18.04) ...
Setting up mysql-server (5.7.37-1ubuntu18.04) ...
Processing triggers for man-db (2.8.3-2ubuntu0.1) ...
Processing triggers for ureadahead (0.100.0-21) ...
Processing triggers for systemd (237-3ubuntu10.52) ...
配置MySQL Replication
二进制日志文件位置
xuej@mysql-master:~$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
[sudo] password for xuej:
# Copyright (c) 2014, 2021, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation. The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# The MySQL Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
bind-address = 0.0.0.0
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-bin=mysql-bin
server-id=49
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
"/etc/mysql/mysql.conf.d/mysqld.cnf" 41L, 1646C 41,12 All
xuej@mysql-master:~$ reboot
xuej@mysql-master:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.37-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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> CREATE USER 'repl'@'%' IDENTIFIED BY 'Passw0rd';
Query OK, 0 rows affected (0.01 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 595 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
xuej@mysql-master:~$ mysqldump --user root --password Passw0rd --all-databases --master-data > dbdump.db
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
xuej@mysql-repl0:~$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
[sudo] password for xuej:
# Copyright (c) 2014, 2021, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation. The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# The MySQL Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
bind-address = 0.0.0.0
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=50
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
"/etc/mysql/mysql.conf.d/mysqld.cnf" 40L, 1628C 40,1 All
xuej@mysql-repl0:~$ reboot
xuej@mysql-repl0:~$ mysql -uroot -p
mysql> CHANGE MASTER TO MASTER_HOST='192.168.246.49', MASTER_USER='repl', MASTER_PASSWORD='Passw0rd', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=595;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
#######################################################
#If you do not have a snapshot of a database to import#
#######################################################
xuej@mysql-repl0:~$ sudo service mysql restart
[sudo] password for xuej:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.246.49
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 595
Relay_Log_File: mysql-repl0-relay-bin.000003
Relay_Log_Pos: 320
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: 595
Relay_Log_Space: 533
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: 49
Master_UUID: f626c919-4ed6-4bf8-bce9-f247cf52bbfb
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 more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
xuej@mysql-master:~$ mysql -uroot -p
mysql> CREATE DATABASE test; USE test; CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL); INSERT INTO t1 VALUES (1, 'Luis'), (2, 'Mark');
Query OK, 1 row affected (0.01 sec)
Database changed
Query OK, 0 rows affected (0.02 sec)
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
xuej@mysql-repl0:~$ mysql -uroot -p
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.01 sec)
mysql> USE test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
| 2 | Mark |
+----+------+
2 rows in set (0.00 sec)
################################################
#If you have a snapshot of a database to import#
################################################
xuej@mysql-master:~$ mysql -uroot -p
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 1219 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
xuej@mysql-master:~$ mysqldump --user=root --password=Passw0rd --databases test --master-data > dbdump.db
mysqldump: [Warning] Using a password on the command line interface can be insecure.
xuej@mysql-master:~$ vi dbdump.db
-- MySQL dump 10.13 Distrib 5.7.37, for Linux (x86_64)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.7.37-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1219;
--
-- Current Database: `test`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `test` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `test`;
--
-- Table structure for table `t1`
--
DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
`c1` int(11) NOT NULL,
`c2` text NOT NULL,
PRIMARY KEY (`c1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `t1`
--
LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1,'Luis'),(2,'Mark');
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
"dbdump.db" 65L, 2106C 1,1 Top
xuej@mysql-master:~$ scp dbdump.db xuej@192.168.246.51:/home/xuej
The authenticity of host '192.168.246.51 (192.168.246.51)' can't be established.
ECDSA key fingerprint is SHA256:ZCtf3RNeE7VhyrDVZ/GwW7hxcbSFsYztcUnksnwnXkI.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.246.51' (ECDSA) to the list of known hosts.
xuej@192.168.246.51's password:
dbdump.db
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
xuej@mysql-repl1:~$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
# Copyright (c) 2014, 2021, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation. The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# The MySQL Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
bind-address = 0.0.0.0
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=51
skip-slave-start=ON
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
"/etc/mysql/mysql.conf.d/mysqld.cnf" 41L, 1648C 41,19 All
xuej@mysql-repl1:~$ sudo service mysql restart
xuej@mysql-repl1:~$ mysql -uroot -p
mysql> CHANGE MASTER TO MASTER_HOST='192.168.246.49', MASTER_USER='repl', MASTER_PASSWORD='Passw0rd', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1219;
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.246.49
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1219
Relay_Log_File: mysql-repl1-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
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: 1219
Relay_Log_Space: 154
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: NULL
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: 0
Master_UUID:
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql>
xuej@mysql-repl1:~$ mysql -uroot -p < dbdump.db
Enter password:
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| t1 |
+----------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
| 2 | Mark |
+----+------+
2 rows in set (0.00 sec)
mysql> START SLAVE;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.246.49
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1219
Relay_Log_File: mysql-repl1-relay-bin.000002
Relay_Log_Pos: 320
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: 1219
Relay_Log_Space: 533
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: 49
Master_UUID: f626c919-4ed6-4bf8-bce9-f247cf52bbfb
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 more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
xuej@mysql-master:~$ mysql -uroot -p
mysql> INSERT INTO t1 VALUES (3, 'Nice');
Query OK, 1 row affected (0.01 sec)
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 1480 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
xuej@mysql-repl0:~$ mysql -uroot -p
mysql> SELECT * FROM t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
| 2 | Mark |
| 3 | Nice |
+----+------+
3 rows in set (0.00 sec)
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.246.49
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1480
Relay_Log_File: mysql-repl0-relay-bin.000003
Relay_Log_Pos: 1205
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: 1480
Relay_Log_Space: 1418
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: 49
Master_UUID: f626c919-4ed6-4bf8-bce9-f247cf52bbfb
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 more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
xuej@mysql-repl1:~$ mysql -uroot -p
mysql> SELECT * FROM t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | Luis |
| 2 | Mark |
| 3 | Nice |
+----+------+
3 rows in set (0.00 sec)
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.246.49
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1480
Relay_Log_File: mysql-repl1-relay-bin.000002
Relay_Log_Pos: 581
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: 1480
Relay_Log_Space: 794
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: 49
Master_UUID: f626c919-4ed6-4bf8-bce9-f247cf52bbfb
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 more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
GTID事务（TODO）
Replication Format：Statement Based、Row Based。为了获得最佳效果，建议使用Row Based。
配置MySQL Group Replication
mysql-n1的配置如下所示：
xuej@xuej-VirtualBox:~$ uuidgen
72c81f66-68a2-4592-8eb8-52457877171f
xuej@xuej-VirtualBox:~$ sudo vi /var/lib/mysql/auto.cnf
[sudo] password for xuej:
[auto]
server-uuid=72c81f66-68a2-4592-8eb8-52457877171f
xuej@xuej-VirtualBox:~$ sudo hostnamectl --transient --static --pretty set-hostname db1
xuej@xuej-VirtualBox:~$ sudo vi /etc/hosts
127.0.0.1 localhost
127.0.1.1 xuej-VirtualBox
192.168.246.43 db1
192.168.246.44 db1-repl0
192.168.246.45 db1-repl1
# The following lines are desirable for IPv6 capable hosts
::1 ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
xuej@xuej-VirtualBox:~$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
# Copyright (c) 2014, 2021, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation. The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# The MySQL Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
# bind-address = 127.0.0.1
bind-address = 0.0.0.0
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#
# Disable other storage engines
#
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
#
# Replication configuration parameters
#
server_id=43
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
#
# Group Replication configuration
#
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="62a8920f-7a88-4998-9c46-cce7b8f99cfd"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "db1:33061"
loose-group_replication_group_seeds= "db1:33061,db1-repl0:33061,db1-repl1:33061"
loose-group_replication_bootstrap_group= off
xuej@xuej-VirtualBox:~$ sudo service mysql restart
xuej@db1:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.37-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER repl@'%' IDENTIFIED BY 'Passw0rd'; GRANT REPLICATION SLAVE ON *.* TO repl@'%'; FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='Passw0rd' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.02 sec)
mysql> SET GLOBAL group_replication_bootstrap_group=ON; START GROUP_REPLICATION; SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (2.04 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 72c81f66-68a2-4592-8eb8-52457877171f | db1 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.01 sec)
mysql> CREATE DATABASE test; USE test; CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 TEXT NOT NULL); INSERT INTO t1 VALUES (1, 'Luis'), (2, 'Mark');
Query OK, 1 row affected (0.01 sec)
Database changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000001 | 2906 | | | 62a8920f-7a88-4998-9c46-cce7b8f99cfd:1-9 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW STATUS LIKE 'group_replication_primary_member';
+----------------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 72c81f66-68a2-4592-8eb8-52457877171f |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'group_replication_group_name';
+------------------------------+--------------------------------------+
| Variable_name | Value |
+------------------------------+--------------------------------------+
| group_replication_group_name | 62a8920f-7a88-4998-9c46-cce7b8f99cfd |
+------------------------------+--------------------------------------+
1 row in set (0.01 sec)
############################################################
# user account for develop #
############################################################
mysql> create user 'root'@'%' identified by 'Passw0rd'; grant all on *.* to 'root'@'%';
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for current_user;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show grants for current_user();
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> show grants for root;
+-------------------------------------------+
| Grants for root@% |
+-------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' |
+-------------------------------------------+
1 row in set (0.00 sec)
mysql> show create user root;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for root@% |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*DDFB542AA0BD1D251995D81AEBEB96DEEAD1132F' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql-n2的配置如下所示：
xuej@xuej-VirtualBox:~$ uuidgen
3eef4839-dafd-43db-8716-eb4c2163c0b0
xuej@xuej-VirtualBox:~$ sudo vi /var/lib/mysql/auto.cnf
[auto]
server-uuid=3eef4839-dafd-43db-8716-eb4c2163c0b0
xuej@xuej-VirtualBox:~$ sudo vi /etc/hosts
127.0.0.1 localhost
127.0.1.1 xuej-VirtualBox
192.168.246.43 db1
192.168.246.44 db1-repl0
192.168.246.45 db1-repl1
# The following lines are desirable for IPv6 capable hosts
::1 ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
xuej@xuej-VirtualBox:~$ sudo hostnamectl --transient --static --pretty set-hostname db1-repl0
xuej@xuej-VirtualBox:~$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
# Copyright (c) 2014, 2021, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation. The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# The MySQL Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
# bind-address = 127.0.0.1
bind-address = 0.0.0.0
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#
# Disable other storage engines
#
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
#
# Replication configuration parameters
#
server_id=44
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
#
# Group Replication configuration
#
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="62a8920f-7a88-4998-9c46-cce7b8f99cfd"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "db1-repl0:33061"
loose-group_replication_group_seeds= "db1:33061,db1-repl0:33061,db1-repl1:33061"
loose-group_replication_bootstrap_group= off
xuej@xuej-VirtualBox:~$ sudo service mysql restart
xuej@db1-repl0:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.37-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER repl@'%' IDENTIFIED BY 'Passw0rd'; GRANT REPLICATION SLAVE ON *.* TO repl@'%'; FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='Passw0rd' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.02 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (5.71 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 3eef4839-dafd-43db-8716-eb4c2163c0b0 | db1-repl0 | 3306 | ONLINE |
| group_replication_applier | 72c81f66-68a2-4592-8eb8-52457877171f | db1 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000001 | 2897 | | | 62a8920f-7a88-4998-9c46-cce7b8f99cfd:1-9 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW STATUS LIKE 'group_replication_primary_member';
+----------------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 72c81f66-68a2-4592-8eb8-52457877171f |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)
mysql-n3的配置如下所示：
xuej@xuej-VirtualBox:~$ uuidgen
bd1086f7-bb9c-46b7-b073-809ba6e043aa
xuej@xuej-VirtualBox:~$ sudo vi /var/lib/mysql/auto.cnf
[auto]
server-uuid=bd1086f7-bb9c-46b7-b073-809ba6e043aa
xuej@xuej-VirtualBox:~$ sudo vi /etc/hosts
127.0.0.1 localhost
127.0.1.1 xuej-VirtualBox
192.168.246.43 db1
192.168.246.44 db1-repl0
192.168.246.45 db1-repl1
# The following lines are desirable for IPv6 capable hosts
::1 ip6-localhost ip6-loopback
fe00::0 ip6-localnet
ff00::0 ip6-mcastprefix
ff02::1 ip6-allnodes
ff02::2 ip6-allrouters
xuej@xuej-VirtualBox:~$ sudo hostnamectl --transient --static --pretty set-hostname db1-repl1
xuej@xuej-VirtualBox:~$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
# Copyright (c) 2014, 2021, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation. The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# The MySQL Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
# bind-address = 127.0.0.1
bind-address = 0.0.0.0
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
#
# Disable other storage engines
#
disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY"
#
# Replication configuration parameters
#
server_id=45
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
#
# Group Replication configuration
#
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="62a8920f-7a88-4998-9c46-cce7b8f99cfd"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "db1-repl1:33061"
loose-group_replication_group_seeds= "db1:33061,db1-repl0:33061,db1-repl1:33061"
loose-group_replication_bootstrap_group= off
xuej@xuej-VirtualBox:~$ sudo service mysql restart
xuej@db1-repl1:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.37-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER repl@'%' IDENTIFIED BY 'Passw0rd'; GRANT REPLICATION SLAVE ON *.* TO repl@'%'; FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='Passw0rd' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)
mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.01 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (3.04 sec)
mysql> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 3eef4839-dafd-43db-8716-eb4c2163c0b0 | db1-repl0 | 3306 | ONLINE |
| group_replication_applier | 72c81f66-68a2-4592-8eb8-52457877171f | db1 | 3306 | ONLINE |
| group_replication_applier | bd1086f7-bb9c-46b7-b073-809ba6e043aa | db1-repl1 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.01 sec)
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000001 | 2897 | | | 62a8920f-7a88-4998-9c46-cce7b8f99cfd:1-9 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> SHOW STATUS LIKE 'group_replication_primary_member';
+----------------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 72c81f66-68a2-4592-8eb8-52457877171f |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)