To login (from unix shell) use -h only if needed.
#mysql -h hostname -u root -p
Create a database on the sql server.
mysql> create database [databasename];
List all databases on the sql server.
mysql> show databases;
Switch to a database.
mysql> use [db name];
To see all the tables in the db.
mysql> show tables;
To see database’s field formats.
mysql> describe [table name];
To delete a database.
mysql> drop database [database name];
To delete a table.
mysql> drop table [table name];
Show all data in a table.
mysql> SELECT * FROM [table name];
Creating a new user.
# 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 unix shell.
#mysqladmin -u username -h hostname -p password ‘new-password’
Change a users password from MySQL prompt.
# mysql -u root -p
mysql> SET PASSWORD FOR ‘user’@'hostname’ = PASSWORD(‘password’);
mysql> flush privileges;
Recover a MySQL root password.
# /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
Update a root password.
# mysqladmin -u root -p oldpassword newpassword
Allow the user “user1” to connect to the server from localhost
# mysql -u root -p
mysql> use mysql;
mysql> grant usage on *.* to user1@localhost identified by ‘password’;
mysql> flush privileges;
Give user privilages for a database.
mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;
or
# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES (‘%’,'databasename’,'username’,'Y’,'Y’,'Y’,'Y’,'Y’,'N’);
mysql> flush privileges;.
Load a CSV file into a table.
mysql> LOAD DATA INFILE ‘/tmp/filename.csv’ replace INTO TABLE [table name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (field1,field2,field3);
Dump all databases for backup.
#mysqldump -u root -ppassword –opt >/tmp/alldatabases_backup.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/tablename.sql
Restore database (or database table) from backup.
mysql -u username -ppassword databasename < /tmp/databasename.sql
