Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to configure MYSQL remote connection, the article is very detailed, has a certain reference value, interested friends must read it!

There are two big steps to open a remote login account for MySQL:

1. Make sure that the firewall on the server does not block port 3306.

The default port of MySQL is 3306. You need to make sure that the firewall does not block port 3306, otherwise the remote cannot connect to MySQL through port 3306.

If you specified a different port when you installed MySQL, open the port number you specified for MySQL in the firewall.

If you don't know how to set up a firewall on your server, consult your server administrator.

2. Add allowing remote connection and authorization of MySQL users.

1) first log in to MySQL with your root account

Click the start menu in the Windows host, run, type "cmd", enter the console, MySQL's bin directory, and enter the following command.

Enter the following command at the command prompt on the Linux host.

CODE: [COPY]

> MySQL-uroot-p123456

123456 is the password of the root user.

2) create a remote login user and authorize

CODE: [COPY]

> grant all PRIVILEGES on discuz.* to ted@'123.123.123.123' identified by '123456'

The above statement authorizes all permissions of the discuz database to the ted user, allows the ted user to log in remotely at 123.123.123.123 IP, and sets the password of the ted user to 123456.

Let's analyze all the parameters one by one:

All PRIVILEGES means to grant all permissions to the specified user, which can also be replaced with a specific permission, such as select,insert,update,delete,create,drop, which is separated by a "," comma.

Discuz.* indicates which table the above permissions are for, discuz refers to the database, and the following * means for all tables, it can be inferred that the authorization for all tables in all databases is "*. *", for all tables in a database is "database name. *", and for a table in a database is "database name. table name".

Ted indicates which user you want to authorize, either an existing user or a non-existent user.

123.123.123.123 indicates the IP address that allows remote connections. If you want to not limit the IP of the link, set it to "%".

123456 is the user's password.

After executing the above statement, then execute the following statement before it can take effect immediately.

CODE: [COPY]

> flush privileges

-

Solution:

1. Table change method:

It may be that your account is not allowed to log in remotely, only in localhost. At this time, as long as on the computer in localhost, after logging in to mysql, change the "host" entry in the "user" table in the "mysql" database from "localhost" to "%"

X:\ > mysql-u root-pvmware

Mysql > use mysql

Mysql > update user set host ='% 'where user =' root'

Mysql > select host, user from user

Mysql > flush privileges

Note: mysql > flush privileges; makes the changes effective.

2. Authorization Law:

For example, if you want myuser to connect to the mysql server from any host using mypassword.

Mysql > GRANT ALL PRIVILEGES ON *. * TO 'myuser'@'%' IDENTIFIED BY' mypassword' WITH GRANT OPTION

If you want to allow the user myuser to connect to the mysql server from the host with 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

Transferred from: http://hi.baidu.com/593313600/blog/item/52c13d3d4640d208baa167cf.html/cmtid/df0698f382f04d5d352acce8

-

Ask how to open a remote connection to MySQL

Q:

Recently learn PHP, installed a phpwind forum and FTP traffic plug-in, need to connect to MySQL database remotely. I don't know how to open a remote connection to the local server. Now the forums and FTP traffic plug-ins on the local server are running normally. Install the plug-ins on another server and can't connect to the database. No one replied to the PW official for help. So come here for help.

Server information

PHP version: 4.3.11

MySQL version: 4.1.10-nt

Server-side information: Microsoft-IIS/5.0

Equipped with phpMyAdmin

A1:

What you need to do to connect to MySQL remotely

1. Go to MySQL and create a new user xuys:

Format: grant permissions on database name. Table name user @ Login Host identified by "user password"

Grant select,update,insert,delete on *. * to xuys@192.168.88.234 identified by "xuys1234"

View the results and execute:

Use mysql

Select host,user,password from user

You can see that there is the xuys user just created in the user table. The host field indicates the login host. The value can be either IP or hostname. Changing the value of the host field to% means that you can log in to the MySQL server as a xuys user on any client machine. It is recommended to set it to% at the time of development.

Update user set host ='% 'where user =' xuys'

two。

. / mysqladmin-u root-p pwd reload

. / mysqladmin-u root-p pwd shutdown

3.

. / mysqld_safe-- user=root &

Remember: any changes to the authorization table require re-reload, that is, step 3.

If you cannot connect from the client after the above three steps, 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) (' 192.168.88.234).

Update db set host ='% 'where user =' xuys'

Repeat steps 2 and 3 above.

A2:

It is a good choice to separate Web from MySQL database to avoid the shortage of Web resources because a large number of database queries occupy CPU. At the same time, the Web server can provide browsing services as much as possible, while the database server only handles database transactions alone.

I don't know much about the principle in this respect. In fact, what I do is what I want to say below. It's very simple. If you have better experiences and skills, you might as well share them.

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 An is the Web server (assuming IP is 192.192.192.192) and B is the MySQL data server (assuming IP is 168.168.168.168)

Hand action:

1. Configure the Web service on Web server A. There are a lot of articles about this. Suppose the IP of the Web server is: 192.192.192.192

two。 Install the MySQL service on database server B

3. Now the new version of MySQL generally does not allow remote connection by default, so you need to establish a remote connection account.

Use the root account to enter MySQL from the command line

Mysql-u root-p pass

Choose to enter the MySQL database

Use mysql

Check 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)

In other words, there are three localhost accounts that only allow local connections, namely root,pma, empty users.

Now that you have decided to let root have access to the remote link to Web server An above, that's it:

UPDATE `user`SET `Host` = '192.192.192.192' WHERE `User` = 'root' LIMIT 1

In this way, the Web server of 192.192.192.192 can connect to the database server remotely. If you want any remote machine to connect to this database, change 192.192.192.192 to%, but this is not recommended. You know the reason!

If you want to create a new user new_user with remote link permission, that's it:

INSERT INTO `user` (`Host`, `User`, `Password`, `Select_ priv`, `User`, `Update_ priv`, `Delete_ priv`, `Create_ 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_ priv`, `Repl_slave_ priv`, `Repl_client_ priv`, `ssl_ type`, `ssl_ cipher`, `x509issuer`, `x509_ privt`, `max_ privs`, `max_ updates` `192.192.192.192) VALUES ('192.192.192.192),' new_user', PASSWORD ('new_user_password'),' Yee, 'Yboy,' Yee, 'Yboy,' YYue, 'YYOU,' YYOU, 'YYOU Yee, Yee, 0th, 0th, 0th, 0th)

Just change new_user to the name you want, the password is: new_user_password, of course, you can set it as you like.

When your database can be connected remotely, you can set the $dbhost variable in the forum config.inc.php of your Web server to the IP of your MySQL database server B.

$dbhost = '168.168.168.168'

In practice, it is best for two machines to be 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 the local area network within the Web server network.

Q3:

Let's keep it this way:

Grant all on yourdb.* to yourUsername@yourHost identified by "yourPassword"

Flush privileges; / / make permissions effective immediately

These are all the contents of the article "how to configure remote connections in MYSQL". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.