Mysql backup and restore - Revision history

Nighthawk at 04:01, 12 June 2018

2018-06-12T04:01:50Z

Nighthawk at 16:03, 26 September 2013

2013-09-26T16:03:03Z

Nighthawk: Pushed from thelinuxwiki.com.

2013-02-25T19:01:36Z

Pushed from thelinuxwiki.com.

New page

Backing up all databases
mysqldump -u root -h localhost -p --all-databases > backup.sql

Restoring all databases
mysql -u root -h localhost -p < backup.sql

Automated backup (not tested yet)

You can automate the backup process by making a small shell script which will create a daily backup file. How do you get cron to back up your database without overwriting the older backup? You can use a tiny shell script to add the date to your backup file. An example of a shell script you could use is shown below.
#!/bin/sh
date=`date -I`
mysqldump --all-databases | gzip > /var/backup/backup-$date.sql.gz

Dump all databases for backup. Backup file is sql commands to recreate all db's.
# [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql
Dump one database for backup.

# [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql
Dump a table from a database.

# [mysql dir]/bin/mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql
Restore database (or database table) from backup.

# [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql
Create Table Example 1.

@@ Line 4: / Line 4: @@
 Restoring all databases
    mysql -u root -h localhost -p < backup.sql
 Automated backup (not tested yet)

@@ Line 13: / Line 13: @@
 Dump all databases for backup. Backup file is sql commands to recreate all db's.
-   # [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql
+   # [mysql dir]/bin/mysqldump -u root -p <password> --opt >/tmp/alldatabases.sql
 Dump one database for backup.
-   # [mysql dir]/bin/mysqldump -u username -ppassword --databases databasename >/tmp/databasename.sql
+   # [mysql dir]/bin/mysqldump -u username -p <password> --databases databasename >/tmp/databasename.sql
 Dump a table from a database.
@@ Line 22: / Line 22: @@
 Restore database (or database table) from backup.
-   # [mysql dir]/bin/mysql -u username -ppassword databasename < /tmp/databasename.sql
+   # [mysql dir]/bin/mysql -u username -p <password> databasename < /tmp/databasename.sql
 Create Table Example 1.