MySql commands


This is a short-list of handy MySQL commands that I have to use time and time again, in my development machine as well as in servers and for importing and exporting databases.

Login to mysql console (from unix bash)

	$ mysql -u user -p
	# if host name is required
	$ mysql -h hostname -u user -p

Create a new database

	mysql> create database databasename;

List all databases

	mysql> show databases;

Switch to a database

	mysql> use database_name;

List tables

	mysql> show tables;

See database’s field formats.

	 mysql> describe table_name;

Drop a db.

	mysql> drop database database_name;

Delete a table.

	mysql> drop table table_name;

Show all data in a table.

	mysql> SELECT * FROM table_name;

Returns the columns and column information pertaining to the designated table.

	mysql> show columns from table_name;

Set a root password if there is on root password.

	$ mysqladmin -u root password newpassword

Update a root password.

	$ mysqladmin -u root -p oldpassword newpassword

Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.

	$ /etc/init.d/mysql stop
	$ mysqld_safe --skip-grant-tables &
	$ mysql -u root
	mysql> use mysql;
	mysql> update user set password=PASSWORD("newrootpassword") where User='root';
	mysql> flush privileges;
	mysql> quit
	$ /etc/init.d/mysql stop
	$ /etc/init.d/mysql start

Export a database for backup.

	$ mysqldump -u user -p database_name > file.sql

Import a database

	$ mysql -p -u username database_name < file.sql 

Dump all databases

	$ mysqldump -u root -password --opt > alldatabases.sql