Frequently asked questions

How do I backup my MySQL database?

Below, we describe three methods for backing up/downloading your MySQL database:

1. The control panel at www.domainnameshop.com
2. MySQL WorkBench (simple GUI, use it on your own PC)
3. mysqldump (for advanced users, use it on our login server)
4. phpMyAdmin is also installed, and if you are familiar with this software, you may use it.

---

The control panel at www.domainnameshop.com

Note! If your database is huge, downloading it from the control panel may be impossible. If so, there will be a warning icon () with info about that, and you must use one of the other methods.

1. Login as usual in the controlpanel at www.domainnameshop.com/login.
2. Select the domain from My domains.
3. Select the Webhotel tab.
4. Select View/change on the MySQL line.
5. Click on ... rightmost on the line with the relevant database.
6. Click on Export.
7. You will now receive a ZIP file, which contains an SQL file with a copy of the database.
8. You may restore the database by unpacking the zip file and running the SQL file as an SQL script.

---

The mysqldump command

Using the Unix shell on our login server, you may backup your databse with the mysqldump. This creates a set of valid SQL statements that can be used for restoring your database later.

In the interest of space and resource usage, we recommend that you download such backups to your own PC and remove them from your webhotel afterwards.

mysqldump has many advanced options for exporting data safely, we recommend that you consider which you need for your data. On our login server, mysqldump comes with Debian's standard package for MariaDB 10.1, see here for documentation.

Recommended options for exporting with mysqldump

 --skip-disable-keys --no-autocommit --hex-blob --no-tablespaces

Other options

If you want to use these, read the documentation first, pay close attention to any error messages or warnings.

 --force --routines --triggers

If you have large BLOB data, try:

 --max-allowed-packet=32M

but not greater values.

MySQL 5.6 and 5.7

Oracle introduced incompatible changes to MySQL 5.6.49 and MySQL 5.7.31, removing access to non-essential metadata.

If you get the error message "Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces" , you must use this option to mysqldump:

 --no-tablespaces

Examples for mysqldump

Note: you will be prompted for your MySQL password, so be sure to keep it handy!

1. Compressed backup

In this example, we compress the SQL data with gzip; it therefore must be unzipped with gunzip or other gzip compatible software.

mysqldump DATABASE NAME -uUSERNAME -hDATABASE NAME.mysql.domeneshop.no -p | gzip > filename.sql.gz

Substitute any filename you like for filename.sql.gz.

You may also use other software for compressing your data.

2. Uncompresed backup

If you do not wish to compress the file, drop gzip and use this command instead:

mysqldump DATABASE NAME -uUSERNAME -hDATABASE NAME.mysql.domeneshop.no -p > filename.sql

3. As our control panel does it

mysqldump DATABASE NAME -uUSERNAME -hDATABASE NAME.mysql.domeneshop.no \
  --skip-opt --single-transaction --set-charset \
  --create-options --quick --force --add-locks --extended-insert \
  --add-drop-table --no-autocommit --routines --triggers -p > filename.sql

---