Show MySQL processlist

1
SHOW FULL PROCESSLIST

Check Binlog

1
show variables like 'log_bin'

show tables

SHOW TABLES command provides you with an option that allows you to filter the returned tables using the LIKE operator or an expression in the WHERE clause as follows:

1
2
3
4
5
6
7
8
9
10
11
12
SHOW TABLES LIKE pattern;

SHOW TABLES WHERE expression;

> SHOW TABLES LIKE 'p%';
+------------------------------+
| Tables_in_testdb (p%) |
+------------------------------+
| productlines                 |
| products                     |
+------------------------------+
2 rows in set (0.00 sec)

添加权限

1
2
3
4
5
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root';
Query OK, 0 rows affected (0.27 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.25 sec)

lock a row

1
2
3
4
START TRANSACTION
SELECT * FROM x_tab WHERE id = 2 FOR UPDATE;
UPDATE x_tab SET amount = amount + 5 WHERE id = 2;
COMMIT

rename table

1
RENAME TABLE old_db.table TO new_db.table;

generate rename SQL

1
2
mysql -u username -ppassword old_db -sNe 'show tables' | while read table; \ 
    do mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table"; done

show the tables that end with the string ‘es’

1
2
3
4
5
6
7
8
9
> SHOW TABLES LIKE '%es';
+-------------------------------+
| Tables_in_testdb (%es) |
+-------------------------------+
| employees                     |
| offices                       |
| productlines                  |
+-------------------------------+
3 rows in set (0.00 sec)

query table_schema

1
select table_schema, count(1) from information_schema.tables where table_schema like "keyword%" group by table_schema;

create view

1
CREATE VIEW your_view_tab AS SELECT * FROM your_tab;