Differences

This shows you the differences between two versions of the page.

--- docs:mysql:mysqldump [2007/08/08 11:27] – created billh
+++ docs:mysql:mysqldump [2008/08/03 00:25] (current) – external edit 127.0.0.1
@@ Line 10: / Line 10: @@
 ===== examples =====
+My example...adds create database statements too so the only thing you need to do is source the file to restore
+<code>
+mysqldump -u user -p --opt --databases "database" > "database".sql
+</code>
 The most normal use of mysqldump is probably for making a backup of whole databases.  See Mysql Manual section 21.2 Database Backups.
 <code>
-mysqldump --opt database > backup-file.sql
+shell> mysqldump --opt database > backup-file.sql
 Note: --opt is the same as: --add-drop-table --add-locks --all --extended-insert --quick --lock-tables
 </code>
+Most likely, you will need to provide user credentials:
+<code>
+shell> mysqldump --opt -u user -p database > backup-file.sql
+</code>
 You can read this back into MySQL with:
 <code>
-mysql database < backup-file.sql
+shell> mysql database < backup-file.sql
 or
-mysql -e 'source /path-to-backup/backup-file.sql' database
+shell> mysql -e 'source /path-to-backup/backup-file.sql' database
+or
+mysql> source /path-to-backup/backup-file.sql
+or, most likely...
+mysql> create database databasename;
+mysql> use databasename;
+mysql> source /path-to-backup/backup-file.sql
+* the source command can be followed with a path containing spaces, without escapes or quotes
 </code>
 More options and examples are available in the man page and in the MySQL manual
+===== automated shell script =====
+<code>
+#!/bin/bash
+# user must have select and lock tables privileges
+destination='/path-to-dest-dir/MySQL Backups/'
+databases="database1
+database2
+database3"
+user=username
+pass=password
+# make the destination folder, if it doesn't exist
+if [ ! -d "$destination" ]; then
+	mkdir -p "$destination"
+fi
+# continue if the directory was successfully created
+if [ -d "$destination" ]; then
+	for db in $databases
+	do
+		echo "$db..."
+		mysqldump --opt -u$user -p$pass "$db" > "$destination$db".sql
+	done
+fi
+exit 0
+</code>