时间：2021-07-01 10:21:17 帮助过：10人阅读
master_info_repository=TABLE （开启MTS 功能后，会频繁更新master.info，设置为TABLE 减小开销）relay_log_info_repository=TABLE
slave_master_info 记录了首次同步master 的位置relay_log_recovery=ON （slave IO 线程crash，如果relay‐log损坏，则自动放弃所有未执行的relay‐log，重新从master 上获取日志，保证relay‐log 的完整性）
slave_preserve_commit_order=ON （保证提交的顺序性）在slave 上应用事务的顺序是无序的，和relay log 中记录的事务顺序不一样，这样数据一致性是无法保证的，为了保证事务是按照relay log 中记录的顺序来回放，就需要开启参数slave_preserve_commit_order。
虽然mysql5.7 添加MTS 后，虽然slave 可以并行应用relay log，但commit 部分仍然是顺序提交，其中可能会有等待的情况。
优化参数
[mysqld]
bind-address=0.0.0.0
port=3306
datadir=/data/mysql
socket=/data/mysql/mysql.sock
user=mysql
skip-name-resolve
slow_query_log=on
long_query_time=1
slow_query_log_file=/data/mysql/mysql-slow.log
innodb-file-per-table=1
innodb_flush_log_at_trx_commit = 2
log_warnings = 1
connect_timeout = 60
net_read_timeout = 120
performance_schema_max_table_instances = 400
server-id = 2
relay-log = relay-log
relay-log-index = relay-log.index
slave_parallel_type=LOGICAL_CLOCK
slave_parallel_workers=2
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
slave_preserve_commit_order=ON
log_bin=mysql‐bin
log_slave_updates=ON
[mysqld_safe]
log-error=/data/mysql/mysqld.log
pid-file=/data/mysql/mysqld.pid
重启mysqld进入数据库
mysql> show variables like ‘%slave_parallel_type%‘; +---------------------+---------------+ | Variable_name | Value | +---------------------+---------------+ | slave_parallel_type | LOGICAL_CLOCK | +---------------------+---------------+ mysql> show variables like ‘%slave_parallel_worker%‘; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | slave_parallel_workers | 2 | +------------------------+-------+ mysql> show variables like ‘%master_info_repository%‘; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | master_info_repository | TABLE | +------------------------+-------+ mysql> set global relay_log_info_repository=‘TABLE‘; Query OK, 0 rows affected (0.00 sec) mysql> show variables like ‘%relay_log_info_repository%‘; +---------------------------+-------+ | Variable_name | Value | +---------------------------+-------+ | relay_log_info_repository | TABLE | +---------------------------+-------+ mysql> show variables like ‘%relay_log_recovery%‘; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | relay_log_recovery | ON | +--------------------+-------+ 1 row in set (0.00 sec) mysql> show variables like ‘%slave_preserve_commit_order%‘; +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | slave_preserve_commit_order | ON | +-----------------------------+-------+
开启slave
mysql> start slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.132.121 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000002 Read_Master_Log_Pos: 40870 Relay_Log_File: relay-log.000010 Relay_Log_Pos: 321 Relay_Master_Log_File: master-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: 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: 40870 Relay_Log_Space: 689 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: 0 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: 1 Master_UUID: 6b00724f-a094-11e9-8f47-000c2991dd19 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:
主库写入数据
[root@master mysql]#mysql -uroot -p123456 -e ‘use darren;insert into test values (1); [root@master mysql]#mysql -uroot -p123456 -e ‘use darren;insert into test values (1); [root@master mysql]#mysql -uroot -p123456 mysql> mysql> show master status; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000002 | 41388 | | | | +-------------------+----------+--------------+------------------+-------------------+
从库
mysql> use mysql; Database changed mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | engine_cost | | event | | func | | general_log | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | server_cost | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ mysql> select * from slave_master_info; +-----------------+-------------------+----------------+-----------------+-------------+---------------+------+---------------+-------------+--------+------------+----------+------------+---------+------------------------+-----------+------+--------------------+--------------------------------------+-------------+---------+-------------+-----------------------+--------------+-------------+ | Number_of_lines | Master_log_name | Master_log_pos | Host | User_name | User_password | Port | Connect_retry | Enabled_ssl | Ssl_ca | Ssl_capath | Ssl_cert | Ssl_cipher | Ssl_key | Ssl_verify_server_cert | Heartbeat | Bind | Ignored_server_ids | Uuid | Retry_count | Ssl_crl | Ssl_crlpath | Enabled_auto_position | Channel_name | Tls_version | +-----------------+-------------------+----------------+-----------------+-------------+---------------+------+---------------+-------------+--------+------------+----------+------------+---------+------------------------+-----------+------+--------------------+--------------------------------------+-------------+---------+-------------+-----------------------+--------------+-------------+ | 25 | master-bin.000002 | 40870 | 192.168.132.121 | replication | 1234567 | 3306 | 60 | 0 | | | | | | 0 | 30 | | 0 | 6b00724f-a094-11e9-8f47-000c2991dd19 | 86400 | | | 0 | | | +-----------------+-------------------+----------------+-----------------+-------------+---------------+------+---------------+-------------+--------+------------+----------+------------+---------+------------------------+-----------+------+--------------------+--------------------------------------+-------------+---------+-------------+-----------------------+--------------+-------------+ mysql> select * from slave_relay_log_info; +-----------------+--------------------+---------------+-------------------+----------------+-----------+-------------------+----+--------------+ | Number_of_lines | Relay_log_name | Relay_log_pos | Master_log_name | Master_log_pos | Sql_delay | Number_of_workers | Id | Channel_name | +-----------------+--------------------+---------------+-------------------+----------------+-----------+-------------------+----+--------------+ | 7 | ./relay-log.000010 | 839 | master-bin.000002 | 41388 | 0 | 2 | 1 | | +-----------------+--------------------+---------------+-------------------+----------------+-----------+-------------------+----+--------------+
查看线程
+----+-------------+-----------+-------+---------+------+--------------------------------------------------------+-------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+-------+---------+------+--------------------------------------------------------+-------------------+ | 7 | root | localhost | mysql | Query | 0 | starting | show processlist | | 36 | system user | | NULL | Connect | 762 | Waiting for master to send event | NULL | | 37 | system user | | NULL | Connect | 567 | Slave has read all relay log; waiting for more updates | NULL | | 38 | system user | | NULL | Connect | 567 | Waiting for an event from Coordinator | NULL | | 39 | system user | | NULL | Connect | 762 | Waiting for an event from Coordinator | NULL | +----+-------------+-----------+-------+---------+------+--------------------------------------------------------+-------------------+
mysql主从之多线程复制
标签：keyword 进制 code mic 怎样 info 频繁 延时 update