mysql-mariadb配置远程连接

一. MySQL5.7授权用户远程访问

概览：

1.授权远程登陆
2.注释配置文件中bind=127.0.0.1

或

1.创建远程登陆用户注释bind=127.0.0.1
2.授权登陆

做个记录，每次弄环境的时候，特别是弄mysql环境，时不时都要用到下面的命令 命令如下:

授权

1
2
3
4
5
6
# 授权远程登陆
grant all privileges on *.* to 'root'@'%' identified by 'oa123456' with grant option;
# 刷新
flush privileges;
# 退出
quit;

创建远程登陆用户

1
2
3
4
5
6
7
8
9
10
# 创建远程登陆用户
CREATE USER 'gegewu'@'%' IDENTIFIED BY '123456';
# 授权远程登陆以及操作所有数据库的权限
grant all privileges on *.* to 'gegewu'@'%' identified by '123456' with grant option;
# 授权对特定数据库的权限
GRANT ALL PRIVILEGES ON cacti.* TO 'gegewu'@'%' IDENTIFIED BY '123456';
# 刷新
flush privileges;
# 退出
quit;

注意:

上面的命令原型如下:

1
grant all privileges on *.* to 'username'@'%' identified by 'password' with grant option;

命令中的“%”相当于授权任意主机。

另外还有就是通常授权用户远程连接，还需要修改配置文件，以Ubuntu16.04为例，需要修改mysqld.cnf配置文件，将里面的bind=127.0.0.1注释掉即可

image-20230516112937052

文件位置：/etc/mysql/mysql.conf.d/mysqld.cnf
然后重启一下mysql服务，这时你就可以通过sqlyong或navicat连接mysql服务。

image-20230516113014695

二. 例子mariadb mysql配置远程连接

1
2
3
4
5
6
7
8
9
10
11
例子1： 
       mariadb配置远程连接
           grant all privileges on *.* to 'root'@'%' identified by 'oa123456' with grant option;
           flush privileges;
       quit;
例子2：
       mysql设置远程连接
           grant all privileges on *.* to 'root'@'%' identified by 'oa123456' with grant option;
           flush privileges;
       quit;

image-20230516113342530 image-20230516113430142

mysql5.7 配置文件 /etc/mysql/mysql.conf.d/mysqld.cnf

1
2
3
4
# 注释bind=127.0.0.1
vim /etc/mysql/mysql.conf.d/mysqld.cnf       
# 重启mysql
systemctl restart mysql

image-20230516113541859

三. mysql 8版本配置远程连接不太一样 (mysql给root开启远程访问权限(mysql8))

参考：

https://www.wgstart.com/help/docs67.html
https://blog.csdn.net/haha112358/article/details/115254606

1.mysql给root开启远程访问权限(mysql8)
2.注释bind=127.0.0.1 文件路径： /etc/mysql/mysql.conf.d/mysqld.cnf

或

1.创建远程登陆用户 授权用户远程访问
2.注释bind=127.0.0.1

方式1 授权root远程登陆

命令概览

1
2
3
4
5
6
7
8
9
10
# 登陆
mysql -uroot -p
# sql
use mysql;
select host,user from user where user='root';
update user set host='%' where user='root' and host='localhost';
select host,user from user where user='root';
# 修改初始密码刷新
alter user 'root'@'%' identified with mysql_native_password by '123456';
flush privileges;
1、在连接服务器后，操作mysql系统数据库
这里给root设置的密码是123456，我们也可以改成自己的密码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
root@vultr:~/mysql8# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.29 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
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> select host,user from user where user='root';
+-----------+------+
| host      | user |
+-----------+------+
| localhost | root |
+-----------+------+
1 row in set (0.00 sec)
mysql> update user set host='%' where user='root' and host='localhost';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select host,user from user where user='root';
+------+------+
| host | user |
+------+------+
| %    | root |
+------+------+
1 row in set (0.00 sec)
mysql> alter user 'root'@'%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)



注意，这里最后一行是把root密码设置为123456，可以把自定义成自己的密码
2、如果是遇到root连不上数据库的问题，可能是加密规则变了，试试如下处理，这里修改加密规则，这里密码用的是123456，可以改成自己的密码
1
2
3
4
5
6
7
8
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER; 
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456'; 
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES; 
Query OK, 0 rows affected (0.01 sec)

截图：

image-20230702134444931image-20230702134530553image-20230702134631217

方式2 创建远程登陆用户

1
2
3
4
5
6
# 创建一个名为gegewu且密码是123456的用户@%表示所有的ip地址都可以用这个用户名连接
CREATE USER 'gegewu'@'%' IDENTIFIED BY '123456';
# 赋予相应的权限 *.*代表所有的数据库都可以访问
grant all on *.* to 'gegewu'@'%';
# 刷新以下权限信息
flush privileges;

image-20230608092047292

四. mysql常用命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
# 创建cacti数据库
1. CREATE DATABASE cacti DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
# 创建用户
1.1. CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
例子：
{
CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.101' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'%' IDENTIFIED BY '';
CREATE USER 'pig'@'%';
       }
# 授权用户对数据库的权限
2. GRANT ALL PRIVILEGES ON cacti.* TO 'cacti'@'localhost' IDENTIFIED BY 'cacti';
例子： 授权test用户拥有所有数据库的某些权限：
2.1. grant select,delete,update,create,drop on *.* to 'test'@'%' identified by 'test123';
# 授权对数据库的操作权限
2.1 GRANT privileges ON databasename.tablename TO 'username'@'host';
例子：
{
privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
databasename：数据库名
tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*
GRANT SELECT, INSERT ON test.user TO 'pig'@'%';
GRANT ALL ON *.* TO 'pig'@'%';
GRANT ALL ON maindataplus.* TO 'pig'@'%'; 
注意:
用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令:
GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
     }
# 撤销用户权限
REVOKE privilege ON databasename.tablename FROM 'username'@'host';
例子：REVOKE SELECT ON *.* FROM 'pig'@'%';
# 删除用户
DROP USER 'username'@'host';
# 数据库时区设置
3. GRANT SELECT ON mysql.time_zone_name TO cacti@localhost;
# 更改数据库编码
4. ALTER DATABASE cacti CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
# 写入my.cnf配置文件
set global collation_server=utf8mb4_unicode_ci;
set global character_set_client=utf8mb4;
set global max_allowed_packet=17700000;
set global join_buffer_size=20971520;
set global innodb_file_per_table=ON;
set global innodb_file_format=Barracuda;
set global innodb_large_prefix=ON;
set global innodb_flush_log_at_trx_commit=2;
FLUSH PRIVILEGES;
exit;
# 设置和更改密码
5. SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
如果是当前登陆用户用:
SET PASSWORD = PASSWORD("newpassword");
例子: SET PASSWORD FOR 'pig'@'%' = PASSWORD("123456");
# shou远程登录用户
grant all privileges on *.* to 'username'@'%' identified by 'password' with grant option;
=========================================================================
登录数据库创建 neutron 数据库:
创建并授予 neutron 用户完全操作 neutron 库权限
CREATE DATABASE neutron default character set utf8;
GRANT ALL PRIVILEGES ON neutron.* TO 'neutron'@'localhost' IDENTIFIED BY 'openstack';
GRANT ALL PRIVILEGES ON neutron.* TO 'neutron'@'%' IDENTIFIED BY 'openstack';
=========================================================================
# 刷新
FLUSH PRIVILEGES;