======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>