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 shell)
$ 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
Comments