- [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 [2009/04/24 03:08] admin |
linux:mysql [2019/10/21 09:53] (current) admin |
||
---|---|---|---|
Line 1: | Line 1: | ||
======MySQL====== | ======MySQL====== | ||
- | =====Replication===== | + | ====== Backup ====== |
- | ====What replication is not==== | + | ===== 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> | ||
+ | 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> | ||
+ | |||
+ | ======Replication====== | ||
+ | =====Correcting an error===== | ||
+ | Chyba: | ||
+ | |||
+ | |||
+ | phpmyadmin nemohl neco neco neco | ||
+ | |||
+ | |||
+ | Reseni: | ||
+ | <code> | ||
+ | stop slave; | ||
+ | SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; | ||
+ | start slave; | ||
+ | |||
+ | show slave status \G | ||
+ | ....: YES | ||
+ | ....: YES | ||
+ | </code> | ||
+ | |||
+ | =====What replication is not===== | ||
* Replication is not a backup policy. A mistyped DELETE statement will be replicated on the slave too, and you could end up with two, perfectly synchronized, empty databases. Replication can help protect against hardware failure though. | * Replication is not a backup policy. A mistyped DELETE statement will be replicated on the slave too, and you could end up with two, perfectly synchronized, empty databases. Replication can help protect against hardware failure though. | ||
* Replication is not an answer to all performance problems. Although updates on the slave are more optimized than if you ran the updates normally, if you use MyISAM tables, table-locking will still occur, and databases under high-load could still struggle. | * Replication is not an answer to all performance problems. Although updates on the slave are more optimized than if you ran the updates normally, if you use MyISAM tables, table-locking will still occur, and databases under high-load could still struggle. | ||
Line 47: | Line 105: | ||
====== Performance ====== | ====== Performance ====== | ||
* http://www.mysqlperformanceblog.com | * http://www.mysqlperformanceblog.com | ||
+ | |||
+ | |||
+ | ====== Regain root access ====== | ||
+ | |||
+ | From http://www.simplehelp.net/2008/11/26/how-to-reset-a-lost-mysql-root-password/ | ||
+ | |||
+ | <code> | ||
+ | # /etc/init.d/mysql stop | ||
+ | # mysqld_safe --skip-grant-tables & | ||
+ | # mysql -u root | ||
+ | |||
+ | mysql> use mysql; | ||
+ | mysql> update user set password=PASSWORD("NewMySQLPassword") where User="root"; | ||
+ | mysql> flush privileges; | ||
+ | mysql> quit | ||
+ | |||
+ | # /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.1240535318.txt.gz · Last modified: 2014/07/21 16:04 (external edit)