MySQL Admin Commands

Posted on Edited on In

Check Status

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.7.33, for Linux (x86_64) using  EditLine wrapper

Connection id:          513
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.16 MySQL Community Server - GPL
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /tmp/mysql.sock
Uptime:                 4 days 21 hours 55 min 37 sec

Threads: 3  Questions: 388  Slow queries: 0  Opens: 368  Flush tables: 6  Open tables: 23  Queries per second avg: 0.000
--------------
  • Threads: This indicates the total number of currently connected client threads (connections) to the MySQL server
  • Questions: This is a cumulative counter representing the total number of queries (statements) that the server has executed since it was last started
  • Slow queries: This is a cumulative counter for the number of queries that have taken longer than the long_query_time system variable setting to execute. By default, long_query_time is usually 10 seconds.
  • Opens: This is a cumulative counter for the number of files (tables, logs, etc.) that MySQL has opened.
  • Flush tables: This is a cumulative counter for the number of times a FLUSH TABLES command (or similar flush operation) has been executed. FLUSH TABLES forces MySQL to close all open tables and reload them from disk. This is often done after making changes to table structures, privileges, or for backup purposes.
  • Open tables: This represents the actual number of tables that are currently open in the MySQL table cache. These tables are kept open to avoid the overhead of opening and closing them repeatedly, improving performance.
  • Query per second: It’s calculated as Questions / Uptime (where Uptime is the server’s running time in seconds).

Check Service ID

1
2
3
4
5
6
7
8
mysql> SHOW VARIABLES LIKE 'server_id';

+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| server_id     | 633025519 |
+---------------+-----------+
1 row in set (0.00 sec)

Each server (source and all replicas) in a replication chain must have a unique server_id. When a source server writes changes to its binary log, it includes its server_id with each event.

Replicas use this ID to:

  • Prevent loops: A replica will skip applying events that originated from a server with its own server_id to avoid infinite loops in multi-master or circular replication topologies.
  • Identify the source: In more complex replication setups, the server_id helps identify which server generated a particular set of changes

Check Users

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SELECT User, Host FROM mysql.user;

+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| root             | %         |
| myuser          | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)

Or you can use

1
2
SELECT USER(); 
SELECT CURRENT_USER();

These are the most common and direct ways to see the current user.

  • USER() returns the username and host that the client attempted to authenticate with.
  • CURRENT_USER() returns the username and host that the MySQL server actually authenticated the client connection with. This is the user account that determines your privileges.

They can sometimes be different (e.g., if you tried to connect with a non-existent user, MySQL might connect you as an anonymous user).

Check Active User States

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> SELECT Id, User, Host, db, Command, Time, State, Info \
FROM information_schema.processlist\G

*************************** 1. row ***************************
     Id: 8433
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: executing
   Info: SELECT Id, User, Host, db, Command, Time, State, Info FROM information_schema.processlist
*************************** 2. row ***************************
     Id: 4
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 2859469
  State: Waiting on empty queue
   Info: NULL
2 rows in set (0.01 sec)

Show DBs/Schemas

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> show databases;
mysql> show schemas;

+--------------------+
| Database           |
+--------------------+
| animals            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

Drop Schema

1
DROP SCHEMA animals;

List Tables

1
2
use <db>;
show tables;

Describe Table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql> describe animals.animals;

+---------+--------------+------+-----+---------+----------------+
| Field   | Type         | Null | Key | Default | Extra          |
+---------+--------------+------+-----+---------+----------------+
| id      | mediumint(9) | NO   | PRI | NULL    | auto_increment |
| name    | varchar(255) | YES  |     | NULL    |                |
| species | varchar(255) | YES  |     | NULL    |                |
| cute    | tinyint(1)   | YES  |     | NULL    |                |
+---------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

mysql> SHOW CREATE TABLE animals.animals\G

*************************** 1. row ***************************
       Table: animals
Create Table: CREATE TABLE `animals` (
  `id` mediumint(9) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `species` varchar(255) DEFAULT NULL,
  `cute` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

Drop Table

Please check the table dependency first

1
DROP TABLE animals.animals;

Create User

For example, create a user for replication purposes, logging in with root(or at least with create user privilege)

1
2
3
4
5
CREATE USER 'myuser'@'%' IDENTIFIED BY 'YourSecurePasswordHere';

-- with IP range
CREATE USER 'myuser'@'192.168.1.0/24' IDENTIFIED BY 'YourSecurePasswordHere';
CREATE USER 'myuser'@'192.168.1.%' IDENTIFIED BY 'YourSecurePasswordHere';

Grant Privileges

Grant privileges to user:

1
2
mysql> GRANT EVENT ON animals.* TO 'myuser'@'%';
mysql> FLUSH PRIVILEGES;

Show Privileges

1
2
3
4
5
6
7
8
mysql>  SHOW GRANTS FOR 'myuser'@'%';

+------------------------------------------------------------------------------------------------------------------+
| Grants for myuser@%                                                                                             |
+------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, RELOAD, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, SHOW VIEW, TRIGGER ON *.* TO `myuser`@`%` |
+------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Revoke Privileges

1
2
mysql> REVOKE EVENT ON `animals`.* FROM `myuser`@`%`;
mysql> FLUSH PRIVILEGES;

Check Replication

This is important if you launch the replication and how to check the progress or status.

On Source Server

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
SHOW MASTER STATUS\G

-- Only binlog
*************************** 1. row ***************************
             File: binlog.000041
         Position: 556
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)


-- when GTID is ON
*************************** 1. row ***************************
             File: mysql-bin.000451
         Position: 194
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: c390f45d-d4b5-11e9-99a5-42010af00084:1-96
1 row in set (0.00 sec)


SHOW VARIABLES LIKE 'server_id';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+


SHOW PROCESSLIST\G

*************************** 2. row ***************************
     Id: 2620
   User: myuser
   Host: 35.194.40.52:59948
     db: NULL
Command: Binlog Dump
   Time: 615115
  State: Source has sent all binlog to replica; waiting for more updates
   Info: NULL


-- when GTID is ON
*************************** 1. row ***************************
     Id: 11437
   User: myuser
   Host: 34.121.192.197:56772
     db: NULL
Command: Binlog Dump GTID
   Time: 800846
  State: Master has sent all binlog to slave; waiting for more updates
   Info: NULL

On Replica

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
SHOW SLAVE STATUS\G
mysql> SHOW REPLICA STATUS\G

*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 35.204.135.141
                  Source_User: speckle
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: mysql-bin.000009
          Read_Source_Log_Pos: 194
               Relay_Log_File: relay-log.000021
                Relay_Log_Pos: 410
        Relay_Source_Log_File: mysql-bin.000009
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table: mysql.%
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 194
              Relay_Log_Space: 701
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File:
           Source_SSL_CA_Path:
              Source_SSL_Cert:
            Source_SSL_Cipher:
               Source_SSL_Key:
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Source_Server_Id: 1
                  Source_UUID: c390f45d-d4b5-11e9-99a5-42010af00084
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 86400
                  Source_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Source_SSL_Crl:
           Source_SSL_Crlpath:
           Retrieved_Gtid_Set: c390f45d-d4b5-11e9-99a5-42010af00084:2
            Executed_Gtid_Set: 7b5e76d5-4971-11f0-8015-42010a400007:1-26,
c390f45d-d4b5-11e9-99a5-42010af00084:1-2
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 0
            Network_Namespace:
1 row in set (0.00 sec)

Replica_IO_Running (I/O Thread / Receiver Thread)::

This thread is responsible for connecting to the source server and reading the binary log events. Think of it as the “data fetcher.” It streams the changes (SQL statements, row changes, etc.) from the source’s binary logs.

Once received, the I/O thread writes these events to a local file on the replica called the relay log. The relay log acts as a temporary cache of the binary log events from the source.

If a replica is offline for a period, the I/O thread can quickly retrieve all the accumulated binary log events from the source once it reconnects, even if the SQL thread takes longer to process them.

Replica_SQL_Running (SQL Thread / Applier Thread):

This thread is responsible for reading the events from the relay log (written by the I/O thread) and executing them on the replica’s database. Think of it as the “data applier.”

Its goal is to apply the changes to the replica’s data as quickly and efficiently as possible to keep the replica synchronized with the source.

Check Binlog

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
-- log_bin_basename is the binlog file path
mysql> SHOW VARIABLES LIKE 'log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
+---------------------------------+-----------------------------+
5 rows in set (0.00 sec)

mysql> show variables where variable_name in \
('log_bin', 'binlog_format');
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
| log_bin       | ON    |
+---------------+-------+
2 rows in set (0.00 sec)


-- if you enabled binlog, the retention is set automatically
-- check binlog retention  binlog_expire_logs_seconds is for latest version
mysql> show variables where variable_name in \
('binlog_expire_logs_seconds', 'expire_logs_days');
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| binlog_expire_logs_seconds | 2592000 |
| expire_logs_days           | 0       |
+----------------------------+---------+
2 rows in set (0.00 sec)

mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000017 |       201 | No        |
| binlog.000018 |       201 | No        |
| binlog.000019 |       201 | No        |
| binlog.000020 |       201 | No        


-- check the location of bin log file and the file name pattern:
mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------------+
| Variable_name                   | Value                                 |
+---------------------------------+---------------------------------------+
| log_bin                         | ON                                    |
| log_bin_basename                | /usr/local/mysql/data/mysql-bin       |
| log_bin_index                   | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                   |
| log_bin_use_v1_row_events       | OFF                                   |
| sql_log_bin                     | ON                                    |
+---------------------------------+---------------------------------------+|

Inspect Binlog Event

You can do inspection by mysqlbinlog cli, but we can also do it from SQL, reference from CSQL PITR public document.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000031 |       809 | No        |
| binlog.000032 |       201 | No        |
| binlog.000033 |      2948 | No        |
| binlog.000034 |       519 | No        |
| binlog.000035 |       201 | No        |
| binlog.000036 |       201 | No        |


SHOW BINLOG EVENTS IN 'binlog.000033' limit 10;
+---------------+-----+----------------+-----------+-------------+----------------------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                               |
+---------------+-----+----------------+-----------+-------------+----------------------------------------------------+
| binlog.000033 |   4 | Format_desc    |         1 |         126 | Server ver: 8.0.42-0ubuntu0.20.04.1, Binlog ver: 4 |
| binlog.000033 | 126 | Previous_gtids |         1 |         157 |                                                    |
| binlog.000033 | 157 | Anonymous_Gtid |         1 |         236 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'               |
| binlog.000033 | 236 | Query          |         1 |         307 | BEGIN                                              |
| binlog.000033 | 307 | Table_map      |         1 |         505 | table_id: 523 (mysql.user)                         |
| binlog.000033 | 505 | Delete_rows    |         1 |         693 | table_id: 523 flags: STMT_END_F                    |
| binlog.000033 | 693 | Xid            |         1 |         724 | COMMIT /* xid=1459 */                              |
| binlog.000033 | 724 | Anonymous_Gtid |         1 |         801 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'               |
| binlog.000033 | 801 | Query          |         1 |         912 | drop schema animals /* xid=1621 */                 |
| binlog.000033 | 912 | Anonymous_Gtid |         1 |         989 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'               |
+---------------+-----+----------------+-----------+-------------+----------------------------------------------------+
10 rows in set (0.00 sec)


SHOW BINLOG EVENTS IN 'binlog.000033' from 157 limit 3;
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                 |
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
| binlog.000033 | 157 | Anonymous_Gtid |         1 |         236 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000033 | 236 | Query          |         1 |         307 | BEGIN                                |
| binlog.000033 | 307 | Table_map      |         1 |         505 | table_id: 523 (mysql.user)           |
+---------------+-----+----------------+-----------+-------------+--------------------------------------+
3 rows in set (0.00 sec)

Check GTID is ON

1
2
3
4
5
6
7
8
9
10
mysql> show variables where variable_name in \
('gtid_mode','enforce_gtid_consistency');

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| enforce_gtid_consistency | ON    |
| gtid_mode                | ON    |
+--------------------------+-------+
2 rows in set (0.01 sec)

Check MyISAM vs InnoDB

One server can have both MyISAM and InnoDB for different tables.

1
2
3
4
SELECT count(table_name) as table_count FROM information_schema.tables tab
WHERE engine != 'InnoDB'
AND table_type = 'BASE TABLE'
AND table_schema not in ('information_schema','sys','performance_schema','mysql');