Introduction to MySQL Open remote connection method

Go out security considerations, the default MySQL is to allow only native links, and not allow other hosts to connect through the client tools. To resolve this problem at this point, there are three ways to resolve it.

A, authorized logins to other IP. Specifically for the first in this machine through the mysql-uroot-p password to login. After MySQL >, the permission is opened through the GRANT command: Grant permission on database object to user

Note: The following takes Phpboke as an example of an existing test database table, detailing the grant command
One, grant ordinary data user, query, insert, UPDATE, delete all table data in the database right.

Grant SELECT on phpboke.* to common_user@ '% ' grant inserts on phpboke.* to common_user@ '% ' grant update on phpboke.* to Commo n_user@ '% ' grant Delete on phpboke.* to common_user@ '% '
Alternatively, replace it with a MySQL command:

Grant SELECT, INSERT, UPDATE, delete on phpboke.* to common_user@ '% '

Second, grant database developers, create tables, indexes, views, stored procedures, functions ... and other permissions.

Grant creates, modifies, and deletes MySQL data table structure permissions.

Grant create on phpboke.* to developer@ ' 192.168.0.% ', Grant alter on phpboke.* to developer@ ' 192.168.0.% '; grant drop on phpboke.* to developer@ ' 192.168.0.% ';

Grant operates MySQL foreign key permissions.

Grant references on phpboke.* to developer@ ' 192.168.0.% ';

Grant operates the MySQL temporary table permission.

Grant create temporary tables on phpboke.* to developer@ ' 192.168.0.% ';

Grant operates MySQL indexing permissions.

Grant index on phpboke.* to developer@ ' 192.168.0.% ';

Grant operates the MySQL view and views the view source code permissions.

Grant CREATE view on phpboke.* to developer@ ' 192.168.0.% ', grant Show view on phpboke.* to developer@ ' 192.168.0.% ';

Grant operates MySQL stored procedures, function permissions.

Grant create routine on phpboke.* to developer@ ' 192.168.0.% '; -Now, can show procedure statusgrant alter routine in phpboke.* to developer@ ' 192.168.0.% '; --Now, you can drop a proceduregrant execute in phpboke.* to developer@ ' 192.168.0.% ';

Third, grant ordinary DBA management of a MySQL database permissions.

Grant all privileges on Phpboke to dba@ ' localhost '
Among them, the keyword "privileges" can be omitted.

The grant advanced DBA manages permissions for all databases in MySQL.

Grant all on *.* to dba@ ' localhost '

MySQL grant permissions, respectively, can function at multiple levels.

1. Grant functions on the entire MySQL server:

Grant SELECT on *.* to Dba@localhost; --The DBA can query the tables in all databases in MySQL. Grant all on *.* to Dba@localhost; --DBA can manage all databases in MySQL

2. Grant functions on a single database:

Grant SELECT on phpboke.* to Dba@localhost; --DBAs can query the tables in Phpboke.

3. Grant functions on a single datasheet:

Grant SELECT, INSERT, UPDATE, delete on phpboke.orders to Dba@localhost;

Here, when you authorize multiple tables for a user, you can execute the above statements more than once. For example:

Grant Select (user_id,username) on smp.users to mo_user@ '% ' identified by ' 123345 ', grant Select on Smp.mo_sms to mo_user@ '% ' Identified by ' 123345 ';

4. Grant acts on the columns in the table:

Grant Select (ID, SE, rank) on Phpboke.apache_log to Dba@localhost;

5. Grant functions on stored procedures, functions:

Grant execute on procedure phpboke.pr_add to ' dba ' @ ' localhost ' grant execute in function Phpboke.fn_add to ' dba ' @ ' localhost '

Vi. View MySQL User rights

View current user (Own) permissions:

Show grants;

To view additional MySQL user rights:

Show grants for Dba@localhost;

Vii. revoke permissions that have been given to the MySQL user.

Revoke is similar to Grant's syntax, simply replace the keyword "to" with "from":

Grant all on *.* to dba@localhost;revoke all on *.* from Dba@localhost;

Viii. MySQL Grant, REVOKE user rights considerations

1. Grant, revoke user rights, the user can only reconnect to the MySQL database, permissions to take effect.

2. If you want to allow authorized users to grant these permissions to other users, you need option "grant option"

Grant SELECT on phpboke.* to dba@localhost with GRANT option;
This feature is generally not used. In practice, database permissions are best managed by DBAs.

This error is encountered with the SELECT command denied to user ' username ' ' @ ' host name ' for table ', the solution is to have the following table name authorized, that is, to authorize the core database as well.

I encountered the select command denied to user ' I ' @ '% ' for table ' proc ', which occurs when the stored procedure is invoked, as long as it is necessary to authorize the specified database, what stored procedures, functions, etc. Who knows also to put the database MySQL proc table authorization

The MySQL authorization form has 5 tables: User, DB, host, Tables_priv, and Columns_priv.

The contents of the authorization table are used for the following purposes:
User table
The user table lists the users and their passwords that can connect to the server, and it specifies what global (superuser) permissions they have. Any permissions that are enabled in the user table are global permissions and apply to all databases. For example, if you have the Delete permission enabled, the users listed here can delete records from any table, so think carefully before you do so.

DB table
The DB table lists the databases, and the user has permission to access them. The permissions specified here apply to all tables in a database.

Host table
The host table is used in conjunction with the DB table to control the access of a particular host to the database at a better level, which may be better than using DB alone. This table is not affected by the grant and REVOKE statements, so you may find that you are not using it at all.

Tables_priv table
The Tables_priv table specifies table-level permissions, and one of the permissions specified here applies to all columns of a table.

Columns_priv table
The COLUMNS_PRIV table specifies column-level permissions. The permissions specified here apply to a specific column of a table.

B, such as LAMPP, WAMP, etc. when the phpMyAdmin Client tool is installed, you can modify the httpd-xampp.conf option to

Order Deny,allow
Deny from all
Allow from

By adding the IP you allow after allow from, you can use the phpMyAdmin Web client to prevent the question.

C, you can also connect through many tools that support SSH and SSL channels for verification links. such as: Navicat, sqlyogent and so on.

D, the last one is my most recommended one, the default other machines can not link the security restrictions do not change, through the client tool Navicat connection database, the Navicat directory Ntunnel_ mysql.php upload to your site's root directory (if you have MySQL server installed Web services, and you can parse PHP files, Linux on the default is Apache, this is very convenient), and then create a new link, select the HTTP check using the HTTP channel, in the address input

http://your IP or domain name/ntunnel_mysql.php (if you are in a different location, plus the full path, for security reasons, you can rename the file, such as hello.php.) At this point in the regular entry of the user name password, host name or IP with localhost.

Someone said I MySQL is a separate database server, no web, no PHP environment, so it does not matter, you can use putty and other SSH clients to connect your MySQL server (if your server is a Linux server, If not, you can also install the SSH server on the Windows Server, and do the SSH port mapping (see my putty  SSH port map for specific mapping), map the remote 3306 (MySQL database port) to local, Navicat in the general unchanged, enter the usage name password, you can also link to the remote database.