Differences

This shows you the differences between two versions of the page.

--- computing:migratewp [2022/07/31 17:50] – oemb1905
+++ computing:migratewp [2022/09/03 23:11] (current) – oemb1905
@@ Line 11: / Line 11: @@
 -------------------------------------------
-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. First, use mysqldump to dump the entire database:
+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
@@ Line 29: / Line 29: @@
   --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:
-Use scp to get the file to the new host:
+  mysqldump -u root -p --all-databases > all_databases.sql
-  sudo scp mysqldump-all-databases.sql user@newhostorip.com:
+Once that's done, send the ''.sql'' file over to the target host as follows:
-Before your import the mysqldump databases into the new database, you need to create what I call a "surrogate" user and database to do the heavy lifting for you first.  You will also grant this surrogate user super privileges.
+  scp all_databases.sql root@targethost.com:
-  CREATE DATABASE temp;
-  CREATE USER tempuser@localhost IDENTIFIED BY 'tempass';
-  GRANT ALL PRIVILEGES ON temp.* to tempuser@localhost IDENTIFIED BY 'tempass';
-  FLUSH PRIVILEGES;
-  EXIT;
-Now that you have created a temporary database and database user with full privileges, it is time to import the backup. The second command is what I used on some temperamental instance. The first command below is simple enough and should work. I retain both just in case an instance talks back to me.
+Now, ssh into the target host and import the databases as follows:
-  mysql -u tempuser -p temp < /path/to/backup-databases.sql
-  mysql -u tempuser -h localhost -p --database=temp < /path/to/backup-databases.sql
-Once it finishes, log into the mysql command mode and verify the original databases made it over:
+  mysql -u root -p < /root/all_databases.sql
-  sudo mysql -u user -p
+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:
-  > SHOW DATABASES;
-  > EXIT
-After the database is migrated, simply copy all the files from web root for the instance over to the new host. Ensure that permissions and configuration files are appropriate, then reboot. Once the database is migrated, you can delete the surrogate user and database as follows:
+    DROP DATABASE olddb;
+    DROP USER 'olddbuser'@'localhost';
-    DROP DATABASE temp;
+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:
-    DROP USER 'tempuser'@'localhost';
-That should be all there is to it!
+  nano /etc/mysql/mariadb.conf.d/50-server.cnf
+  <wait_timeout = 28800>
+  <interactive_timeout = 28800>
+  <max_allowed_packet=2G>
- --- //[[jonathan@haacksnetworking.org|oemb1905]] 2022/07/31 11:42//
+ --- //[[jonathan@haacksnetworking.org|oemb1905]] 2022/09/03 17:10//

Haack's Wiki

User Tools

Site Tools

Differences

Page Tools