[B! mysql] shayashã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯

shayash id:shayash

mysqlã«é–¢ã™ã‚‹shayashã®ãƒ–ãƒƒã‚¯ãƒžãƒ¼ã‚¯ (77)

${{author_name}}$

{{{comment_expanded}}}

{{label}}

{{#is_bookmark}}ãƒªã‚¹ãƒˆ{{/is_bookmark}}{{^is_bookmark}}ãƒªãƒ³ã‚¯{{/is_bookmark}}

${{author_name}}$
{{author_name}}{{created}}
{{ #comment }}{{ comment }}{{ /comment }}
- {{ label }}

${{author_name}}$

{{{comment_expanded}}}

{{label}}

{{#is_bookmark}}ãƒªã‚¹ãƒˆ{{/is_bookmark}}{{^is_bookmark}}ãƒªãƒ³ã‚¯{{/is_bookmark}}

MySQL5.7ã‚’Homebrewã§macOSã«ã‚¤ãƒ³ã‚¹ãƒˆãƒ¼ãƒ«ã™ã‚‹æ‰‹é † |
æ¦‚è¦ æœ¬é …ã§ã¯ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã‚µãƒ¼ãƒãƒ¼ã€ŒMySQL Server 5.7ã€ã‚’ã€ãƒ‘ãƒƒã‚±ãƒ¼ã‚¸ç®¡ç†ãƒ„ãƒ¼ãƒ«ã€ŒHomebrewã€ã‚’åˆ©ç”¨ã—ã¦ macOS ã«ã‚¤ãƒ³ã‚¹ãƒˆãƒ¼ãƒ«ã™ã‚‹æ‰‹é †ã«ã¤ã„ã¦è§£èª¬ã—ã¾ã™ã€‚ MySQL5.7ã‚’æ¤œç´¢ã—ã‚ˆã† ã¾ãšã¯ Homebrew ã§ MySQL 5.7 ãŒæä¾›ã•ã‚Œã¦ã„ã‚‹ã“ã¨ã‚’ç¢ºèªã™ã‚‹ãŸã‚ã«ã€brew search ã‚³ãƒžãƒ³ãƒ‰ã‚’åˆ©ç”¨ã—ã¦æ¤œç´¢ã—ã¦ã¿ã¾ã™ã€‚ æ¬¡ã®ã‚ˆã†ã« mysql@5.7 ã‚’æŒ‡å®šã—ã¦æ¤œç´¢ã—ã¦ã¿ã¾ã—ã‚‡ã†ã€‚ æ¬¡ã®ã‚ˆã†ã« Formulae ã« mysql@5.7 ã¨è¡¨ç¤ºã•ã‚Œã‚Œã°OKã§ã™ã€‚ $ brew search mysql@5.7 ==> Formulae mysql@5.7 MySQL5.7ã‚’ã‚¤ãƒ³ã‚¹ãƒˆãƒ¼ãƒ«ã—ã‚ˆã† Homebrew ã§ MySQL5.7 ãŒæä¾›ã•ã‚Œã¦ã„ã‚‹ã“ã¨ãŒç¢ºèªã§ããŸã‚‰ã€ã•ã£ããã‚¤ãƒ³ã‚¹ãƒˆãƒ¼ãƒ«ã—ã¦ã¿ã¾ã—ã‚‡ã†ã€‚ æ¬¡ã®ã‚ˆã†ã« brew install ã‚³ãƒžãƒ³ãƒ‰ã«
shayash 2020/03/10
homebrew

mysql
ãƒªãƒ³ã‚¯
ã€Python3ã€‘MySQL æ“ä½œã‚’ã²ã¨é€šã‚Šãƒžã‚¹ã‚¿ãƒ¼ï¼å°Žå…¥æ–¹æ³•ã¨CRUDã‚µãƒ³ãƒ—ãƒ«ã‚³ãƒ¼ãƒ‰é›†
ã“ã®è¨˜äº‹ã§ã¯ Python3 ã§ MySQL ã‚’æ“ä½œã™ã‚‹æ–¹æ³•ã‚’ã²ã¨é€šã‚Šã¾ã¨ã‚ã¾ã—ãŸã€‚MySQL ãƒ‰ãƒ©ã‚¤ãƒã®å°Žå…¥æ–¹æ³•ã€SQLæ–‡ã«ã‚ˆã‚‹CRUDæ“ä½œã‚„ã‚¨ãƒ©ãƒ¼å‡¦ç†ã‚’ã‚µãƒ³ãƒ—ãƒ«ä»˜ãã§ç´¹ä»‹ã—ã¾ã™ã€‚ MySQL ã®æ“ä½œã¯ã€ŒSQLã«ã‚ˆã‚‹ç›´ ...
shayash 2020/03/10
python

mysql
ãƒªãƒ³ã‚¯
MySQLã®æ–‡å—ã‚³ãƒ¼ãƒ‰ã‚’utf8mb4ã«å¤‰æ›´ - Qiita
Deleted articles cannot be recovered. Draft of this article would be also deleted. Are you sure you want to delete this article?
shayash 2020/02/27
mysql
ãƒªãƒ³ã‚¯
ãƒ†ãƒ¼ãƒ–ãƒ«ãŒå£Šã‚Œã¦ã„ã‚‹ã‹ã¨æ€ã£ãŸã®ã§ã€mysqlcheckã§ä¿®å¾©ã—ãŸã¨ãã®ãƒ¡ãƒ¢
mysqlcheckã¨ã¯ mysqlcheckã¯ã€MySQLã®ãƒ†ãƒ¼ãƒ–ãƒ«ãƒ¡ãƒ³ãƒ†ãƒŠãƒ³ã‚¹ã€ä¿®å¾©ã‚’è¡Œãªã†ã“ã¨ãŒã§ãã‚‹ã€‚ å†…éƒ¨çš„ã«ã¯ã€CHECK, ANALYZE, REPAIR, OPTIMIZEã®ã‚³ãƒžãƒ³ãƒ‰ã‚’åˆ©ç”¨ã—ã¦ä½œæ¥ã‚’è¡Œãªã†ã€‚ ã¾ãŸã€MySQLã‚’ç¨¼åƒã—ã¦ã„ã‚‹ã¨ãã«ã€å®Ÿè¡Œã™ã‚‹ã“ã¨ãŒã§ãã‚‹ã€‚ 1. checkï¼ˆãƒ†ãƒ¼ãƒ–ãƒ«ã®ã‚¨ãƒ©ãƒ¼ãƒã‚§ãƒƒã‚¯ï¼‰ ã‚ªãƒ—ã‚·ãƒ§ãƒ³
shayash 2019/07/24
mysql

mysqlcheck
ãƒªãƒ³ã‚¯
MySQL ã§ã®ãƒ†ãƒ¼ãƒ–ãƒ«ãƒ¡ãƒ³ãƒ†ãƒŠãƒ³ã‚¹ã¯ã€Œmysqlcheckã€ - ç¨šæ‹™ãªå‚™å¿˜éŒ²ã§ã™ãŒ
ä»Šæ—¥MySQLã®ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã‚’åˆ©ç”¨ã—ã¦ã„ã‚‹ã‚¢ãƒ—ãƒªã‚±ãƒ¼ã‚·ãƒ§ãƒ³ã®å‹•ããŒ å°‘ã—é…ã„ã‚ˆã†ãªæ°—ãŒã—ã¦ã€ã€Œãã†ã„ãˆã°PostgreSQLã®vacuumã¨åŒæ§˜ã®ã‚‚ã®ã¯ãªã„ã‹ã€ã¨ ãµã¨æ€ã£ã¦èª¿ã¹ã¾ã—ãŸã€‚ MySQLã§ã¯ mysqlcheck -u ãƒ¦ãƒ¼ã‚¶ãƒ¼å -p(ãƒ‘ã‚¹ãƒ¯ãƒ¼ãƒ‰æŒ‡å®šã®æ„) Â {optionã‚’æŒ‡å®š} ã§è¡Œã†ã¨ã®ã“ã¨ã€‚ ã²ã¨ã¾ãšå…¨ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã«å¯¾ã—ã¦å®Ÿè¡Œ -Aã€€å…¨ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ -oã€€optimize ã‚’å®Ÿè¡Œ mysqlcheck -A -o -u root -p ã“ã‚Œã§OKã€‚ å‡¦ç†ãƒã‚°ã‚’è¦‹ã‚‹ã¨ã€ŒTable does not support optimize, doing recreate + analyze insteadã€ ã¨ã„ã†ã®ãŒæ•£è¦‹ã™ã‚‹ã€‚ ã“ã‚Œã¯INNODBã¯ãƒ†ãƒ¼ãƒ–ãƒ«ã‚’ä½œã‚Šç›´ã—ã¦ã„ã‚‹ã¨ã®æ„å‘³ã€‚ãã†ãªã®ã‹ã€‚ ä»¥ä¸‹ä¸»ãªã‚ªãƒ—ã‚·ãƒ§ãƒ³æŒ‡å®š -Aï¼šã€€å…¨ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹æŒ‡å®š -aï¼šã€€ãƒ†ãƒ¼ãƒ–ãƒ«ã‚’åˆ†æž -cï¼š
shayash 2019/07/24
mysql

mysqlcheck
ãƒªãƒ³ã‚¯
MySQLã§é«˜é€Ÿã«ãƒ©ãƒ³ã‚ãƒ³ã‚°ã‚’æ±‚ã‚ã‚‹ - å‚™å¿˜éŒ²
ä¾‹ãˆã°ä¸‹è¨˜ã®ã‚ˆã†ãªãƒ†ãƒ¼ãƒ–ãƒ«ãŒã‚ã£ãŸã¨ã—ã¦ã€ãƒã‚¤ã‚¹ã‚³ã‚¢ã®ä¸Šä½ãƒ©ãƒ³ã‚ãƒ³ã‚°ã‚„æŒ‡å®šidã®ãƒ©ãƒ³ã‚ãƒ³ã‚°ã‚’å–å¾—ã—ãŸã„å ´åˆãŒã‚ã‚‹ã€‚ CREATE TABLE `test` ( `id` int(11) NOT NULL AUTO_INCREMENT, `highscore` int(11), PRIMARY KEY (`id`), KEY `highscore` (`highscore`) ); ã„ã¤ã‚‚ã¯ãƒãƒƒãƒå‡¦ç†ã—ã¦ãƒ©ãƒ³ã‚ãƒ³ã‚°ç”¨ã®ãƒ†ãƒ¼ãƒ–ãƒ«ã‚’ç”¨æ„ã—ã¦ã„ãŸãŒã€ãµã¨MySQLã ã‘ã§é«˜é€Ÿã«ãƒ©ãƒ³ã‚ãƒ³ã‚°ã‚’æ±‚ã‚ã‚‰ã‚Œãªã„ã‚‚ã®ã‹ã¨æ€ã£ãŸã®ã§è‰²ã€…æŽ¢ã—ã¦ã¿ãŸã€‚ ã¾ãšã¯ã€ã“ã¡ã‚‰ã®è¨˜äº‹ã®æ—©ã„ç‰ˆã¨æ›¸ã‹ã‚Œã¦ã„ã‚‹æ–¹æ³•ãŒé«˜é€Ÿã§è‰¯ã„æ„Ÿã˜ã ã£ãŸã€‚ MySQL - SQL åˆè¨ˆå€¤ã‹ã‚‰ãƒ©ãƒ³ã‚ãƒ³ã‚°ã‚’å–å¾—ã™ã‚‹ä¾‹ - Qiita ã—ã‹ã—åŒç‚¹ï¼ˆåŒé †ä½ï¼‰ã¯è€ƒæ…®ã•ã‚Œã¦ã„ãªã‹ã£ãŸã®ã§ã€ä»Šåº¦ã¯ã“ã¡ã‚‰ã‚’å‚è€ƒã«ã—ãŸã‚¯ã‚¨ãƒªã‚’ä½¿ã£ã¦ã¿ãŸã€‚ mysqlã ã‘ã§ãƒ©ãƒ³ã‚ãƒ³ã‚°ã—
shayash 2019/06/17
mysql
ãƒªãƒ³ã‚¯
MySQL ã®å£Šã‚ŒãŸãƒ†ãƒ¼ãƒ–ãƒ«ã‚’ä¿®å¾© | Mac person
ã‚µãƒ¼ãƒãƒ¼ã®ã‚¨ãƒ©ãƒ¼ã‚’æ¤œçŸ¥ã—ãŸã®ã§ç¢ºèªã—ã¦ã¿ã‚‹ã¨ã€mysql ã®ãƒã‚°ãƒ•ã‚¡ã‚¤ãƒ«ã«ä¸‹è¨˜ã®ã‚ˆã†ãªã‚¨ãƒ©ãƒ¼ãŒå‡ºã¦ã„ã¾ã—ãŸã€‚ ã©ã†ã‚„ã‚‰ WordPress ã§ä½¿ç”¨ã—ã¦ã„ã‚‹ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã® wp_options ã¨ã„ã†ãƒ†ãƒ¼ãƒ–ãƒ«ã®èªã¿è¾¼ã¿ã§ã‚¨ãƒ©ãƒ¼ãŒç™ºç”Ÿã—ã¦ã„ã‚‹ã¿ãŸã„ã§ã™ã€‚ 150102 23:20:17 [ERROR] Got error 127 when reading table './wordpress/wp_options' èª¿ã¹ã¦ã¿ã‚‹ã¨ã€ãƒ†ãƒ¼ãƒ–ãƒ«ãŒç ´æã—ãŸå¯èƒ½æ€§ãŒé«˜ã„ã‚‰ã—ã„â€¦ ã¨ã„ã†ã“ã¨ã§ã€ãƒ†ãƒ¼ãƒ–ãƒ«ã‚’ä¿®å¾©ã™ã‚‹æ‰‹é †ã‚’ç´¹ä»‹ã—ã¾ã™ã€‚ â€»CUI ä¸Šã§æ“ä½œã—ã¦ã„ã¾ã™ãŒã€SQL æ–‡ã•ãˆå®Ÿè¡Œã§ãã‚Œã°ã‚ˆã„ã®ã§ã€phpmyadmin ç‰ã§ã‚‚ä¿®å¾©å¯èƒ½ã§ã™ã€‚ Mysql ã®ç ´æã—ãŸãƒ†ãƒ¼ãƒ–ãƒ«ã‚’ä¿®å¾© mysql ã«ãƒã‚°ã‚¤ãƒ³ #mysql ã«ãƒã‚°ã‚¤ãƒ³ sudo mysql -u root -p ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã®é¸æŠž mysql>u
shayash 2019/05/13
mysql
ãƒªãƒ³ã‚¯
latin1ã®DBã‹ã‚‰dumpã—ãŸãƒ‡ãƒ¼ã‚¿ã‚’utf8ã®DBã«ç§»è¡Œã™ã‚‹ã‚ˆãƒ¼ - ã¦ãã¨ã†
ç§»è¡Œå…ƒ DB:hoge mysql> show variables like 'collation%'; +----------------------+-------------------+ | Variable_name | Value | +----------------------+-------------------+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +----------------------+-------------------+ mysql> show variables like 'char%'; +--------------------
shayash 2019/03/22
mysql
ãƒªãƒ³ã‚¯
MySQLæ¨©é™ä¸€è¦§ã‚’ãã‚Œã„ã«ä½œã‚‹æ–¹æ³•ã¨ã€rootãƒ¦ãƒ¼ã‚¶ãƒ¼ä»¥å¤–ã§æ£šå¸ã—ã™ã‚‹æ–¹æ³• - Qiita
information_schemaã‚’åˆ©ç”¨ã—ãŸæ¨©é™ãƒªã‚¹ãƒˆä½œæˆ MySQLã§ã€å˜åœ¨ã™ã‚‹ãƒ¦ãƒ¼ã‚¶ãƒ¼ã¨æ¨©é™ã®ä¸€è¦§ãŒæ¬²ã—ã„ã¨ãã€ rootãƒ¦ãƒ¼ã‚¶ãƒ¼ã§ä»¥ä¸‹ã®ã‚³ãƒžãƒ³ãƒ‰ã‚’å®Ÿè¡Œã™ã‚‹ã¨ç°¡å˜ã«ç¢ºèªã§ãã¾ã™ã€‚ ã‚·ãƒ³ãƒ—ãƒ«ãªã‚¯ã‚¨ãƒª SELECT * FROM information_schema.user_privileges; -- ã‚°ãƒãƒ¼ãƒãƒ«ãƒ¬ãƒ™ãƒ«æ¨©é™ã®ãƒªã‚¹ãƒˆ SELECT * FROM information_schema.schema_privileges; -- ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ãƒ¬ãƒ™ãƒ«æ¨©é™ã®ãƒªã‚¹ãƒˆ SELECT * FROM information_schema.table_privileges; -- ãƒ†ãƒ¼ãƒ–ãƒ«ãƒ¬ãƒ™ãƒ«æ¨©é™ã®ãƒªã‚¹ãƒˆ SELECT * FROM information_schema.column_privileges; -- ã‚«ãƒ©ãƒ ãƒ¬ãƒ™ãƒ«æ¨©é™ã®ãƒªã‚¹ãƒˆ -- ã‚°ãƒãƒ¼ãƒãƒ«ãƒ¬ãƒ™ãƒ«æ¨©é™ã®ãƒªã‚¹ãƒˆ SELEC
shayash 2019/03/04
mysql
ãƒªãƒ³ã‚¯
LOCATEï¼šç‰¹å®šã®æ–‡å—åˆ—ãŒæœ€åˆã«å‡ºç¾ã™ã‚‹ä½ç½®ã‚’èª¿ã¹ã‚‹
LOCATE( æ¤œç´¢æ–‡å—åˆ—, æ¤œç´¢å¯¾è±¡æ–‡å—åˆ— ) LOCATE( æ¤œç´¢æ–‡å—åˆ—, æ¤œç´¢å¯¾è±¡æ–‡å—åˆ—, æ•°å€¤ ) LOCATEé–¢æ•°ã‚’ä½¿ç”¨ã™ã‚‹ã“ã¨ã§ç‰¹å®šã®æ–‡å—åˆ—ãŒæœ€åˆã«å‡ºç¾ã™ã‚‹ä½ç½®ã‚’èª¿ã¹ã‚‹ã“ã¨ãŒã§ãã¾ã™ã€‚ ç¬¬3å¼•æ•°ã«æ•°å€¤ã‚’æŒ‡å®šã™ã‚‹ã“ã¨ã§ã€ä½•æ–‡å—ç›®ä»¥é™ã‚’æ¤œç´¢å¯¾è±¡ã¨ã™ã‚‹ã‹ã‚’æŒ‡å®šã§ãã¾ã™ã€‚ æ¤œç´¢æ–‡å—åˆ—ãŒè¦‹ã¤ã‹ã£ãŸå ´åˆã¯ãã®ä½ç½®ã‚’ã€è¦‹ã¤ã‹ã‚‰ãªã‹ã£ãŸå ´åˆã¯0ã‚’è¿”ã—ã¾ã™ã€‚ mysql> SELECT LOCATE( 'SQ', 'MySQL' ); +-------------------------+ | LOCATE( 'SQ', 'MySQL' ) | +-------------------------+ | 3 | +-------------------------+ 1 row in set (0.00 sec) mysql> SELECT LOCATE( 'ã‚ã«', 'ã«ã‚ã«ã‚ã«ã‚ã«ã‚ã¨ã‚ŠãŒã„
shayash 2019/02/15
mysql
ãƒªãƒ³ã‚¯
MySQLã®innodb_log_file_sizeã‚’å¤‰æ›´ã—ãŸã‚‰MySQLãŒèµ·å‹•ã—ãªããªã£ãŸ - /dev/null
MySQLã®ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°ãªãŠè©±ã€‚ /etc/my.cnfã« innodb_log_file_size= ã¨ã„ã†ãƒ‘ãƒ©ãƒ¡ãƒ¼ã‚¿ãŒã‚ã‚Šã¾ã™ã€‚ ãƒã‚°ã®ã‚µã‚¤ã‚ºãªã‚“ã‚„ã‚ï¼Ÿåˆ¥ã«å¤§ãããªãã¦ã‚‚ã„ã„ã‚“ã˜ã‚ƒãï¼Ÿï¼Ÿï¼Ÿï¼Ÿã¨æ€ã„ã¾ã—ãŸãŒã€InnoDBã®ãƒã‚°ã¯ãŸã ã®ãƒã‚°ã«ã‚ã‚‰ãšã€‚ InnoDBã®ãƒã‚°ã¯æ‰€è¬‚WALãƒã‚°ã§ã™ã€‚ ãƒ¦ãƒ¼ã‚¶ã®insert æ“ä½œ â†“ Innodb_logã«ä¸€æ—¦æ›¸ãè¾¼ã¿ â†“ ã‚³ãƒŸãƒƒãƒˆ â†“ è½ã¡ç€ã„ãŸã‚‰DBã«æ›¸ãè¾¼ã¿ ã¨ã„ã†æµã‚Œã€‚ ãªã®ã§InnoDBãƒã‚°ã‚’å®‰æ˜“ã«æ¶ˆã™ã¨ã€æ›¸ãè¾¼ã‚€äºˆå®šã®ãƒ‡ãƒ¼ã‚¿ãŒãªããªã‚Šã¾ã™ã—ã€ã“ã®ã‚µã‚¤ã‚ºãŒå°ã•ã„ã¨ä¸€æ™‚ã‚¹ãƒšãƒ¼ã‚¹ãŒå°ã•ã„ã®ã¨åŒã˜ã§ãƒ‘ãƒ•ã‚©ãƒ¼ãƒžãƒ³ã‚¹ãŒæ‚ªããªã‚Šã¾ã™ã€‚ ã¨ã„ã†ã‚ã‘ã§å¤§ãã‚ã«ã«ã—ã‚ˆã†ã€ã¨ã„ã†ã®ãŒãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°ã€‚ï¼ˆå¤§ãããªã‚Šã™ãŽã¦ã‚‚ãƒªã‚«ãƒãƒªã«æ™‚é–“ã‹ã‹ã‚‹ã‚“ã§ã‚¢ãƒ¬ãªã®ã§å…¼ãåˆã„ï¼‰ ãƒ‡ãƒ•ã‚©ãƒ«ãƒˆã¯5MBã½ã£ã¡ãªã®ã§128MBã¨ã‹ã«ã—ã‚ˆã†ã¨è€ƒãˆã¾ã™ã€‚ ãã“ã§ innodb_log_fi
shayash 2018/11/13
mysql

ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°
ãƒªãƒ³ã‚¯
MySQL ã‚¯ã‚¨ãƒªãƒ¼ã‚ãƒ£ãƒƒã‚·ãƒ¥ ã€ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°æ–¹æ³•ã¨ã‹ã‚‚ã€‘ - Qiita
å‚ç…§ãƒ¡ã‚¤ãƒ³ã®WordPressã•ã‚“ã§RDS(for MySQL)ã®ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°ã‚’è¡Œã£ãŸã€‚ ä»¥å‰ã«å‚ç…§ãƒ¡ã‚¤ãƒ³ã®Wordpressã®å ´åˆã¯MySQLã®ã‚¯ã‚¨ãƒªã‚ãƒ£ãƒƒã‚·ãƒ¥ã‚’æœ‰åŠ¹ã«ã™ã‚‹ã¨å¹¸ã›ã«ãªã‚‹ã‹ã‚‚ã—ã‚Œãªã„ ã§ã‚¯ã‚¨ãƒªãƒ¼ã‚ãƒ£ãƒƒã‚·ãƒ¥ã‚’å°Žå…¥ã—ãŸã®ã§ã™ãŒã€ä»Šå›žã¯ã©ã®ç¨‹åº¦ã®å‰²åˆã§ã‚¯ã‚¨ãƒªãƒ¼ã‚ãƒ£ãƒƒã‚·ãƒ¥ã‚’å…¥ã‚Œã‚‹ã¨ ã‚ˆã„ãƒ‘ãƒ•ã‚©ãƒ¼ãƒžãƒ³ã‚¹ã«ãªã‚‹ã®ã‹ï¼Ÿã‚’èª¿ã¹ã¦ã¿ã¾ã—ãŸã€‚ ç’°å¢ƒ wordpress 4.5.3 RDS for MySQL 5.6.29 å‚è€ƒæƒ…å ± MySQL ã‚¯ã‚¨ãƒªãƒ¼ã‚ãƒ£ãƒƒã‚·ãƒ¥ ã‚µãƒ¼ãƒãƒ¼ã‚¹ãƒ†ãƒ¼ã‚¿ã‚¹å¤‰æ•°#statvar_Qcache_free_blocks MySQL ã‚¯ã‚¨ãƒªãƒ¼ã®ã‚ãƒ£ãƒƒã‚·ãƒ¥ãƒ’ãƒƒãƒˆçŽ‡ã‚’SQLã§æ±‚ã‚ã‚‹æ–¹æ³• ã‚¯ã‚¨ãƒªã‚ãƒ£ãƒƒã‚·ãƒ¥ã¨ã¯ãªã‚“ãžã‚„??? MySQLãƒªãƒ•ã‚¡ãƒ¬ãƒ³ã‚¹ã‚ˆã‚Š ã‚¯ã‚¨ãƒªãƒ¼ã‚ãƒ£ãƒƒã‚·ãƒ¥ã«ã¯ã€ã‚¯ãƒ©ã‚¤ã‚¢ãƒ³ãƒˆã«é€ä¿¡ã•ã‚ŒãŸå¯¾å¿œã™ã‚‹çµæžœã¨ã¨ã‚‚ã«ã€SELECT ã‚¹ãƒ†ãƒ¼ãƒˆãƒ¡ãƒ³ãƒˆã®ãƒ†ã‚ã‚¹ãƒˆãŒæ ¼ç´ã•ã‚Œã¾ã™ã€‚ã‚ã¨ã§åŒ
shayash 2018/11/13
mysql

ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°
ãƒªãƒ³ã‚¯
MySQLãƒ‘ãƒ•ã‚©ãƒ¼ãƒžãƒ³ã‚¹ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚° -my.cnfã®è¦‹ç›´ã—- - Qiita
â€»RDSã¯ä½¿ã£ã¦ã„ã¾ã›ã‚“ã€‚ è² è·ã‚’è¦‹ã¦ã¿ã‚‹ DBã‚µãƒ¼ãƒãƒ¼ã®è² è·çŠ¶æ³ã‚’è¦‹ã¦ã¿ã¾ã™ã€‚ å½“æ™‚ã®ç›£è¦–ãƒ„ãƒ¼ãƒ«ã®ç”»åƒãŒãªã„ã®ã§ã™ãŒã€ä»¥ä¸‹ã®çŠ¶æ³ã§ã—ãŸã€‚ LA(Load Average)ãŒçªãæŠœã‘ã¦ã„ã‚‹ ãƒªã‚¯ã‚¨ã‚¹ãƒˆæ•°ã¯ã€Œå¸¸è˜çš„ã«è€ƒãˆã¦ã€ãã‚Œã»ã©ã§ã‚‚ãªã„ ãƒ¡ãƒ¢ãƒªã®ä½¿ç”¨é‡ã«ã‚ã¾ã‚Šå¤‰åŒ–ãŒãªã„ swapã¯ã—ã¦ã„ãªã„ ã‚¹ãƒˆãƒ¬ãƒ¼ã‚¸å®¹é‡ã‚’çµæ§‹é£Ÿã£ã¦ã„ã‚‹ WEBã‚µãƒ¼ãƒãƒ¼ã‹ã‚‰è¦‹ã‚Œã°ã€å‡¦ç†å¾…ã¡ã®ã¾ã¾ãƒ—ãƒã‚»ã‚¹ãŒå‡¦ç†ã•ã‚Œã¦ã„ãªã„å…¸åž‹çš„ãªãƒ‘ã‚¿ãƒ¼ãƒ³ã ã£ãŸã¨æ€ã„ã¾ã™ã€‚ DBã‚µãƒ¼ãƒãƒ¼ã¨ã—ã¦ã¯ã€LAã«å¯¾ã—ã€ãƒ¡ãƒ¢ãƒªã®ä½¿ç”¨é‡ãŒã‚ã£ã¦ã„ãªã„ã‚ˆã†ã«æ€ã‚ã‚Œã¾ã—ãŸã€‚ ä»®èª¬ ä¸Šè¨˜ã®çŠ¶æ…‹ã‹ã‚‰ã€ä»®èª¬ã‚’ç«‹ã¦ã¾ã™ã€‚ ã‚¹ãƒãƒ¼ã‚¯ã‚¨ãƒª ãŒé »ç™ºã—ã¦ã„ã‚‹ã®ã§ã¯ãªã„ã‹ ãƒ¡ãƒ¢ãƒª ãŒæ£ã—ãå‰²ã‚Šå½“ã¦ã‚‰ã‚Œã¦ã„ãªã„ã®ã§ã¯ãªã„ã‹ å„ç¨®ãƒã‚° ã®è¨å®šãŒé©åˆ‡ã§ã¯ãªã„ã®ã§ã¯ãªã„ã‹ ä»®èª¬ã‚’æ¤œè¨¼ã™ã‚‹ã“ã¨ã§ã€å¯¾ç–ã‚’ã—ã¦ã„ãã¾ã™ã€‚ è¨å®šã‚’è¦‹ç›´ã™ ä¸Šè¨˜ã®ä»®èª¬ã®è¨å®šã¯ã€MySQLã®è¨å®šãƒ•ã‚¡ã‚¤ãƒ«ã§ã‚ã‚‹ã€Œ
shayash 2018/11/12
mysql

ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°
ãƒªãƒ³ã‚¯
MariaDBã®VARCHAR(10)ã«ã¯ä½•æ–‡å—æ ¼ç´ã§ãã‚‹ã§ã—ã‚‡ã†ã‹ï¼Ÿ - ãƒãƒžãƒã‚°
ã“ã†èžã‹ã‚Œã‚Œã°ã€ŒåŠè§’10æ–‡å—ã€å…¨è§’5æ–‡å—ã€ã¨ç”ãˆã‚‹äººãŒå¤šã„ã®ã§ã¯ãªã„ã§ã—ã‚‡ã†ã‹ã€‚ ãŠãã‚‰ãVARCHAR(10)ã®10ã¯åŠè§’æ–‡å—æ•°ã¨èªè˜ã•ã‚Œã¦ã„ã‚‹ã¨æ€ã„ã¾ã™ã€‚ MariaDBã®VARCHARã«ã¤ã„ã¦èª¿ã¹ç›´ã—ã¦ã¿ã¾ã—ãŸã€‚(5.5.52-MariaDB) MariaDBã«ã¯ã‚¹ãƒˆãƒ¬ãƒ¼ã‚¸ã‚¨ãƒ³ã‚¸ãƒ³ãŒã„ãã¤ã‹ã‚ã‚‹ã®ã§ã€ãã®è¾ºã¯ã¡ã‚‡ã£ã¨ã”ã£ã¡ã‚ƒã«ãªã£ã¦ã‚‹ã¨æ€ã„ã¾ã™ã€‚InnoDBã‹MyISAMã‹ã§ã€å°‘ã—è©±ãŒé•ã£ã¦ãã‚‹ã¨ã¯æ€ã†ã‘ã©ã€ãã“ã¾ã§ã¯è¿½ãˆã¦ã¾ã›ã‚“ã€‚ VARCHAR(n)ã¯æ–‡å—æ•° VARCHAR(10)ã®ç®‡æ‰€ã«å…¨è§’æ–‡å—ã§10æ–‡å—å…¥ã‚Šã¾ã™ã€‚ æ˜”ã¯ãƒã‚¤ãƒˆæ•°ã ã£ãŸã‚ˆã†ãªæ°—ã‚‚ã™ã‚‹ã‘ã©ã€ä»Šã¯æ–‡å—æ•°ã‚’è¨å®šã—ã¾ã™ã€‚ VARCHAR å€¤ã¯å¿…è¦ãªæ–‡å—æ•°ã¨ã€é•·ã•ã‚’è¨˜éŒ²ã™ã‚‹ç‚ºã®1ãƒã‚¤ãƒˆ(255ã‚ˆã‚Šã‚‚é•·ã„ã‚«ãƒ©ãƒ ã¯2ãƒã‚¤ãƒˆ)ã ã‘ã‚’åˆ©ç”¨ã—ã¦æ ¼ç´ã§ãã¾ã™ã€‚ https://mariadb.com/kb/en/mariadb/var
shayash 2018/10/24
mysql

varchar
ãƒªãƒ³ã‚¯
MySQLã®ãƒªã‚¢ãƒ«ã‚¿ã‚¤ãƒ ãƒ¢ãƒ‹ã‚¿ãƒªãƒ³ã‚°ã«ã€Œinnotopã€
innotopã¨ã¯ ã€Œinnotopã€ã¯MySQLã®ã‚¯ã‚¨ãƒªãƒ¼å®Ÿè¡ŒçŠ¶æ³ã‚„ã‚¹ãƒ†ãƒ¼ã‚¿ã‚¹å¤‰æ•°ã®å¤‰åŒ–ãªã©ã‚’topã‚³ãƒžãƒ³ãƒ‰é¢¨ã«å‡ºåŠ›ã—ã¦ãã‚Œã‚‹ã€Perlã§æ›¸ã‹ã‚ŒãŸã‚¹ã‚¯ãƒªãƒ—ãƒˆã§ã™ã€‚ä¸èº«ã¯éžå¸¸ã«ã‚·ãƒ³ãƒ—ãƒ«ï¼ˆSHOW GLOBAL STATUSã‚„SHOW ENGINE INNODB STATUSãªã©ã‚’ãƒ‘ãƒ¼ã‚¹ã—ã¦è¡¨ç¤ºã™ã‚‹ã ã‘ï¼‰ã§ã™ãŒã€ topãƒ©ã‚¤ã‚¯ãªã‚¤ãƒ³ã‚¿ãƒ¼ãƒ•ã‚§ã‚¤ã‚¹ã¯ãƒªã‚¢ãƒ«ã‚¿ã‚¤ãƒ ãƒ¢ãƒ‹ã‚¿ãƒªãƒ³ã‚°ã¨ç›¸æ€§ãŒè‰¯ãã€éžå¸¸ã«ç›´è¦³çš„ã§ã‚ã‹ã‚Šã‚„ã™ã„æƒ…å ±ã‚’æä¾›ã—ã¦ãã‚Œã¾ã™ã€‚ ç†è€…ã¯ã“ã‚Œã‚’ã€Œç¨¼åƒä¸ã®ã‚µãƒ¼ãƒ“ã‚¹ã«ALTER TABLEï¼ˆã¾ãŸã¯pt-online-schema-changeï¼‰ã‚’ã‹ã‘ã‚‹éš›ã®è² è·ãƒ¢ãƒ‹ã‚¿ãƒ¼ã€ã‚„ã€ŒMySQLãŒé«˜è² è·çŠ¶æ…‹ã«ãªã£ã¦ã„ã‚‹æ™‚ã®çŠ¶æ³ç¢ºèªã€ãªã©ã«ã‚ˆãåˆ©ç”¨ã—ã¦ã„ã¾ã™ã€‚ã„ã‹ã«ã‚‚topã‚³ãƒžãƒ³ãƒ‰ã¨åŒã˜ã‚ˆã†ãªä½¿ã„æ–¹ã§ã™ã€‚ innotopã®é–‹ç™ºã¨ãƒ¡ãƒ³ãƒ†ãƒŠãƒ³ã‚¹ innotopã®ç«‹ã¡ä½ç½®ã¯å°‘ã—å¾®å¦™ã§ã™ã€‚innotopã¯ã‹ã¤ã¦Pe
shayash 2018/09/21
mysql

innotop
ãƒªãƒ³ã‚¯
é–‹ç™ºç’°å¢ƒç”¨æœ€ä½Žé™ã®MySQL / MariaDBè¨å®š
å„ã€…ã®ã‚¨ãƒ³ã‚¸ãƒ‹ã‚¢ãŒé–‹ç™ºã«ä½¿ç”¨ã™ã‚‹Dockerã‚„Vagrantç‰å†…ã®ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã£ã¦ã€ã‚ã¾ã‚Šãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°ã•ã‚Œã¦ã„ãªã‹ã£ãŸã‚Šã—ã¾ã™ã‚ˆãã€‚ ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°ã•ã‚Œã¦ã„ã‚‹ã‚µãƒ¼ãƒãƒ¼ã¯ã€ã‚«ãƒ¼ãƒãƒ«ãƒ‘ãƒ©ãƒ¡ãƒ¼ã‚¿å«ã‚å¤šãã®ãƒ‘ãƒ©ãƒ¡ãƒ¼ã‚¿ãŒèª¿æ•´ã•ã‚Œã¦ã„ã‚‹ã®ã§ä½•ãŒä¸»è¦ãªãƒã‚¤ãƒ³ãƒˆã‹åˆ†ã‹ã‚‰ãªã„ã€‚ è€éšœå®³æ€§ã‚’æ¨ã¦ã¦ã§ã‚‚é–‹ç™ºç’°å¢ƒã¯é€Ÿãã€‚ ãã‚“ãªæ–¹ã®ãŸã‚ã®é–‹ç™ºç’°å¢ƒç”¨ç°¡æ˜“ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°è¨˜äº‹ã§ã™ã€‚ MariaDBã¯10.1(10.xç³»)ã‚’æƒ³å®šã—ã¦ã„ã¾ã™ã€‚ â€»ã¡ãªã¿ã«ç†è€…ã¯ä¾¡æ ¼ã®äº‹ã‚’æ£šã«ä¸Šã’ã‚Œã°SQLServer(MSSQL)ã‚„ PostgreSQLã‚‚9.4ä»¥é™(ç‰¹ã«9.5)ãªã‚‰å¥½ãã§ã™ã—ã€MariaDBã¯Galera ClusterãŒã‚ã‚‹ã‹ã‚‰å¥½ãã§ã™ã€‚ ä»•äº‹ã§ã¯AWSã‚’æ‰±ã†æ©Ÿä¼šãŒéžå¸¸ã«å¤šã„ã®ã§Amazon Auroraã¯å¤–ã›ã¾ã›ã‚“ã€‚ è¨å®šä¾‹ãƒ¡ãƒ¢ãƒª1Gã»ã©ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã«å‰²ã‚Šå½“ã¦ã‚‹ä¾‹ã§ã™ã€‚ [mysqld] skip-name-resolve
shayash 2018/09/21
mysql

ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°
ãƒªãƒ³ã‚¯
MySQLã®EXPLAINã‚’å¾¹åº•è§£èª¬!!
ä»¥å‰ã€MySQLã‚’é«˜é€ŸåŒ–ã™ã‚‹10ã®æ–¹æ³•ã¨ã„ã†æŠ•ç¨¿ã§ã€ŒEXPLAINã®è¦‹æ–¹ã«ã¤ã„ã¦ã¯ã„ãšã‚Œè§£èª¬ã—ã‚ˆã†ã¨æ€ã†ã€ã¨æ›¸ã„ã¦ã—ã¾ã£ãŸã®ã§ã€ä»Šæ—¥ã¯ãã®å…¬ç´„ï¼Ÿã‚’æžœãŸãã†ã¨æ€ã†ã€‚ MySQLã®ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°ã§æœ€ã‚‚å¤§åˆ‡ãªã®ã¯ã€ã‚¯ã‚¨ãƒªã¨ã‚¹ã‚ãƒ¼ãƒžã®æœ€é©åŒ–ã§ã‚ã‚‹ã€‚ã‚¹ã‚ãƒ¼ãƒžã®è¨è¨ˆã¯ä¸€åº¦æ±ºã‚ã¦ã—ã¾ã†ã¨ãã®ãƒ†ãƒ¼ãƒ–ãƒ«ã‚’åˆ©ç”¨ã™ã‚‹å…¨ã¦ã®ã‚¯ã‚¨ãƒªã«å½±éŸ¿ã—ã¦ã—ã¾ã†ãŸã‚ãªã‹ãªã‹å¤‰æ›´ã™ã‚‹ã“ã¨ã¯å‡ºæ¥ãªã„ãŒã€ã‚¯ã‚¨ãƒªã¯ãã®ã‚¯ã‚¨ãƒªã ã‘ã‚’æ›¸ãç›´ã›ã°è‰¯ã„ã®ã§å¤‰æ›´ã®æ•·å±…ã¯ä½Žã„ã€‚ãã—ã¦é…ã„ã‚¯ã‚¨ãƒªã‚’ãªãã™ã“ã¨ã¯ã€æ€§èƒ½ã‚’å¤§å¹…ã«å‘ä¸Šã•ã›ã‚‹ãŸã‚ã®æœ€ã‚‚æœ‰åŠ¹ãªæ‰‹æ®µã§ã‚ã‚‹ã€‚å¾“ã£ã¦ã€ã‚¢ãƒ—ãƒªã‚±ãƒ¼ã‚·ãƒ§ãƒ³ã®æ€§èƒ½ã‚’å‘ä¸Šã•ã›ãŸã„ãªã‚‰ã€ã¾ãšæœ€åˆã«ã‚¯ã‚¨ãƒªã®ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°ã‚’æ¤œè¨Žã™ã‚‹ã¹ããªã®ã§ã‚ã‚‹ã€‚ æœ€é©åŒ–ã™ã‚‹ã¹ãã‚¯ã‚¨ãƒªã¯ã‚¹ãƒãƒ¼ã‚¯ã‚¨ãƒªãƒã‚°ã‚„ã‚¯ã‚¨ãƒªã‚¢ãƒŠãƒ©ã‚¤ã‚¶ã§è¦‹ä»˜ã‘ã‚‰ã‚Œã‚‹ãŒã€ã§ã¯ãã®ã‚ˆã†ãªã‚¯ã‚¨ãƒªãŒè¦‹ã¤ã‹ã£ãŸå ´åˆã«ã¯ã©ã®ã‚ˆã†ã«æœ€é©åŒ–ã™ã‚Œã°ã„ã„ã®ã‹ï¼Ÿãã®ãŸã‚ã«ã¯ã¾ãšç¾åœ¨ã©ã®ã‚ˆã†ã«ã‚¯ã‚¨ãƒªãŒå®Ÿè¡Œã•
shayash 2018/09/21
mysql

explain

ãƒãƒ¥ãƒ¼ãƒ‹ãƒ³ã‚°
ãƒªãƒ³ã‚¯
doruby.jp
shayash 2018/09/19
rds

mysql
ãƒªãƒ³ã‚¯
mysqlã§æ–‡å—ã‚³ãƒ¼ãƒ‰ã‚’utf8ã«ã‚»ãƒƒãƒˆã™ã‚‹ - Qiita
mysqlã®æ–‡å—ã‚³ãƒ¼ãƒ‰ã¯ãƒã‚§ãƒƒã‚¯ã™ã‚‹å ´æ‰€ãŒå¤šã„ã®ã§åŽŸå› ã‚’çªãæ¢ã‚ã‚‹ã®ã«æ¯Žå›žè‹¦åŠ´ã—ã¾ã™ã€‚ å¤§ããäºŒç¨®é¡žã«åˆ†ã‘ã‚‰ã‚Œã¦ã€ ã‚¯ãƒ©ã‚¤ã‚¢ãƒ³ãƒˆå´ã€ã‚µãƒ¼ãƒãƒ¼å´ï¼ˆmysqlã‚µãƒ¼ãƒãƒ¼ï¼‰ã€åŠã³ãã‚Œã‚‰ã®æŽ¥ç¶šã®æ–‡å—ã‚³ãƒ¼ãƒ‰ ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹/ãƒ†ãƒ¼ãƒ–ãƒ«/ã‚«ãƒ©ãƒ ã®æ–‡å—ã‚³ãƒ¼ãƒ‰ ã§ã™ã€‚ ##ãƒ‡ãƒ•ã‚©ãƒ«ãƒˆã‚’ãã¡ã‚“ã¨è¨å®šã—ã¦ãŠã ãã‚‚ãã‚‚ä½œæˆã—ãŸDBã®æ–‡å—ã‚³ãƒ¼ãƒ‰ãŒæ„å›³ã—ãªã„è¨å®šã«ãªã£ã¦ã„ãŸã‚‰ã€ãƒ‡ãƒ•ã‚©ãƒ«ãƒˆã®è¨å®šãŒé–“é•ã£ã¦ã„ã‚‹å¯èƒ½æ€§ãŒé«˜ã„ã®ã§ã€å†åº¦åŒã˜å•é¡Œã‚’èµ·ã“ã•ãªã„ãŸã‚ã«ã‚‚ã€è¨å®šè¦‹ç›´ã—â†’DBã‚’dropâ†’DBã‚’createã¨ã„ã†é †ç•ªã§ç›´ã—ã«è¡Œãã¾ã™ã€‚ 1ã‚‚2ã‚‚ãƒ‡ãƒ•ã‚©ãƒ«ãƒˆã®è¨å®šã¯ä¸‹è¨˜ã‚’å®Ÿè¡Œã™ã‚Œã°okã€‚ +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+---------
shayash 2018/09/01
mysql
ãƒªãƒ³ã‚¯
ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã¨ãƒ†ãƒ¼ãƒ–ãƒ«ã®ã‚µã‚¤ã‚ºã‚’ç¢ºèªã™ã‚‹æ–¹æ³• - Simple, Slowly
ãƒ†ãƒ¼ãƒ–ãƒ«ã®ã‚µã‚¤ã‚ºã¯phpMyAdminã§ã‚‚è¦‹ã‚Œã¾ã™ãŒã€ã‚³ãƒžãƒ³ãƒ‰ã‹ã‚‰è¦‹ã‚ŒãŸã»ã†ãŒä½•ã‹ã¨ä¾¿åˆ©ãªã®ã§ã€èª¿ã¹ã¦ã¿ã¾ã—ãŸã€‚ å…¨ã¦ã®ãƒ‡ãƒ¼ã‚¿ãƒ™ãƒ¼ã‚¹ã®ã‚µã‚¤ã‚ºã‚’ç¢ºèªã™ã‚‹ # MBå˜ä½ select table_schema, sum(data_length+index_length) /1024 /1024 as MB from information_schema.tables group by table_schema order by sum(data_length+index_length) desc; +--------------------------+---------------+ | table_schema | MB | +--------------------------+---------------+ | database1 | 5579.89978790| | databas
shayash 2018/08/31
mysql
ãƒªãƒ³ã‚¯
1 2 3 4 æ¬¡ã®ãƒšãƒ¼ã‚¸