返回
首页
业界
电商
创业
访谈
手机
移动
报告
运营
建站
互联网+
系统
教程
搜索

mysql常用命令以及小技巧

2022-06-07 18:08:24

目录1.清理二进制日志2.mysqldump不锁表3.mysql跳过空事务4.番外5.mysql8.0使用mysqldump导出数据1.清理二进制日志purgemasterlogst...

目录
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 statusG
*************************** 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常用命令和小技巧内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

相关文章 大家在看