Exporting a MySQL database: Difference between revisions

Line 35:

* Select 'System Cron' from the radio buttons in the top task bar.

* Select 'New entry'.

* In the command box, we need to formulate a command. The following command gives most of the things you may need in the command and can be adapted to suit your needs:<br><pre>mysqldump -A -u USER -pPASSWORD > /path/to/backup/backup-name-$(date+\%d-\%m-\%Y).sql</pre>

* In the command box, we need to formulate a command. The following command gives most of the things you may need in the command and can be adapted to suit your needs:<br><pre>mysqldump -A -u USER -pPASSWORD > /path/to/backup/backup-name-$(date +%Y-%m-%d).sql</pre>

** <code>-A</code> switch makes <code>mysqldump</code> backup all databases.

** <code>USER</code> should be changed to the user.

** <code>PASSWORD</code> should be changed to the password used for the user. Notice, no space between the switch <code>-p</code> and <code>PASSWORD</code> or the job will fail.

** <code>/path/to/backup</code> should be the full path to the location you want the backup to take place.

** <code>backup-name</code> should be the name of the backup file, <code>$(date+\%d-\%m-\%Y)</code> adds the date to the place you place it in the file name, especially handy if you want to keep your backups every day.

** <code>backup-name</code> should be the name of the backup file, <code>$(date +%Y-%m-%d)</code> adds the date to the place you place it in the file name, especially handy if you want to keep your backups every day. Starting with the year as opposed to the day makes sorting the files in lists a lot easier.

* Select the user you want this to run under (probably root)

* Now choose the time and date you would like the backup to take place. If you would like it to happen every day, select the 'Run every day' tick box and this will select all the relevant options, leaving you just to choose a time.

* Now select 'OK' and then 'OK' on the main window.

* Wait until the time of the backup and make sure the file is created correctly.

* Wait until the time of the backup and make sure the file is created correctly or press 'Run now' to test the command out now (This will also make sure you have an initial backup).

=== Troubleshooting ===

Line 50:

** Make sure there is no space between the <code>-p</code> switch and your password.

** Make sure MySQL is running

** Make sure the user has the correct ~~privilidges~~

** Make sure the user has the correct privileges

* The % on the date part of the command must be escaped with a \ or the code will stop past the %. Cron interprets % as the end of the line and must be escaped to make it pass it.

* Passwords with any special characters may make cron fail as it uses some characters for commands. You may have to change your MySQL password to an alphanumeric only password.

@@ Line 35: / Line 35: @@
 * Select 'System Cron' from the radio buttons in the top task bar.
 * Select 'New entry'.
-* In the command box, we need to formulate a command. The following command gives most of the things you may need in the command and can be adapted to suit your needs:<br><pre>mysqldump -A -u USER -pPASSWORD > /path/to/backup/backup-name-$(date+\%d-\%m-\%Y).sql</pre>
+* In the command box, we need to formulate a command. The following command gives most of the things you may need in the command and can be adapted to suit your needs:<br><pre>mysqldump -A -u USER -pPASSWORD > /path/to/backup/backup-name-$(date +%Y-%m-%d).sql</pre>
 ** <code>-A</code> switch makes <code>mysqldump</code> backup all databases.
 ** <code>USER</code> should be changed to the user.
 ** <code>PASSWORD</code> should be changed to the password used for the user. Notice, no space between the switch <code>-p</code> and <code>PASSWORD</code> or the job will fail.
 ** <code>/path/to/backup</code> should be the full path to the location you want the backup to take place.
-** <code>backup-name</code> should be the name of the backup file, <code>$(date+\%d-\%m-\%Y)</code> adds the date to the place you place it in the file name, especially handy if you want to keep your backups every day.
+** <code>backup-name</code> should be the name of the backup file, <code>$(date +%Y-%m-%d)</code> adds the date to the place you place it in the file name, especially handy if you want to keep your backups every day. Starting with the year as opposed to the day makes sorting the files in lists a lot easier.
 * Select the user you want this to run under (probably root)
 * Now choose the time and date you would like the backup to take place. If you would like it to happen every day, select the 'Run every day' tick box and this will select all the relevant options, leaving you just to choose a time.
 * Now select 'OK' and then 'OK' on the main window.
-* Wait until the time of the backup and make sure the file is created correctly.
+* Wait until the time of the backup and make sure the file is created correctly or press 'Run now' to test the command out now (This will also make sure you have an initial backup).
 === Troubleshooting ===
@@ Line 50: / Line 50: @@
 ** Make sure there is no space between the <code>-p</code> switch and your password.
 ** Make sure MySQL is running
-** Make sure the user has the correct privilidges
+** Make sure the user has the correct privileges
 * The % on the date part of the command must be escaped with a \ or the code will stop past the %. Cron interprets % as the end of the line and must be escaped to make it pass it.
 * Passwords with any special characters may make cron fail as it uses some characters for commands. You may have to change your MySQL password to an alphanumeric only password.