Difference between revisions of "MariaDB"

From Alpine Linux
Jump to: navigation, search
(Created page with "[https://mariadb.org/ MariaDB] is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. It is notable for be...")
 
(added links to the production focused documentation)
(16 intermediate revisions by 7 users not shown)
Line 1: Line 1:
 
[https://mariadb.org/ MariaDB] is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. It is notable for being led by the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle.
 
[https://mariadb.org/ MariaDB] is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. It is notable for being led by the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle.
  
= Setup =
+
'''This is the general documentation for normal and common general usage, for professional usage please use [[Production DataBases : mysql]] that is the same as MariaDB but with several modifications focused on production and security!'''
MariaDB packages can be installed by running
 
{{cmd|apk add mariadb mariadb-client}}
 
  
Defining variables that will be used for setup and configuration
+
== Instalation ==
{{cmd|<nowiki>DB_DATA_PATH="/var/lib/mysql"
 
DB_ROOT_PASS="mariadb_root_password"
 
DB_USER="mariadb_user"
 
DB_PASS="mariadb_user_password"
 
MAX_ALLOWED_PACKET="200M"</nowiki>}}
 
  
Installing mysql database
+
Alpine Linux has dummy counterparts packages for those that are not close to that change from ''mysql'' to ''mariadb'' naming packages.
{{cmd|<nowiki>mysql_install_db --user=mysql --datadir=${DB_DATA_PATH}</nowiki>}}
 
  
Starting service
+
Take in consideration that the user <code>mysql</code> was created during instalation of packages, in the initialization section two users will be created in database init: <code>root</code> and <code>mysql</code>, and in that point only if are in their respective system accounts, will be able to connect to the database service.
{{cmd|rc-service mariadb start}}
+
 
 +
<pre><nowiki>
 +
apk add mysql mysql-client
 +
</nowiki></pre>
 +
 
 +
That will install the most used ones.. <code>mariadb-cient</code> and <code>mariadb-server</code>, rest of packages are brief described here for more information, here are listed in orden of relevance for production server
 +
 
 +
{| class="wikitable"
 +
|-
 +
! MySQL name package !! Since Alpine: !! Brief usage !! Related package
 +
|-
 +
| mysql || v2 || it's a dummy package to easy install of mariadb || mariadb
 +
|-
 +
| mysql-client || v2 || it's a dummy package to easy install of commands tools || mariadb-client
 +
|-
 +
| mariadb || v2 || server equivalent to mysql-server || mariadb-common
 +
|-
 +
| mariadb-client || v2 || connection command line and tools || mariadb-common
 +
|-
 +
| mariadb-doc || v3.0 || manpages are there! || man man-pages
 +
|-
 +
| mariadb-connector-odbc || edge || coding or making OS level connections, to any DB without libs install || .
 +
|-
 +
| mariadb-connector-c || v3.8 || coding connection on C sources || mariadb-connector-c-dev
 +
|-
 +
| mariadb-backup || v3.8 || to external backup devices, not widely used, in past was inside mariadb package || .
 +
|-
 +
| mariadb-server-utils || v3.8 || server commands not widely used, in past was inside mariadb package || .
 +
|-
 +
| mariadb-dev || v3.1 || Need for compilations depends on source code || .
 +
|-
 +
| mariadb-test || v3.3 || testing suite from MariaDB tools || .
 +
|-
 +
| mariadb-mytop || v3.9 || data performance monitoring || .
 +
|-
 +
| mariadb-plugin-rocksdb || v3.9 || plain key-value event relational for data || .
 +
|-
 +
| mariadb-static || v3.8 || static libs for static non depends linking in builds || .
 +
|-
 +
| mariadb-embedded || v3.9 || the libmysqld identical interface as the C client || mariadb-embedded-dev
 +
|-
 +
| mariadb-embedded-dev || v3.9 ||  use the normal mysql.h and link with libmysqld instead of libmysqlclient || mariadb-dev
 +
|-
 +
| mariadb-openrc || v3.8 || separate scripts, in past was embebed on server package || .
 +
|}
 +
 
 +
== Initialization ==
 +
 
 +
The alpine package of MySQL/MariaDB are like normal tarball of MySQL one, admins must know what they want.. there's no automatic window-like installation here.
 +
 
 +
The ''datadir'' located at <code>/var/lib/mysql</code> must be owned by the mysql user and group. You can modify this behavior but must edit the service file at <code>/etc/init.d</code> directory. Also, you need to set <code><nowiki>datadir=<YOUR_DATADIR></nowiki></code> under section <code>[mysqld]</code> at the config file.
 +
 
 +
# Initialize the main mysql database, and the data dir as standardized to <code>/var/lib/mysql</code> by the rc script.<br>/etc/init.d/mariadb setup
 +
# Then you can start the service but there's no root password set until this point.<br>/etc/init.d/mariadb start
 +
# Secure the installation by running<code>mysql_secure_installation</code>
 +
# Setup permissions for manage others users and databases
 +
 
 +
== Configuration ==
 +
 
 +
In order to finish setup into '''MariaDB''' now provide '''this script called <code>mysql_secure_instalation</code> that also are present as <code>mariadb-secure-installation</code>''', too. This script provides minimal and security setup to the database, and here are the questions made explained:
 +
 
 +
# '''Enter current password for root (enter for none):''' If you have previously set up a root password, provide it here and press enter. If correctly entered, the response will be <code>OK, successfully used password, moving on...</code>
 +
#  '''Switch to unix_socket authentication [Y/n]''' Setting the root password or using the Unix_socket ensures that only admins can log into engine database. MySQL 5.6 and MariaDB 10.2 introduced socket authentication, where system users are the same as the MySQL/MariaDB users. For production servers you should disable this by answering "n", which will give you the response <code>... skipping.</code>
 +
# '''Change the root password? [Y/n]''' This gives you the opportunity to change the root password to a stronger one if necessary. If this is not needed, enter 'n'.
 +
# '''Remove anonymous users? [Y/n]''' Remove anonymous users created to log in using socket authentication. For production systems, disallow this by answering 'Y', resulting in: <code>... Success!</code>.
 +
# '''Disallow root login remotely? [Y/n]''' Normally, root should only be allowed to connect from 'localhost' in order to protect from password sniffing attempts over the network. Enter 'Y' to get: <code>... Success!</code>.
 +
# '''Remove test database and access to it? [Y/n]''' By default, MariaDB comes with a database named 'test' that anyone can access. If this is not needed, 'Y'. The response will be: <code>... Success!</code>.
 +
# '''Reload privilege tables now? [Y/n]''' Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Answer 'Y' and the response will be: <code>... Success!</code>.
 +
 
 +
After responding to each question, restart the service with <code>rc-service mariadb restart</code>
 +
 
 +
=== Configuration files and customization ===
 +
 
 +
Due today were influenced by systemd standardization, the famous <nowiki>my.cnf</nowiki> are not more the main config file for the server engine. Now only few variables are defined there and all the settings are provided by independent files into the <code>/etc/my.cnf.d/</code> directory, user own config files are under <code>~/.my.cnf</code> config file of each home dir, and are read after global ones; so then we have:
 +
 
 +
{| class="wikitable"
 +
|-
 +
! Config file !! Path and name !! Versions of Alpine !! Contents to configure
 +
|-
 +
| my.cnf || <nowiki>/etc/mysql/my.cnf</nowiki> || v2 to v3.8 || All the directives, Global config file
 +
|-
 +
| mariadb-server.cnf || /etc/my.cnf.d/mariadb-server.cnf || since 3.9 || First Global config file, main directives
 +
|-
 +
| .my.cnf || <nowiki>$HOME</nowiki> || all || user name only config directives
 +
|}
 +
 
 +
 
 +
Newer system Alpine packages can set in independent files in any case those commands always works and where are not apply just will ignore the output:
 +
 
 +
* On older Alpine system must set config files for MAX ALLOWED PACKETS to minimun proper amount:
  
You should get something like
 
 
<pre>
 
<pre>
* Caching service dependencies ...                       [ ok ]
+
<nowiki>
* Starting mariadb ...
+
sed -i "s|.*max_allowed_packet\s*=.*|max_allowed_packet = 100M|g" /etc/mysql/my.cnf
161122 09:23:06 mysqld_safe Logging to syslog.            [ ok ]
+
sed -i "s|.*max_allowed_packet\s*=.*|max_allowed_packet = 100M|g" /etc/my.cnf.d/mariadb-server.cnf
 +
</nowiki>
 
</pre>
 
</pre>
  
Setting root password
+
* Only allow local connections on cases where there's only one server or no expected to connect from others:
{{cmd|<nowiki>mysqladmin -u root password '${DB_ROOT_PASS}'</nowiki>}}
+
 
 +
<pre>
 +
<nowiki>
 +
sed -i "s|.*bind-address\s*=.*|bind-address=127.0.0.1|g" /etc/mysql/my.cnf
 +
sed -i "s|.*bind-address\s*=.*|bind-address=127.0.0.1|g" /etc/my.cnf.d/mariadb-server.cnf
 +
</nowiki>
 +
</pre>
  
Creating new user, removing sequrity sensitive data
+
* If are not in domain controller, dont search for hostnames to improve performance responses (ideal for local only servers):
{{cmd|<nowiki>echo "GRANT ALL ON *.* TO ${DB_USER}@'127.0.0.1' IDENTIFIED BY '${DB_PASS}' WITH GRANT OPTION;" > /tmp/sql
 
echo "GRANT ALL ON *.* TO ${DB_USER}@'localhost' IDENTIFIED BY '${DB_PASS}' WITH GRANT OPTION;" >> /tmp/sql
 
echo "GRANT ALL ON *.* TO ${DB_USER}@'::1' IDENTIFIED BY '${DB_PASS}' WITH GRANT OPTION;" >> /tmp/sql
 
echo "DELETE FROM mysql.user WHERE User='';" >> /tmp/sql
 
echo "DROP DATABASE test;" >> /tmp/sql
 
echo "FLUSH PRIVILEGES;" >> /tmp/sql
 
cat /tmp/sql | mysql -u root --password='${DB_ROOT_PASS}'</nowiki>}}
 
  
Modifying configuration file /etc/mysql/my.cnf
+
<pre>
{{cmd|<nowiki>sed -i "s|max_allowed_packet\s*=\s*1M|max_allowed_packet = ${MAX_ALLOWED_PACKET}|g" /etc/mysql/my.cnf
+
<nowiki>
sed -i "s|max_allowed_packet\s*=\s*16M|max_allowed_packet = ${MAX_ALLOWED_PACKET}|g" /etc/mysql/my.cnf</nowiki>}}
+
sed -i "s|.*skip-networking.*|skip-networking|g" /etc/mysql/my.cnf
 +
sed -i "s|.*skip-networking.*|skip-networking|g" /etc/my.cnf.d/mariadb-server.cnf
 +
</nowiki>
 +
</pre>
  
Normally you want to start the MariaDB server when the system is launching. This is done by adding MariaDB to the needed runlevel.
+
* Set default charset to UTF8MB4, in newer versions (since Alpine v3.9), just added a new file to added thus customization, but older versions (below Alpine v3.8)of the package does not have a charset section, so you must added manually to the main configuration in each respective section:
{{cmd|rc-update add mariadb default}}
 
  
Now MariaDB server should start automatically when you launch your system next time. To test that run:
+
<pre>
{{cmd|reboot}}
+
<nowiki>
 +
cat > /etc/my.cnf.d/mariadb-server-default-charset.cnf << EOF
 +
[client]
 +
default-character-set = utf8mb4
  
To make sure that Nginx and PHP are started run:
+
[mysqld]
{{cmd|<nowiki>ps aux | grep mysql</nowiki>}}
+
collation_server = utf8mb4_unicode_ci
 +
character_set_server = utf8mb4
  
You should get something like this:
+
[mysql]
<pre>
+
default-character-set = utf8mb4
  382 mysql      0:00 /usr/bin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --pid-file=/run/mysqld/mysqld.pid --socket=/run/mysqld/mysqld.sock --port=3306
+
EOF
  383 root      0:00 logger -t mysqld -p daemon.error
+
</nowiki>
 
</pre>
 
</pre>
 +
 +
== Updating or comming from upgrading ==
 +
 +
Mayor Upgrades beetween Alpine linux version are so easy as change the repository version, but the MySQL/MariaDB  engine need some extra steps when this are performed:
 +
 +
Upgrade databases on major releases
 +
Upon a major version release of mariadb (for example mariadb-10.1.10-1 to mariadb-10.1.18-1), it is wise to upgrade databases:
 +
 +
# keep the old database (mysql sheme) structure of the engine daemon, currently this are not more the case, today this not make sense anymore
 +
# upgrade the MariaDB/MySQL packages, of course with must be done if the upgrade process to mayor alpine version does not!
 +
# run the <code>mysql_upgrade -u root -p</code> script, providing the password or root, (from the new package version) against the old still-running database (mysql sheme). This will produce some error messages; however, the upgrade will succeed.
 +
# Restart the service
 +
 +
If are unable to run ''mysql_upgrade'' because MySQL cannot start try run MySQL in safemode with <code>mysqld_safe --datadir=/var/lib/mysql/</code> command and then run the <code>mysql_upgrade -u root -p</code> script.
 +
 +
= Relevant important notes =
 +
 +
== File system notes about the databases managed ==
 +
 +
'''BTRFS Notes'''
 +
 +
If the database (in <code>/var/lib/mysql</code>) resides on a Btrfs file system, you should consider disabling '''Copy-on-Write''' for the directory before creating any database (schemes), after initialization you can enabled again. But .. on every database creation (scheme creation), you must disabled again, to avoid corrupted data.
 +
 +
'''ZFS Bock sizes'''
 +
 +
ZFS, unlike most other file systems, has a variable record size, or what is commonly referred to as a block size. By default, the recordsize on ZFS is 128KiB, which means it will dynamically allocate blocks of any size from 512B to 128KiB depending on the size of file being written. Most RDBMSes work in 8KiB-sized blocks by default. Although the block size is tunable for MySQL/MariaDB use an 8KiB block size by default.
 +
 +
It is usually desirable to tune ZFS instead to accommodate the databases, using a command such as <code>zfs set recordsize=8K /var/lib/mysql</code> (or change /var/lib/mysql to the mount point where /var/lib/mysql resides) and in the interest of saving memory, it is best to simply disable ZFS's caching of the database's file data and let the database do its own job  with <code>zfs set primarycache=metadata /var/lib/mysql</code> (or change /var/lib/mysql to the mount point where /var/lib/mysql resides).
 +
 +
But beware, these kinds of tuning parameters are only if RDBMSes are setup in dedicated partitions, if your root and of course database are all in one partition, dont do that. Separate ones.
 +
 +
= See Also =
 +
 +
* [[Production DataBases : mysql]]
 +
* [[Production LAMP system: Lighttpd + PHP + MySQL]]
 +
* [[Alpine newbie developer]]
 +
* [[Alpine newbie lammers]]
 +
 +
[[Category:Newbie]]
 +
[[Category:Server]]
 +
[[Category:Database]]
 +
[[Category:Development]]
 +
[[Category:Security]]
 +
[[Category:Production]]

Revision as of 16:00, 7 June 2020

MariaDB is a community-developed fork of the MySQL relational database management system intended to remain free under the GNU GPL. It is notable for being led by the original developers of MySQL, who forked it due to concerns over its acquisition by Oracle.

This is the general documentation for normal and common general usage, for professional usage please use Production DataBases : mysql that is the same as MariaDB but with several modifications focused on production and security!

Instalation

Alpine Linux has dummy counterparts packages for those that are not close to that change from mysql to mariadb naming packages.

Take in consideration that the user mysql was created during instalation of packages, in the initialization section two users will be created in database init: root and mysql, and in that point only if are in their respective system accounts, will be able to connect to the database service.

apk add mysql mysql-client

That will install the most used ones.. mariadb-cient and mariadb-server, rest of packages are brief described here for more information, here are listed in orden of relevance for production server

MySQL name package Since Alpine: Brief usage Related package
mysql v2 it's a dummy package to easy install of mariadb mariadb
mysql-client v2 it's a dummy package to easy install of commands tools mariadb-client
mariadb v2 server equivalent to mysql-server mariadb-common
mariadb-client v2 connection command line and tools mariadb-common
mariadb-doc v3.0 manpages are there! man man-pages
mariadb-connector-odbc edge coding or making OS level connections, to any DB without libs install .
mariadb-connector-c v3.8 coding connection on C sources mariadb-connector-c-dev
mariadb-backup v3.8 to external backup devices, not widely used, in past was inside mariadb package .
mariadb-server-utils v3.8 server commands not widely used, in past was inside mariadb package .
mariadb-dev v3.1 Need for compilations depends on source code .
mariadb-test v3.3 testing suite from MariaDB tools .
mariadb-mytop v3.9 data performance monitoring .
mariadb-plugin-rocksdb v3.9 plain key-value event relational for data .
mariadb-static v3.8 static libs for static non depends linking in builds .
mariadb-embedded v3.9 the libmysqld identical interface as the C client mariadb-embedded-dev
mariadb-embedded-dev v3.9 use the normal mysql.h and link with libmysqld instead of libmysqlclient mariadb-dev
mariadb-openrc v3.8 separate scripts, in past was embebed on server package .

Initialization

The alpine package of MySQL/MariaDB are like normal tarball of MySQL one, admins must know what they want.. there's no automatic window-like installation here.

The datadir located at /var/lib/mysql must be owned by the mysql user and group. You can modify this behavior but must edit the service file at /etc/init.d directory. Also, you need to set datadir=<YOUR_DATADIR> under section [mysqld] at the config file.

  1. Initialize the main mysql database, and the data dir as standardized to /var/lib/mysql by the rc script.
    /etc/init.d/mariadb setup
  2. Then you can start the service but there's no root password set until this point.
    /etc/init.d/mariadb start
  3. Secure the installation by runningmysql_secure_installation
  4. Setup permissions for manage others users and databases

Configuration

In order to finish setup into MariaDB now provide this script called mysql_secure_instalation that also are present as mariadb-secure-installation, too. This script provides minimal and security setup to the database, and here are the questions made explained:

  1. Enter current password for root (enter for none): If you have previously set up a root password, provide it here and press enter. If correctly entered, the response will be OK, successfully used password, moving on...
  2. Switch to unix_socket authentication [Y/n] Setting the root password or using the Unix_socket ensures that only admins can log into engine database. MySQL 5.6 and MariaDB 10.2 introduced socket authentication, where system users are the same as the MySQL/MariaDB users. For production servers you should disable this by answering "n", which will give you the response ... skipping.
  3. Change the root password? [Y/n] This gives you the opportunity to change the root password to a stronger one if necessary. If this is not needed, enter 'n'.
  4. Remove anonymous users? [Y/n] Remove anonymous users created to log in using socket authentication. For production systems, disallow this by answering 'Y', resulting in: ... Success!.
  5. Disallow root login remotely? [Y/n] Normally, root should only be allowed to connect from 'localhost' in order to protect from password sniffing attempts over the network. Enter 'Y' to get: ... Success!.
  6. Remove test database and access to it? [Y/n] By default, MariaDB comes with a database named 'test' that anyone can access. If this is not needed, 'Y'. The response will be: ... Success!.
  7. Reload privilege tables now? [Y/n] Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Answer 'Y' and the response will be: ... Success!.

After responding to each question, restart the service with rc-service mariadb restart

Configuration files and customization

Due today were influenced by systemd standardization, the famous my.cnf are not more the main config file for the server engine. Now only few variables are defined there and all the settings are provided by independent files into the /etc/my.cnf.d/ directory, user own config files are under ~/.my.cnf config file of each home dir, and are read after global ones; so then we have:

Config file Path and name Versions of Alpine Contents to configure
my.cnf /etc/mysql/my.cnf v2 to v3.8 All the directives, Global config file
mariadb-server.cnf /etc/my.cnf.d/mariadb-server.cnf since 3.9 First Global config file, main directives
.my.cnf $HOME all user name only config directives


Newer system Alpine packages can set in independent files in any case those commands always works and where are not apply just will ignore the output:

  • On older Alpine system must set config files for MAX ALLOWED PACKETS to minimun proper amount:

sed -i "s|.*max_allowed_packet\s*=.*|max_allowed_packet = 100M|g" /etc/mysql/my.cnf
sed -i "s|.*max_allowed_packet\s*=.*|max_allowed_packet = 100M|g" /etc/my.cnf.d/mariadb-server.cnf

  • Only allow local connections on cases where there's only one server or no expected to connect from others:

sed -i "s|.*bind-address\s*=.*|bind-address=127.0.0.1|g" /etc/mysql/my.cnf
sed -i "s|.*bind-address\s*=.*|bind-address=127.0.0.1|g" /etc/my.cnf.d/mariadb-server.cnf

  • If are not in domain controller, dont search for hostnames to improve performance responses (ideal for local only servers):

sed -i "s|.*skip-networking.*|skip-networking|g" /etc/mysql/my.cnf
sed -i "s|.*skip-networking.*|skip-networking|g" /etc/my.cnf.d/mariadb-server.cnf

  • Set default charset to UTF8MB4, in newer versions (since Alpine v3.9), just added a new file to added thus customization, but older versions (below Alpine v3.8)of the package does not have a charset section, so you must added manually to the main configuration in each respective section:

cat > /etc/my.cnf.d/mariadb-server-default-charset.cnf << EOF
[client]
default-character-set = utf8mb4

[mysqld]
collation_server = utf8mb4_unicode_ci
character_set_server = utf8mb4

[mysql]
default-character-set = utf8mb4
EOF

Updating or comming from upgrading

Mayor Upgrades beetween Alpine linux version are so easy as change the repository version, but the MySQL/MariaDB engine need some extra steps when this are performed:

Upgrade databases on major releases Upon a major version release of mariadb (for example mariadb-10.1.10-1 to mariadb-10.1.18-1), it is wise to upgrade databases:

  1. keep the old database (mysql sheme) structure of the engine daemon, currently this are not more the case, today this not make sense anymore
  2. upgrade the MariaDB/MySQL packages, of course with must be done if the upgrade process to mayor alpine version does not!
  3. run the mysql_upgrade -u root -p script, providing the password or root, (from the new package version) against the old still-running database (mysql sheme). This will produce some error messages; however, the upgrade will succeed.
  4. Restart the service

If are unable to run mysql_upgrade because MySQL cannot start try run MySQL in safemode with mysqld_safe --datadir=/var/lib/mysql/ command and then run the mysql_upgrade -u root -p script.

Relevant important notes

File system notes about the databases managed

BTRFS Notes

If the database (in /var/lib/mysql) resides on a Btrfs file system, you should consider disabling Copy-on-Write for the directory before creating any database (schemes), after initialization you can enabled again. But .. on every database creation (scheme creation), you must disabled again, to avoid corrupted data.

ZFS Bock sizes

ZFS, unlike most other file systems, has a variable record size, or what is commonly referred to as a block size. By default, the recordsize on ZFS is 128KiB, which means it will dynamically allocate blocks of any size from 512B to 128KiB depending on the size of file being written. Most RDBMSes work in 8KiB-sized blocks by default. Although the block size is tunable for MySQL/MariaDB use an 8KiB block size by default.

It is usually desirable to tune ZFS instead to accommodate the databases, using a command such as zfs set recordsize=8K /var/lib/mysql (or change /var/lib/mysql to the mount point where /var/lib/mysql resides) and in the interest of saving memory, it is best to simply disable ZFS's caching of the database's file data and let the database do its own job with zfs set primarycache=metadata /var/lib/mysql (or change /var/lib/mysql to the mount point where /var/lib/mysql resides).

But beware, these kinds of tuning parameters are only if RDBMSes are setup in dedicated partitions, if your root and of course database are all in one partition, dont do that. Separate ones.

See Also