Frequently asked questions

Keyword:

How do I backup my MySQL database?

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

The control panel at www.domainnameshop.com
MySQL WorkBench (simple GUI, use it on your own PC)
mysqldump (for advanced users, use it on our login server)
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.

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

MySQL WorkBench

The program MySQL Workbench, available for Windows, Mac and Linux, is an easy-to-use program for administering and backing up a MySQL database. Follow these steps to backup your database with MySQL Workbench. If you already have established a connection profile, skip to point 6 about exporting.

Please note that MySQL Workbench in its latest edition (8.0.19) corrects yet another severe vulnerability. Regrettably, most variants of MySQL Workbench 8 are incompatible with MySQL 5.7 and MariaDB, and require MySQL 8. Exercise caution using this software, and consider alternatives such as DBeaver (dbeaver.io).

The following instructions are for older (and therefore insecure) versions of Workbench.

Check that you have a recent version installed, to avoid as many security issues as possible.
Note! Version 6.3.6 has a serious bug and cannot be used. More recent versions should work, such as 6.3.8 in Debian Stretch.
You will see this error message at the bottom of the window if you have an outdated version.

Create a new connection profile by clicking on the plus sign next to "MySQL Connections".
Enter the usual login information for MySQL.

When the connection profile is created, click on it to connect.
Note: WorkBench may issue a warning about MySQL version. This is not a problem, click "Continue Anyway".

Enter the program menu, select "Server", and then "Data Export".

Select your database schema for export, and unselect any tables you don't want to export on the right hand side.
Select to export your backup dumped to one file ("Export to Self-Contained File") to avoid issues with TCP rate limits.
For MySQL WorkBench 8.x and up with MySQL 5.6 and MariaDB, you must enter "Advanced Options..." and change "column-statistics" from TRUE to FALSE.
If you want your backup to include stored procedures and functions, check the mark for that also.
Click on "Start Export".
When you want to restore from a backup, select "Server" -> "Data Import" in the program menu.

Error message about MySQL version

MySQL WorkBench is an Oracle product, and will therefore warn about version numbers they have strong opinions about. The error message starts with a text similar to this: "Incompatible/nonstandard server version or connection protocol detected (10.3.10)" Select "Continue Anyway" to continue connecting.

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