MySQL 远程访问连接及用户授权设置

一、如何确定 MySQL 配置文件的位置

当我们不确定当前主机上的 MySQL 的配置文件的位置时，我们可以使用如下方式

$which mysql
/bin/mysql

$/bin/mysql --verbose --help|grep -A 1 'Default options'

二、MySQL 重置密码

我们经常遇到

ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES )

这个错误一般都是由于账号密码错误导致的，所以要重置密码。

2.1、跳过 MySQL 的密码认证

重置密码的第一步就是跳过 MySQL 的密码认证过程

vi /etc/my.cnf

搜索 mysqld，在其后面任意一行添加

[mysqld]
skip-grant-tables

用来跳过密码验证的过程，然后重启 MySQL

2.2、更改用户密码

输入 mysql 进入 MySQL，

mysql> use mysql;
mysql> update user set password=password("abcdef") where user="quma";
mysql> flush privileges;
mysql> quit

2.3、添加用户并授权

我们也可以添加用户，添加用户 quma，并给他最高权限，可以远程登录

mysql> use mysql;
mysql> create user quma@'%' identified by 'password';
mysql> grant all privileges on *.* to quma@'%';

三、MySQL 无法远程连接

telnet 39.98.39.73 3306

阿里云安全组策略未加 3306 端口

telnet: connect to address IP地址: No route to host 原因：防火墙阻止你访问。

iptables -nL

中有一条drop的拦截规则，

vi  /etc/sysconfig/iptables

增加下面一行

-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT

注意必须在icmp-host-prohibited之前，否则将仍不能进行访问

service iptables restart

四、MySQL 配置性能优化

一台4核8G的机器 vi /etc/my.cnf 根据这篇文章 https://www.cnblogs.com/angryprogrammer/p/6667741.html

将 max_connections 调整为 3000
将 key_buffer_size 调整为600M
将 query_cache_size 调整为 256M
将 table_open_cache 调整为2048，默认为512

检查mysql 连接数，在mysql命令行中输入如下

mysql> show status like '%conn%'

上面的代码显示数据库状态中有conn字眼的参数。显示结果

+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| Aborted_connects         | 11     |
| Connections              | 104439 |
| Max_used_connections     | 78     |
| Ssl_client_connects      | 0      |
| Ssl_connect_renegotiates | 0      |
| Ssl_finished_connects    | 0      |
| Threads_connected        | 14     |
+--------------------------+--------+

Connections=104439 并不是代表目前有104439 个链接，那只代表试图连接mysql的次数。

五、MySQL 文件复制备份

直接到MySQL的数据库存储目录data里

tar -czvf fncftc_sql_20190411_02.tar ./fncftc_sql
scp -r ./fncftc_sql_20190411_02.tar root@127.0.0.1:/usr/local/mysql/var

解压

tar -xzvf fncftc_sql_20190411_02.tar

排除

tar -czvf fncftc_sql_20190411_02.tar --exclude=fncftc_sql/logs ./fncftc_sql

felix-cao / Blog