How to Connect to MariaDb

Connect to a Cloud MariaDB

All cloud providers, like AWS, Oracle or Google allows to get the JDBC URL in the cloud console. In DbSchema Connection Dialog choose 'Manually Edit the JDBC URL' and copy the URL.

Connect to a MariaDB Server

The Connection Dialog fields are explained on a dedicated page.

If you got problems connecting to MariaDb check the items below.

• Introduction
• Start MariaDb Service
• Missing Privileges in MariaDb & Reset 'root' User Password
• Enable Remote Connections
• Allow MariaDb through Windows Firewall
• What to Check on the Common Error Messages
• Guided MariaDb Installation on Windows

Introduction

MariaDb users are a combination of username and host name which are allowed to connect ( can be '%' for any host). For example you can create an user in the database which may connect only from 'localhost':

CREATE USER 'sample'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'sample'@'localhost';
FLUSH PRIVILEGES;

If you try to connect from another host or from outside a docker container, you may receive the error 'Host ... is not allowed to connect'. In this case you better create the user with the right to connect from any host ('%'), or use the host which is shown in the error message.

CREATE USER 'sample'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'sample'@'%';
FLUSH PRIVILEGES;

Connecting from DbSchema you should use the user 'sample'. By default MariaDb already has an user 'root'@'localhost', with a password you should have already set during installation. This user will work only if you run DbSchema on the same machine where MySql is running ( if you use docker containers it is a virtual separate machine ).

If this is not the case, you should create an user with a the a host which is matching your host. Also check the chapter which is explaining how to enable remote connections ( disable bind_address in my.ini or my.cnf ).

Enable MariaDb Service

If MariaDb service is not running, you won't be able to connect.

Windows
To enable MariaDb Service follow this steps:

1. Go to Start -> Control Panel -> System and Security -> Administrative Tools -> Component Services
   
   
2. Open Service Local
3. Find your MariaDb service name setup during installation (For Example: MariaDb56)
4. Right click on the service name and click start.
   
   

Linux
To start the MariaDb service on linux just type in the console: service mysqld start

Reset Password or Missing Privileges to Login

If you forgot the database user password you can follow this steps and reset it :

Windows

1. Shut down MySql or MariaDb service: Go to Start -> run -> services.msc -> MySQL -> right click and select STOP
2. Create a text file reset.txt. Im my case I save it under "C:\Temp\reset.txt". In this file paste the following lines:

   UPDATE mysql.user SET Password=PASSWORD('EnterPassword') WHERE User='root';
   FLUSH PRIVILEGES;
   

   Replace "EnterPassword" with your new password. Also make sure that the UPDATE and FLUSH commands are on separate lines!
   
   
3. Open the command prompt and navigate to your MySQL or MariaDb bin directory inside the program folder ("C:\Program Files\MySQL\MySQL Server 5.0\bin" or "C:\Program Files\MariaDb\bin")
   enter the following command

   mysqld.exe --init-file=C:\Temp\reset.txt --console
   

   This will replace your root password with the one you have set in the text file. The command may hang as the daemon continues to run. You can stop it ( Ctrl-C ) and start MySql again from Windows Services.
4. Restart MySQL Service: Start -> run -> services.msc -> MySQL > right click and select START
   

Linux

1. Log in as root and stop MariaDb service(daemon) by entering the command: service mysql stop
2. Start MariaDb service(daemon) and skip the grant tables which store the passwords by entering the command: mysqld_safe --skip-grant-tables
3. Now you should be able to connect to MariaDb without a password.Run the following commands:

   mysql --user=root mysql
   

   update user set Password=PASSWORD('new-password') where user='root';
   flush privileges;
   exit
   

4. Log in as root and start the MariaDb daemon by entering the command: service mysqld start

Enable Remote Connections and Grant Privileges

If the MariaDb connection is only working locally, make sure the user has been granted privileges to be able to connection remotely to MariaDb.
Follow the next steps to grant the user remote access or privileges:

Windows
The easiest way to do this is during installation. Read Guided MariaDb Windows Installation to learn now.

1. Open the command prompt by following this steps: Start -> run -> cmd -> press enter.
2. Navigate to your MariaDb installation folder (Default: C:\Program Files\MariaDb\MariaDb Server 12\bin)
3. Type in: mysql -u root -p
4. GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'IP' IDENTIFIED BY 'PASSWORD';
   USERNAME is the account you wish to create or use.
   IP is the physical IP address of the computer you wish to grant remote access to.
   If you enter % instead of an IP number, that user will be able to remote access the MariaDb server from any computer.
   PASSWORD is the password you wish to create or modify.
5. Run this last command: FLUSH PRIVILEGES;
6. To exit type: quit

Linux

1. Login to the MariaDb server and edit the file /etc/my.cnf
   Add or edit the row bind-address=YOUR_SERVER_IP
   Comment out the row #skip-networking
2. Restart the server using '/etc/init.d/mariadb restart'
3. Login on the server using 'mariadb -u root -p mariadb' and execute the statements below replacing the user, ip and password :
   • For a new database:
     CREATE DATABASE foo;
     GRANT ALL ON foo.* TO bar@'202.54.10.20' IDENTIFIED BY 'PASSWORD';
   • For a existing database:
     UPDATE DB SET Host='202.54.10.20' where Db='webdb';
     UPDATE USER SET Host='202.54.10.20' WHERE user='webadmin';

Enable Connections to Database through Windows Firewall

The common reason for connectivity problems is the error 'Communications link failure'. This is because during the MariaDb installation the checkbox to add an firewall exception was not enabled. To enable it later do as follow :

Open Windows Firewall by clicking the Start button Picture of the Start button, and then clicking Control Panel. In the search box, type firewall, and then click Windows Firewall.
In the left pane, click Advanced settings. Administrator permission required If you're prompted for an administrator password or confirmation, type the password or provide confirmation.
In the Windows Firewall with Advanced Security dialog box, in the left pane, click Inbound Rules, and then, in the right pane, click New Rule.
Follow the instructions in the New Inbound Rule wizard. Make sure to select Add Port.
For the Port name use MariaDb.
For the specific local port use 3306
Make sure to check off Allow the Connection
For When does this rule apply?
Put a check in Domain, private, and public.
Make sure to follow the directions above for creating an Outbound rule...basically the same way except you click Outbound Rules on the left pane.

SSL Connections

If you wish to use ssl encryption, append this '?useSSL=true&verifyServerCertificate=false' to the URL Pattern in the JDBC Driver Manager.

Common Error Messages

Exception: Communications link failure. Exception: Communications link failure. The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

1. Check if the MariaDb Service on Server is Started
   
   
2. Check the MariaDb server port and hostname
   Test the connection using the PING button from the Connection Dialog.
   The default MariaDb port is 3306. However, this port can be changed for each MariaDb instance.
   Check the server files my.ini or my.cnf, in the mysqld section, to see the configured port, for example, port=3306.
   Find out the computer name right clicking My Computer and selecting Properties
   
   
3. Disable Firewalls on Server and Client
   Firewalls on client or server may block the connections. Configure them to accept incoming connections for MariaDb port or disable them.
   
   
4. Check the JDBC Driver Version
   DbSchema includes one JDBC driver for MariaDb. If the driver is not an actual version,
   search the internet for 'download MariaDb driver' or look on http://dev.mariadb.com/downloads/connector/j/
   You will probably download a file MariaDb-connector-java-x.x.x.zip.
   Decompress it and look for a file MariaDb-connector-java-x.x.x.jar. Upload this file in the in DbSchema JDBC Driver Manager.
   
   

• Exception: Test Connection Failed java.sql.SQLException: null, message from server: "Host 'YOUR_IP' is not allowed to connect to this MariaDb server"
Your ip is not allowed to connect to the MariaDb server.
• If the bind_address values is set to 127.0.0.1 in the MariaDb configuration file, the MariaDb server will not accept connections from remote hosts.
  The bind_address line in the MariaDb configuration file (my.ini or my.cnf) can either be removed or commented out to allow remote connections.

Guided MariaDb Installation on Windows

Download MariaDb

During the installation you have to enter the password for the database user root. Also you may want to enable access to MariaDb from remote computers

MariaDb Statements

• Create Database

      mysql -U root -p
      use <schema>;
      show tables;
      create database test;
      drop database test;
              

• Change Table Engine to Myisam

  ALTER TABLE schema.table ENGINE=Myisam;