- [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 | Previous revision | ||
linux:mysql [2019/01/12 13:27] admin |
linux:mysql [2019/10/21 09:53] (current) admin |
||
---|---|---|---|
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> | ||
Line 89: | Line 123: | ||
# /etc/init.d/mysql stop | # /etc/init.d/mysql stop | ||
# /etc/init.d/mysql start | # /etc/init.d/mysql start | ||
+ | </code> | ||
+ | ===== Tunning ====== | ||
+ | <code> | ||
+ | max_connections = 500 | ||
+ | |||
+ | |||
+ | innodb_buffer_pool_dump_at_shutdown = 0 | ||
+ | innodb_buffer_pool_dump_pct = 100 | ||
+ | innodb_buffer_pool_instances = 8 | ||
+ | innodb_buffer_pool_load_at_startup = 0 | ||
+ | innodb_buffer_pool_size = 220G | ||
+ | innodb_log_file_size = 5G | ||
+ | thread_cache_size = 500 | ||
+ | thread_pool_size = 12 | ||
+ | innodb_read_io_threads = 64 | ||
+ | innodb_write_io_threads = 64 | ||
+ | |||
+ | tmp_table_size = 12G | ||
+ | max_heap_table_size = 12G | ||
+ | |||
+ | skip-name-resolve | ||
+ | |||
+ | query_cache_type = 0 | ||
+ | query_cache_limit = 128M | ||
+ | query_cache_min_res_unit = 4k | ||
+ | query_cache_size = 0 | ||
+ | |||
+ | max_allowed_packet = 1G | ||
+ | |||
+ | key_buffer_size = 64M | ||
+ | |||
+ | aria_pagecache_buffer_size = 1G | ||
+ | |||
+ | back_log = 500 | ||
+ | sort_buffer_size = 2M | ||
+ | |||
+ | join_buffer_size = 4M | ||
+ | table_open_cache = 10000 | ||
+ | performance_schema = ON | ||
+ | |||
+ | innodb_flush_log_at_trx_commit = 2 | ||
+ | innodb_lru_scan_depth = 128 | ||
+ | innodb_autoinc_lock_mode = 2 | ||
+ | |||
+ | #skip-log-error = OFF | ||
+ | |||
+ | slave-skip-errors=all | ||
</code> | </code> |
linux/mysql.1547296067.txt.gz · Last modified: 2019/01/12 13:27 by admin