The following MySQL Commands were originally split into several smaller blog posts that I had built up over the years, I have now consolidated the articles into a single post (feel free to link to this resource from your site).

Please note this article contains commands & examples for the mysql command line client, it does not contain information for phpMyadmin or similar GUI based software.
MySQL Set Root Password

By default MySQL has no password set, this might be fine for a private development environment but unacceptable for production servers. You can set the mysql root password various ways but below is a nice simple method that works:

1

    

mysqladmin -u root password YOURNEWPASSWORD

Set / Change MySQL Users Passwords from the Linux Shell

    

mysqladmin -u username -h your-mysql-host -p password ‘newpassword’

You should now be able to restart MySQL and login with your new root password.
How To Connect to MySQL

To connect to your local MySQL server from the command line enter:

    

mysql -u root -p

If you need to login to a remote MySQL server, you cn either SSH to the server and login or use the following commnd (if the server allows external connections):

    

mysql -h hostname -u root -p

MySQL Create Database

The following command will create a new MySQL database:

    

create database example_db;

Backup a MySQL Database using mysqldump

Backing up a MySQL database to a flat file is refered to as “dumping the database”, there are several ways to acomplish this taske here are a few of the methods I use.

Basic mysqldump to a .sql file:

    

mysqldump -u root -p database-name > /tmp/database-backup.sql

You can also dump the database and compress on the fly by piping it through gzip:

    

mysqldump -u root -p database-name | gzip -v > database-backup.sql.gz

Mysqldump a remote database & transfer over SSH using gzip compression

Note you should execute the following command on the remote server that is currently serving the database, so you are affectivly pushing the db to your local machine.

Mysqldump a remote mysql database to your local machine using SSH & gzip compression (a fast way of taking a backup of a remote database). :

    

mysqldump -u root -p database-name | gzip -c | ssh user@your-local-machine ‘cat > /tmp/database-backup.sql.gz’

Dump all MySQL Databases on a server

If you wish to dump all databses on a server to a single dump file enter:

    

mysqldump -u root -p your-root-password –opt >/tmp/databases.sql

Mysqldump & Skip Table(s)

While carrying out a nasty phpBB migration I was faced with the task of dumping a MyISAM databse with some broken tables, you will get an error “mysqldump: Error 1194” or something similar to:

    

mysqldump: Error 1194: Table ‘phpbb_sessions’ is marked as crashed and should be repaired when dumping table `phpbb_sessions` at row: 37 71.0%

The best option you have if you need to take a backup in it’s current state is to tell mysqldump to skip the tables with:

    

mysqldump -u username -p your-database –ignore-table=your-database.broken-table > your-database.sql

If you need to skip more than one table you can just add multiple, example below:

    

mysqldump -u username -p your-database –ignore-table=your-database.broken-table –ignore-table=your-database.broken-table2 > your-database.sql

Once you have a backup I would recommend repairing the tables.
Dump a specific table from a mysql database

    

mysqldump -c -u username -p your-pass database-name table-name > /tmp/db-name.table-name.sql

Import a MySQL Database

Simple mysql db import from a .sql file:

    

mysql -u username -p -h localhost database-name < database-backup.sql

Import a mysql database from .sql.gz

    

zcat database-backup.sql.gz | mysql -u root -p database-name

Import a .sql file from the mysql command line (you can se the output on the console as it imports, handy if your getting an import error from mysql), first select the mysql database you wish to import into and run:

    

source ./db-backup.sql

Select a Database in MySQL

How to select a database in mysql:

    

user database-name;

Show Tabels in a Database

First select the databse you wish to use and run the following to show tables in a mysql databse:

    

show tables;

Create MySQL User

The following example creates a MySQL user called “jesus” with the password “jedimaster”:

    

grant usage on *.* to jesus@localhost identified by ‘jedimaster’;

Next you need to grant the user permission to access your database:

    

grant all privileges on heaven_db.* to jesus@localhost

The above will allow permission for the user “jesus” on the database “heaven_db”.

If you want “jesus” to have access to all databases on the server you would enter:

    

grant all privileges on *.* to jesus@localhost;

Show MySQL Database Size

The simple way is to use the filesystem to show the mysql database size on the disk with:

    

cd /var/lib/mysql && ls -lh

If you need to find out the size of the mysql database from within mysql you could use:

    

SELECT table_schema “Database-Name”, SUM( data_length + index_length) / 1024 / 1024 “Data Base Size in MB” FROM information_schema.TABLES GROUP BY table_schema;

List MySQL Databases

The following will list all mysql databases on a server:

    

show databases;

This will give you an output similar to:

    

+——————–+
| Database           |
+——————–+
| mysql              |
| snort_log          |
| squirrelmail       |
| ssweb              |
| test               |
| wikidb             |
+——————–+
13 rows in set (0.07 sec)

Drop A MySQL Database (deletes a db)

The following will drop a databases, when you drop a database you are deleting it. Be careful with this command…

    

drop database db-name;

Drop a MySQL Table

The following will delete (drop) a mysql table, you need to select the database you wish to use first.

    

drop table table-name;

How To Reset the MySQL root password

The following proccess will allow you to reset the mysql root password:

Stop mysql:

/etc/init.d/mysqld stop

Start mysql in safe mode:

mysqld_safe –skip-grant-tables &

Login as root:

 

mysql -u root

Set the mysql root password:

use mysql;
update user set password=PASSWORD(“new-root-passwd”) where user=’root’;
flush privileges;
quit

Restart the mysql service and you can login with your new password:

/etc/init.d/mysql restart

Create a MySQL table

Here is the basic create table syntax for mysql:

CREATE TABLE example (
id INT,
data VARCHAR(100)
);

Here is a more complex example:

CREATE TABLE table-name (firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),suffix VARCHAR(3),officeid VARCHAR(10),userid VARCHAR(15),username VARCHAR(8),email VARCHAR(35),phone VARCHAR(25), groups VARCHAR(15),datestamp DATE,timestamp time,pgpemail VARCHAR(255));

Create an INNODB Table in MySQL

The following will create an innodb table:

 CREATE TABLE your_table_name_innodb (
 id INT,
 data VARCHAR(100)
 ) TYPE=innodb;

Convert MyISAM to INNODB

It goes without saying, backup up your db first before running such a task, but here is the mysql syntax to convert a MyISAM table to INNODB:

    

ALTER TABLE ENGINE=INNODB;

Repair Broken Table(s) in MySQL

If you have a a corrupt / broken table (pretty common with MyiSAM) then take a dump (see the skip broken table with mysqldump instructions above) and then run:

    

repair table broke_table_name;

Show MySQL Database Fields & Field Formats

describe table-name;

MySQL Show Table Data (Displays the contents of a table)

SELECT * FROM table-name;

Show Columns in a MySQL Table

show columns from table-name;

Add a new column in MySQL

The following is an example of how to add a new column in mysql:

    

alter table table-name add column new-column varchar (20);

Delete a Column in MySQL

The following is an example of how to delete (drop) a column in mysql:

    

alter table table-name drop column column-name;

Delete a Row from a field

How to delete a row:

    

DELETE from table-name where field-name = ‘darth-vader’;

Show How Many Rows in a MySQL Table

SELECT COUNT(*) FROM table-name;

MySQL Join Tables

How to join tables in MySQL:

    

SELECT column_names FROM table-1, table-2 WHERE (table-1.column = table-2.column);

MySQL SUM Column Example

SELECT SUM(*) FROM table-name;

Show MySQL & List in Descending Order (DESC)

Show records from col6 and col5 and sort in a descending order using col6:

SELECT col6,col5 FROM table-name ORDER BY col6 DESC;

MySQL Show Records & List in Ascending Order (MySQL ASC)

Show records from col6 and col5 and sort in a ascending order using col6:

SELECT col6,col5 FROM table-name ORDER BY col6 ASC;

MySQL Show Unique Records

Shows all unique records from a mysql table:

    

SELECT DISTINCT column-name FROM table-name;

Search MySQL Records using a Regular Expression

This regular expression example will show you how to search for MySQL records using regular expressions and the REGXP Binary, the following example will return all results beging with the lower case letter z.

    

SELECT * FROM table-name WHERE rec RLIKE “^z”;

Show Rows Containing a Value

This example will show all rows containing “jesus”:

    

SELECT * FROM table-name WHERE field-name = “jesus”;

MySQL Search for a Record Matching (Various Examples)

Search for records with the name “Jesus” born in “1984”:

    

SELECT * FROM table-name WHERE name = “Jesus” AND year = ‘1984’;

Search for anyone called “Jesus” with the phone number “911”

    

SELECT * FROM table-name WHERE name = “Jesus” AND year = ‘1984’;

Search MySQL for any records matching the name “Jesus” with the phone number “911” and sort by phone number:

    

SELECT * FROM table-name WHERE name != “Jesus” AND phone_number = ‘911’ order by phone_number;

Show all records starting with “Jesus” and the phone number “911”:

  

SELECT * FROM table-name WHERE name like “Jesus%” AND phone_number = ‘911’;

Do the same as about but only show records 1 to 10:

  

SELECT * FROM [table name] WHERE name like Dave%” AND phone_number = ‘911’ limit 1,10;

Feel free to link to this resource from your blog, if you have any suggestions for additional commands please drop me a comment below and I will amend the post.

[mysqld]
socket=/path/to/mysql.sock
datadir=/var/lib/mysql
skip-locking
skip-innodb
# MySQL 4.x has query caching available.
# Enable it for vast improvement and it may be all you need to tweak.
query_cache_type=1
query_cache_limit=1M
query_cache_size=32M
# max_connections=500
# Reduced to 200 as memory will not be enough for 500 connections.
# memory=key_buffer+(sort_buffer_size+read_buffer_size)*max_connections
# which is now: 64 + (1 + 1) * 200 = 464 MB
# max_connections = approx. MaxClients setting in httpd.conf file
# Default set to 100.
#max_connections=200
#interactive_timeout=180
interactive_timeout=100
#wait_timeout=180
#wait_timeout=100
# Reduced wait_timeout to prevent idle clients holding connections.
#wait_timeout=30
wait_timeout=15
connect_timeout=10
# max_connect_errors is set to 10 by default
#max_connect_errors=10
#table_cache=256
#table_cache=1024
# Checked opened tables and adjusted accordingly after running for a while.
table_cache=512
#tmp_table_size=32M by default
#thread_cache=128
# Reduced it to 32 to prevent memory hogging. Also, see notes below.
thread_cache=32
# key_buffer=258M
# Reduced it by checking current size of *.MYI files, see notes below.
key_buffer=128M
# Commented out the buffer sizes and keeping the default.
# sort_buffer_size=2M by default.
#sort_buffer_size=1M
# read_buffer_size=128K by default.
#read_buffer_size=1M
# 1Mb of read_rnd_buffer_size for 1GB RAM — see notes below.
# read_rnd_buffer_size=256K by default.
#read_rnd_buffer_size=1M
# myisam_sort_buffer_size used for ALTER, OPTIMIZE, REPAIR TABLE commands.
# myisam_sort_buffer_size=8M by default.
#myisam_sort_buffer_size=64M
# thread_concurrency = 2 * (no. of CPU)
thread_concurrency=2
# log slow queries is a must. Many queries that take more than 2 seconds.
# If so, then your tables need enhancement.
log_slow_queries=/var/log/mysqld.slow.log
long_query_time=2

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

[client]
socket=/path/to/mysql.sock

Below are notes on some of the important variables, I took down while tuning the config file.

query_cache_size:
MySQL 4 provides one feature that can prove very handy – a query cache. In a situation where the database has to repeatedly run the same queries on the same data set, returning the same results each time, MySQL can cache the result set, avoiding the overhead of running through the data over and over and is extremely helpful on busy servers.
key_buffer_size:
The value of key_buffer_size is the size of the buffer used with indexes. The larger the buffer, the faster the SQL command will finish and a result will be returned. The rule-of-thumb is to set the key_buffer_size to at least a quarter, but no more than half, of the total amount of memory on the server. Ideally, it will be large enough to contain all the indexes (the total size of all .MYI files on the server).
A simple way to check the actual performance of the buffer is to examine four additional variables: key_read_requests, key_reads, key_write_requests, and key_writes.
If you divide the value of key_read by the value of key_reads_requests, the result should be less than 0.01. Also, if you divide the value of key_write by the value of key_writes_requests, the result should be less than 1.
table_cache:
The default is 64. Each time MySQL accesses a table, it places it in the cache. If the system accesses many tables, it is faster to have these in the cache. MySQL, being multi-threaded, may be running many queries on the table at one time, and each of these will open a table. Examine the value of open_tables at peak times. If you find it stays at the same value as your table_cache value, and then the number of opened_tables starts rapidly increasing, you should increase the table_cache if you have enough memory.
sort_buffer:
The sort_buffer is very useful for speeding up myisamchk operations (which is why it is set much higher for that purpose in the default configuration files), but it can also be useful everyday when performing large numbers of sorts.
read_rnd_buffer_size:
The read_rnd_buffer_size is used after a sort, when reading rows in sorted order. If you use many queries with ORDER BY, upping this can improve performance. Remember that, unlike key_buffer_size and table_cache, this buffer is allocated for each thread. This variable was renamed from record_rnd_buffer in MySQL 4.0.3. It defaults to the same size as the read_buffer_size. A rule-of-thumb is to allocate 1KB for each 1MB of memory on the server, for example 1MB on a machine with 1GB memory.
thread_cache:
If you have a busy server that’s getting a lot of quick connections, set your thread cache high enough that the Threads_created value in SHOW STATUS stops increasing. This should take some of the load off of the CPU.
tmp_table_size:
“Created_tmp_disk_tables” are the number of implicit temporary tables on disk created while executing statements and “created_tmp_tables” are memory-based. Obviously it is bad if you have to go to disk instead of memory all the time.