backup/restore remotely

[inittux]

Finally wrote a to do list and learning about backups and restores is the first on my list so this will be a useful post

[inittux]

Works great. Made a backup of my web data and dumps of mysql db's. Now I can ftp them over to my own pc and keep backups there. All though I need to change the file permissions before I do.

[inittux]

what's the advantage of backing up a mysql db with a dump rather then making a tar archief? You can restore from both I would think.

[Dungeon-Dave]

• "tar" takes a series of files and creates one file from them.
  
  
  
• "mysqldump" dumps the contents of several tables/databases and creates one file from it.
  
  
  

 

A restore from a tar archive involves extracting the archive into a directory (and optionally moving the contents back to their original location, once verified).

 

A restore from a mysqldump file involves connecting to the database then "executing" (sourcing) the dump file - the file itself is essentially a load of SQL statements that recreates tables and constraints, restoring the data via a pile of insert statements.

 

They do the same kind of thing, just in a different context - one is for content held in directories, the other for content held in databases.

[inittux]

ah I see. Which is more pratical for backing up and restoring?

[Dungeon-Dave]

For databases... mysqldump.

 

For files... tar. (or rsync)

 

Both files can be compressed (gzip, bzip2) to reduce their size.

[hybrid]

A mysqldump as a .sql file is a much better prospect for restoring than tarring up the /var/lib/mysql/data (or wherever) directory.

 

If you run the tar of the MySQL data while the MySQL daemon is running, the data directory will be in an unknown state -- perhaps there are locks on tables, or tar runs at just the moment a new row is inserted, and your database backup is inconsistent and possibly useless. Even if the MySQL daemon isn't running, it's not good practice.

 

Running a proper MySQL dump avoids all of these issues -- dumping the data exactly as it is, in a format where MySQL can just execute the SQL statements, one by one, to recreate the databases at the exact time of the snapshot.

[inittux]

I succeeded in doing my backup and restore :)

backing up mysqldump:

 

Code:

mysqldump -u test_dba -pPassword1234 feedtest_db > testbackup.sql

backup htdocs:

Code:

tar -czpvf /home/user/website/htdocs /home/other-user/testbackup-htdocs.tar.gz

copy these files to my virtualbox test machine. On my test machine I create db/db user. Then after that is done.

 

Restore db:

Code:

mysql -u testuser -ppassword feedtest_db < testbackup..sql

 

then restore your webdata:

Code:

tar -zxvf testbackup-htdocs.tar.gz /home/user/website

 

Then you can see if you can get to your website and you will get the following error:Database connection error (2): Could not connect to MySQLThe problem which I figured out is that when setting up the db/user on the test machine is that you setup a different user/password, the credentials differs from the the credentials in the dumpfile. All you have to dois edit the configuration.php file of Joomla(in this case) and replace the user/password with the correct credentials and your site restore will have worked :)

[hybrid]

Nice to see you've got it working!