This site requires JavaScript to be enabled
Welcome|
Recent searches
IE BUMPER

Exporting and Importing Your MySQL Database

Number of views : 31
Article Number : KB0011584
Published on : 2023-05-31
Last modified : 2023-05-31 21:42:56
Knowledge Base : IT Public Self Help

Overview

The ITS Database team creates and retains daily backups of your database in accordance with our SLA. However, you may want to make your own database export to backup the data prior to application updates or for archival purposes. We recommend using the mysqldump command line utility for this purpose.

If you need to import an existing export to restore a database, we recommend using the mysql command line utility.

It is important to note that your application should be stopped prior to running mysqldump to prevent it from writing to the database during the export. If this is not done, the database backup might contain inconsistent data. The application should also be stopped prior to running mysql to import data. If this is not done, end users may receive inconsistent data if they read from the database as the import runs.

If your database is very large (several GB), the export or import may take several minutes to complete.

 

Obtaining the 'mysqldump' and 'mysql' Utilities

For a Linux or Mac system, the mysqldump and mysql utilities are included in the MySQL command line client. Install the MySQL client appropriate for your distribution of Linux or version of Mac OS. The mysqldump and mysql utilities can then be run from a terminal.

For a Windows system, the mysqldump and mysql utilities are included with various MySQL GUI packages, such as MySQL Workbench. Install MySQL Workbench and then open a Windows command prompt. Navigate to where MySQL Workbench is installed, for example “C:\Program Files\MySQL\MySQL Workbench 8.0 CE”, to use the mysqldump and mysql utility executables.

If you need assistance installing the MySQL client or MySQL Workbench, contact your system administrator.

 

Exporting a Database with 'mysqldump'

When exporting the database, be sure to use the administrative user (ie. dept100) rather than the database user since the administrative user has elevated permissions.

Note: Access to the ITS MySQL servers is restricted to the UT Campus network and the UT VPN.

To export a database using mysqldump, do the following:

  1. Ensure the application using the database is stopped. Failure to do this may result in an inconsistent backup.

  2. Export the database to create a backup (you will be prompted for your password):


    • If your connection does not require SSL:

      mysqldump -u [admin_user] -p -h [host] -P [port] --opt --routines --triggers --set-gtid-purged=OFF --no-tablespaces --column-statistics=0 [database] > dump.sql
    • If your connection does require SSL (some operating systems may also require the --ssl-mode=REQUIRED option):

      mysqldump -u [admin_user] -p -h [host] -P [port] --opt --routines --triggers --set-gtid-purged=OFF --no-tablespaces --column-statistics=0 --ssl-ca=[cert] [database] > dump.sql
  3. Optionally open the dump file using a text editor to verify its contents.

  4. Start the application and verify it is working as expected.

 

Importing a Database with 'mysql'

When importing the database, be sure to use the administrative user (ie. dept100) rather than the database user since the administrative user has elevated permissions.

Note: Access to the ITS MySQL servers is restricted to the UT Campus network and the UT VPN.

To import a database using mysql, do the following:

  1. Ensure the application using the database is stopped. Failure to do this may result in end users receiving inconsistent data.

  2. Import the database (you will be prompted for your password):


    • If your connection does not require SSL:

      mysql -u [admin_user] -p -h [host] -P [port] [database] < dump.sql
    • If your connection does require SSL (some operating systems may also require the --ssl-mode=REQUIRED option):

      mysql -u [admin_user] -p -h [host] -P [port] --ssl-ca=[cert] [database] < dump.sql
  3. Verify the database contains the expected data.

  4. Start the application and verify it is working as expected.

Thank You! Your feedback has been submitted.

Feedback