Managing your databases

Exporting a database

Important

Exporting larger databases through phpMyAdmin is not advised, because the PHP execution time is limited.

If you do use phpMyAdmin, export SQL in a compressed format, preferably bzip2, then gzip, then zip.

On the command line, here is how to prepare a compressed SQL file. Replace the xxxxx with the right values:

bash

# Directory to back the database up to
DBPATH=xxxxx/

# Database name
DBNAME=xxxxx

# Database login
DBUSER=xxxxx

# Password for that login
DBPASS=xxxxx

# Database server (use localhost instead of 127.0.0.1 if it does not work)
DBHOST=127.0.0.1

mysqldump --single-transaction --routines --triggers --events --hex-blob -h${DBHOST} -u${DBUSER} -p${DBPASS} ${DBNAME} | bzip2 -c > ${DBPATH}/mysql_backup_${DBNAME}_`date +%Y%m%d-%H%M%S`.sql.bz2

Important

For the database server, 127.0.0.1 or localhost?

Both point at your own server, but with 127.0.0.1 the connection goes through the network, and with localhost it goes through a socket.

Transferring the resulting file

• The resulting file is named something like mysql_backup_${DBNAME}_`date +%Y%m%d-%H%M%S`.sql.bz2.
• Upload it to the server in the virtualmin-backup directory.

Importing a database

Important

Importing larger databases through phpMyAdmin is not advised, because the PHP execution time is limited.

To import without going through PHP, use the command line, either over an SSH connection or through the terminal built into Virtualmin.

Once connected to the terminal:

bash

# Name of the new database
DBNAME=xxxxx

# Database login
DBUSER=xxxxx

# Password for that login
DBPASS=xxxxx

# Database server (use localhost instead of 127.0.0.1 if it does not work)
DBHOST=127.0.0.1

# The database must be EMPTY: drop all its tables if needed (without touching the database itself)
mysql --protocol=TCP -N -h${DBHOST} -u${DBUSER} -p${DBPASS} ${DBNAME} -e 'SHOW TABLES' | sed 's/.*/DROP TABLE IF EXISTS `&`;/' | mysql --protocol=TCP -h${DBHOST} -u${DBUSER} -p${DBPASS} ${DBNAME}

# Alternatives depending on the file format.
# The sed strips the USE / CREATE DATABASE statements from the dump: otherwise it
# switches to the original database (error "Access denied ... to database ...").
# Import a sql.bz2 file
bunzip2 -dc db_filename.sql.bz2 | sed -E '/^\s*(USE|CREATE DATABASE)\b/Id' | mysql --protocol=TCP --binary-mode=1 -h${DBHOST} -u${DBUSER} -p${DBPASS} ${DBNAME}

# Import a sql.gz file
gunzip -c db_filename.sql.gz | sed -E '/^\s*(USE|CREATE DATABASE)\b/Id' | mysql --protocol=TCP --binary-mode=1 -h${DBHOST} -u${DBUSER} -p${DBPASS} ${DBNAME}

# Import a sql.zip file
unzip -p db_filename.sql.zip | sed -E '/^\s*(USE|CREATE DATABASE)\b/Id' | mysql --protocol=TCP --binary-mode=1 -h${DBHOST} -u${DBUSER} -p${DBPASS} ${DBNAME}

Important

If the import stops with an error such as ASCII '\0' appeared in the statement ... --binary-mode, the file contains binary data. The --binary-mode=1 option above fixes it (it only applies when the import comes from a file, which is the case here).

Important

If the import fails with Access denied for user ... to database ... while showing a database name different from the one you targeted, the dump contains a USE / CREATE DATABASE statement that switches it to the original database. The sed in the commands above removes those lines, so the import lands in the database given by DBNAME.

See also

• Using Virtualmin, to reach phpMyAdmin and the built-in terminal.
• Virtualmin on the command line, for server administration.