Skip to content

5 Comments

  1. RUEDIGER R.
    May 19, 2017 @ 10:03 am

    Hi Dave,

    thanks very much for this advanced tutorial.

    I recently have exactly the mentioned timeout problem. The site I have developed is still a prototype prior to launch as the life site, but the database already has a size of 1.6 GB and so I wanted to test if backup restoring is working. I use a managed backup solution from WPMU Dev which offers direct restoring or download backup file. As automated restoring always dropped after some minutes with an error message I downloaded the backup file and tried restoring manually via the given .php installer file. Writing WP files worked well, but when it came to writing the DB tables it always ended in timeout, even with no timeout limits set on my server.

    That is why I have a special question. I am using AWS’ external database service named Aurora, which is 100% MySQL compatible, and the database is configured in wp-config.php with this line of code:

    define(‘DB_HOST’, ‘111.22.33.444:3306’);

    Now I am not sure if I could use the given mysql commands just like that because it would probably configure the localhost mysql default engine holding a Plesk installation. As far as I understand I might have to use the AWS CLI to send mysql commands. But I have no experience yet with this.

    I really want to dive deeper into SSL based restoring because I know that the DB will grow tremendously as soon as the multisite environment will show up with subdomain based subscriptions and the store will contain a couple of 100K products (right now it contains only 16K products).

    Any thoughts are welcome.

    Thanks again and best regards.

    Rod

    Reply

    • Anon
      May 19, 2017 @ 10:23 am

      No you definitely don’t need to use the AWS CLI to send the MySQL commands. One issue you might run into (hopefully would run into) is a firewall issue – this would mean you couldn’t connect to your 111.22.33.444:3306 MySQL instance from just anywhere. So – first thing to do is to SSH to your web server (which we know is accepted by the remote db otherwise wordpress wouldn’t be working). You just need to add a parameter to the mysql and mysqldump commands. Note, in my examples, I’m using unix sockets to connect to MySQL hence why I don’t need the -u (username) or -p (password parameters) – it trusts me because I’m root basically. Since your MySQL is on a different server, you’ll need the username, password and host parameters. e.g.

      mysql -u username -p -h 111.22.33.444 -P 3306 dbname

      So -u username is obvious. -p says Ask me for a password. This avoids passwords being registered in your server’s history (you shouldn’t put passwords in the command line). -P (capital p) lets you choose the port. And then you might need to use the dbname at the end too.

      If you run the above from your server, you should get a password prompt, enter that and you should be logged in to mysql on your remote host where you can run mysql commands (e.g. to create database, create users, grant privileges etc).

      Note: To enter MySQL passwords with SSH is easy, but confusing at first if you’re not familiar. Copy the password from your wp-config.php file, so it’s in your clipboard, now when the password prompt appears in SSH just right click on the screen. It won’t look like anything happened – because linux doesn’t use the *** approach of other operating systems – but right click is your PASTE command in a shell window.

      For mysqldump, it’s very similar:

      mysqldump -u username -p -h 111.22.33.444 -P 3306 dbname > /root/dump.sql

      So to restore, you’d use this command – Note: the dbname parameter has moved to the -D parameter here (this also means you don’t need to sed the file to add USE DB at the top, although I prefer that):

      mysql -u username -p -h 111.22.33.444 -P 3306 -D dbname < /root/dump.sql 

      Remember to use screen so you can happily disconnect and reconnect at will.

      Reply

      • Rod Roller
        May 29, 2017 @ 9:39 am

        Hi Dave,

        thanks a lot for the detailed information. Really excellent instructions. So it’s a complete tutorial now that puts me on the safe side. It’s good to know. Now I just need to test everything.

        Thanks again.

        Rod

        Reply

  2. René Hermenau
    May 27, 2017 @ 11:55 am

    Excellent instructions. Thanks for writing them.

    If you just want to create a clone of your website you could try out the WP STAGING plugin. I tested it with websites with 20GB of data without any timeouts: https://wordpress.org/plugins/wp-staging/

    Disclaimer: I am the author of the plugin

    Reply

    • Anon
      May 27, 2017 @ 8:08 pm

      Hi – thanks for letting us know. It looks like it creates a backup of the site on the server before copying across. If that includes the uploads folder, that’s not really a possibility for the larger sites because they just don’t have enough disk space – hence why rsync above is used.

      But thanks – there’s also Navicat which is a desktop application that will download and reupload your site to somewhere else and WP DB Migrate Pro.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *