mysql my-note


# troubleshooting
## Force output to ERRORLOG
`mysqladmin -u root debug`

## reduce mysql memory (table_definition_cache)
http://dba.stackexchange.com/questions/64055/how-to-reduce-mysql-memory-used

set `table_definition_cache` !
## Repair MySQL GTID replication
https://www.percona.com/blog/2013/03/26/repair-mysql-5-6-gtid-replication-by-injecting-empty-transactions/

### Skip error sql @ GTID
1. `show slave status \G` and Get current executing GTID.
2. `stop slave`
3. `set GTID_NEXT = "<Current_GTID>"`
4. `begin; commit; SET GTID_NEXT="AUTOMATIC";`
5. `start slave`

__How to get current GTID?__
`show slave status \G` display

```
Last_SQL_Error: Error 'Duplicate entry '4' for key 'PRIMARY'' on query. Default database: 'test'. Query: 'insert into t VALUES(NULL,'salazar')'
Retrieved_Gtid_Set: 7d72f9b4-8577-11e2-a3d7-080027635ef5:1-5
Executed_Gtid_Set: 7d72f9b4-8577-11e2-a3d7-080027635ef5:1-4
```

`Retrieved_Gtid_Set` is current master GTID (range). Last GTID is `7d72f9b4-8577-11e2-a3d7-080027635ef5:5`
`Executed_Gtid_Set` is already executed GTID. Last executed GTID is `7d72f9b4-8577-11e2-a3d7-080027635ef5:4`
So, Now failed at `7d72f9b4-8577-11e2-a3d7-080027635ef5:5`.

# troubleshooting @ AWS/RDS
## How to skip repli-error?
`CALL mysql.rds_skip_repl_error;`

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.MySQL.SQLRef.html

## `GRANT ALL wildcard` !
`mysql> GRANT ALL ON '%'.* TO 'admin_sync'@'%';`

## Kill Process...
`CALL mysql.rds_kill(processID);`
or rds_kill_query ..

# mysqldump / restore data contains foreign key

```
mysql> set foreign_key_checks=0;
mysql> \. [your dump file]
```
Do not have to `set foreign_key_checks=1`. This setting apply to only current session.

# For systemd..
## mysqld.log `Buffered warning: Changed limits: max_connections: 214 (requested xxx)`
Env: centos7 & mysql-5.6.28 (rpm install)
`systemd`'s unit file ___DOES NOT___ refs `/etc/security/limits.conf`. You must set `LimitNOFILE=xxx` @ systemd unit file.

## Edit /etc/systemd/system/mysqld.service
Or /etc/systemd/system/mysqld.service.d/limit.conf (mkdir & mkfile)

```ini:
# append
LimitNOFILE=infinity
LimitMEMLOCK=infinity
```
and `systemctl daemon-reload`
and `systemctl restart mysqld`

## More effective way!
https://blog.kteru.net/systemd-ulimit-tips/

Create `/etc/systemd/system/mysqld.service.d/limits.conf` and restart mysql, ~~but ___have not applied it___ .. why?~~ I had typo `mysql.service.d`, forget mysql`d`.

## mysqld_safe & /etc/security/limits.conf
`mysqld_safe` = (RPM default) ignore /etc/security/limits.conf
http://studio3104.hatenablog.com/entry/20121030/1351587278

# User Account
## create users
```sql:
mysql>
GRANT ALL PRIVILEGES ON *.* TO system@localhost IDENTIFIED BY 'my_pass' WITH GRANT OPTION;

mysql>
GRANT ALL PRIVILEGES ON *.* TO system@`%` IDENTIFIED BY 'my_pass' WITH GRANT OPTION;
```
With GRANTとは、他人に権限を付けることができるという意味。
## for aws RDS
```
mysql> GRANT ALL ON *.* TO 'admin_sync'@'%';
ERROR 1045 (28000): Access denied for user 'admin'@'%' (using password: YES) issue"
```
solution

Now, that we know we can use `%` as the wildcard at the database level, let’s try this:

```sql:
mysql> GRANT ALL ON *.* TO 'admin_sync'@'%';
ERROR 1045 (28000): Access denied for user 'admin'@'%' (using password: YES) issue"
```

# logging
## error-log
```ini:my.cnf
[mysqld_safe]
log-error=/var/log/mysqld.log
```
You can set `mysqld` directive or `mysqld_save` directive.


## slow-log
```ini:my.cnf
[mysqld]
slow_query_log=ON
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=1
```
## rotation
mysqlのログはプロセスが握ってしまうため、SQL一発打つ
`flush logs` だけだと、bin-logも対象になるので気をつけて

```
#slow log only
mysql> flush slow logs;

#gen log only
mysql> flush general logs;

#bin log only
mysql> flush binary logs;

#error log only
mysql> flush error logs;
```

# Replication (traditional)
1. my.cnf `server-id`の指定
2. レプリケーション用ユーザの作成
3. SRC-mysqld table read lock
4. show master status; (keep output!)
5. mysqldump -u root -p -x --all-databases > dump.sql  (-x means --lock-tables)
6. copy dump.sql to DEST-mysqld
7. UNLOCK TABLES;
8. @DEST-mysqld `mysql -u<user> -p < dump.sql`
9. @DEST-mysqld change rep-setting
10. mysql > START SLAVE;

## detail ops
###1. `server-id`
  set diff value eath other.
###2. create user for rep
  
```sql:
grant replication slave on *.* to repl@`%` identified by 'passwd';
```

###3. table read lock SRC-mysqld
  
```sql:
FLUSH TABLES WITH READ LOCK;
```

### 4. show master status;
  
```sql:
show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysqld-bin.000015 | 3856     |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
```

### 5. `mysqldump -u root -p -x --all-databases  --triggers --routines --events  > dump.sql`
### 6. `UNLOCK TABLES;`
### 7. copy dump.sql to DEST-mysqld
### 8. `mysql -u root -p < dump.sql` @ DEST-mysqld -->　recomend! `flush privileges`
### 9. set rep params @ DEST-mysqld
```sql:
change master to master_host='<host>', \
master_user='repl', \
master_password='password', \
MASTER_LOG_FILE='mysqld-bin.000015', \ #<- File Excecuted_Gtid_set
master_log_pos=3856; #<- Position
```
### 10. `START SLAVE` @ DEST-mysqld
### 11. confirm `SHOW SLAVE STATUS\G`

# Replication (GTID)
### 1. `my.cnf` 
```
server-id = 1
log-bin
gtid-mode = on
enforce-gtid-consistency = on
log-slave-updates
relay_log_info_repository = TABLE # recomend!
relay_log_recovery = ON # recomend!
master_info_repository = TABLE # recomend
report_host=[this servers ip or hostname] # recomend!
```
### 2. create user for rep
  
```sql:
grant replication slave on *.* to repl@`%` identified by 'passwd';
```
### 3. mysqldump
  
```bash:
mysqldump -u root -p --all-databases --single-transaction --master-data=2  --triggers --routines --events > dump.sql
```
### 4. copy dump.sql to DEST-mysqld
### 5. clean all DEST-mysqld datas
```bash:
rm -rf /var/lib/<mysql_dir>/*
mysql_install_db --datadir=/var/lib/<mysql_dir> --user=mysql
```
### 6. `mysql -u root -p < dump.sql` @ DEST-mysqld -->　recomend!  do `flush privileges`
### 7. set rep params @ DEST-mysqld
```sql:
change master to master_host='<host>', \
master_user='repl', \
master_password='passwd', \
master_port=<port>, \
master_auto_position =1;
```
### 8. `START SLAVE` @ DEST-mysqld
```sql:
start slave;
```
Other way...

```sql:
start slave user='repl' password='passwd';
```

If you set `master_info_repository = TABLE` via my.cnf, user/password settings written at  mysql.master_info table. And `mysqlfailover/mysqlrpladmin` require it!
   
### 9. confirm `SHOW SLAVE STATUS\G`

## gtid info
http://nippondanji.blogspot.jp/2014/12/mysqlgtid.html
http://d.hatena.ne.jp/hiroi10/20130308/1362765495
http://yoku0825.blogspot.jp/2013/07/mysql-56change-master-to.html

## learn about GTID

```
show slave status \G

                  Master_UUID: xxxx-yyyy-zzzz
           Retrieved_Gtid_Set: xxxx-yyyy-zzzz:19308329-26539281
            Executed_Gtid_Set: xxxx-yyyy-zzzz:1-26539281
```
xxxx-yyyy-zzzz: UUID
n-n': GNO

`Executed_Gtid_Set` = Already executed GTID GNO
`Retrived_Gtid_Set` = Fetched (Received) GTID GNO

Anytime Retrived_Gtid_Set > Executed_Gtid_Set

### Purged GTID

```
mysql> show global variables like '%gtid%';
+---------------------------------+-------------------------------------------------+
| Variable_name                   | Value                                           |
+---------------------------------+-------------------------------------------------+
| binlog_gtid_simple_recovery     | OFF                                             |
| enforce_gtid_consistency        | ON                                              |
| gtid_executed                   | xxxx-yyyy-zzzz:1-26539281                       |
| gtid_mode                       | ON                                              |
| gtid_owned                      |                                                 |
| gtid_purged                     | xxxx-yyyy-zzzz:1-18549601                       |
| simplified_binlog_gtid_recovery | OFF                                             |
+---------------------------------+-------------------------------------------------+
```
Purged 


# Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

Run `rm /var/lib/mysql/auto.cnf` @ slave-side.

# slave完全に辞める
スレーブ設定をクリアする手順が5.5以降で異なります。
`RESET SLAVE ALL;`を覚えておきましょう！  
# replication paformance
Async-rep drop 20% QPS from non slave (of cource bin-log on).
2 Async-rep drop 10-15% QPS from 1 Async-rep.
Semi-Sync-rep drop 20% QPS from Async-rep.

http://yoku0825.blogspot.jp/2012/05/blog-post_28.html

# mysqldump
## options
ストアドプロシジャーも対象とする -R (--routines)
Eventスケジューラも対象とする -E (--events)
トリガーも対象とする --triggers
## Anti read-lock

`--single-transaction`

```bash:
  mysqldump -u root -p --all-databases --single-transaction --master-data=2  --triggers --routines --events > dump.sql
```

Only innodb

# xtraBackup
## install
amazon, redhat = eqpl ready!

```
yum install xtrabackup
yum install pigz
```

## Full backup 

```bash:
mkdir /tmp/xtrabackup
cd /tmp
innobackupex --user=<user> --password=<password> --stream=xbstream --parallel=4  /tmp/xtrabackup/ | pigz > xtrabackup_yyyymmdd.gz
```

## restore

```bash:
cd /tmp/xtrabackup
unpigz -c ../xtrabkup_yyyymmdd.gz | xbstream -x -v
cd /tmp
innobackupex --user=<user> --password=<password> --apply-log xtrabackup/
#TODO: backup your old DB
innobackupex --copy-back xtrabackup/

```


# event scheduler

```:my.cnf
[mysqld]
event-scheduler=ON
```

```sql:
mysql> CREATE EVENT flush_query_cache
    -> ON SCHEDULE EVERY 1 DAY STARTS '2014-05-12 03:00:00'
    -> ENABLE
    -> DO FLUSH QUERY CACHE;
Query OK, 0 rows affected (0.12 sec)
```

```sql:confirm
mysql> SHOW EVENTS\G
## no table lock
```

# maint
## decode row formatted bin-log
`mysqlbinlog --base64-output=decode-rows mysqld-bin.000999 -v > a.sql`

## set read_only
`SET GLOBAL READ_ONLY=1;`

## process list
show processlist;

## innotop
https://github.com/innotop/innotop