LAMP搭建21：MySQL本地登入和遠程登入

標籤：lamp   mysql   本地登入   遠程登入   

本地登入直接使用mysql命令，-u選項指定使用者名稱，-p選項指定該使用者密碼

[[email protected] ~]# 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被授權遠程登入：

[[email protected] ~]# 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>

預設情況下沒有被授權的使用者不能登入：

[[email protected] ~]# 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

授權成功之後可以遠程登入

[[email protected] ~]# 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()                        |

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

| [email protected] |

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

1 row in set (0.00 sec)

如果是127.0.0.1登入：

mysql> select user();

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

| user()                |

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

| [email protected] |

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

1 row in set (0.00 sec)

如果本地有多個MySQL，則可以使用-S選項指定socket，登入到相應的MySQL

[[email protected] ~]# 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本地登入和遠程登入