linux系统mysql安装到远程连接到配置log

1.安装mysql

下载官网二进制版本，无需安装，解压即可用

2.远程连接mysql

[root@iZuf662ezm094cpns9yxkqZ untilapp]# # 使用 tar 命令解压 MySQL 压缩包
[root@iZuf662ezm094cpns9yxkqZ untilapp]# tar -xvf mysql-8.0.39-linux-glibc2.28-x86_64.tar.xz -C /usr/local/
[root@iZuf662ezm094cpns9yxkqZ untilapp]# # 编辑/etc/profile文件，添加 MySQL 的 bin 目录到 PATH 环境变量中
[root@iZuf662ezm094cpns9yxkqZ untilapp]# echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile
[root@iZuf662ezm094cpns9yxkqZ untilapp]# # 使配置生效
[root@iZuf662ezm094cpns9yxkqZ untilapp]# source /etc/profile
[root@iZuf662ezm094cpns9yxkqZ untilapp]# # 创建一个专门的 MySQL 用户和组
[root@iZuf662ezm094cpns9yxkqZ untilapp]# groupadd mysql
[root@iZuf662ezm094cpns9yxkqZ untilapp]# useradd -r -g mysql -s /bin/false mysql
[root@iZuf662ezm094cpns9yxkqZ untilapp]# # 将 MySQL 安装目录的所有权更改为 mysql 用户
[root@iZuf662ezm094cpns9yxkqZ untilapp]# chown -R mysql:mysql /usr/local/mysql
[root@iZuf662ezm094cpns9yxkqZ untilapp]# # 初始化 MySQL 数据目录
[root@iZuf662ezm094cpns9yxkqZ untilapp]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/usr/local/mysql/data
[root@iZuf662ezm094cpns9yxkqZ untilapp]# # 启动 MySQL 服务
[root@iZuf662ezm094cpns9yxkqZ untilapp]# /usr/local/mysql/bin/mysqld_safe --user=mysql &
[root@iZuf662ezm094cpns9yxkqZ untilapp]# Logging to '/usr/local/mysql/data/iZuf662ezm094cpns9yxkqZ.err'.
[root@iZuf662ezm094cpns9yxkqZ untilapp]# # 登录 MySQL
[root@iZuf662ezm094cpns9yxkqZ untilapp]# mysql -u root -p

#登陆过后
#修改初始密码
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'zhongxinyi';
Query OK, 0 rows affected (0.01 sec)

mysql> # 允许 root 用户从任何 IP 访问
mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'zhongxinyi';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DATABASE steamdata;
Query OK, 1 row affected (0.01 sec)

mysql> -- 更新 root 用户的权限，允许从任何主机连接
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'zhongxinyi';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'zhongxinyi';
ERROR 1396 (HY000): Operation CREATE USER failed for 'root'@'%'
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;

#设置一个系统文件自启动mysql
[root@iZuf662ezm094cpns9yxkqZ untilapp]# sudo vi /etc/systemd/system/mysql.service
[root@iZuf662ezm094cpns9yxkqZ untilapp]# sudo systemctl daemon-reload
[root@iZuf662ezm094cpns9yxkqZ untilapp]# sudo systemctl start mysql
[root@iZuf662ezm094cpns9yxkqZ untilapp]# sudo systemctl enable mysql

#mysql配置如下，解释上豆包   位置在/etc/systemd/system
[Unit]
Description=MySQL Server
After=network.target

[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/mysqld.pid --user=mysql
ExecReload=/bin/kill -HUP $MAINPID
Restart=on-failure

[Install]
WantedBy=multi-user.target

3.配置log

#上面service中指定的my.cnf配置   位置在/etc/my.cnf
[mysqld]
bind-address = 0.0.0.0

# 通用查询日志
general_log = 1
general_log_file = /var/log/mysql/mysqlPuTong.log

# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysqlManChaXun.log
long_query_time = 2  # 可选：定义慢查询阈值（单位：秒）

# 错误日志
log_error = /var/log/mysql/mysqlError.log

#关于上面的my.conf查看配置流程
[root@iZuf662ezm094cpns9yxkqZ ~]# mysql -u root -p
mysql> show variables like 'general_log_file';
+------------------+---------------------------------------------------+
| Variable_name    | Value                                             |
+------------------+---------------------------------------------------+
| general_log_file | /usr/local/mysql/data/iZuf662ezm094cpns9yxkqZ.log |
+------------------+---------------------------------------------------+
1 row in set (0.01 sec)

mysql> show variables like 'slow_query_log_file';
+---------------------+--------------------------------------------------------+
| Variable_name       | Value                                                  |
+---------------------+--------------------------------------------------------+
| slow_query_log_file | /usr/local/mysql/data/iZuf662ezm094cpns9yxkqZ-slow.log |
+---------------------+--------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ^C
mysql> show variables like 'log_error';
+---------------+-------------------------------+
| Variable_name | Value                         |
+---------------+-------------------------------+
| log_error     | ./iZuf662ezm094cpns9yxkqZ.err |
+---------------+-------------------------------+
1 row in set (0.01 sec)


[root@iZuf662ezm094cpns9yxkqZ ~]# mysql --help | grep 'my.cnf'
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 
[root@iZuf662ezm094cpns9yxkqZ ~]# mysql --verbose --help | grep -A1 "Default options"
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 
[root@iZuf662ezm094cpns9yxkqZ ~]# ps -ef | grep mysqld
root       13930  0.0  0.0 222384  1048 ?        S    Mar25   0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --user=mysql
mysql      14012  0.3 25.3 1804748 485212 ?      Sl   Mar25  14:49 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=iZuf662ezm094cpns9yxkqZ.err --pid-file=iZuf662ezm094cpns9yxkqZ.pid
root      152074  0.0  0.4 248532  8068 pts/1    T    10:32   0:00 mysql -u root -p
root      152596  0.0  0.4 248532  8236 pts/1    T    11:14   0:00 mysql -u root -p
root      153130  0.0  0.6 248532 11512 pts/1    T    12:03   0:00 mysql -u root -p
mysql     153226  0.6 15.6 705424 300748 ?       Ssl  12:13   0:00 /usr/local/mysql/bin/mysqld --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/mysqld.pid
root      153242  0.0  0.0 221528   956 pts/1    R+   12:15   0:00 grep --color=auto mysql
[root@iZuf662ezm094cpns9yxkqZ ~]# sudo systemctl stop mysql.service  # 确保服务停止
[root@iZuf662ezm094cpns9yxkqZ ~]# kill -9 13930,14012,152074上面全部

[root@iZuf662ezm094cpns9yxkqZ ~]# sudo systemctl start mysql.service
[root@iZuf662ezm094cpns9yxkqZ ~]# sudo systemctl status mysql.service

[root@iZuf662ezm094cpns9yxkqZ ~]# mysql -u root -p
Enter password:
mysql> show variables like 'general_log_file';
+------------------+--------------------------------+
| Variable_name    | Value                          |
+------------------+--------------------------------+
| general_log_file | /var/log/mysql/mysqlPuTong.log |
+------------------+--------------------------------+
1 row in set (0.00 sec)