MariaDB

As of openSUSE 12.3, MySQL has been replaced by MariaDB and is in use on my server. This is a drop-in replacement of MySQL and so all commands below will still work in exactly the same way.

MariaDB is a fork of the original MySQL database software. It is intended to be a drop-in replacement, that is, it is almost functionally identical to its predecessor. It is however, optimized to be faster and has more choice of database formats than the original MySQL allowed. It is also completely free (libre) and will stay this way, even if Oracle do decide to stop supplying MySQL free (this is the biggest concern for MySQL's future).

Examples of programs that use MariaDB include:

• Amarok music player
• Akonadi PIM subsystem (KDE)
• MediaWiki
• Many other content management systems

Setting up MariaDB for the first use

You will need to set the root password. This is like the root account on Linux, it allows full control over MariaDB. Make sure this password is kept secure at all times. Many of the programs that require the MariaDB database will require root access initially to make the tables they require.

Changing the root password

Authentication is done by the Linux root user as of openSUSE 15.4 - log in as root and type mysql to login as root

• Open Konsole

( At the command prompt, type:

# mysqladmin -u root password newpassword

• For example, if you wanted to change your password to computer, type:

# mysqladmin -u root password computer

• Remember, you password is in plain sight on the command prompt. Make sure no one sees you typing and clear the screen afterwards by typing at the prompt:

# clear

• You may also want to clear your scrollback. This can be done in the 'Tools' menu of Konsole.
• Test your new password by logging into MariaDB. Type at the prompt:

# mysql -u root -p

• Type your password when asked.
• If you've logged in OK, exit by typing (Don't forget the ;):

mysql> exit; 

• MariaDB will respond with 'Bye' if you have correctly exited and return you to the prompt.
• If your stuck and MariaDB is returning -> at any time, you have forgotten the ; at the end of the command. If all you have typed is exit, type ; on its own and MariaDB will exit.

Configuring MySQL

There are a few things you may want to change with MySQL, depending on how you plan on using MySQL.

InnoDB separate tables

MariaDB does not use the InnoDB storage engine by default. If you are using MariaDB with standard settings, these settings will have no effect and so don't need to be changed.

By default, MySQL uses InnoDB to store the information in the database. MariaDB uses XtraDB. The old method, MyIASM stored the data within the database folder on the hard drive separately. InnoDB now stores this data for all created databases under one file called ibdata1. There are a few problems with this implementation:

• All data stored under one file means if this one file becomes corrupt, you are more likely to loose ALL the databases you have.
• A problem in InnoDB makes it so that when you drop a table, the space is not freed from the file, the table is basically unlinked from the data. This can be a security issue if you are working with sensitive data. Another problem means that the file will never shrink, the file will grow until all space is exhausted.
• Once set up in the mode, any database created before this change will still be stored in the one file. The only way to make it use it's own table is by exporting the database dropping the table and then importing the database again.

MySQL does let you fall back to the old MyIASM method of storing the data while using the InnoDB storage method still. If you want to turn this option on, you will need to do the following:

• Goto the Launch Menu and launch Kwrite.
• You then need to goto 'File' on the menu bar and click 'Open'.
• You now need to open the MySQL configuration file. This is located at /etc/my.cnf.
• Scroll down and find the block (values edited out for security):

The MySQL server
[mysqld]
port            = 3306
socket          = /<dir>/mysql.sock
Change following line if you want to store your database elsewhere
datadir = /<dir>
skip-external-locking
key_buffer_size = <num>M
max_allowed_packet = <num>M
table_open_cache = <num>
sort_buffer_size = <num>K
net_buffer_length = <num>K
read_buffer_size = <num>K
read_rnd_buffer_size = <num>K
myisam_sort_buffer_size = <num>M 

• At the end of the block, you will need to add the following:

innodb_file_per_table=1

• Now save this change and close the file.
• For the change to take effect, you will need to restart the MariaDB server.
• Goto the Lanuch menu and open Konsole
• At the prompt type service mysql restart. This can take a few seconds.
• Now type service mysql status. You should see that the sever is now running. If it isn't and it says 'failed', there is a problem with what you have typed. You will need to go back through from the beginning, ensuring you have typed the directive correctly and have placed it in the correct block.

Binary logging

MariaDB does not use binary logging or replication by default. If you are using MariaDB with standard settings, these settings will have already been disabled and so don't need to be changed.

MySQL by default turns on a feature called binary logging. This makes it so that every transaction within the database, including the data, is stored in a binary log. That way, if you are running more than 1 MySQL server in a master-slave configuration, you can link them together and the slave reads the logs to get the same data. You more than likely won't need this if you are running a stand-alone MySQL setup. The problems with this implementation are:

• MySQL has to write the transaction twice, once to the table and once to the log. This slows things down.
• MySQL has data stored in 2 places, doubling the disk space for each write. This is also a security issue if working with sensitive data.

If you are not using a MySQL duplication server, the logging is of no use. It can be turned off by the following:

• Go to the Launch Menu and launch Kwrite.
• You then need to go to 'File' on the menu bar and click 'Open'.
• You now need to open the MariaDB configuration file. This is located at /etc/my.cnf.
• Scroll down and find the block:

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

• You now need to comment out the lines log-bin=mysql-bin and binlog_format. This is done by adding a # to the beginning of the lines. Once commented out, the block should look like this:

# Replication Master Server (default)
# binary logging is required for replication
# log-bin=mysql-bin

# binary logging format - mixed recommended
# binlog_format=mixed

• Now save this change and close the file.
• For the change to take effect, you will need to restart the MariaDB server.
• Go to the Lanuch menu and open Konsole
• At the prompt type service mysql restart. This can take a few seconds.
• Now type service mysql status. You should see that the sever is now running. If it isn't and it says 'failed', there is a problem with what you have typed. You will need to go back through from the beginning, ensuring you have put the # in front of the lines of code shown.

Adding a new user to MariaDB

You may need to add a user to MariaDB that doesn't need all the permissions of root (for example, to backup MariaDB). Here are some instructions to help you set up a read-only user, in this example, to make a read-only backup user.

• Open Konsole.
• If not logged on a root, su.
• Run mysql -u root -p and type your password when requested.
• Type the following, adapting to your needs:

mysql> CREATE USER backup@localhost IDENTIFIED BY 'password';

• Change password to a password of your choice. If using for a cron job later on, keep the password to an alphanumeric one without any special characters.
• To grant it read-only privileges, run the following command:

mysql> GRANT SELECT, LOCK TABLES ON *.* TO backup@localhost IDENTIFIED 'password';

• To make MariaDB take note of the new privileges, restart the MariaDB server or run the following command:

mysql> FLUSH PRIVILEGES;

• You can now exit the MariaDB server.

Upgrading to MariaDB 10

In openSUSE 13.1, it is possible to update to MariaDB 10 via the repositories system. If you do decide to upgrade, MariaDB will not run straight away after installation. To make it run, follow these steps:

1. Make sure all databases are backed up (on this server, make sure your nightly backups have exported successfully)
2. Run touch /var/lib/mysql/.force_upgrade - this allows the upgrade to take place
3. Run service mysql start - this may take a while the first time it is started as the database is being upgraded

Related Articles

• Importing a MySQL database
• Exporting a MySQL database

External Links

• MariaDB website