Exporting a MySQL database - Revision history

Sam: Changed date code in cron command to correct order

2013-11-09T22:51:56Z

Changed date code in cron command to correct order

← Older revision		Revision as of 22:51, 9 November 2013
Line 35:		Line 35:
	* Select 'System Cron' from the radio buttons in the top task bar.		* Select 'System Cron' from the radio buttons in the top task bar.
	* Select 'New entry'.		* 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 +%Y-%m-%d).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 +%d-%m-%Y).sql</pre>
	** <code>-A</code> switch makes <code>mysqldump</code> backup all databases.		** <code>-A</code> switch makes <code>mysqldump</code> backup all databases.
	** <code>USER</code> should be changed to the user.		** <code>USER</code> should be changed to the user.

Sam: Changed date code in cron to correct order

2013-11-09T22:51:11Z

Changed date code in cron to correct order

← Older revision		Revision as of 22:51, 9 November 2013
Line 32:		Line 32:

	=== Creating the cron job ===		=== Creating the cron job ===
	* Log on as <code>root</code> and start KDE Task Scheduler from the Kickstart menu (this can be found by typing 'cron'.		* Log on as <code>root</code> and start KDE Task Scheduler from the Kickstart menu (this can be found by typing 'cron').
	* Select 'System Cron' from the radio buttons in the top task bar.		* Select 'System Cron' from the radio buttons in the top task bar.
	* Select 'New entry'.		* Select 'New entry'.
Line 40:		Line 40:
	** <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>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>/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 +%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.		** <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. 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)		* 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 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.

>Samthecrazyman: Removed escaping statement, not needed anymore

2013-05-07T00:43:41Z

Removed escaping statement, not needed anymore

← Older revision		Revision as of 00:43, 7 May 2013
Line 51:		Line 51:
	** Make sure MySQL is running		** Make sure MySQL is running
	** Make sure the user has the correct privileges		** 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.		* 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.
	* KDE task scheduler has a problem at the time of writing that when you edit a job, the user randomly changes. Make sure that when you edit a job, you change the user back to root or the job will more than likely fail.		* KDE task scheduler has a problem at the time of writing that when you edit a job, the user randomly changes. Make sure that when you edit a job, you change the user back to root or the job will more than likely fail.

>Samthecrazyman: Changed date format to be correct an work, spelling correction

2013-05-07T00:40:37Z

Changed date format to be correct an work, spelling correction

>Samthecrazyman: Changed formatting, added MySQL to related articles

2012-12-05T01:24:35Z

Changed formatting, added MySQL to related articles

>Samthecrazyman: Added automatic cron backup instructions.

2012-12-04T03:51:05Z

Added automatic cron backup instructions.

@@ Line 1: / Line 1: @@
-You may need to export a MySQL database if you are moving the database to another computer (for example, you are moving you Mediawiki server). You may also want to export the database to back it up as databases are not stored like files and must be recovered differently. This page will guide you through the process.
+You may need to export a MySQL database if you are moving the database to another computer (for example, you are moving you Mediawiki server). You may also want to export the database to back it up as databases are not stored like files and must be recovered differently. This page will guide you through the process and also show you how to create an automatic job to create these dumps for you.
 The process of exporting a database to a file is generally refereed to as 'dumping' the database (don't confuse with dropping a database!)
 == What you need to know ==
 You will need the following information before you start:
@@ Line 23: / Line 24: @@
 * Don't forget the '-c' option. This makes sure that mysqldump dumps the entire database exactly as it is.
 * To aid with importing later, it is worth dumping the database to a file with the name of the database itself. You need to make the database before importing and it must have the same as the original database.
-== Related Articles ==
+= Creating a cron job to back up MySQL automatically =
 * [[Importing a MySQL database]]

>Samthecrazyman: Changed pages to articles, small format changes

2012-03-01T03:12:24Z

Changed pages to articles, small format changes

← Older revision		Revision as of 03:12, 1 March 2012
Line 24:		Line 24:
	* To aid with importing later, it is worth dumping the database to a file with the name of the database itself. You need to make the database before importing and it must have the same as the original database.		* To aid with importing later, it is worth dumping the database to a file with the name of the database itself. You need to make the database before importing and it must have the same as the original database.

	== Related ~~pages~~ ==		== Related Articles ==
	[[Importing a MySQL database]]
			* [[Importing a MySQL database]]

>Samthecrazyman: Created, from Dokuwiki

2012-02-29T01:11:08Z

Created, from Dokuwiki

New page

You may need to export a MySQL database if you are moving the database to another computer (for example, you are moving you Mediawiki server). You may also want to export the database to back it up as databases are not stored like files and must be recovered differently. This page will guide you through the process.

The process of exporting a database to a file is generally refereed to as 'dumping' the database (don't confuse with dropping a database!)

== What you need to know ==
You will need the following information before you start:
* Root access to the MySQL server
* The name of the database to be exported (it might be an idea to write this down, you will need this name to import it later)
* A place to save it that you have write access to (a good place to be would be your home folder)

== Exporting your database ==
# If not at the console shell, open a console shell (Konsole in KDE)
# We are using a program called 'mysqldump'. This does what it says - dumps the database to a file.
# Type the following into the shell: <pre># mysqldump -u [username] -p [database name] -c > [path to file/file.sql]</pre> For example, assuming the MySQL user is root, the database name is wikidb and we are writing to the Linux root home directory, we will type the following: <pre># mysqldump -u root -p wikidb -c > /root/Documents/wikidb.sql</pre>
# MySQL will prompt you for the password for the user
# Once you have typed and entered the password, it may look like nothing is happening, be patient, it can take a while to dump the database, especially if it is a large database or the computer is slow.
# When all is finished you will be returned to the command prompt.
# Browse to the path and check the database dump file has been created and contains data (check the size, make sure it is more than a few kilobytes)
# You database is now dumped in a file!

== Troubleshooting ==
* Make sure you include a file path. mysqldump will by default dump to your screen if run without an input. If you see lots of scrolling with lots of random writing on the screen, you haven't included an output. The output comes after the >.
* Don't forget the '-c' option. This makes sure that mysqldump dumps the entire database exactly as it is.
* To aid with importing later, it is worth dumping the database to a file with the name of the database itself. You need to make the database before importing and it must have the same as the original database.

== Related pages ==
[[Importing a MySQL database]]

← Older revision		Revision as of 00:40, 7 May 2013
Line 35:		Line 35:
	* Select 'System Cron' from the radio buttons in the top task bar.		* Select 'System Cron' from the radio buttons in the top task bar.
	* Select 'New entry'.		* 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>-A</code> switch makes <code>mysqldump</code> backup all databases.
	** <code>USER</code> should be changed to the user.		** <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>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>/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)		* 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 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.		* 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 ===		=== Troubleshooting ===
Line 50:		Line 50:
	** Make sure there is no space between the <code>-p</code> switch and your password.		** Make sure there is no space between the <code>-p</code> switch and your password.
	** Make sure MySQL is running		** 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.		* 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.		* 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.

← Older revision		Revision as of 01:24, 5 December 2012
Line 3:		Line 3:
	The process of exporting a database to a file is generally refereed to as 'dumping' the database (don't confuse with dropping a database!)		The process of exporting a database to a file is generally refereed to as 'dumping' the database (don't confuse with dropping a database!)

	= Manually dumping a database =		== Manually dumping a database ==
	== What you need to know ==		=== What you need to know ===
	You will need the following information before you start:		You will need the following information before you start:
	* Root access to the MySQL server		* Root access to the MySQL server
Line 10:		Line 10:
	* A place to save it that you have write access to (a good place to be would be your home folder)		* A place to save it that you have write access to (a good place to be would be your home folder)

	== Exporting your database ==		=== Exporting your database ===
	# If not at the console shell, open a console shell (Konsole in KDE)		# If not at the console shell, open a console shell (Konsole in KDE)
	# We are using a program called 'mysqldump'. This does what it says - dumps the database to a file.		# We are using a program called 'mysqldump'. This does what it says - dumps the database to a file.
Line 20:		Line 20:
	# You database is now dumped in a file!		# You database is now dumped in a file!

	== Troubleshooting ==		=== Troubleshooting ===
	* Make sure you include a file path. mysqldump will by default dump to your screen if run without an input. If you see lots of scrolling with lots of random writing on the screen, you haven't included an output. The output comes after the >.		* Make sure you include a file path. mysqldump will by default dump to your screen if run without an input. If you see lots of scrolling with lots of random writing on the screen, you haven't included an output. The output comes after the >.
	* Don't forget the '-c' option. This makes sure that mysqldump dumps the entire database exactly as it is.		* Don't forget the '-c' option. This makes sure that mysqldump dumps the entire database exactly as it is.
	* To aid with importing later, it is worth dumping the database to a file with the name of the database itself. You need to make the database before importing and it must have the same as the original database.		* To aid with importing later, it is worth dumping the database to a file with the name of the database itself. You need to make the database before importing and it must have the same as the original database.

	= Creating a cron job to back up MySQL automatically =		== Creating a cron job to back up MySQL automatically ==
	== What you need to know ==		=== What you need to know ===
	* You will need KDE Task Scheduler installed. This will make creating a cron job easier.		* You will need KDE Task Scheduler installed. This will make creating a cron job easier.
	* Where you will be storing your dumps on an ongoing basis.		* Where you will be storing your dumps on an ongoing basis.
	* A user with read-only rights to access the database when backing up.		* A user with read-only rights to access the database when backing up.

	== Creating the cron job ==		=== Creating the cron job ===
	* Log on as <code>root</code> and start KDE Task Scheduler from the Kickstart menu (this can be found by typing 'cron'.		* Log on as <code>root</code> and start KDE Task Scheduler from the Kickstart menu (this can be found by typing 'cron'.
	* Select 'System Cron' from the radio buttons in the top task bar.		* Select 'System Cron' from the radio buttons in the top task bar.
Line 46:		Line 46:
	* 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.

	== Troubleshooting ==		=== Troubleshooting ===
	* If the job fails with <code>access denied</code> a few points to check are:		* If the job fails with <code>access denied</code> a few points to check are:
	** Make sure there is no space between the <code>-p</code> switch and your password.		** Make sure there is no space between the <code>-p</code> switch and your password.
Line 55:		Line 55:
	* KDE task scheduler has a problem at the time of writing that when you edit a job, the user randomly changes. Make sure that when you edit a job, you change the user back to root or the job will more than likely fail.		* KDE task scheduler has a problem at the time of writing that when you edit a job, the user randomly changes. Make sure that when you edit a job, you change the user back to root or the job will more than likely fail.

	= Related Articles =		== Related Articles ==

	* [[Importing a MySQL database]]		* [[Importing a MySQL database]]
			* [[MySQL]]