MySQL 的运维与优化

MySQL 服务器的运维与优化

一、对 MySQL 服务器优化的作用

提高性能：通过优化查询、索引和服务器参数，可以显著提高 MySQL 的查询速度和整体性能。
减少资源消耗：优化可以减少 CPU、内存和磁盘 I/O 的使用，从而降低服务器的负载。
提高稳定性：通过优化，可以减少服务器崩溃和宕机的风险，确保系统的稳定运行。
提升用户体验：优化后的 MySQL 服务器可以提供更快的响应时间和更高的可靠性，从而提升用户体验。
支持扩展：优化可以为未来的扩展和增长奠定基础，确保系统能够应对更大的数据量和更高的访问量。

二、MySQL 运维

1. 安装数据库

首先配置本地 YUM 源

# 首先将 /etc/yum.repos.d/ 目录下的所有文件都移动到 /media 目录下
$ sudo mv /etc/yum.repos.d/* /media/

# 然后使用 wget 命令下载 gpmall-repo，然后解压
$ sudo wget https://moka.anitsuri.top/images/gpmall/gpmall-repo.zip
$ sudo unzip gpmall-repo.zip

$ sudo vi /etc/yum.repos.d/local.repo
# 填入以下内容
[gpmall]
name=gpmall
baseurl=file:///root/gpmall-repo
gpgcheck=0
enabled=1

# 然后刷新 yum 源（清除 yum 缓存 + 展示现在的 repo 列表）
$ sudo yum clean all && yum repolist

然后安装并启动数据库

$ sudo yum install -y mariadb mariadb-server
# 启动数据库
$ sudo systemctl start mariadb

# 运行安全脚本（它可以 设置 root 用户密码、移除匿名用户、禁止 root 用户远程登录、移除测试数据库 等）
$ mysql_secure_installation 

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

# 这里需要输入当前 root 用户的密码
# 如果刚安装 MariaDB 并还没有设置 root 用户密码，那么密码是空的，按回车即可
Enter current password for root (enter for none):  #默认按回车
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

# 这里问你是否需要设置 root 用户的密码
# 按 Y 进行设置
Set root passworNew password:  #这里输入密码123456
Re-enter new password:  #确认密码：再输一遍123456
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB 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.

# 是否移除匿名用户
# 按 Y 进行移除
Remove anonymous users? [Y/n] d? [Y/n] 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.

# 是否禁用 root 用户远程登录
# 按 Y 禁用远程登录
Disallow root login remotely? [Y/n] n
 ... skipping.

By default, MariaDB 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.

# 是否移除测试数据库 test 及其权限
# 按 Y 移除
Remove test database and access to it? [Y/n] y
 - 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.

# 是否重新加载权限表，以确保所有更改立刻生效
# 按 Y 重新加载
Reload privilege tables now? [Y/n] 
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

2. 创建数据库

# 以 root 用户身份，使用密码 123456 创建一个名称为 test 数据库
$ mysqladmin -uroot -p123456 create test

# 在 test 数据库中创建一个名为 tables 数据表
# 首先先进入数据库
$ mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 10.3.18-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

# 然后进入 test 数据库
MariaDB [(none)]> USE test;
Database changed
# 创建一个名为 tables 的新表，如果该表不存在
MariaDB [test]> CREATE TABLE IF NOT EXISTS `tables` (
    # 定义一个名为 tables_id 的整数列，并设置为自动递增，每次插入新记录时其值会自动增加
    ->     `tables_id` INT AUTO_INCREMENT,
    # 定义一个名为 tables_title 的可变长度字符串列，最大长度为100字符，并且不能为空
    ->     `tables_title` VARCHAR(100) NOT NULL,
    # 定义一个名为 tables_author 的可变长度字符串列，最大长度为40字符，并且不能为空
    ->     `tables_author` VARCHAR(40) NOT NULL,
    # 定义一个名为 tables_date 用于储存日期的日期类型列
    ->     `tables_date` DATE,
    # 将 tables_id 列设置为表的主键，来确保每条记录都有唯一的标识
    ->     PRIMARY KEY (`tables_id`)
    # 指定表的存储引擎为 InnoDB，并将默认字符集设置为 utf8，用于存储多语言字符数据
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.008 sec)

# 然后查看表
MariaDB [test]> SHOW tables;
+----------------+
| Tables_in_test |
+----------------+
| tables         |
+----------------+
1 row in set (0.001 sec)

2.2

3. 备份数据库

# 导出整个数据库
# 导出 test 数据库，使用 root 123456 进行登录，并且把导出备份在当前目录下的 test.sql 文件里
$ mysqldump -uroot -p123456 test > test.sql

# 导出一个表
# 导出 test 数据库中的 tables 表，使用 root 123456 进行登录，并且把导出备份在当前目录下的 test_tables.sql 文件里
$ mysqldump -uroot -p123456 test tables > test_tables.sql

删除 test 数据库进行导入测试，有两种方式可以导入

用 mysql 命令，把数据库文件恢复到指定数据库

# 首先删除 test 数据库
$ mysqladmin -uroot -p123456 drop test
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

# 这里询问是否删除，输入 Y 进行确认删除
Do you really want to drop the 'test' database [y/N] y
Database "test" dropped

# 然后登录数据库
$ mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 22
Server version: 10.3.18-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

# 创建一个名为 test 的数据库
MariaDB [(none)]> CREATE DATABASE test;
Query OK, 1 row affected (0.003 sec)

MariaDB [(none)]> Ctrl-C -- exit!
Aborted

# 导入数据库 test.sql 到 数据库 test
$ mysql -uroot -p123456 test < test.sql

2.3.1

可以用 source 语句导入数据库，把数据库文件恢复到指定的数据库

# 首先删除 test 数据库
$ mysqladmin -uroot -p123456 drop test
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

# 然后登录数据库
$ mysql -uroot -p123456
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 22
Server version: 10.3.18-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

# 创建一个名为 test 的数据库
MariaDB [(none)]> CREATE DATABASE test;
Query OK, 1 row affected (0.001 sec)

# 切换到 test 数据库，并导入
MariaDB [(none)]> USE test;
Database changed
MariaDB [test]> source /root/test.sql

2.3.2

4. 添加用户并授权

# 授权 root 用户可以在任何节点 test 数据库下的所有表， '%' 代表所有节点机器
MariaDB [(none)]> GRANT ALL PRIVILEGES ON test.* TO 'root'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.017 sec)

# 授权 root 用户可以在任何节点 test 数据库下的所有表， 'localhost' 代表所有本机
MariaDB [(none)]> GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.002 sec)

# 添加 root 用户对 test 数据库授予 增、删、改、查 的权限
MariaDB [(none)]> GRANT SELECT,INSERT,DELETE,UPDATE ON test.* TO 'root'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.001 sec)

2.4

三、MySQL 数据库优化

# 编辑数据库配置文件
$ sudo vi /etc/my.cnf
# 添加以下内容来进行优化

[mysqld]
thread_concurrency = 64		# CPU核数 * 2
max_connections=1500		# 最大连接（用户）数。每个连接MySQL的用户均算作一个连接
max_connect_errors=30		# 最大失败连接限制
bulk_insert_buffer_size = 32M	# 批量插入数据缓存大小
query_cache_type=1			# 查询缓存  (0 = off、1 = on、2 = demand)
query_cache_size = 64M		# 指定mysql查询缓冲区大小
max_allowed_packet = 128M	# 通信缓冲大小
read_buffer_size = 8M		# 顺序读取数据缓冲区使用内存
read_rnd_buffer_size = 32M	# 随机读取数据缓冲区使用内存

以下是常见优化数据库的命令

配置条目	描述
`innodb_buffer_pool_size`	设置InnoDB缓冲池的大小，通常设置为物理内存的70-80%。
`innodb_log_file_size`	设置InnoDB日志文件的大小，较大的日志文件可以提高写入性能。
`innodb_flush_log_at_trx_commit`	控制InnoDB事务日志刷新行为，值为`1`时最安全，值为`2`或`0`提高性能。
`innodb_file_per_table`	为每个表创建一个独立的表空间文件，有助于减少表碎片。
`query_cache_size`	设置查询缓存的大小，缓存经常执行的SELECT查询结果。
`query_cache_type`	启用查询缓存，值为`1`表示总是缓存，`2`表示按需缓存。
`max_connections`	设置最大允许的并发连接数，适当增加此值以支持更多用户。
`thread_cache_size`	缓存线程数量，提升高并发下的性能，减少线程创建开销。
`table_open_cache`	控制表缓存的数量，较高的值能减少表打开和关闭的频率。
`tmp_table_size`	设置内存临时表的最大大小，超过该大小的表将写入磁盘。
`max_heap_table_size`	定义内存表的最大大小，优化大数据量的临时表操作。
`key_buffer_size`	设置用于MyISAM表索引的缓冲区大小，较大值有助于索引查找。
`log_bin`	启用二进制日志，用于备份和复制，记录所有更改操作。
`expire_logs_days`	设置二进制日志的保留天数，自动删除过期的日志文件。
`slow_query_log`	启用慢查询日志，帮助识别和优化性能较差的查询。
`slow_query_log_file`	指定慢查询日志的输出文件路径。
`long_query_time`	定义慢查询的时间阈值，超过此时间的查询将被记录。
`thread_concurrency`	并发线程数，建议为CPU核心数乘以2
`max_connections`	最大连接（用户）数。每个连接MySQL的用户均算作一个连接
`max_connect_errors`	最大失败连接限制
`bulk_insert_buffer_size`	批量插入数据缓存大小，可以有效提高写入效率，默认为8 MB
`query_cache_type`	控制着查询缓存功能的开启的关闭。0时表示关闭，1时表示打开，2表示只要select 中明确指定SQL_CACHE才缓存
`query_cache_size`	指定MySQL查询缓冲区的大小，用来缓冲select的结果，并在下一次同样查询的时候不再执行查询而直接返回结果，根据Qcache_lowmem_prunes的大小，来查看当前的负载是否足够高，在数据库写入量或是更新量也比较大的系统，该参数不适合分配过大。而且在高并发，写入量大的系统，建议把该功能禁掉。属重点优化参数（主库增删改-MyISAM）
`max_allowed_packet`	设定在网络传输中一次可以传输消息的最大值，系统默认为1 MB，最大可1 GB
`read_buffer_size`	来做MYISAM表全表扫描的缓冲大小，对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且用户认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能
`read_rnd_buffer_size`	随机读（查询操作）缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大

#MySQL

MySQL 的运维与优化

https://moka.anitsuri.top/2024/09/04/MySQL_Maintenance/

作者

アニつり

发布于

2024年9月4日

许可协议

认识 Docker 容器技术与镜像仓库上一篇

企业商城单节点部署下一篇

MySQL 的运维与优化

MySQL 服务器的运维与优化

一、 对 MySQL 服务器优化的作用

二、MySQL 运维

1. 安装数据库

2. 创建数据库

3. 备份数据库

4. 添加用户并授权

三、MySQL 数据库优化

一、对 MySQL 服务器优化的作用