Connecting to MariaDB

[steven]

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.



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.



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.



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.



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 (AKA TSL) is not needed for local connections and will prevent your program from connecting to the database correctly. SSL is only needed for databases where you allow allow remote clients or clients on another network to connect directly to the database. This can only be used if you have an X509 certificate or a self-signed certificate, that is created with OpenSSL, and Click here for more information.
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. These instructions were written with DSM 5 installed.



After upgrading to DSM 7 the socket changed. However my programs were still using the original socket and working correctly. To avoid problems with future updates, I changed to socket from localhost:/run/mysqld/mysqld10.sock to localhost:/run/mysqld/mysqld.sock. Note I do not have MaridDB 5 installed so I'm not sure this applies if both MariaDB versions are installed.



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.



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



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



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 using option 3 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';
OR
$database_host = 'localhost:/run/mysqld/mysqld.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';
OR
$database_socket = '/run/mysqld/mysqld.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.



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.



Rights should be the same as those shown above.

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

Return to the top

[Chaandgus]

Hello !!

Thanks for your really nice explainations svp !! I am on Maria DB 10 on a NAS but can't access the database ).

My MAriaDB database :
**User:** root
**PAssowrd :** 1234*

2021-08-30_09h44_28.png (52.09 KiB) Viewed 80504 times

On PHPMyAdmin (wich I reach on **IPofmyNAS/phpMyAdmin/**
- Connexion PHPMyAdmin
**User:** root
**PAssword :** 1234*

2021-08-30_09h44_28.png (52.09 KiB) Viewed 80504 times

Database creation : utilisateur
**User cration** : utilisateur
**password:** 12345*


I did that :


What is advise on terms of accounts, privileges, passwords, may I
- put the same passowrd on PHPMyAdmin and MariaDB please ?
- the security management of the database is it true in my case please ? (the IDs are for exemple in my post

MLy connexion.php page :
```

Code: Select all

<?

$sql_host = "localhost:/run/mysqld/mysqld10.sock"; //I also tried option1 and 3 in your post
$sql_user = "utilisateur";
$sql_pwd = "12345*";
$sql_db = "utilisateur";

if($id = mysql_connect($sql_host,$sql_user,$sql_pwd))//Si j'arrive à me connecter avec ses paramêtres
{ if($id_db = mysql_select_db($sql_db))//Puis à cette base de données
 { echo "Succès !";//Ça roule !
 }else{
 die("Echec");//Ou impossible de se connecter à la base 😞 (vous êtes connectez au serveur mais impossible //de sélectionner la base $sql_db)
 }
 
mysql_close($id);
}else{
die("Echec complet");//Ou encore pire ! L'échec complet, c'est que vous n'êtes même pas arriver à vous connecter !
}
?>

```
Could you please help me please ?

May I need to open the ports on my routeur or NAS please ?

A big thx

[steven]

What is advise on terms of accounts, privileges, passwords, may I
- put the same passowrd on PHPMyAdmin and MariaDB please ?
- the security management of the database is it true in my case please ?

Regarding privleges, phpMyAdmin needs ALL permissions as it manages the databases and can edit any database if needed. The remaining users should only have permission for the database(s) they access. Typical permissions are shown below.

The MariaDB password is used by phpMyAdmin to connect to MariaDB. If they are different phpMyAdmin will not be able to connect. MariaDB is a database server and phphMyAdmin is the program used to administer MariaDB. MariaDB may contain many databases for programs like phpBB, Joomla and WordPress. Each program will use a different user name and password to connect and should NEVER be the same as the MariaDB user name and password.

You do not need to use SSL for security unless you allow remote client access. Users accessing your website use a program installed on the NAS to access the database so this is not remote client database access. However, if users are using a local program on another network and remotely accessing your database, you should enable SSL but that gets more complicated and requires a separate certificate.

MLy connexion.php page :
localhost:/run/mysqld/mysqld10.sock

I just updated the post. If you are running DSM 7 you may need to use a different socket, if a socket is required for a program, such as WordPress. In my case the NAS was updated from DSM 6 to DSM 7 so localhost:/run/mysqld/mysqld10.sock still worked although the MariaDB socket was really localhost:/run/mysqld/mysqld.sock. It appears Synology left some redirects so users would not have to change the connection information. I manually edited the WordPress config file and changed it to localhost:/run/mysqld/mysqld.sock and it continued to work without issue.

May I need to open the ports on my routeur or NAS please ?

DO NOT forward ports on your router and NAS that expose MariaDB to the WAN. This is not required unless you want to allow remote clients or clients on another network to connect to the database. Users accessing you website do not need a remote connection to the database.

The biggest issue most users have is getting the login name and password correct. If they do not match exactly you will not connect. Keep in mind they are case sensitive.

[Chaandgus]

Many thanks to have took time for me...furthermore my english sould'nt be nice
Yes my user have All privileges ans password are the same ;o)

@ the moment, the connexion looks to the database looks to works with the try.php :

Code: Select all

<?php
$serveur = '127.0.0.1:3307'; //par exemple 127.0.0.1 ou 127.0.0.1:3307 pour se connecter via le port 3307
$utilisateur = 'utilisateur'; //utilisateur qui a accès
$motdepasse = '12345*'; //mot de passe de l'utilisateur qui va bien
$base = 'utilisateur'; //surtout pas oublier la base
 
$lien_SGBD = mysqli_connect($serveur, $utilisateur, $motdepasse, $base);
 
if (!$lien_SGBD) {
    die('Erreur de connexion (' . mysqli_connect_errno() . ') '
            . mysqli_connect_error());
}
 
echo 'Succès... ' . mysqli_get_host_info($lien_SGBD) . "\n";
 
mysqli_close($lien_SGBD);
?>

That give "Succès... 127.0.0.1:3307 via TCP/IP" in my explorer

But, I can't write on with that code :

connexiondb.php

Code: Select all

<?php
  	// Déclaration d'une nouvelle classe
  	class connexionDB {
		private $host = '127.0.0.1:3307'; //nom de l'host
		private $name= 'utilisateur'; //nom de la base de données
		private $user = 'utilisateur';  //utilisateur
		private $pass = '12345*'; 
		private $connexion;
	function __construct($host = null, $name = null, $user = null, $pass = null){
			if($host != null){
		        $this->host = $host;           
		        $this->name = $name;           
		        $this->user = $user;          
		        $this->pass = $pass;
			}
			try{
		    	$this->connexion = new PDO('mysql:host=' . $this->host . ';dbname=' . $this->name,
					$this->user, $this->pass, array(PDO::MYSQL_ATTR_INIT_COMMAND =>'SET NAMES UTF8MB4', 
					PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING));
		  	}catch (PDOException $e){
		        echo 'Erreur : Impossible de se connecter  à la BDD !';
				die();
			}
		}
		
		public function connexion(){
			return $this->connexion;
		}
  	}
  	
  	$DB = new connexionDB;
  	
  	$BDD = $DB->connexion();
 
?>

I also tried that one from https://www.php.net/manual/fr/mysqli.qu ... ctions.php :

Code: Select all

 
<?php
/*
 * Description of connexionDB
 *  Connexion à la base de donnée avec des fonctions des requêtes;
 * @author Sitedudev
 */
 
    class connexionDB {
        private $host = '127.0.0.1:3307'; //nom de l'host
        private $name= 'utilisateur'; //nom de la base de données
        private $user = 'utilisateur';  //utilisateur
        private $pass = '12345*';
        private $connexion;
    
        function __construct($host = null, $name = null, $user = null, $pass = null){
            if($host != null){
                $this->host = $host;
                $this->name = $name;
                $this->user = $user;
                $this->pass = $pass;
            }
            
            try{$this->connexion = new mysqli($host, $user, $pass, $name);
/*Pas sur que la ligne ci-dessous soit correcte*/
            if ($this->connect_errno) {
                echo "Échec lors de la connexion à MySQL : (" . $this->connect_errno . ") " . $this->connexion->connect_error;
                }
                echo $this>host_info . "\n";
 
                $this->connexion = new mysqli($host, $user, $pass, $name, 3307);
                if ($this->connect_errno) {
                echo "Échec lors de la connexion à MySQL : (" . $this->connect_errno . ") " . $this->connect_error;
                }
 
                echo $mysqli->host_info . "\n";
                }
        }
                public function connexion(){
                    return $this->connexion;
                }
                }
                
    $BDD = new connexionDB();
    $DB = $BDD->connexion();  

?>

and inscription.php :

Code: Select all

<?php
	session_start();
	include_once('db/connexiondb.php');
if(isset($_SESSION['id'])){
		header('Location: /');
		exit;
	}

	if(!empty($_POST)){
		extract($_POST);
		$valid = (boolean) true;
		
		if(isset($_POST['inscription'])){
			$nom = (String) trim($nom);
			$prenom = (String) trim($prenom);
			$mail = (String) strtolower(trim($mail));
			$password = (String) trim($password);
			$jour = (int) $jour;
			$mois = (int) $mois;
			$annee = (int) $annee;
			$departement = (String) trim($departement);

			
			if(empty($pseudo)){
				$valid = false;
				$err_pseudo = "Veuillez renseigner ce champs !";
			}else{
				$req = $BDD->prepare("SELECT id
					FROM sportives 
					WHERE pseudo = ?");
					
				$req->execute(array($pseudo));
				$sportives = $req->fetch();
				
				if(isset($sportives['id'])){
					$valid = false;
					$err_pseudo = "Ce pseudo existe déjà";
				}
			}
			
			if(empty($mail)){
				$valid = false;
				$err_mail = "Veuillez renseigner ce champs !";
			}else{
				$req = $BDD->prepare("SELECT id
					FROM sportives 
					WHERE mail = ?");
					
				$req->execute(array($mail));
				$sportives = $req->fetch();
				
				if(isset($sportives['id'])){
					$valid = false;
					$err_mail = "Ce mail existe déjà";
				}
			}
			
			if(empty($password)){
				$valid = false;
				$err_password = "Veuillez renseigner ce champs !";
			}
						
			if($jour <= 0 || $jour > 31){
				$valid = false;
				$err_jour = "Veuillez renseigner ce champs jour !";
			}
			
			$verif_mois = array(1, 2, 3);
						
			if(!in_array($mois, $verif_mois)){
				$valid = false;
				$err_mois = "Veuillez renseigner ce champs mois !";
			}
			
			$verif_annee = array(1990, 2000, 3);
						
			if(!in_array($annee, $verif_annee)){
				$valid = false;
				$err_annee = "Veuillez renseigner ce champs annee !";
			}
			
			if(!checkdate($mois, $jour, $annee)){
				$valid = false;
				$err_date = "Date fausse";
			}else{
				$date_naissance = $annee . '-' . $mois . '-' . $jour;
			}
			
			
			$req = $BDD->prepare("SELECT departement_id
				FROM departement
				WHERE departement_code = ?");
			$req->execute(array($departement));
			
			$verif_departement = $req->fetch();
						
			if(!isset($verif_departement['departement_id'])){
				$valid = false;
				$err_departement = "Veuillez renseigner ce champs !";
			}
			
			if($valid){
				$date_inscription = date("Y-m-d h:m:s");
				
				$password = crypt($password, '$6$rounds=5000$wzxMHkmn72fgdfgdfgdfgdrttjhgndtzsfghgfjhgnvcbcgfdgtfdgfdhvcnbvhbfhgf$');
				
				$req = $BDD->prepare("INSERT INTO sportives ($prenom, $nom, mail, password, date_naissance, departement, date_inscription, date_connexion) 
					VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
					
				$req->execute(array($prenom, $nom, $mail, $password, $date_naissance, $departement, $date_inscription, $date_inscription));
				
				header('Location: /');
				exit;
			}
		}
	}
	
?>
<!doctype html>
<html lang="fr">
	<head>
		<!-- Required meta tags -->
		<meta charset="utf-8">
		<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
		
		<!-- Bootstrap CSS -->
		<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
		<link rel="stylesheet" href="dist/css/style.css">

		<title>Connexion</title>
	</head>
	<body>
		<?php
			require_once('menu.php');	
		?>
		
		<h1>Inscription</h1>
		
		<form method="post">
			<section>
				<div>
					<?php
						if(isset($err_prenom)){
							echo $err_prenom;
						}	
					?>
					<input type="text" name="prenom" placeholder="Prenom" value="<?php if(isset($prenom)){ echo $prenom;} ?>">
					<?php
						if(isset($err_nom)){
							echo $err_nom;
						}	
					?>
					<input type="text" name="nom" placeholder="Nom" value="<?php if(isset($nom)){ echo $nom;} ?>">
				</div>				
				<div>
					<?php
						if(isset($err_mail)){
							echo $err_mail;
						}	
					?>
					<input type="text" name="mail" placeholder="Mail" value="<?php if(isset($mail)){ echo $mail;} ?>">
				</div>
				<div>
					<?php
						if(isset($err_password)){
							echo $err_password;
						}	
					?>
					<input type="password" name="password" placeholder="Mot de passe" value="<?php if(isset($password)){ echo $password;} ?>">
				</div>
				<div>
					<?php
						if(isset($err_jour)){
							echo $err_jour;
						}	
						if(isset($err_mois)){
							echo $err_mois;
						}	
						if(isset($err_annee)){
							echo $err_annee;
						}	
						if(isset($err_date)){
							echo $err_date;
						}	
					?>
					<select name="jour">
						<?php
							for($i = 1; $i <= 31; $i++){
						?>
						<option value="<?= $i ?>"><?= $i ?></option>
						<?php
							}
						?>
					</select>
					<select name="mois">
						<option value="1">Janvier</option>
						<option value="2">Février</option>
						<option value="3">Mars</option>
						<option value="4">Avril</option>
						<option value="5">Mai</option>
						<option value="6">Juin</option>
						<option value="7">Juillet</option>
						<option value="8">Août</option>
						<option value="9">Septembre</option>
						<option value="10">Octobre</option>
						<option value="11">Novembre</option>
						<option value="12">Décembre</option>
					</select>
					<select name="annee">
						<?php
							for($i = 1940; $i <= 2020; $i++){
						?>
						<option value="<?= $i ?>"><?= $i ?></option>
						<?php
							}
						?>
					</select>
				</div>
				<div>
					<select name="departement">
						<option value="1">Ain</option>
						<option value="2">Aisneption>
						<option value="4">Allier</option>
					</select>
				</div>
			</section>
			
			<input type="submit" name="inscription" value="S'inscrire">
		</form>
		
		<!-- Optional JavaScript -->
		<!-- jQuery first, then Popper.js, then Bootstrap JS -->
		<script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rTwzxMHkmn72fgdfgdfgdfgdrttjhgndtzsfghgfjhgnvcbcgfdgtfdgfdhvcnbvhbfhgfizo" crossorigin="anonymous"></script>
		<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js" integrity="sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1" crossorigin="anonymous"></script>
		<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM" crossorigin="anonymous"></script>
	</body>
</html>

If you have a nice idea, it could be very fare. Cheers fot that and again : thanks Steve

[steven]

Looking at your code it appears you writing a program. If that's the case I may not be able to devote enough time or answer all your questions.

That said, connecting to MariaDB is pretty straight forward. Try this website with some nice tutorials for connecting to the MariaDB database. This article has additional information on variables and sessions.

Here are some additional resources about session information and this one from W3schools.

If you cannot connect using the example above, check your user account and database to make sure it is set to localhost. You should also use localhost with the port number like localhost:3306 or localhost:3307 for the connection, if you are not explicitly specifying the port and socket in the connection code. Note the port number depends on which port is used on your NAS.

Good Luck!

But, I can't write on with that code :

The database must contain something to read from and write to. Did you manually create the necessary tables for your program or do you plan to use PHP to create the tables?

[Chaandgus]

Hie Steve ! Many thanks for the links... I'll look for a solution with that !
The first links doesn't works, if you can send it again it should be really fair.
Best regards,
Augustin

[steven]

I checked and the link and it works for me but here is the actual link:

Code: Select all

https://www.tutorialspoint.com/mariadb/mariadb_connection.htm

If you can't get to that web site here is a simple connection code posted there:

Code: Select all

<html>
   <head>
      <title>Connect to MariaDB Server</title>
   </head>

   <body>
      <?php
         $dbhost = 'localhost:3036';
         $dbuser = 'guest1';
         $dbpass = 'guest1a';
         $conn = mysql_connect($dbhost, $dbuser, $dbpass);
      
         if(! $conn ) {
            die('Could not connect: ' . mysql_error());
         }
         
         echo 'Connected successfully';
         mysql_close($conn);
      ?>
   </body>
</html>

[Chaandgus]

You are great !

Fist if I detele "private" before that :

Code: Select all

private $host = 'localhost'; //nom de l'host
private $name= 'database'; //nom de la base de données
private $user = 'root';  //utilisateur
private $pass = 'password';

With the code below I successfully be connected :

Code: Select all

<html>
   <head>
      <title>Connect to MariaDB Server</title>
   </head>

   <body>
      <?php
         $host = 'localhost'; //nom de l'host
         $name= 'database'; //nom de la base de données
         $user = 'root';  //utilisateur
         $pass = 'password';

         $conn = mysqli_connect($host, $user, $pass);
      
         if(! $conn ) {
            die('Could not connect: ' . mysql_error());
         }
         
         echo 'Connected successfully';
         mysql_close($conn);
      ?>
   </body>
</html>

Then, when I put that parts :

Code: Select all

$host = 'localhost'; //nom de l'host
$name= 'database'; //nom de la base de données
$user = 'root';  //utilisateur
$pass = 'password';

in another file config.inc.php in the file "db" that I call with include 'db/config.inc.php'; it doesn't works. If you have another issue Steve it should be great. I understand you can't spend all your time for me ;o)

[steven]

Your program should NOT use the user "root" to connect to any database. Root is the MariaDB and therefore the phpMyAdmin user. When you create a database, it is easier if you first create a user and then let phpMyadmin create a database with the same name and assign privileges.

If you create a program that accesses the database with the user root, and you get hacked, every database in MariaDB will be exposed. Since "root" is the admin user for MariaDB, it has all privileges by default.

Keep in mind the example code opens a connection to a database then closes the connection with the mysql_close($conn); statement. You must keep the connection open to do reads and writes then close the connection when finished.

[Chaandgus]

You are great ! It works !! Thanks a lot