5æéã¢ãã® alter table ã Lost connection to MySQL server during query ãããã ãã© show processlist ããã¨ã¾ã åãã¦ãã£ã½ãããæ¾ç½®ãã¨ãã°å®éããã®ããªã¨æã£ã¦ããã ãã©å¤§ä¸å¤«ã ãããããã 2015-03-30 16:47:05 via Twitter for iPhone ã¨ãããã show processlist ãã¦ã¾ã åãã¦ãã£ã½ãã£ãããdatadir é ä¸ã§ #sql-xxxx_xxxxx.ibd ãªååã®ãã¡ã¤ã«ãè²ã£ã¦ãããã©ãã確èªããã ã¿ã¤ã ã¹ã¿ã³ããæ´æ°ããã¦ããã°ãã³ãã©ãªãã¼ãã«ã¸ã®ãã¼ã¿ã³ãã¼ãçãã¦ãã¨ãããã¨ã«ãªãã®ã§ããã¨ã¯ show processlist ãç£è¦ãã¦çµããã®ãå¾ ã¦ã°è¯ãã ã§ãçµãã£ãã£ã½ãã£ãã show create table ã確èªãã¦å¤æ´ã
12. ã¹ãããçãªãã® ãµã¤ãº 使ãã©ãã ã³ãã³ãä¾ ãã«ããã¯ã¢ãã ã§ãã å¿ ãå¿ è¦ tar, rsync, mysqldump, XtraBackup å·®åããã¯ã¢ãã ⼩ãã ãã«ããã¯ã¢ããã® ééãçããã°è¦ã ãªã mysqldump(ã¹ãã¼ ãã«å¶ç´) XtraBackup å¢åããã¯ã¢ãã æ´æ°éã«ä¾å ã»ã¼ééããªãå¿ è¦ cp, rsync, mysqlbinlog 11/58 14. ãã«ããã¯ã¢ããã®é¸æè¢ ã³ãã³ã ã¨ã³ã¸ã³ ã¢ããªå½±é¿ â½ å¼ ãµã¤ãº tar, rsync MyISAM à åâ½ã¾ãã¯ãã 㯠ç©ç ⼤ãã tar, rsync InnoDB à mysqldåâ½ ç©ç ⼤ãã LVMã¹ããã ã·ã§ãã MyISAM InnoDB â³ æ§è½å£åãã²ã© ã ç©ç ⼤ãã mysqldump MyISAM à ãã㯠è«ç ⼩ãã mysqldum
ãã¾ãã«ãå¦çã«æéãããããããªSQLãå®è¡ãã¦ãã¾ããMySQLãããã¨ãããã¨ãè¨ããªããªã£ã¦ãã¾ããããªç¶æ³ãããããã¾ããããã£ã¦ããããã¾ããããªç¶æ³ãã£ã¦ã¯ãªããªããã§ãããæãã¾ããã¾ãããããªæãåé¡ã¨ãªã£ã¦ããã¯ã¨ãªã®å¦çãæ¢ãããããã§ãã ç¹å®ã®ã¯ã¨ãªãæ¢ããæ¹æ³ MySQLã§å®è¡ä¸ã®ã¯ã¨ãªä¸è¦§ãè¦ã¦ãSQLãå¼·å¶çµäºããæ¹æ³ ãã¡ããè¦ã¦ããããã°ããæ¹ã¯åããã¾ããåç´ã«MySQLã«å ¥ã£ã¦ãshow processlist;ã§åé¡ã®ããã¯ã¨ãªãçºè¦ããããã»ã¹IDã kill ããã ããã¨ã¦ãç°¡åã è¤æ°ã®ã¯ã¨ãªãä¸æ¬ã§æ¢ããæ¹æ³ ä»åã¯åé¡ã®ããã¯ã¨ãªã100åãã£ããã©ãããâ¦ï¼çãªã®ã解決ããã¨ã³ããªã¼ã§ããã¾ããåé¡ããã¯ã¨ãª100åããç¶æ³ã¯ãã¢ããªçã«åé¡ããããããï¼ã£ã¦ããã¬ãã«ã§ããã 1åãã¤ããã»ã¹IDãã³ãããã¦â¦ãªãã¦ãã£ã¦ãããªãã§ã
ãã¾ãããã£ã¦ãåºã¦ããªãã£ãã®ã§ç°¡åã«æ¸ãã¦ã¿ãã MySQL5.5ã®my.cnfããã®ã¾ã¾æµç¨ãã¦MySQL5.6ãèµ·åããã¨ä»¥ä¸ã®ãããªã¡ãã»ã¼ã¸ãã¨ã©ã¼ãã°ãã¡ã¤ã«ã«åºåããããé·ãã®ã§æ¹è¡ãã¦ãã¾ããå®éã¯ä¸è¡ã 121104 22:29:57 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).ãã¡ãã¯MySQL 5.6.6ãã追å ãããexplicit_defaults_for_timestampã«ãããã®ã booleanã§ããã©ã«ãã¯FALSE(0)ãåçå¤æ´ã¯ä¸å¯ã¨ãªãã¾ããå ¬å¼Doc ã¨ããããFALSEã®
MySQL ã¯ã次ã®ãããªå¤ãªæ¥æã DATETIME ã«æ¿å ¥åºæ¥ã¦ãã¾ãã¾ãã create table tt (dd datetime); insert into tt value (0); /* 0000-00-00 00:00:00 */ insert into tt value (''); /* 0000-00-00 00:00:00 */ insert into tt value ('9999-99-99 99:99:99'); /* 0000-00-00 00:00:00 */ insert into tt value ('2000-04-31 00:00:00'); /* 0000-00-00 00:00:00 */ insert into tt value ('2000-00-00 00:00:00'); /* 2000-00-00 00:00:00 */ ãã¾ãè¨ç·´ãã
æ²é¡ã®MySQL5.6ã®æ°æ©è½ã§éãã§ã¿ãã¡ã¢ã InnoDBãããã¡ãã¼ã«ã®å 容ããã¡ã¤ã«ã«æ¸ãåºãã¦ãããã¨ãåºæ¥ãæ©è½ã追å ãããã æ¸ãåºãã¿ã¤ãã³ã°ã¯æ£å¸¸çµäºæ(SET GLOBALã¾ãã¯my.cnfã§innodb_buffer_pool_dump_at_shutdown = 1ãè¨å®)ã¾ã㯠任æã®ã¿ã¤ãã³ã°ã§å³ææ¸ãåºã(SET GLOBAL innodb_buffer_pool_dump_now = 1;) èªã¿åºãã¿ã¤ãã³ã°ã¯èµ·åæ(my.cnfã«innodb_buffer_pool_load_at_startup = 1)ã¾ã㯠任æã®ã¿ã¤ãã³ã°ã§å³æèªã¿è¾¼ã¿(SET GLOBAL innodb_buffer_pool_load_now = 1;) ããã§ãmysqldãåèµ·åãã¦ãããã¡ãã¼ã«ã空ã£ã½ã«ãªã£ã¦ã ç´ æ©ãã¦ã©ã¼ã ã¢ããã§ããããã¨ãããã¨ãããã ç¹ã«ä½
MySQLã«ã¯Strict Modeã£ã¦ã®ããããã¨ãç¥ã£ãã®ã§ã¡ã¢ã Strict Modeã£ã¦ã®ã¯STRICT_TRANS_TABLESãSTRICT_ALL_TABLESã®ã©ã¡ãããæå¹ã«ãªã£ã¦ãç¶æ ã®äºã è¨å®æ¹æ³ã¯my.cnfã«æ¸ããã§ãããããããStrict Modeã£ã¦ä½ã£ã¦è©±ããã§ããã Strict Modeã¨ã¯ ãã®è¨å®ã¯ã«ã©ã ã«ä¸æ£ãªå¤ãå ¥ããå ´åã«è¦åãããªãã¦ã¨ã©ã¼ãè¿ãã¨ãããã®ã ä¾ãã°charã®ã«ã©ã ã«"1"ã¨ããæååãå ¥ãããæãStrict Modeãoffã®å ´åã¯ããã«ã³ã¼ãã¼ã·ã§ã³ãä»ããªãã¨ã¨ã©ã¼ã«ãªããStrict Modeãoffã ã¨ãã¾ããã¨ãã£ã¦ãããã ç´°ããæåããªãã·ã§ã³ã§å¶å¾¡å¯è½ã ä¾ãã°Strict Modeãonã«ããç¶æ ã§Dateã®ã«ã©ã ã«"2012-04-31"ã¨å ¥ããã¨ã¨ã©ã¼ã«ãªãã ã ãã©ALLOW_INVALID_
ããã¾ããããã¥ã¢ã«ã«æ¸ãã¦ãããã©ã http://dev.mysql.com/doc/refman/5.6/en/option-files.html Linuxã§å ¬å¼MySQLã--defaults-file, --defaults-extra-fileãæå®ããªãå ´åã¯ã /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf SYSCONFDIR/my.cnfã®ãã¨ã å ¬å¼ã®ãã¤ããªã¼ã¯SYSCONFDIR== /usr/local/mysql/etcãcmakeã®ãªãã·ã§ã³ã§å¤ããããã MariaDBã¯ããã¤ãè¨å®ããã¦ããªãã£ã½ãã /usr/local/mysql/my.cnf $MYSQL_HOME/my.cnfã®ãã¨ã basedirãè¨å®ãã¦ãã°$MYSQL_HOME== basedirã«ãªãã /home/
ããå¯ã®å ¥ããéãã¾ããããDBAã®ããªãã§ãã GAãããããã1å¹´ã社å ã§ã¯ããç¸å½ã«ã¸ã¥ã¢ã«ã«MySQL 5.6ãã¤ã³ã¹ãã¼ã«ãã¦ãã¾ããä»ã¾ã§ã¯æ°è¦ãµã¼ãã¹(ããæ°è¦æ©è½)ã§ã®å°å ¥ãã»ã¨ãã©ã ã£ã5.6ãããã®ãã³ãã©ãã£ãã¯ã¬ã³ã¬ã³ã®ãµã¼ãã¹ã«ã¢ããã°ã¬ã¼ãã§å°å ¥ãã¾ããï¼ã¨ãåããã«è¨ã£ã¦ãã¾ããç§ã§ãªãå¥ã®DBAæ°ã主æ å½ã®ãµã¼ãã¹ã§ãï¼ ä¸»ãªçç±ã¯InnoDB Compressedã使ã£ã¦ããã®ã§ãã®æ§è½ã¢ããã«æå¾ â¦ã¨ããã¨ããã ã£ããã§ãããå¼ç¤¾DBAãç¥ä»£ã®æ代ãã試è¡é¯èª¤ãéããã©ãçãã究極ã®my.cnf(?)ãããããç§ä¼ã®ã¿ã¬ã æ®ã念ããªãããããè ãã£ãã¦ãããã å¤ãå¾¹ãã¦ã¢ããã°ã¬ã¼ãä½æ¥ããã¦ããæ å½DBAæ°ãéãé¡(æ¨å®ããã£ããã ã£ã)㧠ã¹ãããã¼ãã¯ãã¨ããªãã¼ããã1ã0ãåãããããããã«ããªãã£ãããããã ãããã©ã⦠ã¨è¨´ããå½¼ã¯ãã®
ããã£ãï¼DBA å ¼ãªãã¬ã¼ã·ã§ã³ã¨ã³ã¸ãã¢å ¼ã¿ã¹ã¯ããã¼ã¸ã£ãã£ã¦ã riywo ã§ããä½ã®ãã¿ãæ¸ããããªãã¨èããã®ã§ãããæ£ç´ãã¿ãä»è¾¼ãæéããªãã£ãã®ã§åããã¤ããã£ã¦ããã¨ãããã£ã¨ç´¹ä»ããã¨ãããã¨ã§åå¼ãã¦ä¸ããï¼ï¼ MySQL ãããããï¼ 03:14 hidek: ãªããã¨ã©ã¼åºã¾ãã£ã¦ããã ãã©ï¼ 03:14 zigorou: MySQL ã¨éä¿¡ãã¦ãã¨ãã£ã½ã 03:15 riywo: è¦ã¾ã ãããªããã¨ãã¯çããæ¥å¸¸è¶é£¯äºã§ãããï¼ãï¼ãï¼ãããªæã«ãDB ã«è²¬ä»»ãæã¤ãã®ã¨ãã¦çã£å ã«å¯¾å¿ããã®ãåãã®ä»äºã§ããã§ãããããå ·ä½çã«ãã®ãã¨ä½ããã¾ãããï¼æ¢ã«ãµã¼ãã¹ã¯ã¨ã©ã¼ã ããã§ä¸å»ãäºãç¶æ ã§ãã (対å¿éå§) ã¾ãã¯ã¨ã©ã¼ã¡ãã»ã¼ã¸ ä»åã®æ§ãªå ´åã¯ã¢ããªã®ã¨ã©ã¼ãã°ã«ã©ã°ã£ã¨ MySQL ã«é¢ããã¨ã©ã¼ãåºã¦ããã§ããããã¾ãã¯ãããè¦ããã¨ãå§ã¾
FROMå¥ã«ãµãã¯ã¨ãªã使ãæã®é çªãæ¸ããã®ã§ã ä»åº¦ã¯WHEREå¥ã«ä½¿ãæï¼ç¸é¢ãµãã¯ã¨ãªï¼ã®é çªãèå¯ãã¦ã¿ãã æ¯åº¦ã®æãé£ããµã³ãã«ãã¼ãã«ã¯FROMå¥ãµãã¯ã¨ãªã®æã¨ä¸ç·ã ãã¨ãä»åã¯t1ãã¼ãã«ãã¾ãã£ã¨tt1ãã¼ãã«ã«ãã³ãã¼ãã¦2ã¤ã«ãã¦ããã åãæ¢ãããããªãµãã¯ã¨ãªã¯å¦ä½ã ãããã mysql> SELECT * FROM t1 WHERE val IN (SELECT val FROM tt1 WHERE num BETWEEN 1 AND 10); +-----+----------------------------------+ | num | val                | +-----+----------------------------------+ |  1 | c4ca4238a0b923820dcc5
å ¬å¼Facebookãã¼ã¸ã§ã»ãã·ã§ã³æ å ±ãè¬å¸«æ å ±ãå ¬é be a Database Tongalist ãã¨ããã£ããã¼ã¿ãã¼ã¹ã¨ã³ã¸ãã¢ã«ãªãã ã»ãã·ã§ã³ ã»ãã·ã§ã³ã¹ã±ã¸ã¥ã¼ã« *ã»ãã·ã§ã³ããã¯ã¹ãã¯ãªãã¯ããã¨ãè¬å¸«æ å ±ãã»ãã·ã§ã³æ¦è¦ãã覧ããã ãã¾ã *ã»ãã·ã§ã³ã¹ã±ã¸ã¥ã¼ã«ã»å 容ã¯å¤æ´ãããå ´åããããã¾ã *ã»ãã·ã§ã³ã¯äºç´å¶ã§ã¯ãããã¾ãããæºå®¤ã®å ´åã¯å ¥å®¤ããã ããªãå ´åããããã¾ã *ã»ãã·ã§ã³ã«ã¼ã ã®å®å¡ã¯ã åé¨å±70åã¨ãªã£ã¦ããã¾ã *å¤å½äººè¬å¸«ã®ã»ãã·ã§ã³ã¯ãè±èªã¨ãªãã¾ã 11æ13æ¥(æ°´) Opening Session: 10:00 â 10:45 A11:Opening Database Tongalistãåãéãããã¼ã¿ãã¼ã¹æè¡ã®æªæ¥å°å¹¡ä¸é Session2: 11:00 â 11:45 A12:Oracle Trouble-s
æ¾ä¿¡ãããä½ã£ã Replication Booster for MySQL ããã¼ã¿ãµã¤ãºã大ãããã¼ã¿ãã¼ã¹ã«å¯¾ãã¦ä½¿ã£ã¦ã¿ã¾ããã Yoshinori Matsunobuâs blog: Making slave pre-fetching work better with SSD github - yoshinorim/replication-booster-for-mysql Replication Booster for MySQL ããã®ãããç°¡åã«èª¬æããã¨ã以ä¸ã®ããã«ãªãã§ããããã MySQL ã§ã¬ããªã±ã¼ã·ã§ã³ãè¨å®ããå ´åããã¹ã¿ã¼ã®ãã¤ããªãã°ãIOã¹ã¬ãããèªã¿åããrelay-logã¸è¨é²ãã¾ããããã¦SQLã¹ã¬ãããrelay-logããèªã¿åã£ã¦ãã¼ãã«ãæ´æ°ãã¦è¡ãã¾ããReplication Booster ãå®è¡ããã¨relay-logãèªã¿åããæ´
April 8, 2013 MySQL Casual Talks vol.4ã§è©±ãã¦ã¿ã¦ ã£ã¦myfinderããããã声ãããã¦ããã£ã¦ãSQLå¨ããã¼ã¿ããã§çãã¦ã㦠ä»è²ã åå¼·ãã ãã¦ããã©ãæ£ç´å½æ¥ã¾ã§ã«æç¨ãªè©±ãã§ããæ°ããã¦ãªãã åæ¥é±ã®ãªã¬ãä»ã®ãªã¬ããããå°ãææ義ãªæ å ±ãæã¡åããã¦ããããã«ãããã ãã®åtweets.zipãMySQLã«çªã£è¾¼ãã§SQLãå¦ã¶(å°å ¥ç·¨)ã¨ããã®ãæ¸ã㦠ãããã³ããªã¼å ¥ã£ããã¨ããä½åããã¯ãæ°æ°ããã§ããï¼ã¨ã ã³ã³ãã³ãåé«ãã¨ããæ£ç´ä¸èº«ãªãã§ããã¢ã¬ã¨ããè²ã è¨ããããã ãã© SQLãã¯ããã¦ä½æããã«ããã£ã¦åå¼·ã«ãªã£ãã®ã§ããã®æã®ã¡ã¢ åè: MySQL 5.1 ãªãã¡ã¬ã³ã¹ããã¥ã¢ã« :: 10 ãã¼ã¿ã¿ã¤ã :: 10.1 ãã¼ã¿ã¿ã¤ãæ¦è¦ :: 10.1.4 ãã¼ã¿ã¿ã¤ãããã©ã«ãå¤ TEXTã¿ã¤ãã§ã¯NOT
追è¨ï¼è¨äºã®æä¸ã§5.6ã®sql_modeããã©ã«ãå¤ã«ã¤ãã¦è¥å¹²å®éã®æåã¨ç°ãªã表è¨ããã¦ãã¾ãããrpmã§installããã¨/usr/my.cnfã¨ããã®ãã²ãã£ãããã¦ããã®ä¸ã« [mysqld] sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES ã¨ããè¨è¿°ããããããã/etc/my.cnfã¨åããã¦è¨å®ãã¦ããæ§ã§ããã§ãããã©ã«ãå¤ã«ã¤ãã¦ã¯5.6.6以éã¯ããã©ã«ãå¤ããThe default SQL mode in MySQL 5.6.6 and later is NO_ENGINE_SUBSTITUTION;ãã§ãã以åã®ããã©ã«ãå¤ã¯ãMySQL 5.6.5 and earlier, it was empty (no modes set)ãã¨ãªã£ã¦ããããã§ããã 詳ãã㯠http://yoku0825.blo
1 Copyright 2009 Sun Microsystems inc The Worldâs Most Popular Open Source Database Linux/MySQLãµã¼ãã¼ã® ããã©ã¼ãã³ã¹ãã¥ã¼ãã³ã° æ¾ä¿¡ åç¯ (MATSUNOBU Yoshinori) http://twitter.com/matsunobu http://opendatabaselife.blogspot.com 2 Copyright 2009 Sun Microsystems inc The Worldâs Most Popular Open Source Database èªå·±ç´¹ä» ⢠Sun Microsystemsæå± MySQLã³ã³ãµã«ã¿ã³ã ⢠2006å¹´9æããMySQLã³ã³ãµã«ã¿ã³ã ã¨ãã¦å¤å ⢠ããã©ã¼ãã³ã¹ãã¥ã¼ãã³ã°ã HAç°å¢ã®æ§ç¯ãDBAãã¬ã¼ãã³ã°ç ãæ°
使ã£ã¦ã¿ããBugsã«è²ã ä¸ãã£ãããã¦ããã®ãè¦ãã®ã§ã¡ã¢ã ãã¿å ã¯Oracleå ¬å¼ã®ããã MySQL Server 5.6 defaults changes ã»binlog_checksum â5.6ããã®æ°è¦ãã©ã¡ã¼ã¿ã æé»ã®ããã©ã«ãã¯crc32ã ãã ãã¹ã¿ã¼ã5.6ãã¹ã¬ã¼ãã5.5以ä¸ã®(å®ç³ãç¡è¦ãã)ç°å¢ã§ã¯noneã§ãªãã¨I/O Threadã転ããã ã»innodb_buffer_pool_instances â5.5ã§ã¯ããã©ã«ã1ããããã©ã«ãautosized8ã«ã autosizedã§ã¯innodb_buffer_pool_sizeã1300M以ä¸ã®æã¯innodb_buffer_pool_size/128Mã«è¨å®ããããããã æ¨ä¸ãããæã5.5ã§ã¯1ããåãããªãæ¹ãè¯ãããã£ã¦æ¸ãã¦ããããã©ã Dimitriããã5.6ã§ãã£ããã¤ãè¦ãã¨ä½¿ã
Viva Developer! Oracle ACEã£ã¦ã©ããªäººï¼ä¸çåºæºã®ãããã¨ã³ã¸ãã¢ã«ã¤ã³ã¿ãã¥ã¼ ã第11åï¼å¹³å¡ è²å¤«æ°ããMySQL ã®ãã¨ãæ£ããä¼ããã 2012å¹´ãMySQLã®åéã§æ°ããªOracle ACE ãèªçãã¾ãããä»åæ°ãã«èªå®ãããå¹³å¡è²å¤«æ°ã«Oracle ACE èªå®ã®ææ³ã伺ãã¾ãããMySQL ã®æè¡æ å ±ãæ£ããä¼ããããã«å¹³å¡æ°ãã¨ã£ãè¡åã¨ã¯ï¼ç·¨éé¨ï¼ â MySQL ã¨ã®åºä¼ã Oracle ACE å¹³å¡ è²å¤«æ° NTTã³ã ã¦ã§ã¢æ ªå¼ä¼ç¤¾ã§ããã¼ã¿ãã¼ã¹ã¨ã³ã¸ãã¢ã¨ãã¦Oracle Databaseã¨MySQLã使ãåãæ¥ã ã ã¯ã©ã·ãã¯é³æ¥½ã好ãã§ãèªãããã¢ããæ¼å¥ããã ããã°ï¼SH2ã®æ¥è¨(http://d.hatena.ne.jp/sh2/) ç¾å¨ããã¼ã¿ãã¼ã¹ã®æè¡æ¯æ´ããã©ãã«å¯¾å¿ãè¡ãé¨ç½²ã«æå±ãã¦ãã¾ãããã®é¨ç½²ã¯ãå ã Orac
ãªãªã¼ã¹ãé害æ å ±ãªã©ã®ãµã¼ãã¹ã®ãç¥ãã
ææ°ã®äººæ°ã¨ã³ããªã¼ã®é ä¿¡
å¦çãå®è¡ä¸ã§ã
j次ã®ããã¯ãã¼ã¯
kåã®ããã¯ãã¼ã¯
lãã¨ã§èªã
eã³ã¡ã³ãä¸è¦§ãéã
oãã¼ã¸ãéã
{{#tags}}- {{label}}
{{/tags}}