Everyday MySQL console commands and MySQL hints

PHPMyAdmin is a great tool, but due its web based nature it can sometimes fall a little short, especially when dealing with large amounts of data. Don't get me wrong, it's still very useful for browsing large data sets, but if you are looking to do imports, exports, adding indexes etc., then you really need to use the console or some other application.

To be honest, most of the commands here, you will find on many other websites, I just wanted to compile this list as a reference: All the following commands were ran using the console in BASH, but should work on Windows/Mac consoles.

Logging in with a supplied username and using password option:

mysql --user=your_username -p

Logging in with a supplied username and a supplied password:

mysql --user=your_username --password=your_password

Logging in to a different host with a supplied username and password:

mysql --host  host_address --user=your_username --password=your_password

List databases

SHOW DATABASES;

Select a table

USE table_name;

Add an index

When you have a million plus records, this can save so much time in your lookups. I had a table that 1.5 million records, using a single WHERE clause on a non-index field was taking around 1.9 seconds, adding an index reduced the time to 0.0005 seconds:

ALTER TABLE table_name add index (column_name);

Export a database to a file:

mysqldump --user=your_username --password=your_password database_name > name_and_path_of_sql_file_to_create.sql

Will use a modification of the above to show how you can use PHP to backup an entire database in a future post - you can read that here: backup up your MySQL server with PHP

Import a database on to a different host with a supplied username and password:

mysql --host host_address --user=your_username --password=your_password db_name < your_sql_file.sql

Hope this is of use to you.

James