MySQL部署

1.准备工作目录和环境

1.创建目录
[root@mysql-114 ~]# mkdir /zy/{app,data,logs,bak} -p
2.查看防火墙是否关闭
[root@mysql-114 ~]# systemctl status firewalld
3.查看selinux安全策略是否关闭
[root@mysql-114 ~]# getenforce 
4.清除系统自带的mariadb数据库服务相关的程序包
[root@mysql-114 ~]# rpm -qa|grep mariadb
[root@mysql-114 ~]# yum remove -y mariadb-libs
5.安装数据库服务程序所需的依赖软件包
[root@mysql-114 ~]# yum install -y libaio-devel
6.创建数据库服务管理用户信息
[root@mysql-114 ~]# useradd mysql
7.创建数据库服务相关目录并进行目录信息进行授权
[root@mysql-114 ~]# chown -R mysql. /zy/

2.上传mysql安装包，并解压

[root@mysql-114 ~]# cd /zy/app/
[root@mysql-114 app]# ll
total 650144
-rw-r--r-- 1 root root 665746960 May 29 14:26 mysql-commercial-8.0.35-linux-glibc2.12-x86_64.tar.xz
[root@mysql-114 app]# tar xf mysql-commercial-8.0.35-linux-glibc2.12-x86_64.tar.xz -C /zy/app/

3.创建软链接

[root@mysql-114 app]# ln -s /zy/app/mysql-commercial-8.0.35-linux-glibc2.12-x86_64 /usr/local/mysql

4.配置环境变量

[root@mysql-114 ~]# vim /etc/profile
#在最下面增加以下内容：
export MYSQL_HOME=/usr/local/mysql
export PATH=$PATH:$MYSQL_HOME/bin

[root@mysql-114 ~]# source /etc/profile   #重新加载系统环境变量

5.查看是否配置成功

[root@mysql-114 ~]# mysql --version
mysql  Ver 8.0.35-commercial for Linux on x86_64 (MySQL Enterprise Server - Commercial)

[root@mysql-114 ~]# mysql -V
mysql  Ver 8.0.35-commercial for Linux on x86_64 (MySQL Enterprise Server - Commercial)

6.修改my.cnf配置文件

[root@mysql-114 ~]# cat /etc/my.cnf
[mysql]
socket=/tmp/mysql.sock

[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/zy/data
socket=/tmp/mysql.sock

7.初始化mysql

[root@mysql-114 ~]# mysqld --defaults-file=/etc/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/zy/data --initialize
2024-08-13T13:58:59.334951Z 0 [System] [MY-013169] [Server] /zy/app/mysql-commercial-8.0.35-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.35-commercial) initializing of server in progress as process 1516
2024-08-13T13:58:59.340770Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-08-13T13:59:01.565838Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-08-13T13:59:08.618023Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: kV,DlQ/kw9ps              #这里就是初始化默认管理员密码
#初始化操作参数：
--initialize-insecure     #表示不安全方式初始化（无管理员密码）
--initialize              #表示安全方式初始化（有随机默认管理员密码）

# 数据库服务初始化拨错信息
[报错信息]：
mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
[解决方法]：
yum install -y libaio-devel

[报错信息]：
initialize specified but the data directory has files in it. Aborting.
The designated data directory /zy/data/ is unusable. You can remove all files that the server added to it.
[解决方法]：
rm -rf /zy/data/*

8.启动mysql服务

1.前台启动
[root@mysql-114 ~]# mysqld_safe --defaults-file=/etc/my.cnf --user=mysql

2.后台启动
[root@mysql-114 ~]# nohup /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql >> /zy/logs/local_start.log 2>&1 &
[root@mysql-114 ~]# nohup /usr/local/mysql/bin/mysqladmin shutdown -uroot -p'数据库密码' --socket /tmp/mysql.sock >> /zy/logs/local_stop.log 2>&1 &     #停止数据库

3.设置数据库服务程序启动运行文件
[root@mysql-114 ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

4.启动数据库服务程序利用脚本文件
[root@mysql-114 ~]# /etc/init.d/mysqld start
[root@mysql-114 ~]# /etc/init.d/mysqld stop
[root@mysql-114 ~]# service mysqld start
[root@mysql-114 ~]# service mysqld stop

5.启动数据库服务程序利用脚本文件 (systemd方式)
[root@mysql-114 ~]# systemctl enable mysqld
mysqld.service is not a native service, redirecting to /sbin/chkconfig.
Executing /sbin/chkconfig mysqld on
#将service管理服务方式转换为systemd管理服务方式:
[root@mysql-114 ~]# systemctl start mysqld
[root@mysql-114 ~]# systemctl is-active mysqld

9.验证数据库是否启动成功

 [root@mysql-114 ~]# netstat -lntup|grep 3306
 [root@mysql-114 ~]# ss -ntl|grep 3306
 LISTEN     0      128       [::]:3306                  [::]:*                  
 LISTEN     0      70        [::]:33060                 [::]:*   
 [root@mysql-114 ~]# ps -ef|grep mysqld
 
 #特殊说明：在启动mysql数据库服务时，可能看到33060端口信息，此端口信息主要实现mysqlx协议的通讯过程；
 #利用mysqlx协议可以实现利用mysql-shell功能组件，对数据进行key-value操作，即识别json文件信息，进行远程管理；

10.修改默认密码并开启远程连接

[root@mysql-114 ~]# mysql -u root -p
Enter password:                      #第一次需输入初始化默认密码
...
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Zy200211@';  #修改密码
Query OK, 0 rows affected (0.01 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set host = '%' where user = 'root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select host,user,plugin,authentication_string from mysql.user;    #查询数据库服务用户信息

11.主从复制（二进制日志文件和位置）

1.修改主库配置文件
vim /etc/my.cnf
[mysql]
socket=/tmp/mysql.sock

[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/zy/data
socket=/tmp/mysql.sock
server-id = 1                # 唯一 ID
log_bin=binlog               # 启用二进制日志
binlog-do-db = test_db       # 需要复制的数据库（多个数据库可重复此参数）
expire-logs-days = 7         # 二进制日志过期时间
binlog_format = ROW          # 日志格式（推荐 ROW）

2.重启mysql
systemctl restart mysqld

3.创建复制用户
mysql -u root -p
mysql> CREATE USER 'slave1'@'%' IDENTIFIED WITH mysql_native_password BY 'your_slave_password';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%';
mysql> FLUSH PRIVILEGES;
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000008 |      823 | test_db      |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

4.修改从库配置文件
vim /etc/my.cnf
[mysql]
socket=/tmp/mysql.sock

[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/zy/data
socket=/tmp/mysql.sock
server-id = 2                # 每个从库必须唯一
relay-log = mysql-relay-bin  # 中继日志
log_bin=binlog               # 可选：从库也可作为其他从库的主库
read-only = 1                # 从库设置为只读（超级用户除外）

5.重启mysql
systemctl restart mysqld
mysql -u root -p

6.配置主从复制
mysql> CHANGE MASTER TO
    ->     MASTER_HOST='10.0.0.114',
    ->     MASTER_USER='slave1',
    ->     MASTER_PASSWORD='slave123456',
    ->     MASTER_LOG_FILE='binlog.000008',
    ->     MASTER_LOG_POS=823; 
Query OK, 0 rows affected, 8 warnings (0.02 sec)

mysql> FLUSH PRIVILEGES;
mysql> START SLAVE;             #启动复制
mysql> SHOW SLAVE STATUS\G      #检查复制状态
#关键状态检查：
Slave_IO_Running: Yes        # 必须为 Yes
Slave_SQL_Running: Yes       # 必须为 Yes
Seconds_Behind_Master: 0     # 延迟时间，0 表示无延迟

12.验证主从复制

1. 在主库创建测试数据
CREATE DATABASE test_db;
USE test_db;
CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50));
INSERT INTO users (name) VALUES ('Alice'), ('Bob');

2. 在从库验证数据同步
USE test_db;
SELECT * FROM users;
+----+-------+
| id | name  |
+----+-------+
|  1 | Alice |
|  2 | Bob   |
+----+-------+
2 rows in set (0.00 sec)

13.配置文件优化（生产环境根据业务需求）

cat /etc/my.cnf
[mysqld]
#内存分配（关键）：
innodb_buffer_pool_size = 2G       # 主库建议较大值
innodb_buffer_pool_instances = 8   # 缓冲池实例数，建议为 CPU 核心数的一半
innodb_log_file_size = 256M        # 单个日志文件大小
innodb_log_files_in_group = 3      # 日志文件数量
innodb_log_buffer_size = 16M       # 日志缓冲区大小
#写入性能优化：
sync_binlog = 1  # 确保二进制日志写入磁盘
innodb_flush_log_at_trx_commit = 2  # 性能与安全的平衡   # 性能与持久性的平衡（0=最快，2=中等，1=最安全）
innodb_thread_concurrency = 0     # 0 表示不限制，建议设为 CPU 核心数*2
innodb_read_io_threads = 8        # 读 I/O 线程数
innodb_write_io_threads = 8       # 写 I/O 线程数
max_connections = 1000  # 根据业务需求调整
bind-address = 127.0.0.1 #限制远程访问，仅允许本地访问，通过中间件转发请求 （根据业务需求调整）
general_log = 1
general_log_file = /var/log/mysql/mysql.log  #审计日志
#查询优化：
sort_buffer_size = 4M             # 每个排序操作的缓冲区大小
join_buffer_size = 4M             # 连接操作的缓冲区大小
slow_query_log = 1                # 启用慢查询日志
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1               # 超过 1 秒的查询记录
log_queries_not_using_indexes = 1 # 记录未使用索引的查询
#主从复制优化：
（1）主库配置：
# 二进制日志配置
log-bin = mysql-bin               # 启用二进制日志
binlog_format = ROW               # 推荐 ROW 格式（更安全，支持更多特性）
expire_logs_days = 7              # 日志保留天数
max_binlog_size = 100M            # 单个日志文件最大 size
binlog_checksum = CRC32           # 日志校验

# GTID 模式（强烈推荐）
gtid_mode = ON
enforce_gtid_consistency = ON

（2）从库配置
# 中继日志
relay_log = mysql-relay-bin
relay_log_recovery = 1            # 从库崩溃恢复时自动清理未完成的中继日志

# 复制线程
slave_parallel_type = LOGICAL_CLOCK  # 基于逻辑时钟的并行复制
slave_parallel_workers = 8           # 并行工作线程数
master_info_repository = TABLE       # 主库信息存储在表中
relay_log_info_repository = TABLE    # 中继日志信息存储在表中

# 从库只读（除超级用户外）
read_only = 1
super_read_only = 1                # 严格只读，超级用户也无法写入

#安全加固配置：
（1）网络与认证
# 绑定 IP，限制外部访问
bind-address = 127.0.0.1           # 仅允许本地访问，通过中间件或代理访问
skip-name-resolve = 1             # 禁用 DNS 解析，提高安全性

# 密码策略（MySQL 8.0 默认要求强密码）
default_authentication_plugin = caching_sha2_password

# 限制连接超时
wait_timeout = 28800              # 连接闲置超时时间（秒）
interactive_timeout = 28800

（2）数据加密
# 开启 TLS（需要生成证书）
ssl = ON
ssl_ca = /etc/mysql/certs/ca.pem
ssl_cert = /etc/mysql/certs/server-cert.pem
ssl_key = /etc/mysql/certs/server-key.pem

# 加密临时表和临时文件
internal_tmp_disk_storage_engine = InnoDB
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:128M

（3）文件权限
# 日志和数据目录权限
log-error = /var/log/mysql/error.log
pid-file = /var/run/mysqld/mysqld.pid
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log   # 错误日志 
log_timestamps = SYSTEM                # 使用系统时区记录时间
# 确保目录权限正确
# chown -R mysql:mysql /var/lib/mysql
# chmod 700 /var/lib/mysql

#审计日志：
# 启用审计插件（MySQL 企业版或社区版通过插件实现）
plugin-load-add = audit_log.so
audit_log_format = JSON
audit_log_file = /var/log/mysql/audit.log
audit_log_policy = ALL             # 记录所有操作
audit_log_exclude_accounts = 'repl_user@%'  # 排除复制用户

#InnoDB 存储引擎配置
innodb_rollback_segments=128       # 回滚段数量
innodb_undo_log_truncate=1         # 启用 undo 日志截断
innodb_max_undo_log_size=4G        # 最大 undo 日志大小
innodb_data_file_path=ibdata1:12M:autoextend # 数据文件路径和初始大小
innodb_log_files_in_group=4        # 日志文件数量
innodb_log_file_size=2G            # 单个日志文件大小
innodb_temp_data_file_path=ibtmp1:16M:autoextend # 临时数据文件
innodb_open_files=409600           # 允许打开的 InnoDB 文件数量
innodb_change_buffering=all        # 变更缓冲策略（提高写入性能）
innodb_adaptive_hash_index=ON      # 自适应哈希索引
innodb_autoinc_lock_mode=2         # 自增锁模式（减少锁争用）
innodb_buffer_pool_size=128G       # 缓冲池大小（关键性能参数）
innodb_buffer_pool_instances=8     # 缓冲池实例数
innodb_max_dirty_pages_pct=50      # 脏页最大百分比
innodb_old_blocks_pct=25           # 旧块百分比（LRU 算法）
innodb_old_blocks_time=3000        # 旧块停留时间（毫秒）
innodb_log_buffer_size=64M         # 日志缓冲区大小
innodb_buffer_pool_chunk_size=128M # 缓冲池块大小
innodb_page_cleaners=8             # 页面清理线程数
innodb_flush_method=O_DIRECT       # 刷新方法（直接 I/O）
innodb_use_native_aio              # 使用原生 AIO
innodb_adaptive_flushing           # 自适应刷新
innodb_flush_log_at_trx_commit=1   # 事务提交时刷新日志（最安全）
innodb_io_capacity=20000           # I/O 能力（根据存储性能调整）
innodb_read_io_threads=16          # 读 I/O 线程数
innodb_write_io_threads=16         # 写 I/O 线程数
innodb_read_ahead_threshold=56     # 预读阈值
innodb_doublewrite                 # 双写缓冲区（提高数据安全性）
innodb_purge_threads=1             # 清除线程数
innodb_purge_batch_size=20         # 清除批处理大小
innodb_stats_persistent=1          # 持久化统计信息
innodb_flush_neighbors=0           # 刷新相邻页（SSD 建议 0）
innodb_file_per_table              # 每个表独立文件
innodb_autoextend_increment=32     # 自动扩展增量（MB）
innodb_fast_shutdown=1             # 快速关闭模式
autocommit=1                       # 自动提交
transaction-isolation=READ-COMMITTED # 事务隔离级别
innodb_table_locks                 # 启用表锁
lock_wait_timeout=600              # 锁等待超时（秒）
innodb_lock_wait_timeout=30        # InnoDB 锁等待超时（秒）
innodb_spin_wait_delay=6           # 自旋等待延迟
innodb_sync_spin_loops=30          # 同步自旋循环次数
innodb_commit_concurrency=0        # 提交并发度（0 表示无限制）
innodb_thread_concurrency=0        # 线程并发度（0 表示无限制）