So, I use quite a fair bit of MySQL at work, especially when customer has some issues with their MySQL, anything from tuning and performance analysis to system configuration and solution architecture. I thought I’d put together a little article that had some of the most common commands using MySQL.
Connect to a MySQL server
mysql -u root -p
Connecting to the local mysql server as root level user and use password authentication. It’s possible to supply the password directly after the -p so you don’t have to type it at the commandline but please don’t do this with the root user!
Display Databases in MySQL
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mystuff | | wordpress | | mysql | | performance_schema | | somesitedb | +--------------------+ 6 rows in set (0.00 sec)
Change active database
mysql> use information_schema; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed
Show tables within active database
mysql> show tables; +---------------------------------------+ | Tables_in_information_schema | +---------------------------------------+ | CHARACTER_SETS | | COLLATIONS | | COLLATION_CHARACTER_SET_APPLICABILITY | | COLUMNS | | COLUMN_PRIVILEGES | | ENGINES | | EVENTS | | FILES | | GLOBAL_STATUS | | GLOBAL_VARIABLES | | KEY_COLUMN_USAGE | | PARAMETERS | | PARTITIONS | | PLUGINS | | PROCESSLIST | | PROFILING | | REFERENTIAL_CONSTRAINTS | | ROUTINES | | SCHEMATA | | SCHEMA_PRIVILEGES | | SESSION_STATUS | | SESSION_VARIABLES | | STATISTICS | | TABLES | | TABLESPACES | | TABLE_CONSTRAINTS | | TABLE_PRIVILEGES | | TRIGGERS | | USER_PRIVILEGES | | VIEWS | | INNODB_BUFFER_PAGE | | INNODB_TRX | | INNODB_BUFFER_POOL_STATS | | INNODB_LOCK_WAITS | | INNODB_CMPMEM | | INNODB_CMP | | INNODB_LOCKS | | INNODB_CMPMEM_RESET | | INNODB_CMP_RESET | | INNODB_BUFFER_PAGE_LRU | +---------------------------------------+ 40 rows in set (0.00 sec)
Select all records from a given table
mysql> select * from CHARACTER_SETS; +--------------------+----------------------+-----------------------------+--------+ | CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN | +--------------------+----------------------+-----------------------------+--------+ | big5 | big5_chinese_ci | Big5 Traditional Chinese | 2 | | dec8 | dec8_swedish_ci | DEC West European | 1 | | cp850 | cp850_general_ci | DOS West European | 1 | | hp8 | hp8_english_ci | HP West European | 1 | | koi8r | koi8r_general_ci | KOI8-R Relcom Russian | 1 | | latin1 | latin1_swedish_ci | cp1252 West European | 1 | | latin2 | latin2_general_ci | ISO 8859-2 Central European | 1 | | swe7 | swe7_swedish_ci | 7bit Swedish | 1 | | ascii | ascii_general_ci | US ASCII | 1 | | ujis | ujis_japanese_ci | EUC-JP Japanese | 3 | | sjis | sjis_japanese_ci | Shift-JIS Japanese | 2 | | hebrew | hebrew_general_ci | ISO 8859-8 Hebrew | 1 | | tis620 | tis620_thai_ci | TIS620 Thai | 1 | | euckr | euckr_korean_ci | EUC-KR Korean | 2 | | koi8u | koi8u_general_ci | KOI8-U Ukrainian | 1 | | gb2312 | gb2312_chinese_ci | GB2312 Simplified Chinese | 2 | | greek | greek_general_ci | ISO 8859-7 Greek | 1 | | cp1250 | cp1250_general_ci | Windows Central European | 1 | | gbk | gbk_chinese_ci | GBK Simplified Chinese | 2 | | latin5 | latin5_turkish_ci | ISO 8859-9 Turkish | 1 | | armscii8 | armscii8_general_ci | ARMSCII-8 Armenian | 1 | | utf8 | utf8_general_ci | UTF-8 Unicode | 3 | | ucs2 | ucs2_general_ci | UCS-2 Unicode | 2 | | cp866 | cp866_general_ci | DOS Russian | 1 | | keybcs2 | keybcs2_general_ci | DOS Kamenicky Czech-Slovak | 1 | | macce | macce_general_ci | Mac Central European | 1 | | macroman | macroman_general_ci | Mac West European | 1 | | cp852 | cp852_general_ci | DOS Central European | 1 | | latin7 | latin7_general_ci | ISO 8859-13 Baltic | 1 | | utf8mb4 | utf8mb4_general_ci | UTF-8 Unicode | 4 | | cp1251 | cp1251_general_ci | Windows Cyrillic | 1 | | utf16 | utf16_general_ci | UTF-16 Unicode | 4 | | cp1256 | cp1256_general_ci | Windows Arabic | 1 | | cp1257 | cp1257_general_ci | Windows Baltic | 1 | | utf32 | utf32_general_ci | UTF-32 Unicode | 4 | | binary | binary | Binary pseudo charset | 1 | | geostd8 | geostd8_general_ci | GEOSTD8 Georgian | 1 | | cp932 | cp932_japanese_ci | SJIS for Windows Japanese | 2 | | eucjpms | eucjpms_japanese_ci | UJIS for Windows Japanese | 3 | +--------------------+----------------------+-----------------------------+--------+ 39 rows in set (0.00 sec)
Create a database
mysql> CREATE database testdb; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mystuff | | wordpress | | mysql | | performance_schema | | somesitedb | | testdb | +--------------------+ 7 rows in set (0.00 sec)
Display/Describe Table Fields
mysql> describe character_sets; +----------------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------------+-------------+------+-----+---------+-------+ | CHARACTER_SET_NAME | varchar(32) | NO | | | | | DEFAULT_COLLATE_NAME | varchar(32) | NO | | | | | DESCRIPTION | varchar(60) | NO | | | | | MAXLEN | bigint(3) | NO | | 0 | | +----------------------+-------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)
Deleting a database or a Table
mysql> drop database testdb; Query OK, 0 rows affected (0.00 sec)
Counting the number of records in a table, in this case wordpress wp_comments
mysql> select COUNT(*) FROM wp_comments
-> ;
+----------+
| COUNT(*) |
+----------+
| 91 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) FROM wp_posts;
+----------+
| COUNT(*) |
+----------+
| 56 |
+----------+
1 row in set (0.00 sec)
Create A New MySQL User
mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));
mysql> flush privileges;
Change a MySQL Users password
# mysql -u root -p
mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
mysql> flush privileges;