There are two major strides in opening MySQL's remote login account:
1. Determine that the firewall on the server does not block port 3306.
MySQL default port is 3306, need to determine the firewall does not block 3306 port, otherwise remote is unable to connect via 3306 port to MySQL.
If you specified a different port when you installed MySQL, open the port number that you specified for MySQL in the firewall.
If you don't know how to set up a firewall on your server, check with your server administrator.
2, add to allow remote connection to MySQL users and authorized.
1) First log in to MySQL with the root account
In the Windows Host, click Start menu, Run, enter "cmd", go to the console, MySQL Bin directory, and enter the following command.
In the Linux host, enter the following command at the command prompt line.
CODE: [COPY]
123456 is the root user's password.
2) Create a remote login user and authorize
CODE: [COPY]
- > Grant all privileges the discuz.* to [email protected] ' 123.123.123.123 ' identified by ' 123456 ';
The above statement means that all permissions to the Discuz database are granted to the TED user, allowing the TED user to remotely log on to the 123.123.123.123 IP and set the TED user's password to 123456.
All parameters are analyzed below:
All privileges means giving all permissions to the specified user, which can also be replaced by assigning a specific permission, such as: Select,insert,update,delete,create,drop, and so on, with the "," half-width comma separated by the specific permissions.
Discuz.* means that the above permissions are for which table, discuz refers to the database, followed by the * for all tables, it can be inferred that: for all of the database is authorized as "*. *" For all tables, the entire table for a database is authorized as "database name. *", A table for a database is authorized as the database name. Table name.
Ted indicates which user you want to authorize, and this user can be either an existing user or a non-existent user.
123.123.123.123 represents the IP address that allows remote connections, or "%" if you want to limit the IP of the link.
123456 is the user's password.
After executing the above statement, execute the following statement before it can take effect immediately.
CODE: [copy]> flush privileges;
--------------------------------------------------------------------------------------------------------------- --
Workaround:
1, change the table method:
It may be that your account is not allowed to log on remotely, only on localhost. This time, as long as the computer on the localhost, log in to MySQL, change the "MySQL" Database in the "User" table "host", from "localhost" to "%"
X:\>mysql-u Root-pvmware mysql> use MySQL;
mysql> Update user Set host = '% ' where user = ' root ' and host= ' localhost '; #如果不带 and host= ' localhost ' will error, C3>error 1062 (23000): Duplicate entry '%-root ' for key ' PRIMARY '
Mysql> Select Host, user from user; mysql> flush Privileges; |
Note:mysql> flush privileges; Make the changes effective.
2. Authorization Law:
For example, if you want to myuser use MyPassword to connect to a MySQL server from any host.
Mysql> Grant all privileges on * * to ' myuser ' @ '% ' identified by ' MyPassword ' with GRANT OPTION; |
If you want to allow users to connect to the MySQL server from a host myuser IP 192.168.1.3 and use MyPassword as the password
Mysql> GRANT All privileges on * * to ' myuser ' @ ' 192.168.1.3 ' identified by ' MyPassword ' with GRANT OPTION; |
Transfer from: Http://hi.baidu.com/593313600/blog/item/52c13d3d4640d208baa167cf.html/cmtid/df0698f382f04d5d352acce8------- --------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------Second, ask how to turn on MySQL remote connection Q: Recently learning PHP, With a phpwind forum and FTP traffic plug-ins, you need to connect to the MySQL database remotely. You do not know how to open a remote connection to the local server. Now the forums and FTP traffic plug-ins on the local server are working properly, and the plugin is installed on the other server. Cannot connect to the database. To PW's official help nobody huitie. So come here and ask for help.
Server information
PHP Program version: 4.3.11
MySQL version: 4.1.10-nt
Server-side information: microsoft-iis/5.0
Equipped with phpMyAdmin A1: remote connection to MySQL needs to be done
1. Enter MySQL to create a new user xuys:
Format: Grant permission on the database name. Table name user @ Login host identified by "User password";
Grant Select,update,insert,delete on * * to [e-mail protected] identified by "xuys1234"; To view the results, execute:
Use MySQL;
Select Host,user,password from user; You can see that in the user table has just created the Xuys users, the Host field represents the login of the hosts, its value can be used IP, the hostname, the Host field value to% is to be able to log on any client machine to the MySQL server with Xuys users, it is recommended to be set at development time to%. Update user Set host = '% ' where user = ' Xuys ';
2../mysqladmin-u root-p pwd Reload
./mysqladmin-u root-p pwd shutdown
3../mysqld_safe--user=root &
Remember: Any modifications to the authorization table need to be re-reload, that is, the 3rd step is performed.
If you are unable to connect from the client after the 3 steps above, insert a record in the DB table of the MySQL database by doing the following: use MySQL;
INSERT into DB values (' 192.168.88.234 ', '% ', ' xuys ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ');
Update db Set host = '% ' where user = ' Xuys '; Repeat the 2nd, 3 steps above. A2:web separation from MySQL database is a good choice to avoid because a large number of database queries occupy the CPU and the Web resources are not enough, while the resources of the Web server can provide the most browsing service, and the database server only handles the database transactions separately.
I am not very familiar with the principle of this aspect, my practice is actually the following to say, very simple. We have better experience and skills to share.
Scope of application: with independent host permissions
Hardware configuration: Two servers, as for the specific server hardware configuration is not within the scope of this article
Where: A is a Web server (assuming IP is: 192.192.192.192) and B is a MySQL data server (assuming IP is: 168.168.168.168)
Start action: 1. Configure the Web service in Web Server A. There are many articles about this. Assume that the IP of the Web server is: 192.192.192.192
2. Install MySQL service on database server B
3. Now the new version of MySQL generally does not allow remote connection, you need to establish a remote connection account to use the root account to enter MySQL command line mode
Mysql-u Root-p Pass
Choose to go to MySQL database
Use MySQL;
View all existing accounts and addresses
SELECT ' Host ', ' user ' from ' user ';
For example, mine is:
+------------+-------+
| Host | User |
+------------+-------+
| localhost | |
| localhost | PMA |
| localhost | Root |
+------------+-------+
3 Rows in Set (0.00 sec) that is, there are three (localhost) accounts that allow only local connections, respectively, ROOT,PMA, and empty users.
Now that you decide to let root have the remote link permission for the Web server A above, then:
UPDATE ' user ' SET ' Host ' = ' 192.192.192.192 ' WHERE ' user ' = ' root ' LIMIT 1;
So 192.192.192.192 this Web server can connect to this database server remotely, if you want any remote machine can connect to this database, will be 192.192.192.192 for%, but not recommended, for reasons you know!
If you want to create a new user New_user with remote link permissions, this is the case:
INSERT into ' user ' (' Host ', ' user ', ' Password ', ' select_priv ', ' insert_priv ', ' update_priv ', ' delete_priv ', ' creat E_priv ', ' drop_priv ', ' reload_priv ', ' shutdown_priv ', ' process_priv ', ' file_priv ', ' grant_priv ', ' References_priv ' , ' Index_priv ', ' alter_priv ', ' show_db_priv ', ' super_priv ', ' create_tmp_table_priv ', ' lock_tables_priv ', ' EXECUTE_PR IV ', ' repl_slave_priv ', ' repl_client_priv ', ' ssl_type ', ' ssl_cipher ', ' x509_issuer ', ' x509_subject ', ' max_questions ', ' max_updates ', ' max_connections ') VALUES (' 192.192.192.192 ', ' New_user ', PASSWORD (' New_user_password '), ' y ', ' y ', ' Y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ' y ', ', ', ', ', ', ' 0 ', ' 0 ', ' 0 ');
Change the New_user to the name you want, the password is: New_user_password, of course, you can set it freely.
When your database can be connected remotely, you can set the $dbhost variable in your Web server's forum config.inc.php to the IP of your MySQL database server B: $dbhost = ' 168.168.168.168 ';
In practice, it is best to have two machines in the same network segment/firewall in the same computer room. Of course, if possible, it would be better to place the database server in a local area network within the Web server. Q3: This is still more concise: Grant all on yourdb.* to [email protected] identified by "YourPassword";
Flush privileges; Make permissions effective immediately
MySQL Remote access authorization
