{"id":5769,"date":"2019-04-08T13:27:00","date_gmt":"2019-04-08T05:27:00","guid":{"rendered":"https:\/\/blog.hoyo.idv.tw\/?p=5769"},"modified":"2023-03-08T13:13:25","modified_gmt":"2023-03-08T05:13:25","slug":"mysql-etc-my-cnf","status":"publish","type":"post","link":"https:\/\/blog.hoyo.idv.tw\/?p=5769","title":{"rendered":"MySQL - my.cnf"},"content":{"rendered":"<h2>\u8a2d\u5b9a\u6642\u5340<\/h2>\n<pre class=\"lang:default highlight:0 decode:true\">[mysqld]\r\ndefault-time-zone = '+8:00'<\/pre>\n<p>--<\/p>\n<h2>\u6700\u5927\u9023\u7dda\u6578<\/h2>\n<pre class=\"lang:default decode:true\">[mysqld]\r\nmax_connections=65535<\/pre>\n<p>\u91cd\u65b0\u555f\u52d5 MySQL \u5f8c\u4f7f\u7528 SQL \u6307\u4ee4\u67e5\u8a62\u662f\u5426\u751f\u6548<\/p>\n<pre class=\"lang:default decode:true\">SHOW GLOBAL VARIABLES LIKE '%max_connecti%'<\/pre>\n<p>--<\/p>\n<h2>CentOS 7 + MySQL 5.6\u00a0max_connections 214 \u554f\u984c<\/h2>\n<ul>\n<li><a href=\"https:\/\/sibevin.github.io\/posts\/2016-12-02-174519-mysql-max-connections-always-be-set-with-214\" target=\"_blank\" rel=\"noopener\">MySQL\u7684\u6700\u5927\u9023\u7dda\u6578\u4e00\u76f4\u88ab\u8a2d\u6210214<\/a><\/li>\n<li><a href=\"https:\/\/waylau.com\/rhel-mysql-connections-limit-214\/\" target=\"_blank\" rel=\"noopener\">RHEL\\CentOS 7 \u4e0b MySQL \u8fde\u63a5\u6570\u88ab\u9650\u5236\u4e3a214\u4e2a<\/a><\/li>\n<li><a href=\"https:\/\/www.opencli.com\/mysql\/change-mysql-open-files-limit\" target=\"_blank\" rel=\"noopener\">\u63d0\u9ad8 MySQL \u7684 open files limit<\/a><\/li>\n<\/ul>\n<p>Hoyo \u53ea\u4fee\u6539\u4e86 mysqld.service \u5373\u53ef\u8b93\u300c\u6578\u5b57\u6b63\u5e38\u300d\uff0c\u6c92\u6709\u4fee\u6539\u7cfb\u7d71 Open File Limit \u662f\u5426\u6709\u5f71\u97ff\u9700\u8981\u5f8c\u7e8c\u89c0\u5bdf<\/p>\n<pre class=\"lang:default decode:true \"># vi \/usr\/lib\/systemd\/system\/mysqld.service\r\n\r\n[Service]\r\nLimitNOFILE=65535\r\nLimitNPROC=65535\r\n\r\n# systemctl daemon-reload\r\n# systemctl restart mysqld.service<\/pre>\n<p>--<\/p>\n<h2>\u958b\u555f log<\/h2>\n<p>\u958b\u555f general-log \u529f\u80fd\u53ef\u4ee5\u7d00\u9304 MySQL \u6240\u6709\u57f7\u884c\u7684 SQL \u6307\u4ee4\uff0c<\/p>\n<pre class=\"lang:mysql decode:true\">show variables like 'log';<\/pre>\n<p>\u7de8\u8f2f my.cnf<\/p>\n<pre>[mysqld]\r\ngeneral-log = 1 # \u6253\u958b General Log\r\nlog_output=TABLE\u00a0# \u5c07 Log \u5132\u5b58\u5728 mysql \u8cc7\u6599\u5eab \u5167\u7684 general_log\u00a0\u8cc7\u6599\u8868\u5167<\/pre>\n<p>\u4f7f\u7528 SQL \u6307\u4ee4\u4fee\u6539 Log \u72c0\u614b<\/p>\n<pre class=\"lang:mysql decode:true\">SET GLOBAL general_log = 'ON';<\/pre>\n<p>--<\/p>\n<h2>\u4fee\u6539\u8a9e\u7cfb\u70ba UTF8<\/h2>\n<pre class=\"lang:default decode:true\">[mysqld]\r\ninit-connect = 'SET NAMES utf8'\r\ncharacter-set-server = utf8\r\ncollation-server = utf8_unicode_ci\r\n\r\n[client]\r\ndefault-character-set=utf8<\/pre>\n<ul>\n<li>character-set-server = utf8 \u2192\u00a0collation_server : utf8_general_ci<\/li>\n<li>collation-server = utf8_unicode_ci \u2192\u00a0collation_server : utf8_unicode_ci<\/li>\n<\/ul>\n<p>--<\/p>\n<h2>\u5c07 SQL mode \u79fb\u9664\u9810\u8a2d\u56b4\u683c\u6a21\u5f0f<\/h2>\n<pre class=\"lang:default decode:true\">[mysqld]\r\nsql_mode=<\/pre>\n<p>--<\/p>\n<h2>InnoDB \u5982\u4f55\u5206\u5340\u5340\u5206\u6a94\u6848<\/h2>\n<pre class=\"lang:default decode:true\">vi \/etc\/my.cnf\r\n\r\n[mysqld]\r\ninnodb_file_per_table=1<\/pre>\n<p>--<\/p>\n<h2>\u5e38\u99d0 IP<\/h2>\n<pre class=\"lang:default decode:true\">[mysqld]\r\nbind-address = 0.0.0.0<\/pre>\n<p>--<\/p>\n<h2>\u4fee\u6539 wait_timeout<\/h2>\n<ul>\n<li><a href=\"http:\/\/www.cnblogs.com\/jiunadianshi\/articles\/2475475.html\">MySQL\u4e2d\u7684\u914d\u7f6e\u53c2\u6570interactive_timeout\u548cwait_timeout(\u53ef\u80fd\u5bfc\u81f4\u8fc7\u591asleep\u8fdb\u7a0b\u7684\u4e24\u4e2a\u53c2\u6570)<\/a><\/li>\n<\/ul>\n<pre class=\"lang:default decode:true\">[mysqld]\r\nwait_timeout = 600\r\ninteractive_timeout = 600<\/pre>\n<p>--<\/p>\n<h2>Warning: World-writable config file '\/etc\/mysql\/my.cnf' is ignored<\/h2>\n<ul>\n<li><a href=\"https:\/\/blog.csdn.net\/yumushui\/article\/details\/49124699\" target=\"_blank\" rel=\"noopener\">mysql\u542f\u52a8\u65f6\uff0c\u63d0\u793a\/etc\/my.cnf \u88ab\u5ffd\u7565\u7684\u95ee\u9898\u5904\u7406_yumushui\u7684\u535a\u5ba2-CSDN\u535a\u5ba2<\/a><\/li>\n<\/ul>\n<pre class=\"lang:default decode:true \"># chmod 664 my.cnf<\/pre>\n<p>--<\/p>\n<div class=\"pvc_clear\"><\/div>\n<p class=\"pvc_stats all \" data-element-id=\"5769\" style=\"\"><i class=\"pvc-stats-icon medium\" aria-hidden=\"true\"><svg aria-hidden=\"true\" focusable=\"false\" data-prefix=\"far\" data-icon=\"chart-bar\" role=\"img\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 512 512\" class=\"svg-inline--fa fa-chart-bar fa-w-16 fa-2x\"><path fill=\"currentColor\" d=\"M396.8 352h22.4c6.4 0 12.8-6.4 12.8-12.8V108.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v230.4c0 6.4 6.4 12.8 12.8 12.8zm-192 0h22.4c6.4 0 12.8-6.4 12.8-12.8V140.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v198.4c0 6.4 6.4 12.8 12.8 12.8zm96 0h22.4c6.4 0 12.8-6.4 12.8-12.8V204.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v134.4c0 6.4 6.4 12.8 12.8 12.8zM496 400H48V80c0-8.84-7.16-16-16-16H16C7.16 64 0 71.16 0 80v336c0 17.67 14.33 32 32 32h464c8.84 0 16-7.16 16-16v-16c0-8.84-7.16-16-16-16zm-387.2-48h22.4c6.4 0 12.8-6.4 12.8-12.8v-70.4c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v70.4c0 6.4 6.4 12.8 12.8 12.8z\" class=\"\"><\/path><\/svg><\/i> &nbsp;2,734&nbsp;total views<\/p>\n<div class=\"pvc_clear\"><\/div>\n","protected":false},"excerpt":{"rendered":"<p>\u8a2d\u5b9a\u6642\u5340 [mysqld] d...<\/p>\n<div class=\"pvc_clear\"><\/div>\n<p class=\"pvc_stats all \" data-element-id=\"5769\" style=\"\"><i class=\"pvc-stats-icon medium\" aria-hidden=\"true\"><svg aria-hidden=\"true\" focusable=\"false\" data-prefix=\"far\" data-icon=\"chart-bar\" role=\"img\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" viewBox=\"0 0 512 512\" class=\"svg-inline--fa fa-chart-bar fa-w-16 fa-2x\"><path fill=\"currentColor\" d=\"M396.8 352h22.4c6.4 0 12.8-6.4 12.8-12.8V108.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v230.4c0 6.4 6.4 12.8 12.8 12.8zm-192 0h22.4c6.4 0 12.8-6.4 12.8-12.8V140.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v198.4c0 6.4 6.4 12.8 12.8 12.8zm96 0h22.4c6.4 0 12.8-6.4 12.8-12.8V204.8c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v134.4c0 6.4 6.4 12.8 12.8 12.8zM496 400H48V80c0-8.84-7.16-16-16-16H16C7.16 64 0 71.16 0 80v336c0 17.67 14.33 32 32 32h464c8.84 0 16-7.16 16-16v-16c0-8.84-7.16-16-16-16zm-387.2-48h22.4c6.4 0 12.8-6.4 12.8-12.8v-70.4c0-6.4-6.4-12.8-12.8-12.8h-22.4c-6.4 0-12.8 6.4-12.8 12.8v70.4c0 6.4 6.4 12.8 12.8 12.8z\" class=\"\"><\/path><\/svg><\/i> &nbsp;2,734&nbsp;total views<\/p>\n<div class=\"pvc_clear\"><\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[31],"tags":[],"_links":{"self":[{"href":"https:\/\/blog.hoyo.idv.tw\/index.php?rest_route=\/wp\/v2\/posts\/5769"}],"collection":[{"href":"https:\/\/blog.hoyo.idv.tw\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.hoyo.idv.tw\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.hoyo.idv.tw\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.hoyo.idv.tw\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5769"}],"version-history":[{"count":11,"href":"https:\/\/blog.hoyo.idv.tw\/index.php?rest_route=\/wp\/v2\/posts\/5769\/revisions"}],"predecessor-version":[{"id":11712,"href":"https:\/\/blog.hoyo.idv.tw\/index.php?rest_route=\/wp\/v2\/posts\/5769\/revisions\/11712"}],"wp:attachment":[{"href":"https:\/\/blog.hoyo.idv.tw\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5769"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.hoyo.idv.tw\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5769"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.hoyo.idv.tw\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5769"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}