mysql下载与安装

https://dev.mysql.com
https://downloads.mysql.com/archives/community/

下载带bundle的：

可以下载二进制的包

---

MySQL数据库创建流程（使用官网二进制包部署）
第一步：创建需要的文件夹
存放数据的目录
mkdir -p /data/mysql
存放mysql配置文件的目录
mkdir -p /home/cmcs/package/mysql/etc
存放mysql日志文件的目录
mkdir -p /home/cmcs/package/mysql/log
第二步：把上传好的解压tar包到目标文件夹
二进制包
tar -xf mysql-8.0.44-linux-glibc2.28-x86_64.tar.xz -C /home/cmcs/package/mysql

第三步：创建用户及文件夹权限
groupadd mysql
useradd -M -g mysql -s /sbin/nologin -c "MySQL runtime user" mysql

chown -R mysql:mysql /home/cmcs/package/mysql
chmod 755 /home/cmcs/package/mysql

chown mysql:mysql /home/cmcs/package/mysql/etc
chown mysql:mysql /home/cmcs/package/mysql/log # 日志目录单独授权
chmod 750 /home/cmcs/package/mysql/log
chown -R mysql:mysql /data/mysql
chmod 755 /data/mysql

第四步：配置文件放到对应的目录下
可以直接上传到对应目录，也可以上传到其他目录，然后复制到对应目录下
cp /当前目录/my.cnf /home/cmcs/package/mysql/etc/my.cnf
cp /当前目录/mysqld.service /etc/systemd/system/mysqld.service

第五步：权限验证
namei -l /home/cmcs/package/mysql/etc/my.cnf # 逐级看权限
su -s /bin/bash mysql -c "head -3 /home/cmcs/package/mysql/etc/my.cnf"
su -s /bin/bash mysql -c "touch /data/mysql/.t && rm /data/mysql/.t" # 验证 datadir 可写
chmod 755 /home/cmcs /home/cmcs/package

第六步：初始化MySQL设置
初始化之前需确保目录/data/mysql为空
如果目录非空会导致初始化失败，提示目录非空
清空目录命令：
rm -rf /data/mysql/*

su -s /bin/bash mysql -c "/home/cmcs/package/mysql/bin/mysqld
--defaults-file=/home/cmcs/package/mysql/etc/my.cnf
--initialize"

第七步：启动MySQL服务
如果环境允许可以先getenforce查看SELinux状态
如果是Enforcing状态，需要先关闭SELinux
setenforce 0
/etc/systemd/system/mysqld.service
新增mysqld.service之后执行下面的命令
systemctl daemon-reload
systemctl enable --now mysqld
systemctl status mysqld

第八步：服务启动失败检查验证
getenforce # Enforcing 则继续
ausearch -m avc -ts recent | grep -i mysqld # 找 denied 记录
sudo grep "denied" /var/log/audit/audit.log | grep "mysqld"

selinux导致服务启动失败，快速验证（临时放行）

setenforce 0
systemctl daemon-reload
systemctl reset-failed mysqld
systemctl start mysqld
systemctl status mysqld
修复问题,如果不需要开启SELinux,则可以跳过下面的命令，下面是安全修改
setenforce 1
semanage fcontext -a -t bin_t "/home/cmcs/package/mysql/bin(/.)?"
semanage fcontext -a -t lib_t "/home/cmcs/package/mysql/lib(/.)?"
semanage fcontext -a -t mysqld_db_t "/data/mysql(/.)?"
semanage fcontext -a -t mysqld_log_t "/home/cmcs/package/mysql/log(/.)?"
semanage fcontext -a -t etc_t "/home/cmcs/package/mysql/etc(/.*)?"
restorecon -Rv /home/cmcs/package/mysql /data/mysql
systemctl start mysqld

查看MySQL默认root用户的密码
grep 'temporary password' /home/cmcs/package/mysql/log/error.log
或
cat /home/cmcs/package/mysql/log/error.log | grep 'temporary password'
进入MySQL
mysql -uroot -p
修改root用户的密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'xsn123';

查看数据存储路径是否是配置的路径
SHOW VARIABLES LIKE 'datadir';

将 mysql 客户端命令加入全局环境变量
echo 'export PATH=$PATH:/home/cmcs/package/mysql/bin' >> /etc/profile
source /etc/profile

远程备份

CREATE USER 'dump_user'@'新服务器IP' IDENTIFIED BY '强密码';
-- 赋予全库读取权限
GRANT SELECT, LOCK TABLES, REPLICATION CLIENT ON . TO 'dump_user'@'新服务器IP';
FLUSH PRIVILEGES;

mysqldump --single-transaction --master-data=2 --routines --triggers --events
-h192.168.1.100 -udump_user -p | mysql -uroot -p

---

my.cnf
[client]

指定了客户端与服务器通信的套接字文件路径

socket = /data/mysql/mysql.sock
[mysqld]
innodb_file_per_table = ON
innodb_flush_method = O_DIRECT
innodb_buffer_pool_size = 8G

mysql 的运行用户 这里的用户一定要和初始化时的用户一致

user = mysql

指定 MySQL 服务监听的端口为 3306

port = 3306

指定 MySQL 的安装目录

basedir = /home/cmcs/package/mysql

指定数据文件的存储位置。

datadir = /data/mysql
socket = /data/mysql/mysql.sock

修改最大连接数，防止后续服务因为连接不足报错。

max_connections = 300

连接最大error次数，防止nacos因为连接失败次数过多，被拉入黑名单

max_connect_errors = 100

日志位置

log_error = /home/cmcs/package/mysql/log/error.log
pid-file = /data/mysql/mysql.pid

cd /etc/systemd/system
vi mysqld.service

[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
After=network.target

[Service]
Type=forking
User=mysql
Group=mysql
PIDFile=/data/mysql/mysql.pid
ExecStart=/home/cmcs/package/mysql/bin/mysqld --defaults-file=/home/cmcs/package/mysql/etc/my.cnf --daemonize
ExecReload=/home/cmcs/package/mysql/bin/mysqladmin --defaults-file=/home/cmcs/package/mysql/etc/my.cnf flush-privileges

Restart=on-failure
LimitNOFILE=65535

[Install]
WantedBy=multi-user.target