LAMP搭建21：MySQL本地登录和远程登录

[root@centos6 ~]# mysql -uroot -p123456

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

Your MySQL connection id is 2

Server version: 5.1.49 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL v2 license

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

mysql>

远程登录mysql需要-h选项指定主机，-P选项指定端口，默认情况下127.0.0.1被授权远程登录：

[root@centos6 ~]# mysql -uroot -h127.0.0.1 -P3306 -p123456

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

Your MySQL connection id is 3

Server version: 5.1.49 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL v2 license

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

mysql>

默认情况下没有被授权的用户不能登录：

[root@centos6 ~]# mysql -uroot -h192.168.147.132 -P3306 -p123456

ERROR 1130 (HY000): Host ‘192.168.147.132‘ is not allowed to connect to this MySQL server

授权一个用户远程登录MySQL，这里的IP是客户端IP

mysql> use mysql;

mysql> grant all on *.* to ‘root‘@‘192.168.147.132‘ identified by ‘123456‘;

Query OK, 0 rows affected (0.01 sec)

查看关于主机192.168.11.160用户的信息

mysql> select * from user where host=‘192.168.147.132‘\G;

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

Host: 192.168.147.132

User: root

Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9

Select_priv: Y

Insert_priv: Y

Update_priv: Y

Delete_priv: Y

Create_priv: Y

Drop_priv: Y

Reload_priv: Y

Shutdown_priv: Y

Process_priv: Y

File_priv: Y

Grant_priv: N

References_priv: Y

Index_priv: Y

Alter_priv: Y

Show_db_priv: Y

Super_priv: Y

Create_tmp_table_priv: Y

Lock_tables_priv: Y

Execute_priv: Y

Repl_slave_priv: Y

Repl_client_priv: Y

Create_view_priv: Y

Show_view_priv: Y

Create_routine_priv: Y

Alter_routine_priv: Y

Create_user_priv: Y

Event_priv: Y

Trigger_priv: Y

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)

ERROR:

No query specified

授权成功之后可以远程登录

[root@centos6 ~]# mysql -uroot -h192.168.147.132 -P3306 -p123456

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

Your MySQL connection id is 6

Server version: 5.1.49 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL v2 license

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

mysql>

查看当前登录的用户是谁

mysql> select user();

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

| user()                        |

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

| root@192.168.147.132 |

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

1 row in set (0.00 sec)

如果是127.0.0.1登录：

mysql> select user();

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

| user()                |

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

| root@localhost |

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

1 row in set (0.00 sec)

如果本地有多个MySQL，则可以使用-S选项指定socket，登录到相应的MySQL

[root@centos6 ~]# mysql -uroot -S /tmp/mysql.sock -p123456

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

Your MySQL connection id is 8

Server version: 5.1.49 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

This software comes with ABSOLUTELY NO WARRANTY. This is free software,

and you are welcome to modify and redistribute it under the GPL v2 license

Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.

mysql>

LAMP搭建21：MySQL本地登录和远程登录

标签：lamp   mysql   本地登录   远程登录