This post related to MySQL database SQL command which is used in daily database work by database administrator.
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 XYZ;
In this example I have created the "XYZ" 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 XYZ > XYZ_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 XYZ_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 XYZ > XYZ_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.
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 XYZ;
In this example I have created the "XYZ" 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 XYZ > XYZ_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 XYZ_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 XYZ > XYZ_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.
