So, every now and then we get customers asking if they can increase their memory because MySQL keeps on being killled by the kernel, mainly because on say a 2GB physical RAM server, MySQL eats it all up and even tries to use more than is there. So the kernel scheduler is like ‘no.. stop that’. This kind of thing could be avoided by configuring MySQL with proper limits as to not flood the physical hardware.

This is often and commonly overlooked in MySQL databases, and no tuning is done, but it’s important to base the MySQL configuration (/etc/my.conf) on the physical hardware of the server. So IF you increase the RAM on the server, to get the optimum speed you’d want to increase some of the RAM usage. A friend of mine said of a great trick used by many organisations of pointing the mysql filesystem to memory, this is a great performance increase, as it completely avoids the filesystem, the only downside is if the box turns off, the database is gone 😀

innodb_buffer_pool_size = 384M
key_buffer = 256M
query_cache_size = 1M
query_cache_limit = 128M
thread_cache_size = 8
max_connections = 400
innodb_lock_wait_timeout = 100

I found this config for a 2GB server on stack overflow, and it looks just about right. Adjusting the connections to suit should ensure that the box doesn’t get too overloaded, but also the memory is important too. One thing to bare in mind, by restricting RAM queries might not run as fast, but the database won’t suddenly go offline and process be killed this way. That’s what you want, really isn’t it;.

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;