引言
本篇将介绍最近在
Ubuntu Server 20.04LTS上折腾MySQL时的一些笔记分享。
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的
RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。
MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。
MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL
软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。
在对MySQL进行部署与配置前，你需要执行👇：
# refresh software list
sudo apt-get update
# [Optional] Upgrade your local software
sudo apt-get upgrade
# install MySQL
sudo apt-get install mysql-server
（一）修改MySQL数据源Data文件夹位置
Tips: 原有MySQL的Data路径为/var/lib/mysql/，当我们进行长期存储时，硬盘空间将会持续吃紧，故本章介绍如何迁移MySQL中的Data路径以将MySQL的数据保存更换到新硬盘。
Step1: 关闭MySQL服务
# 检查原有Data目录位置，默认的MySQL中Data文件夹位置位于 /var/lib/mysql/
sudo mysql
# 输出结果：
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.21-0ubuntu0.20.04.3 (Ubuntu)
Copyright (c) 2000, 2020, 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> select @@datadir;
+-------------------+
| @@datadir |
+-------------------+
| /var/lib/mysql/ |
+-------------------+
1 row in set (0.00 sec)
# 关闭MySQL服务
sudo systemctl stop mysql
Step2: 检查MySQL运行状况
sudo systemctl status mysql
# 输出结果：
• mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: inactive (dead) since Mon 2020-08-03 03:32:37 UTC; 1h 55min ago
Main PID: 5065 (code=exited, status=0/SUCCESS)
Status: "Server shutdown complete"
Aug 03 03:06:59 ubuntu-server systemd[1]: Starting MySQL Community Server...
Aug 03 03:07:00 ubuntu-server systemd[1]: Started MySQL Community Server.
Aug 03 03:32:35 ubuntu-server systemd[1]: Stopping MySQL Community Server...
Aug 03 03:32:37 ubuntu-server systemd[1]: mysql.service: Succeeded.
Aug 03 03:32:37 ubuntu-server systemd[1]: Stopped MySQL Community Server.
Step3: 迁移原MySQL数据文件
Tips: 假定待迁移Data目录为: /data/test
sudo rsync -av /var/lib/mysql /data/test
Step4: 备份原MySQL数据文件
sudo mv /var/lib/mysql /var/lib/mysql.bak
Step5: 交接目标目录权限
sudo chown -R mysql:mysql /data/test
Step6: 修改MySQL配置文件
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
Tips: 修改[mysqld]组中datadir的属性值，新增一行datadir键值。
······
[mysqld]
#
# * Basic Settings
#
user = mysql
# pid-file = /var/run/mysqld/mysqld.pid
# socket = /var/run/mysqld/mysqld.sock
# port = 3306
# datadir = /var/lib/mysql
datadir = /data/test/mysql
······
Step7: 修改apparmor的别名配置文件
sudo vim /etc/apparmor.d/tunables/alias
Tips: 新增一项别名，并指向目标位置。
# Alias rules can be used to rewrite paths and are done after variable
# resolution. For example, if '/usr' is on removable media:
# alias /usr/ -> /mnt/usr/,
#
# Or if mysql databases are stored in /home:
# alias /var/lib/mysql/ -> /home/mysql/,
alias /var/lib/mysql/ -> /data/test/mysql/,
Step8: 重启系统apparmor服务
sudo invoke-rc.d apparmor reload
sudo systemctl restart apparmor
Step9: 越过MySQL检查
sudo mkdir /var/lib/mysql/mysql -p
Step10: 重启MySQL服务
sudo systemctl start mysql
sudo systemctl status mysql
# 输出结果：
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Mon 2020-08-03 05:42:18 UTC; 7s ago
Process: 10205 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status>
Main PID: 10228 (mysqld)
Status: "Server is operational"
Tasks: 39 (limit: 2249)
Memory: 334.7M
CGroup: /system.slice/mysql.service
└─10228 /usr/sbin/mysqld
Aug 03 05:42:17 ubuntu-server systemd[1]: Starting MySQL Community Server...
Aug 03 05:42:18 ubuntu-server systemd[1]: Started MySQL Community Server.
End: 修改后的MySQL中Data位置
sudo mysql
# 输出结果：
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.21-0ubuntu0.20.04.3 (Ubuntu)
Copyright (c) 2000, 2020, 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> select @@datadir;
+-------------------+
| @@datadir |
+-------------------+
| /data/test/mysql/ |
+-------------------+
1 row in set (0.00 sec)
（二）通过mysqlslap对MySQL进行压力测试
mysqlLslap
先来看看MySQL官方对mysqlslap工具的介绍：mysqlslap is a diagnostic program designed to emulate client load for a MySQL server and to report the timing of each stage. It works as if multiple clients are accessing the server.
简明概括就是mysqlslap是MySQL5.1.4之后自带的benchmark基准测试工具，该工具可以模拟多个客户端同时并发的向服务器发出查询更新，给出了性能测试数据而且提供了多种引擎的性能比较。
该工具有诸多可选项可供选择以实现大部分场景下对MySQL模拟进行压力测试。
Tips: 以下是常用的部分参数
# 连接到的MySQL服务器的主机名(或IP地址)，默认为本地localhost
--host=host_name
# 连接MySQL服务时用的用户名(如：root)
--user=root
# 连接MySQL服务时用的密码(如：root)
--password
# 指定测试时生成的临时数据库名称(如：test)
--create-schema=test
# 工具自动生成测试用SQL语句
--auto-generate-sql
# 选择生成的SQL语句的格式(write, read, update, mixed)
--auto-generate-sql-load-type=write
# 执行的SQL总数量
--number-of-queries=100000
# 并发数，模拟多少个客户端同时执行query
--concurrency=200
# 引擎(如：myisam,innodb)
--engines=innodb
# 迭代的实验次数
--iterations=100
以下是我的机器上对MySQL进行一组压力测试的结果
写入:
# 写入脚本
sudo mysqlslap --user=root --password=root --create-schema=test --auto-generate-sql --auto-generate-sql-load-type=write --concurrency=20 --number-of-queries=1000 --iterations=100
# 结果
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 0.205 seconds
Minimum number of seconds to run all queries: 0.173 seconds
Maximum number of seconds to run all queries: 0.395 seconds
Number of clients running queries: 20
Average number of queries per client: 50
读取:
# 读取脚本
sudo mysqlslap --user=root --password=root --create-schema=test --auto-generate-sql --auto-generate-sql-load-type=read --concurrency=20 --number-of-queries=1000 --iterations=100
# 结果
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Average number of seconds to run all queries: 0.132 seconds
Minimum number of seconds to run all queries: 0.120 seconds
Maximum number of seconds to run all queries: 0.191 seconds
Number of clients running queries: 20
Average number of queries per client: 50
