mysql start server ip_mysql新增专给特定IP访问的用户

最新推荐文章于 2022-09-26 08:52:19 发布

weixin_39828102

最新推荐文章于 2022-09-26 08:52:19 发布

阅读量143

点赞数

文章标签： mysql start server ip

本文链接：https://blog.csdn.net/weixin_39828102/article/details/113910982

版权

1 新创建同时授权用户：

[root@XX-90 ~]# mysql -root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 5386

Server version: 5.5.15-log MySQL Community Server (GPL)

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>

mysql> grant select,insert,update,delete on my_test_db.* to biao@localhost Identified by "123456";

上面的my_test_db是要授权访问到的数据库实例，biao是新用户名，123456是密码，localhost应该是数据库允许被访问的ip

2 创建用户后，可以在mysql数据库的user表看到新创建的用户信息：

mysql> show database;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 15

Current database: karaoke

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| test |

+--------------------+

6 rows in set (0.00 sec)

mysql> use mysql

Database changed

mysql> show tables;

+---------------------------+

| Tables_in_mysql |

+---------------------------+

| columns_priv |

| db |

| event |

| func |

| general_log |

| help_category |

| help_keyword |

| help_relation |

| help_topic |

| host |

| ndb_binlog_index |

| plugin |

| proc |

| procs_priv |

| proxies_priv |

| servers |

| slow_log |

| tables_priv |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

+---------------------------+

24 rows in set (0.00 sec)

mysql> select * from user where user='karaoke' \G

*************************** 1. row ***************************

Host: localhost

User: biao

Password: *E7E5651E6FD1A0960EC95E6DE3D971537515F66E

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: N

Drop_priv: N

Reload_priv: N

Shutdown_priv: N

Process_priv: N

File_priv: N

Grant_priv: N

References_priv: N

Index_priv: N

Alter_priv: N

Show_db_priv: N

Super_priv: N

Create_tmp_table_priv: N

Lock_tables_priv: N

Execute_priv: N

Repl_slave_priv: N

Repl_client_priv: N

Create_view_priv: N

Show_view_priv: N

Create_routine_priv: N

Alter_routine_priv: N

Create_user_priv: N

ssl_type:

ssl_cipher:

x509_issuer:

x509_subject:

max_questions: 0

max_updates: 0

max_connections: 0

max_user_connections: 0

1 row in set (0.00 sec)

如果你的结果不面上面user='biao'那样，你可以自己update这个表这段信息，如我想让这个用户只给10.11.11.11的IP访问，就update user set Host='10.11.11.11' where User='biao';

3 新增用户授权后无法使用，如ip为10.11.11.11无法使用，这里不用急，我是通过重启数据库来让它生效的：

#创建用户后,外面机如如还没法登陆,可以重启数据库服务:

[root@TJHY95-90 ~]# sodu service mysql restart;

-bash: sodu: command not found

[root@TJHY95-90 ~]# sudo service mysql restart;

Shutting down MySQL..... [确定]

Starting MySQL.... [确定]

[root@TJHY95-90 ~]# mysql -uroot -p

这样外面就可以访问了，如这里我指定了10.11.11.11这台机来访问，那么在这台机上使用如下访问语句:

[root@xx11-11 ~]# hostname -i

10.11.11.11

[root@xx11-11 ~]# mysql -ukaraoke -pkaraoke -h10.11.11.142

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 17

Server version: 5.5.15-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

这里的10.11.11.142是我们刚才数据库所在的IP。

更多参考：

配置外部访问帐号密码

本地登录mysql

Java代码

mysql -uroot -p

执行

Java代码

grant all on *.* to rails@'client_ip' identified by 'password' with grant option;

例子：所有局域网客户端都可访问

Java代码

grant all on *.* to root@'192.168.0.%' identified by 'root' with grant option;

例子：为boyu数据库设置用户boyu

Java代码

grant all on boyu.* to boyu@'192.168.0.%' identified by 'p4ssword';

app_server_id为APP应用服务器的IP

设置mysql外部能访问

/etc/mysql/my.cnf

注释到下面这行

Java代码

bind-address = 127.0.0.1

重启mysql-server

Java代码

sudo service mysql restart

及参考：

如果在源代码编译未安装成功的情况，用yum安装是比较好的选择，特别是对新手来说，是很好的选择，安装的过程很简单，只要输入：yum -y install mysql-server ，系统自动下载和安装Mysql的，chkconfig --add mysqld 在服务清单中添加mysql服务service mysqld start 服务启动mysqladmin -u root password 'newpassword' 更改密码mysql -u root -pmysql> DROP DATABASE test; 删除test数据库mysql> DELETE FROM mysql.user WHERE user = ''; 删除匿名帐户mysql> FLUSH PRIVILEGES; 重载权限添加mysql用户：GRANT ALL PRIVILEGES ON my_db.* TO 'user'@'localhost' IDENTIFIED BY 'password';

分享到：