MySQL开启远程连接
远程连接MySQL
无法远程连接MySQL
配置Linux防火墙,允许访问MySQL的TCP 3306端口 [root@centos ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent success [root@centos ~]# firewall-cmd --list-ports [root@centos ~]# [root@centos ~]# firewall-cmd --reload success [root@centos ~]# firewall-cmd --list-ports 3306/tcp [root@centos ~]# 查看MySQL是否已经启动 [root@centos ~]# systemctl status mysqld ● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: active (running) since Sat 2018-07-28 17:24:50 CST; 29s ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 2294 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 2311 (mysqld) Status: "SERVER_OPERATING" CGroup: /system.slice/mysqld.service └─2311 /usr/sbin/mysqld Jul 28 17:24:47 centos systemd[1]: Starting MySQL Server... Jul 28 17:24:50 centos systemd[1]: Started MySQL Server. 查看系统是否已经监听端口TCP 3306 [root@centos ~]# netstat -tunlp | grep 3306 tcp6 0 0 :::33060 :::* LISTEN 24130/mysqld tcp6 0 0 :::3306 :::* LISTEN 24130/mysqld [root@centos ~]#
修改配置
[root@centos ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 8.0.12 MySQL Community Server - GPL 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> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | component | | db | | default_roles | | engine_cost | | func | | general_log | | global_grants | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | password_history | | plugin | | procs_priv | | proxies_priv | | role_edges | | server_cost | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 33 rows in set (0.01 sec) mysql> select Host,User from user; +-----------+------------------+ | Host | User | +-----------+------------------+ | localhost | mysql.infoschema | | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-----------+------------------+ 4 rows in set (0.00 sec) mysql> update user set Host='%' where User='root'; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select Host,User from user; +-----------+------------------+ | Host | User | +-----------+------------------+ | % | root | | localhost | mysql.infoschema | | localhost | mysql.session | | localhost | mysql.sys | +-----------+------------------+ 4 rows in set (0.00 sec) mysql> mysql> flush privileges; Query OK, 0 rows affected (0.01 sec) mysql> quit Bye [root@centos ~]#
MySQL 8.0无法使用客户端连接
修改/etc/my.cnf
[root@centos ~]# vim /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] # # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # # Remove the leading "# " to disable binary logging # Binary logging captures changes between backups and is enabled by # default. It's default setting is log_bin=binlog # disable_log_bin # # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M # # Remove leading # to revert to previous value for default_authentication_pluginn , # this will increase compatibility with older clients. For background, see: # https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_dee fault_authentication_plugin # default-authentication-plugin=mysql_native_password 启用这句 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
修改认证方式
[root@centos ~]# mysql -uroot -p Enter password: mysql> use mysql; Database changed mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root'; Query OK, 0 rows affected (0.04 sec) 或 mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root'; mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)
重启MySQL
[root@centos ~]# systemctl restart mysqld
文章评论