mysql5.7怎样安装
发布时间：2022-01-04 17:29:25 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要介绍了mysql5.7如何安装，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。 一、软件下载 登录到 http://dev.mysql.com/downloads/mysql/ ，选择对应的版本进行下载。 二、创建
|
这篇文章主要介绍了mysql5.7如何安装，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。 一、软件下载 登录到 http://dev.mysql.com/downloads/mysql/ ，选择对应的版本进行下载。 二、创建mysql 用户 [root@localhost ~]# groupadd mysql [root@localhost ~]# useradd -g mysql mysql [root@localhost ~]# passwd mysql 将tar 包拷贝到mysql的home 目录下，进行解压后得到的目录 重命名为 mysql-5.7.9 三、安装数据库 进入 /mysql/mysql5.7/bin [mysql@localhost bin]$ ./mysql_install_db --user=mysql --basedir=/mysql/mysql5.7 --datadir=/mysql/mysql5.7/data 2017-04-19 06:49:00 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize 2017-04-19 06:49:07 [ERROR] The bootstrap log isn't empty: 2017-04-19 06:49:07 [ERROR] 2017-04-19T14:49:01.852039Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead 2017-04-19T14:49:01.868000Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000) 2017-04-19T14:49:01.868056Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000) 2017-04-19T14:49:01.888730Z 0 [ERROR] Can't read from messagefile '/usr/share/mysql/english/errmsg.sys' 提示mysql_install_db 命令已弃用，使用mysqld [mysql@localhost bin]$ ./mysqld --user=mysql --basedir=/mysql/mysql5.7 --datadir=/mysql/mysql5.7/data [root@webtest1 mysql]# bin/mysqld --initialize --user=mysql --basedir=/mysql/mysql5.7 --datadir=/mysql/mysql5.7/data 2017-04-19T14:50:18.145225Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000) 2017-04-19T14:50:18.145526Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000) 2017-04-19T14:50:18.797964Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2017-04-19T14:50:18.798019Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path. 2017-04-19T14:50:18.798053Z 0 [Note] ./mysqld (mysqld 5.7.9) starting as process 3578 ... 2017-04-19T14:50:18.798104Z 0 [ERROR] Can't find error-message file '/mysql/mysql5.7share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive. 2017-04-19T14:50:18.802586Z 0 [Note] InnoDB: PUNCH HOLE support not available 2017-04-19T14:50:18.802645Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins 2017-04-19T14:50:18.802656Z 0 [Note] InnoDB: Uses event mutexes 2017-04-19T14:50:18.802665Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier 2017-04-19T14:50:18.802674Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3 2017-04-19T14:50:18.802682Z 0 [Note] InnoDB: Using Linux native AIO 2017-04-19T14:50:18.803153Z 0 [Note] InnoDB: Number of pools: 1 2017-04-19T14:50:18.803868Z 0 [Note] InnoDB: Using CPU crc32 instructions 2017-04-19T14:50:18.843406Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M 2017-04-19T14:50:18.860507Z 0 [Note] InnoDB: Completed initialization of buffer pool 2017-04-19T14:50:18.864349Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority(). 2017-04-19T14:50:18.878937Z 0 [Note] InnoDB: Highest supported file format is Barracuda. 2017-04-19T14:50:18.901395Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables 2017-04-19T14:50:18.901629Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ... 2017-04-19T14:50:18.943961Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB. 2017-04-19T14:50:18.945376Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active. 2017-04-19T14:50:18.945403Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active. 2017-04-19T14:50:18.952368Z 0 [Note] InnoDB: 5.7.9 started; log sequence number 2471242 2017-04-19T14:50:18.974882Z 0 [Note] InnoDB: Loading buffer pool(s) from /mysql/mysql5.7/data/ib_buffer_pool 2017-04-19T14:50:18.976665Z 0 [Note] InnoDB: Buffer pool(s) load completed at 151119 6:50:18 2017-04-19T14:50:18.978694Z 0 [Note] InnoDB: not started 2017-04-19T14:50:18.978878Z 0 [Note] Plugin 'FEDERATED' is disabled. 2017-04-19T14:50:18.983412Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them. 2017-04-19T14:50:18.984289Z 0 [Warning] CA certificate ca.pem is self signed. 2017-04-19T14:50:19.003696Z 0 [Note] Server hostname (bind-address): '*'; port: 3306 2017-04-19T14:50:19.021246Z 0 [Note] IPv6 is available. 2017-04-19T14:50:19.021291Z 0 [Note] - '::' resolves to '::'; 2017-04-19T14:50:19.021299Z 0 [Note] Server socket created on IP: '::'. 2017-04-19T14:50:19.037949Z 0 [ERROR] Could not create unix socket lock file /var/lib/mysql/mysql.sock.lock. 2017-04-19T14:50:19.037976Z 0 [ERROR] Unable to setup unix socket lock file. 2017-04-19T14:50:19.037982Z 0 [ERROR] Aborting 2017-04-19T14:50:19.038004Z 0 [Note] Binlog end 2017-04-19T14:50:19.038084Z 0 [Note] Shutting down plugin 'ngram' 2017-04-19T14:50:19.038094Z 0 [Note] Shutting down plugin 'ARCHIVE' 2017-04-19T14:50:19.038098Z 0 [Note] Shutting down plugin 'partition' 2017-04-19T14:50:19.038102Z 0 [Note] Shutting down plugin 'BLACKHOLE' 2017-04-19T14:50:19.038106Z 0 [Note] Shutting down plugin 'CSV' 2017-04-19T14:50:19.038112Z 0 [Note] Shutting down plugin 'MEMORY' 2017-04-19T14:50:19.038118Z 0 [Note] Shutting down plugin 'MRG_MYISAM' 2017-04-19T14:50:19.038124Z 0 [Note] Shutting down plugin 'INNODB_SYS_VIRTUAL' 2017-04-19T14:50:19.038128Z 0 [Note] Shutting down plugin 'INNODB_SYS_DATAFILES' 2017-04-19T14:50:19.038131Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESPACES' 2017-04-19T14:50:19.038135Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN_COLS' 2017-04-19T14:50:19.038138Z 0 [Note] Shutting down plugin 'INNODB_SYS_FOREIGN' 2017-04-19T14:50:19.038142Z 0 [Note] Shutting down plugin 'INNODB_SYS_FIELDS' 2017-04-19T14:50:19.038145Z 0 [Note] Shutting down plugin 'INNODB_SYS_COLUMNS' 2017-04-19T14:50:19.038149Z 0 [Note] Shutting down plugin 'INNODB_SYS_INDEXES' 2017-04-19T14:50:19.038152Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLESTATS' 2017-04-19T14:50:19.038155Z 0 [Note] Shutting down plugin 'INNODB_SYS_TABLES' 2017-04-19T14:50:19.038159Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_TABLE' 2017-04-19T14:50:19.038162Z 0 [Note] Shutting down plugin 'INNODB_FT_INDEX_CACHE' 2017-04-19T14:50:19.038165Z 0 [Note] Shutting down plugin 'INNODB_FT_CONFIG' 2017-04-19T14:50:19.038168Z 0 [Note] Shutting down plugin 'INNODB_FT_BEING_DELETED' 2017-04-19T14:50:19.038172Z 0 [Note] Shutting down plugin 'INNODB_FT_DELETED' 2017-04-19T14:50:19.038175Z 0 [Note] Shutting down plugin 'INNODB_FT_DEFAULT_STOPWORD' 2017-04-19T14:50:19.038179Z 0 [Note] Shutting down plugin 'INNODB_METRICS' 2017-04-19T14:50:19.038182Z 0 [Note] Shutting down plugin 'INNODB_TEMP_TABLE_INFO' 2017-04-19T14:50:19.038186Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_POOL_STATS' 2017-04-19T14:50:19.038189Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE_LRU' 2017-04-19T14:50:19.038192Z 0 [Note] Shutting down plugin 'INNODB_BUFFER_PAGE' 2017-04-19T14:50:19.038196Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX_RESET' 2017-04-19T14:50:19.038199Z 0 [Note] Shutting down plugin 'INNODB_CMP_PER_INDEX' 2017-04-19T14:50:19.038203Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM_RESET' 2017-04-19T14:50:19.038206Z 0 [Note] Shutting down plugin 'INNODB_CMPMEM' 2017-04-19T14:50:19.038210Z 0 [Note] Shutting down plugin 'INNODB_CMP_RESET' 2017-04-19T14:50:19.038213Z 0 [Note] Shutting down plugin 'INNODB_CMP' 2017-04-19T14:50:19.038217Z 0 [Note] Shutting down plugin 'INNODB_LOCK_WAITS' 2017-04-19T14:50:19.038221Z 0 [Note] Shutting down plugin 'INNODB_LOCKS' 2017-04-19T14:50:19.038224Z 0 [Note] Shutting down plugin 'INNODB_TRX' 2017-04-19T14:50:19.038228Z 0 [Note] Shutting down plugin 'InnoDB' 2017-04-19T14:50:19.038317Z 0 [Note] InnoDB: FTS optimize thread exiting. 2017-04-19T14:50:19.038421Z 0 [Note] InnoDB: Starting shutdown... 2017-04-19T14:50:19.151526Z 0 [Note] InnoDB: Dumping buffer pool(s) to /mysql/mysql5.7/data/ib_buffer_pool 2017-04-19T14:50:19.152515Z 0 [Note] InnoDB: Buffer pool(s) dump completed at 151119 6:50:19 2017-04-19T14:50:20.802442Z 0 [Note] InnoDB: Shutdown completed; log sequence number 2471261 2017-04-19T14:50:20.811473Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1" 2017-04-19T14:50:20.811528Z 0 [Note] Shutting down plugin 'PERFORMANCE_SCHEMA' 2017-04-19T14:50:20.811690Z 0 [Note] Shutting down plugin 'MyISAM' 2017-04-19T14:50:20.811730Z 0 [Note] Shutting down plugin 'sha256_password' 2017-04-19T14:50:20.811739Z 0 [Note] Shutting down plugin 'mysql_native_password' 2017-04-19T14:50:20.812061Z 0 [Note] Shutting down plugin 'binlog' 2017-04-19T14:50:20.813161Z 0 [Note] 在mysql5.7.17的安装过程中，初始化参数使用了--initialize-insecure，这样不会设置初始化root密码，如果是 --initialize的话，会随机生成一个密码，这个密码显示在安装过程最后 2017-04-19T14:50:20.813161Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 2800d0e1-a3ec-11e7-a082-005056bb44b1. 2017-04-19T14:50:20.813161Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2017-04-19T14:50:20.813161Z 1 [Note] A temporary password is generated for root@localhost: VfAG=aal=8;/ 四、配置文件 [root@localhost ~]# cd /mysql/mysql5.7/support-files/ [root@localhost support-files]# cp my-default.cnf /etc/my.cnf cp: overwrite `/etc/my.cnf'? yes [root@localhost support-files]# cp mysql.server /etc/init.d/mysql [root@localhost ~]# vim /etc/init.d/mysql basedir=/mysql/mysql5.7 datadir=/mysql/mysql5.7/data 五、配置环境变量 [mysql@localhost ~]$ vi .bash_profile PATH=$PATH:$HOME/bin # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs export MYSQL_HOME="/mysql/mysql5.7" export PATH="$PATH:$MYSQL_HOME/bin" ~ 六、添加开机启动服务 chkconfig --add mysql chkconfig mysql on 七、启动mysql [root@localhost support-files]# su - mysql 报错1 [mysql@localhost ~]$ service mysql start /etc/init.d/mysql: line 256: my_print_defaults: command not found Starting MySQL ERROR! Couldn't find MySQL server (/mysql/mysql5.7/bin/mysqld_safe) 启动报错了，原来是编辑/etc/init.d/mysql 文件出错了，修改为下面 vim /etc/init.d/mysql basedir=/mysql/mysql5.7 datadir=/mysql/mysql5.7/data [mysql@localhost ~]$ service mysql start Starting MySQL. SUCCESS! 再次启动成功 报错2 [mysql@goldqua1 ~]$ service mysqld start Starting MySQL.2017-04-19T02:28:36.399450Z mysqld_safe The file /usr/local/mysql/bin/mysqld does not exist or is not executable. Please cd to the mysql installation directory and restart this script from there as follows: ./bin/mysqld_safe& See http://dev.mysql.com/doc/mysql/en/mysqld-safe.html for more information The server quit without updating PID file (/mysql/mysql5.7/data/goldqua1.pid).[FAILED] 说明：mysqld_safe启动脚本默认的从/usr/local/mysql目录中读取另外一个启动脚本mysqld，因为我的安装目录为/mysql/mysql5.7product/5.6/mysql-1。所以找不到相关文件。可以从两个方面解决。 方法一：在/usr/local/mysql创建链接文件 1.mkdir -p /usr/local/mysql/bin 2.ln -s /mysql/mysql5.7/bin/mysqld /usr/local/mysql/bin/mysqld 方法二：将mysqld_safe中的所有/usr/local/mysql目录改为自己实际的安装目录 1.<pre name="code" class="sql">sed -i 's#/usr/local/mysql#/mysql/mysql5.7product/5.6/mysql-1#g' /mysql/mysql5.7product/5.6/mysql-1/bin/mysqld_safe 再次启动成功。 报错3 mysql> show databases; ERROR 1820 (HY000): Unknown error 1820 mysql> use mysql; ERROR 1820 (HY000): Unknown error 1820 mysql> show databases; ERROR 1820 (HY000): Unknown error 1820 这里，不管怎么执行都会报错，挺奇怪的，后来发现需要首先执行密码修改 mysql> set password=password('(Kl1b4X*'); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) 初始密码： [root@localhost bin]# cat /root/.mysql_secret # Password set for user 'root@localhost' at 2016-06-01 15:23:25 ,xxxxxR5H9 [root@localhost bin]# ./mysql -uroot -p 八、网络上出现的故障处理方法： 1、故障现象 [root@mysqltest mysqldata]# mysql -uroot -p123456 Warning: Using a password on the command line interface can be insecure. ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/data/mysqldata/mysql.sock' (111) #故障环境 [root@mysqltest mysqldata]# more /etc/issue CentOS release 5.9 (Final) Kernel r on an m 2、故障分析 #查看mysql实例的状态 [root@mysqltest mysqldata]# netstat -ntlp | grep 3306 tcp 0 0 :::3306 :::* LISTEN 13001/mysqld #查看my.cnf关于socket的配置 [root@mysqltest mysqldata]# more /etc/my.cnf |grep sock socket = /tmp/mysql.sock #由上可知my.cnf中定义的为/tmp目录下，而错误提示为/data/mysqldata/目录下 #也就是说mysqld已经声称了正确的sock文件，但客户端连接还是从初始目录去找sock文件 #下面查看后台日志，有个ERROR，是关于满查询日志的，是由于目录不存在而产生的错误，与当前故障无关 [root@mysqltest mysqldata]# more mysqltest.err ............ 2014-10-11 13:17:21 13001 [Note] InnoDB: 5.6.12 started; log sequence number 1625997 /app/soft/mysql/bin/mysqld: File '/log/mysql_logs/slowquery.log' not found (Errcode: 2 - No such file or directory) 2014-10-11 13:17:21 13001 [ERROR] Could not use /log/mysql_logs/slowquery.log for logging (error 2). Turning logging off for the who le duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it. 2014-10-11 13:17:21 13001 [Note] Server hostname (bind-address): '*'; port: 3306 2014-10-11 13:17:21 13001 [Note] IPv6 is available. 2014-10-11 13:17:21 13001 [Note] - '::' resolves to '::'; 2014-10-11 13:17:21 13001 [Note] Server socket created on IP: '::'. 2014-10-11 13:17:21 13001 [Note] Event Scheduler: Loaded 0 events 2014-10-11 13:17:21 13001 [Note] /app/soft/mysql/bin/mysqld: ready for connections. Version: '5.6.12-log' socket: '/tmp/mysql.sock' port: 3306 Source distribution 3、解决故障 a、通过配置my.cnf mysql选项socket文件位置解决 #先停止mysql服务器 [root@mysqltest mysqldata]# service mysqld stop Shutting down MySQL.[ OK ] #修改my.cnf，如下 [root@mysqltest mysqldata]# vi /etc/my.cnf [mysql] no-auto-rehash socket = /tmp/mysql.sock #添加该行 #重启mysql服务器 [root@mysqltest mysqldata]# service mysqld start Starting MySQL..[ OK ] #再次连接正常 [root@mysqltest mysqldata]# mysql -uroot -p Enter password: mysql> show variables like 'version'; +---------------+------------+ | Variable_name | Value | +---------------+------------+ | version | 5.6.12-log | +---------------+------------+ b、为socket文件建立链接方式 [root@mysqltest mysqldata]# ln -s /tmp/mysql.sock /data/mysqldata/mysql.sock ln: creating symbolic link `/data/mysqldata/mysql.sock' to `/tmp/mysql.sock': File exists [root@mysqltest mysqldata]# rm mysql.sock #上面提示文件存在，所以删除之前的mysql.sock文件 [root@mysqltest mysqldata]# ln -s /tmp/mysql.sock /data/mysqldata/mysql.sock [root@mysqltest mysqldata]# ls -hltr mysql.sock lrwxrwxrwx 1 root root 15 Oct 11 14:00 mysql.sock -> /tmp/mysql.sock [root@mysqltest mysqldata]# mysql -uroot -p Enter password: mysql> show variables like 'socket'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | socket | /tmp/mysql.sock | +---------------+-----------------+ 九、mysql undo空间说明 [ERROR] InnoDB: Expected to open 4 undo tablespaces but was able to find only 0 undo tablespaces. Set the innodb_undo_tablespaces parameter to the correct value and retry. Suggested value is 0 报错原因： 在初始化数据库的时候没有指定innodb_undo_tablespaces参数 解决方法： 在初始化数据库的时候写上--defaults-file参数，在对应的配置文件中写上对应的innodb_undo_tablespaces参数 [mysql@localhost mysql_data]$ vi /tmp/my.cnf ##InnoDB engine undo log related setting innodb_undo_directory = /u01/mysql_data innodb_undo_tablespaces = 4 [mysql@localhost percona]$ bin/mysqld --defaults-file=/tmp/my.cnf --user=mysql --initialize-insecure 注意初始化数据库语句的写法，将--defaults-file放到语句最后会导致配置文件中的参数不会生效 十、参数说明 # MySql5.7配置文件my.cnf设置 [client] port = 3306 socket = /tmp/mysql.sock [mysqld] ###############################基础设置##################################### #Mysql服务的唯一编号 每个mysql服务Id需唯一 server-id = 1 #服务端口号 默认3306 port = 3306 #mysql安装根目录 basedir = /opt/mysql #mysql数据文件所在位置 datadir = /opt/mysql/data #临时目录 比如load data infile会用到 tmpdir = /tmp #设置socke文件所在目录 socket = /tmp/mysql.sock #主要用于MyISAM存储引擎,如果多台服务器连接一个数据库则建议注释下面内容 skip-external-locking #只能用IP地址检查客户端的登录，不用主机名 skip_name_resolve = 1 #事务隔离级别，默认为可重复读，mysql默认可重复读级别（此级别下可能参数很多间隙锁，影响性能） transaction_isolation = READ-COMMITTED #数据库默认字符集,主流字符集支持一些特殊表情符号（特殊表情符占用4个字节） character-set-server = utf8mb4 #数据库字符集对应一些排序等规则，注意要和character-set-server对应 collation-server = utf8mb4_general_ci #设置client连接mysql时的字符集,防止乱码 init_connect='SET NAMES utf8mb4' #是否对sql语句大小写敏感，1表示不敏感 lower_case_table_names = 1 #最大连接数 max_connections = 400 #最大错误连接数 max_connect_errors = 1000 #TIMESTAMP如果没有显示声明NOT NULL，允许NULL值 explicit_defaults_for_timestamp = true #SQL数据包发送的大小，如果有BLOB对象建议修改成1G max_allowed_packet = 128M #MySQL连接闲置超过一定时间后(单位：秒)将会被强行关闭 #MySQL默认的wait_timeout 值为8个小时, interactive_timeout参数需要同时配置才能生效 interactive_timeout = 1800 wait_timeout = 1800 #内部内存临时表的最大值 ，设置成128M。 #比如大数据量的group by ,order by时可能用到临时表， #超过了这个值将写入磁盘，系统IO压力增大 tmp_table_size = 134217728 max_heap_table_size = 134217728 #禁用mysql的缓存查询结果集功能 #后期根据业务情况测试决定是否开启 #大部分情况下关闭下面两项 query_cache_size = 0 query_cache_type = 0 ##----------------------------用户进程分配到的内存设置BEGIN-----------------------------## ##每个session将会分配参数设置的内存大小 #用于表的顺序扫描，读出的数据暂存于read_buffer_size中，当buff满时或读完，将数据返回上层调用者 #一般在128kb ~ 256kb,用于MyISAM #read_buffer_size = 131072 #用于表的随机读取，当按照一个非索引字段排序读取时会用到， #一般在128kb ~ 256kb,用于MyISAM #read_rnd_buffer_size = 262144 #order by或group by时用到 #建议先调整为2M，后期观察调整 sort_buffer_size = 2097152 #一般数据库中没什么大的事务，设成1~2M，默认32kb binlog_cache_size = 524288 ##---------------------------用户进程分配到的内存设置END-------------------------------## #在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中 #官方建议back_log = 50 + (max_connections / 5),封顶数为900 back_log = 130 ############################日 志 设置########################################## #数据库错误日志文件 log_error = error.log #慢查询sql日志设置 slow_query_log = 1 slow_query_log_file = slow.log #检查未使用到索引的sql log_queries_not_using_indexes = 1 #针对log_queries_not_using_indexes开启后，记录慢sql的频次、每分钟记录的条数 log_throttle_queries_not_using_indexes = 5 #作为从库时生效,从库复制中如何有慢sql也将被记录 log_slow_slave_statements = 1 #慢查询执行的秒数，必须达到此值可被记录 long_query_time = 8 #检索的行数必须达到此值才可被记为慢查询 min_examined_row_limit = 100 #mysql binlog日志文件保存的过期时间，过期后自动删除 expire_logs_days = 5 ############################主从复制 设置######################################## #开启mysql binlog功能 log-bin=mysql-bin #binlog记录内容的方式，记录被操作的每一行 binlog_format = ROW #对于binlog_format = ROW模式时，减少记录日志的内容，只记录受影响的列 binlog_row_image = minimal #master status and connection information输出到表mysql.slave_master_info中 master_info_repository = TABLE #the slave's position in the relay logs输出到表mysql.slave_relay_log_info中 relay_log_info_repository = TABLE #作为从库时生效,想进行级联复制，则需要此参数 log_slave_updates #作为从库时生效,中继日志relay-log可以自我修复 relay_log_recovery = 1 #作为从库时生效,主从复制时忽略的错误 slave_skip_errors = ddl_exist_errors ##---redo log和binlog的关系设置BEGIN---## #(步骤1) prepare dml相关的SQL操作，然后将redo log buff中的缓存持久化到磁盘 #(步骤2)如果前面prepare成功，那么再继续将事务日志持久化到binlog #(步骤3)如果前面成功，那么在redo log里面写上一个commit记录 #当innodb_flush_log_at_trx_commit和sync_binlog都为1时是最安全的， #在mysqld服务崩溃或者服务器主机crash的情况下，binary log只有可能丢失最多一个语句或者一个事务。 #但是都设置为1时会导致频繁的io操作，因此该模式也是最慢的一种方式。 #当innodb_flush_log_at_trx_commit设置为0，mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。 #当innodb_flush_log_at_trx_commit设置为2，只有在操作系统崩溃或者系统掉电的情况下，上一秒钟所有事务数据才可能丢失。 #commit事务时,控制redo log buff持久化磁盘的模式 默认为1 innodb_flush_log_at_trx_commit = 2 #commit事务时,控制写入mysql binlog日志的模式 默认为 0 #innodb_flush_log_at_trx_commit和sync_binlog都为1时，mysql最为安全但性能上压力也是最大 sync_binlog = 1 ##---redo log 和 binlog的关系设置END---## ############################Innodb设置########################################## #数据块的单位8k，默认是16k，16kCPU压力稍小，8k对select的吞吐量大 #innodb_page_size的参数值也影响最大索引长度，8k比16k的最大索引长度小 #innodb_page_size = 8192 #一般设置物理存储的60% ~ 70% innodb_buffer_pool_size = 1G #5.7.6之后默认16M #innodb_log_buffer_size = 16777216 #该参数针对unix、linux，window上直接注释该参数.默认值为NULL #O_DIRECT减少操作系统级别VFS的缓存和Innodb本身的buffer缓存之间的冲突 innodb_flush_method = O_DIRECT #此格式支持压缩, 5.7.7之后为默认值 innodb_file_format = Barracuda #CPU多核处理能力设置，假设CPU是2颗4核的，设置如下 #读多，写少可以设成2:6的比例 innodb_write_io_threads = 4 innodb_read_io_threads = 4 #提高刷新脏页数量和合并插入数量，改善磁盘I/O处理能力 #默认值200（单位：页） #可根据磁盘近期的IOPS确定该值 innodb_io_capacity = 500 #为了获取被锁定的资源最大等待时间，默认50秒，超过该时间会报如下错误: # ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction innodb_lock_wait_timeout = 30 #调整buffer pool中最近使用的页读取并dump的百分比,通过设置该参数可以减少转储的page数 innodb_buffer_pool_dump_pct = 40 #设置redoLog文件所在目录, redoLog记录事务具体操作内容 innodb_log_group_home_dir = /opt/mysql/redolog/ #设置undoLog文件所在目录, undoLog用于事务回滚操作 innodb_undo_directory = /opt/mysql/undolog/ #在innodb_log_group_home_dir中的redoLog文件数, redoLog文件内容是循环覆盖写入。 innodb_log_files_in_group = 3 #MySql5.7官方建议尽量设置的大些，可以接近innodb_buffer_pool_size的大小 #之前设置该值较大时可能导致mysql宕机恢复时间过长，现在恢复已经加快很多了 #该值减少脏数据刷新到磁盘的频次 #最大值innodb_log_file_size * innodb_log_files_in_group <= 512GB,单文件<=256GB innodb_log_file_size = 1024M #设置undoLog文件所占空间可以回收 #5.7之前的MySql的undoLog文件一直增大无法回收 innodb_undo_log_truncate = 1 innodb_undo_tablespaces = 3 innodb_undo_logs = 128 #5.7.7默认开启该参数 控制单列索引长度最大达到3072 #innodb_large_prefix = 1 #5.7.8默认为4个, Inodb后台清理工作的线程数 #innodb_purge_threads = 4 #通过设置配置参数innodb_thread_concurrency来限制并发线程的数量， #一旦执行线程的数量达到这个限制，额外的线程在被放置到对队列中之前，会睡眠数微秒， #可以通过设定参数innodb_thread_sleep_delay来配置睡眠时间 #该值默认为0,在官方doc上，对于innodb_thread_concurrency的使用，也给出了一些建议: #(1)如果一个工作负载中，并发用户线程的数量小于64，建议设置innodb_thread_concurrency=0； #(2)如果工作负载一直较为严重甚至偶尔达到顶峰，建议先设置innodb_thread_concurrency=128, ###并通过不断的降低这个参数，96, 80, 64等等，直到发现能够提供最佳性能的线程数 #innodb_thread_concurrency = 0 #强所有发生的死锁错误信息记录到error.log中，之前通过命令行只能查看最近一次死锁信息 innodb_print_all_deadlocks = 1 ############################其他内容 设置########################################## [mysqldump] quick max_allowed_packet = 128M [mysql] no-auto-rehash [myisamchk] key_buffer_size = 20M sort_buffer_size = 256k read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [mysqld_safe] #增加每个进程的可打开文件数量. open-files-limit = 28192 （编辑：晋中站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
