目录
- 1. 清理二进制日志
- 2. mysqldump不锁表
- 3. mysql跳过空事务
- 4. 番外
- 5. mysql8.0使用mysqldump导出数据
1. 清理二进制日志
purge master logs to 'log-bin.004193'; #表示直接清理到4193位置
2. mysqldump不锁表
在使用mysqldump备份mysql数据时，要尽量去从库拿，如果有需求去主库，可以加 —single-transaction 参数不锁表，不加此参数有可能会把主库的表全锁了！！导致业务出现故障
mysqldump --single-transaction --compact -uroot -p（password） -h（dbip） -d （databasesname） > /tmp/test.sql
3. mysql跳过空事务
问题出现：
slave_io_state: waiting for master to send event master_host: 10.187.97.219 master_user: repl master_port: 3306 connect_retry: 60 master_log_file: log-bin.000047 read_master_log_pos: 61907358 relay_log_file: relay.000114 relay_log_pos: 61906291 relay_master_log_file: log-bin.000047 slave_io_running: yes slave_sql_running: no replicate_do_db: replicate_ignore_db: replicate_do_table: replicate_ignore_table: replicate_wild_do_table: replicate_wild_ignore_table: last_errno: 1062 last_error: coordinator stopped because there were error(s) in the worker(s). the most recent failure being: worker 1 failed executing transaction '81a58913-993d-11eb-94c7-00e0ed7ae706:37497' at master log log-bin.000047, end_log_pos 61906370. see error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. skip_counter: 0 exec_master_log_pos: 61906082 relay_log_space: 61907849 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: null master_ssl_verify_server_cert: no last_io_errno: 0 last_io_error: last_sql_errno: 1062 last_sql_error: coordinator stopped because there were error(s) in the worker(s). the most recent failure being: worker 1 failed executing transaction '81a58913-993d-11eb-94c7-00e0ed7ae706:37497' at master log log-bin.000047, end_log_pos 61906370. see error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any. replicate_ignore_server_ids: master_server_id: 5345323 master_uuid: 81a58913-993d-11eb-94c7-00e0ed7ae706 master_info_file: mysql.slave_master_info sql_delay: 0 sql_remaining_delay: null slave_sql_running_state: master_retry_count: 86400 master_bind: last_io_error_timestamp: last_sql_error_timestamp: 210413 05:25:50 master_ssl_crl: master_ssl_crlpath: #master# retrieved_gtid_set: 81a58913-993d-11eb-94c7-00e0ed7ae706:2-37500 #slave# executed_gtid_set: 119cf71e-993c-11eb-94bd-00e0ed93753c:1-3, #81a58913-993d-11eb-94c7-00e0ed7ae706:1-37496 auto_position: 1 replicate_rewrite_db: channel_name: master_tls_version: 1 row in set (0.00 sec)
开始处理：首先我们知道
executed_gtid_set: 119cf71e-993c-11eb-94bd-00e0ed93753c:1-3,
81a58913-993d-11eb-94c7-00e0ed7ae706:1-37496
是slave 已经执行过的事务。
其中：
81a58913-993d-11eb-94c7-00e0ed7ae706:1-37496 是已经回放了的从master 同步的事务， 119cf71e-993c-11eb-94bd-00e0ed93753c:1-3, 是在 slave 上 执行的事务
而下面这个是从master 同步的事务，等待slave
retrieved_gtid_set: 81a58913-993d-11eb-94c7-00e0ed7ae706:2-37500
执行停止slave
mysql> stop slave; query ok, 0 rows affected (0.00 sec)
检查当前 gtid 相关信息
mysql> show variables like '%gtid%'; +----------------------------------+----------------------------------------+ | variable_name | value | +----------------------------------+----------------------------------------+ | binlog_gtid_simple_recovery | on | | enforce_gtid_consistency | on | | gtid_executed | | | gtid_executed_compression_period | 1000 | | gtid_mode | on | | gtid_next | automatic | | gtid_owned | | | gtid_purged | 81a58913-993d-11eb-94c7-00e0ed7ae706:1 | | session_track_gtids | off | +----------------------------------+----------------------------------------+ 9 rows in set (0.00 sec)
将事务指向37496 的下一个事务，即 37497，注意规范，把 :1-37396 的 1 去掉
mysql> set gtid_next='81a58913-993d-11eb-94c7-00e0ed7ae706:37497'; query ok, 0 rows affected (0.00 sec) mysql> show variables like '%gtid%'; +----------------------------------+--------------------------------------------+ | variable_name | value | +----------------------------------+--------------------------------------------+ | binlog_gtid_simple_recovery | on | | enforce_gtid_consistency | on | | gtid_executed | | | gtid_executed_compression_period | 1000 | | gtid_mode | on | | gtid_next | 81a58913-993d-11eb-94c7-00e0ed7ae706:37497 | | gtid_owned | 81a58913-993d-11eb-94c7-00e0ed7ae706:37497 | | gtid_purged | 81a58913-993d-11eb-94c7-00e0ed7ae706:1 | | session_track_gtids | off | +----------------------------------+--------------------------------------------+ 9 rows in set (0.01 sec)
跳过空事务：
mysql> begin; query ok, 0 rows affected (0.00 sec) mysql> commit; query ok, 0 rows affected (0.00 sec)
查询gtid 信息：
mysql> show variables like '%gtid%'; +----------------------------------+--------------------------------------------+ | variable_name | value | +----------------------------------+--------------------------------------------+ | binlog_gtid_simple_recovery | on | | enforce_gtid_consistency | on | | gtid_executed | | | gtid_executed_compression_period | 1000 | | gtid_mode | on | | gtid_next | 81a58913-993d-11eb-94c7-00e0ed7ae706:37497 | | gtid_owned | | | gtid_purged | 81a58913-993d-11eb-94c7-00e0ed7ae706:1 | | session_track_gtids | off | +----------------------------------+--------------------------------------------+ 9 rows in set (0.00 sec)
设置自动分配 gtid：
ysql> set gtid_next='automatic'; query ok, 0 rows affected (0.00 sec)
4. 番外
我们知道一个新的事务在提交后会被分配一个新的gtid,当该事务在从库上被应用时会保留主库上的gtid
我们可以通过设定gtid_next 的值来改变这种行为
1 automatic
当设置为automatic时(默认值)时，系统会自动分配一个gtid，如果事务回滚或者没有写入到二进制文件时则不会分配
2 具体的gtid值
我们可以设置该变量为一个具体的有效的gtid，这时服务器会将该gtid分配给下一个事务，就算该事务没有被写入二进制日志或者为空事务，该gtid也会被分配并加入到gtid_executed变量中
这里需要注意的是，如果该变量值不为automatic，我们需要手动的为每个事务指定gtid，否则该事务会失败，你可以将其改为automatic，让服务器自动分配
启动 slave：
mysql> start slave; query ok, 0 rows affected (0.03 sec) mysql> show variables like '%gtid%'; +----------------------------------+----------------------------------------+ | variable_name | value | +----------------------------------+----------------------------------------+ | binlog_gtid_simple_recovery | on | | enforce_gtid_consistency | on | | gtid_executed | | | gtid_executed_compression_period | 1000 | | gtid_mode | on | | gtid_next | automatic | | gtid_owned | | | gtid_purged | 81a58913-993d-11eb-94c7-00e0ed7ae706:1 | | session_track_gtids | off | +----------------------------------+----------------------------------------+ 9 rows in set (0.00 sec) mysql> show slave status\g *************************** 1. row *************************** slave_io_state: waiting for master to send event master_host: 10.187.97.219 master_user: repl master_port: 3306 connect_retry: 60 master_log_file: log-bin.000047 read_master_log_pos: 61907358 relay_log_file: relay.000115 relay_log_pos: 448 relay_master_log_file: log-bin.000047 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: 61907358 relay_log_space: 61908058 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: 5345323 master_uuid: 81a58913-993d-11eb-94c7-00e0ed7ae706 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: 81a58913-993d-11eb-94c7-00e0ed7ae706:2-37500 executed_gtid_set: 119cf71e-993c-11eb-94bd-00e0ed93753c:1-3, 81a58913-993d-11eb-94c7-00e0ed7ae706:1-37500 auto_position: 1 replicate_rewrite_db: channel_name: master_tls_version: 1 row in set (0.00 sec)
5. mysql8.0使用mysqldump导出数据
导出指定库的所有数据：
./bin/mysqldump -uroot -padmin123 -s status/mysql3306.sock aaa5 --set-gtid-purged=off --single-transaction > /root/aaa5.sql
注释：
- 1.数据库前面不需要加任何参数，如果加-d就是只导出表结构
- 2.当数据库开启
gtid后需要在导出数据时把gtid关掉，即加--set-gtid-purged=off参数，因为gtid是唯一的，再插入时会报错 - 3.加参数–
single-transaction不锁表
到此这篇关于mysql常用命令以及小技巧的文章就介绍到这了,更多相关mysql常用命令和小技巧内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！