2,187
edits
Difference between revisions of "Backup MySQL (Ubuntu)"
Jump to navigation
Jump to search
m
→The Script: Updated URL in script
(Initial creation) |
m (→The Script: Updated URL in script) |
||
| (5 intermediate revisions by the same user not shown) | |||
| Line 6: | Line 6: | ||
** Can use [[Acronyms#F|FTPS]] for secure backup transfer | ** Can use [[Acronyms#F|FTPS]] for secure backup transfer | ||
=== Set-up === | |||
# Create the required folders using... | # Create the required folders using... | ||
#* <code> mkdir backup </code> | #* <code> mkdir backup </code> | ||
| Line 14: | Line 14: | ||
#* <code> chmod +x /backup/mysql.sh </code> | #* <code> chmod +x /backup/mysql.sh </code> | ||
# Perform a test run of the backup | # Perform a test run of the backup | ||
# Schedule the script to run with crontab | # Schedule the script to run with crontab | ||
#* <code> crontab -e </code> | #* <code> crontab -e </code> | ||
#* <code> 30 1 * * * /bin/bash /backup/mysql.sh </code> | #* <code> 30 1 * * * /bin/bash /backup/mysql.sh </code> | ||
To create a MySQL user with limited privileges to run the backup with use... | |||
<source lang="mysql"> | |||
GRANT SELECT,LOCK TABLES ON *.* TO backup@'localhost' IDENTIFIED BY 'password'; | |||
</source> | |||
=== The Script === | |||
<source lang="bash"> | <source lang="bash"> | ||
#!/bin/bash | #!/bin/bash | ||
| Line 26: | Line 31: | ||
# Simon Strutt (Apr 2012) | # Simon Strutt (Apr 2012) | ||
# | # | ||
# See | # See https://vwiki.co.uk/Backup_MySQL_(Ubuntu) | ||
### MySQL Server Login and local backup info ### | ### MySQL Server Login and local backup info ### | ||
MUSER=" | MUSER="backup" # MySQL user | ||
MPASS=" | MPASS="password" # MySQL password | ||
MHOST="localhost" | MHOST="localhost" | ||
MYSQL="$(which mysql)" | MYSQL="$(which mysql)" | ||
| Line 42: | Line 47: | ||
## FTP info | ## FTP info | ||
FTPDIR="/ | FTPDIR="/backup/server1/db" # Folder path on FTP server | ||
FTPUSER="backup" | FTPUSER="backup-user" # FTP username | ||
FTPPASS="backup" | FTPPASS="backup-pass" # FTP password | ||
FTPSERVER=" | FTPSERVER="ftp.domain.com" # FTP server name/IP | ||
FTPS= | FTPS=0 # Set to 1 enable FTP over SSL/TLS | ||
## Functions | ## Functions | ||
| Line 74: | Line 79: | ||
else | else | ||
# Is backup required? | # Is backup required? | ||
DBUPDATE=`mysql -u | DBUPDATE=`mysql -u $MUSER -h $MHOST -p$MPASS -Bse "SELECT max(update_time) FROM information_schema.tables WHERE table_schema='${db}'"` | ||
DBUPDATEU=`date --date "${DBUPDATE}" +%s` | DBUPDATEU=`date --date "${DBUPDATE}" +%s` | ||
#Logger "$db last update was $DBUPDATE" | #Logger "$db last update was $DBUPDATE" | ||
if [ -f ${STAMPS}/${db} ] | if [ -f ${STAMPS}/${db} ] | ||
then | then | ||
BAKDATE=` | BAKDATE=`cat ${STAMPS}/${db}` | ||
BAKDATEU=`date --date "${BAKDATE}" +%s` | BAKDATEU=`date --date "${BAKDATE}" +%s` | ||
#Logger "$db last backup was $BAKDATE" | #Logger "$db last backup was $BAKDATE" | ||