Embedded Files
Backup
Backup
To make a backup of a single database...
mysqldump -u myuser -p myDB > backup-myDB.sql
Backup entire DBMS (all tables consistent)...
mysqldump -A -R -E --single-transaction --quick --lock-tables=false >full-backup-$(date +%F).sql -u root -p
To create a dump file suitable for moving a database from one instance to another...
mysqldump -u myuser -p --single-transaction --databases myDB --result-file backup-myDB.sql
To dump just stored procedures, functions and triggers for all databases...
mysqldump -h myhost -u myuser -p -n -d -t --routines --all-databases > routines.sql
mysqldump -h myhost -u myuser -p --no-create-db --no-data --no-create-info --routines --all-databases > routines.sql
For a specific database...
mysqldump -h myhost -u myuser -p --no-create-db --no-data --no-create-info --routines --databases mydatabase > routines.sql
-h-u-p-n-d
-R-t>
-A-E
-R-t>
-A-E
--host--user--password--no-create-db--no-data--triggers--skip-triggers--routines--no-create-info--result-file--single-transaction--databases--all-databases--events--lock-tables--skip-lock-tables--quick--ignore-table="my.table"--hex-blob--where="expression"
hostnameusernamepassword (if no password passed then prompt for password)Suppress the CREATE DATABASE ... IF EXISTS statement for each dumped databaseNo row informationDump triggers (this is the default)Don't dump triggersDump stored routines (functions and procedures)Suppress the CREATE TABLE statement for each dumped tableOutput filePreserve data consistencylist of databaseAll databasesDump Events
Dump tables row by row, avoiding difficulties of buffering large amounts of data in memoryIgnore named tableDumps binary strings to hexadecimalConditional dump
Dump tables row by row, avoiding difficulties of buffering large amounts of data in memoryIgnore named tableDumps binary strings to hexadecimalConditional dump
Restore
Restore
NOTE: The script creates databases, tables, indexes etc, but does not create users or make grants
To load the dump file back into the server:
mysql myDB < backup-myDB.sql
Alternatively...
mysql -e "source /path-to-backup/backup-myDB.sql" myDB
Backup Script
Backup Script
sudo -u mysql -s vi mysql-backup.sh
mysql_config_editor is a safer way of storing and using passwords in scripts.
#!/bin/bashrm /backup/full-backup.sqlOmv /backup/full-backup.sql /backup/full-backup.sqlOmysqldump -A -R -E --single-transaction -u root -p53curEpa$$w0rd! >/backup/full-backup.sql
sudo -u mysql -s crontab -e
00 22 * * * /bin/bash /backup/mysql-backup.sh >/tmp/mysql-backup.out 2>&1
Bibliography
Bibliography
https://mysqlserverteam.com/creating-and-restoring-database-backups-with-mysqldump-and-mysql-enterprise-backup-part-1-of-2/ https://mysqlserverteam.com/mysql-shell-dump-load-part-2-benchmarks/ https://www.forknerds.com/reduce-the-size-of-mysql/https://www.mysqltutorial.org/mysql-copy-database/https://dba.stackexchange.com/questions/4086/dump-only-the-stored-procedures-in-mysqlhttps://www.eversql.com/exporting-mysql-schema-structure-to-xml-using-mysql-clients/https://simplebackups.com/blog/the-complete-mysqldump-guide-with-examples/https://www.red-gate.com/simple-talk/databases/mysql/backing-up-mysql-part-1-mysqldump/https://stackoverflow.com/questions/1078196/take-perfect-backup-with-mysqldumphttps://stackoverflow.com/questions/44723154/using-cronjob-to-automatically-backup-mysql-files
8.0https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html
5.6https://dev.mysql.com/doc/refman/5.6/en/mysqldump.html
8.0https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html
5.6https://dev.mysql.com/doc/refman/5.6/en/mysqldump.html
Page updated
Google Sites
Report abuse