MySQL 5.5 配置文件设置

ゝ一世哀愁。 2024-04-17 23:52 119阅读 0赞

在MySQL数据库中，可以没有配置文件，在这种情况下，MySQL会安装编译时的默认参数设置启动实例  
用以下命令查看，当MYSQL数据库实例启动时，它会在哪些位置查找配置文件  
\[root@localhost test\]\# mysql --help | grep my.cnf  
                      order of preference, my.cnf, $MYSQL\_TCP\_PORT,  
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf  
  
**MySQL里面有启动参数、系统参数，启动参数通过show variables无法查询到，可以在mysqld的选项中查到**  
例如关于二进制日志有Startup Options Used with Binary Logging、System Variables Used with Binary Logging两种参数  
  
 \[root@localhost log\]\# mysqld --verbose --help > 02.txt  
\[root@localhost log\]\# vim 02.txt  
..  
  **--binlog-do-db=name** Tells the master it should log updates for the specified  
                      database, and exclude all others not explicitly  
                      mentioned.  
..  
\[root@localhost log\]\# vim /etc/my.cnf  
..  
\# log  
server-id = 10000  
log-bin = /log/binlog/mysql-bin  
binlog\_format = MIXED   
binlog-do-db = test  
 \[root@localhost data\]\# vim /etc/my.cnf  
  
 \[client\]  
 port = 3306  
 socket=/mysql\_data/70005/mysql.sock  
  
 \# Mysql server  
 \[mysqld\]  
 port = 3306  
 user = mysql  
 socket=/mysql\_data/70005/mysql.sock  
 pid-file = /mysql\_data/70005/mysqld.pid  
 basedir = /software  
 datadir = /data  
 \#tmpdir =   
 open\_files\_limit = 10240  
 max\_connections = 500  
 max\_connect\_errors = 100000  
 sql\_mode = NO\_ENGINE\_SUBSTITUTION,STRICT\_TRANS\_TABLES  
 wait\_timeout = 100  
  
 \# Cache  
 table\_open\_cache = 400  
 thread\_cache\_size = 300  
  
 \# Buffer  
 max\_allowed\_packet = 256M  
 max\_heap\_table\_size = 256M  
 net\_buffer\_length = 8K  
 sort\_buffer\_size = 4M  
 join\_buffer\_size = 2M  
 read\_buffer\_size = 2M  
 read\_rnd\_buffer\_size = 16M  
 \#query\_cache\_size = 32M  
  
 \# Log  
 server-id = 1  
 log-bin = /log/binlog/mysql-bin  
 sync\_binlog = 2  
 binlog\_cache\_size = 32M  
 max\_binlog\_cache\_size = 512M  
 max\_binlog\_size = 512M  
 binlog\_format = MIXED  
 \#binlog\_cache\_use = 100  
 \#binlog\_cache\_disk\_use = 50  
 log\_output = FILE  
 log\_error = /log/err.log  
 slow\_query\_log = 1  
 slow\_query\_log\_file = /log/slow\_query.log  
 long\_query\_time = 10  
 log-short-format = 0  
 log\_slow\_admin\_statements = 0  
 log\_queries\_not\_using\_indexes = 0  
 \#log\_throttle\_queries\_not\_using\_indexes = 0  
 log\_slow\_slave\_statements = 0  
 general\_log = 0  
 general\_log\_file = /log/general\_query.log  
  
 \# InnoDB  
 innodb\_data\_file\_path = ibdata1:12M:autoextend:max:100G  
 \#innodb\_data\_file\_path = /ibdata/ibdata1:12M:autoextend:max:100G  
 innodb\_data\_home\_dir = /ibdata  
 innodb\_file\_per\_table = 1  
 innodb\_log\_group\_home\_dir = /log/innodb  
 \#innodb\_log\_file\_size = 256M  
 \#innodb\_log\_files\_in\_group = 3  
 innodb\_log\_buffer\_size = 8M  
 innodb\_buffer\_pool\_size = 100M  
 \# InnoDB undo \_\_ Mysql 5.6 or higher  
 \#innodb\_undo\_directory = /log/undo  
 \#innodb\_undo\_tablespaces = 2  
 \#innodb\_undo\_logs = 200

innodb\_fast\_shutdown = 1

innodb\_flush\_log\_at\_trx\_commit = 1  
 innodb\_buffer\_pool\_instances = 5  
  
 \# MyISAM  
 \#skip-external-locking  
 \#key\_buffer\_size = 128M  
 \#myisam\_sort\_buffer\_size = 64M  
  
 \# Character set  
 \#character\_set\_server = gbk  
 \#collation\_server = gbk\_bin  
  
 \# Federated storage engine  
 federated  
  
 \# Transaction  
 init\_connect='SET autocommit=0'

\[mysql\]  
 no-auto-rehash  
 default-character-set = gbk  
  
 \[mysqldump\]  
 quick  
 max\_allowed\_packet = 16M  
  
 \[myisamchk\]  
 \#key\_buffer\_size = 256M  
 \#sort\_buffer\_size = 256M  
 \#read\_buffer = 2M  
 \#write\_buffer = 2M  
  
 \[mysqlhotcopy\]  
 \#interactive-timeout

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2073963/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2073963/