Backup

• MYSQLDUMP
  • This is a shell level command, not SQL
  • mysqldump [options] database_name [table_name]
  • mysqldump [options] --databases database_name
  • mysqldump [options] --all-databases
  • Output is to stdout, so you will probably need to redirect.
  • This will produce a set of commands to recreate and repopulate the tables, data, ...
  • There are MANY options.
  • mysql -p < backup
  • You need the options --triggers and --routines for these to be added to the dump.
  • Jason will be removing csci313Lin over the summer, if you want your data back it up.
• This is an example of a logical backup.
  • structure and content of the tables is stored.
  • Slower and larger than other methods.
  • Machine independent and portable, possibly to other DBMS
  • Can be done on a running server.
• Physical backups
  • Are a copy of the database as it appears in the files/directories
  • Granularity of restore is not as varied as logical.
  • Should be done when db is down, or tables are flushed and locked.
  • Method depends on type of table used.
  • cp, tar, scp, ...
• mysqlhotcopy
  • This is a shell level command (perl script)
  • It fulshes and locks tables
  • Then uses a copy command to back up the database.
  • But only for MyISAM tables.
• Backup Strategies
  • Establish a regular backup policy
  • Automate this backup policy
  • Check to see that the backups are effective
  • Keep checkpoints available off-site.
• Between Backups
  • DBMSs are capable of logging all transactions recorded.
  • INNODB --log_bin flag
  • "The binary log contains 'events' that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. "
  • Used for database replication
  • And for recovery.
  • You must configure the server to do this.
  • But you can replay it with mysqlbinlog