Haack's Wiki

User Tools

Site Tools

Trace:
computing:migratewp

Differences

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

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
Next revisionBoth sides next revision
computing:migratewp [2022/07/31 17:42] – oemb1905computing:migratewp [2022/07/31 17:50] – oemb1905
Line 11: Line 11:
 ------------------------------------------- -------------------------------------------
  
-This tutorial is designed to step one through how to migrate a mysql database to another host. 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. First, use mysqldump to dump the entire database:
  
   sudo -i   sudo -i
Line 34: Line 34:
   sudo scp mysqldump-all-databases.sql user@newhostorip.com:   sudo scp mysqldump-all-databases.sql user@newhostorip.com:
      
-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.  Make sure the name you pick is unique and was not present in the database backup and/or a restricted user name.+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.
  
-  CREATE DATABASE newdatabase DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; +  CREATE DATABASE temp; 
-  GRANT ALL ON newdatabase.* TO 'newdatabaseuser'@'localhost' IDENTIFIED BY 'temporarypassword';+  CREATE USER tempuser@localhost IDENTIFIED BY 'tempass'; 
 +  GRANT ALL PRIVILEGES ON temp.* to tempuser@localhost IDENTIFIED BY 'tempass';
   FLUSH PRIVILEGES;   FLUSH PRIVILEGES;
   EXIT;   EXIT;
-  CREATE USER 'newdatabaseuser'@'%' IDENTIFIED BY 'temporarypassword'; 
-  GRANT ALL ON newdatabase.* TO 'newdatabase'@'localhost' IDENTIFIED BY 'temporarypassword'; 
      
-Now, you need to create databases and grant privileges to the surrogate user for each of them.  You do this as follows:+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.
  
-  CREATE DATABASE restoreddb1 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci; +  mysql -u tempuser -p temp < /path/to/backup-databases.sql 
-  GRANT ALL ON restoreddb1.* TO 'newdatabaseuser'@'localhost' IDENTIFIED BY 'temporarypassword'; +  mysql -u tempuser -h localhost -p --database=temp < /path/to/backup-databases.sql
-   +
-If there are others, then repeat the commands above for each one, replacing restoreddb1 with the name of the databases you are importing and bringing in.  Now, time to import that database into the new host: +
- +
-  mysql -u newdatabaseuser -h localhost -p --database=newdatabase < /path/to/backup-databases.sql+
  
 Once it finishes, log into the mysql command mode and verify the original databases made it over: Once it finishes, log into the mysql command mode and verify the original databases made it over:
Line 58: Line 53:
   > EXIT   > EXIT
  
-If possibly, run this command on both machines, and the output should be identical.  Now, it is time to migrate the website //files// over to the new host:+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:
  
-  sudo scp -r /var/www/website.com/public_html user@newhostorip.com:/var/www/newwebsite.com/ +    DROP DATABASE temp; 
-   +    DROP USER 'tempuser'@'localhost';
-Put the files in the appropriate locations, restart the mysql service and reboot.  Lastly, once you verify the original databases made it over and the website is restored and functioning, you can delete the surrogate user and database as follows:+
  
-    DROP DATABASE newdatabase; +That should be all there is to it!
-    DROP USER 'newdatabaseuser'@'localhost';+
  
- --- //[[jonathan@haacksnetworking.com|oemb1905]] 2019/12/30 02:37// + --- //[[jonathan@haacksnetworking.org|oemb1905]] 2022/07/31 11:42//
-  +
computing/migratewp.txt · Last modified: 2022/09/03 23:11 by oemb1905

Page Tools

Except where otherwise noted, content on this wiki is licensed under the following license: GNU Free Documentation License 1.3
GNU Free Documentation License 1.3 Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki