Connecting to MariaDB

Post Reply
Steve
Posts: 36
Joined: Sun Oct 01, 2017 3:08 pm

Connecting to MariaDB

Post by Steve » Mon Oct 16, 2017 1:19 am

These instructions assume you have MariaDB installed and it is working. If MariaDB does not start the please check What to do if MariaDB doesn't start.

Additionally mysqli must be enabled in php extensions. To check the php configuration, create a file named phpinfo.php and insert the following :

Code: Select all

<?php
    phpinfo();
?>
To verify php is connecting to MariaDB, open your browser and enter http://your_domain_name/phpinfo.php. Scroll down to MYSQLI and verify it is enabled. The port and socket should contain the values for programs to access MariaDB.

You should also install the phpMyAdmin utility program. The phpMyAadmin program allows you to create and delete users and databases. It also runs queries and can change information in the database.

Connecting a program to MariaDB depends on the server settings. Since I use a Synology server the examples show the settings from the Synology DSM. However the connection information applies to nearly any server.

If you use the Synology version of WordPress you will not need to enter any additional information because Synology automates the WordPress installation. If you install WordPress from WordPress.org you will need to setup the database manually, even if you are using a Synology DSM. These instructions apply to the WordPress.org version or any other program that needs connections to MariaDB.

The minimum requirements to connect are entering the host, the database name, the database user name and the database password as shown below.
$dbhost = 'localhost';
$dbname = 'database_name';
$dbuser = 'user_name';
$dbpasswd = 'database_password';

If you are lucky the above entries may work as shown but in most cases the question is where and how do I enter the information so my program connects?

Before you connect to MariaDB, the database and user must be created manually because many programs can not create the user or database in MariaDB or MySQL. The easiest way to create users and databases is to use phpMyAdmin.

Login to phpMyAdmin using the username root and your MariaDB password.
When phpMyAdmin starts you will a screen like the one below.

Image

Select the User Account tab and the following screen appears. Then select the Add User Account option shown at the red arrow below and then select Go at the bottom right of the screen. Go must be pressed after you make changes in phpMyAdmin or the changes will not be accepted.

Image

On the user account page enter the user name, host name and password.
The user name is the name you enter in the program to access the database.
In this example the database name will be the same as the username.
Programs use a user name and a password to access the database.

Image

For the Host Name select local from the pull down menu.
Enter a password you want to use for your database and re-type to confirm.
This is the password your program will use to access the database. It IS NOT the MariaDB password. You can select Generate and phpMyAdmin will create a password as well. Make sure you copy and paste or write down the password somewhere safe. You will need it to connect to the database.

Check the box Create database with same name and grant all privileges.

Image

You do not need to grant the user global privileges however privileges can always be changed later if you need this user to access another database.

Note that enabling SSL may prevent your program from connecting to the database correctly.
When finished select the Go button at the lower right of the screen.

Once your user and database are created you need to enter the connection information in the program that uses MariaDB. There are a number of ways programs connect to MariaDB but most usually require the port and socket. However in some cases additional information may be required.

If you are using a Synology DSM, login to DSM and select the main menu at the top of the screen. Then select MariaDB. The screen will show the port and socket to connect. The port number and sock path may be different on your host or server.

Image

If you are not using a Synology server, you can use phpMyAdmin to find the connection information necessary to connect to your database. From the phpMyAdmin home screen select variables.

Image

There are many variables so scroll down to port and write down the numbers (DO NOT use commas).

Image

Scroll down further to Socket and write down the complete socket path info as shown.

Image

You may need to enter the port and socket information in one of three different ways in your program's configuration file. My genealogy program and phpBB works with option 1 but WordPress from WordPress.org works with option 2. Neither program connects with entries in the database port and socket fields.


OPTION -1
$database_host = 'localhost:3307';
$database_name = 'database_name';
$database_username = 'user_name';
$database_password = 'your_password';
$database_port = '';
$database_socket = '';

OPTION - 2
$database_host = 'localhost:/run/mysqld/mysqld10.sock';
$database_name = 'database_name';
$database_username = 'user_name';
$database_password = 'your_password';
$database_port = '';
$database_socket = '';

OPTION - 3
$database_host = 'localhost';
$database_name = 'database_name';
$database_username = 'user_name';
$database_password = 'your_password';
$database_port = '3307';
$database_socket = '/run/mysqld/mysqld10.sock';

One of the above options should work.

If these options do not work, check your spelling and make sure you are using the correct port and socket information. Make sure mysqli is enabled in your php extensions. If you suspect the MariaDB password you are using is incorrect, you can change it using phpMyAdmin. Last make sure your user has access to the database you created. To check the user, log in to phpMyAdmin and select User Accounts. Then locate the user and select edit privileges. Then select database.

Image

The database-specific privileges should say "All Privileges". (Note that Global privileges should be blank for this user) Select edit privileges again to check the rights for this database.

Image

Rights should be the same as those shown above.

You can also check Configuring MariaDB for Remote Client Access for additional information.



Last bumped by Steve on Mon Oct 16, 2017 1:19 am.

Post Reply