Open bingoohuang opened 4 years ago
bingoohuang
commented
4 years ago 开启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; ➜ 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使用语法如下：
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 -p123456mysql> 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.sockbingoohuang
commented
4 years ago bingoohuang
commented
4 years ago 主库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:
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.
如果在事务交换期间发现从库已经在GTID中接收或提交了事务，但主库本身没有它们的记录，则复制停止，主库将错误ER_SLAVE_HAS_MORE_GTIDS_THAN_MASTER发送给从库。当没有配置sync_binlog=1的主库遇到电源故障或操作系统崩溃，导致尚未同步到二进制日志文件的已提交事务已被从库接收，则会发生这种情况。如果主库重新提交事务，可能导致主库和从库对不同的事务使用相同的GTID，这时只能根据需要对各个事务手动解决冲突（例如手工设置gtid_next）。如果问题仅在于主库缺少事务，则可以主从切换，允许它跟上复制拓扑中的其它服务器，然后在需要时再次将其设置为主库。可见sync_binlog=1对于主从数据一致至关重要，这也是MySQL 8的缺省配置值。
bingoohuang
commented
4 years ago bingoohuang
commented
4 years ago | 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:
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.
For durability and consistency in a replication setup that uses InnoDB with transactions:
玩转一下MySQL双主集群
安装
准备配置文件
server1.cnf:
server2.cnf与server1相同，调整server-id=3311, auto-increment-offset = 2即可。
docker安装
检查参数，建立复制用户
增加双向复制
在server1上执行：
在server2上执行：
分别在server1和server2上检查slave状态
验证
脚本
docker看两个server的IP
参考