{"id":22475,"date":"2023-10-15T04:31:48","date_gmt":"2023-10-15T04:31:48","guid":{"rendered":"https:\/\/linuxways.net\/?p=22475"},"modified":"2023-10-19T02:14:37","modified_gmt":"2023-10-19T02:14:37","slug":"allow-remote-connections-mysql","status":"publish","type":"post","link":"https:\/\/linuxways.net\/de\/scripting\/allow-remote-connections-mysql\/","title":{"rendered":"How to Allow Remote Connections to MySQL"},"content":{"rendered":"
\nMySQL is a famous free-to-use\u00a0relational database administration system due to its reliability, scalability, and user-friendliness. MySQL is set up by default to allow connections exclusively from the localhost, limiting the access from the database server to the local machine. There are times, however, when remote connections to the MySQL server are required, particularly when numerous users need to access the database from various places. Enabling remote connections can help the teams cooperate more effectively by allowing for continuous communication.<\/p>\n

Users can connect to the database server from multiple locations by setting MySQL to allow the remote connections. This promotes productivity and cooperation. This adaptability enables the enterprises and organizations to take use of MySQL’s capabilities in dispersed contexts. This allows collaborative data management and improves the overall operational efficiency.<\/p>\n

Installing MySQL in Linux<\/strong><\/h2>\n

To install MySQL on our Linux system, refresh or update the package index first.<\/p>\n

$ <\/span>sudo<\/span> apt update<\/div><\/div>\n

Setup the MySQL server software. We can select either the standard MySQL server or the community edition which is also known as MySQL Server CE.<\/p>\n

$ <\/span>sudo<\/span> apt install<\/span> mysql-server<\/div><\/div>\n

The user has to set a password for the MySQL during the installation.<\/p>\n

The MySQL service should begin soon after the installation is complete. We can check the status if it is active or not by typing the following command:<\/p>\n

$ <\/span>sudo<\/span> systemctl status mysql<\/div><\/div>\n

Somehow, if it is not started, we can start it by typing the following command:<\/p>\n

$ <\/span>sudo<\/span> systemctl start mysql<\/div><\/div>\n

Changing the Bind-Address IP<\/strong><\/h2>\n

After completing the whole installation process, run this command:<\/p>\n

$ <\/span>sudo<\/span> nano<\/span> \/<\/span>etc\/<\/span>mysql\/<\/span>mysql.conf.d\/<\/span>mysqld.cnf<\/div><\/div>\n

Output:<\/strong><\/p>\n

[<\/span>mysqld]<\/span>
\n
\n#<\/span>
\n
\n# * Basic Settings<\/span>
\n
\n#<\/span>
\n
\nuser = mysql
\n
\n# pid-file = \/var\/run\/mysqld\/mysqld.pid<\/span>
\n
\n# socket = \/var\/run\/mysqld\/mysqld.sock<\/span>
\n
\n# port = 4405<\/span>
\n
\n# datadir = \/var\/lib\/mysql<\/span>
\n
\n**<\/span> many more<\/span> outputs **<\/span>
\n
\n# tmpdir = \/tmp<\/span>
\n
\n#<\/span>
\n
\nbind-address = 127.0.0.1
\n
\nmysqlx-bind-address = 127.0.0.1
\n
\n#<\/span><\/div><\/div>\n

The server only seeks for local connections if this value is set to 127.0.0.1. The previous directive needs to be changed to contain an external IP address. To help with fixing the problems, we may set this command to a global IP address such as *,::, or 0.0.0.0:<\/p>\n

Output:<\/strong><\/p>\n

[<\/span>mysqld]<\/span>
\n
\n#<\/span>
\n
\n# * Basic Settings<\/span>
\n
\n#<\/span>
\n
\nuser = mysql
\n
\n# pid-file = \/var\/run\/mysqld\/mysqld.pid<\/span>
\n
\n# socket = \/var\/run\/mysqld\/mysqld.sock<\/span>
\n
\n# port = 4405<\/span>
\n
\n# datadir = \/var\/lib\/mysql<\/span>
\n
\n**<\/span> \u2026\u2026.. many more<\/span> outputs \u2026\u2026\u2026**<\/span>
\n
\n. Ref https:\/\/<\/span>dev.mysql.com\/<\/span>doc\/<\/span>refman\/<\/span>8.0<\/span>\/<\/span>en\/<\/span>server-system-variables.html#sysvar_tmpdir<\/span>
\n
\n# tmpdir = \/tmp<\/span>
\n
\nbind-address = 0.0.0.0
\n
\nmysqlx-bind-address = 127.0.0.1
\n
\n#<\/span><\/div><\/div>\n

After that, just do the following steps:<\/p>\n

    \n
  • Save the Changes:<\/strong> To save the modifications that we made, press the following key combination: \u201cCTRL + X\u201d. This command tells Nano that we want to exit the editor.<\/li>\n
  • Confirm the Saved Changes:<\/strong> After pressing \u201cCTRL + X\u201d, Nano will ask, “Save modified buffer?” and there will be two options: \u201cY\u201d or \u201cN\u201d. We need to type \u201cY\u201d.<\/li>\n
  • Specify the File Name:<\/strong> After confirming the saved changes, Nano presents the option to specify the file name if we made changes to the original file. If we want to keep the same file name, simply press \u201cENTER\u201d on our keyboard.<\/li>\n
  • Exit Nano:<\/strong> Once we saved the changes, Nano will exit and we’ll return to the command line interface or terminal.<\/li>\n<\/ul>\n

    Conclusion<\/strong><\/h2>\n

    Remote MySQL connections are required in a variety of scenarios involving dispersed applications, remote development, and collaborative database management. By following the processes that are outlined in this extensive guide, we may securely modify MySQL server’s settings and enable the remote access which allows us to fully utilize MySQL’s capabilities while ensuring the security of our database.<\/p>","protected":false},"excerpt":{"rendered":"

    Guide on securely modifying MySQL server’s settings and enabling the remote access to fully utilize MySQL’s capabilities while ensuring the database\u2019s security.<\/p>","protected":false},"author":111,"featured_media":22480,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[168],"tags":[],"class_list":["post-22475","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting"],"_links":{"self":[{"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/posts\/22475","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\/111"}],"replies":[{"embeddable":true,"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/comments?post=22475"}],"version-history":[{"count":0,"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/posts\/22475\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/media\/22480"}],"wp:attachment":[{"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/media?parent=22475"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/categories?post=22475"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/linuxways.net\/de\/wp-json\/wp\/v2\/tags?post=22475"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}