- migratewp
- Jonathan Haack
- Haack's Networking
- netcmnd@jonathanhaack.com
migratewp
This tutorial is designed to step one through how to migrate a mysql database to another host. The tutorial is named migratewp, because I first did this on a WP instance. In my case, I have cron jobs that run mysqldump nightly as follows:
sudo -i /usr/bin/mysqldump \ --add-drop-database \ --all-databases \ --allow-keywords \ --comments \ --complete-insert \ --lock-all-tables \ --skip-dump-date \ --events \ --flush-logs \ --flush-privileges \ --hex-blob \ --opt \ --routines \ > mysqldump-all-databases.sql
But if you don't have that, then just make a backup of the database on the source host as follows:
mysqldump -u root -p --all-databases > all_databases.sql
Once that's done, send the .sql file over to the target host as follows:
scp all_databases.sql root@targethost.com:
Now, ssh into the target host and import the databases as follows:
mysql -u root -p < /root/all_databases.sql
After the database is migrated, simply copy all the files from web root for the instance over to the new host. Ensure that permissions, configuration files, certificates, etc., are appropriately configured, and then reboot. If any databases came over, but you no longer need them, you can drop them and the users as follows:
DROP DATABASE olddb; DROP USER 'olddbuser'@'localhost';
In cases of large databases, you might get an error that the shell lost connection to the database or that it vanished, etc. In that case, adjust your packet and timeout settings:
nano /etc/mysql/mariadb.conf.d/50-server.cnf <wait_timeout = 28800> <interactive_timeout = 28800> <max_allowed_packet=2G>
— oemb1905 2022/09/03 17:10
