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.
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 &gt; /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:
Modify the previous mysqldump command to take the stored code
mysqldump -v ESS_ANR --routines --single-transaction --user=root --password=XpWd2734 &gt; /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.
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 &lt; /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