玩转一下 MySQL 双主集群

发布于 2022-09-29 12:09:58 字数 24167 浏览 192 评论 0

安装

准备配置文件

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

使用语法如下：

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

详见

直接 ./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 下载

-------------------------

主库 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与复制

如果在事务交换期间发现从库已经在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

-------------------------------------

sync_binlog

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.