Backup Files & Databases

backups.sh

#
# BACKUP FILES
# sources:
# https://tonyteaches.tech/rsync-backup-tutorial/
# https://www.nexcess.net/help/using-rsync-for-backups/
#

# daily
tar -zcf /home/tony/backup/daily/backup-$(date +%Y%m%d).tar.gz /home/website/public_html
find /home/tony/backup/daily/* -mtime +7 -delete

# weekly
tar -zcf /home/tony/backup/weekly/backup-$(date +%Y%m%d).tar.gz -C /var/www/ html
find /home/tony/backup/weekly/* -mtime +31 -delete

# monthly
tar -zcf /home/tony/backup/monthly/backup-$(date +%Y%m%d).tar.gz -C /var/www/ html
find /home/tony/backup/monthly/* -mtime +365 -delete

# cronjob each backupd frequency
15 0 * * * sh /home/tony/backup-daily.sh
30 0 * * 1 sh /home/tony/backup-weekly.sh
45 0 1 * * sh /home/tony/backup-monthly.sh

# rsync from source server to destiny server
rsync -a --delete /home/tony/backup/ root@161.35.143.122:/path/to/remote/backups/
rsync -avzHP website_backup.tar.gz root@destinationhost:/home/wpsite/ (will ask pwd)

# rsync from destiny server to source server (specifying the port 2254) :)
rsync -azvHP -e "ssh -p 2254" root@sourcehost:/var/www/myweb.com/webbackup.tar.gz /home/wpsite/ (will ask pwd)

# cronjob rsync command
0 2 * * * rsync -a --delete /home/tony/backup/ root@161.35.143.122:/path/to/remote/backups/

# copy ssh from source to destiny server
ssh-copy-id -i ~/.ssh/mykey user@destinyhost

#
# BACKUP DATABASES
#

#Back up From the Command Line (using mysqldump)

#If you have shell or telnet access to your web server, you can backup your MySQL data by using the mysqldump command. 
#This command connects to the MySQL server and creates an SQL dump file. 
#The dump file contains the SQL statements necessary to re-create the database. Here is the proper syntax:
mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]

#For example, to backup a database named 'Tutorials' with the username 'root' and with no password to a file tut_backup.sql, 
#you should accomplish this command
mysqldump -u root -p Tutorials > tut_backup.sql
#This command will backup the 'Tutorials' database into a file called tut_backup.sql
#which will contain all the SQL statements needed to re-create the database

#With mysqldump command you can specify certain tables of your database you want to backup.
#For example, to back up only php_tutorials and asp_tutorials tables from the 'Tutorials' database accomplish the command below.
#Each table name has to be separated by space.
mysqldump -u root -p Tutorials php_tutorials asp_tutorials > tut_backup.sql

#Sometimes it is necessary to back up more that one database at once. 
#In this case you can use the --database option followed by the list of databases you would like to backup.
#Each database name has to be separated by space
mysqldump -u root -p --databases Tutorials Articles Comments > content_backup.sql

#If you want to back up all the databases in the server at one time you should use the --all-databases option. 
#It tells MySQL to dump all the databases it has in storage.
mysqldump -u root -p --all-databases > alldb_backup.sql

#Back up your MySQL Database with Compress

#If your mysql database is very big, you might want to compress the output of mysqldump.
#Just use the mysql backup command below and pipe the output to gzip, then you will get the output as gzip file
mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]

#If you want to extract the .gz file, use the command below
gunzip [backupfile.sql.gz]

#Restoring your MySQL Database

mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]

#Have a look how you can restore your tut_backup.sql file to the Tutorials database.
mysql -u root -p Tutorials < tut_backup.sql

#To restore compressed backup files you can do the following
gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

#If you need to restore a database that already exists, you'll need to use mysqlimport command. 
#The syntax for mysqlimport is as follows:
mysqlimport -u [uname] -p[pass] [dbname] [backupfile.sql]