Bitnami MariaDB Virtual Machine

Description

MariaDB is an open source, community-developed SQL database server that is widely in use around the world due to its enterprise features, flexibility, and collaboration with leading tech firms.

First steps with the Bitnami MariaDB Stack

Welcome to your new Bitnami application! This guide includes some basic information you will need to get started with your application.

How to import a Bitnami Virtual Machine?

Check the following instructions to import a Bitnami Virtual Machine:

Importing a Bitnami Virtual Machine in VirtualBox
  • Select the "File -> Import Appliance" menu option and select the .ova file downloaded from the Bitnami website. Then click "Continue".
  • Once it is imported, click the "Start" button in the VirtualBox toolbar.

For a detailed walkthrough, check our Virtualbox tutorial.

Importing a Bitnami Virtual Machine in VMware
  • Select the "File -> Import" menu option and select the .ova file downloaded from the Bitnami website. Then click "Continue".
  • Once the import is complete, click "Finish" to start the virtual machine.

For a detailed walkthrough, check our VMware tutorial.

What credentials do I need?

You need two sets of credentials:

  • The application password. This will allow you to log in to the services of the Bitnami stack.

  • The server credentials, consisting of an SSH username and password. These credentials allow you to log in to your Virtual Machines server using an SSH client and execute commands on the server using the command line.

What is the administrator password?

Password: The administrator password to log in to your application is randomly generated during the first boot. Check the FAQ to learn how to retrieve it.

What SSH username should I use for secure shell access to my application?

SSH username: bitnami

How do I get my SSH key or password?

You can obtain the SSH password from the virtual machine console when it starts up. Click here for more information.

How to access your application?

Once you have imported your Bitnami Virtual Machine, the IP address for your application is displayed on the virtual machine's login screen. Access the application via your browser by entering this IP address.

Check these instructions about how to remotely access the Bitnami application.

How to start or stop the services?

Each Bitnami stack includes a control script that lets you easily stop, start and restart services. The script is located at /opt/bitnami/ctlscript.sh. Call it without any service name arguments to start all services:

$ sudo /opt/bitnami/ctlscript.sh start

Or use it to restart a single service, such as Apache only, by passing the service name as argument:

$ sudo /opt/bitnami/ctlscript.sh restart apache

Use this script to stop all services:

$ sudo /opt/bitnami/ctlscript.sh stop

Restart the services by running the script without any arguments:

$ sudo /opt/bitnami/ctlscript.sh restart

Obtain a list of available services and operations by running the script without any arguments:

$ sudo /opt/bitnami/ctlscript.sh

What is the default configuration?

The grant tables define the initial MariaDB user accounts and their access privileges. The default configuration consists of:

  • A privileged account with a username of root. The root user has remote access to the database.
  • An anonymous user without remote access to the database server. This user can only connect from the local machine and it is only intended for testing.
  • A test database only intended for testing.

Check our recommendations for a production server.

MariaDB version

In order to see which MariaDB version are your machine running you can execute the following command:

$ mysqld --version

MariaDB configuration file

The MariaDB configuration file is located at /opt/bitnami/mariadb/my.cnf.

The MariaDB official documentation has more details about how to configure the MariaDB database.

MariaDB socket

On Unix, MariaDB clients can connect to the server in the local machine using an Unix socket file at /opt/bitnami/mariadb/tmp/mysql.sock.

MariaDB port

The default port for MariaDB is 3306.

MariaDB Process Identification Number

The MariaDB .pid file allows other programs to find out the PID (Process Identification Number) of a running script. Find it at /opt/bitnami/mariadb/data/mysqld.pid.

MariaDB log file

The log-error file contains information indicating when MariaDB was started and stopped and also any critical errors that occur while the server is running. If MariaDB notices a table that needs to be automatically checked or repaired, it writes a message to the error log. Find it at /opt/bitnami/mariadb/data/mysqld.log.

What are the default ports?

A port is an endpoint of communication in an operating system that identifies a specific process or a type of service. Bitnami stacks include several services or servers that require a port.

Remember that if you need to open some ports you can follow the instructions given in the FAQ to learn how to open the server ports for remote access.

Port 22 is the default port for SSH connections.

The MariaDB access port is 3306. This port is closed by default, you must open it to enable remote access.

How to secure your server?

Once you have created a new database and user for your application, connect to your MariaDB server and follow these recommendations:

  • Remove anonymous users:

     MariaDB> DELETE FROM mysql.user WHERE User='';
    
  • Remove the test database and access to it:

     MariaDB> DROP DATABASE test;
     MariaDB> DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';
    
  • Disallow root login remotely:

     MariaDB> DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
    

    Don't forget to reload the privileges tables to apply the changes:

     MariaDB> FLUSH PRIVILEGES;
    
  • Change your root user password.

  • It is strongly recommended that you do not have empty passwords for any user accounts when using the server for any production work.

  • If you don't need remote access, uncomment the line

     #bind-address=127.0.0.1
    

    in the MariaDB configuration file to only listen for connections on the local machine. Restart the server once done.

How to find the MariaDB database credentials?

How to connect to the MariaDB database?

You can connect to the MariaDB database from the same computer where it is installed with the mysql client tool.

$ mysql -u root -p

You will be prompted to enter the root user password. This is the same as the application password.

How to connect to MariaDB from a different machine?

For security reasons, the MariaDB port in this solution cannot be accessed over a public IP address. To connect to MariaDB from a different machine, you must open port 3306 for remote access. Refer to the FAQ for more information on this.

IMPORTANT: By default, the database port for the nodes in this solution cannot be accessed over a public IP address. As a result, you will only be able to connect to your database nodes from machines that are running in the same network. For security reasons, we do not recommend making the database port accessible over a public IP address. If you must make it accessible over a public IP address, we recommend restricting access to a trusted list of source IP addresses using firewall rules. Refer to the FAQ for information on opening ports in the server firewall.

Once you have an active SSH tunnel or you opened the port for remote access, you can then connect to MariaDB using a command like the one below.

Remember to replace SOURCE-PORT with the source port number specified in the SSH tunnel configuration or 3306 if you opened the port for remote access.

$ mysql -h 127.0.0.1 -P SOURCE-PORT -u root -p

You will be prompted to enter the root user password. This is the same as the application password.

How to reset the MariaDB root password?

Please note that depending on the version you have installed, you may find the MariaDB files at /opt/bitnami/mysql

If you don't remember your MariaDB root password, you can follow the steps below to reset it to a new value:

  • Create a file in /home/bitnami/mysql-init with the content shown below (replace NEW_PASSWORD with the password you wish to use):

     UPDATE mysql.user SET Password=PASSWORD('NEW_PASSWORD') WHERE User='root';
     FLUSH PRIVILEGES;
    
  • Stop the MariaDB server:

     $ sudo /opt/bitnami/ctlscript.sh stop mariadb
    
  • Start MariaDB with the following command:

     $ sudo /opt/bitnami/mariadb/bin/mysqld_safe --defaults-file=/opt/bitnami/mariadb/my.cnf --pid-file=/opt/bitnami/mariadb/data/mysqld.pid --init-file=/home/bitnami/mysql-init 2> /dev/null &
    
  • Restart the MariaDB server:

     $ sudo /opt/bitnami/ctlscript.sh restart mariadb
    
  • Remove the init script

     $ rm /home/bitnami/mysql-init
    

How to change the MariaDB root password?

You can modify the MariaDB password using the following command at the shell prompt:

$ /opt/bitnami/mariadb/bin/mysqladmin -p -u root password NEW_PASSWORD

How to create a database for a custom application?

These are the basic steps to create a new database and user for your applications:

  • Create a new database:

     MariaDB> create database DATABASE_NAME;
     Query OK, 1 row affected (0.00 sec)
    
  • Create a new user (only with local access) and grant privileges to this user on the new database:

     MariaDB> grant all privileges on DATABASE_NAME.* TO 'USER_NAME'@'localhost' identified by 'PASSWORD';
     Query OK, 1 row affected (0.00 sec)
    
  • Create a new user (with remote access) and grant privileges to this user on the new database:

     MariaDB> grant all privileges on DATABASE_NAME.* TO 'USER_NAME'@'%' identified by 'PASSWORD';
     Query OK, 1 row affected (0.00 sec)
    
  • After modifying the MariaDB grant tables, execute the following command in order to apply the changes:

     MariaDB> flush privileges;
     Query OK, 1 row affected (0.00 sec)
    

Some applications require specific privileges in the database. Check the MariaDB official documentation for getting, installing, and upgrading MariaDB.

How to change the data directory?

The data directory for MariaDB is set to /opt/bitnami/mariadb/data by default. You can modify the location of this folder modifying the /opt/bitnami/mariadb/my.cnf file, as shown below:

...
datadir=/opt/bitnami/mariadb/data
...

Also modify the /opt/bitnami/mariadb/scripts/ctl.sh file to reflect the new directory location:

--datadir=/opt/bitnami/mariadb/data

Finally, move the data/ directory to the new location and restart the database.

How to encrypt a database table?

NOTE: Table encryption support is only available for InnoDB tables stored as individual files (the innodb_file_per_table option, enabled by default).

Follow the steps below to configure table encryption support:

  • Edit the /opt/bitnami/mysql/bitnami/my.cnf file and add the following lines to it, within the [mysqld] section, to activate the keyring_file plugin:

    early-plugin-load=keyring_file.so
    keyring_file_data=/opt/bitnami/mysql/data/keyring
    
    NOTE: The keyring file will be automatically created in the above location when the first table is encrypted. Keep a backup of this file as the data stored in the encrypted tables cannot be recovered without it.
  • Restart the MySQL server:

    $ sudo /opt/bitnami/ctlscript.sh restart mysql
    
  • Confirm that the keyring_file plugin is active by running the query below in the MySQL client:

    SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%';
    

You should now be able to create an encrypted table by adding the ENCRYPTED='Y' clause to any CREATE TABLE command. Here is an example:

CREATE TABLE mytable (id INT, value VARCHAR(255)) ENCRYPTION='Y'

Tables which are not already encrypted can be encrypted by using an ALTER TABLE command, such as the one below:

ALTER TABLE mytable ENCRYPTION='Y'

How can I run a command in the Bitnami MariaDB Stack?

Log in to the server console as the bitnami user and run the command as usual. The required environment is automatically loaded for the bitnami user.

How to create a database backup?

To back up all the databases, create a dump file using the mysqldump tool.

$ mysqldump -A -u root -p > backup.sql

This operation could take some time depending on the database sizes.

NOTE: The steps previously described will only back up the data contained inside your databases. There may be other files that you should take into account when performing a full backup, such as files that may have been uploaded to your application. Refer to your application's documentation for more details.

How to restore a database backup?

Once you have the backup file, you can restore it with a command like the one below:

$ mysql -u root -p < backup.sql

How to debug errors in your database?

Please note that depending on the version you have installed, you may find the MariaDB files at /opt/bitnami/mysql

The main log file is created at /opt/bitnami/mariadb/data/mysqld.log on the MySQL database server host.

How to configure MySQL Workbench?

NOTE: This section assumes that you have downloaded and installed MySQL Workbench.

To connect to your remote MySQL database server using MySQL Workbench, you have to allow remote connections to the server.

Once your MySQL server is configured to accept remote connections, you can connect to it using MySQL Workbench. Follow these steps:

  • Launch MySQL Workbench.
  • Click the "+" symbol in the "MySQL Connections" tab to add a new connection.

    MySQL Workbench configuration

  • Configure the connection as follows:
    • Enter a name for the connection in the "Connection Name" field.
    • Select "Standard (TCP/IP)" as the "Connection Type".
    • Enter your cloud server's IP address in the "Hostname" field.
    • Specify the "Port" as "3306".
    • Specify the "Username" as "root".

      MySQL Workbench configuration

  • Click "Test Connection" to test the connection.
  • If the connection is successful, click "OK" to save the connection.

    MySQL Workbench configuration

  • Double-click the new connection to launch the MySQL Workbench SQL Editor. You may be prompted for a password. Use the same password you used when previously configuring the server to accept remote connections. Once connected, the SQL editor window will open and you can interact with the server using SQL commands, as shown below:

MySQL Workbench configuration

How to allow remote connections to the MySQL server?

IMPORTANT: Making this application's network ports public is a significant security risk. You are strongly advised to only allow access to those ports from trusted networks. If, for development purposes, you need to access from outside of a trusted network, please do not allow access to those ports via a public IP address. Instead, use a secure channel such as a VPN or an SSH tunnel. Follow these instructions to remotely connect safely and reliably.

To connect to your remote MySQL database server using a remote client or a visual tool like MySQL Workbench, follow these steps:

  • Make sure that you have your cloud server's IP address and application credentials (instructions).
  • Open port 3306 in the server firewall (instructions).
  • Connect to your cloud server using PuTTY or another SSH client (instructions).
  • At the server console, edit the file /opt/bitnami/mysql/my.cnf and find the line containing

    bind-address=127.0.0.1
    
  • Comment out this line by placing a hash (#) symbol at the beginning, so that it looks like this:

    #bind-address=127.0.0.1
    
  • Save the file.
  • At the server console, allow remote access to the MySQL database server using the MySQL command line client. Use the following command, remembering to replace PASSWORD with the application password from the first step:

    $ /opt/bitnami/mysql/bin/mysql -u root -p -e "grant all privileges on *.* to 'root'@'%' identified by 'PASSWORD' with grant option";
    
  • When prompted for a password, enter the application password you noted in the first step.
  • Restart the MySQL server:

    $ sudo /opt/bitnami/ctlscript.sh restart mysql
    

How to recover a MariaDB database with errors?

Before trying to recover a MariaDB database, you should check the exact error in the MariaDB log file at /opt/bitnami/mariadb/data/mysqld.log. Check the latest entries in the MariaDB log file with the following command:

$ sudo tail -n 100 /opt/bitnami/mariadb/data/mysqld.log

In this case, assume the following error in the log file:

110108 10:37:45 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'user' is marked as crashed

Here are some steps to resolve this error:

  • The MariaDB database is configured to use InnoDB engine by default. You can add the innodb_force_recovery=1 option in the main MariaDB configuration file at /opt/bitnami/mariadb/my.cnf to try and fix the database:

     [mysqld]
     innodb_force_recovery = 1
    
  • Start the MariaDB database with the following command:

     $ mysqld --skip-grant-tables --user=mysql --pid-file=/opt/bitnami/mariadb/data/mysqld.pid
     --skip-external-locking --port=3306 --sock=/opt/bitnami/mariadb/tmp/mysql.sock
    
  • Open a new console and try to log in the database:

     $ mysql -u root -p
    
  • In this case, the error was related to the mysql.user table. Run these commands:

     MariaDB> use mysql;
     MariaDB> repair table user;
     MariaDB> check table user;
     MariaDB> exit;
    

If the table is recovered, you should see "OK" in the mysql.user status table. Do not forget to remove the innodb_force_recovery option from the my.cnf file and restart the MariaDB server again.

$ sudo /opt/bitnami/ctlscript.sh restart mariadb

If you find a different error or cannot fix an issue, we can try to help at http://community.bitnami.com.

virtualMachine

Bitnami Documentation