一.mysql大小写敏感设置
- 停止MySQL服务器
[root@localhost ~]# systemctl stop mysqld
- 修改mysql配置,添加lower_case_table_names=1
[root@localhost ~]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[mysqld]
…省略
# default-authentication-plugin=mysql_native_password
lower_case_table_names=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@localhost ~]#
- 删文件/var/lib/mysql
[root@localhost ~]# rm /var/lib/mysql/* -rf
- 启动mysql
[root@localhost ~]# systemctl start mysqld
- 查看MySQL端口是否已经使用,使用netstat -anp 命令查看服务器端口使用情况。
[root@localhost ~]# netstat -anp | grep 3306
- 查看MySQL的启动状态
[root@localhost ~]# systemctl status mysqld.service
- 进入mysql数据库控制台(注意,修改配置并重启mysql后,root账户的密码发生了变化,需要重新获取)
[root@localhost ~]# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
查询临时密码进行重新登录
[root@localhost ~]# cat /var/log/mysqld.log | grep password
2020-09-21T01:19:26.618543Z 6 [Note] [MY-010454] [Server] A temporary password is generate d for root@localhost: =q+l:d3GLC4:
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15573
Server version: 8.0.21
- 修改root用户密码
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'P@ssw0rd';
Query OK, 0 rows affected (0.03 sec)
刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
- 设置root访问权限
mysql> create user 'root'@'%' identified with mysql_native_password by 'P@ssw0rd';
Query OK, 0 rows affected (0.00 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)
- 修改root用户密码的有效期
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'P@ssw0rd' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
- 确认远程访问用户与权限
mysql> select host,user from mysql.user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | root |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+------------------+
5 rows in set (0.00 sec)
mysql>
- 查看大小写敏感设置
mysql> show variables like '%low%';
+----------------------------+-----------------------------------+
| Variable_name | Value |
+----------------------------+-----------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_extra | OFF |
| log_slow_slave_statements | OFF |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 1 |
| max_allowed_packet | 67108864 |
| mysqlx_max_allowed_packet | 67108864 |
| slave_allow_batching | OFF |
| slave_max_allowed_packet | 1073741824 |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
| transaction_allow_batching | OFF |
+----------------------------+-----------------------------------+
14 rows in set (0.01 sec)
mysql>