MySQL command cheat sheet

Here I show how to use mysql from command line.

To login into mysql run –

mysql -h hostname -u root -p

To create  database on the sql server run –

mysql> create database [databasename];

List all databases on the sql server run –

mysql> show databases;

Switch to any database, run –

mysql> use [db name];

To see all the tables in the db run –

mysql> show tables;

To see database’s field formats run –

mysql> describe [table name];

To delete a db. run –

mysql> drop database [database name];

To delete a table. run –

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.

you can run any valid SQL command in this mysql command line.

Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privileges.

mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));
mysql> flush privileges;

Change a users password from MySQL prompt. Login as root. Set the password. Update privs.

mysql -u root -p
mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
mysql> flush privileges;

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

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

Allow the user “salayhin” to connect to the server from localhost using the password “passwd”. Login as root. Switch to the MySQL db. Give privs. Update privs.

mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to bob@localhost identified by 'passwd';
mysql> flush privileges;

Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.

mysql> grant all privileges on databasename.* to username@localhost;

mysql> flush privileges;

Update database permissions/privilages.

mysql> flush privileges;

Dump all databases for backup. Backup file is sql commands to recreate all db’s.

mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

Dump one database for backup.

mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql

Dump a table from a database.

mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup.

mysql -u username -ppassword databasename < /tmp/databasename.sql

As I say early all valid SQL command will execute on mysql command line utility.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s