Blog @ RohitRox

tips tricks tuts and everything learned

MySql Commands

| Comments

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)

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

Create a new database

1
mysql> create database databasename;

List all databases

1
mysql> show databases;

Switch to a database

1
mysql> use database_name;

List tables

1
mysql> show tables;

See database’s field formats.

1
 mysql> describe table_name;

Drop a db.

1
mysql> drop database database_name;

Delete a table.

1
mysql> drop table table_name;

Show all data in a table.

1
mysql> SELECT * FROM table_name;

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

1
mysql> show columns from table_name;

Set a root password if there is on root password.

1
$ mysqladmin -u root password newpassword

Update a root password.

1
$ 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.

1
2
3
4
5
6
7
8
9
$ /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.

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

Import a database

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

Dump all databases

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

Comments