2,187
edits
m (typo) |
m (→The Script: Updated URL in script) |
||
(4 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="password" # MySQL password | MPASS="password" # MySQL password | ||
MHOST="localhost" | MHOST="localhost" | ||
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" |