-
Notifications
You must be signed in to change notification settings - Fork 23
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
玩转一下MySQL双主集群 #118
Comments
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 使用语法如下:
说明:
测试实例 # 单线程测试。测试做了什么。
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 客户端无密码登录
|
主库
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 In case
|
For the greatest possible durability and consistency in a replication setup that uses InnoDB with transactions, use these settings:
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
参考
The text was updated successfully, but these errors were encountered: