Export/Import Database On MySQL

In this post we will see how to export a database and move it to another instance. First of all I must say that we to take several steps.

i) Database

With the following command we get all tables in the database we want to export, in our case ESS_ANR

 
mysqldump -v ESS_ANR --single-transaction --user=root --password=XpWd2734 > /tmp/BCK_ESS_ANR.SQL

ii) procedures / stored functions

mysqldump will backup by default all the triggers but NOT the stored procedures/functions. There is a mysqldump parameter that control this behavior:

–routines

Modify the previous mysqldump command to take the stored code

 
mysqldump -v ESS_ANR --routines --single-transaction --user=root --password=XpWd2734 > /tmp/BCK_ESS_ANR.SQL

iii) users and privileges

List all users;

 
SELECT user, host FROM user

List users with privileges on databases;

select * from mysql.db

Select the user or users to export, the next command give us the ddl to execute in the new database;

SHOW GRANTS FOR XXXX

Save the result to execute at the import.

 
Import process

i) Create the database;

CREATE DATABASE `NEW_ESS_ANR` ;

ii) Create users and grant priveleges, from the list we have previously obtained. We must filter users that interest us

iii) Import the tables, triggers and stored procedured;

mysql -u root -p XpWd2734 NEW_ESS_ANR < /tmp/BCK_ESS_ANR.SQL

/*** OR ***/

mysql -u root -p XpWd2734 NEW_ESS_ANR -e '/tmp/BCK_ESS_ANR.SQL'

finally is a good idea to make a backup of the entire system in case something happened.

HTH – Antonio NAVARRO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s