新的一年开始了，项目上需要重新配置需要的生产环境，第一个想到的就是mysql，因为所有项目都在使用，所以全面了解mysql的配置尤为重要，特地再查了许多资料之后重新归档整理了这篇文档，希望对看到这篇文章的读者有帮助。
环境介绍
这篇文章记录的是在linux环境下安装的mysql，环境是云服务+Centos
准备
在安装mysql之前需要首先去安装一下yum
请参考另一篇文章----Yum安装
安装mysql仓库
因为使用yum无法直接安装mysql官网上的版本，所以需要先到安装mysql官方的仓库
这里我选用的是最新的地址：
http://repo.mysql.com/mysql57-community-release-el7.rpm
先下载：
wget http://repo.mysql.com/mysql57-community-release-el7.rpm
使用yum安装
yum install mysql57-community-release-el7.rpm
查看是否可用
yum repolist all | grep mysql
安装mysql服务
yum install mysql-community-server
[root@semi ~]# yum repolist all | grep mysql
mysql-cluster-7.5-community/x86_64 MySQL Cluster 7.5 Community disabled
mysql-cluster-7.5-community-source MySQL Cluster 7.5 Community - disabled
mysql-cluster-7.6-community/x86_64 MySQL Cluster 7.6 Community disabled
mysql-cluster-7.6-community-source MySQL Cluster 7.6 Community - disabled
mysql-connectors-community/x86_64 MySQL Connectors Community enabled: 95
mysql-connectors-community-source MySQL Connectors Community - disabled
mysql-tools-community/x86_64 MySQL Tools Community enabled: 84
mysql-tools-community-source MySQL Tools Community - Sourc disabled
mysql-tools-preview/x86_64 MySQL Tools Preview disabled
mysql-tools-preview-source MySQL Tools Preview - Source disabled
mysql55-community/x86_64 MySQL 5.5 Community Server disabled
mysql55-community-source MySQL 5.5 Community Server - disabled
mysql56-community/x86_64 MySQL 5.6 Community Server disabled
mysql56-community-source MySQL 5.6 Community Server - disabled
mysql57-community/x86_64 MySQL 5.7 Community Server enabled: 327
mysql57-community-source MySQL 5.7 Community Server - disabled
mysql80-community/x86_64 MySQL 8.0 Community Server disabled
mysql80-community-source MySQL 8.0 Community Server - disabled
查看临时密码
grep 'temporary password' /var/log/mysqld.log
启动mysql服务
systemctl start mysqld
使用临时密码登录
mysql -u root -p
修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Semi&1001';
配置远程数据库访问
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'NEW PASSWORD' WITH GRANT OPTION;
FLUSH PRIVILEGES;
更改数据库存放地址
新建数据库要存放的文件夹
cd /mnt
mkdir data
cd data
停止mysql服务
systemctl stop mysqld
移动文件夹
mv /var/lib/mysql /mnt/data
修改配置文件
vi /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/mnt/data/mysql
socket=/mnt/data/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysql]
socket=/mnt/data/mysql.sock
查看是否是开机启动
systemctl list-unit-files | grep mysql
mysqld.service enabled
mysqld@.service disabled
设置开机启动
systemctl enbale mysqld
取消开机启动
systemctl disable mysqld
常见错误
错误:Packet for query is too large (12238 > 1024). You can change this value
解决方案
mysql max_allowed_packet 设置过小导致记录写入失败
mysql根据配置文件会限制server接受的数据包大小
有时候大的插入和更新会受max_allowed_packet 参数限制，导致写入或者更新失败。
查看目前配置
show VARIABLES like '%max_allowed_packet%';
显示的结果为：
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
以上说明目前的配置是：1M
修改方法
1、修改配置文件
可以编辑my.cnf来修改（windows下my.ini）,在[mysqld]段或者mysql的server配置段进行修改。
max_allowed_packet = 20M
如果找不到my.cnf可以通过
mysql --help | grep my.cnf
去寻找my.cnf文件
linux下该文件在/etc/下。
2、在mysql命令行中修改
在mysql 命令行中运行
set global max_allowed_packet = 2*1024*1024*10
然后退出命令行，重启mysql服务，再进入。
show VARIABLES like '%max_allowed_packet%';
查看下max_allowed_packet是否编辑成功
注意：该值设置过小将导致单个记录超过限制后写入数据库失败，且后续记录写入也将失败。
3、重启Linux的mysql
使用 service 启动：service mysqld restart
使用 mysqld 脚本启动：/etc/inint.d/mysqld restart
网友评论