新的一年開始了，項(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無法直接安裝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ù)訪問
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
查看是否是開機(jī)啟動(dòng)
systemctl list-unit-files | grep mysql
mysqld.service enabled
mysqld@.service disabled
設(shè)置開機(jī)啟動(dòng)
systemctl enbale mysqld
取消開機(jī)啟動(dòng)
systemctl disable mysqld
常見錯(cuò)誤
錯(cuò)誤:Packet for query is too large (12238 > 1024). You can change this value
解決方案
mysql max_allowed_packet 設(shè)置過小導(dǎo)致記錄寫入失敗
mysql根據(jù)配置文件會(huì)限制server接受的數(shù)據(jù)包大小
有時(shí)候大的插入和更新會(huì)受max_allowed_packet 參數(shù)限制，導(dǎo)致寫入或者更新失敗。
查看目前配置
show VARIABLES like '%max_allowed_packet%';
顯示的結(jié)果為：
+--------------------+---------+
| Variable_name | Value |
+--------------------+---------+
| max_allowed_packet | 1048576 |
+--------------------+---------+
以上說明目前的配置是：1M
修改方法
1、修改配置文件
可以編輯my.cnf來修改（windows下my.ini）,在[mysqld]段或者mysql的server配置段進(jìn)行修改。
max_allowed_packet = 20M
如果找不到my.cnf可以通過
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è)置過小將導(dǎo)致單個(gè)記錄超過限制后寫入數(shù)據(jù)庫(kù)失敗，且后續(xù)記錄寫入也將失敗。
3、重啟Linux的mysql
使用 service 啟動(dòng)：service mysqld restart
使用 mysqld 腳本啟動(dòng)：/etc/inint.d/mysqld restart
