Bind address mysql linux

MySQL: How to allow remote connection to mysql

I have installed MySQL Community Edition 5.5 on my local machine and I want to allow remote connections so that I can connect from external source. How can I do that?

19 Answers 19

That is allowed by default on MySQL.

What is disabled by default is remote root access. If you want to enable that, run this SQL command locally:

 GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION; FLUSH PRIVILEGES; 

And then find the following line and comment it out in your my.cnf file, which usually lives on /etc/mysql/my.cnf on Unix/OSX systems. In some cases the location for the file is /etc/mysql/mysql.conf.d/mysqld.cnf).

If it’s a Windows system, you can find it in the MySQL installation directory, usually something like C:\Program Files\MySQL\MySQL Server 5.5\ and the filename will be my.ini .

And restart the MySQL server (Unix/OSX, and Windows) for the changes to take effect.

Ok so I’ve added the binnd-address = 127.0.0.1 at the end of the file and I’ve tried to connect using navicat from external host and I get 10060 error

Hmm, you don’t need to add it. Like my answer said, you need to comment it out, not add it. There’s one in there by default, so you’d need to find that, and comment it out by prefixing it with #

Hmm , in \MYsql Server 5.5\ there’s only 1 ini file called my-default.ini and the only line without the # is this : sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

To those looking for the my.cnf file and not finding a bind-address directive in it, note that in my case (MySQL version 14.14 on Ubuntu 16.04.2) the location for the file was /etc/mysql/mysql.conf.d/mysqld.cnf

I follow and run command but mysql return You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘IDENTIFIED BY ‘*****’ WITH GRANT OPTION’ at line 1 . What should I do?

After doing all of above I still couldn’t login as root remotely, but Telnetting to port 3306 confirmed that MySQL was accepting connections.

I started looking at the users in MySQL and noticed there were multiple root users with different passwords.

select user, host, password from mysql.user; 

So in MySQL I set all the passwords for root again and I could finally log in remotely as root .

use mysql; update user set password=PASSWORD('NEWPASSWORD') where User='root'; flush privileges; 

This is correct. While following steps in other answers, user accounts with empty password seem to be created. So you need to add passwords for them.

SQL for 5.7 and above update user set authentication_string=password(‘YOUR_PASSWORD’) where user=’root’;

Just a note from my experience, you can find configuration file under this path /etc/mysql/mysql.conf.d/mysqld.cnf .

(I struggled for some time to find this path)

This is highly dependent on what distribution you are using. Best way is to just search for it: sudo find /etc -iname ‘mysql*.cnf’

In my case I was trying to connect to a remote mysql server on cent OS. After going through a lot of solutions (granting all privileges, removing ip bindings,enabling networking) problem was still not getting solved.

As it turned out, while looking into various solutions,I came across iptables, which made me realize mysql port 3306 was not accepting connections.

Here is a small note on how I checked and resolved this issue.

telnet (mysql server ip) [portNo]

iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT

• Would not recommend this for production environment, but if your iptables are not configured properly, adding the rules might not still solve the issue. In that case following should be done:

All process for remote login. Remote login is off by default.You need to open it manually for all ip..to give access all ip

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password'; 

GRANT ALL PRIVILEGES ON *.* TO 'root'@'your_desire_ip' IDENTIFIED BY 'password'; 

You can check your User Host & Password

SELECT host,user,authentication_string FROM mysql.user; 

Now your duty is to change this

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf 

if you not find this on there then try this

sudo service mysql restart 

Please follow the below mentioned steps inorder to set the wildcard remote access for MySQL User.

(1) Open cmd.

(2) navigate to path C:\Program Files\MySQL\MySQL Server 5.X\bin and run this command.

mysql -u root -p

(3) Enter the root password.

(4) Execute the following command to provide the permission.

GRANT ALL PRIVILEGES ON *.* TO ‘USERNAME’@’IP’ IDENTIFIED BY ‘PASSWORD’;

USERNAME: Username you wish to connect to MySQL server.

IP: Public IP address from where you wish to allow access to MySQL server.

PASSWORD: Password of the username used.

IP can be replaced with % to allow user to connect from any IP address.

(5) Flush the previleges by following command and exit.

FLUSH PRIVILEGES;

exit; or \q

Источник

MySQL: Allow remote connections

After installing a MySQL server on a Linux system, by default it will only accept incoming connections from itself (i.e. the loopback address 127.0.0.1 ).

This default configuration works perfectly fine if you are only trying to read or write information from the database on the same server. So users that host their website and MySQL server on the same box won’t need to do any extra configuration to allow remote connections.

If you want to allow remote connections to your MySQL server, because you have other computers and/or users that need to access that data, you will need to bind the MySQL service to a public IP address on your server, and perhaps allow incoming MySQL connections through your system firewall.

In this tutorial, we will take you through the step by step instructions to allow remote connections to a MySQL server on a Linux system. These instructions should work independently of whichever Linux distro you are using.

In this tutorial you will learn:

• How to allow remote connections to MySQL server
• How to allow remote connections to MySQL through system firewall
• How to create or alter a MySQL user to allow remote connections

Software Requirements and Linux Command Line Conventions

Category	Requirements, Conventions or Software Version Used
System	Linux system
Software	MySQL
Other	Privileged access to your Linux system as root or via the sudo command.
Conventions	# – requires given linux commands to be executed with root privileges either directly as a root user or by use of sudo command $ – requires given linux commands to be executed as a regular non-privileged user

MySQL: Allow remote connections step by step instructions

First, we will need to setup the MySQL service to be accessible from remote machines by configuring a public bind address in the MySQL configuration file.

Second, we will need to allow remote access through our system firewall. By default, MySQL runs on port 3306, so connections to this port will need allowed through.

Third, we will need to create a new user or edit an existing one to make it accessible from remote IP addresses. We can choose to allow all IP addresses or just particular ones.

Configure MySQL bind address

1. We will start by opening the /etc/mysql/mysql.cnf file. With root permissions, open this in nano or your favorite text editor.

$ sudo nano /etc/mysql/mysql.cnf

[mysqld] bind-address = 10.1.1.1

If you want, you can instead use 0.0.0.0 as your bind address, which is a wildcard and should bind the service to all reachable interfaces. This is not recommended, but can be good for troubleshooting if you encounter problems later on.

$ sudo systemctl restart mysql

$ sudo systemctl restart mysqld

Allow remote access through firewall

Assuming you are using port 3306 for your MySQL server, we will need to allow this through the system firewall. The command you need to execute is going to depend on the distribution you are using. Refer to the list below or adapt the command as needed to adhere to your own system’s firewall syntax.

On Ubuntu systems and others that use ufw (uncomplicated firewall):

On Red Hat, CentOS, Fedora, and derivative systems that use firewalld:

$ sudo firewall-cmd --zone=public --add-service=mysql --permanent $ sudo firewall-cmd --reload

And the good old iptables command that should work on any system:

$ sudo iptables -A INPUT -p tcp --dport 3306 -m conntrack --ctstate NEW,ESTABLISHED -j ACCEPT

Allow remote connections to a particular user

Now that the MySQL service can accept incoming connections and our firewall will allow them through, we just need to configure our user to accept remote connections.

Start by opening up MySQL with the root account.

Or, on some configurations you may be required to enter the following command and provide your root password:

mysql> RENAME USER 'linuxconfig'@'localhost' TO 'linuxconfig'@'10.150.1.1';

Or, if you are creating this user for the first time, we will use the CREATE USER command. Be sure to substitute the following username, IP address, and password with your own.

mysql> CREATE USER 'linuxconfig'@'10.150.1.1' IDENTIFIED BY 'password_here';

mysql> RENAME USER 'linuxconfig'@'localhost' TO 'linuxconfig'@'%';

mysql> CREATE USER 'linuxconfig'@'%' IDENTIFIED BY 'password_here';

That’s all there is to it. After granting your user access to one or more databases, you will be able to use the account credentials to access the database remotely.

Closing Thoughts

In this tutorial, we saw how to allow remote connections to the MySQL service on a Linux system. This was a three part process of making the service accessible, allowing connections through the firewall, and making an accessible MySQL account. Since MySQL works basically the same across all distributions, these steps should be usable for everyone.

Related Linux Tutorials:

Comments and Discussions

Источник

Доступ к mysql из-вне

Для доступа к MySQL из-вне должно быть выполнено 2 условия:

1. MySQL должен слушать все адреса, а не только 127.0.0.1
2. У пользователя должны быть пермишены для коннекта не только с localhost (с самого сервера где установлена БД), а и других адресов.

Слушать все адреса

Во-первых, изначально mysql слушает только localhost. Нужно исправить это в конфигурационном файле my.cnf (обычно в линуксах путь /etc/mysql/my.cnf). В my.cnf находим строку:

Теперь после service mysql restart мы можем коннектиться к mysql с любого ip (если создан пользователь). При этом для супер-пользователя root доступ из-вне под пустым паролем остаётся закрыт.

bind-address 127.0.0.1 означает, что mysql слушает соединения только с текущего хоста.

Создание пользователя для коннекта к базе данных из-вне

Во-вторых, нужно создать пользователя, под которым мы будем подключаться из-вне к mysql. У пользователя должны быть пермишены для подключения не только по localhost, но и иных адресов.

CREATE USER 'username'@'%' IDENTIFIED BY 'password'; #создаём пользователя username с паролем password GRANT ALL PRIVILEGES ON database.* TO 'username'@'%'; #даём пользователю username все права для работы с базой данных database FLUSH PRIVILEGES; # применяем новые права (чтобы не делать restart)

В команде CREATE USER мы создали пользователя, который может коннектиться к mysql со всех ip. Мы можем разрешать коннектиться только с определённых ip, например:

#создаём пользователя user с паролем pass, который может подключаться только с ip '10.10.50.50 CREATE USER 'user'@'10.10.50.50' IDENTIFIED BY 'pass';

Если разрешаете внешние подключения к базе данных, то нужно давать сложные пароли пользователям mysql.

В случае проблем с внешними подключениями, проверить не блокирует ли брандмауэр или фаервол MySQL-порт 3306 или другой, если используете не стандартный порт.

mysql> show variables like 'port'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | port | 3306 | +---------------+-------+ 1 row in set (0,05 sec)

После изменения bind-address в MySQL и создания пользователя с пермишенами (либо грант пермишенов текущим пользователям), к базе данных можно подключиться передавая параметр -h . Хост — адрес машины, на которой установлен MySQL:

Источник