MySQL basic - Knowledgebase of giip

Management

Connect from shell
- mysql -u root -h giipdb.mysql.com -p -P 3306
  - -u : user
  - -p : ask password
  - -h : host
  - -P : Port
show running SQL
- show processlist;
- show full processlist \G
Cursor
- https://codeday.me/jp/qa/20190213/271235.html - jp
Binlog(Binary Log)
- https://www.ritolab.com/entry/98
  - managing binary log
- https://www.percona.com/blog/2015/07/30/why-base64-outputdecode-rows-does-not-print-row-events-in-mysql-binary-logs/
  - Decode binary log
Start option
- Official : https://dev.mysql.com/doc/refman/5.6/en/mysql-command-options.html
stop
```
mysqladmin -u root -p shutdown
```
Run from sql file
- Official : https://dev.mysql.com/doc/refman/8.0/en/mysql-batch-commands.html

Check tuning point using mysqltuner
- You can tune easy by suggestion on mysqltuner

$ wget https://github.com/major/MySQLTuner-perl/zipball/master
$ unzip master
$ cd master
$ ./mysqltuner.pl

-- Configuration
SHOW VARIABLES LIKE '%query_cache%';

-- Usages
SHOW STATUS LIKE 'Qcache%';

Characterset vs collation
- collation_connection, collation_server
- character_server, character_database
- https://atsuizo.hatenadiary.jp/entry/2016/08/22/090000

extend timeout from MySQL Workbench client
- http://oboe2uran.hatenablog.com/entry/2016/03/21/102931
InnoDB: Doing recovery: scanned up to log sequence number 2938498040110
- https://yakst.com/ja/posts/53
If you forgot root password...
- https://liginc.co.jp/web/programming/mysql/87393
- mysqld_safe --skip-grant-tables & as Run with no pass
Can not effect grant command
- resolve to recreate user using grant command
  - https://talklowy-jp.blogspot.com/2020/06/mysql-grant.html

UPDATE users a
LEFT JOIN userlog b
ON a.userid = b.userid
SET a.point = a.point + 100
WHERE b.loginstatus = 1;

insert select on duplicate key update
- it have to make primary key. Compare with TABLEA and TABLEB by primary key.

insert into TABLEA (FIELDA01, FIELDA02)
select FIELDB01, FIELDB02
from TABLEB
where FIELDB03 = 'formerge'
on duplicate key update FIELDA02 = FIELDB04