Backing up Your Textpattern Database Remotely

If you’ve been following my previous posts on Textpattern CMS, you should now know what to do to install it on Arvixe hosting. Before we delve into creating and managing content, this is the perfect time for me to show you how to backup your Textpattern database. The database contains content (articles, links, comments), organisational scaffolding (sections, tags, categories), presentational markup (pages, forms and styles) plus some settings and other things. In short: it’s important you back up this database.

There are many tutorials on how to use phpMyAdmin to manage databases, and frankly the Arvixe implementation of phpMyAdmin is about as foolproof as you can get. This tutorial is a little different because it focuses on remote management of your database. The difference between phpMyAdmin and a remote management application is that phpMyAdmin is installed to your server, whereas the remote management application is, as the name suggests, remote to the server; that is to say, it runs on your computer. Make sense? Rather than manage your database via a browser, you’ll be using a locally-installed application instead.

When I first started out with Apache + MySQL + PHP (AMP) applications, I was a big fan of phpMyAdmin and used it exclusively. Over the years, I’ve seen it become more of a target from opportunistic hackers. A well-maintained phpMyAdmin installation working in tandem with a secured MySQL database is pretty secure, but if updates are missed and security gets a little sloppy, then problems may occur. There’s also the risk that if you’re running phpMyAdmin from an Internet-visible URL, you increase the chance of someone else trying to log into your database. My solution to this risk was to remove phpMyAdmin from the equation and use a remote management application instead.

I use a Mac for my work, and I use the excellent SequelPro for database management. There is no version of SequelPro for Windows, but I have heard good things about MySQL Workbench which is cross-platform. This tutorial is a walkthrough for Textpattern and SequelPro, but you could just as easily adapt it for any other MySQL database running on your hosting and work out how to do the same in MySQL Workbench or your application of choice.

Before you use your application to remotely connect to MySQL, you need to change a cPanel setting to allow remote connections to the database. By default, MySQL access on Personal Class hosting is restricted to localhost. That is to say, MySQL on your web server will happily talk to Textpattern, WordPress, Drupal, Joomla etc on the same server, but not from any other IP address. It is straightforward to add an entry on the ‘allow’ list, either for a single IP address, a ranges or all IPs.

Log into your cPanel and find the Databases panel. From there, click Remote MySQL. You can now add your host, range or wildcard to allow remote access. If you want to keep things very secure, you can add your current IP address into the access list and remove it after your management is complete. Alternatively, you can add a wildcard of %.%.%.% which allows remote access from any IP address. Regardless of the access restrictions, you will require login credentials. Bear these factors in mind and you’ll find a balance of security and usability that works for you.

Open SequelPro. Go to Quick Connect. Enter your details into the boxes provided. Here’s what you need:

  • Host: this is your domain or hosting IP address. I recommend a domain in case Arvixe change the IP address of your server.
  • Username: your MySQL username, not your Arvixe billing name. Check your Textpattern config.php for this.
  • Password: your MySQL password, not your Arvixe billing password. Check your Textpattern config.php for this.
  • Database: the name of your Textpattern database. Check your Textpattern config.php for this.
  • Port: the port you connect to on the host you supplied above. This should be 3306.

Click Test Connection to make sure the credentials check out. You will see Connection Succeeded if it worked, and a more verbose dialogue box appear if it didn’t. You can choose to add this Quick Connection to Favorites by clicking Add to Favorites. This makes it appear on the left column and speeds up the connection process in the future. If you have a problem at this point, make sure your IP address is allowed to connect remotely by checking the cPanel list (Databases -> Remote MySQL) and that your credentials all check out. It’s safe to say that if you copy and paste from a working Textpattern’s config.php, the credentials are good.

Once you’re connected to your database, you can go to File -> Export and copy the entire contents of your database to a local file. When you have this exported file, it’s possible to import it if you irretrievably break something in Textpattern (which is tricky to do, believe me) or need a known-good backup to be restored.

Throughout this month we’ve taken the manual approach of installing Textpattern to gain a clearer understanding of how the components work. If you’re late to the party and haven’t yet installed it, my next post is going to show you how to install Textpattern using Softaculous on Arvixe cPanel. This is the ideal way if you want it to just work without getting your hands dirty in database stuff. I hope you’ll join me.

Looking for quality Textpattern Hosting? Look no further than Arvixe Web Hosting!

Tags: , , , , , , , | Posted under Textpattern | RSS 2.0

Author Spotlight

Pete Cooper

Pete Cooper

Pete Cooper has been using Textpattern since 2005. Textpattern is his preferred CMS weapon of choice. Its logical and flexible approach to content management makes Pete happy, as does its lightweight core and helpful user community. Pete's website - petecooper.org - runs on top of Textpattern and chronicles his day-to-day experiences from his home near the Atlantic in north Cornwall, United Kingdom.

Leave a Reply

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


5 × = 15

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>