- [Show page]
- [Old revisions]
- [[unknown link type]]
- []
Differences
This shows you the differences between two versions of the page.
Both sides previous revision Previous revision | Next revision Both sides next revision | ||
linux:mysql [2019/01/12 13:27] admin |
linux:mysql [2019/01/12 13:41] admin [Backup] |
||
---|---|---|---|
Line 2: | Line 2: | ||
====== Backup ====== | ====== Backup ====== | ||
+ | ===== Simple //naive// backup ===== | ||
+ | Back up all databases into one file at once is a **BAD** idea. | ||
+ | - If there is a single problem (corrupted table, unreadable file, whatever), the rest of DB will **not* be dumped | ||
+ | - Restoring one DB within many from one file is difficult, time consuming and with slightly bigger databases ~ 10GB i wish you a lot of RUM | ||
+ | |||
+ | Here is an example anyway | ||
<code sql> | <code sql> | ||
- | GRANT LOCK TABLES, SELECT ON *.* TO 'backupuser'@'localhost' IDENTIFIED BY 'secret-password'; | + | mysqldump -u root -p --all-databases > all_db_backup.sql |
+ | </code> | ||
+ | |||
+ | In a script | ||
+ | <code sql> | ||
+ | mysqldump -u root --password="my_secret" --all-databases > all_db_backup.sql | ||
+ | </code> | ||
+ | If you are going to use this simple and //not so clever// backup, please at least make the file root readable only. | ||
+ | |||
+ | =====Better way===== | ||
+ | The backup user just need lock and read privileges. Create a backup user and grant lock tables and select privileges to it with the following MariaDB/MySQL commands | ||
+ | |||
+ | <code sql> | ||
+ | GRANT LOCK TABLES, SELECT ON *.* TO 'backupuser'@'localhost' IDENTIFIED BY 'some_passwd'; | ||
flush privileges; | flush privileges; | ||
+ | </code> | ||
+ | |||
+ | Then list all databases and in backup one after another. Use umask to keep you files root readable only. Here is our script for 7 days rotating backups. | ||
+ | <code bash> | ||
+ | DAY=$(date +"%w") | ||
+ | BACKUP_TMP="/some_storage/db/mysql-${DAY}" | ||
+ | PASS="some_passwd" | ||
+ | USR="backupuser" | ||
+ | |||
+ | umask 077 | ||
+ | mkdir -p $BACKUP_TMP | ||
+ | |||
+ | mysql -u $USR --password=$PASS -Bse "SHOW DATABASES" | while read DB ; do | ||
+ | mysqldump --no-tablespaces --single-transaction -u $USR --password=$PASS ${DB} | bzip2 > $BACKUP_TMP/${DB}.sql.bz2 | ||
+ | done | ||
</code> | </code> | ||
linux/mysql.txt · Last modified: 2019/10/21 09:53 by admin