If you are completely new to MariaDB and relational databases, you may want to start with the MariaDB Primer. Also, make sure you understand the connection parameters discussed in the Connecting to MariaDB article.

There are a number of common problems that can occur when connecting to MariaDB.

Server Not Running in Specified Location

If the error you get is something like:

or

the server is either not running, or not running on the specified port, socket or pipe. Make sure you are using the correct host, port, pipe, socket and protocol options, or alternatively, see , or .

The socket file can be in a non-standard path. In this case, the socket option is probably written in the my.cnf file. Check that its value is identical in the [mysqld] and [client] sections; if not, the client will look for a socket in a wrong place.

If unsure where the Unix socket file is running, it's possible to find this out, for example:

Unable to Connect from a Remote Location

Usually, the MariaDB server does not by default accept connections from a remote client or connecting with tcp and a hostname and has to be configured to permit these.

To solve this, see

Authentication Problems

The is enabled by default on Unix-like systems. This uses operating system credentials when connecting to MariaDB via the local Unix socket file. See for instructions on connecting and on switching to password-based authentication as well as for an overview.

Authentication is granted to a particular username/host combination. user1'@'localhost', for example, is not the same as user1'@'166.78.144.191'. See the article for details on granting permissions.

Passwords are hashed with function. If you have set a password with the statement, the PASSWORD function must be used at the same time. For example, SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass') rather than just SET PASSWORD FOR 'bob'@'%.loc.gov' = 'newpass' .

Problems Exporting Query Results

If you can run regular queries but get an authentication error when running the , or statements, you do not have permission to write files to the server. This requires the FILE privilege. See the article.

Access to the Server, but not to a Database

If you can connect to the server, but not to a database, for example:

or can connect to a particular database, but not another, for examplemariadb -uname -p -u name db1 works but not mariadb -uname -p -u name db2, you have not been granted permission for the particular database. See the article.

Option Files and Environment Variables

It's possible that option files or environment variables may be providing incorrect connection parameters. Check the values provided in any option files read by the client you are using (see and the documentation for the particular client you're using - see ).

Option files can usually be suppressed with no-defaults option, for example:

Unable to Connect to a Running Server / Lost root Password

If you are unable to connect to a server, for example because you have lost the root password, you can start the server without using the privilege tables by running the option, which gives users full access to all tables. You can then run to resume using the grant tables, followed by to change the password for an account.

localhost and %

You may have created a user with something like:

This creates a user with the '%' wildcard host.

However, you may still be failing to login from localhost. Some setups create anonymous users, including localhost. So, the following records exist in the user table:

Since you are connecting from localhost, the anonymous credentials, rather than those for the 'melisa' user, are used. The solution is either to add a new user specific to localhost, or to remove the anonymous localhost user.

See Also

CC BY-SA / Gnu FDL

Some MariaDB packages bind MariaDB to 127.0.0.1 (the loopback IP address) by default as a security measure using the bind-address configuration directive. Old MySQL packages sometimes disabled TCP/IP networking altogether using the skip-networking directive. Before going in to how to configure these, let's explain what each of them actually does:

• skip-networking is fairly simple. It just tells MariaDB to run without any of the TCP/IP networking options.

• bind-address requires a little bit of background information. A given server usually has at least two networking interfaces (although this is not required) and can easily have more. The two most common are a Loopback network device and a physical Network Interface Card (NIC) which allows you to communicate with the network. MariaDB is bound to the loopback interface by default because it makes it impossible to connect to the TCP port on the server from a remote host (the bind-address must refer to a local IP address, or you will receive a fatal error and MariaDB will not start). This of course is not desirable if you want to use the TCP port from a remote host, so you must remove this bind-address directive or replace it either 0.0.0.0 to listen on all interfaces, or the address of a specific public interface.

Finding the Defaults File

To enable MariaDB to listen to remote connections, you need to edit your defaults file. See for more detail.

Common locations for defaults files:

You can see which defaults files are read and in which order by executing:

The last line shows which defaults files are read.

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:

The lines may not be in this particular order, but 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:

Again, the order of these lines don't matter.

Alternatively, just add the following lines at the end of your .my.cnf (notice that the file name starts with a dot) file in your home directory or alternative last in your /etc/my.cnf file.

This works as one can have any number of [mysqld] sections.

Save the file and restart the mariadbd daemon or service (see ).

You can check the options mariadbd is using by executing:

It doesn't matter if you have the original --bind-address left as the later --skip-bind-address will overwrite it.

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 different permissions and/or passwords.

To create a new user:

• Log into the (or your favorite graphical client if you wish):

• if you are interested in viewing any existing remote users, issue the following SQL statement on the table:

(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

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.

% is a wildcard.

For more information about how to use GRANT, please see the 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 firewall 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 MariaDB from remote hosts. To do so, execute both of these commands:

Caveats

• If your system is running a software firewall (or behind a hardware firewall or NAT) you must allow connections destined to TCP port that MariaDB runs on (by default and almost always 3306).

• To undo this change and not allow remote access anymore, simply remove the skip-bind-address line or uncomment the line in your defaults file. The end result should be that you should have in the output from ./sql/mariadbd --print-defaults the option --bind-address=127.0.0.1 and no --skip-bind-address.

The initial version of this article was copied, with permission, from on 2012-10-30.

_{This page is licensed: CC BY-SA / Gnu FDL}

This article covers connecting to MariaDB and the basic connection parameters. If you are completely new to MariaDB, take a look at first.

In order to connect to the MariaDB server, the client software must provide the correct connection parameters. The client software will most often be the , used for entering statements from the command line, but the same concepts apply to any client, such as a , a client to run backups such as , etc. The rest of this article assumes that the mariadb command line client is used.

If a connection parameter is not provided, it will revert to a default value.

For example, to connect to MariaDB using only default values with the mariadb client, enter the following from the command line:

In this case, the following defaults apply:

• The host name is localhost