解决远程连接Mysql出现的权限问题

情景：

今天运行了本地服务器程序出现数据库连接报错的，通过查看程序发现对应连接的数据库是虚拟器上的数据库，先使用linux命令ifconfig查看对应的虚拟机Ip地址，虚拟机的ip地址为192.168.1.100，在虚拟机上使用"mysql -uroot -p"，之后输入对应的数据库密码可以进入，而在本地服务器上配置对应的数据库参数，却始终提示权限问题：

mysql远程连接问题 Access denied for user 'root'@'192.168.1.107' (using password: YES)

解决方案：

通过查阅资料，发现了是由于虚拟机上的mysql数据库没有配置对应的访问权限，使用对应的sql语句进行访问配置即可：

[root@cqs ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.73-log Source distribution


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


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 ALL PRIVILEGES ON *.* TO'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

再次运行程序，运行正常。

知识点：

mysql中的grant指令：

mysql> \h grant
Name: 'GRANT'
Description:
Syntax:
GRANT
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    TO user_specification [, user_specification] ...
    [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
    [WITH with_option ...]


object_type:
    TABLE
  | FUNCTION
  | PROCEDURE


priv_level:
    *
  | *.*
  | db_name.*
  | db_name.tbl_name
  | tbl_name
  | db_name.routine_name


user_specification:
    user [IDENTIFIED BY [PASSWORD] 'password']


ssl_option:
    SSL
  | X509
  | CIPHER 'cipher'
  | ISSUER 'issuer'
  | SUBJECT 'subject'


with_option:
    GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count


The GRANT statement grants privileges to MySQL user accounts. GRANT
also serves to specify other account characteristics such as use of
secure connections and limits on access to server resources. To use
GRANT, you must have the GRANT OPTION privilege, and you must have the
privileges that you are granting.


Normally, a database administrator first uses CREATE USER to create an
account, then GRANT to define its privileges and characteristics. For
example:


CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

执行完grant后需要让其生效，需执行 FLUSH PRIVILEGES;  命令。