Finding the Defaults File
To enable MariaDB to listen to remote connections, you need to edit your defaults file. See Configuring MariaDB with my.cnf for more detail.
Common locations for defaults files:
* /etc/my.cnf (*nix/BSD) * $MYSQL_HOME/my.cnf (*nix/BSD) *Most Notably /etc/mysql/my.cnf * SYSCONFDIR/my.cnf (*nix/BSD) * DATADIR\my.ini (Windows)
Editing the Defaults File
Once you have located the defaults file, use a text editor to open the file and try to find lines like this under the [mysqld] section:
[mysqld]
...
skip-networking
...
bind-address =
...
(The lines may not be in this order, and the order doesn't matter.)
If you are able to locate these lines, make sure they are both commented out (prefaced with hash (#) characters), so that they look like this:
[mysqld]
...
#skip-networking
...
#bind-address =
...
(Again, the order of these lines don't matter)
Save the file and restart the mysqld daemon or service (see Starting and Stopping MariaDB).
Granting User Connections From Remote Hosts
Now that your MariaDB server installation is setup to accept connections from remote hosts, we have to add a user that is allowed to connect from something other than 'localhost' (Users in MariaDB are defined as 'user'@'host', so 'chadmaynard'@'localhost' and 'chadmaynard'@'1.1.1.1' (or 'chadmaynard'@'server.domain.local') are different users that can have completely different permissions and/or passwords.
To create a new user:
- log into the mysql command line client (or your favorite graphical client if you wish)
Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 36 Server version: 5.5.28-MariaDB-mariadb1~lucid mariadb.org binary distribution Copyright (c) 2000, 2012, Oracle, Monty Program Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]>
- if you are interested in viewing any existing remote users, issue the following SQL statement on the mysql.usertable:
SELECT User, Host FROM mysql.user WHERE Host <> 'localhost'; +--------+-----------+ | User | Host | +--------+-----------+ | daniel | % | | root | 127.0.0.1 | | root | ::1 | | root | gandalf | +--------+-----------+ 4 rows in set (0.00 sec)
(If you have a fresh install, it is normal for no rows to be returned)
Now you have some decisions to make. At the heart of every grant statement you have these things:
- list of allowed privileges
- what database/tables these privileges apply to
- username
- host this user can connect from
- and optionally a password
It is common for people to want to create a "root" user that can connect from anywhere, so as an example, we'll do just that, but to improve on it we'll create a root user that can connect from anywhere on my local area network (LAN), which has addresses in the subnet 192.168.100.0/24. This is an improvement because opening a MariaDB server up to the Internet and granting access to all hosts is bad practice.
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.100.%' IDENTIFIED BY 'my-new-password' WITH GRANT OPTION;
(% is a wildcard)
For more information about how to use GRANT, please see the GRANT page.
At this point we have accomplished our goal and we have a user 'root' that can connect from anywhere on the 192.168.100.0/24 LAN.
Port 3306 is Configured in Firewall
One more point to consider whether the firwall is configured to allow incoming request from remote clients:
On RHEL and CentOS 7, it may be necessary to configure the firewall to allow TCP access to MySQL from remote hosts. To do so, execute both of these commands:
firewall-cmd --add-port=3306/tcp firewall-cmd --permanent --add-port=3306/tcp
