MySQL Basics

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; 

Leave a Reply

Your email address will not be published. Required fields are marked *