Mysql has it pretty simple when it comes to database backup. Using mysqldump tool you can create an sql dump of one, more or all databases on your server. What it does, is writes SQL scripts for every object inside a database (DML, grants, data inserts etc.).
Database restore (or cloning) is then done by running the generated script through mysql client on the server where we want to restore/clone the exported database. Example (let’s say we run this as root user, so we have all privileges on the server):
# EXPORT: mysqldump --all-databases > dump.sql # IMPORT: mysql < dump.sql
However, when importing to another server one should keep in mind that all passwords on new server will be overwritten. Even if it was a totaly empty database, it still has users like “debian-sys-maint” that are created during the installation of mysql. When you do try to start your new database you can get the following error:
Access denied for user 'debian-sys-maint'@'localhost'
This is because mysql is using wrong password to login as debian-sys-maint. On debian systems this password is kept in “debian.cnf” file on your server (usually in /etc/mysql directory). You need to logon to mysql as root and change the password for debian-sys-maint to one written in debian.cnf.
SET PASSWORD FOR 'debian-sys-maint'@'localhost' = PASSWORD('yourPasswordFromCNFfile');
After that restart mysql server (if it won’t stop, just kill the mysql processes manually) and everything should be in order.