今天配了一下午的mysql 到了晚上总算配好 和网上其他有许些不同 可能是系统不一样。
先说环境
腾讯云 ubuntu16.0.4先登录 mysql
mysql -u root -p它会让你输个密码
进入mysql库
use mysql;删除匿名用户 这里可能因为我删过了所以是0 affect
delete from user where user='';给予所有ip root权限
mysql> grant all privileges on *.* to 'root'@'%' identified by '******' with grant option;‘root’是用户名的代替 可以直接按照代码打 ‘*****’ 是密码的代替 ‘%’是给予指定ip的位置 现在是所有ip都可以
最后 退出数据库
exit接下来是 第二件事情 修改mysqld.cnf 许多博客上 都说要修改/etc/mysql/my.cnf 里的bind-address 但是呢 我的my.cnf 是
# # The MySQL database server configuration file. # # You can copy this to one of: # - "/etc/mysql/my.cnf" to set global options, # - "~/.my.cnf" to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html # # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # !includedir /etc/mysql/conf.d/ !includedir /etc/mysql/mysql.conf.d/ ~根本 没有 bind-address 经过一番查证 我修改的是
/etc/mysql/ mysql.conf.d/mysqld.cnf使用
sudo vim /etc/mysql/ mysql.conf.d/mysqld.cnf # The MySQL database server configuration file. # # You can copy this to one of: # - "/etc/mysql/my.cnf" to set global options, # - "~/.my.cnf" to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html # This will be passed to all mysql clients # It has been reported that passwords should be enclosed with ticks/quotes # escpecially if they contain "#" chars... socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking # # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. bind-address = 127.0.0.1 # # * Fine Tuning # key_buffer_size = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam-recover-options = BACKUP #max_connections = 100 #table_cache = 64 #thread_concurrency = 10 # # * Query Cache Configuration # query_cache_limit = 1M query_cache_size = 16M # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. # As of 5.1 you can enable the log at runtime! #general_log_file = /var/log/mysql/mysql.log #general_log = 1 # # Error log - should be very few entries. # localhost which is more compatible and is not less secure. #bind-address = 127.0.0.1 # # * Fine Tuning # key_buffer_size = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam-recover-options = BACKUP #max_connections = 100 #table_cache = 64 #thread_concurrency = 10 # # * Query Cache Configuration ubuntu@VM-0-12-ubuntu:~$ mysql -u root -p mysql: [ERROR] Found option without preceding group in config file /etc/mysql/mysql.conf.d/mysqld.cnf at line 18! Enter password: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) ubuntu@VM-0-12-ubuntu:~$ mysql -u root -p mysql: [ERROR] Found option without preceding group in config file /etc/mysql/mysql.conf.d/mysqld.cnf at line 18! Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.21-0ubuntu0.16.04.1 (Ubuntu) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases -> ; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec) # # The MySQL database server configuration file. # # You can copy this to one of: # - "/etc/mysql/my.cnf" to set global options, # - "~/.my.cnf" to set user-specific options. # ~中的bind-address 注释掉就可以了 现在发现 有两个 我注释的是下面的一个 否则 会有 error2003的错误吗
最后试验一下
我用了 navicat for mysql 使用腾讯云提供的外网ip 用刚刚设置的 root 和 相应的密码
成功登录！
