Please advise on how to backup database (and restore if necessary)

[#1]

I had an 'Unable to establish database connection' error today and when I went into phpMyAdmin in the Control Panel, I had a '#145 - Table xxx is marked as crashed and should be repaired' error on a table.   I was able to use the Repair Table option and thankfully everything seems to be back up and running again.
This has highlighted that I need to be backing up the database locally, however I'm not sure of the best way of doing it.
Looking at the phpMyAdmin screen it lists two databases, although I only have one WordPress install.
Both end with (ccgijtxxxx4xxxxx) but the numbers at the end are different.  However they seem to be the same thing and repairing the table in one repaired it in the other, so that's confused me.
To make a backup, do I just select the top database in phpMyadmin and choose 'Operations' then use 'Copy Database To'?
What do I put as a location under the Copy Database To heading?
Are the default options of 'Data and Structure', 'CREATE DATABASE before copying' and 'Add AUTO_INCREMENT value' sufficient?
I could probably find a WordPress plugin to do the backups, but I'm not sure what to trust to get the job done properly.
Once I've got a backup, how would I restore it should the need ever arise?  Is it just a case of going to 'Import' in phpMyAdmin and entering the name of the backup file and click Go?
Apologies for all the questions.
- Jules.

[#2]

It's odd having two databases that appear to be replicas of each other, but Plusnet will have to advise on this. The 'master' database should be the one where the WordPress configuration entries match the six digits at the end of the database name.
To backup this database you should select and open it in the phpMyAdmin left-hand panel, than go to the Export tab. (The Operations tab is used for copying tables between database whereas you need to export them to a file on your PC.)
On this tab, select "Custom", and make sure all tables are selected/highlighted.
Modify the content of the File name template textbox to @DATABASE@-%Y-%m-%d
to add the date to the file name of the dump file.
Select gzipped from the Compression drop down menu.
In Object creation options tick Add DROP TABLE statement,
Remaining settings should be left at their default values.
Now click Go … and wait.
After 2-3 minutes your browser download dialogue should display to handle saving the file to your PC.
If you ever need to restore the complete database you use the Import tab, browse to locate the file on your PC and click Go.
Complications arise if you only want to import a few tables. The simplest solution is perhaps to create another empty database and import the complete backup into that. Using Operations/Copy table is the obvious way to copy it across but I suspect access permissions wouldn't allow that. You'd probably have to export just the relevant tables then import these into the original database.
David

[#3]

Hi David,
Thanks for the detailed instructions.
However, when I click Go when using Internet Explorer it comes up with Error 500 and if I do the same steps using Google Chrome I get 'No web page was found for the web address http://ccgi.xxxxxxxx.plus.com/phpmyadmin/export.php'. ; No browser download dialogue. 
- Jules.

[#4]

Hmm, I think Plusnet need to take a look at this. I wonder if your access permissions to the database lack something essential.
Exporting using the settings I suggested works OK for me.
David

[#5]

Looking at this now, there might be some disruption to your site...

[#6]

Thanks Bob.  Am I OK to do updates to the site, or should I wait?

[#7]

You should be OK if it's just content/database updates etc.
I've removed your old databases and replaced them with a single user. Password's the same but the database name and username are different (I've updated your wp-config file). I was hoping this would sort the phpMyAdmin export problem out but it hasn't so I'm still working on that. Note that you'll not be able to get into phpMyAdmin at the moment. Try not to touch that until you've heard back from me.

[#8]

Quote from: Bob

I've removed your old databases and replaced them with a single user. Password's the same but the database name and username are different (I've updated your wp-config file).

Thank you.

Quote

Note that you'll not be able to get into phpMyAdmin at the moment.

OK

[#9]

Bob,
Any news on how I can make a database backups?
Regards,
Jules,

[#10]

Using phpMyAdmin, no. We're still looking into it.
Using the details from your wp-config file though you should be able to use an alternative like Adminer or MySQL Administrator.

[#11]

OK, so I've downloaded 'Adminer 4.0.3 for MySQL English only'.  I've entered the details from my wp-config file and have clicked on the 'Dump' link.  I'm now faced with a screen headed Export:databasename.
What options do I choose in the box above the Export button?
At the moment the options are:
Output: open (I'm guessing I should change that to gzip or Save).
Format: SQL
Database: Is blank with Routines and Events ticked.
Tables: DROP+CREATE with Auto Increment unticked and Triggers ticked. (Doesn't DROP mean delete?  Do I change that to just CREATE?)
Data: INSERT
There doesn't appear to be an help files anywhere and I don't want to risk damaging the database by clicking on something incorrectly.
Are there any security issues with having the php file in a subdirectory of /public?  What file permissions should I set?
Regards - Jules.

[#12]

Yes, for Output you should select the gzip radio button
  (open lists the file on the screen, save downloads an uncompressed file -  5-10 times larger)
Format you want SQL
Database leave the text box empty, with routines and events ticked
Tables leave as DROP+CREATE, Auto-Increment unticked, Triggers ticked.
  (DROP+CREATE is what will happen on a restore and you want the old table deleted first).
Data leave as INSERT
  (there won't be anything to UPDATE on a complete restore)
For a complete database dump ensure the Tables and Data heading checkboxes are ticked (which ticks all rows).
If you want to dump a prefixed selection, untick everything and select the prefix required at the bottom of the table. Make sure appropriate rows for both Tables (on the left) and Data (on the right) get ticked.
Now click the Export button. After a while a standard download dialogue box should open for managing dump file download to your computer.
Should you need to restore the file you use the Import button, browse to locate the dump file on your computer, and click the execute button.
Permissions for the adminer executable can be left as 644. Anyone trying to interfere with your database has know its name and password.
David

[#13]

Hi David,
Thanks again for your help.  I feel much happier now that I have all my files and the database backed up locally.
Kind regards,
Jules.

[#14]

I'm probably a bit late to mention mysqldump, but if we want an automated local backup, we can still use that and request a cron.
Something like:

#!/bin/bash
echo "Content-type: text/plain"
echo
# Add in your mysql details here
MYSQL_HOST=
MYSQL_USER=
MYSQL_PASSWORD=
MYSQL_DATABASE=
# Add in your email address
EMAIL=
#Select files to backup
# . backs up everything
# or select files and folders
# between quotes, e.g.:
# "mysql_backup.sql wordpress/wp-content/ foo.txt bar/foo.txt"
BACKUPFILE="mysql_backup.sql"
#Nothing more to add
DATE=$(date +'%Y-%m-%d')
cd ~/public
mysqldump --opt -h $MYSQL_HOST -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE > mysql_backup.sql
cat <<EOF> tmpfile.txt
To: $EMAIL
From: $EMAIL
Subject: Backup on $DATE
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary=$DATE-backup
--$DATE-backup
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
See attached.
--$DATE-backup
Content-Type: application/x-tgz
Content-Disposition: attachment; filename=backup.tar.gz
Content-Transfer-Encoding: base64
EOF
if [ -e $BACKUPFILE ]
then
tar -czf - $BACKUPFILE | openssl base64 >> tmpfile.txt
cat <<EOF>> tmpfile.txt
--$DATE-backup--
EOF
fi
cat tmpfile.txt | /usr/sbin/sendmail -t -i
rm tmpfile.txt mysql_backup.sql
echo "Backup sent to "$EMAIL

Gabe

[#15]

That would also work. Bit of a pain to get the crontab set up though.

Quote from: jtonline

However, when I click Go when using Internet Explorer it comes up with Error 500 and if I do the same steps using Google Chrome I get 'No web page was found for the web address http://ccgi.xxxxxxxx.plus.com/phpmyadmin/export.php'. ; No browser download dialogue.

To conclude, Hostopia have confirmed that this is happening because your database is hitting size limits (set to the default 128M I'm told). I tried it with another customer's database that's bigger than yours and encounter the same problem.
Looks like you'll have to settle for one of the alternative methods rather than relying on phpMyAdmin for back-ups (the alternatives are probably a lot quicker anyway if I'm honest).