As I’m migrating websites and applications from one server to another, I’m also migrating databases from one server to another.
Even though I’ve done this dozens, if not hundreds, of times, I always find myself looking up how to do this. I’m migrating from one MySQL (MariaDB) servers to another MySQL (MariaDB), so relatively straightforward but still some command syntax I don’t remember off the top of my head.
First, export the old database to a file:
DBHOST=old-db-host.osric.com DBUSER=dbusername DBNAME=dbname mysqldump --add-drop-table -h $DBHOST -u $DBUSER -p $DBNAME >$DBNAME.07-NOV-2023.bak.sql
mysqldump command produces output that will re-create the necessary tables and insert the data.
In this case I’m not compressing the output, but it would be trivial to pipe the output of
mysqldump to a compression utility such as
gzip. For my data, which is entirely text-based, any of these utilities performs well, although
xz achieves the best compression:
mysqldump --add-drop-table -h $DBHOST -u $DBUSER -p $DBNAME | xz -c >$DBNAME.07-NOV-2023.bak.sql.xz mysqldump --add-drop-table -h $DBHOST -u $DBUSER -p $DBNAME | bzip2 -c >$DBNAME.07-NOV-2023.bak.sql.bz2 mysqldump --add-drop-table -h $DBHOST -u $DBUSER -p $DBNAME | gzip -c >$DBNAME.07-NOV-2023.bak.sql.gz
Next, create the new database and a database user account. This assumes there is a database server running:
sudo mysql -u root CREATE DATABASE dbname; CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'your-t0p-s3cr3t-pa55w0rd'; GRANT ALL PRIVILEGES ON dbname.* TO 'dbuser'@'localhost';
Note that the
CREATE USER and
GRANT PRIVILEGES commands will result in a “0 rows affected” message, which is normal:
Query OK, 0 rows affected (0.002 sec)
There are other ways to create the database, see 7.4.2 Reloading SQL-Format Backups in the MySQL documentation.
Next, import the database from the file. This example uses the root user because I did not grant the dbuser PROCESS privileges (which are not table-level privileges):
sudo mysql --user=root --host=localhost dbname <dbname.07-NOV-2023.bak.sql