Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
linux:mysql [2009/04/06 15:09]
admin
linux:mysql [2019/10/21 09:53]
admin
Line 1: Line 1:
 +======MySQL======
  
 +====== 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>
 +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 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 a guarantee that the slave will be in sync with the master at any one point in time. Even assuming the connection is always up, a busy slave may not yet have caught up with the master, so you can't simply interchange SELECT queries across master and slave servers.
 +Source: [[http://​www.databasejournal.com/​features/​mysql/​article.php/​3355201/​Database-Replication-in-MySQL.htm]]
 +
 +=====Moving to another server=====
 +First import mysql tables whith all users
 +<​code>​
 +mysql -u root -p mysql <​temp/​mysql.sql ​
 +</​code>​
 +
 +Then you can do ''​flush privileges''​ or use the old password. Finaly import all databases
 +
 +<​code>​
 +cd /srv/temp
 +for i in *; do 
 +   ​db=${i%%.sql};​
 +   echo $db;
 +   ​mysqladmin create $db --password=xxxxx;​
 +   mysql -u root --password=xxxxxx $db <$i;
 +done
 +</​code>​
 +
 +unfortunately in the middle the cycle failed (i caused it intentionaly)
 +<​code>​
 +X=0
 +for i in *; do
 +   test $X -eq 0 && mv $i $i.done;
 +   if [ $i = '​gld.sql'​ ]; then
 +     X=1;
 +   fi;
 +done
 +</​code>​
 +
 +===== Reparing Tables=====
 +<​code>​
 +mysql> CHECK TABLE fixtures;
 +mysql> REPAIR TABLE fixtures;
 +mysql> REPAIR TABLE fixtures USE_FRM;
 +</​code>​
 +
 +  * http://​felipecruz.com/​repair-mysql-database.php
 +====== Performance ======
 +  * 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.txt · Last modified: 2019/10/21 09:53 by admin