Back to MySQL
MySQL Commands
1. Administrator
1.1. 常用的命令
SHOW STATUS LIKE '%THREAD%'; //Threads_connected，当前连接数 SHOW VARIABLES LIKE '%connect%'; //max_connections，配置的最大连接数
1.2. SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
mysql> SHOW VARIABLES; ... 329 rows in set (0.00 sec)
mysql> show variables like '%storage_engine%'; +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | default_storage_engine | MyISAM | | storage_engine | MyISAM | +------------------------+--------+ 2 rows in set (0.00 sec) mysql> show create table <Table Name>;
mysql> SHOW VARIABLES like '%query%'; +------------------------------+-----------------------------------------+ | Variable_name | Value | +------------------------------+-----------------------------------------+ | ft_query_expansion_limit | 20 | | have_query_cache | YES | | long_query_time | 3.000000 | | query_alloc_block_size | 8192 | | query_cache_limit | 2097152 | | query_cache_min_res_unit | 2048 | | query_cache_size | 536870912 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | | slow_query_log | OFF | | slow_query_log_file | /var/log/mysql/slow-query.log | +------------------------------+-----------------------------------------+ 12 rows in set (0.00 sec)
mysql> SHOW VARIABLES like '%%'
mysql> SHOW VARIABLES like '%%'
1.3. Show Privileges;
MySQL [(none)]> show privileges; +-------------------------+---------------------------------------+-------------------------------------------------------+ | Privilege | Context | Comment | +-------------------------+---------------------------------------+-------------------------------------------------------+ | Alter | Tables | To alter the table | | Alter routine | Functions,Procedures | To alter or drop stored functions/procedures | | Create | Databases,Tables,Indexes | To create new databases and tables | | Create routine | Databases | To use CREATE FUNCTION/PROCEDURE | | Create temporary tables | Databases | To use CREATE TEMPORARY TABLE | | Create view | Tables | To create new views | | Create user | Server Admin | To create new users | | Delete | Tables | To delete existing rows | | Drop | Databases,Tables | To drop databases, tables, and views | | Event | Server Admin | To create, alter, drop and execute events | | Execute | Functions,Procedures | To execute stored routines | | File | File access on server | To read and write files on the server | | Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess | | Index | Tables | To create or drop indexes | | Insert | Tables | To insert data into tables | | Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) | | Process | Server Admin | To view the plain text of currently executing queries | | Proxy | Server Admin | To make proxy user possible | | References | Databases,Tables | To have references on tables | | Reload | Server Admin | To reload or refresh tables, logs and privileges | | Replication client | Server Admin | To ask where the slave or master servers are | | Replication slave | Server Admin | To read binary log events from the master | | Select | Tables | To retrieve rows from table | | Show databases | Server Admin | To see all databases with SHOW DATABASES | | Show view | Tables | To see views with SHOW CREATE VIEW | | Shutdown | Server Admin | To shut down the server | | Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. | | Trigger | Tables | To use triggers | | Create tablespace | Server Admin | To create/alter/drop tablespaces | | Update | Tables | To update existing rows | | Usage | Server Admin | No privileges - allow connect only | +-------------------------+---------------------------------------+-------------------------------------------------------+ 31 rows in set (0.00 sec)
1.4. Create Database
Create a database named jiradb with default charset utf8
CREATE DATABASE jiradb
DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;MySQL [(none)]> SHOW CREATE DATABASE jiradb; +-----------+--------------------------------------------------------------------+ | Database | Create Database | +-----------+--------------------------------------------------------------------+ | videotest | CREATE DATABASE `jiradb` /*!40100 DEFAULT CHARACTER SET utf8 */ | +-----------+--------------------------------------------------------------------+ 1 row in set (0.00 sec)
1.5. ALTER TABLE
alter table `gas_click_100287`
Add column `sub_channel` varchar(50) DEFAULT NULL COMMENT '子渠道' AFTER `channel_id`;
ALTER TABLE `gas_click_100307` MODIFY `dynamic_callbackurl` VARCHAR(1024);
1.6. Account Management
List Account use root login
mysql root@localhost:mysql> select host, user from mysql.user;
+-----------+--------------+
| host | user |
|-----------+--------------|
| % | root |
| % | tmp |
+-----------+--------------+
2 rows in set
Time: 0.001s
1.6.1. Add User
Add a root user named liyan
GRANT ALL PRIVILEGES ON *.* TO liyan@'%'
IDENTIFIED BY 'password' WITH GRANT OPTION;
Add an account named jira(jira/password) on jiradb Add an account for application named app1(app1/password) on wp_li3huo GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE ON jiradb.* TO jira@'%'
IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON wp_li3huo.* TO app1@'%'
IDENTIFIED BY 'password';
1.6.1.1. For MySQL 4.1.20
--V4.1.20
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON {tbl_name | * | *.* | db_name.*}
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER ON jiradb.* TO jira@'%'
IDENTIFIED BY 'password';
1.6.1.2. For MySQL 8.0
https://stackoverflow.com/questions/50177216/how-to-grant-all-privileges-to-root-user-in-mysql-8-0
CREATE USER 'foo'@'localhost' IDENTIFIED WITH mysql_native_password BY 'bar'; GRANT ALL PRIVILEGES ON database_name.* TO'foo'@'localhost'; SELECT * FROM mysql.User WHERE user='foo'\G
1.7. Dump Database
o --no-data, -d
Do not write any table row information (that is, do not dump table
contents). This is useful if you want to dump only the CREATE TABLE
statement for the table (for example, to create an empty copy of
the table by loading the dump file).
o --compact
Produce more compact output. This option enables the
--skip-add-drop-table, --skip-add-locks, --skip-comments,
--skip-disable-keys, and --skip-set-charset options.
Note
Prior to MySQL 5.1.21, this option did not create valid SQL if
the database dump contained views. The recreation of views
requires the creation and removal of temporary tables and this
option suppressed the removal of those temporary tables. As a
workaround, use --compact with the --add-drop-table option and
then manually adjust the dump file.mysqldump -h192.168.1.216 -ujira -ppassword jiradb > jiradb.sql
mysqldump: Got error: 1146: Table 'db_name.table_name' doesn't exist when using LOCK TABLES
mysqlcheck -u mysql_username -p database_name
drop error tables and dump again
$ fab hosts.li3huo wiki.wp_db_backup
[li3huo.com] Executing task 'wiki.wp_db_backup'
[li3huo.com] sudo: mysqldump --opt wp_li3huo |gzip -c > wp_li3huo-2014-05-30.sql.gz
[li3huo.com] download: /srv/fabric/li3huo.com/wp_li3huo-2014-05-30.sql.gz <- /home/liyan/wp_li3huo-2014-05-30.sql.gz
1.8. Show Process List
mysql -h192.168.1.216 -uliyan -ppassword mysql> show processlist;
1.9. Max Connections(default 100)
vi /etc/mysql/my.cnf
#max_connections = 100
1.9.1. Change by SQL
https://www.electrictoolbox.com/update-max-connections-mysql/
show variables like "max_connections";
set global max_connections = 200;
2. for Users
select CONVERT_TZ(NOW(),'+00:00','+8:00') as ts
2.1. SHOW COLUMNS Syntax
https://dev.mysql.com/doc/refman/5.0/en/show-columns.html
MariaDB [wp_li3huo]> show full columns from wp_users; +---------------------+---------------------+------+-----+---------------------+----------------+---------+ | Field | Type | Null | Key | Default | Extra | Comment | +---------------------+---------------------+------+-----+---------------------+----------------+---------+ | ID | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | | user_login | varchar(60) | NO | MUL | | | | | user_pass | varchar(64) | NO | | | | | | user_nicename | varchar(50) | NO | MUL | | | | | user_email | varchar(100) | NO | | | | | | user_url | varchar(100) | NO | | | | | | user_registered | datetime | NO | | 0000-00-00 00:00:00 | | | | user_activation_key | varchar(60) | NO | | | | | | user_status | int(11) | NO | | 0 | | | | display_name | varchar(250) | NO | | | | | +---------------------+---------------------+--------+------+-----+---------------------+-------+---------+ 10 rows in set (0.00 sec) SET @Table_Name= '<Table Name>'; SELECT COLUMN_NAME as 'Field', COLUMN_TYPE as 'Type', IS_NULLABLE as 'Null', COLUMN_KEY as 'Key', COLUMN_DEFAULT as 'Default', COLUMN_COMMENT as 'Comment' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME= @Table_Name ORDER BY ORDINAL_POSITION; select @Table_Name as 'Table_Name';
https://dev.mysql.com/doc/refman/5.7/en/columns-table.html
> select version() +-------------+ | version() | |-------------| | 5.6.35-log | +-------------+ > SELECT COLUMN_NAME Field, DATA_TYPE Type, COLUMN_DEFAULT "Default", COLUMN_COMMENT Comment FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'land_channel'; +--------------+----------+-----------+-------------------- | Field | Type | Default | Comment |--------------+----------+-----------+-------------------- | id | smallint | <null> | 自增主键 | channel_id | varchar | <null> | 渠道标识 | channel_name | varchar | <null> | 渠道名称 | create_time | int | <null> | 创建时间 | update_time | int | 0 | 更新时间 | status | tinyint | 1 | 状态: 1生效，0失效 +--------------+----------+-----------+--------------------
2.2. Execute multiple MySQL commands
mysql -h$host -u$user -p$password db_dbname < cmds.txt
#!/bin/sh
echo "This program will update blahblahblah..."
echo "After starting this program, enter your database password for root"
DATABASENAME=wordpress
mysql -u root -p <<-ENDMARKER
use ${DATABASENAME};
UPDATE wp_posts SET post_content = replace(post_content, 'http://oldurl.com/image/image1.jpg', http://newurl.com/image/image1.jpg');
UPDATE wp_posts SET post_content = replace(postcontent, 'http://oldurl.com/image/image2.jpg', http://newurl.com/image/image2.jpg');
UPDATE wp_posts SET post_content = replace(post_content, 'http://oldurl.com/image/image3.jpg', http://newurl.com/image/image3.jpg');
commit;
ENDMARKER
3. Mysql Client Configuration
-- change default charset SET character_set_results = utf8; -- show column comment show create table <table name>\G show full columns from <table name>\G
3.1. Helpful Functions
3.1.1. MD5
mysql> SELECT MD5('testing')\G
*************************** 1. row ***************************
MD5('testing'): ae2b1fca515949e5d54fb22b8ed95575
1 row in set (0.00 sec)
3.1.2. Convert timestamp to date in MYSQL
https://stackoverflow.com/questions/9251561/convert-timestamp-to-date-in-mysql-query
SELECT UNIX_TIMESTAMP(STR_TO_DATE('2009-06-02','%Y-%m-%d')) as "UNIX TIME STAMP"
+-------------------+
| UNIX TIME STAMP |
|-------------------|
| 1243872000 |
+-------------------+
select from_unixtime('1243872000', '%Y-%m-%d') as Date
+------------+
| Date |
|------------|
| 2009-06-02 |
+------------+
4. Solved Iussues
4.1. Resolve Blocked
Caused by: java.sql.SQLException: null, message from server: "Host 'didibabawu' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'"
#mysqladmin flush-hosts
4.2. 设置慢查询最长时间(long_query_time)
对MySQL5.21+的版本，long_query_time单位是s，但其实这已经是变相支持毫秒级别了，比如查询时间大于100ms将被记录到slow log中。
mysql> set global long_query_time=0.1;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye!
mysql> select sleep(0.2);
+------------+
| sleep(0.2) |
+------------+
| 0 |
+------------+
1 row in set (0.21 sec)
# administrator command: Quit;
# Time: 120412 15:39:15
# User@Host: root[root] @ localhost []
# Query_time: 0.202244 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1334216355;
select sleep(0.2);
4.3. 压测监控
4.3.1. 慢查询设置
-- 开启慢查询
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)
-- 本机日志位置
mysql > show variables like '%slow_query_log%';
+---------------------+----------------------------------------------+
| Variable_name | Value |
|---------------------+----------------------------------------------|
| slow_query_log | ON |
| slow_query_log_file | /usr/local/mysql/data/10-19-108-171-slow.log |
+---------------------+----------------------------------------------+
-- 设置慢查询标准
mysql> set global long_query_time=0.1;
-- 未使用索引的查询也计入慢查询
mysql> set global log_queries_not_using_indexes=1;
mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+---------+
| Variable_name | Value |
|-------------------------------+---------|
| log_queries_not_using_indexes | ON |
+-------------------------------+---------+
4.3.2. 压测监控
website> SHOW STATUS WHERE `variable_name` = 'Threads_connected';
+-------------------+---------+
| Variable_name | Value |
|-------------------+---------|
| Threads_connected | 14 |
+-------------------+---------+
1 row in set
Time: 0.018s
website> SHOW STATUS WHERE `variable_name` = 'Threads_connected';
+-------------------+---------+
| Variable_name | Value |
|-------------------+---------|
| Threads_connected | 8 |
+-------------------+---------+
1 row in set
Time: 0.018s
➜ ~ sudo tail -f /usr/local/mysql/data/10-19-108-171-slow.log
4.3.3. 容量监控
website> select table_schema as db, concat(round(sum(data_length/1024/1024),2),' MB') as data from information_schema.tables group by table_schema;
+--------------------+---------+
| db | data |
|--------------------+---------|
| information_schema | 0.00 MB |
| website | 0.23 MB |
+--------------------+---------+