All downloads for MariaDB are located in the section of the official MariaDB foundation website. Click the link to the version you would like, and a list of downloads for multiple operating systems, architectures, and installation file types is displayed.

Installing on LINUX/UNIX

If you have intimate knowledge of Linux/Unix systems, simply download source to build your install. Our recommended way of installing is to utilize distribution packages. MariaDB offers packages for the following Linux/Unix distributions −

• RedHat/CentOS/Fedora
• Debian/Ubuntu

The following distributions include a MariaDB package in their repositories −

• openSUSE
• Arch Linux
• Mageia
• Mint
• Slackware

Follow these steps to install in an Ubuntu environment −

Step 1 − Login as a root user.

Step 2 − Navigate to the directory containing the MariaDB package.

Step 3 − Import the GnuPG signing key with the following code −

sudo apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db

Step 4 − Add MariaDB to the sources.list file. Open the file, and add the following code −

sudo add-apt-repository ''deb http://ftp.osuosl.org/pub/mariadb/repo/5.5/ubuntuprecise main''

Step 5 − Refresh the system with the following −

sudo apt-get update

Step 6 − Install MariaDB with the following −

sudo apt-get install mariadb-server

Installing on Windows

After locating and downloading an automated install file (MSI), simply double click the file to start the installation. The installation wizard will walk you through every step of installation and any necessary settings.

Test the installation by starting it from the command prompt. Navigate to the location of the installation, typically in the directory, and type the following at the prompt −

mysqld.exe --console

If the installation is successful, you will see messages related to startup. If this does not appear, you may have permission issues. Ensure that your user account can access the application. Graphical clients are available for MariaDB administration in the Windows environment. If you find the command line uncomfortable or cumbersome, be sure to experiment with them.

Testing the Installation

Perform a few simple tasks to confirm the functioning and installation of MariaDB.

Use the Admin Utility to Get Server Status

View the server version with the mysqladmin binary.

[root@host]# mysqladmin --version

It should display the version, distribution, operating system, and architecture. If you do not see the output of that type, examine your installation for issues.

Execute Simple Commands with a Client

Bring up the command prompt for MariaDB. This should connect you to MariaDB and allow execution of commands. Enter a simple command as follows −

mysql> SHOW DATABASES;

Post- Installation

After successful installation of MariaDB, set a root password. A fresh install will have a blank password. Enter the following to set the new password −

mysqladmin -u root password "[enter your password here]";

Enter the following to connect to the server with your new credentials −

mysql -u root -p
Enter password:*******

Upgrading on Windows

If you already have MySQL installed on your Windows system, and want to upgrade to MariaDB; do not uninstall MySQL and install MariaDB. This will cause a conflict with the existing database. You must instead install MariaDB, and then use the upgrade wizard in the Windows installation file.

The options of your MySQL my.cnf file should work with MariaDB. However, MariaDB has many features, which are not found in MySQL.

Consider the following conflicts in your my.cnf file −

• MariaDB uses Aria storage engine by default for temporary files. If you have a lot of temporary files, modify key buffer size if you do not use MyISAM tables.

• If your applications connect/disconnect frequently, alter the thread cache size.

• If you use over 100 connections, use the thread pool.

Compatibility

MySQL and MariaDB are essentially identical. However, there are enough differences to create issues in upgradation. Review more of these key differences in the

One way to establish a connection with MariaDB consists of using the mysql binary at the command prompt.

MYSQL Binary

Review an example given below.

[root@host]# mysql -u root -p

Enter password:******

The code given above connects to MariaDB and provides a command prompt for executing SQL commands. After entering the code, a welcome message should appear indicating a successful connection, with the version number displayed.

Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 122323232
Server version: 5.5.40-MariaDB-log

Type ''help;'' or ''h'' for help. Type ''c'' to clear the current input statement.
mysql>

The example uses root access, but any user with privileges can of course access the MariaDB prompt and perform operations.

Disconnect from MariaDB through the exit command as follows −

mysql> exit

PHP Connection Script

Another way to connect to and disconnect from MariaDB consists of employing a PHP script. PHP provides the mysql_connect() function for opening a database connection. It uses five optional parameters, and returns a MariaDB link identifier after a successful connection, or a false on unsuccessful connection. It also provides the mysql_close() function for closing database connections, which uses a single parameter.

Syntax

Review the following PHP connection script syntax −

connection mysql_connect(server,user,passwd,new_link,client_flag);

The description of the parameters is given below −

Review the PHP disconnection script syntax given below −

bool mysql_close ( resource $link_identifier );

If you omit the resource, the most recent opened resource will close. It returns a value of true on a successful close, or false.

Try the following example code to connect with a MariaDB server −

<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>

On successful connection, you will see the following output −

mysql> Connected successfully

MariaDB partners well with a wide variety of programming languages and frameworks such as PHP, C#, JavaScript, Ruby on Rails, Django, and more. PHP remains the most popular of all available languages due to its simplicity and historical footprint. This guide will focus on PHP partnered with MariaDB.

PHP provides a selection of functions for working with the MySQL database. These functions perform tasks like accessing it or performing operations, and they are fully compatible with MariaDB. Simply call these functions as you would call any other PHP function.

The PHP functions you will use for MariaDB conform to the following format −

mysql_function(value,value,...);

The second part of the function specifies its action. Two of the functions used in this guide are as follows −

mysqli_connect($connect);
mysqli_query($connect,"SQL statement");

The following example demonstrates the general syntax of a PHP call to a MariaDB function −

<html>
   <head>
      <title>PHP and MariaDB</title>
   </head>

   <body>
      <?php
         $retval = mysql_function(value, [value,...]);

         if( !$retval ) {
            die ( "Error: Error message here" );
         }
         // MariaDB or PHP Statements
      ?>
   </body>
</html>

In the next section, we will examine essential MariaDB tasks, using PHP functions.

Good field definitions are essential for the optimization of your database. The ideal approach requires that you exclusively use a field of the type and size needed. For example, if you will only use a field, five-characters wide, do not define a field, 20-characters wide. Field (or column) types are also known as data types given the data types stored within the field.

MariaDB data types can be categorized as numeric, date and time, and string values.

Numeric Data Types

The numeric data types supported by MariaDB are as follows −

• TINYINT − This data type represents small integers falling within the signed range of -128 to 127, and the unsigned range of 0 to 255.

• BOOLEAN − This data type associates a value 0 with “false,” and a value 1 with “true.”

• SMALLINT − This data type represents integers within the signed range of -32768 to 32768, and the unsigned range of 0 to 65535.

• MEDIUMINT − This data type represents integers in the signed range of -8388608 to 8388607, and the unsigned range of 0 to 16777215.

• INT(also INTEGER) − This data type represents an integer of normal size. When marked as unsigned, the range spans 0 to 4294967295. When signed (the default setting), the range spans -2147483648 to 2147483647. When a column is set to ZEROFILL( an unsigned state), all its values are prepended by zeros to place M digits in the INT value.

• BIGINT − This data type represents integers within the signed range of 9223372036854775808 to 9223372036854775807, and the unsigned range of 0 to 18446744073709551615.

• DECIMAL( also DEC, NUMERIC, FIXED)− This data type represents precise fixed-point numbers, with M specifying its digits and D specifying the digits after the decimal. The M value does not add “-” or the decimal point. If D is set to 0, no decimal or fraction part appears and the value will be rounded to the nearest DECIMAL on INSERT. The maximum permitted digits is 65, and the maximum for decimals is 30. Default value for M on omission is 10, and 0 for D on omission.

• FLOAT − This data type represents a small, floating-point number of the value 0 or a number within the following ranges −

  • -3.402823466E+38 to -1.175494351E-38

  • 1.175494351E-38 to 3.402823466E+38

• DOUBLE (also REAL and DOUBLE PRECISION) − This data type represents normal-size, floating-point numbers of the value 0 or within the following ranges −

  • -1.7976931348623157E+308 to -2.2250738585072014E-308

  • 2.2250738585072014E-308 to 1.7976931348623157E+308

• BIT − This data type represents bit fields with M specifying the number of bits per value. On omission of M, the default is 1. Bit values can be applied with “ b’[value]’” in which value represents bit value in 0s and 1s. Zero-padding occurs automatically from the left for full length; for example, “10” becomes “0010.”

Date and Time Data Types

The date and time data types supported by MariaDB are as follows −

• DATE − This data type represents a date range of “1000-01-01” to “9999-12-31,” and uses the “YYYY-MM-DD” date format.

• TIME − This data type represents a time range of “-838:59:59.999999” to “838:59:59.999999.”

• DATETIME − This data type represents the range “1000-01-01 00:00:00.000000” to “9999-12-31 23:59:59.999999.” It uses the “YYYY-MM-DD HH:MM:SS” format.

• TIMESTAMP − This data type represents a timestamp of the “YYYY-MM-DD HH:MM:DD” format. It mainly finds use in detailing the time of database modifications, e.g., insertion or update.

• YEAR − This data type represents a year in 4-digit format. The four-digit format allows values in the range of 1901 to 2155, and 0000.

String DataTypes

The string type values supported by MariaDB are as follows −

• String literals − This data type represents character sequences enclosed by quotes.

• CHAR − This data type represents a right-padded, fixed-length string containing spaces of specified length. M represents column length of characters in a range of 0 to 255, its default value is 1.

• VARCHAR − This data type represents a variable-length string, with an M range (maximum column length) of 0 to 65535.

• BINARY − This data type represents binary byte strings, with M as the column length in bytes.

• VARBINARY − This data type represents binary byte strings of variable length, with M as column length.

• TINYBLOB − This data type represents a blob column with a maximum length of 255 (28 – 1) bytes. In storage, each uses a one-byte length prefix indicating the byte quantity in the value.

• BLOB − This data type represents a blob column with a maximum length of 65,535 (216 – 1) bytes. In storage, each uses a two-byte length prefix indicating the byte quantity in the value.

• MEDIUMBLOB − This data type represents a blob column with a maximum length of 16,777,215 (2²⁴ – 1) bytes. In storage, each uses a three-byte length prefix indicating the byte quantity in the value.

• LONGBLOB − This data type represents a blob column with a maximum length of 4,294,967,295(2³² – 1) bytes. In storage, each uses a four-byte length prefix indicating the byte quantity in the value.

• TINYTEXT − This data type represents a text column with a maximum length of 255 (2⁸ – 1) characters. In storage, each uses a one-byte length prefix indicating the byte quantity in the value.

• TEXT − This data type represents a text column with a maximum length of 65,535 (2¹⁶ – 1) characters. In storage, each uses a two-byte length prefix indicating the byte quantity in the value.

• MEDIUMTEXT − This data type represents a text column with a maximum length of 16,777,215 (2²⁴ – 1) characters. In storage, each uses a three-byte length prefix indicating the byte quantity in the value.

• LONGTEXT − This data type represents a text column with a maximum length of 4,294,967,295 or 4GB (2³² – 1) characters. In storage, each uses a four-byte length prefix indicating the byte quantity in the value.

• ENUM − This data type represents a string object having only a single value from a list.

• SET − This data type represents a string object having zero or more values from a list, with a maximum of 64 members. SET values present internally as integer values.