另一個MySQL DBA

2024年2月25日星期日

MariaDB 加密（静态数据）

下面是一个简单的示例，显示了 MariaDB 加密的几个选项。

您必须考虑要加密的内容。数据（传输中的数据）或实例中的数据（静态数据）的通信。

本文将重点介绍使用在 Amazon Linux 上运行的 AWS 免费套餐节点的静态数据选项。我将在 2 个不同的实例上使用全局数据库来显示通过加密更新当前表以及将自动加密的新加载表。

首先，我们将从安装开始......对于这个演示来说快速而简单。

# yum -y install mariadb105-server
# vi /etc/my.cnf.d/mariadb-server.cnf
# cat /etc/my.cnf.d/mariadb-server.cnf | grep server_id
server_id=100

# cat /etc/my.cnf.d/mariadb-server.cnf | grep server_id
server_id=200
# systemctl start mariadb.service
# mysql
MariaDB [(none)]> status
--------------
mysql Ver 15.1 Distrib 10.5.23-MariaDB, for Linux (x86_64) using EditLine wrapper
Connection id: 3
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.5.23-MariaDB MariaDB Server
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 27 sec
MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 100 |
+-------------+
MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 200 |
+-------------+

我们将在实例 server_id 100 上加载全局数据库。

# mysql < world.sql
# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.5.23-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

现在我们可以看到两个实例当前都没有使用加密。

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 100 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;
Empty set (0.000 sec)

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 200 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;
Empty set (0.000 sec)

现在，我将在两个系统上设置随机密钥并对其进行加密。

# mkdir /etc/mysql/

# mkdir /etc/mysql/encryption/
# (echo -n "1;" ; openssl rand -hex 32 ) | sudo tee -a /etc/mysql/encryption/keyfile
# (echo -n "2;" ; openssl rand -hex 32 ) | sudo tee -a /etc/mysql/encryption/keyfile
# (echo -n "100;" ; openssl rand -hex 32 ) | sudo tee -a /etc/mysql/encryption/keyfile

# cat /etc/mysql/encryption/keyfile
1;466139b487d5735b536a10502b0607d2b96dfa58a7f95ce4847d98ef585af8b5
2;a0f533c2e459dc64e0aeb1f0f8c9dfb496571e71001ca60fac35c8bbc6361096
100;4b4bfd61af39d57e068385cf1a023bdfc5972ed414a24167067ca5256fc314e6

# cat /etc/mysql/encryption/keyfile
1;f8b1b250d3bf7159e2abd15be146367415a80d3781bbcf7d96b26640d7efdc8a
2;c7eaccedd0ef561f0c08d461abfd651947230b08c71aec67c6064bbedb6408ec
100;90475521b6eb6be3c1ec02770f3e8f80b34130f37d25c89b51cf10ec6a7c5bb6

openssl rand -hex 128 > /etc/mysql/encryption/keyfile.key
openssl enc -aes-256-cbc -md sha1 -pass file:/etc/mysql/encryption/keyfile.key -in /etc/mysql/encryption/keyfile -out /etc/mysql/encryption/keyfile.enc

ls -ltr /etc/mysql/encryption/
total 12
-rw-r--r--. 1 root root 203 Feb 24 23:39 keyfile
-rw-r--r--. 1 root root 257 Feb 24 23:40 keyfile.key
-rw-r--r--. 1 root root 224 Feb 24 23:41 keyfile.enc

现在我们可以设置 cnf 文件来启用插件以及加密选项.

# vi /etc/my.cnf.d/mariadb-server.cnf
[mariadb]
## File Key Management
plugin_load_add = file_key_management
loose_file_key_management_filename = /etc/mysql/encryption/keyfile
loose_file_key_management_filename = /etc/mysql/encryption/keyfile.enc
loose_file_key_management_filekey = FILE:/etc/mysql/encryption/keyfile.key
loose_file_key_management_encryption_algorithm = AES_CBC

## InnoDB/XtraDB Encryption Setup
innodb_encrypt_log = ON
innodb_encrypt_temporary_tables=ON
innodb_encryption_threads=4
innodb_encrypt_tables=ON
innodb_default_encryption_key_id=1

## Aria Encryption Setup
aria_encrypt_tables = ON

## Temp & Log Encryption
encrypt-tmp-disk-tables = 1
encrypt-tmp-files = 1
encrypt_binlog = ON

重新启动 MariaDB,以便我们可以检查当前状态.

systemctl restart mariadb.service

mysql

MariaDB [(none)]> select @@server_id;

+-------------+

| @@server_id |

+-------------+

| 100 |

+-------------+

1 row in set (0.001 sec)

MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;

+----------------------------+-------------------+----------------+

| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |

+----------------------------+-------------------+----------------+

| innodb_system | 1 | 1 |

| mysql/innodb_table_stats | 1 | 1 |

| mysql/innodb_index_stats | 1 | 1 |

| mysql/transaction_registry | 1 | 1 |

| mysql/gtid_slave_pos | 1 | 1 |

| world/city | 1 | 1 |

| world/country | 1 | 1 |

| world/countrylanguage | 1 | 1 |

+----------------------------+-------------------+----------------+

8 rows in set (0.000 sec)

MariaDB [(none)]> select @@server_id;

+-------------+

| @@server_id |

+-------------+

| 200 |

+-------------+

1 row in set (0.000 sec)

MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;

+----------------------------+-------------------+----------------+

| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |

+----------------------------+-------------------+----------------+

| innodb_system | 1 | 1 |

| mysql/innodb_index_stats | 1 | 1 |

| mysql/gtid_slave_pos | 1 | 1 |

| mysql/innodb_table_stats | 1 | 1 |

| mysql/transaction_registry | 1 | 1 |

+----------------------------+-------------------+----------------+

5 rows in set (0.000 sec)

同时将世界数据上传到实例server_id 200。

# mysql < world.sql
# mysql
MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 200 |
+-------------+
1 row in set (0.000 sec)
MariaDB [(none)]> SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID FROM information_schema.INNODB_TABLESPACES_ENCRYPTION ;
+----------------------------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------------------------+-------------------+----------------+
| innodb_system | 1 | 1 |
| mysql/innodb_index_stats | 1 | 1 |
| mysql/gtid_slave_pos | 1 | 1 |
| mysql/innodb_table_stats | 1 | 1 |
| mysql/transaction_registry | 1 | 1 |
| world/city | 1 | 1 |
| world/country | 1 | 1 |
| world/countrylanguage | 1 | 1 |
+----------------------------+-------------------+----------------+
8 rows in set (0.000 sec)

根据 information_schema.INNODB_TABLESPACES_ENCRYPTION 我们现在已加密。但是，它们不会在架构级别显示它。虽然他们说如果它出现在 INNODB_TABLESPACES_ENCRYPTION 表中，它就会被加密，但我更愿意确定并在表和模式中看到它。

MariaDB [(none)]> show create table world.city\G

*************************** 1. row ***************************
Table: city
Create Table: CREATE TABLE `city` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.001 sec)

到目前为止，您可以看到在重新启动或加载架构和数据后，两个实例都已计入 INNODB_TABLESPACES_ENCRYPTION 架构中。

所以...对表格进行一些修改将会有所帮助...

MariaDB [world]> ALTER TABLE city ENCRYPTED=Yes ENCRYPTION_KEY_ID=1;
Query OK, 0 rows affected (0.074 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [world]> ALTER TABLE country ENCRYPTED=Yes ENCRYPTION_KEY_ID=1;
Query OK, 0 rows affected (0.031 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [world]> ALTER TABLE countrylanguage ENCRYPTED=Yes ENCRYPTION_KEY_ID=1;
Query OK, 0 rows affected (0.033 sec)
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [world]> show create table city\G

*************************** 1. row ***************************

Table: city

Create Table: CREATE TABLE `city` (

`ID` int(11) NOT NULL AUTO_INCREMENT,

`Name` char(35) NOT NULL DEFAULT '',

`CountryCode` char(3) NOT NULL DEFAULT '',

`District` char(20) NOT NULL DEFAULT '',

`Population` int(11) NOT NULL DEFAULT 0,

PRIMARY KEY (`ID`),

KEY `CountryCode` (`CountryCode`),

CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)

) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci `ENCRYPTED`=Yes `ENCRYPTION_KEY_ID`=1

1 row in set (0.000 sec)

这很简单等等。到目前为止...现在我们需要启用二进制日志并检查更多信息。

vi /etc/my.cnf.d/mariadb-server.cnf
log_bin=demo

cat /etc/my.cnf.d/mariadb-server.cnf | grep log_bin
log_bin=demo

# systemctl restart mariadb.service

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 100 |
+-------------+
1 row in set (0.000 sec)

MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
File: demo.000001
Position: 363
Binlog_Do_DB:
Binlog_Ignore_DB:

MariaDB [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 200 |
+-------------+
1 row in set (0.000 sec)

MariaDB [(none)]> show master status\G
*************************** 1. row ***************************
File: demo.000001
Position: 363
Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.000 sec)

通过查看二进制日志来检查......

mariadb-binlog--base64-output=DECODE-ROWS --verbose demo.000001

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#240225 0:06:06 server id 100 end_log_pos 256 CRC32 0x04ce3741 Start: binlog v 4, server v 10.5.23-MariaDB-log created 240225 0:06:06 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

# at 256

# Encryption scheme: 1, key_version: 1, nonce: eb7991b210f3f4d2f7f21537

# The rest of the binlog is encrypted!

ERROR: Error in Log_event::read_log_event(): 'Event decryption failure', data_len: 2400465656, event_type: 240

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

很高兴看到它说它现在正在加密。

MariaDB [world]> create table city2 like city;
Query OK, 0 rows affected (0.013 sec)

MariaDB [world]> insert into city2 select * from city;
Query OK, 4079 rows affected (0.078 sec)
Records: 4079 Duplicates: 0 Warnings: 0

MariaDB [world]> show create table city2\G
*************************** 1. row ***************************
Table: city2
Create Table: CREATE TABLE `city2` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT 0,
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci `ENCRYPTED`=Yes `ENCRYPTION_KEY_ID`=1
1 row in set (0.000 sec)

I want to see these transactions though in the binlog.. how? You can use mariadb_binlog along with --read-from-remote-server to be able to see the data in the logs...

mariadb-binlog --base64-output=DECODE-ROWS --verbose --read-from-remote-server demo.000001 | more
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#240225 0:06:06 server id 100 end_log_pos 256 CRC32 0x04ce3741 Start: binlog v 4, server v 10.5.23-MariaDB-log created 240225 0:06:06 at startup
ROLLBACK/*!*/;
# at 256
#240225 0:06:06 server id 100 end_log_pos 296 CRC32 0x0c89f3bb Ignorable
# Ignorable event type 164 (Start_encryption)
# at 296
#240225 0:06:06 server id 100 end_log_pos 325 CRC32 0x535776a2 Gtid list []
# at 325
#240225 0:06:06 server id 100 end_log_pos 363 CRC32 0x2ac4a61b Binlog checkpoint demo.000001
# at 363
#240225 0:09:40 server id 100 end_log_pos 405 CRC32 0x93e10dc4 GTID 0-100-1 ddl
/*!100101 SET @@session.skip_parallel_replication=0*//*!*/;
/*!100001 SET @@session.gtid_domain_id=0*//*!*/;
/*!100001 SET @@session.server_id=100*//*!*/;
/*!100001 SET @@session.gtid_seq_no=1*//*!*/;
# at 405
#240225 0:09:40 server id 100 end_log_pos 501 CRC32 0x39269040 Query thread_id=5 exec_time=0 error_code=0
use `world`/*!*/;
SET TIMESTAMP=1708819780/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1, @@session.check_constraint_checks=1, @@session.sql_if_exists=0, @@session.explicit_defaults_
for_timestamp=0/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=utf8,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create table city2 like city
/*!*/;
# at 501
#240225 0:09:49 server id 100 end_log_pos 543 CRC32 0xde82b753 GTID 0-100-2 trans
/*!100001 SET @@session.gtid_seq_no=2*//*!*/;
START TRANSACTION
/*!*/;
# at 543
# at 602
#240225 0:09:49 server id 100 end_log_pos 602 CRC32 0x05bbb9e6 Annotate_rows:
#Q> insert into city2 select * from city
#240225 0:09:49 server id 100 end_log_pos 661 CRC32 0x9e0b4e0d Table_map: `world`.`city2` mapped to number 21
# at 661

希望这至少可以让你开始......

资源：

https://mariadb.com/kb/en/securing-mariadb-encryption/

2020年11月12日星期四

使用您的FRM文件获取Schema，然后导入idb文件。

总的来说，这是一个您永远不必做的主题...为什么？因为您创建的备份是正确的...您已经测试并知道了备份的工作原理，所以您可以仅还原那些备份并获取丢失的架构和相关数据...

但是，角落办公室中的一个实例..您从来没有进行设置..那不是那么重要...只是崩溃了，现在您认为了您实际上是如何使用它的...

一切都没有丢失。

MySQL不久前发布了他们的MySQL实用程序，之后被MySQL Shell所取代。

mysqlfrm仍然非常方便，但是当需要通过快速简单的命令从FRM文件中提取模式时，这是一个简单的安装。


           mysqlfrm --diagnostic city.frm

          # WARNING: Cannot generate character set or collation names 
without the --server option. 
          # CAUTION: The diagnostic mode is a best-effort parse of the 
.frm file. As such, it may not identify all of the components of the 
table correctly. This is especially true for damaged files. It will also
 not read the default values for the columns and the resulting statement
 may not be syntactically correct.

         # Reading .frm file for city.frm:

         # The .frm file is a TABLE.

         # CREATE TABLE Statement:

         

         CREATE TABLE `city` (

           `ID` int(11) NOT NULL AUTO_INCREMENT,

           `Name` char(160) DEFAULT NULL,

           `CountryCode` char(12) NOT NULL,

           `District` char(80) NOT NULL,

           `Population` int(11) NOT NULL,

         PRIMARY KEY `PRIMARY` (`ID`),

         KEY `CountryCode` (`CountryCode`),

         KEY `popkey` (`Population`)

         ) ENGINE=InnoDB;

         

         #...done.

因此，现在您拥有丢失的架构...重建数据库或表。对于这个例子的目的，我会说，我们刚刚从世界DB丢失了城市的数据。

$ cp city.ibd / tmp /


$ cp city.ibd /tmp/ 

mysql> LOCK TABLES city WRITE;

mysql> ALTER TABLE city DISCARD TABLESPACE;

 

cp  city.ibd /edb/local/mysql/data/rundeck/

chown tmdba:dba /edb/local/mysql/data/rundeck/city.ibd



mysql> ALTER TABLE city IMPORT TABLESPACE;

mysql> UNLOCK TABLES;

mysql> SELECT COUNT(*) FROM city;

2020年9月22日星期二

MySQL mysql_config_editor与期望

这只是一条注释，旨在帮助可能会在其自动化工具中使用mysql_config_editor命令的任何人。

mysql_config_editor不使用密码参数，因此在尝试使用mysql_config_editor的.my.cnf文件中设置密码之前可能拥有的自动化工具会失败。

尽管使用期望工具，这是可能的并且非常简单。


 yum -y install expect

它也适用于apt-get。

因此，在此示例中，我将显示一个简单的bash脚本版本。

1 ..我的登录路径不起作用...


mysql --login-path=local
ERROR 1045 (28000): Access denied for user

设置这个与期望

您可以通过bash脚本执行此操作。


expect <<EOD
spawn mysql_config_editor set --login-path=local --host=localhost --user=root --password 
expect "password"
send  -- "<PASSWORD>\r"
interact
EOD

现在可以了...


mysql --login-path=local
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1002

2020年3月16日星期一

MySQL和Docker ...一個簡單的設置

MySQL和Docker ...不是新概念，人們已經使用Docker已有一段時間了。對於剛開始為此發展的人來說，可能會有一些障礙。

儘管MySQL在本地運行良好，但如果要在不同版本的MySQL上測試代碼，則很容易擁有多個版本是很好的。

多年來，一種選擇當然是Giuseppe Maxia的https://mysqlsandbox.net/ 。這是一個非常有效的解決方案，能夠啟動多個實例並測試複製等。

在跨不同版本的MySQL進行測試時，Docker現在也是另一個經常使用的場景。下面僅介紹一些步驟，以輕鬆安裝多個版本。我使用OSX，因此這些示例適用於OSX。

您需要Docker才能啟動，當然，Docker Desktop是一個方便的工具，使您能夠輕鬆獲得訪問權限。

設置好Docker之後，就可以為MySQL準備好環境了。

在這裡，我創建了一個Docker文件夾，其中包含MySQL數據目錄，配置文件以及mysql-files目錄（如果需要）。

mkdir ~/Docker ;

mkdir ~/Docker/mysql_data;

mkdir ~/Docker/mysql-files;

mkdir ~/Docker/cnf;

現在在mysql_data

cd ~/Docker/mysql_data;

mkdir 8.0;

mkdir 5.7;

mkdir 5.6;

mkdir 5.5;

現在，我為該示例設置簡單的cnf文件。要注意的主要事情是綁定地址。這樣做是為了確保它對我們開放，使其可以在docker之外訪問MySQL。您還可以注意到，對於每個MySQL docker實例，這些文件都可以用來設置其他配置信息。

cd ~/Docker/cnf;

cat my.8.0.cnf

[mysqld]

pid-file = /var/run/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.sock

datadir = /var/lib/mysql

secure-file-priv= /var/lib/mysql-files

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

bind-address = 0.0.0.0

port=3306

server-id=80

# Custom config should go here

!includedir /etc/mysql/conf.d/

cat my.5.7.cnf

[mysqld]

bind-address = 0.0.0.0

server-id=57

max_allowed_packet=32M

$ cat my.5.6.cnf

[mysqld]

bind-address = 0.0.0.0

server-id=56

$ cat my.5.5.cnf

[mysqld]

bind-address = 0.0.0.0

server-id=55

好的，現在我們已經設置了配置文件，我們需要構建docker。生成命令要注意的幾件事。

--name我們為docker設置了一個命名引用。

在這裡，我們將配置文件，數據目錄和mysql-files目錄映射到docker。這使我們可以輕鬆調整my.cnf文件等。

-v〜/ Docker / cnf / my.8.0.cnf：/etc/mysql/my.cnf

-v〜/ Docker / mysql_data / 8.0：/ var / lib / mysql

-v〜/ Docker / mysql文件：/ var / lib / mysql文件

我們希望能夠在docker之外訪問這些MySQL實例，因此我們需要相應地發布和映射端口。

-p 3306：3306這意味著3306在docker內部的3306本地

-p 3307：3306這意味著3307在docker內部3306本地

-p 3308：3306這意味著3308在docker內部3306本地

-p 3309：3306這意味著3309在docker內部3306本地

然後，我們還要傳遞幾個環境變量。

-e MYSQL_ROOT_HOST =％-e MYSQL_ROOT_PASSWORD = <在此處設置密碼>

所以放在一起...

docker run --restart always --name 
mysql8.0   -v ~/Docker/cnf/my.8.0.cnf:/etc/mysql/my.cnf -v 
~/Docker/mysql_data/8.0:/var/lib/mysql -v 
~/Docker/mysql-files:/var/lib/mysql-files -p  3306:3306 -d -e 
MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=<set a password here> 
mysql:8.0

docker
 run --restart always --name mysql5.7   -v 
~/Docker/cnf/my.5.7.cnf:/etc/mysql/my.cnf -v 
~/Docker/mysql_data/5.7:/var/lib/mysql -v 
~/Docker/mysql-files:/var/lib/mysql-files -p  3307:3306 -d -e 
MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=<set a password here> 
mysql:5.7

docker
 run --restart always --name mysql5.6   -v 
~/Docker/cnf/my.5.6.cnf:/etc/mysql/my.cnf -v 
~/Docker/mysql_data/5.6:/var/lib/mysql -v 
~/Docker/mysql-files:/var/lib/mysql-files -p  3308:3306 -d -e 
MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=<set a password here> 
mysql:5.6

docker
 run --restart always --name mysql5.5   -v 
~/Docker/cnf/my.5.5.cnf:/etc/mysql/my.cnf -v 
~/Docker/mysql_data/5.5:/var/lib/mysql -v 
~/Docker/mysql-files:/var/lib/mysql-files -p  3309:3306 -d -e 
MYSQL_ROOT_HOST=% -e MYSQL_ROOT_PASSWORD=<set a password here> 
mysql:5.5

每次執行以上命令後，您都應該返回一個ID。

示例：3cb07d7c21476fbf298648986208f3429ec664167d8eef7fed17bf9ee3ce6316

您可以通過Docker桌面輕鬆啟動/重新啟動和訪問每個Docker終端，或者只需記下相關ID即可通過終端執行。

Docker桌面還會顯示您傳遞的所有變量，以便您進行驗證。

當然，您也可以在此處訪問CLI，輕鬆停止或啟動或銷毀它。

 $ docker exec -it 3cb07d7c21476fbf298648986208f3429ec664167d8eef7fed17bf9ee3ce6316 /bin/sh; exit 

 # mysql -p 

如果Docker容器已經在運行，您現在可以通過localhost終端訪問MySQL。

$ mysql --host=localhost --protocol=tcp --port=3306 -p -u root

現在，如果您遇到任何訪問問題，請記住確保MySQL帳戶正確並且您的端口和映射正確。

在“讀取初始通信數據包”時失去與MySQL服務器的連接
錯誤1045（28000）：用戶'root'@'192.168.0.5'的訪問被拒絕（使用密碼：是）

現在，您可以看到所有設備均已啟動並且可用，並且服務器ID與我們為每個cnf文件的eariler設置的設置匹配。

$ mysql --host=localhost --protocol=tcp --port=3306 -e "Select @@hostname, @@version, @@server_id " 
 +--------------+-----------+-------------+ 
 | @@hostname | @@version | @@server_id | 
 +--------------+-----------+-------------+ 
 | 58e9663afe8d | 8.0.19 | 80 | 
 +--------------+-----------+-------------+ 
 $ mysql --host=localhost --protocol=tcp --port=3307 -e "Select @@hostname, @@version, @@server_id " 
 +--------------+-----------+-------------+ 
 | @@hostname | @@version | @@server_id | 
 +--------------+-----------+-------------+ 
 | b240917f051a | 5.7.29 | 57 | 
 +--------------+-----------+-------------+ 
 $ mysql --host=localhost --protocol=tcp --port=3308 -e "Select @@hostname, @@version, @@server_id " 
 +--------------+-----------+-------------+ 
 | @@hostname | @@version | @@server_id | 
 +--------------+-----------+-------------+ 
 | b4653850cfe9 | 5.6.47 | 56 | 
 +--------------+-----------+-------------+ 
 $ mysql --host=localhost --protocol=tcp --port=3309 -e "Select @@hostname, @@version, @@server_id " 
 +--------------+-----------+-------------+ 
 | @@hostname | @@version | @@server_id | 
 +--------------+-----------+-------------+ 
 | 22e169004583 | 5.5.62 | 55 | 
 +--------------+-----------+-------------+

2019年7月13日星期六

MySQL如何恢復表空間

MySQL如何恢復表空間？

這不是新的信息，但我沒有多說，所以現在為那些需要它的人解決它。

如果您丟失了ibd文件......您將丟失數據。因此，如果您有一個可用的副本..或者即使您從另一個數據庫同步，您仍然可以導入它。什麼/你如何失去表空間？

這是一個恢復表空間的簡單示例。


 
 mysql> Create database demo; 
 
 mysql> use demo; 
 
 mysql> CREATE TABLE `demotable` ( 
 ->   `id` int(11) NOT NULL AUTO_INCREMENT, 
 ->   `dts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
 ->   PRIMARY KEY (`id`) 
 -> ) ENGINE=InnoDB;

現在我們存儲一些數據......


 mysql> INSERT INTO demotable (id) VALUES (NULL); 
 Query OK, 1 row affected (0.10 sec) 
 
 mysql> INSERT INTO demotable (id) VALUES (NULL); 
 Query OK, 1 row affected (0.08 sec) 
 
 mysql> SELECT * FROM demotable; 
 +----+---------------------+ 
 | id | dts                 | 
 +----+---------------------+ 
 |  1 | 2019-07-12 23:31:34 | 
 |  2 | 2019-07-12 23:31:35 | 
 +----+---------------------+ 
 2 rows in set (0.00 sec)

好的，現在讓我們打破它..


 # systemctl stop mysqld 
 # cd /var/lib/mysql/demo/ 
 # ls -ltr 
 total 80 
 -rw-r-----. 1 mysql mysql 114688 Jul 12 23:31 demotable.ibd 
 # mv demotable.ibd /tmp/ 
 
 # systemctl start mysqld 
 # mysql demo 
 
 mysql> show tables; 
 +----------------+ 
 | Tables_in_demo | 
 +----------------+ 
 | demotable      | 
 +----------------+ 
 1 row in set (0.00 sec) 
 
 mysql> desc demotable; 
 +-------+-----------+------+-----+-------------------+-----------------------------------------------+ 
 | Field | Type      | Null | Key | Default           | Extra                                         | 
 +-------+-----------+------+-----+-------------------+-----------------------------------------------+ 
 | id    | int(11)   | NO   | PRI | NULL              | auto_increment                                | 
 | dts   | timestamp | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | 
 +-------+-----------+------+-----+-------------------+-----------------------------------------------+ 
 2 rows in set (0.01 sec) 
 
 mysql> INSERT INTO demotable (id) VALUES (NULL); 
 ERROR 1812 (HY000): Tablespace is missing for table `demo`.`demotable`.

破損和丟失的表空間......現在我們可以恢復它了..


 demo]# cp /tmp/demotable.ibd . 
 
 mysql> ALTER TABLE demotable DISCARD TABLESPACE; 
 
 demo]# cp /tmp/demotable.ibd . 
 demo]# ls -ltr 
 total 112 
 -rw-r-----. 1 root root 114688 Jul 12 23:50 demotable.ibd 
 demo]# chown mysql:mysql demotable.ibd 
 demo]# mysql demo 
 mysql> ALTER TABLE demotable IMPORT TABLESPACE; 
 ERROR 1034 (HY000): Incorrect key file for table 'demotable'; try to repair it 
 
 mysql> REPAIR TABLE demotable; 
 +----------------+--------+----------+---------------------------------------------------------+ 
 | Table          | Op     | Msg_type | Msg_text                                                | 
 +----------------+--------+----------+---------------------------------------------------------+ 
 | demo.demotable | repair | note     | The storage engine for the table doesn't support repair | 
 +----------------+--------+----------+---------------------------------------------------------+

現在註意我們還有另一個錯誤..這通常與tmpdir可用的空間有關，而且無論如何修復都不適用於.ibd。


 mysql> select @@tmpdir; 
 +----------+ 
 | @@tmpdir | 
 +----------+ 
 | /tmp     | 
 +----------+ 
 
 # vi /etc/my.cnf 
 tmpdir=/var/lib/mysql-files/ 
 
 # systemctl restart mysqld 
 # mysql demo

OK只使用了mysql-files目錄。
現在我們可以再試一次。


 mysql> ALTER TABLE demotable IMPORT TABLESPACE; 
 Query OK, 0 rows affected, 1 warning (0.61 sec) 
 
 mysql>  INSERT INTO demotable (id) VALUES (NULL); 
 Query OK, 1 row affected (0.11 sec) 
 
 mysql>  SELECT * FROM demotable; 
 +----+---------------------+ 
 | id | dts                 | 
 +----+---------------------+ 
 |  1 | 2019-07-12 23:31:34 | 
 |  2 | 2019-07-12 23:31:35 | 
 |  3 | 2019-07-12 23:56:08 | 
 +----+---------------------+

好的工作。
現在，如果您只有一張桌子，這一切都很簡單。但是100多歲......

當然，自動化它，並使用您的information_schema來提供幫助。

再做幾個副本進行測試。


 mysql> create table demotable1 like demotable; 
 Query OK, 0 rows affected (0.51 sec) 
 
 mysql> create table demotable2 like demotable; 
 Query OK, 0 rows affected (1.04 sec) 
 
 mysql> create table demotable3 like demotable; 
 Query OK, 0 rows affected (0.74 sec) 
 
 mysql> create table demotable4 like demotable; 
 Query OK, 0 rows affected (2.21 sec)

打破他們所有..


 demo]# mv *.ibd /tmp/

現在使用您的information_schema.tables表，您可以構建所需的所有命令。


 # vi build_discard.sql 
 # cat build_discard.sql 

 SELECT CONCAT(" ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," DISCARD 
TABLESPACE;  ") as CMD FROM information_schema.TABLES WHERE 
TABLE_SCHEMA='demo'; 
 
 # vi build_import.sql 
 # cat build_import.sql 

 SELECT CONCAT(" ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," IMPORT 
TABLESPACE;  ") as CMD FROM information_schema.TABLES WHERE 
TABLE_SCHEMA='demo';


 # mysql -N < build_import.sql > import_tablespace.sql 
 # mysql -N < build_discard.sql  | mysql demo 
 
 demo]# cp /tmp/*.ibd . 
 demo]# chown mysql:mysql *.ibd 
 # systemctl restart mysqld 
 # mysql demo < import_tablespace.sql 
 # mysql demo 
 
 mysql> INSERT INTO demotable (id) VALUES (NULL); 
 Query OK, 1 row affected (0.08 sec) 
 
 mysql> INSERT INTO demotable1 (id) VALUES (NULL); 
 Query OK, 1 row affected (0.05 sec) 
 
 mysql> INSERT INTO demotable2 (id) VALUES (NULL); 
 Query OK, 1 row affected (0.09 sec) 
 
 mysql> INSERT INTO demotable3 (id) VALUES (NULL); 
 ^[[AQuery OK, 1 row affected (0.37 sec) 
 
 mysql> INSERT INTO demotable4 (id) VALUES (NULL); 
 Query OK, 1 row affected (0.12 sec)

它奏效了。

MySQL Binlogs ::如何恢復

所以我意識到在最近出現這種情況後我沒有發表過關於此的帖子。

以下是場景：在午夜進行備份，他們使用每個數據庫的MySQL轉儲。然後在第二天上午十點數據庫崩潰。在我被調用之前發生了一系列事件，但他們把它帶到了MyISAM表的數據庫版本和表空間中缺少的IBD文件。

所以選項1，從備份恢復會讓我們到午夜，我們會丟失數小時的數據。選項2，我們重新導入1000的ibd文件並保留所有內容。然後我們有選項3，從備份恢復，然後應用binlogs進行最近的更改。

為了使它更有趣，他們沒有我被告知的所有ibd文件，我確實看到一些丟失。所以不確定這是怎麼可能的，但是選項2變成了無效選項。當然，他們希望盡可能減少數據丟失，因此我們選擇了3。

為了安全地做到這一點，我在端口3307下啟動了另一個MySQL實例。這使我有了一個安全的工作場所，同時流量對端口3306實例上的MyISAM數據具有讀訪問權限。

一旦所有備份轉儲文件解壓縮並導入3307實例，我就可以專注於binlog文件。

起初，這個概念聽起來比實際風險要大得多。它實際上很簡單直接。

首先，您必須找到您之後的數據。通過查看binlog文件，您可以了解哪些文件是相關的。在我的情況下，他們設法重置了binlog，因此117文件中有2個日期範圍。

首先對於binlog審查，以下命令以人類可讀的格式輸出數據。

mysqlbinlog
 --defaults-file=/root/.my.cnf  --base64-output=DECODE-ROWS  --verbose 
mysql-bin.000117 >   review_mysql-bin.000117.sql

*注意......小心運行上面的命令。請注意，我將文件直接轉儲到binlog所在的位置。因此，確認您的文件名有效。這個mysql-bin.000117.sql與這個mysql-bin.000117 .sql不同。您將使用第二個選項和.sql之前的空格來丟失binlog。

現在保存數據，以便可以應用它。由於我有幾個binlogs，我創建了一個文件，我想要仔細檢查時間範圍。



 mysqlbinlog --defaults-file=/root/.my.cnf --start-datetime="2019-07-09 
00:00:00" --stop-datetime="2019-07-10 00:00:00" mysql-bin.000117 > 
binlog_restore.sql 
 mysqlbinlog --defaults-file=/root/.my.cnf mysql-bin.000118 >> binlog_restore.sql 
 mysqlbinlog --defaults-file=/root/.my.cnf mysql-bin.000119 >> binlog_restore.sql 

 mysqlbinlog --defaults-file=/root/.my.cnf --start-datetime="2019-07-10 
00:00:00" --stop-datetime="2019-07-10 10:00:00" mysql-bin.000117 
>> binlog_restore.sql 
 mysqlbinlog --defaults-file=/root/.my.cnf --stop-datetime="2019-07-10 10:00:00" mysql-bin.000120 >> binlog_restore.sql 
 mysqlbinlog --defaults-file=/root/.my.cnf --stop-datetime="2019-07-10 10:00:00" mysql-bin.000121 >> binlog_restore.sql 
 
 mysql --socket=/var/lib/mysql_restore/mysql.sock -e "source /var/lib/mysql/binlog_restore.sql"

現在我將這些binlog中的所有數據應用於給定的時間範圍。客戶端仔細檢查了所有數據，並非常高興能夠全部恢復。

對於這種情況存在幾種不同的選擇，這恰好與客戶一起鍛煉。

一旦驗證的all在恢復的版本上沒問題，它就是一個簡單的停止兩個數據庫，移動數據目錄（想要保持datadir默認完整），chown目錄只是為了安全並啟動MySQL。現在，已恢復的實例已在端口3306上啟動。

2019年6月17日星期一

MySQL組複製

所以MySQL的組複製出來了MySQL 5.7。現在已經有一段時間了，人們開始更多地詢問它。

下面是一個如何設置它的例子和一些痛點的例子，因為我用它來探討。
我使用三個不同的服務器，

服務器CENTOSA


 mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so'; 
 Query OK, 0 rows affected (0.02 sec) 
 
 vi my.cnf 
 disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" 
 server_id=1 
 gtid_mode=ON 
 enforce_gtid_consistency=ON 
 binlog_checksum=NONE 
 
 log_bin=binlog 
 log_slave_updates=ON 
 binlog_format=ROW 
 master_info_repository=TABLE 
 relay_log_info_repository=TABLE 
 
 transaction_write_set_extraction=XXHASH64 
 group_replication_group_name="90d8b7c8-5ce1-490e-a448-9c8d176b54a8" 
 group_replication_start_on_boot=off 
 group_replication_local_address= "192.168.111.17:33061" 
 group_replication_group_seeds= "192.168.111.17:33061,192.168.111.89:33061,192.168.111.124:33061" 
 group_replication_bootstrap_group=off 
 
 mysql> SET SQL_LOG_BIN=0; 
 mysql> CREATE USER repl@'%' IDENTIFIED BY 'replpassword'; 
 mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%'; 
 mysql> FLUSH PRIVILEGES; 
 mysql> SET SQL_LOG_BIN=1; 
 
 
 CHANGE MASTER TO 
 MASTER_USER='repl', 
 MASTER_PASSWORD='replpassword' 
 FOR CHANNEL 'group_replication_recovery'; 
 
 
 mysql> SET GLOBAL group_replication_bootstrap_group=ON; 
 Query OK, 0 rows affected (0.00 sec) 
 
 
 mysql> START GROUP_REPLICATION; 
 Query OK, 0 rows affected (3.11 sec) 
 
 
 mysql> SET GLOBAL group_replication_bootstrap_group=OFF; 
 Query OK, 0 rows affected (0.00 sec) 
 
 
 mysql> SELECT * FROM performance_schema.replication_group_members \G 
 
 *************************** 1. row *************************** 
 CHANNEL_NAME: group_replication_applier 
 MEMBER_ID: 1ab30239-5ef6-11e9-9b4a-08002712f4b1 
 MEMBER_HOST: centosa 
 MEMBER_PORT: 3306 
 MEMBER_STATE: ONLINE 
 MEMBER_ROLE: PRIMARY 
 MEMBER_VERSION: 8.0.15

所以現在我們可以添加更多服務器。
服務器CENTOSB

https://dev.mysql.com/doc/refman/8.0/en/group-replication-adding-instances.html

vi my.cnf 
 disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" 
 server_id=2 
 gtid_mode=ON 
 enforce_gtid_consistency=ON 
 binlog_checksum=NONE 
 
 log_bin=binlog 
 log_slave_updates=ON 
 binlog_format=ROW 
 master_info_repository=TABLE 
 relay_log_info_repository=TABLE 
 
 
 transaction_write_set_extraction=XXHASH64 
 group_replication_group_name="90d8b7c8-5ce1-490e-a448-9c8d176b54a8" 
 group_replication_start_on_boot=off 
 group_replication_local_address= "192.168.111.89:33061" 
 group_replication_group_seeds= "192.168.111.17:33061,192.168.111.89:33061,192.168.111.124:33061" 
 group_replication_bootstrap_group=off 
 
 mysql> CHANGE MASTER TO 
 MASTER_USER='repl', 
 MASTER_PASSWORD='replpassword' 
 FOR CHANNEL 'group_replication_recovery'; 
 Query OK, 0 rows affected, 2 warnings (0.02 sec) 
 
 mysql> CHANGE MASTER TO GET_MASTER_PUBLIC_KEY=1; 
 Query OK, 0 rows affected (0.02 sec) 
 
 mysql> START GROUP_REPLICATION; 
 Query OK, 0 rows affected (4.03 sec) 
 
 mysql> SELECT * FROM performance_schema.replication_group_members; 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 | group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | ONLINE | PRIMARY | 8.0.15 | 
 | group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | RECOVERING | SECONDARY | 8.0.15 | 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 2 rows in set (0.00 sec)

服務器CENTOSC

https://dev.mysql.com/doc/refman/8.0/en/group-replication-adding-instances.html

vi my.cnf 
 disabled_storage_engines="MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" 
 server_id=3 
 gtid_mode=ON 
 enforce_gtid_consistency=ON 
 binlog_checksum=NONE 
 log_bin=binlog 
 log_slave_updates=ON 
 binlog_format=ROW 
 master_info_repository=TABLE 
 relay_log_info_repository=TABLE 
 
 transaction_write_set_extraction=XXHASH64 
 group_replication_group_name="90d8b7c8-5ce1-490e-a448-9c8d176b54a8" 
 group_replication_start_on_boot=off 
 group_replication_local_address= "192.168.111.124:33061" 
 group_replication_group_seeds= "192.168.111.17:33061,192.168.111.89:33061,192.168.111.124:33061" 
 group_replication_bootstrap_group=off 
 
 mysql> CHANGE MASTER TO 
 -> MASTER_USER='repl', 
 -> MASTER_PASSWORD='replpassword' 
 -> FOR CHANNEL 'group_replication_recovery'; 
 Query OK, 0 rows affected, 2 warnings (0.02 sec) 
 
 mysql> CHANGE MASTER TO GET_MASTER_PUBLIC_KEY=1; 
 Query OK, 0 rows affected (0.02 sec) 
 
 mysql> START GROUP_REPLICATION; 
 Query OK, 0 rows affected (3.58 sec) 
 mysql> SELECT * FROM performance_schema.replication_group_members \G 
 *************************** 1. row *************************** 
 CHANNEL_NAME: group_replication_applier 
 MEMBER_ID: 1ab30239-5ef6-11e9-9b4a-08002712f4b1 
 MEMBER_HOST: centosa 
 MEMBER_PORT: 3306 
 MEMBER_STATE: ONLINE 
 MEMBER_ROLE: PRIMARY 
 MEMBER_VERSION: 8.0.15 
 
 *************************** 2. row *************************** 
 CHANNEL_NAME: group_replication_applier 
 MEMBER_ID: 572ca2fa-5eff-11e9-8df9-08002712f4b1 
 MEMBER_HOST: centosb 
 MEMBER_PORT: 3306 
 MEMBER_STATE: ONLINE 
 MEMBER_ROLE: SECONDARY 
 MEMBER_VERSION: 8.0.15 
 
 *************************** 3. row *************************** 
 CHANNEL_NAME: group_replication_applier 
 MEMBER_ID: c5f3d1d2-8dd8-11e9-858d-08002773d1b6 
 MEMBER_HOST: centosc 
 MEMBER_PORT: 3306 
 MEMBER_STATE: ONLINE 
 MEMBER_ROLE: SECONDARY 
 MEMBER_VERSION: 8.0.15 
 3 rows in set (0.00 sec)

所以這一切都很棒，但並不總是意味著他們上網，他們通常可以坐在恢復模式。
到目前為止，我已經看到MySQL崩潰失敗，所以需要確保它穩定。

mysql>
 create database testcentosb;<br> ERROR 1290 (HY000): The MySQL 
server is running with the --super-read-only option so it cannot execute
 this statement<br>

附註解決其中一些因素 -

mysql> START GROUP_REPLICATION; 
 ERROR 3094 (HY000): The START GROUP_REPLICATION command failed as the applier module failed to start. 
 
 mysql> reset slave all; 
 Query OK, 0 rows affected (0.03 sec)

- 然後從Change master命令重新開始

mysql> START GROUP_REPLICATION; 

 ERROR 3092 (HY000): The server is not configured properly to be an 
active member of the group. Please see more details on error log. 
 

 [ERROR] [MY-011735] [Repl] Plugin group_replication reported: '[GCS] 
Error on opening a connection to 192.168.111.17:33061 on local port: 
33061.' 
 [ERROR] [MY-011526] [Repl] Plugin group_replication 
reported: 'This member has more executed transactions than those present
 in the group. Local transactions: 
c5f3d1d2-8dd8-11e9-858d-08002773d1b6:1-4 > 
 [ERROR] [MY-011522] 
[Repl] Plugin group_replication reported: 'The member contains 
transactions not present in the group. The member will now exit the 
group.'

https://ronniethedba.wordpress.com/2017/04/22/this-member-has-more-executed-transactions-than-those-present-in-the-group/

[ERROR]
 [MY-011620] [Repl] Plugin group_replication reported: 'Fatal error 
during the recovery process of Group Replication. The server will leave 
the group.' 
 [ERROR] [MY-013173] [Repl] Plugin group_replication 
reported: 'The plugin encountered a critical error and will abort: Fatal
 error during execution of Group Replication' 
 
 SELECT * FROM performance_schema.replication_connection_status\G

我的想法...
請記住，可以在單主模式或多節點中設置組複製

mysql> select @@group_replication_single_primary_mode\G 
 *************************** 1. row *************************** 
 @@group_replication_single_primary_mode: 1 
 
 mysql> create database testcentosb; 
 ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement

如果您寫入無主節點，您當然會收到錯誤。

group-replication-single-primary-mode = off < - 添加到cnf文件中。

mysql> SELECT * FROM performance_schema.replication_group_members;

+ --------------------------- + --------------------- ----------------- + ------------- ------------- + + ---- ---------- + ------------- + ---------------- +

+ --------------------------- + --------------------- ----------------- + ------------- ------------- + + ---- ---------- + ------------- + ---------------- +

| group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | RECOVERING | 主 | 8.0.15 |

| group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | 線上 | 主 | 8.0.15 |

| group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | RECOVERING | 主 | 8.0.15 |

+ --------------------------- + --------------------- ----------------- + ------------- ------------- + + ---- ---------- + ------------- + ---------------- +

3組（0.00秒）

現在，如果您使用Keepalived，MySQL路由器，ProxySQL等來處理您的流量，以便在發生故障轉移時自動翻轉。當我停止主要時，我們可以從下面看到它立即失敗了。

mysql> SELECT * FROM performance_schema.replication_group_members ; 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 | group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | ONLINE | PRIMARY | 8.0.15 | 
 | group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | ONLINE | SECONDARY | 8.0.15 | 
 | group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | ONLINE | SECONDARY | 8.0.15 | 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 3 rows in set (0.00 sec) 
 
 [root@centosa]# systemctl stop mysqld 
 
 mysql> SELECT * FROM performance_schema.replication_group_members ; 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 | group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | ONLINE | PRIMARY | 8.0.15 | 
 | group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | ONLINE | SECONDARY | 8.0.15 | 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 2 rows in set (0.00 sec) 
 
 [root@centosa]# systemctl start mysqld 
 [root@centosa]# mysql 
 mysql> START GROUP_REPLICATION; 
 Query OK, 0 rows affected (3.34 sec) 
 
 mysql> SELECT * FROM performance_schema.replication_group_members ; 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 | group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | RECOVERING | SECONDARY | 8.0.15 | 
 | group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | ONLINE | PRIMARY | 8.0.15 | 
 | group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | ONLINE | SECONDARY | 8.0.15 | 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 3 rows in set (0.00 sec)

現在復蘇仍然是一個問題，因為它不會簡單地加入。不得不再次審查所有帳戶和步驟，但我最終確實得到了它。

mysql> SELECT * FROM performance_schema.replication_group_members; 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 | group_replication_applier | 1ab30239-5ef6-11e9-9b4a-08002712f4b1 | centosa | 3306 | ONLINE | SECONDARY | 8.0.15 | 
 | group_replication_applier | 572ca2fa-5eff-11e9-8df9-08002712f4b1 | centosb | 3306 | ONLINE | PRIMARY | 8.0.15 | 
 | group_replication_applier | c5f3d1d2-8dd8-11e9-858d-08002773d1b6 | centosc | 3306 | ONLINE | SECONDARY | 8.0.15 | 

 
+---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+
 
 3 rows in set (0.00 sec)