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. <\/p>\n
Connect to a MySQL server<\/p>\n
\r\nmysql -u root -p \r\n<\/pre>\nConnecting 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!<\/p>\n
Display Databases in MySQL<\/p>\n
\r\nmysql> show databases;\r\n+--------------------+\r\n| Database |\r\n+--------------------+\r\n| information_schema |\r\n| mystuff |\r\n| wordpress |\r\n| mysql |\r\n| performance_schema |\r\n| somesitedb |\r\n+--------------------+\r\n6 rows in set (0.00 sec)\r\n<\/pre>\nChange active database<\/p>\n
\r\nmysql> use information_schema;\r\n\r\nReading table information for completion of table and column names\r\nYou can turn off this feature to get a quicker startup with -A\r\n\r\nDatabase changed\r\n<\/pre>\nShow tables within active database<\/p>\n
\r\nmysql> show tables;\r\n+---------------------------------------+\r\n| Tables_in_information_schema |\r\n+---------------------------------------+\r\n| CHARACTER_SETS |\r\n| COLLATIONS |\r\n| COLLATION_CHARACTER_SET_APPLICABILITY |\r\n| COLUMNS |\r\n| COLUMN_PRIVILEGES |\r\n| ENGINES |\r\n| EVENTS |\r\n| FILES |\r\n| GLOBAL_STATUS |\r\n| GLOBAL_VARIABLES |\r\n| KEY_COLUMN_USAGE |\r\n| PARAMETERS |\r\n| PARTITIONS |\r\n| PLUGINS |\r\n| PROCESSLIST |\r\n| PROFILING |\r\n| REFERENTIAL_CONSTRAINTS |\r\n| ROUTINES |\r\n| SCHEMATA |\r\n| SCHEMA_PRIVILEGES |\r\n| SESSION_STATUS |\r\n| SESSION_VARIABLES |\r\n| STATISTICS |\r\n| TABLES |\r\n| TABLESPACES |\r\n| TABLE_CONSTRAINTS |\r\n| TABLE_PRIVILEGES |\r\n| TRIGGERS |\r\n| USER_PRIVILEGES |\r\n| VIEWS |\r\n| INNODB_BUFFER_PAGE |\r\n| INNODB_TRX |\r\n| INNODB_BUFFER_POOL_STATS |\r\n| INNODB_LOCK_WAITS |\r\n| INNODB_CMPMEM |\r\n| INNODB_CMP |\r\n| INNODB_LOCKS |\r\n| INNODB_CMPMEM_RESET |\r\n| INNODB_CMP_RESET |\r\n| INNODB_BUFFER_PAGE_LRU |\r\n+---------------------------------------+\r\n40 rows in set (0.00 sec)\r\n<\/pre>\nSelect all records from a given table<\/p>\n
\r\nmysql> select * from CHARACTER_SETS;\r\n+--------------------+----------------------+-----------------------------+--------+\r\n| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION | MAXLEN |\r\n+--------------------+----------------------+-----------------------------+--------+\r\n| big5 | big5_chinese_ci | Big5 Traditional Chinese | 2 |\r\n| dec8 | dec8_swedish_ci | DEC West European | 1 |\r\n| cp850 | cp850_general_ci | DOS West European | 1 |\r\n| hp8 | hp8_english_ci | HP West European | 1 |\r\n| koi8r | koi8r_general_ci | KOI8-R Relcom Russian | 1 |\r\n| latin1 | latin1_swedish_ci | cp1252 West European | 1 |\r\n| latin2 | latin2_general_ci | ISO 8859-2 Central European | 1 |\r\n| swe7 | swe7_swedish_ci | 7bit Swedish | 1 |\r\n| ascii | ascii_general_ci | US ASCII | 1 |\r\n| ujis | ujis_japanese_ci | EUC-JP Japanese | 3 |\r\n| sjis | sjis_japanese_ci | Shift-JIS Japanese | 2 |\r\n| hebrew | hebrew_general_ci | ISO 8859-8 Hebrew | 1 |\r\n| tis620 | tis620_thai_ci | TIS620 Thai | 1 |\r\n| euckr | euckr_korean_ci | EUC-KR Korean | 2 |\r\n| koi8u | koi8u_general_ci | KOI8-U Ukrainian | 1 |\r\n| gb2312 | gb2312_chinese_ci | GB2312 Simplified Chinese | 2 |\r\n| greek | greek_general_ci | ISO 8859-7 Greek | 1 |\r\n| cp1250 | cp1250_general_ci | Windows Central European | 1 |\r\n| gbk | gbk_chinese_ci | GBK Simplified Chinese | 2 |\r\n| latin5 | latin5_turkish_ci | ISO 8859-9 Turkish | 1 |\r\n| armscii8 | armscii8_general_ci | ARMSCII-8 Armenian | 1 |\r\n| utf8 | utf8_general_ci | UTF-8 Unicode | 3 |\r\n| ucs2 | ucs2_general_ci | UCS-2 Unicode | 2 |\r\n| cp866 | cp866_general_ci | DOS Russian | 1 |\r\n| keybcs2 | keybcs2_general_ci | DOS Kamenicky Czech-Slovak | 1 |\r\n| macce | macce_general_ci | Mac Central European | 1 |\r\n| macroman | macroman_general_ci | Mac West European | 1 |\r\n| cp852 | cp852_general_ci | DOS Central European | 1 |\r\n| latin7 | latin7_general_ci | ISO 8859-13 Baltic | 1 |\r\n| utf8mb4 | utf8mb4_general_ci | UTF-8 Unicode | 4 |\r\n| cp1251 | cp1251_general_ci | Windows Cyrillic | 1 |\r\n| utf16 | utf16_general_ci | UTF-16 Unicode | 4 |\r\n| cp1256 | cp1256_general_ci | Windows Arabic | 1 |\r\n| cp1257 | cp1257_general_ci | Windows Baltic | 1 |\r\n| utf32 | utf32_general_ci | UTF-32 Unicode | 4 |\r\n| binary | binary | Binary pseudo charset | 1 |\r\n| geostd8 | geostd8_general_ci | GEOSTD8 Georgian | 1 |\r\n| cp932 | cp932_japanese_ci | SJIS for Windows Japanese | 2 |\r\n| eucjpms | eucjpms_japanese_ci | UJIS for Windows Japanese | 3 |\r\n+--------------------+----------------------+-----------------------------+--------+\r\n39 rows in set (0.00 sec)\r\n<\/pre>\nCreate a database<\/p>\n
\r\nmysql> CREATE database testdb;\r\nQuery OK, 1 row affected (0.00 sec)\r\n\r\nmysql> show databases;\r\n+--------------------+\r\n| Database |\r\n+--------------------+\r\n| information_schema |\r\n| mystuff |\r\n| wordpress |\r\n| mysql |\r\n| performance_schema |\r\n| somesitedb |\r\n| testdb |\r\n+--------------------+\r\n7 rows in set (0.00 sec)\r\n<\/pre>\nDisplay\/Describe Table Fields<\/p>\n
\r\nmysql> describe character_sets;\r\n+----------------------+-------------+------+-----+---------+-------+\r\n| Field | Type | Null | Key | Default | Extra |\r\n+----------------------+-------------+------+-----+---------+-------+\r\n| CHARACTER_SET_NAME | varchar(32) | NO | | | |\r\n| DEFAULT_COLLATE_NAME | varchar(32) | NO | | | |\r\n| DESCRIPTION | varchar(60) | NO | | | |\r\n| MAXLEN | bigint(3) | NO | | 0 | |\r\n+----------------------+-------------+------+-----+---------+-------+\r\n4 rows in set (0.01 sec)\r\n<\/pre>\nDeleting a database or a Table<\/p>\n
\r\nmysql> drop database testdb;\r\nQuery OK, 0 rows affected (0.00 sec)\r\n<\/pre>\nCounting the number of records in a table, in this case wordpress wp_comments<\/p>\n
\r\nmysql> select COUNT(*) FROM wp_comments\r\n -> ;\r\n+----------+\r\n| COUNT(*) |\r\n+----------+\r\n| 91 |\r\n+----------+\r\n1 row in set (0.00 sec)\r\n\r\nmysql> SELECT COUNT(*) FROM wp_posts;\r\n+----------+\r\n| COUNT(*) |\r\n+----------+\r\n| 56 |\r\n+----------+\r\n1 row in set (0.00 sec)\r\n<\/pre>\nCreate A New MySQL User<\/p>\n
\r\nmysql -u root -p\r\n\r\nmysql> use mysql;\r\nmysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));\r\nmysql> flush privileges; \r\n<\/pre>\nChange a MySQL Users password<\/p>\n
\r\n# mysql -u root -p\r\nmysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');\r\nmysql> flush privileges; \r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"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 … Continue reading