{"id":2779,"date":"2022-09-29T09:55:51","date_gmt":"2022-09-29T09:55:51","guid":{"rendered":"https:\/\/www.webhi.com\/how-to\/?p=2779"},"modified":"2022-10-01T12:11:06","modified_gmt":"2022-10-01T12:11:06","slug":"allow-remote-access-to-mysql-database","status":"publish","type":"post","link":"https:\/\/www.webhi.com\/how-to\/allow-remote-access-to-mysql-database\/","title":{"rendered":"Allow Remote Access to MySQL database"},"content":{"rendered":"\n
\"Allow<\/figure>\n\n\n\n

Many websites and applications begin with the web server and database backend running on the same machine. A setup like this, on the other hand, can become complicated and challenging to scale over time. The solution is to separate these functions and establishing a remote database. Permitting the server and database to grow independently on their respective machines.<\/p>\n\n\n\n

To enable this, open up your mysqld.cnf<\/code> file:<\/p>\n\n\n\n

$ sudo nano \/etc\/mysql\/mysql.conf.d\/mysqld.cnf<\/code><\/pre>\n\n\n\n
or<\/kbd><\/p>\n\n\n\n
$ sudo nano \/etc\/mysql\/my.cnf<\/code><\/pre>\n\n\n\n
or<\/kbd><\/p>\n\n\n\n
$ sudo nano \/etc\/my.cnf<\/code><\/pre>\n\n\n\n
Find the line that starts with the bind-address<\/kbd> directive. It will appear as follows:<\/p>\n\n\n\n
# If MySQL is running as a replication slave, this should be\n# changed. Ref https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/server-system-variables.html#sysvar_tmpdir\n# tmpdir                = \/tmp\n#\n# Instead of skip-networking the default is now to listen only on\n# localhost which is more compatible and is not less secure.\nbind-address            = 127.0.0.1\nmysqlx-bind-address     = 127.0.0.1<\/pre>\n\n\n\n
This value is set to 127.0.0.1<\/kbd> by default, which means that the server will only look for local connections. This directive must be modified to refer to an external IP address. For troubleshooting purposes, you could set this directive to a wildcard IP address, such as *<\/kbd> , : : <\/kbd>, or 0.0.0.0<\/kbd>:<\/p>\n\n\n\n
# If MySQL is running as a replication slave, this should be\n# changed. Ref https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/server-system-variables.html#sysvar_tmpdir\n# tmpdir                = \/tmp\n#\n# Instead of skip-networking the default is now to listen only on\n# localhost which is more compatible and is not less secure.\nbind-address            = 0.0.0.0<\/pre>\n\n\n\n
Save and close the file after changing this parameter.<\/p>\n\n\n\n
Then restart the MySQL service to apply the changes made to mysqld.cnf<\/kbd>:<\/p>\n\n\n\n
$ sudo systemctl restart mysql<\/code><\/pre>\n\n\n\n
If you already have a MySQL user account that you intend to use to connect to the database from a remote host, you must reconfigure that account to connect from the remote server rather than localhost. Open the MySQL client as the root MySQL user or another privileged user account:<\/p>\n\n\n\n
$ sudo mysql<\/code><\/pre>\n\n\n\n
if User root has a password use:<\/p>\n\n\n\n
$ mysql -u root -p<\/code><\/pre>\n\n\n\n
With the following command, we can create a new user account that will only connect from the remote host:<\/p>\n\n\n\n
mysql> CREATE USER 'bob'@'r_server_ip' IDENTIFIED BY 'password';<\/code><\/pre>\n\n\n\n
Or To change an existing user\u2019s host, you can use MySQL\u2019s RENAME USER<\/code> command. Run the following command:<\/p>\n\n\n\n
mysql> RENAME USER 'bob'@'localhost' TO 'bob'@'r_server_ip';<\/code><\/pre>\n\n\n\n
Then, for your specific needs, assign the new user the relevant rights. The following example allows a user to CREATE, ALTER, and DROP databases, tables, and users, as well as INSERT, UPDATE, and DELETE data from any table on the server. It also allows the user to query data using SELECT, construct foreign keys using the REFERENCES keyword, and conduct FLUSH operations using the RELOAD permission. You should, however, only provide people the access they require, so feel free to change your own user’s privileges as required.<\/p>\n\n\n\n
mysql> GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'bob'@'r_server_ip' WITH GRANT OPTION;<\/code><\/pre>\n\n\n\n
After that, it’s a good idea to execute the FLUSH PRIVILEGES<\/kbd> command. This will release any memory cached by the server as a result of the previous CREATE USER<\/kbd> and GRANT<\/kbd> statements:<\/p>\n\n\n\n
mysql> FLUSH PRIVILEGES;<\/code><\/pre>\n\n\n\n
The MySQL client can then be closed:<\/p>\n\n\n\n
mysql> exit;<\/code><\/pre>\n\n\n\n
Finally, if you have a firewall installed on your database server, you must open port 3306 \u2014 MySQL’s default port \u2014 to enable communication to MySQL.<\/p>\n\n\n\n
If you only want to connect to the database server from one machine, use the following command to grant that machine exclusive authorization to connect to the database remotely. Replace r_server_ip<\/kbd> with the real IP address of the computer to which you want to connect:<\/p>\n\n\n\n
Or to enable anyone to access your MySQL database in use:<\/p>\n\n\n\n
UFW<\/kbd><\/p>\n\n\n\n
$ sudo ufw allow from r_server_ip to any port 3306<\/code><\/pre>\n\n\n\n
Iptables<\/kbd><\/p>\n\n\n\n
$ sudo iptables -A INPUT -p tcp -s r_server_ip --dport 3306 -j ACCEPT\n$ sudo iptables-save<\/code><\/pre>\n\n\n\n
firewalld<\/kbd><\/p>\n\n\n\n
$ sudo firewall-cmd --permanent --zone=public --add-port=3306\/tcp<\/code><\/pre>\n\n\n\n
After that, try remotely accessing your database from another machine:<\/p>\n\n\n\n
Note:<\/strong> If you configured your firewall to only accept connections from a single IP address, you must attempt to access the database using the machine associated with that address.<\/pre>\n\n\n\n
$ mysql -u user -h db_server_ip -p<\/code><\/pre>\n\n\n\n
If you can access your database, it demonstrates that the bind-address<\/kbd> directive in your configuration file was the source of the problem. However, setting bind-address<\/kbd> to 0.0.0.0<\/kbd> is unsafe since it permits connections to your server from any IP address.<\/p>\n","protected":false},"excerpt":{"rendered":"
Many websites and applications begin with the web server and database backend running on the same machine. A setup like this, on the other hand, can become complicated and challenging to scale over time. The solution is to separate these functions and establishing a remote database. Permitting the server and database to grow independently on ..<\/p>\n
<\/div>\n
Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[57,3],"tags":[],"yoast_head":"\nAllow Remote Access to MySQL database - WebHi Tutorials & Documentations<\/title>\n<meta name=\"description\" content=\"Easy tutorial to allow remote user access to MySQL and MariaDB database step by step. It works on most of Linux distributions (Ubuntu, CentOS, RedHat, Debian, Fedora, Suse, ...)\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.webhi.com\/how-to\/allow-remote-access-to-mysql-database\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Allow Remote Access to MySQL database - WebHi Tutorials & Documentations\" \/>\n<meta property=\"og:description\" content=\"Easy tutorial to allow remote user access to MySQL and MariaDB database step by step. It works on most of Linux distributions (Ubuntu, CentOS, RedHat, Debian, Fedora, Suse, ...)\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.webhi.com\/how-to\/allow-remote-access-to-mysql-database\/\" \/>\n<meta property=\"og:site_name\" content=\"WebHi Tutorials & Documentations\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/webhi.technology\" \/>\n<meta property=\"article:published_time\" content=\"2022-09-29T09:55:51+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-10-01T12:11:06+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.webhi.com\/how-to\/gilrogre\/2022\/09\/mysql_remote_en.jpg\" \/>\n<meta name=\"author\" content=\"webhi\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@WebHiTechnology\" \/>\n<meta name=\"twitter:site\" content=\"@WebHiTechnology\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"webhi\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"TechArticle\",\"@id\":\"https:\/\/www.webhi.com\/how-to\/allow-remote-access-to-mysql-database\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.webhi.com\/how-to\/allow-remote-access-to-mysql-database\/\"},\"author\":{\"name\":\"webhi\",\"@id\":\"https:\/\/www.webhi.com\/how-to\/#\/schema\/person\/b31e76e2311cdc0bb90f5e2733059a54\"},\"headline\":\"Allow Remote Access to MySQL database\",\"datePublished\":\"2022-09-29T09:55:51+00:00\",\"dateModified\":\"2022-10-01T12:11:06+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.webhi.com\/how-to\/allow-remote-access-to-mysql-database\/\"},\"wordCount\":512,\"commentCount\":2,\"publisher\":{\"@id\":\"https:\/\/www.webhi.com\/how-to\/#organization\"},\"articleSection\":[\"Databases\",\"Security\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.webhi.com\/how-to\/allow-remote-access-to-mysql-database\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.webhi.com\/how-to\/allow-remote-access-to-mysql-database\/\",\"url\":\"https:\/\/www.webhi.com\/how-to\/allow-remote-access-to-mysql-database\/\",\"name\":\"Allow Remote Access to MySQL database - WebHi Tutorials & Documentations\",\"isPartOf\":{\"@id\":\"https:\/\/www.webhi.com\/how-to\/#website\"},\"datePublished\":\"2022-09-29T09:55:51+00:00\",\"dateModified\":\"2022-10-01T12:11:06+00:00\",\"description\":\"Easy tutorial to allow remote user access to MySQL and MariaDB database step by step. It works on most of Linux distributions (Ubuntu, CentOS, RedHat, Debian, Fedora, Suse, ...)\",\"breadcrumb\":{\"@id\":\"https:\/\/www.webhi.com\/how-to\/allow-remote-access-to-mysql-database\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.webhi.com\/how-to\/allow-remote-access-to-mysql-database\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.webhi.com\/how-to\/allow-remote-access-to-mysql-database\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.webhi.com\/how-to\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Allow Remote Access to MySQL database\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.webhi.com\/how-to\/#website\",\"url\":\"https:\/\/www.webhi.com\/how-to\/\",\"name\":\"WebHi Tutorials & Documentations\",\"description\":\"System administration and knowledge base\",\"publisher\":{\"@id\":\"https:\/\/www.webhi.com\/how-to\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.webhi.com\/how-to\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.webhi.com\/how-to\/#organization\",\"name\":\"WebHi Technology\",\"url\":\"https:\/\/www.webhi.com\/how-to\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.webhi.com\/how-to\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.webhi.com\/how-to\/gilrogre\/2022\/07\/logo.png\",\"contentUrl\":\"https:\/\/www.webhi.com\/how-to\/gilrogre\/2022\/07\/logo.png\",\"width\":288,\"height\":95,\"caption\":\"WebHi Technology\"},\"image\":{\"@id\":\"https:\/\/www.webhi.com\/how-to\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/webhi.technology\",\"https:\/\/twitter.com\/WebHiTechnology\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.webhi.com\/how-to\/#\/schema\/person\/b31e76e2311cdc0bb90f5e2733059a54\",\"name\":\"webhi\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.webhi.com\/how-to\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/www.webhi.com\/how-to\/ahuphiph\/litespeed\/avatar\/e20da107d0f4c765ead2eef88ad019d8.jpg?ver=1781819544\",\"contentUrl\":\"https:\/\/www.webhi.com\/how-to\/ahuphiph\/litespeed\/avatar\/e20da107d0f4c765ead2eef88ad019d8.jpg?ver=1781819544\",\"caption\":\"webhi\"},\"sameAs\":[\"https:\/\/www.webhi.com\/how-to\"],\"url\":\"https:\/\/www.webhi.com\/how-to\/author\/webhi\/\"}]}<\/script>\n","yoast_head_json":{"title":"Allow Remote Access to MySQL database - WebHi Tutorials & Documentations","description":"Easy tutorial to allow remote user access to MySQL and MariaDB database step by step. It works on most of Linux distributions (Ubuntu, CentOS, RedHat, Debian, Fedora, Suse, ...)","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.webhi.com\/how-to\/allow-remote-access-to-mysql-database\/","og_locale":"en_US","og_type":"article","og_title":"Allow Remote Access to MySQL database - WebHi Tutorials & Documentations","og_description":"Easy tutorial to allow remote user access to MySQL and MariaDB database step by step. It works on most of Linux distributions (Ubuntu, CentOS, RedHat, Debian, Fedora, Suse, ...)","og_url":"https:\/\/www.webhi.com\/how-to\/allow-remote-access-to-mysql-database\/","og_site_name":"WebHi Tutorials & Documentations","article_publisher":"https:\/\/www.facebook.com\/webhi.technology","article_published_time":"2022-09-29T09:55:51+00:00","article_modified_time":"2022-10-01T12:11:06+00:00","og_image":[{"url":"https:\/\/www.webhi.com\/how-to\/gilrogre\/2022\/09\/mysql_remote_en.jpg"}],"author":"webhi","twitter_card":"summary_large_image","twitter_creator":"@WebHiTechnology","twitter_site":"@WebHiTechnology","twitter_misc":{"Written by":"webhi","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"TechArticle","@id":"https:\/\/www.webhi.com\/how-to\/allow-remote-access-to-mysql-database\/#article","isPartOf":{"@id":"https:\/\/www.webhi.com\/how-to\/allow-remote-access-to-mysql-database\/"},"author":{"name":"webhi","@id":"https:\/\/www.webhi.com\/how-to\/#\/schema\/person\/b31e76e2311cdc0bb90f5e2733059a54"},"headline":"Allow Remote Access to MySQL database","datePublished":"2022-09-29T09:55:51+00:00","dateModified":"2022-10-01T12:11:06+00:00","mainEntityOfPage":{"@id":"https:\/\/www.webhi.com\/how-to\/allow-remote-access-to-mysql-database\/"},"wordCount":512,"commentCount":2,"publisher":{"@id":"https:\/\/www.webhi.com\/how-to\/#organization"},"articleSection":["Databases","Security"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.webhi.com\/how-to\/allow-remote-access-to-mysql-database\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.webhi.com\/how-to\/allow-remote-access-to-mysql-database\/","url":"https:\/\/www.webhi.com\/how-to\/allow-remote-access-to-mysql-database\/","name":"Allow Remote Access to MySQL database - WebHi Tutorials & Documentations","isPartOf":{"@id":"https:\/\/www.webhi.com\/how-to\/#website"},"datePublished":"2022-09-29T09:55:51+00:00","dateModified":"2022-10-01T12:11:06+00:00","description":"Easy tutorial to allow remote user access to MySQL and MariaDB database step by step. It works on most of Linux distributions (Ubuntu, CentOS, RedHat, Debian, Fedora, Suse, ...)","breadcrumb":{"@id":"https:\/\/www.webhi.com\/how-to\/allow-remote-access-to-mysql-database\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.webhi.com\/how-to\/allow-remote-access-to-mysql-database\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.webhi.com\/how-to\/allow-remote-access-to-mysql-database\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.webhi.com\/how-to\/"},{"@type":"ListItem","position":2,"name":"Allow Remote Access to MySQL database"}]},{"@type":"WebSite","@id":"https:\/\/www.webhi.com\/how-to\/#website","url":"https:\/\/www.webhi.com\/how-to\/","name":"WebHi Tutorials & Documentations","description":"System administration and knowledge base","publisher":{"@id":"https:\/\/www.webhi.com\/how-to\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.webhi.com\/how-to\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.webhi.com\/how-to\/#organization","name":"WebHi Technology","url":"https:\/\/www.webhi.com\/how-to\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.webhi.com\/how-to\/#\/schema\/logo\/image\/","url":"https:\/\/www.webhi.com\/how-to\/gilrogre\/2022\/07\/logo.png","contentUrl":"https:\/\/www.webhi.com\/how-to\/gilrogre\/2022\/07\/logo.png","width":288,"height":95,"caption":"WebHi Technology"},"image":{"@id":"https:\/\/www.webhi.com\/how-to\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/webhi.technology","https:\/\/twitter.com\/WebHiTechnology"]},{"@type":"Person","@id":"https:\/\/www.webhi.com\/how-to\/#\/schema\/person\/b31e76e2311cdc0bb90f5e2733059a54","name":"webhi","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.webhi.com\/how-to\/#\/schema\/person\/image\/","url":"https:\/\/www.webhi.com\/how-to\/ahuphiph\/litespeed\/avatar\/e20da107d0f4c765ead2eef88ad019d8.jpg?ver=1781819544","contentUrl":"https:\/\/www.webhi.com\/how-to\/ahuphiph\/litespeed\/avatar\/e20da107d0f4c765ead2eef88ad019d8.jpg?ver=1781819544","caption":"webhi"},"sameAs":["https:\/\/www.webhi.com\/how-to"],"url":"https:\/\/www.webhi.com\/how-to\/author\/webhi\/"}]}},"_links":{"self":[{"href":"https:\/\/www.webhi.com\/how-to\/wp-json\/wp\/v2\/posts\/2779"}],"collection":[{"href":"https:\/\/www.webhi.com\/how-to\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.webhi.com\/how-to\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.webhi.com\/how-to\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.webhi.com\/how-to\/wp-json\/wp\/v2\/comments?post=2779"}],"version-history":[{"count":45,"href":"https:\/\/www.webhi.com\/how-to\/wp-json\/wp\/v2\/posts\/2779\/revisions"}],"predecessor-version":[{"id":2884,"href":"https:\/\/www.webhi.com\/how-to\/wp-json\/wp\/v2\/posts\/2779\/revisions\/2884"}],"wp:attachment":[{"href":"https:\/\/www.webhi.com\/how-to\/wp-json\/wp\/v2\/media?parent=2779"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.webhi.com\/how-to\/wp-json\/wp\/v2\/categories?post=2779"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.webhi.com\/how-to\/wp-json\/wp\/v2\/tags?post=2779"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}