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