MySQL

• 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

Tuning

Innodb

• 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

• https://corporate.inter-edu.com/developper/1373

• innodb storage engine

  • https://enterprisezine.jp/dbonline/detail/3829

• SQL relative innodb buffer pool size

  • https://dba.stackexchange.com/questions/27328/how-large-should-be-mysql-innodb-buffer-pool-size

• Query Cache Tuning

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

-- Usages
SHOW STATUS LIKE 'Qcache%';

• https://qiita.com/tukiyo3/items/797f9916e6494ec33991

Information

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

Trouble shooting

• 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

Useful SQL

• Update join

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

Shell

• do not ask password when running cron job or shell batch
  • https://qiita.com/goto_yoshiki/items/4f75568799c044df811d