{"id":9981,"date":"2021-09-20T12:59:54","date_gmt":"2021-09-20T12:59:54","guid":{"rendered":"https:\/\/linuxways.net\/?p=9981"},"modified":"2021-09-20T12:59:54","modified_gmt":"2021-09-20T12:59:54","slug":"how-to-allow-remote-connections-to-mysql","status":"publish","type":"post","link":"https:\/\/linuxways.net\/de\/centos\/how-to-allow-remote-connections-to-mysql\/","title":{"rendered":"How to Allow Remote Connections to MySQL"},"content":{"rendered":"

Introduction<\/h2>\n

In the traditional deployment, people often install the MySQL server and use it from the same host. However, it\u2019s highly recommended that we should separate the MySQL server and client, the MySQL server should be accessed from a remote host. This method helps the system become more secure and increases performance.<\/p>\n

To make the above method possible, we have to configure the MySQL server to allow remote connection from its client.<\/p>\n

Configure the MySQL server<\/h2>\n

Firstly, let\u2019s configure the MySQL server to listen on a pre-defined IP or all IP addresses on your server.<\/p>\n

If the MySQL server is in the same network as the clients, you should configure the listen port on the private IP.<\/p>\n

On another hand, if you need to connect to the MySQL server through a public network, let\u2019s configure the server following the below configuration:<\/p>\n

Open the configuration file:<\/p>\n

$ sudo vim \/etc\/mysql\/mysql.conf.d\/mysqld.cnf<\/pre>\n
Go to the line that begins with bind-address<\/p>\n
By default, the value of bind-address is 127.0.0.1<\/p>\n
<\/p>\n
If you want to set the MySQL server to listen on all of the IP addresses, configure the value of bind-address = 0.0.0.0<\/p>\n
To make the change effect, let\u2019s restart the MySQL service:<\/p>\n
$ sudo systemctl restart mysql<\/pre>\n
Grant access to a user from remote hosts<\/h2>\n
In this part, we will show you the way to allow access to a database to remote users.<\/p>\n
Log in to the MySQL server:<\/p>\n
$ sudo mysql<\/pre>\n
Then,<\/p>\n
mysql> GRANT ALL ON DATABASE-NAME.* TO username@IP-ADDRESS IDENTIFIED BY \u2018USER-PASSWORD\u2019;<\/pre>\n
For example:<\/p>\n
mysql> GRANT ALL ON linuxways.* TO karim@10.0.0.5 IDENTIFIED BY \u2018mysecuredpass\u2019;<\/pre>\n
Configure Firewall<\/h2>\n
The MySQL server listens on port 3306. You have to open this port to allow traffic from the remote hosts. You can use one of these below methods:<\/p>\n
iptables<\/strong><\/pre>\n
$ sudo iptables -A INPUT -p tcp --destination-port 3306 -j ACCEPT<\/pre>\n
<\/strong><\/p>\n
ufw<\/strong><\/pre>\n
If you use ufw to open port 3306, you can simply run the following command:<\/p>\n
$ sudo ufw allow 3306\/tcp<\/pre>\n
Conclusion<\/h2>\n
You\u2019ve already go through the details of how to allow remote connections to a MySQL server.<\/p>\n
If you have any concerns, please let me know. Thank you for reading.<\/p>","protected":false},"excerpt":{"rendered":"
Introduction In the traditional deployment, people often install the MySQL server and use it from the same host. However, it\u2019s highly recommended that we should separate the MySQL…<\/p>","protected":false},"author":1,"featured_media":10295,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1,4,5,83,165,2],"tags":[27],"class_list":["post-9981","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-centos","category-debian","category-mint","category-opensuse","category-red-hat","category-ubuntu","tag-mysql"],"_links":{"self":[{"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/posts\/9981","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/comments?post=9981"}],"version-history":[{"count":0,"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/posts\/9981\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/media\/10295"}],"wp:attachment":[{"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/media?parent=9981"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/categories?post=9981"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/tags?post=9981"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}