玩转一下 MySQL 双主集群
安装
准备配置文件
server1.cnf:
[mysqld]
datadir = /var/lib/mysql
server-id=3310
log-bin=mysql-bin
relay-log=relay-log
log-slave-updates=1
gtid-mode=on
enforce-gtid-consistency=on
slave-skip-errors=all
binlog_format = row
binlog-ignore-db=test
binlog-ignore-db=information_schema
replicate-ignore-db=test
replicate-ignore-db=information_schema
auto-increment-increment = 2
auto-increment-offset = 1
expire_logs_days = 10
#max_binlog_size = 100M
server2.cnf 与 server1 相同，调整 server-id=3311，auto-increment-offset = 2 即可。
docker 安装
➜ docker pull mysql:5.7.27
➜ docker network create mysql-net
➜ docker run -d --name mysql-server1 \
--network=mysql-net \
-e MYSQL_USER=my_user \
-e MYSQL_DATABASE=my_database \
-e MYSQL_PASSWORD=my_database_password \
-e MYSQL_ROOT_PASSWORD=my_root_password \
-p 33061:3306 \
-v ~/mysqlcluster/server1.cnf:/etc/mysql/conf.d/my.cnf \
-v ~/mysqlcluster/server1data:/var/lib/mysql \
mysql:5.7.27
➜ docker run -d --name mysql-server2 \
--network=mysql-net \
-e MYSQL_USER=my_user \
-e MYSQL_DATABASE=my_database \
-e MYSQL_PASSWORD=my_database_password \
-e MYSQL_ROOT_PASSWORD=my_root_password \
-p 33062:3306 \
-v ~/mysqlcluster/server2.cnf:/etc/mysql/conf.d/my.cnf \
-v ~/mysqlcluster/server2data:/var/lib/mysql \
mysql:5.7.27
检查参数 建立复制用户
➜ docker exec -it mysql-server1 mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.27 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| my_database |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> show variables like "%log_bin%";
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------+
6 rows in set (0.01 sec)
mysql> create user 'repl_user'@'%' identified by 'XXXXXXXXXX';
Query OK, 0 rows affected (0.00 sec)
mysql> Grant replication slave on *.* to 'repl_user'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
➜ docker exec -it mysql-server2 mysql -u root -p 同理见上，省略
增加双向复制
在 server1 上执行：
stop slave;
CHANGE MASTER TO
MASTER_HOST = 'mysql-server2',
MASTER_PORT = 3306,
MASTER_USER = 'repl_user',
MASTER_PASSWORD = 'XXXXXXXXXX',
MASTER_AUTO_POSITION = 1;
start slave;
在 server2 上执行：
stop slave;
CHANGE MASTER TO
MASTER_HOST = 'mysql-server1',
MASTER_PORT = 3306,
MASTER_USER = 'repl_user',
MASTER_PASSWORD = 'XXXXXXXXXX',
MASTER_AUTO_POSITION = 1;
start slave;
分别在 server1 和 server2 上检查 slave 状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Queueing master event to the relay log
Master_Host: 172.17.0.2
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 2743486
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 1047
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: test,information_schema
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: 834
Relay_Log_Space: 2743900
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: 2748
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: 3310
Master_UUID: e6dacdce-d3b3-11e9-8bbe-0242ac110002
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Reading event from the relay log
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: e6dacdce-d3b3-11e9-8bbe-0242ac110002:1-4
Executed_Gtid_Set: 29d9da3c-d3b4-11e9-8e33-0242ac110003:1-11,
e6dacdce-d3b3-11e9-8bbe-0242ac110002:1-4
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
验证
mysql> create database bjca;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bjca |
| my_database |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.02 sec)
use bjca;
CREATE TABLE `test` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
select name from mysql.proc where name like 'test%';
DROP PROCEDURE IF EXISTS test_initData;
DELIMITER $
CREATE PROCEDURE test_initData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=10000 DO
INSERT INTO test(id,a,b,c) VALUES(i,i*2,i*3,i*4);
SET i = i+1;
END WHILE;
END $
CALL test_initData();
select count(*) from test;
create table t1(id int auto_increment, `a` int(11) DEFAULT NULL,primary key(id));
insert into t1(a) values(3);
脚本
docker 看两个 server 的 IP
➜ docker network ls
NETWORK ID NAME DRIVER SCOPE
1372d35aa0a2 bridge bridge local
3db1df2cfed9 dockercompose_default bridge local
13db711b5ce1 host host local
68ea27b7ed16 kong-net bridge local
cca642568a4a none null local
➜ docker network inspect mysql-net
[
{
"Name": "mysql-net",
"Id": "eff1f3d28e63dcba33032165e66fd65d2c63655713f840fcb0d206a43517e120",
"Created": "2019-09-11T02:13:48.435231Z",
"Scope": "local",
"Driver": "bridge",
"EnableIPv6": false,
"IPAM": {
"Driver": "default",
"Options": {},
"Config": [
{
"Subnet": "172.21.0.0/16",
"Gateway": "172.21.0.1"
}
]
},
"Internal": false,
"Attachable": false,
"Ingress": false,
"ConfigFrom": {
"Network": ""
},
"ConfigOnly": false,
"Containers": {
"4e2cc5a3b5fdf8a4781147575f616277e09472111423d0f1df7d784eabb69dfb": {
"Name": "mysql-server2",
"EndpointID": "24971b676da5e5fcb5d157837c18c52aeb179f38af7715ab8b8671eaebc84733",
"MacAddress": "02:42:ac:15:00:02",
"IPv4Address": "172.21.0.2/16",
"IPv6Address": ""
},
"fbba9a7fdc3602c0d9953d204ad33a8924077a45cd1f4a4af80e4825aca93efe": {
"Name": "mysql-server1",
"EndpointID": "61b7152460212e39707bbf01e1b8add30c20267d4dfeaf46972c263eb62c39f6",
"MacAddress": "02:42:ac:15:00:03",
"IPv4Address": "172.21.0.3/16",
"IPv6Address": ""
}
},
"Options": {},
"Labels": {}
}
]
MTS 开启关闭
开启 MTS:
STOP SLAVE;
SET GLOBAL slave_parallel_type='LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers=5;
START SLAVE;
关闭 MTS:
STOP SLAVE;
SET GLOBAL slave_parallel_type='DATABASE';
SET GLOBAL slave_parallel_workers=0;
START SLAVE;
MySQL 性能测试
➜ docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=my-secret-pw -p 33061:3306 -d mysql:5.7
28515f68f95d6a24e9072343aabc237414d310df2ddc82e506a2810c160487d8
➜ docker exec -it some-mysql bash
# mysqlslap -uroot -pmy-secret-pw --concurrency=100 --iterations=100 --auto-generate-sql --auto-generate-sql-load-type=mixed --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=10 -P3306 -h172.17.0.2
root@fcf387f1776e:/# mysqlslap -uroot -pmy-secret-pw --concurrency=100 --iterations=100 --auto-generate-sql --auto-generate-sql-load-type=mixed --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=10 -P3306 -h127.0.0.1
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.522 seconds
Minimum number of seconds to run all queries: 0.407 seconds
Maximum number of seconds to run all queries: 1.165 seconds
Number of clients running queries: 100
Average number of queries per client: 0
更多 MySQL 自带的性能压力测试工具 mysqlslap( xstarcd.github io/wiki/MySQL/mysqlslap.html)
使用语法如下：
mysqlslap [options]
常用参数 [options] 详细说明:
--auto-generate-sql, -a 自动生成测试表和数据，表示用mysqlslap工具自己生成的SQL脚本来测试并发压力。
--auto-generate-sql-load-type=type 测试语句的类型。代表要测试的环境是读操作还是写操作还是两者混合的。取值包括：read，key，write，update和mixed(默认)。
--auto-generate-sql-add-auto-increment 代表对生成的表自动添加auto_increment列，从5.1.18版本开始支持。
--number-char-cols=N, -x N 自动生成的测试表中包含多少个字符类型的列，默认1
--number-int-cols=N, -y N 自动生成的测试表中包含多少个数字类型的列，默认1
--number-of-queries=N 总的测试查询次数(并发客户数×每客户查询次数)
--query=name,-q 使用自定义脚本执行测试，例如可以调用自定义的一个存储过程或者sql语句来执行测试。
--create-schema 代表自定义的测试库名称，测试的schema，MySQL中schema也就是database。
--commint=N 多少条DML后提交一次。
--compress, -C 如果服务器和客户端支持都压缩，则压缩信息传递。
--concurrency=N, -c N 表示并发量，也就是模拟多少个客户端同时执行select。可指定多个值，以逗号或者--delimiter参数指定的值做为分隔符。例如：--concurrency=100,200,500。
--engine=engine_name, -e engine_name 代表要测试的引擎，可以有多个，用分隔符隔开。例如：--engines=myisam,innodb。
--iterations=N, -i N 测试执行的迭代次数，代表要在不同并发环境下，各自运行测试多少次。
--only-print 只打印测试语句而不实际执行。
--detach=N 执行N条语句后断开重连。
--debug-info, -T 打印内存和CPU的相关信息。
说明
测试的过程需要生成测试表，插入测试数据，这个 mysqlslap 可以自动生成，默认生成一个 mysqlslap 的 schema，如果已经存在则先删除。可以用 --only-print 来打印实际的测试过程，整个测试完成后不会在数据库中留下痕迹。
测试实例
各种测试参数实例（-p后面跟的是mysql的root密码）：
# 单线程测试。测试做了什么。
mysqlslap -a -uroot -p123456
# 多线程测试。使用–concurrency来模拟并发连接。
mysqlslap -a -c 100 -uroot -p123456
# 迭代测试。用于需要多次执行测试得到平均值。
mysqlslap -a -i 10 -uroot -p123456
mysqlslap ---auto-generate-sql-add-autoincrement -a -uroot -p123456
mysqlslap -a --auto-generate-sql-load-type=read -uroot -p123456
mysqlslap -a --auto-generate-secondary-indexes=3 -uroot -p123456
mysqlslap -a --auto-generate-sql-write-number=1000 -uroot -p123456
mysqlslap --create-schema world -q "select count(*) from City" -uroot -p123456
mysqlslap -a -e innodb -uroot -p123456
mysqlslap -a --number-of-queries=10 -uroot -p123456
# 测试同时不同的存储引擎的性能进行对比：
mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info -uroot -p123456
# 执行一次测试，分别50和100个并发，执行1000次总查询：
mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --debug-info -uroot -p123456
# 50和100个并发分别得到一次测试结果(Benchmark)，并发数越多，执行完所有查询的时间越长。为了准确起见，可以多迭代测试几次:
mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --debug-info -uroot -p123456
--------------------------
mysql> select host, user from mysql.user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| % | repl |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
4 rows in set (0.00 sec)
------------------
MySQL 客户端无密码登录
环境变量 MYSQL_PWD='!12345' ./mysql -h 127.0.0.1 -u root
配置编辑器
mysql_config_editor set --login-path=mysql_login --host=127.0.0.1 --port=33061 --user=root --password
mysql --login-path=mysql_login
详见( github 商业网/bingoohuang/docker-compose-mysql-master-master/blob/0aed0d4f6439e1debada68098f3e3e8b6a4d3c6c/MySQLReplicationCheck.sh)
直接 ./mysql -h 127.0.0.1 -u root -p'!12345'，但是会收获一条告警
mysql: [Warning] Using a password on the command line interface can be insecure.
指定 unix sock 文件本机登录
MYSQL_PWD='!12345' mysql -S /tmp/mysql.sock -uroot -P13306，sock 文件可以从 my.cnf 中查阅，例如 /usr/local/mysql/data/mysql.sock
-----------------------
slideshare Advanced mysql replication techniques( slideshare 网络网/datacharmer/advanced-mysql-replication-techniques) 下载( github 商业网/bingoohuang/blog/files/3812831/advancedmysqlreplicationtechniques-110411153301-phpapp02.pdf)
-------------------------
主库 sync_binlog=100，然后在插入数据中 ，断电重启，然后从库的同步服务就挂了：
Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log:
'Slave has more GTIDs than the master has, using the master's SERVER_UUID.
This may indicate that the end of the binary log was truncated or that the last binary
log file was lost, e.g., after a power or disk failure when sync_binlog != 1.
The master may or may not have rolled back transactions that were already replica'
After this somehow long GTID presentation, we can now discuss what happens after an OS crash on a master with sync_binlog != 1 and with slaves replicating using GTID negotiation (and I promise this will be quick as all the needed background is already explained above). The first thing to remember (from the first post in the series) is that after such a crash, some binary logs are lost. As the GTID state of the master is stored in the binary logs, this state rewinds to a certain point in the past.
Let's suppose that the master has committed transactions 1 to 60 before the crash, and that after the crash, the master only remembers up to transaction 49. In this case, the next committed transaction on the master is tagged with GTID number 50. However, that slave has already seen transaction 50 (maybe it has seen up to transaction 58...). This is depicted in the diagram below.
The main problem is that after an OS crash, a master with sync_binlog != 1 re-uses GTIDs already seen by slaves
From now on, there are two possibilities:
- The slave connects back to the master before it commits transaction 58.
- The slave connects back to the master after it commits transaction 58.
In case #1, replication breaks as the slave requests transactions unknown to the master.
In case #2, the master sends the slave transaction 59 and onward. So in this case, the slave has transactions 50 to 58 from before the crash (those are lost from the binary logs because of the crash, but they could be in InnoDB), the slave also misses transactions 50 and 58 from after the crash (because the GTID state of the slave has these from before the crash), and it executes transaction 59 and onward from after the crash. Obviously, depending on the data modified by these transactions, replication might break, but in all cases, we have serious data inconsistencies.
MySQL 8 复制（四）——GTID与复制( cloud.tencent 商业网/developer/article/1454513)
如果在事务交换期间发现从库已经在GTID中接收或提交了事务，但主库本身没有它们的记录，则复制停止，主库将错误ER_SLAVE_HAS_MORE_GTIDS_THAN_MASTER发送给从库。当没有配置sync_binlog=1的主库遇到电源故障或操作系统崩溃，导致尚未同步到二进制日志文件的已提交事务已被从库接收，则会发生这种情况。如果主库重新提交事务，可能导致主库和从库对不同的事务使用相同的GTID，这时只能根据需要对各个事务手动解决冲突（例如手工设置gtid_next）。如果问题仅在于主库缺少事务，则可以主从切换，允许它跟上复制拓扑中的其它服务器，然后在需要时再次将其设置为主库。可见sync_binlog=1对于主从数据一致至关重要，这也是MySQL 8的缺省配置值。
---------------------------------
MySQL Replication and GTID-Based Failover - A Deep Dive Into Errant Transactions( severalnines 商业网/database-blog/mysql-replication-and-gtid-based-failover-deep-dive-errant-transactions)
-------------------------------------
- sync_binlog( dev.mysql 商业网/doc/refman/5.7/en/replication-options-binary-log.html)
| Property | Value |
|---|---|
| Command-Line Format | --sync-binlog=# |
| System Variable | sync_binlog |
| Scope | Global |
| Dynamic | Yes |
| Type | Integer |
| Default Value (>= 5.7.7) | 1 |
| Default Value (<= 5.7.6) | 0 |
| Minimum Value | 0 |
| Maximum Value | 4294967295 |
For the greatest possible durability and consistency in a replication setup that uses InnoDB with transactions, use these settings:
- sync_binlog=1.
- innodb_flush_log_at_trx_commit=1.
Caution
Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not. In this case, the durability of transactions is not guaranteed even with the recommended settings, and in the worst case, a power outage can corrupt InnoDB data. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try to disable the caching of disk writes in hardware caches.
- innodb_flush_log_at_trx_commit( dev.mysql 商业网/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit)
For durability and consistency in a replication setup that uses InnoDB with transactions:
- If binary logging is enabled, set sync_binlog=1.
- Always set innodb_flush_log_at_trx_commit=1.
湘公网安备 43102202000103号