MySQL CLI – useful commands

I have had for some time ago a file I would like to share with all you. These are the most common commands used by me while I am working with mysql on ubuntu. You are not going to have a GUI always, so it is good to learn some basic lines to work with mysql database.

Set a password:

mysqladmin -u <user> password <password>
e.g. mysqladmin -u root password my_password

Dump a database

mysqldump <db_name> -u <username> -p >> <somedatabasetable>.sql
e.g. mysqldump andnovar_db -u root -p >> database.sql

Login to mysql cli:

mysql -u <user> -p
e.g. mysql -u root -p

On cli:

create database <db_name>

use <db_name>     // set the database in order to do operations on it.

source <some_database>.sql       // Load SQL into a <db_name>

For some joomla problems with table jos_session:

use <db_name>

check table jos_session

repair table jos_session;

In the worst case:

myisamchk -r /var/lib/mysql/<database>/<the_Table>.MYI

If it still does not work (take more time):

myisamchk -r –safe-recover /var/lib/mysql/<database>/<the_Table>.MYI