Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts

Friday, February 17, 2017

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.