MySQL sets multiple ways to remotely access a database

Source: Internet
Author: User
Tags mysql command line

Problem: MySQL permissions are set correctly, but still cannot be accessed remotely. 3306 Port is not open by Telnet.

Analysis: MySQL only binds 127.0.0.1 by default, that is, only the local computer can access port 3306.

Workaround: Locate the MySQL configuration file, search for "bind-address", and find this line:

bind-address = 127.0.0.1 in front add a #, comment out this line, save, restart MySQL. Remote access is available, and Telnet can discover that the port is also open.

Example we configure

1. Ensure that skip-networking is removed or blocked, otherwise TCP/IP access is not supported

2. Add line bind-address = 65.55.55.2, replace 65.55.55.2 as your server address

Save and close the configuration file
Restart MySQL server: #/etc/init.d/mysql restart


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 "%"

Code:mysql-u root-p123mysql>use mysql;mysql>update User Set host = '% ' where user = ' root '; Mysql>select host, U Ser from user;

2. Authorization law.

For example, if you want Kevin to use MyPassword to connect to a MySQL server from any host.

GRANT all privileges on * * to ' kevin ' @ '% ' identified by ' MyPassword ' with GRANT OPTION;

if you want to allow the user Kevin to connect to the MySQL server from the IP-192.168.101.234 host and use MyPassword as the password

GRANT all privileges on * * to ' kevin ' @ ' 192.168.101.234 ' identified by ' MyPassword ' with GRANT OPTION;

Note that it's important to have a Linux system firewall, too.

Iptables-i input-p TCP--dport 3306-j ACCEPT


More content:

MySQL remote database imported to local

MySQL command line export database

MySQL Forgot password solution

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

MySQL sets multiple ways to remotely access a database

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.