配置文件路径为:

/etc/mysql/mysql.conf.d/mysqld.cnf

但更常用的配置文件路径是

/etc/mysql/my.cnf

1 打开配置文件

sudo gedit /etc/mysql/mysql.conf.d/mysqld.cnf

主要配置信息说明

port表示端口号，默认为3306 bind-address表示服务器绑定的ip，默认为127.0.0.1 datadir表示数据库保存路径，默认为/var/lib/mysql log_error表示错误日志，默认为/var/log/mysql/error.log

  1. #
  2. # The MySQL database server configuration file.
  3. #
  4. # One can use all long options that the program supports.
  5. # Run program with --help to get a list of available options and with
  6. # --print-defaults to see which it would actually understand and use.
  7. #
  8. # For explanations see
  9. # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
  11. # Here is entries for some specific programs
  12. # The following values assume you have at least 32M ram
  14. [mysqld]
  15. #
  16. # * Basic Settings
  17. #
  18. user        = mysql
  19. pid-file    = /var/run/mysqld/mysqld.pid
  20. socket    = /var/run/mysqld/mysqld.sock
  21. port        = 3306
  22. datadir    = /var/lib/mysql
  25. # If MySQL is running as a replication slave, this should be
  26. # changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
  27. # tmpdir        = /tmp
  28. #
  29. # Instead of skip-networking the default is now to listen only on
  30. # localhost which is more compatible and is not less secure.
  31. bind-address        = 192.168.3.67
  32. mysqlx-bind-address    = 127.0.0.1
  33. #
  34. # * Fine Tuning
  35. #
  36. key_buffer_size        = 16M
  37. # max_allowed_packet    = 64M
  38. # thread_stack        = 256K
  40. # thread_cache_size       = -1
  42. # This replaces the startup script and checks MyISAM tables if needed
  43. # the first time they are touched
  44. myisam-recover-options  = BACKUP
  46. # max_connections        = 151
  48. # table_open_cache       = 4000
  50. #
  51. # * Logging and Replication
  52. #
  53. # Both location gets rotated by the cronjob.
  54. #
  55. # Log all queries
  56. # Be aware that this log type is a performance killer.
  57. # general_log_file        = /var/log/mysql/query.log
  58. # general_log             = 1
  59. #
  60. # Error log - should be very few entries.
  61. #
  62. log_error = /var/log/mysql/error.log
  63. #
  64. # Here you can see queries with especially long duration
  65. # slow_query_log        = 1
  66. # slow_query_log_file    = /var/log/mysql/mysql-slow.log
  67. # long_query_time = 2
  68. # log-queries-not-using-indexes
  69. #
  70. # The following can be used as easy to replay backup logs or for replication.
  71. # note: if you are setting up a replication slave, see README.Debian about
  72. #       other settings you may need to change.
  73. # server-id        = 1
  74. # log_bin            = /var/log/mysql/mysql-bin.log
  75. # binlog_expire_logs_seconds    = 2592000
  76. max_binlog_size   = 100M
  77. # binlog_do_db        = include_database_name
  78. # binlog_ignore_db    = include_database_name
  80. transaction-isolation=READ-COMMITTED

2 配置完成后重启mysql

service mysql restart

3 mysql8设置密码

sudo mysql_secure_installation

image.png
image.png
image.png
image.png
root的plugin下面是auth_socket,是不允许用密码登录的

select user,authentication_string,plugin,host from mysql.user;

image.png
可能密码太简单不符合
image.png
查看当前安全变量值，可用命令查看 validate_password 密码验证插件是否安装。

show variables like ‘validate_password%’;

image.png
validate_password_mixed_case_count大小写的最小个数。
validate_password_special_char_count 特殊字符的最小个数。
validate_password_dictionary_file 字典文件
要想在高版本使用简单的密码，需要做这样设置:

set global validate_password.policy=0; set global validate_password.length=3; set global validate_password.mixed_case_count=0; set global validate_password.number_count=3; set global validate_password.special_char_count=0;

image.png

alter user ‘root’@’localhost’ identified by ‘123456’; flush privileges; alter user ‘root’@’localhost’ identified with caching_sha2_password by ‘123456’; flush privileges;

image.png

4 新配置文件

以后配置文件统一用下面这个

/etc/mysql/my.cnf

  1. # mysql下的是MySQL客户端的配置
  2. [mysql]
  5. # mysqld下的是MySQL服务端的配置
  6. [mysqld]
  7. user        = mysql
  8. port        = 3306
  9. pid-file    = /var/run/mysqld/mysqld.pid
  10. socket    = /var/run/mysqld/mysqld.sock
  12. # 数据库的数据保存的目录
  13. datadir    = /var/lib/mysql
  15. # 允许连接的IP地址, 注释掉之后所有IP的机器都可以连接本MySQL服务端
  16. # bind-address        = 127.0.0.1
  18. # 禁止MySQL客户端与服务器通过TCP/IP进行通信
  19. # skip-networking
  21. # 指定单个查询能够使用的缓冲区大小
  22. key_buffer_size        = 16M
  24. # 服务器发送和接受的最大包长度
  25. # max_allowed_packet    = 64M
  27. # 线程使用的堆大小. 此容量的内存在每次连接时被预留
  28. # thread_stack        = 256K
  30. # 联合查询操作所能使用的缓冲区大小
  31. # thread_cache_size       = -1
  33. # 指定myisam类型的表通过备份进行数据恢复
  34. myisam-recover-options  = BACKUP
  36. # MySQL允许的最大连接数量, 若超过此数量, 新来的请求将会被存在堆栈中以等待某一连接释放资源
  37. # max_connections        = 151
  39. # 通用日志
  40. general_log_file        = /var/log/mysql/mysql.log
  41. general_log             = 1
  43. # 错误日志
  44. log_error = /var/log/mysql/error.log
  46. # 二进制日志(主从同步时要用)
  47. log_bin            = /var/log/mysql/mysql-bin.log
  48. binlog_expire_logs_seconds = 864000  # 超过10天的binlog删除
  49. max_binlog_size   = 100M
  51. # 本机的序号, 同一局域网内要唯一, 配置主从的关键
  52. server-id        = 1
  54. # 事务隔离: MySQL默认采用的是REPEATABLE-READ
  55. transaction-isolation=READ-COMMITTED

service mysql restart