目錄
- 一.前言
- 二.關于MySQL
- 三.MySQL安裝篇
- 3.1 部署環境
- 3.2 前期準備工具
- 3.3 掛載系統ISO鏡像，配置yum源
- 3.4 安裝mysql編譯所需的依賴包
- 3.5 原始碼編譯安裝mysql5.7
- 3.6 配置mysql
- 四.MySQL調優篇
- 4.1 對MySQL進行安全設定
- 4.2 設定MySQL超時時間
- 4.3 MySQL組態檔優化參考1(按需配置即可)
- 4.4 MySQL組態檔優化參考2（按需配置即可）
- 五.參考資料
一.前言
? 根據安全性和保密性的要求，內網服務器是不能訪問網路的，所以只能離線安裝軟體，安裝MySQL的依賴包需要配置yum源，由于服務器架構的不同，考慮到兼容性，可定制性，以及為了提高MySQL運行性能，編譯安裝MySQL是最合適的方法，
二.關于MySQL
? MySQL是最流行的關系型資料庫管理系統之一，使用簡單，部署快速，功能全面，可以快速滿足中小型公司的資料存盤需求，
三.MySQL安裝篇
3.1 部署環境
| 服務器版本 | MySQL軟體版本 | CPU架構 |
|---|---|---|
| CentOS Linux release 7.4.1708 (Core) | MySQL 5.7 | x86_64 |
3.2 前期準備工具
- CentOS-7-x86_64-DVD-1708.iso #下載地址： http://mirror.unpad.ac.id/centos/7.4.1708/isos/x86_64/
- gcc-7.3.0.tar.gz #下載地址：https://mirrors.tuna.tsinghua.edu.cn/gnu/gcc/gcc-7.3.0/
- gmp-6.1.0.tar.bz2 #下載地址：https://gcc.gnu.org/pub/gcc/infrastructure/
- mpfr-3.1.4.tar.bz2 #下載地址：https://gcc.gnu.org/pub/gcc/infrastructure/
- mpc-1.0.3.tar.gz #下載地址：https://gcc.gnu.org/pub/gcc/infrastructure/
- isl-0.16.1.tar.bz2 #下載地址：https://gcc.gnu.org/pub/gcc/infrastructure/
- mysql-boost-5.7.22.tar.gz #下載地址為 https://dev.mysql.com/downloads/mysql/5.7.html#downloads
3.3 掛載系統ISO鏡像，配置yum源
由于內網無法訪問網路，所以需先掛載centos7的ISO鏡像，然后配置yum源安裝mysql5.7編譯所需要的依賴包
創建ISO鏡像存放目錄并上傳ISO鏡像檔案
[root@node8 ~]# mkdir /mnt/yum-iso
[root@node8 ~]# cd /mnt/
[root@node8 mnt]# cd yum-iso/
[root@node8 yum-iso]# mkdir /mnt/cdrom
[root@node8 yum-iso]# pwd
/mnt/yum-iso
使用xftp工具上傳ISO鏡像到/mnt/yum-iso目錄
掛載ISO鏡像到/mnt/cdrom目錄
[root@node8 yum-iso]# mount -o loop /mnt/yum-iso/CentOS-7-x86_64-DVD-1708.iso /mnt/cdrom #掛載系統鏡像
mount: /dev/loop0 is write-protected, mounting read-only
[root@node8 yum-iso]# df -h #查看磁盤
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/centos-root 17G 13G 4.3G 76% /
devtmpfs 478M 0 478M 0% /dev
tmpfs 489M 0 489M 0% /dev/shm
tmpfs 489M 6.7M 482M 2% /run
tmpfs 489M 0 489M 0% /sys/fs/cgroup
/dev/sda1 1014M 125M 890M 13% /boot
tmpfs 98M 0 98M 0% /run/user/0
/dev/loop0 4.3G 4.3G 0 100% /mnt/cdrom #顯示這一行說明已經鏡像已經掛載成功
配置yum源：
[root@node8 yum-iso]# cd /etc/yum.repos.d/
[root@node8 yum.repos.d]# mkdir yumbak
[root@node8 yum.repos.d]# mv *.repo yumbak/ #備份原有組態檔
[root@node8 yum.repos.d]# vim iso.repo
[root@node8 yum.repos.d]# cat iso.repo
[centos7-iso]
name=centos-iso
baseurl=file:///mnt/cdrom #此處寫鏡像掛載目錄
gpgcheck=0
enabled=1
清空yum快取：
[root@node8 yum.repos.d]# yum clean all
Loaded plugins: fastestmirror
Cleaning repos: centos7-iso
Cleaning up everything
Maybe you want: rm -rf /var/cache/yum, to also free up space taken by orphaned data from disabled or removed repos
Cleaning up list of fastest mirrors
[root@node8 yum.repos.d]#
使用yum list檢查是否設定成功！如果有輸出就表示yum源配置成功
[root@node9 yum.repos.d]# yum list
設定開機自動掛載iso鏡像檔案，在/etc/fstab的最后一行添加配置，將iso檔案永久掛載到系統中
[root@node9 yum.repos.d]# vim /etc/fstab
[root@node9 yum.repos.d]# cat /etc/fstab
# /etc/fstab
# Created by anaconda on Fri Apr 26 17:36:34 2019
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
/dev/mapper/centos-root / xfs defaults 0 0
UUID=c7de6c01-e93f-4907-b919-5989396f857b /boot xfs defaults 0 0
/dev/mapper/centos-swap swap swap defaults 0 0
/mnt/yum-iso/CentOS-7-x86_64-DVD-1708.iso /mnt/cdrom iso9660 loop 0 0 #這行是新增的,注意這里要寫正確，不然服務器開不了機
再次查看磁盤，確認系統鏡像是否掛載成功
[root@node9 yum.repos.d]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/centos-root 17G 7.5G 9.5G 45% /
devtmpfs 2.0G 0 2.0G 0% /dev
tmpfs 2.0G 0 2.0G 0% /dev/shm
tmpfs 2.0G 8.6M 2.0G 1% /run
tmpfs 2.0G 0 2.0G 0% /sys/fs/cgroup
/dev/sda1 1014M 125M 890M 13% /boot
tmpfs 394M 0 394M 0% /run/user/0
/dev/loop0 4.3G 4.3G 0 100% /mnt/cdrom
3.4 安裝mysql編譯所需的依賴包
安裝mysql基本依賴包依賴包
[root@node9 ~]# yum install bison* ncurses* -y
[root@node9 ~]# yum install -y bzip2 wget -y
升級gcc到7.3版本用于編譯MySQL，編譯安裝gcc原始碼包安裝gcc
[root@node9 yum.repos.d]# cd /home/
上傳gcc-7.3.0.tar.gz到/home目錄
[root@node9 home]# rz -E #rz命令上傳gcc原始碼包
rz waiting to receive.
[root@node9 home]# ls
gcc-7.3.0.tar.gz
解壓gcc原始碼包
[root@node9 home]# tar -xvf gcc-7.3.0.tar.gz #tar命令解壓gcc
[root@node9 home]# pwd
/home
[root@node9 home]# ls
gcc-7.3.0 gcc-7.3.0.tar.gz
[root@node9 gcc-7.3.0]# cd gcc-7.3.0
[root@node9 gcc-7.3.0]# pwd
/home/gcc-7.3.0
上傳gmp-6.1.0.tar.bz2,mpfr-3.1.4.tar.bz2,mpc-1.0.3.tar.gz,isl-0.16.1.tar.bz2到/home/gcc-7.3.0目錄下
[root@node9 gcc-7.3.0]# rz -E
rz waiting to receive.
[root@node9 gcc-7.3.0]# rz -E
rz waiting to receive.
[root@node9 gcc-7.3.0]# rz -E
rz waiting to receive.
[root@node9 gcc-7.3.0]# rz -E
rz waiting to receive.
[root@node9 gcc-7.3.0]# ls
ABOUT-NLS COPYING3 isl-0.16.1.tar.bz2 libhsail-rt ltmain.sh mkinstalldirs
ChangeLog COPYING3.LIB LAST_UPDATED libiberty lt~obsolete.m4 move-if-change
ChangeLog.jit COPYING.LIB libada libitm lto-plugin mpc-1.0.3.tar.gz
ChangeLog.tree-ssa COPYING.RUNTIME libatomic libmpx ltoptions.m4 mpfr-3.1.4.tar.bz2
compile depcomp libbacktrace libobjc ltsugar.m4 NEWS
config fixincludes libcc1 liboffloadmic ltversion.m4 README
config.guess gcc libcilkrts libquadmath MAINTAINERS symlink-tree
config-ml.in gmp-6.1.0.tar.bz2 libcpp libsanitizer maintainer-scripts ylwrap
config.rpath gnattools libdecnumber libssp Makefile.def zlib
config.sub gotools libffi libstdc++-v3 Makefile.in
configure include libgcc libtool-ldflags Makefile.tpl
configure.ac INSTALL libgfortran libtool.m4 MD5SUMS
contrib install-sh libgo libvtv missing
COPYING intl libgomp ltgcc.m4 mkdep
在“gcc-7.3.0”目錄下，檢查gcc的依賴包是否已下載和安裝，如果沒有，會自動下載和安裝，
[root@node9 gcc-7.3.0]# ./contrib/download_prerequisites
gmp-6.1.0.tar.bz2: OK
mpfr-3.1.4.tar.bz2: OK
mpc-1.0.3.tar.gz: OK
isl-0.16.1.tar.bz2: OK
All prerequisites downloaded successfully.#如果輸出為這樣，所以所以依賴都已滿足
使用configure命令檢查gcc編譯環境
[root@node9 gcc-7.3.0]# cd /home/gcc-7.3.0
[root@node9 gcc-7.3.0]# mkdir gcc-build-7.3.0
[root@node9 gcc-7.3.0]# cd gcc-build-7.3.0
[root@node9 gcc-build-7.3.0]# ../configure --enable-checking=release --enable-language=c,c++ --disable-multilib --prefix=/usr #--prefix=/usr是把gcc指定安裝在/usr目錄下
.......
....輸出內容過多，只列出如下...
checking where to find the target windmc... host tool
checking whether to enable maintainer-specific portions of Makefiles... no
configure: creating ./config.status
config.status: creating Makefile
[root@node9 gcc-build-7.3.0]# echo $? #輸出結果為0則說明gcc編譯環境滿足條件
0
使用make -j 4編譯gcc,4代表使用幾個執行緒來執行程式，如果是16核服務器則可以使用16
[root@node9 gcc-build-7.3.0]# make -j 4
[root@node9 gcc-build-7.3.0]# echo $? ##輸出結果為0則說明編譯成功
0
使用make install安裝gcc
[root@node9 gcc-build-7.3.0]# make install
[root@node9 gcc-build-7.3.0]# echo $? ##輸出結果為0則說明安裝成功
0
確定“libstdc++.so”是否在“/usr/lib64”目錄下
[root@node9 gcc-build-7.3.0]# cd /usr/lib64/
[root@node9 lib64]# ll | grep libstdc++.so
lrwxrwxrwx 1 root root 19 Nov 5 12:34 libstdc++.so -> libstdc++.so.6.0.24
lrwxrwxrwx 1 root root 19 Nov 5 12:34 libstdc++.so.6 -> libstdc++.so.6.0.24
-rwxr-xr-x. 1 root root 991616 Mar 14 2019 libstdc++.so.6.0.19
-rwxr-xr-x 1 root root 11515840 Nov 5 12:34 libstdc++.so.6.0.24
-rw-r--r-- 1 root root 2385 Nov 5 12:34 libstdc++.so.6.0.24-gdb.py
確定軟連接是否存在
[root@node9 lib64]# ll |grep libstdc++.so.6.0.24
lrwxrwxrwx 1 root root 19 Nov 5 12:34 libstdc++.so -> libstdc++.so.6.0.24
lrwxrwxrwx 1 root root 19 Nov 5 12:34 libstdc++.so.6 -> libstdc++.so.6.0.24
-rwxr-xr-x 1 root root 11515840 Nov 5 12:34 libstdc++.so.6.0.24
-rw-r--r-- 1 root root 2385 Nov 5 12:34 libstdc++.so.6.0.24-gdb.py
查看gcc版本
[root@node9 lib64]# gcc -v #輸出如下內容則說明gcc 7.3安裝成功
Using built-in specs.
COLLECT_GCC=gcc
COLLECT_LTO_WRAPPER=/usr/libexec/gcc/x86_64-pc-linux-gnu/7.3.0/lto-wrapper
Target: x86_64-pc-linux-gnu
Configured with: ../configure --enable-checking=release --enable-language=c,c++ --disable-multilib --prefix=/usr
Thread model: posix
gcc version 7.3.0 (GCC)
3.5 原始碼編譯安裝mysql5.7
上傳mysql-boost-5.7.22.tar.gz到/usr/local/src/目錄下
[root@node9 lib64]# cd /usr/local/src/
[root@node9 src]# pwd
/usr/local/src
[root@node9 src]# rz -E
rz waiting to receive.
[root@node9 src]# ls
mysql-boost-5.7.28.tar.gz
解壓mysql原始碼包
[root@node9 src]# tar -zxf mysql-boost-5.7.28.tar.gz
[root@node9 src]# ls
mysql-5.7.28 mysql-boost-5.7.28.tar.gz
下載mysql補丁檔案，并放置到/usr/local/src/mysql-5.7.28路徑下(這步其實可以省略)
補丁下載路徑：https://bugs.mysql.com/file.php?id=28180&bug_id=94699
[root@node9 src]# cd mysql-5.7.28/
[root@node9 mysql-5.7.28]# pwd
/usr/local/src/mysql-5.7.28
[root@node9 mysql-5.7.28]# rz -E
rz waiting to receive.
[root@node9 mysql-5.7.28]# ls
0001-Bug-94699-Mysql-deadlock-and-bugcheck-on-aarch64.patch
[root@node9 mysql-5.7.28]# pwd
/usr/local/src/mysql-5.7.28
打mysql補丁,如果沒有patch命令則需要安裝patch命令
[root@node9 mysql-5.7.28]# patch -p1 < 0001-Bug-94699-Mysql-deadlock-and-bugcheck-on-aarch64.patch
-bash: patch: command not found #出現這個提示說明patch沒有安裝
#安裝patch命令
[root@node9 mysql-5.7.28]# yum -y install patch
...........................
.....輸出太多只顯示如下.......
Installed:
patch.x86_64 0:2.7.1-8.el7
Complete!
開始打補丁
#打補丁：全部回車即可
[root@node9 mysql-5.7.28]# patch -p1 < 0001-Bug-94699-Mysql-deadlock-and-bugcheck-on-aarch64.patch
patching file storage/innobase/sync/sync0rw.cc
Reversed (or previously applied) patch detected! Assume -R? [n]
Apply anyway? [n]
Skipping patch.
2 out of 2 hunks ignored -- saving rejects to file storage/innobase/sync/sync0rw.cc.rej
創建mysql的cmake腳本
[root@node9 mysql-5.7.28]# pwd
/usr/local/src/mysql-5.7.28
[root@node9 mysql-5.7.28]# vim cmake.sh #由于mysql的編譯引數較多，寫到腳本里便于運行以及復用
[root@node9 mysql-5.7.28]# cat cmake.sh
#CMAKE_INSTALL_PREFIX路徑和WITH_BOOST路徑按照您的真實路徑填寫
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/data/data \
-DSYSCONFDIR=/etc \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DENABLE_DTRACE=0 \
-DDEFAULT_CHARSET=utf8mb4 \
-DDEFAULT_COLLATION=utf8mb4_general_ci \
-DWITH_EMBEDDED_SERVER=1 \
-DDOWNLOAD_BOOST=1 \
-DWITH_BOOST=/usr/local/src/mysql-5.7.28/boost/boost_1_59_0
給cmake.sh賦予可執行權限
[root@node9 mysql-5.7.28]# chmod +x cmake.sh
再次安裝mysql依賴包
[root@node9 mysql-5.7.28]# yum -y install openssl openssl-devel expat-deve
執行腳本
[root@node9 mysql-5.7.28]# ./cmake.sh
[root@node9 mysql-5.7.28]# echo $? #輸出為0則說明腳本執行成功
0
編譯mysql
[root@node9 mysql-5.7.28]# make -j 5
[root@node9 mysql-5.7.28]# echo $? #輸出為0則說明MySQL編譯成功
0
安裝mysql
[root@node9 mysql-5.7.28]# make install
[root@node9 mysql-5.7.28]# echo $? #輸出為0則說明MySQL安裝成功
0
3.6 配置mysql
洗掉系統殘留的mysql用戶和用戶組，以免造成干擾
[root@node9 mysql-5.7.28]# userdel -r mysql #洗掉用戶
userdel: mysql mail spool (/var/spool/mail/mysql) not found
userdel: mysql home directory (/home/mysql) not found
[root@node9 mysql-5.7.28]# groupdel mysql #洗掉用戶組
groupdel: group 'mysql' does not exist
創建mysql用戶和用戶組
[root@node9 mysql-5.7.28]# groupadd mysql #創建MySQL用戶組
[root@node9 mysql-5.7.28]# useradd -g mysql mysql #創建MySQL用戶
修改/usr/local/mysql權限
[root@node9 mysql-5.7.28]# chown -R mysql:mysql /usr/local/mysql
進入安裝路徑，創建data、log、run檔案夾,分別用戶保存資料，日志，行程ID
[root@node9 mysql-5.7.28]# cd /usr/local/mysql/
[root@node9 mysql]# mkdir -p /data/log /data/data /data/run
進行mysql初始化
[root@node9 mysql]# bin/mysqld --initialize --basedir=/usr/local/mysql --datadir=/data/data --user=mysql #basedir填MySQL安裝目錄，datadir填資料存在目錄，user填MySQL運行用戶
2019-11-05T06:33:23.155541Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-11-05T06:33:23.247376Z 0 [ERROR] Could not open file '/var/log/mysqld.log' for error logging: Permission denied #出現這個錯誤，說明/var/log/mysqld.log權限不足
2019-11-05T06:33:23.247427Z 0 [ERROR] Aborting
#賦予/var/log/mysqld.log的擁有者為mysql
[root@node9 mysql]# chown -R mysql:mysql /var/log/mysqld.log
#再次進行MySQL初始化
[root@node9 mysql]# bin/mysqld --initialize --basedir=/usr/local/mysql --datadir=/data/data --user=mysql
[root@node9 mysql]# echo $? #此輸出為0則說明MySQL初始化成功
0
創建mysql.log和mysql.pid檔案，并賦予mysql用戶及用戶組權限
[root@node9 mysql]# touch /data/log/mysql.log
[root@node9 mysql]# touch /data/run/mysql.pid
[root@node9 mysql]# chown -R mysql:mysql /data
編輯mysql組態檔(組態檔最好和我保持一致)
[root@node9 mysql]# vim /etc/my.cnf
[root@node9 mysql]# cat /etc/my.cnf
[mysqld]
basedir=/var/lib/mysql
datadir=/data/data #資料存放目錄
port=3306 #埠號
socket=/data/data/mysql.sock
character-set-server=utf8 #字符集編碼
symbolic-links=0
log-error=/data/log/mysql.log #日志存放目錄
pid-file=/data/run/mysql.pid #MySQL行程ID存放目錄
!includedir /etc/my.cnf.d
啟動mysql
[root@node9 mysql]# pwd
/usr/local/mysql
[root@node9 mysql]# cp support-files/mysql.server /etc/init.d/mysql
[root@node9 mysql]# chkconfig mysql on #配置MySQL開機自啟動
[root@node9 mysql]# service mysql start #啟動MySQL
Starting MySQL SUCCESS! #如果出現此，則說明MySQL啟動成功
添加mysql路徑到當前用戶的環境變數里，這樣可以直接在shell中使用MySQL命令
[root@node9 mysql]# vim ~/.bash_profile
[root@node9 mysql]# cat ~/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
#export PATH
export PATH=/usr/local/mysql/bin:$PATH #新增
使環境變數生效并重啟MySQL
[root@node9 mysql]# source ~/.bash_profile
[root@node9 mysql]# service mysql restart
ERROR! MySQL server PID file could not be found!
Starting MySQL. SUCCESS!
[root@node9 mysql]# ps -ef | grep mysql #查看MySQL行程是否存在
root 121678 1 0 14:55 pts/0 00:00:00 /bin/sh /var/lib/mysql/bin/mysqld_safe --datadir=/data/data --pid-file=/data/run/mysql.pid
mysql 121933 121678 1 14:55 pts/0 00:00:00 /var/lib/mysql/bin/mysqld --basedir=/var/lib/mysql --datadir=/data/data --plugin-dir=/var/lib/mysql/lib/plugin --user=mysql --log-error=/data/log/mysql.log --pid-file=/data/run/mysql.pid --socket=/data/data/mysql.sock --port=3306
root 121963 1325 0 14:55 pts/0 00:00:00 grep --color=auto mysql
進入mysql資料庫
[root@node9 mysql]# mysql -uroot -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
#出現這種報錯說明sock出錯，方法為建立套接字軟鏈接，接入MySQL環境
[root@node9 mysql]# ln -s /data/data/mysql.sock /tmp/mysql.sock
首先查詢mysql默認密碼：
[root@node9 ~]# grep 'temporary password' /var/log/mysqld.log
2019-11-05T06:35:28.565529Z 1 [Note] A temporary password is generated for root@localhost: T<&loC3=%t+Q
#T<&loC3=%t+Q就是mysql初始密碼
#進入mysql資料庫
[root@node9 mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.26-log
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SET PASSWORD = PASSWORD('123456');#修改mysql的root用戶密碼為123456
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> UPDATE mysql.user SET authentication_string =PASSWORD('123456') WHERE User='mysql';#修改mysql的mysql用戶密碼為123456
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 1
mysql> GRANT ALL PRIVILEGES ON *.* TO mysql@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO mysql@"%" IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY '123456' WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> FLUSH PRIVILEGES ; #重繪mysql權限
Query OK, 0 rows affected (0.00 sec)
mysql> exit #退出MySQL
Bye
再次進入mysql資料庫，密碼為修改后的密碼123456
[root@node9 mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.26-log Source distribution
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases; #展示MySQL資料庫
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
自此，mysql5.7原始碼編譯安裝完成，
四.MySQL調優篇
4.1 對MySQL進行安全設定
[root@node9 ~]# mysql_secure_installation #此命令用于對MySQL進行安全設定
Securing the MySQL server deployment.
Enter password for user root: #鍵入root密碼
VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?
Press y|Y for Yes, any other key for No: n #如果要安全MySQL密碼強度插件，則y,安裝之后簡單的密碼是設定不成功的
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n #是否更改root密碼，修改則y
... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y #是否移除匿名用戶，建議移除
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y #是否禁止root用戶遠程登錄，建議禁止，輸入y
Success.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y #是否移除test資料庫，按個人需求選擇即可
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y #輸入y,立即重繪權限表，使配置生效
Success.
All done!
4.2 設定MySQL超時時間
MySQL默認連續8小時內沒有訪問資料庫的操作，就會斷開資料庫連接，mysql資料庫拒絕訪問，在某些場景下，8小時是不夠的，需要把MySQL超時時間調大，此時就需要調節MySQL的wait_timeout和interactive_timeout引數
mysql> show variables like '%timeout%';#用于查看MySQL時間相關的引數
修改MySQL組態檔，使wait_timeout和interactive_timeout引數變為31536000
[root@node9 mysql]# vim /etc/my.cnf
[root@node9 mysql]# cat /etc/my.cnf
[mysqld]
basedir=/var/lib/mysql
datadir=/data/data #資料存放目錄
port=3306 #埠號
socket=/data/data/mysql.sock
character-set-server=utf8 #字符集編碼
symbolic-links=0
log-error=/data/log/mysql.log #日志存放目錄
pid-file=/data/run/mysql.pid #MySQL行程ID存放目錄
wait_timeout=31536000 #此為新增，配置MySQL超時時間，默認8小時，針對非互動式場景(jdbc,pymysql類似)
interactive_timeout=31536000 #此為新增，配置MySQL超時時間， 默認8小時，針對互動式場景(手動執行,mysqldump)
!includedir /etc/my.cnf.d
MySQL組態檔修改后，重啟MySQL使組態檔生效
[root@node9 ~]# service mysql restart
Shutting down MySQL. SUCCESS!
Starting MySQL. SUCCESS!
進入MySQL，查看MySQL超時時間是否修改成功
[root@node9 ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log Source distribution
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like '%timeout%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| have_statement_timeout | YES |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 31536000 | #interactive_timeout已經變了
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 60 |
| wait_timeout | 31536000 | #wait_timeout已經變了
+-----------------------------+----------+
13 rows in set (0.01 sec)
4.3 MySQL組態檔優化參考1(按需配置即可)
[root@node9 mysql]# vim /etc/my.cnf
[root@node9 mysql]# cat /etc/my.cnf
[mysqld]
symbolic-links=0
log-error=/data/log/mysql.log #日志存放目錄
wait_timeout=31536000 #此為新增，配置MySQL超時時間，默認8小時，針對非互動式場景(jdbc,pymysql類似)
interactive_timeout=31536000 #此為新增，配置MySQL超時時間， 默認8小時，針對互動式場景(手動執行,mysqldump)
!includedir /etc/my.cnf.d
[client] #客戶端設定，即客戶端默認的連接引數
port = 3306 #默認連接埠
socket=/data/data/mysql.sock #用于本地連接的socket套接字
default-character-set = utf8mb4 #字符編碼編碼，utf8mb4編碼是utf8編碼的超集，兼容utf8，并且能存盤4位元組的表情字符，采用utf8mb4編碼的好處是：存盤與獲取資料的時候，不用再考慮表情字符的編碼與解碼問題，
[mysqld] #服務端基本設定
port = 3306 #MySQL監聽埠
socket = /data/mysqldata/3307/mysql.sock #為MySQL客戶端程式和服務器之間的本地通訊指定一個套接字檔案
pid-file = /data/run/mysql.pid #pid檔案所在目錄
basedir = /var/lib/mysql #使用該目錄作為根目錄（安裝目錄）
datadir = /data/data #資料檔案存放的目錄
tmpdir = /data/mysqldata/3307/tmp #MySQL存放臨時檔案的目錄
character_set_server = utf8mb4 #服務端默認編碼（資料庫級別）
collation_server = utf8mb4_bin #服務端默認的比對規則，排序規則
user = mysql #MySQL啟動用戶
log_bin_trust_function_creators = 1 #This variable applies when binary logging is enabled. It controls whether stored function creators can be trusted not to create stored functions that will cause #unsafe events to be written to the binary log. If set to 0 (the default), users are not permitted to create or alter stored functions unless they have the SUPER #privilege in addition to the CREATE ROUTINE or ALTER ROUTINE privilege. 開啟了binlog后，必須設定這個值為1.主要是考慮binlog安全
performance_schema = 0 #性能優化的引擎，默認關閉
secure_auth = 1 #secure_auth 為了防止低版本的MySQL客戶端(<4.1)使用舊的密碼認證方式訪問高版本的服務器，MySQL 5.6.7開始secure_auth 默認為啟用值1
#ft_min_word_len = 1 #開啟全文索引
#myisam_recover #自動修復MySQL的myisam表
explicit_defaults_for_timestamp #明確時間戳默認null方式
event_scheduler #計劃任務（事件調度器）
skip-external-locking #跳過外部鎖定;External-locking用于多行程條件下為MyISAM資料表進行鎖定
skip-name-resolve #跳過客戶端域名決議；當新的客戶連接mysqld時，mysqld創建一個新的執行緒來處理請求，該執行緒先檢查是否主機名在主機名快取中，如果不在，執行緒試圖決議主機名，
#使用這一選項以消除MySQL進行DNS決議的時間，但需要注意，如果開啟該選項，則所有遠程主機連接授權都要使用IP地址方式，否則MySQL將無法正常處理連接請求!
#bind-address = 127.0.0.1 #MySQL系結IP，允許哪臺服務器登錄，則在后面添加IP即可，多個IP空格隔開
skip-slave-start #為了安全起見，復制環境的資料庫還是設定--skip-slave-start引數，防止復制隨著mysql啟動而自動啟動
slave_net_timeout = 30 #The number of seconds to wait for more data from a master/slave connection before aborting the read. MySQL主從復制的時候，
#當Master和Slave之間的網路中斷，但是Master和Slave無法察覺的情況下（比如防火墻或者路由問題），
#Slave會等待slave_net_timeout設定的秒數后，才能認為網路出現故障，然后才會重連并且追趕這段時間主庫的資料，
#1.用這三個引數來判斷主從是否延遲是不準確的Slave_IO_Running,Slave_SQL_Running,Seconds_Behind_Master.還是用pt-heartbeat吧，
#2.slave_net_timeout不要用默認值，設定一個你能接受的延時時間，
local-infile = 0 #設定是否支持命令load data local infile，如果指定local關鍵詞，則表明支持從客戶主機讀檔案
back_log = 1024 #指定MySQL可能的連接數量，當MySQL主執行緒在很短的時間內得到非常多的連接請求，該引數就起作用，之后主執行緒花些時間（盡管很短）檢查連接并且啟動一個新執行緒，
#back_log引數的值指出在MySQL暫時停止回應新請求之前的短時間內多少個請求可以被存在堆疊中，
#sql_mode = 'PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
sql_mode = NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER #sql_mode,定義了mysql應該支持的sql語法，資料校驗等! NO_AUTO_CREATE_USER：禁止GRANT創建密碼為空的用戶，
#NO_ENGINE_SUBSTITUTION 如果需要的存盤引擎被禁用或未編譯，可以防止自動替換存盤引擎
key_buffer_size = 32M #索引塊的緩沖區大小，對MyISAM表性能影響最大的一個引數.決定索引處理的速度，尤其是索引讀的速度，默認值是16M，通過檢查狀態值Key_read_requests
#和Key_reads，可以知道key_buffer_size設定是否合理
max_allowed_packet = 512M #一個查詢陳述句包的最大尺寸，訊息緩沖區被初始化為net_buffer_length位元組，但是可在需要時增加到max_allowed_packet個位元組，
#該值太小則會在處理大包時產生錯誤，如果使用大的BLOB列，必須增加該值，
#這個值來限制server接受的資料包大小，有時候大的插入和更新會受max_allowed_packet 引數限制，導致寫入或者更新失敗，
thread_stack = 256K #執行緒快取；主要用來存放每一個執行緒自身的標識資訊，如執行緒id，執行緒運行時基本資訊等等，我們可以通過 thread_stack 引數來設定為每一個執行緒堆疊分配多大的記憶體，
sort_buffer_size = 16M #是MySQL執行排序使用的緩沖大小，如果想要增加ORDER BY的速度，首先看是否可以讓MySQL使用索引而不是額外的排序階段，
#如果不能，可以嘗試增加sort_buffer_size變數的大小，
read_buffer_size = 16M #是MySQL讀入緩沖區大小，對表進行順序掃描的請求將分配一個讀入緩沖區，MySQL會為它分配一段記憶體緩沖區，read_buffer_size變數控制這一緩沖區的大小，
#如果對表的順序掃描請求非常頻繁，并且你認為頻繁掃描進行得太慢，可以通過增加該變數值以及記憶體緩沖區大小提高其性能，
join_buffer_size = 16M #應用程式經常會出現一些兩表（或多表）Join的操作需求，MySQL在完成某些 Join 需求的時候（all/index join），為了減少參與Join的“被驅動表”的
#讀取次數以提高性能，需要使用到 Join Buffer 來協助完成 Join操作，當 Join Buffer 太小，MySQL 不會將該 Buffer 存入磁盤檔案，
#而是先將Join Buffer中的結果集與需要 Join 的表進行 Join 操作，
#然后清空 Join Buffer 中的資料，繼續將剩余的結果集寫入此 Buffer 中，如此往復，這勢必會造成被驅動表需要被多次讀取，成倍增加 IO 訪問，降低效率，
read_rnd_buffer_size = 32M #是MySQL的隨機讀緩沖區大小，當按任意順序讀取行時(例如，按照排序順序)，將分配一個隨機讀快取區，進行排序查詢時，MySQL會首先掃描一遍該緩沖，以避免磁盤搜索，
#提高查詢速度，如果需要排序大量資料，可適當調高該值，但MySQL會為每個客戶連接發放該緩沖空間，所以應盡量適當設定該值，以避免記憶體開銷過大，
net_buffer_length = 16K #通信緩沖區在查詢期間被重置到該大小，通常不要改變該引數值，但是如果記憶體不足，可以將它設定為查詢期望的大小，
#（即，客戶發出的SQL陳述句期望的長度，如果陳述句超過這個長度，緩沖區自動地被擴大，直到max_allowed_packet個位元組，）
myisam_sort_buffer_size = 128M #當對MyISAM表執行repair table或創建索引時，用以快取排序索引；設定太小時可能會遇到” myisam_sort_buffer_size is too small”
bulk_insert_buffer_size = 32M #默認8M，當對MyISAM非空表執行insert … select/ insert … values(…),(…)或者load data infile時，使用樹狀cache快取資料，每個thread分配一個；
#注：當對MyISAM表load 大檔案時，調大bulk_insert_buffer_size/myisam_sort_buffer_size/key_buffer_size會極大提升速度
thread_cache_size = 384 #thread_cahe_size執行緒池，執行緒快取，用來快取空閑的執行緒，以至于不被銷毀，如果執行緒快取在的空閑執行緒，需要重新建立新連接，
#則會優先呼叫執行緒池中的快取，很快就能回應連接請求，每建立一個連接，都需要一個執行緒與之匹配，
query_cache_size = 0 #作業原理： 一個SELECT查詢在DB中作業后，DB會把該陳述句快取下來，當同樣的一個SQL再次來到DB里呼叫時，DB在該表沒發生變化的情況下把結果從快取中回傳給Client，
#在資料庫寫入量或是更新量也比較大的系統，該引數不適合分配過大，而且在高并發，寫入量大的系統，建系把該功能禁掉，
query_cache_type = 0 #決定是否快取查詢結果，這個變數有三個取值：0,1,2，分別代表了off、on、demand，
tmp_table_size = 1024M #它規定了內部記憶體臨時表的最大值，每個執行緒都要分配，（實際起限制作用的是tmp_table_size和max_heap_table_size的最小值，）
#如果記憶體臨時表超出了限制，MySQL就會自動地把它轉化為基于磁盤的MyISAM表，存盤在指定的tmpdir目錄下
max_heap_table_size = 512M #獨立的記憶體表所允許的最大容量.# 此選項為了防止意外創建一個超大的記憶體表導致永盡所有的記憶體資源.
open_files_limit = 10240 #mysql打開最大檔案數
max_connections = 2000 #MySQL無論如何都會保留一個用于管理員（SUPER）登陸的連接，用于管理員連接資料庫進行維護操作，即使當前連接數已經達到了max_connections，
#因此MySQL的實際最大可連接數為max_connections+1；
#這個引數實際起作用的最大值（實際最大可連接數）為16384，即該引數最大值不能超過16384，即使超過也以16384為準；
#增加max_connections引數的值，不會占用太多系統資源，系統資源（CPU、記憶體）的占用主要取決于查詢的密度、效率等；
#該引數設定過小的最明顯特征是出現”Too many connections”錯誤；
max-user-connections = 0 #用來限制用戶資源的，0不限制；對整個服務器的用戶限制
max_connect_errors = 100000 #max_connect_errors是一個MySQL中與安全有關的計數器值，它負責阻止過多嘗試失敗的客戶端以防止暴力破解密碼的情況，max_connect_errors的值與性能并無太大關系，
#當此值設定為10時，意味著如果某一客戶端嘗試連接此MySQL服務器，但是失敗（如密碼錯誤等等）10次，則MySQL會無條件強制阻止此客戶端連接，
table_open_cache = 5120 #表描述符快取大小，可減少檔案打開/關閉次數；
interactive_timeout = 86400 #interactive_time -- 指的是mysql在關閉一個互動的連接之前所要等待的秒數(互動連接如mysql gui tool中的連接
wait_timeout = 86400 #wait_timeout -- 指的是MySQL在關閉一個非互動的連接之前所要等待的秒數
binlog_cache_size = 16M #二進制日志緩沖大小
#我們知道InnoDB存盤引擎是支持事務的，實作事務需要依賴于日志技術，為了性能，日志編碼采用二進制格式，那么，我們如何記日志呢？有日志的時候，就直接寫磁盤？
#可是磁盤的效率是很低的，如果你用過Nginx，，一般Nginx輸出access log都是要緩沖輸出的，因此，記錄二進制日志的時候，我們是否也需要考慮Cache呢？
#答案是肯定的，但是Cache不是直接持久化，于是面臨安全性的問題——因為系統宕機時，Cache中可能有殘余的資料沒來得及寫入磁盤，因此，Cache要權衡，要恰到好處：
#既減少磁盤I/O，滿足性能要求；又保證Cache無殘留，及時持久化，滿足安全要求，
slow_query_log = 1 #開啟慢查詢
long_query_time = 1 #超過的時間為1s；MySQL能夠記錄執行時間超過引數 long_query_time 設定值的SQL陳述句，默認是不記錄的，
log-slow-admin-statements
log-queries-not-using-indexes #記錄管理陳述句和沒有使用index的查詢記錄
# *** Replication related settings ***
binlog_format = ROW #在復制方面的改進就是引進了新的復制技術：基于行的復制，簡言之，這種新技術就是關注表中發生變化的記錄，而非以前的照抄 binlog 模式，
#從 MySQL 5.1.12 開始，可以用以下三種模式來實作：基于SQL陳述句的復制(statement-based replication, SBR)，基于行的復制(row-based replication, RBR)，
混合模式復制(mixed-based replication, MBR)，相應地，binlog的格式也有三種：STATEMENT，ROW，MIXED，MBR 模式中，SBR 模式是默認的，
#max_binlog_cache_size = 102400 # 為每個session 最大可分配的記憶體，在事務程序中用來存盤二進制日志的快取，
log-bin = /data/mysqldata/3307/binlog/mysql-bin #開啟二進制日志功能，binlog資料位置
log-bin-index = /data/mysqldata/3307/binlog/mysql-bin.index
relay-log = /data/mysqldata/3307/relay/mysql-relay-bin #relay-log日志記錄的是從服務器I/O執行緒將主服務器的二進制日志讀取過來記錄到從服務器本地檔案，
#然后SQL執行緒會讀取relay-log日志的內容并應用到從服務器
relay-log-index = /data/mysqldata/3307/relay/mysql-relay-bin.index #binlog傳到備機被寫道relaylog里，備機的slave sql執行緒從relaylog里讀取然后應用到本地，
server_id = 100 #服務端ID，用來高可用時做區分
log_slave_updates = 1 #log_slave_updates是將從服務器從主服務器收到的更新記入到從服務器自己的二進制日志檔案中，
expire-logs-days = 15 #二進制日志自動洗掉的天數，默認值為0,表示“沒有自動洗掉”，啟動時和二進制日志回圈時可能洗掉，
max_binlog_size = 512M #如果二進制日志寫入的內容超出給定值，日志就會發生滾動，你不能將該變數設定為大于1GB或小于4096位元組， 默認值是1GB，
replicate-wild-ignore-table = mysql.% #replicate-wild-ignore-table引數能同步所有跨資料庫的更新，比如replicate-do-db或者replicate-ignore-db不會同步類似
#replicate-wild-do-table = db_name.% #設定需要復制的Table
#slave-skip-errors = 1062,1053,1146 #復制時跳過一些錯誤;不要胡亂使用這些跳過錯誤的引數，除非你非常確定你在做什么，當你使用這些引數時候，MYSQL會忽略那些錯誤，
#這樣會導致你的主從服務器資料不一致，
auto_increment_offset = 1
auto_increment_increment = 2 #這兩個引數一般用在主主同步中，用來錯開自增值, 防止鍵值沖突
relay_log_info_repository = TABLE #將中繼日志的資訊寫入表:mysql.slave_realy_log_info
master_info_repository = TABLE #將master的連接資訊寫入表：mysql.salve_master_info
relay_log_recovery = on #中繼日志自我修復；當slave從庫宕機后，假如relay-log損壞了，導致一部分中繼日志沒有處理，則自動放棄所有未執行的relay-log，
#并且重新從master上獲取日志，這樣就保證了relay-log的完整性
# *** innodb setting ***
innodb_buffer_pool_size = 4G #InnoDB 用來高速緩沖資料和索引記憶體緩沖大小， 更大的設定可以使訪問資料時減少磁盤 I/O，
innodb_data_file_path = ibdata1:1G:autoextend #單獨指定資料檔案的路徑與大小
innodb_flush_log_at_trx_commit = 0 #每次commit 日志快取中的資料刷到磁盤中，通常設定為 1，意味著在事務提交前日志已被寫入磁盤， 事務可以運行更長以及服務崩潰后的修復能力，
#如果你愿意減弱這個安全，或你運行的是比較小的事務處理，可以將它設定為 0 ，以減少寫日志檔案的磁盤 I/O，這個選項默認設定為 0，
#sync_binlog = 1000 #sync_binlog=n，當每進行n次事務提交之后，MySQL將進行一次fsync之類的磁盤同步指令來將binlog_cache中的資料強制寫入磁盤，
innodb_read_io_threads = 8
innodb_write_io_threads = 8 #對于多核的CPU機器，可以修改innodb_read_io_threads和innodb_write_io_threads來增加IO執行緒，來充分利用多核的性能
innodb_file_format = Barracuda #Innodb Plugin引擎開始引入多種格式的行存盤機制，目前支持：Antelope、Barracuda兩種，其中Barracuda兼容Antelope格式，
innodb_open_files = 65536 #限制Innodb能打開的表的數量
innodb_purge_threads = 1 #開始碎片回收執行緒，這個應該能讓碎片回收得更及時而且不影響其他執行緒的操作
innodb_support_xa = FALSE #分布式事務
innodb_log_buffer_size = 256M #InnoDB 將日志寫入日志磁盤檔案前的緩沖大小，理想值為 1M 至 8M，大的日志緩沖允許事務運行時不需要將日志保存入磁盤而只到事務被提交(commit)，
#因此，如果有大的事務處理，設定大的日志緩沖可以減少磁盤I/O，
innodb_log_file_size = 1G #日志組中的每個日志檔案的大小(單位 MB)，如果 n 是日志組中日志檔案的數目，那么理想的數值為 1M 至下面設定的緩沖池(buffer pool)大小的 1/n，較大的值，
#可以減少重繪緩沖池的次數，從而減少磁盤 I/O，但是大的日志檔案意味著在崩潰時需要更長的時間來恢復資料，
innodb_log_files_in_group = 3 #指定有三個日志組
#innodb_lock_wait_timeout = 120 #在回滾(rooled back)之前，InnoDB 事務將等待超時的時間(單位 秒)
innodb_max_dirty_pages_pct = 75 #innodb_max_dirty_pages_pct作用：控制Innodb的臟頁在緩沖中在那個百分比之下，值在范圍1-100,默認為90.這個引數的另一個用處：
#當Innodb的記憶體分配過大，致使swap占用嚴重時，可以適當的減小調整這個值，使達到swap空間釋放出來，建義：這個值最大在90%，最小在15%，
#太大，快取中每次更新需要致換資料頁太多，太小，放的資料頁太小，更新操作太慢，
innodb_buffer_pool_instances = 4 #innodb_buffer_pool_size 一致 可以開啟多個記憶體緩沖池，把需要緩沖的資料hash到不同的緩沖池中，這樣可以并行的記憶體讀寫，
innodb_io_capacity = 500 #這個參資料控制Innodb checkpoint時的IO能力
innodb_file_per_table = 1 #作用：使每個Innodb的表，有自已獨立的表空間，如洗掉檔案后可以回收那部分空間，
#分配原則：只有使用不使用，但ＤＢ還需要有一個公共的表空間，
innodb_change_buffering = inserts #當更新/插入的非聚集索引的資料所對應的頁不在記憶體中時（對非聚集索引的更新操作通常會帶來隨機IO），會將其放到一個insert buffer中，
#當隨后頁面被讀到記憶體中時，會將這些變化的記錄merge到頁中，當服務器比較空閑時，后臺執行緒也會做merge操作
innodb_adaptive_flushing = 1 #該值影響每秒重繪臟頁的操作，開啟此配置后，重繪臟頁會通過判斷產生重做日志的速度來判斷最合適的重繪臟頁的數量；
transaction-isolation = READ-COMMITTED #資料庫事務隔離級別 ，讀取提交內容
innodb_flush_method = O_DIRECT #innodb_flush_method這個引數控制著innodb資料檔案及redo log的打開、刷寫模式
#InnoDB使用O_DIRECT模式打開資料檔案，用fsync()函式去更新日志和資料檔案，
#innodb_use_sys_malloc = 1 #默認設定值為1.設定為0：表示Innodb使用自帶的記憶體分配程式；設定為1：表示InnoDB使用作業系統的記憶體分配程式，
[mysqldump]
quick #它強制 mysqldump 從服務器查詢取得記錄直接輸出而不是取得所有記錄后將它們快取到記憶體中
max_allowed_packet = 512M #限制server接受的資料包大小;指代mysql服務器端和客戶端在一次傳送資料包的程序當中資料包的大小
net_buffer_length = 16384 #TCP/IP和套接字通信緩沖區大小,創建長度達net_buffer_length的行
[mysql]
auto-rehash #auto-rehash是自動補全的意思
[isamchk] #isamchk資料檢測恢復工具
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk] #使用myisamchk實用程式來獲得有關你的資料庫桌表的資訊、檢查和修復他們或優化他們
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout #mysqlhotcopy使用lock tables、flush tables和cp或scp來快速備份資料庫.它是備份資料庫或單個表最快的途徑,完全屬于物理備份,但只能用于備份MyISAM存盤引擎和運行在資料庫目錄所在的機器上.
#與mysqldump備份不同,mysqldump屬于邏輯備份,備份時是執行的sql陳述句.使用mysqlhotcopy命令前需要要安裝相應的軟體依賴包.
4.4 MySQL組態檔優化參考2（按需配置即可）
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
#log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
#支持符號鏈接，就是可以通過軟連接的方式，管理其他目錄的資料庫，最好不要開啟，當一個磁盤或磁區空間不夠時，可以開啟該引數將資料存盤到其他的磁盤或磁區，
#https://blog.csdn.net/moxiaomomo/article/details/17092871
symbolic-links=0
########basic settings########
server-id = 11
port = 3306
user = mysql
#設定autocommit=0，則用戶將一直處于某個事務中，直到執行一條commit提交或rollback陳述句才會結束當前事務重新開始一個新的事務，set autocommit=0的好處是在頻繁開啟事務的場景下，減少一次begin的互動，
autocommit = 1
#utf8mb4編碼是utf8編碼的超集，兼容utf8，并且能存盤4位元組的表情字符，
#采用utf8mb4編碼的好處是：存盤與獲取資料的時候，不用再考慮表情字符的編碼與解碼問題，
character_set_server=utf8mb4
skip_name_resolve = 1
max_connections = 800
# 對于同一主機，如果有超出該引數值個數的中斷錯誤連接，則該主機將被禁止連接，如需對該主機進行解禁，執行：FLUSH HOST，
max_connect_errors = 1000
#資料庫隔離級別
transaction_isolation = READ-COMMITTED
#MySQL在完成某些join（連接）需求的時候，為了減少參與join的“被驅動表”的讀取次數以提高性能，需要使用到join buffer來協助完成join操作當join buffer 太小，MySQL不會將該buffer存入磁盤檔案而是先將join buffer中的結果與需求join的表進行操作，然后清空join buffer中的資料，繼續將剩余的結果集寫入次buffer中
join_buffer_size = 128M
tmp_table_size = 64M
tmpdir = /tmp
#該值設定過小將導致單個記錄超過限制后寫入資料庫失敗，且后續記錄寫入也將失敗
max_allowed_packet = 64M
#mysql在關閉一個互動的連接之前所要等待的秒數
interactive_timeout = 1200
#mysql在關閉一個非互動的連接之前所要等待的秒數
wait_timeout = 600
#MySQL讀入緩沖區的大小
read_buffer_size = 16M
#MySQL的隨機讀緩沖區大小
read_rnd_buffer_size = 8M
#MySQL的順序讀緩沖區大小
sort_buffer_size = 8M
########log settings########
log_error = /var/log/docker_log/mysql/error.log
#開啟慢查詢日志
slow_query_log = 1
#超出次設定值的SQL即被記錄到慢查詢日志
long_query_time = 6
slow_query_log_file = /var/log/docker_log/mysql/slow.log
#表示記錄下沒有使用索引的查詢
log_queries_not_using_indexes = 1
#記錄管理陳述句
log_slow_admin_statements = 1
#開啟復制從庫復制的慢查詢的日志
log_slow_slave_statements = 1
#設定每分鐘增長的沒有使用索引查詢的日志數量
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 90
min_examined_row_limit = 100
########replication settings########
#將master.info和relay.info保存在表中
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_bin = bin.log
#當每進行n次事務提交之后，MySQL將進行一次fsync之類的磁盤同步指令來將binlog_cache中的資料強制寫入磁盤，設定為零是讓系統自行決定
sync_binlog = 5
#開啟全域事務ID，GTID能夠保證讓一個從服務器到其他的從服務器那里實作資料復制而且能夠實作資料整合的
gtid_mode = on
#開啟gtid，必須主從全開
enforce_gtid_consistency = 1
#從服務器的更新是否寫入二進制日志
log_slave_updates = 1
#三種模式 STATEMENT（有可能主從資料不一致，日質量小）、ROW（產生大量二進制日志）、MIXED
binlog_format = mixed
#relay-log日志記錄的是從服務器I/O執行緒將主服務器的二進制日志讀取過來記錄到從服務器本地檔案，然后SQL執行緒會讀取relay-log日志的內容并應用到從服務器
relay_log = /var/log/docker_log/mysql/relay.log
relay_log_recovery = 1
#開啟簡單gtid，開啟此項會提升mysql執行恢復的性能
binlog_gtid_simple_recovery = 1
slave_skip_errors = ddl_exist_errors
########innodb settings########
#這個引數在一開始初始化時就要加入my.cnf里，如果已經創建了表，再修改，啟動MySQL會報錯，最好為8K
#innodb_page_size = 16K
innodb_page_size = 8K
#資料緩沖區buffer pool大小，建議使用物理記憶體的 75%
innodb_buffer_pool_size = 2G
#當buffer_pool的值較大的時候為1，較小的設定為8
innodb_buffer_pool_instances = 8
#運行時load緩沖池，快速預熱緩沖池，將buffer pool的內容（檔案頁的索引）dump到檔案中，然后快速load到buffer pool中，避免了資料庫的預熱程序，提高了應用訪問的性能
innodb_buffer_pool_load_at_startup = 1
#運行時dump緩沖池
innodb_buffer_pool_dump_at_shutdown = 1
#在innodb中處理用戶查詢后，其結果在記憶體空間的緩沖池已經發生變化，但是還未記錄到磁盤，這種頁面稱為臟頁，將臟頁記錄到磁盤的程序稱為刷臟
innodb_lru_scan_depth = 2000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
#事務等待獲取資源等待的最長時間，超過這個時間還未分配到資源則會回傳應用失敗，默認50s
innodb_lock_wait_timeout = 30
#日志組所在的路徑，默認為data的home目錄；
innodb_log_group_home_dir = /data/mysql/
#innodb_undo_directory = /data/mysql/undolog/
#這個引數控制著innodb資料檔案及redo log的打開、刷寫模式，https://blog.csdn.net/gua___gua/article/details/44916207
#innodb_flush_method = O_DIRECT-不經過系統快取直接存入磁盤，
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_strict_mode = 1
#innodb獨享表空間，有點很多，缺點會導致單個表檔案過大
#innodb_file_per_table = 1
#undo日志回滾段 默認為128
innodb_undo_logs = 128
#傳統機械硬碟建議使用，而對于固態硬碟可以關閉
#innodb_flush_neighbors = 1
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
#控制是否使用獨立purge執行緒
innodb_purge_threads = 1
#改為ON時，允許單列索引最大達到3072，否則最大為767
innodb_large_prefix = 1
innodb_thread_concurrency = 8
#開啟后會將所有的死鎖記錄到error_log中
innodb_print_all_deadlocks = 1
innodb_sort_buffer_size = 16M
########semi sync replication settings########
#半同步復制
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000
#表示轉儲每個bp instance LRU上最熱的page的百分比，通過設定該引數可以減少轉儲的page數，
innodb_buffer_pool_dump_pct = 40
#刷臟的行程N-1
innodb_page_cleaners = 4
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 2G
#控制回收(收縮)undo log的頻率.undo log空間在它的回滾段沒有得到釋放之前不會收縮，
innodb_purge_rseg_truncate_frequency = 128
log_timestamps=system
#該引數基于MySQL5.7 Group Replication組復制的，沒有使用不要設定
#transaction_write_set_extraction=MURMUR32
#https://www.cnblogs.com/hzhida/archive/2012/08/08/2628826.html
show_compatibility_56=on
五.參考資料
https://www.cnblogs.com/langdashu/p/5889352.html
https://www.2cto.com/database/201704/633451.html
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/103169.html
標籤：MySQL
下一篇：MySQL 主從復制問題