[Show page]
[Old revisions]
[[unknown link type]]
[
]

Differences

This shows you the differences between two versions of the page.

--- linux:mysql [2019/01/12 13:27]
admin
+++ linux:mysql [2019/10/21 09:53] (current)
admin
@@ Line 2: / Line 2: @@
 ====== 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>
-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;
+</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>
@@ Line 89: / Line 123: @@
 # /etc/init.d/mysql stop
 # /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>

linux/mysql.1547296067.txt.gz · Last modified: 2019/01/12 13:27 by admin