新的一年開(kāi)始了，項(xiàng)目上需要重新配置需要的生產(chǎn)環(huán)境，第一個(gè)想到的就是mysql精算，因?yàn)樗许?xiàng)目都在使用，所以全面了解mysql的配置尤為重要碎连，特地再查了許多資料之后重新歸檔整理了這篇文檔灰羽，希望對(duì)看到這篇文章的讀者有幫助。
環(huán)境介紹
這篇文章記錄的是在linux環(huán)境下安裝的mysql破花，環(huán)境是云服務(wù)+Centos
準(zhǔn)備
在安裝mysql之前需要首先去安裝一下yum
請(qǐng)參考另一篇文章----Yum安裝
安裝mysql倉(cāng)庫(kù)
因?yàn)槭褂脃um無(wú)法直接安裝mysql官網(wǎng)上的版本谦趣，所以需要先到安裝mysql官方的倉(cāng)庫(kù)
這里我選用的是最新的地址：
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服務(wù)
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
查看臨時(shí)密碼
grep 'temporary password' /var/log/mysqld.log
啟動(dòng)mysql服務(wù)
systemctl start mysqld
使用臨時(shí)密碼登錄
mysql -u root -p
修改密碼
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Semi&1001';
配置遠(yuǎn)程數(shù)據(jù)庫(kù)訪問(wèn)
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'NEW PASSWORD' WITH GRANT OPTION;
FLUSH PRIVILEGES;
更改數(shù)據(jù)庫(kù)存放地址
新建數(shù)據(jù)庫(kù)要存放的文件夾
cd /mnt
mkdir data
cd data
停止mysql服務(wù)
systemctl stop mysqld
移動(dòng)文件夾
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
查看是否是開(kāi)機(jī)啟動(dòng)
systemctl list-unit-files | grep mysql
mysqld.service enabled
mysqld@.service disabled
設(shè)置開(kāi)機(jī)啟動(dòng)
systemctl enbale mysqld
取消開(kāi)機(jī)啟動(dòng)
systemctl disable mysqld
常見(jiàn)錯(cuò)誤
錯(cuò)誤:Packet for query is too large (12238 > 1024). You can change this value
解決方案
mysql max_allowed_packet 設(shè)置過(guò)小導(dǎo)致記錄寫(xiě)入失敗
mysql根據(jù)配置文件會(huì)限制server接受的數(shù)據(jù)包大小
有時(shí)候大的插入和更新會(huì)受max_allowed_packet 參數(shù)限制，導(dǎo)致寫(xiě)入或者更新失敗座每。
查看目前配置
show VARIABLES like '%max_allowed_packet%';
顯示的結(jié)果為：
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
以上說(shuō)明目前的配置是：1M
修改方法
1前鹅、修改配置文件
可以編輯my.cnf來(lái)修改（windows下my.ini）,在[mysqld]段或者mysql的server配置段進(jìn)行修改。
max_allowed_packet = 20M
如果找不到my.cnf可以通過(guò)
mysql --help | grep my.cnf
去尋找my.cnf文件
linux下該文件在/etc/下峭梳。
2舰绘、在mysql命令行中修改
在mysql 命令行中運(yùn)行
set global max_allowed_packet = 2*1024*1024*10
然后退出命令行蹂喻，重啟mysql服務(wù)，再進(jìn)入捂寿。
show VARIABLES like '%max_allowed_packet%';
查看下max_allowed_packet是否編輯成功
注意：該值設(shè)置過(guò)小將導(dǎo)致單個(gè)記錄超過(guò)限制后寫(xiě)入數(shù)據(jù)庫(kù)失敗口四，且后續(xù)記錄寫(xiě)入也將失敗。
3秦陋、重啟Linux的mysql
使用 service 啟動(dòng)：service mysqld restart
使用 mysqld 腳本啟動(dòng)：/etc/inint.d/mysqld restart
