Moving at the Speed of Creativity by Wesley Fryer

Restore or Move a Large mySQL Database with mySQLDumper

This afternoon was an abnormally geeky one for me, but thankfully successful. I don’t normally wrangle with mySQL databases, but today I had to. At LONG last I’m completing the migration of my websites to a new web host, and the last two sites I’ve needed to backup and restore are my custom URL shortener site (running the open source software, Yourls) and my Twitter archive (running the open source software, TweetNest). The problem I ran into with this process (for my URL shortener installation) was the size of my database: It was too big (over 100MB) to successfully export using phpMyAdmin in the CPanel controls provided by my web host.

I’m not a server command line guy, so I Googled for an answer. Thanks to this forum post, I discovered the open source software program mySQLDumper.

Like other PHP and mySQL based programs I’ve worked with and installed on my web host account (WordPress, Yourls, TweetNest, etc) mySQLDumper requires that you upload its files to a new directory on your server and then configure it via a web interface. Awhile back I figured out (via trial and error) that it’s MUCH faster to upload files via the CPanel file manager rather than FTP client software, like CyberDuck. I also learned it’s possible to upload smaller, compressed versions of software (usually ZIP files) and then extract or decompress them on the server, using the CPanel file manager. This is what I did with mySQLDumper to create a backup on my OLD server / hosting account:

  1. Downloaded the software as a ZIP file.
  2. Created a new directory on my OLD server to run mySQLDumper.
  3. Uploaded the mySQLDumper ZIP file into that directory using the CPanel file manager.
  4. Extracted the ZIP file, deleted the original ZIP file, and moved the included files into the root of my new folder. (They extracted in a subfolder/child folder.)
  5. Opened the web address of the new folder I created in my computer web browser to open the mySQLDumper configuration screen.
  6. Entered the mySQL database userID and password for the database on my OLD server which I wanted to backup.
  7. Selected the database in the left sidebar of mySQLDumper and chose to create a backup.
  8. Downloaded the backup to my local hard drive.

The next phase involved configuring things on my new server / hosting account to import that backup.

  1. Using CPanel on my new server account, I used the mySQL wizard to create a new database with a new, unique userID and password.
  2. As I did on the old server, I created a new directory, uploaded the mySQLDumper zip file, extracted it, and moved its files to the root level.
  3. Opened the web address of the new folder I created to open the mySQLDumper configuration screen.
  4. Entered the new mySQL database userID and password for the database on my NEW server which I wanted to restore to from my backup.
  5. Uploaded my backup file to my new server.
  6. Selected the database in the left sidebar of mySQLDumper and chose to restore from my backup.

Once my mySQL database was restored on the new server, I installed Yourls by:

  1. Downloading the latest version of Yourls.
  2. Using CPanel to add an “Addon Domain” to my account
  3. Using the CPanel file manager to upload the ZIP of Yourls into the directory I created in the process of adding the “Addon Domain,” unzipping it, and moving its files to the root level of the directory.
  4. Configuring and uploading the user/config.php file with the credentials for my new mySQL database and an administration userID/password per the Yourls install documentation. (I later converted my password to a md5 hash, again using the provided documentation.)
  5. Changing the Name Server addresses with my registrar (GoDaddy) from my old server to the new one.
  6. Verifying the installation worked once the DNS change propagated / took effect. (I used a different computer because for some reason I’m unable to flush the DNS cache of my laptop.)

The Yourls installation initially seemed to work great, and I could login with the admin credentials I’d created. I verified my old/past shortened URLs were visible in the archive, and that I could create new shortened URLs. When I tried to visit any of the created short URLs, however, none of them would work. One more thing was required.

The last step I had to complete to make the Yourls site migration work was to create a custom .htaccess file. I did this per the Yourls FAQ instructions, using TextWrangler software to create the text file and then uploading the file using the CPanel file manager. Because server files like .htaccess are special, you need to upload the file without a period in the filename, and then rename it with the leading period after it’s uploaded. All of this can be done with the CPanel file manager.

As I noted at the outset of this post, this was a MUCH geeker afternoon of computer activities for me. Doing stuff like this isn’t something I look forward to or relish (I’d actually been dreading and putting off this process for months) but they are things I needed to learn and do to migrate my remaining websites.

If you’re migrating WordPress sites to a new web host, I highly recommend you use either BackupBuddy (from iThemes, I company I worked with a bit in 2012) or the Install/Clone WordPress add-on for Infinite WordPress. InfiniteWP is free and is WONDERFUL for managing updates on multiple WordPress sites, but you need to pay for this plug-in if you want to use it to remotely install fresh versions of WordPress and clone an installation. The free version of InfiniteWP lets user create backups as well as restore from backups, but the restoration process to a new server requires a manual WordPress install along with the InfiniteWP plugin.

If this post and information about mySQLDumper is helpful to you with your own server / hosting account changes, please let me know with a comment or tweet to @wfryer. If you have suggestions about other ways to accomplish what I’ve described here, please share those links and steps too!

The banner below is an affiliate link for my web host, Site5.

468x60-1

Technorati Tags: , , , , , , , , , , , ,

If you enjoyed this post and found it useful, subscribe to Wes’ free newsletter. Check out Wes’ video tutorial library, “Playing with Media.” Information about more ways to learn with Dr. Wesley Fryer are available on wesfryer.com/after.

On this day..


Posted

in

,

by

Tags:

Comments

2 responses to “Restore or Move a Large mySQL Database with mySQLDumper”

  1. johnjohnston Avatar

    Thanks for the WP tips, I am squirrelling those away.

    An other way t oskin the mySQL cat is, on a mac, is Sequel Pro
    http://www.sequelpro.com/

    Searching my HD I see a >500MB sql file I downloaded with Sequel Pro. Despite the pro name the application is free and much simpler to use than phpMyadmin

  2. Wesley Fryer Avatar

    Glad you found this helpful, John… I’m SO relieved to have successfully migrated that large database. Thanks for recommending Sequel Pro too… I hadn’t heard of it and will “file that away” too for future use! 🙂