Gallery2:How do I Make Backups of My Database - Gallery Codex
Personal tools

Gallery2:How do I Make Backups of My Database

From Gallery Codex

Revision as of 09:13, 16 April 2008 by Wilburt (Talk | contribs) (Using Straight MySQL Code)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

It is strongly recommended that you backup your database at regular intervals and before an upgrade.

Log into phpMyAdmin

From the main login screen, select 'Databases'

Podz-gallery1.png

Find Database

Now click the name of your database - or your Gallery database if you have several databases.

Podz-Gallery2.png

Click Export

The next screen will show you all the tables inside your Gallery database.

Podz-Gallery3.png

Ignore those, and click the 'Export' tab on the top set of tabs.


Export Options

Look at the left box at the top of the Export section. All the tables in the database you selected are in that box.
If you have other programs that use the database, then choose only those tables that correspond to your Gallery install. They will be the ones that start with "g2_" or whatever 'tablePrefix' you specified in your 'config.php' file.

You need to select the radio buttons and check the checkboxes as shown below.

Podz-Gallery4.png


Tick the 'Save as file' option, and leave the template name alone.
For now, select 'None' for compression.

Now click 'Go' and you should be prompted for a file to download. Save the file to your computer.


Backup Files

Podz-Gallery5.png


Depending on the database size, this may take a few moments.

Once that download is complete, check the 'zipped' option, click 'Go', and download the next file.
If you wanted, you could download a backup in each of the compression formats. Your choice.

You have now backed up your database.

Remember - you have NOT backed up the files and folders - such as images - but all your users, comments and albums are now safe.




Using Straight MySQL Code

phpMyAdmin can not handle large databases so using straight MySQL code will help.

1. Change your directory to the directory you want to dump things to:

user@linux:~> files/g2_backup 

2. Use mysqldump (man mysqldump is available):

user@linux:~/files/g2_backup> mysqldump --add-drop-table -h mysqlhostserver
 -u mysqlusername -p databasename (tablename tablename tablename) | bzip2
 -c > g2.bak.sql.bz2

Enter password: (enter your mysql password)
user@linux~/files/g2_backup>
Example:
mysqldump --add-drop-table -h g2.example.net -u dbocodex -p dbg2 | bzip2 -c > g2.bak.sql.bz2

Enter password: my-password
user@linux~/files/g2_backup>

The bzip2 -c after the | (pipe) means the backup is compressed on the fly. It does in one line the same thing that these two commands do:

mysqldump --add-drop-table -h g2.example.net -u dbocodex -p dbg2 > g2.bak.sql
bzip2 g2.bak.sql

To email the backup:

mysqldump --add-drop-table -h g2.example.net -u dbocodex -p dbg2 | 
 gzip -c | 
 uuencode gallery.sql.gz | 
 mail -s "Gallery Database Backup" email@example.net

Using MySQL Administrator

MySQL Administrator is a program for performing administrative operations, such as configuring your MySQL server, monitoring its status and performance, starting and stopping it, managing users and connections, performing backups, restoring backups and a number of other administrative tasks. You can perform most of those tasks using a command line interface such as that provided by mysqladmin or mysql, but MySQL Administrator is advantageous in the following respects:

  • Its graphical user interface makes it more intuitive to use.
  • It provides a better overview of the settings that are crucial for the performance, reliability, and security of your MySQL servers.
  • It displays performance indicators graphically, thus making it easier to determine and tune server settings.
  • It is available for Linux, Windows and MacOS X, and allows a remote client to backup the database across platforms. As long as you have access to the MySQL databases on the remote server, you can backup your data to wherever you have write access.
  • There is no limit to the size of the database to be backed up as there is with phpMyAdmin.

MySQL Administrator is designed to work with MySQL servers versions 4.0 and above.

Getting MySQL Admin

MySQL Admin may be downloaded from the MySQL.Com site. Installation binaries and documentation may also be found there.

Backing Up the Database

This assumes you have already installed MySQL Admin and set it up so that you can login to the MySQL Database Server either locally or remotely. Refer to the documentation that comes with the installation package of MySQL Admin for your platform for installation instructions.

1. Open the MySQL Admin client and login as you had previously set up to do.

2. From the icon menu on the left hand side of the client window select Backup.

3. If you have not already created a Backup Project, do this now by clicking on the "New Project" button at the lower part of the window and type in a name for the Backup Project where prompted.

4. Select one or more databases that you want to Backup (in the MySQL Admin client these are called a "Schema" (pl. "Schemata")). Add them to the Backup Content window on the right using the right-pointing arrow button.

5. When you have selected the Schema(ta), you can save the Backup Project. Or you may simply choose to Backup Now using the button on the lower right of the window.

6. A dialogue will come up asking you where to put the Backup. Enter the pathname or browse to the location using the dialogue.

7. Assuming all is correct (and you have write permissions in the directory to which you are writing the Backup), the backup will complete shortly.

Restoring From a Backup

1. Open the MySQL Admin client and login as you had previously set up to do.

2. From the icon menu on the left hand side of the client window select Restore.

3. Click the "Open Backup File" button on the lower right of the window.

4. Type in or browse to the Schema(ta) backup file and select. Click "Open".

5. The Target Schema(ta) will most likely be the "Original Location", or you may choose an alternate location using the drop-down menu.

6. Click the "Start Restore" button on the lower right of the window. The database restore will commence.

Content on this page was copied from the WordPress Codex, with permission from the original author Podz, in order to kick-start the Gallery documentation process.

MySQL Code

1. Uncompress the file

user@linux:~> bunzip2 g2.bak.sql.bz2

2. Use mysqladmin to create database

Example:
mysqladmin -u dbocodex -h g2.example.net -p create dbg2

Enter passwored: my-password

3. Restore database

mysql -u dbocodex -h g2.example.net -p dbg2 < g2.bak.sql
advertisements