SQL command for MySQL database on Linux operating system

In this post, i will explain you how we will create, import, export & backup of MySQL database.

Please find the below points which is very useful to database admin on Linux operating system.

How to create new database:

CREATE DATABASE <DATABASENAME>;

e.g. CREATE DATABASE VIBHOR;

In this example I have created the "vibhor" named database, you can replace database name according to your choice.

How to create mater user with all level access to all available databases:

GRANT ALL ON *.* TO <USERNAME> IDENTIFIED BY '<PASSWORD>';

e.g. GRANT ALL ON *.* TO TESTDBUSER IDENTIFIED BY 'TESTDBUSER';

In this example I used "testdbuser" MySQL user on the server. 

How to take backup (dump) of a database:

For this command you need not to login into the database. Once you login on the database please run the below command to take a dump of server.

mysqldump --lock-all-tables -u <USERNAME> -p -h <DATABASESERVERNAME/IP ADDRESS> <DATABASENAME> > <NEWDUMPFILENAME>.sql

e.g. mysqldump --lock-all-tables -u ABC -p -h MACHINE1 VIBHOR > VIBHOR_16022017.sql

After this command you have to provide DB password only and dump will be done in the folder in which u are working currently.

How to take backup (dump) of a table's:

For this command you need not to login into the database.

mysqldump -u <USERNAME> -p <DATABASENAME> <TABLENAME> > <NEWDUMPFILENAME>.sql

e.g. mysqldump -u testdbuser -p testdb test_book > test.sql

After this command you have to provide DB password only and dump will be done in the folder in which u are working currently. for dumping multiple table use space between the table names.

How to run a database dump to another machine:

Go to the folder in which sql dump file is placed and then connect with your Database in which you want to run dump and then run below command.

source <FILENAME>.sql;

e.g source VIBHOR_16022017.sql;

How to take dump only triggers and procedure from database:

mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt -u <USERNAME> -p <DATABASENAME> > <NEWDUMPFILENAME>.sql

e.g. mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt -u TESTDBSUER -p VIBHOR > VIBHOR_16022017.sql

After this command you have to provide DB password only and dump will be done in the folder in which u are working currently.

How to show procedure/function code:

SHOW CREATE PROCEDURE <STORED PROCEDURE/FUNCTION NAME>\G

e.g. SHOW CREATE PROCEDURE FLIGHT_INVOICETOSAP\G;

How to increase filed width of a table attribute:

ALTER TABLE <TABLENAME> CHANGE <EXISTING_FIELD_NAME> <NEW_FIELD_NAME> <DATATYPE>(<NEW_FIELD_WIDTH>);

e.g. ALTER TABLE TRILOK CHANGE PASSENGER_NO PASSENGER_NUMBER VARCHAR(30);

field name and data type change is not recommended.

How to display all existing databases:

SHOW DATABASES;

Using this command you can show all the created database list on the Linux server.

How to display all existing tables:

SHOW TABLES; 

SHOW TABLES LIKE '<CHARACTERS>%';

Using this command you can check all the tables which is created on the database. 

How to recover a MySQL root password:

This is one of the best way to recover the MySQL root password if you forget. I always used below method to reset the root password.

• Stop the MySQL server process.

# /etc/init.d/mysql stop

• Start again with no grant tables.

# mysqld_safe --skip-grant-tables &

• Login to MySQL as root. Set new password.

# mysql -u root

mysql> use mysql;

mysql> update user set password=PASSWORD("newrootpassword") where User='root';

mysql> flush privileges;

mysql> quit

• Exit MySQL and restart MySQL server.

# /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

How to grant privileges to a user:

If we need to provide the privileges to user we will use below two method. I will describe the step by step method for this one.

METHOD 1

Allow the user "bob" 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 privileges for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.

# 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;

METHOD 2

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

mysql> flush privileges;

To update info already in a table.

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:

Backup file is SQL commands to recreate all databases.

# [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

Dump one database for backup.

# [mysql dir]/bin/mysqldump -u username -p password --databases databasename >/tmp/databasename.sql

Dump a table from a database.

# [mysql dir]/bin/mysqldump -c -u username -p password databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup.

# [mysql dir]/bin/mysql -u username -p password databasename < /tmp/databasename.sql

Create Table Example 1.

Using above method we can take database back very fast. I hope through this post you can easily able to create MySQL database, MySQL dump, table creation etc work.