Export the Legacy Database

Determine the name of the databases to export. For the Invision software, there should just be one database.

For this, let DB_NAME be the name of the database to export.

$ mysqldump -u ... \
 --events \
 --routines \
 --single-transaction \
 --triggers \
 old_db \
 -p > sample.sql

A brief explanation:

1. -u root use the username root
2. -p prompt for the password.
3. --events, --routines, --triggers make sure to export the stored events, routines and triggers that are in the database (if any)
4. --single-transaction cause the dump to be a snapshot of the database when the export starts.

old_db is the name of the database being extracted.

Application of the export

To apply the export, the process is quite simple since the sample.sql file contains all of the SQL statements to produce the effect.

On the new destination Server:

$ mysql -u ... -Dnew_db -A -p < sample.sql

Note that the new_db is necessary if there already exists a copy of the database on the destination server.

Note that the mysql tables that store users, and granted privileges are not in the export.

User and Privileges

How to create the user if it doesn't already exist.

mysql> CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'password';

If the name of the database is db1, then this is how the user is granted access to the database:

mysql> GRANT ALL ON newdb.* TO 'jeffrey'@'localhost';

Then cause the grant tables to be reloaded.

mysql> FLUSH PRIVILEGES;