# 一、前置准备
## 1、检查yum源
```shell
[root@host-92 secure]# yum info yum
Loaded plugins: fastestmirror, langpacks
Determining fastest mirrors
Installed Packages
Name : yum
Arch : noarch
Version : 3.4.3
Release : 161.el7.centos
Size : 5.6 M
Repo : installed
From repo : anaconda
Summary : RPM package installer/updater/manager
URL : http://yum.baseurl.org/
License : GPLv2+
Description : Yum is a utility that can check for and automatically download and
: install updated RPM packages. Dependencies are obtained and downloaded
: automatically, prompting the user for permission as necessary.
```
## 2、安装使用MariaDB版本
开源数据库完全兼容Mysql数据库，教程使用的版本为5.5.60
```sql
MariaDB [(none)]> select version();
+----------------+
| version() |
+----------------+
| 5.5.60-MariaDB |
+----------------+
1 row in set (0.00 sec)
```
# 二、安装主节点MySQL
## 1、安装MariaDB
```shell
yum -y install mariadb mariadb-server
```
## 2、启动
```shell
启动数据库
systemctl start mariadb
确保重启reboot后数据库能启动
systemctl enable mariadb
```
## 3、查看状态 ，需要是activing状态
service mariadb status
预期结果： Active: active (running) since Tue 2022-08-09 17:47:54 CST; 20h ago
```shell
[root@host-92 secure]# service mariadb status
Redirecting to /bin/systemctl status mariadb.service
● mariadb.service - MariaDB database server
Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2022-08-09 17:47:54 CST; 20h ago
Process: 22365 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)
Process: 22332 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
Main PID: 22364 (mysqld_safe)
CGroup: /system.slice/mariadb.service
├─22364 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
└─22920 /usr/libexec/mysqld --basedir=/usr --datadir=/data/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/data/...
Aug 09 17:47:23 host-92 systemd[1]: Starting MariaDB database server...
Aug 09 17:47:23 host-92 mariadb-prepare-db-dir[22332]: Database MariaDB is probably initialized in /data/mysql already, nothing is done.
Aug 09 17:47:23 host-92 mariadb-prepare-db-dir[22332]: If this is not the case, make sure the /data/mysql is empty before running mariadb-prepare-db-dir.
Aug 09 17:47:24 host-92 mysqld_safe[22364]: 220809 17:47:24 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
Aug 09 17:47:24 host-92 mysqld_safe[22364]: 220809 17:47:24 mysqld_safe Starting mysqld daemon with databases from /data/mysql
Aug 09 17:47:54 host-92 systemd[1]: Started MariaDB database server.
```
## 4、开启MySQL二进制日志记录
执行vim /etc/my.cnf，向文件中写入下面的记录，要求主节点和从节点server-id不冲突且唯一
```shell
[mysqld]
log-bin=mysql-bin
server-id=24
```
## 5、重启mysql服务
```shell
systemctl restart mariadb
```
## 6、初始化数据库账户密码
```mysql
1.进入mysql命令行
mysql -uroot
直接回车即可
2.执行
use mysql;
update user set Password=password('密码') where User='root';
3.提交修改并检查
flush privileges;
select Host,User,password from user where user='root';
4.退出
quit;
5.重新登录数据库，此时需要密码
mysql -u root -p
```
## 7、检查日志记录
```mysql
日志检查查看二进制日志是否开启 :
show global variables like '%log_bin%';
查看主节点二进制列表
SHOW MASTER LOGS;
查看主节点server id
SHOW GLOBAL VARIABLES LIKE '%server%';
MariaDB [(none)]> show global variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin | ON |
| log_bin_trust_function_creators | OFF |
| sql_log_bin | ON |
+---------------------------------+-------+
3 rows in set (0.00 sec)
MariaDB [(none)]> SHOW MASTER LOGS;
+------------------+------------+
| Log_name | File_size |
+------------------+------------+
| mysql-bin.000001 | 351 |
| mysql-bin.000002 | 1073904425 |
| mysql-bin.000003 | 1074371223 |
| mysql-bin.000004 | 1074895548 |
.......
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%server%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| character_set_server | latin1 |
| collation_server | latin1_swedish_ci |
| server_id | 24 |
+----------------------+-------------------+
```
## 8、在主节点上创建有复制权限的用户
```mysql
GRANT REPLICATION SLAVE ON *.* TO 'repl_master'@'10.8.7.8' IDENTIFIED BY '密码';
update user set Password=password('密码') where User='repl_master';
flush privileges;
select * from mysql.user;, 预期结果如下
*************************** 7. row ***************************
Host: 10.8.7.8
User: repl_master
Password: *9444156E749585F26BA2924CF72860481B44DA76
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: N
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin:
authentication_string:
```
备注：repl_master, 10.8.7.8, '密码' 是要给从节点使用并执行复制权限，分别表示从节点使用的账户、从节点IP、账户密码；当前用例的数据是mock的，不代表真实的IP、账号和密码
## 9、锁表
在从节点开始同步之前，对主节点数据库进行锁表操作，最好不要在源库持续大量写入数据的场景下建立主从连接！
```mysql
此时，如果条件允许 为了同步前数据一致，建议锁定一下表 :
flush tables with read lock;
查看【Master(主库)】状态：
SHOW MASTER STATUS;
记录二进制文件名 (masterl-bin.00000*) 和位置 (Position) (如果有多个二进制文件名，取最后一个的名称和位置！！)，确保同步点正常！
MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000416 | 100147369 | | |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)
```
# 三、安装从节点
## 1-3、从节点安装数据库
先执行步骤二里面主节点安装顺序里的【1、2、3】，然后按照下面的顺序执行！
## 4、开启relay log
```shell
[mysqld]
server-id=23
relay-log=slave-relay-log
relay-log-index=slave-relay-log.index
```
请谨慎添加，**不要随意增加配置参数**！
## 5、同步binlog
**需要主服务器主机名，登陆凭据**，**二进制文件(最后一个)的名称和位置**
```mysql
停止从节点同步
stop slave;
关联主节点账户和binlog信息
CHANGE MASTER TO MASTER_HOST='10.8.7.9', MASTER_USER='repl_master', MASTER_PASSWORD='密码', MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=1448;
开启主从同步
start slave;
show slave status\G;
```
## 6、检查主从同步状态
```sql
show slave status;
输出：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: ***********
Master_User: repl_master
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000416
Read_Master_Log_Pos: 100147369
Relay_Log_File: slave-relay-log.000025
Relay_Log_Pos: 100147653
Relay_Master_Log_File: mysql-bin.000416
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 100147369
Relay_Log_Space: 100147990
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 24
1 row in set (0.00 sec)
```
## 7、解锁主库表
```mysql
unlock tables;
```
# 四、Mysql挂载盘【可选操作】
修改MySQL挂载的数据盘等位置，单独格式化文件系统和挂载到相应的目录下面
## 1、检查挂载目标盘vdb
```shell
[root@huangtj-mysql-01 ~]# ps -ef|grep mysql
mysql 103619 1 0 15:46 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr
mysql 103816 103619 0 15:46 ? 00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock root 107843 102003 0 16:09 pts/1 00:00:00 grep --color=auto mysql
检查根目录的挂载盘
[root@huangtj-mysql-01 ~]# df /
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/vda4 52927232 1924320 51002912 4% /
检查空闲的目标盘vdb
[root@huangtj-mysql-01 ~]# lsblk -f
NAME FSTYPE LABEL UUID MOUNTPOINT
sda
├─sda1
├─sda2 ext4 e98107bb-38cd-4076-9ef5-362039dbf3f4
├─sda3 ext4 edd7e74d-d91a-43f1-af06-fd64413790f8
└─sda4
vda
├─vda1
├─vda2 vfat 4BF4-6DA4 /boot/efi
|─vda3 ext2 cfacfb0b-a2ca-43bc-b114-df92a7100e7b /boot
├─vda4 xfs 3d511aba-cf83-4b85-9f6b-ae217eec098a /
└─vda5 swap 20c68b57-cacc-48af-b29c-ff297b645197
vdb
[root@huangtj-mysql-01 ~]# df /var/lib/
Filesystem 1K-blocks Used Available Use% Mounted on /dev/vda4 52927232 1924324 51002908 4% /
检查磁盘写入性能，检查磁盘不能限制数据库的写入能力
[root@host-92 secure]# dd if=/dev/zero of=/data/tmp/tmp.file bs=4k count=1000000
1000000+0 records in
1000000+0 records out
4096000000 bytes (4.1 GB) copied, 1.89153 s, 2.2 GB/s
```
## 2、修改数据盘并挂载
```shell
格式化文件系统并挂载分区
mkfs.xfs /dev/vdb
mkdir -p /data/mysql
mount /dev/vdb /data/mysql
先停止mysql
systemctl stop mariadb
移动数据:
mv /var/lib/mysql/* /data/mysql/
创建软连接
ln -s /data/mysql/ /var/lib/mysql/
修改my.conf
Under [mysqld]:
datadir=/data/mysql
socket=/data/mysql/mysql.sock
Under [client]:
port=3306
socket=/data/mysql/mysql.sock
启动mysql，保证MySQL文件夹的权限足够 chown -R mysql:mysql /data/mysql/
```
### 常见问题处理：
- **保证MySQL文件夹的权限足够 chown -R mysql:mysql /data/mysql/**
# 五、Sysbench部署
## 1、安装压测工具
```shell
【可选操作】
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
【必须操作】
sudo yum -y install sysbench
```
## 2、安装完成后会有一些 lua 脚本
```shell
[root@mgr1 src]# ll /usr/share/sysbench/
total 132
-rwxr-xr-x. 1 root root 1452 May 8 2020 bulk_insert.lua
-rw-r--r--. 1 root root 14369 May 8 2020 oltp_common.lua
-rwxr-xr-x. 1 root root 1290 May 8 2020 oltp_delete.lua
-rwxr-xr-x. 1 root root 2415 May 8 2020 oltp_insert.lua
......
```
## 3、部分lua脚本说明
```shell
insert.lua 单值插入数据
bulk_inert.lua 批量插入数据
delete.lua 删除数据
select.lua 简单主键查询
oltp.lua 混合读写测试
```
## 4、sysbench参数
```shell
MySQL连接信息参数
--mysql-host：MySQL服务器主机名，默认localhost；如果在本机上使用localhost报错，提示无法连接MySQL服务器，改成本机的IP地址应该就可以了。
--mysql-port：MySQL服务器端口，默认3306
--mysql-user：用户名
--mysql-password：密码
MySQL执行参数
--oltp-test-mode：执行模式，包括simple、nontrx和complex，默认是complex。simple模式下只测试简单的查询；nontrx不仅测试查询，还测试插入更新等，但是不使用事务；complex模式下测试最全面，会测试增删改查，而且会使用事务。可以根据自己的需要选择测试模式。
--oltp-tables-count：测试的表数量，根据实际情况选择
--oltp-table-size：测试的表的大小，根据实际情况选择
--threads：客户端的并发连接数
--time：测试执行的时间，单位是秒，该值不要太短，可以选择120
--report-interval：生成报告的时间间隔，单位是秒，如10
```
## 5、注意事项
```shell
- 尽量不要在MySQL服务器运行的机器上进行测试，一方面可能无法体现网络（哪怕是局域网）的影响，另一方面，sysbench的运行（尤其是设置的并发数较高时）会影响MySQL服务器的表现。
- 可以逐步增加客户端的并发连接数（--thread参数），观察在连接数不同情况下，MySQL服务器的表现；如分别设置为10,20,50,100等。
- 一般执行模式选择complex即可，如果需要特别测试服务器只读性能，或不使用事务时的性能，可以选择simple模式或nontrx模式。
- 如果连续进行多次测试，注意确保之前测试的数据已经被清理干净。
```
## 6、示例
```shell
- 数据准备
sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=192.168.10.10 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=120 --report-interval=10 prepare
执行模式为complex，使用了10个表，每个表有10万条数据，客户端的并发线程数为10，执行时间为120秒，每10秒生成一次报告。
- 压测
sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=192.168.10.10 --mysql-port=3306 --mysql-user=root --mysql-password=123456 --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=120 --report-interval=10 run >> /home/test/mysysbench.log
- 清理测试数据
sysbench ./tests/include/oltp_legacy/oltp.lua --mysql-host=192.168.10.10 --mysql-port=3306 --mysql-user=root --mysql-password=123456 cleanup
1．oltp_read_write
该测试案例对应sysbench 0.5版本的oltp.lua脚本，用于测试数据库的TPS性能。
[root@localhost~]# sysbench --db-driver=mysql --time=180 --threads=4 --report-interval=1 --mysql-host =127.0.0.1 --mysql-port=3306 --mysql-user=qbench --mysql-password=qbench –mysql -db =sbtest --tables=32 --table-size=5000000 oltp_read_write --db-ps-mode=disable run
2．oltp_read_only
该测试案例对应sysbench 0.5版本的select.lua脚本，用于测试数据库的只读性能。
[root@localhost~]# sysbench --db-driver=mysql --time=180 --threads=4 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=qbench --mysql-password=qbench --mysql-db=sbtest --tables=32 --table-size=5000000 oltp_read_only --db-ps-mode=disable run
3．oltp_delete
该测试案例对应sysbench 0.5版本的delete.lua脚本，用于测试数据库的删除性能。
[root@localhost~]# sysbench --db-driver=mysql --time=180 --threads=4 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=qbench --mysql-password=qbench --mysql-db=sbtest --tables=32 --table-size=5000000 oltp_delete --db-ps-mode=disable run
4．oltp_update_index
该测试案例对应sysbench 0.5版本的update_index.lua脚本，用于测试数据库的更新索引性能。
[root@localhost~]# sysbench --db-driver=mysql --time=180 --threads=4 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=qbench --mysql-password=qbench --mysql-db=sbtest --tables=32 --table-size=5000000 oltp_update_index --db-ps-mode=disable run
5．oltp_update_non_index
该测试案例对应sysbench 0.5版本的update_non_index.lua脚本，用于测试数据库的更新非索引字段性能。
[root@localhost~]# sysbench --db-driver=mysql --time=180 --threads=4 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=qbench --mysql-password=qbench --mysql-db=sbtest --tables=32 --table-size=5000000 oltp_update_non_index --db-ps-mode=disable run
6．oltp_insert
该测试案例对应sysbench 0.5版本的insert.lua脚本，用于测试数据库的插入性能。
[root@localhost~]# sysbench --db-driver=mysql --time=180 --threads=4 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=qbench --mysql-password=qbench --mysql-db=sbtest --tables=32 --table-size=5000000 oltp_insert --db-ps-mode=disable run
7．oltp_write_only
该测试案例是sysbench 1.0.x版本新增的，与原来的oltp.lua相比，少了select部分。
[root@localhost~]# sysbench --db-driver=mysql --time=180 --threads=4 --report-interval=1 –mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=qbench --mysql-password=qbench –mysql-db=sbtest --tables=32 --table-size=5000000 oltp_write_only --db-ps-mode=disable run
```
## 7、主要指标说明
```shell
OLTP test statistics:
queries performed:
read: 4003048 //总select数量
write: 1143728 //总update、insert、delete语句数量
other: 571864 //commit、unlock tables以及其他mutex的数量
total: 5718640
transactions: 285932 (2382.10 per sec.) //通常需要关注的数字(TPS)
read/write requests: 5146776 (42877.85 per sec.)
other operations: 571864 (4764.21 per sec.)
ignored errors: 0 (0.00 per sec.) //忽略的错误数
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 120.0334s //即max-time指定的压测实际
total number of events: 285932 //总的事件数，一般与transactions相同
total time taken by event execution: 15362.6623s
response time:
min: 17.60ms
avg: 53.73ms //95%的语句的平均响应时间
max: 252.90ms
approx. 95 percentile: 66.88ms
Threads fairness:
events (avg/stddev): 2233.8438/9.04
execution time (avg/stddev): 120.0208/0.01
response time avg: 平均响应时间。（后面的95%的大小可以通过--percentile=98的方式去更改）
transactions: 精确的说是这一项后面的TPS 。但如果使用了-oltp-skip-trx=on，这项事务数恒为0，需要用total number of events 去除以总时间，得到tps（其实还可以分为读tps和写tps）
read/write requests: 用它除以总时间，得到吞吐量QPS
```
## 常见问题处理
### 1）、预处理
```shell
shell> sync # 将脏数据刷新到磁盘
shell> echo 3 >/proc/sys/vm/drop_caches # 清除 cache
shell> swapoff -a && swapon -a # 如果使用 swap 则释放操作
```
### 2）、手动安装
#### 2.1 RPM包安装
手动下载RPM包来安装（仅限RHEL/CentOS），但是在安装sysbench前需要先安装依赖包，然后再安装该RPM包。
```shell
[root@localhost~]# yum install mysql-libs postgresql-libs -y
[root@localhost~]# rpm -Uvh sysbench-1.0.7-13.el6.x86_64.rpm --nodeps
由于sysbench编译时依赖libmysqlclient动态链接库，所以要选择对应版本的sysbench，或者使用软链接：
[root@localhost~]# ln -s /usr/lib64/mysql/libmysqlclient.so.18 /usr/lib64/ libmysqlclient_r.\ so.16
```
#### 2.2 编译安装
```shell
安装依赖的编译环境：
RHEL/CentOS
[root@localhost~]# yum -y install make automake libtool pkgconfig libaio-devel vim-\
common git
For MySQL support, replace with mysql-devel on RHEL/CentOS 5
[root@localhost~]# yum -y install mariadb-devel
For PostgreSQL support
[root@localhost~]# yum -y install postgresql-devel
最新的sysbench程序包地址为[https://github.com/akopytov/s...](https://link.segmentfault.com/?enc=TMdhpWo2VrfBSRW7FiY0Sg%3D%3D.vncwDncnD%2B5xkDM08N5IwM%2Bikvk18KYz12xYy47rdAt6vhWAbIfK3oCNwVhlHniU)，直接下载并解压缩程序包：
[root@localhost~]# wget
[https://github.com/akopytov/s...](https://link.segmentfault.com/?enc=ax53e7h2ibAG6cdd1E04Aw%3D%3D.HkKhcc%2B5x7B2voNwCXF7MRYYsSVej75bu6kDAsV2r8AKCwTjD2Ve%2BwHX2dDN5de1Z7hsLbBgrdahFd%2FfzrbUSg%3D%3D)
[root@localhost~]# tar zxf sysbench-1.0.13.tar.gz
编译安装：
[root@localhost~]# cd sysbbench-1.0.13
[root@localhost~]# ./autogen.sh
Add --with-pgsql to build with PostgreSQL support
[root@localhost~]# ./configure
[root@localhost~]# make
[root@localhost~]# make install
```
#### 2.3 验证安装是否成功
查看版本信息，如果可以正常查看到版本信息而不报错，则说明sysbench安装成功。
```shell
[root@localhost~]# sysbench --version
```
# 六、修改 /etc/my.cnf
当解决了上述服务器硬件制约因素后，让我们看看MySQL自身的优化是如何操作的。对MySQL自身的优化主要是对其配置文件 my.cnf中的各项参数进行优化调整。下面我们介绍一些对性能影响较大的参数。 由于my.cnf文件的优化设置是与服务器硬件配置息息相关的，因而我们指定一个假想的服务器硬件环境：
## 1、max_allowed_packet
```
允许最大接收数据包的大小，防止服务器发送过大的数据包。
当发出长查询或 mysqld 返回较大结果时，mysqld 才会分配内存，所以增大这个值风险不大，默认 16M，也可以根据需求改大，但太大会有溢出风险。取较小值是一种安全措施，避免偶然出现但大数据包导致内存溢出。
```
## 2、Query Cache
```
uery_cache_type = 1
query_cache_size = 64M
设置为 0 时，则禁用查询缓存（尽管仍分配query_cache_size个字节的缓冲区）。
设置为 1 时 ，除非指定SQL_NO_CACHE，否则所有SELECT查询都将被缓存。
设置为 2 时，则仅缓存带有SQL CACHE子句的查询。
在禁用查询缓存的情况下启动服务器，则无法在运行时启用服务器
```
## 3、skip-locking
```
skip-locking
#避免MySQL的外部锁定，减少出错几率增强稳定性。
```
## 4、 skip-name-resolve
```
#禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！
```
## 5、back_log = 384
```
#back_log 参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。 如果系统在一个短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的TCP/IP连接的侦听队列的大小。不同的操作系统在这个队列大小上有它自 己的限制。 试图设定back_log高于你的操作系统的限制将是无效的。默认值为50。对于Linux系统推荐设置为小于512的整数。
```
## 6、key_buffer_size = 256M
```
#key_buffer_size指定用于索引的缓冲区大小，增加它可得到更好的索引处理性能。对于内存在4GB左右的服务器该参数可设置为256M或384M。注意：该参数值设置的过大反而会是服务器整体效率降低！
```
## 7、thread_stack = 256K
## 8、table_cache = 128K
## 9、sort_buffer_size = 6M
```
#查询排序时所能使用的缓冲区大小。注意：该参数对应的分配内存是每连接独占，如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 6 ＝ 600MB。所以，对于内存在4GB左右的服务器推荐设置为6-8M。
```
## 10、read_buffer_size = 4M
```
#读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每连接独享。
```
## 11、join_buffer_size = 8M
```
#联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享。
```
## 12、myisam_sort_buffer_size = 64M
## 13、table_cache = 512
## 14、thread_cache_size = 64
## 15、query_cache_size = 64M
```
指定MySQL查询缓冲区的大小。可以通过在MySQL控制台观察，如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的 情况；如果Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效率，那么可以考虑不用查询缓 冲；Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多。
```
## 16、tmp_table_size = 256M
## 17、max_connections = 768
```
#指定MySQL允许的最大连接进程数。如果在访问论坛时经常出现Too Many Connections的错误提 示，则需要增大该参数值。
```
## 18、max_connect_errors = 10000000
## 19、wait_timeout = 10
```
#指定一个请求的最大连接时间，对于4GB左右内存的服务器可以设置为5-10。
```
## 20、thread_concurrency = 8
```
#该参数取值为服务器逻辑CPU数量*2，在本例中，服务器有2颗物理CPU，而每颗物理CPU又支持H.T超线程，所以实际取值为4*2=8
```
## 21、skip-networking
```
#开启该选项可以彻底关闭MySQL的TCP/IP连接方式，如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项！否则将无法正常连接！
```
## 22、table_cache=1024
```
#物理内存越大,设置就越大.默认为2402,调到512-1024最佳
```
## 23、innodb_additional_mem_pool_size=4M
```
#默认为2M
```
## 24、innodb_flush_log_at_trx_commit=1
```
#设置为0就是等到innodb_log_buffer_size列队满后再统一储存,默认为1
```
## 25、innodb_log_buffer_size=2M
```
#默认为1M
```
## 26、innodb_thread_concurrency=8
```
#你的服务器CPU有几个就设置为几,建议用默认一般为8
```
## 27、key_buffer_size=256M
```
#默认为218，调到128最佳
```
## 28、tmp_table_size=64M
```
#默认为16M，调到64-256最挂
```
## 29、read_buffer_size=4M
```
#默认为64K
```
## 30、read_rnd_buffer_size=16M
```
#默认为256K
```
## 31、sort_buffer_size=32M
```
#默认为256K
```
## 32、thread_cache_size=120
```
#默认为60
```
## 33、query_cache_size=32M
## 34、key_buffer_size
```
key_buffer_size – 这对MyISAM表来说非常重要。如果只是使用MyISAM表，可以把它设置为可用内存的 30-40%。合理的值取决于索引大小、数据量以及负载 — 记住，MyISAM表会使用操作系统的缓存来缓存数据，因此需要留出部分内存给它们，很多情况下数据比索引大多了。尽管如此，需要总是检查是否所有的 key_buffer 都被利用了 — .MYI 文件只有 1GB，而 key_buffer 却设置为 4GB 的情况是非常少的。这么做太浪费了。如果你很少使用MyISAM表，那么也保留低于 16-32MB 的 key_buffer_size 以适应给予磁盘的临时表索引所需。
```
## 35、innodb_buffer_pool_size
```
innodb_buffer_pool_size – 这对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。MyISAM可以在默认的 key_buffer_size 设置下运行的可以，然而Innodb在默认的 innodb_buffer_pool_size 设置下却跟蜗牛似的。由于Innodb把数据和索引都缓存起来，无需留给操作系统太多的内存，因此如果只需要用Innodb的话则可以设置它高达 70-80% 的可用内存。一些应用于 key_buffer 的规则有 — 如果你的数据量不大，并且不会暴增，那么无需把 innodb_buffer_pool_size 设置的太大了。
```
## 36、innodb_additional_pool_size
```
innodb_additional_pool_size – 这个选项对性能影响并不太多，至少在有差不多足够内存可分配的操作系统上是这样。不过如果你仍然想设置为 20MB(或者更大)，因此就需要看一下Innodb其他需要分配的内存有多少。
```
## 37、innodb_log_file_size
```
innodb_log_file_size 在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高，但是要注意到可能会增加恢复时间。我经常设置为 64-512MB，跟据服务器大小而异。
```
## 38、innodb_log_buffer_size
```
innodb_log_buffer_size 默 认的设置在中等强度写入负载以及较短事务的情况下，服务器性能还可 以。如果存在更新操作峰值或者负载较大，就应该考虑加大它的值了。如果它的值设置太高了，可能会浪费内存 — 它每秒都会刷新一次，因此无需设置超过1秒所需的内存空间。通常 8-16MB 就足够了。越小的系统它的值越小。
```
## 39、innodb_flush_logs_at_trx_commit
```
innodb_flush_logs_at_trx_commit 是否为Innodb比MyISAM慢1000倍而头大？看来也许你忘了修改这个参数了。默认值是 1，这意味着每次提交的更新事务（或者每个事务之外的语句）都会刷新到磁盘中，而这相当耗费资源，尤其是没有电池备用缓存时。很多应用程序，尤其是从 MyISAM转变过来的那些，把它的值设置为 2 就可以了，也就是不把日志刷新到磁盘上，而只刷新到操作系统的缓存上。日志仍然会每秒刷新到磁盘中去，因此通常不会丢失每秒1-2次更新的消耗。如果设置 为 0 就快很多了，不过也相对不安全了 — MySQL服务器崩溃时就会丢失一些事务。设置为 2 指挥丢失刷新到操作系统缓存的那部分事务。
```
## 40、table_cache
```
table_cache — 打开一个表的开销可能很大。例如MyISAM把MYI文件头标志该表正在使用中。你肯定不希望这种操作太频繁，所以通常要加大缓存数量，使得足以最大限度 地缓存打开的表。它需要用到操作系统的资源以及内存，对当前的硬件配置来说当然不是什么问题了。如果你有200多个表的话，那么设置为 1024 也许比较合适（每个线程都需要打开表），如果连接数比较大那么就加大它的值。我曾经见过设置为 100,000 的情况。
```
## 41、thread_cache
```
thread_cache — 线程的创建和销毁的开销可能很大，因为每个线程的连接/断开都需要。我通常至少设置为 16。如果应用程序中有大量的跳跃并发连接并且 Threads_Created 的值也比较大，那么我就会加大它的值。它的目的是在通常的操作中无需创建新线程。
```
## 42、query_cache
```
query_cache — 如果你的应用程序有大量读，而且没有应用程序级别的缓存，那么这很有用。不要把它设置太大了，因为想要维护它也需要不少开销，这会导致MySQL变慢。通 常设置为 32-512Mb。设置完之后最好是跟踪一段时间，查看是否运行良好。在一定的负载压力下，如果缓存命中率太低了，就启用它。
```
## 43、sort_buffer_size
```
sort_buffer_size –如果你只有一些简单的查询，那么就无需增加它的值了，尽管你有 64GB 的内存。搞不好也许会降低性能。
```
## 参考配置
```shell
[root@host-92 secure]# cat /etc/my.cnf
[mysqld]
# datadir=/var/lib/mysql
datadir=/data/mysql
#socket=/var/lib/mysql/mysql.sock
socket=/data/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-bin=mysql-bin
server-id=24
max_connections=99999999999
max_user_connections=10000000
max_prepared_stmt_count=1048576
innodb_buffer_pool_size=608314589184
innodb_read_io_threads = 32
innodb_write_io_threads = 32
back_log = 512
key_buffer_size = 4096M
max_allowed_packet = 64M
thread_stack = 256K
table_cache = 128K
sort_buffer_size = 6M
join_buffer_size = 8M
myisam_sort_buffer_size = 64M
table_cache = 512
thread_cache_size = 64
query_cache_size = 64M
tmp_table_size = 256M
thread_concurrency = 64
table_cache=1024
innodb_additional_mem_pool_size=4M
innodb_flush_log_at_trx_commit=1
#innodb_thread_concurrency=64
key_buffer_size=128M
tmp_table_size=256M
read_buffer_size=4M
read_rnd_buffer_size=16M
sort_buffer_size=32M
thread_cache_size=256
query_cache_size=60M
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
[client]
port=3306
socket=/data/mysql/mysql.sock
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
```
# 七、修改 global 变量
⚠：重启数据库需要重新设置，尽在本次进程存活期间生效！
```mysql
set global table_open_cache = 40000000;
set global max_connections = 1048576;
set global sort_buffer_size=9999999999999999999;
set global innodb_flush_log_at_trx_commit=2;
show variables like '%max_connections%';
show variables like '%max_user_connections%';
show variables like 'max_prepared_stmt_count';
show status like '%Threads%';
show global status like 'com_stmt%';
show variables like '%table_open%';
show engine innodb status\G;
show variables like '%innodb_read_io_thread%';
show variables like '%performance_schema%';
SELECT @@innodb_buffer_pool_size/1024/1024/1024;
```
## 1、innodb_buffer_pool_size
```shell
约等于 RAM 大小*75%
[root@host-92 log]# cat /proc/meminfo
MemTotal: 792076748 kB
innodb_buffer_pool_size=594057216
innodb_buffer_pool_size
用于缓存索引和数据的内存大小，这个当然是越多越好， 数据读写在内存中非常快， 减少了对磁盘的读写。 当数据提交或满足检查点条件后才一次性将内存数据刷新到磁盘中。然而内存还有操作系统或数据库其他进程使用， 根据经验，推荐设置innodb-buffer-pool-size为服务器总可用内存的75%。 若设置不当， 内存使用可能浪费或者使用过多。 对于繁忙的服务器， buffer pool 将划分为多个实例以提高系统并发性， 减少线程间读写缓存的争用。buffer pool 的大小首先受 innodb_buffer_pool_instances 影响， 当然影响较小。
```
## 2、binlog size大小
```
默认设置为1GB
```
## 3、innodb_read_io_threads和innodb_write_io_threads
```shell
innodb_read_io_threads和innodb_write_io_threads，取代了innodb_file_io_threads如此调整后，在Linux平台上就可以根据CPU核数来更改相应的参数值了，默认是4。
假如CPU是2颗8核的，那么可以设置：
innodb_read_io_threads = 8
innodb_write_io_threads = 8
如果数据库的读操作比写操作多，那么可以设置：
innodb_read_io_threads = 10
innodb_write_io_threads = 6
show engine innodb status\G;
写入my.cnf
```
## 4、performance_schema
```
预期：off
```
## 5、主从复制同步模式
```
默认是：异步复制
```
## 6、线程池
```
不开启
```
## 7、Innodb_buffer_pool_pages_data
```
Innodb buffer pool缓存池中包含数据的页的数目，包括脏页。单位是page。
eg、show global status like 'Innodb_buffer_pool_pages_data';
```
## 8、Innodb_buffer_pool_pages_total
```
innodb buffer pool的页总数目。单位是page。
eg：show global status like 'Innodb_buffer_pool_pages_total';
```
## 9、show global status like 'Innodb_page_size';
```
查看@@innodb_buffer_pool_size大小，单位字节
SELECT @@innodb_buffer_pool_size/1024/1024/1024; #字节转为G
在线调整InnoDB缓冲池大小，如果不设置，默认为128M
```
## 10、set global innodb_buffer_pool_size = 4227858432;
单位字节
```
计算Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%
当结果 > 95% 则增加 innodb_buffer_pool_size， 建议使用物理内存的 75%
当结果 < 95% 则减少 innodb_buffer_pool_size，
建议设置大小为： Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (1024*1024*1024)
```
