CMS-Experte | IT-Consultant in Berlin

Performing a Full Backup with mysqldump

When you perform a full backup of your Mambo or Joomla SQL database using mysqldump you may run into problems. Use the -Q switch to quote all identifiers. Otherwise the output of mysqldump can't be used to restore a database and results in a syntax error.

Your sample error output may look similar to the following:

ERROR 1064 (42000) at line 241: You have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right syntax to use
near 'option varchar(50) NOT NULL default '',
ordering int(11) NOT NULL default '0',' at line 9

This is because the mos_components table definition contains the column name option which is a reserved word in MySQL. To perform a full backup that can be used to restore a Mambo database, type the following to back up your database:

mysqldump -p -uusername -Q -c --add_drop_table databasename > backup.sql

You can than restore your database using this backup file:

mysql -p -uusername databasename < backup.sql

The -c switch provides for complete insert statements and --add_drop_table does exactly what it says. It adds SQL statements to drop a table before it is created again, thus avoiding errors on already existing tables when restoring the database.

Backing Up the Table Data only

If you only want to back up the table data and not the table structure, use the following command:

mysqldump -p -uusername -Q -c -t databasename > backup.sql

The -t switch tells MySQL not to include table creation information in the backup file. Restoring the data works the same way as above.