MySQL

From Alpine Linux
Revision as of 13:19, 11 August 2020 by Mckaygerhard (talk | contribs) (Mysql main page)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)

MySQL is the most popular database manager in free software for two simple reasons (which are not the best technical reasons):

  1. It's simple and very easy to use
  2. It is very similar to SQLSERVER and is also used in other systems

In the world of Linux Alpine there is a software that provides it, it is the MariaDB, and in the wiki there are two approaches for its use, the professional one (for servers and deploys) and the fast and simple usage (for developers and/or enthusiasts):

Template:TOC

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 MySQL that is the same as MariaDB but with several modifications focused on production and security!

Installation

The Alpine Linux repositories no longer include the actual MySQL binaries, installing the mysql-* packages will instead install MariaDB.

Installing mariadb will create the user mysql. When the database is initialized, two users will be added to the database: root and mysql. By default these users will only be accessible if you are logged in as the corresponding system user.

apk add mysql mysql-client

Installing the above packages will add the main components of MariaDB to the system: mariadb-cient and mariadb-server. Other available packages are described in the table below, and are listed in order of relevance for a production server.

MySQL name package Since Alpine: Brief usage Related package
mysql v2 a transitional package that installs mariadb mariadb
mysql-client v2 a transitional package that installs the mariadb client 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 for mariadb 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 tool for physical online backups, no longer widely used .
mariadb-server-utils v3.8 server commands not widely used, in past was inside MariaDB package .
mariadb-dev v3.1 development files for MariaDB .
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 version of MariaDB in the Alpine repositories behave like the MySQL tarball. No graphical tools are included.

The datadir located at /var/lib/mysql must be owned by the mysql user and group. The location of the datadir can be changed by editing the mariadb service file in /etc/init.d. The new location will also need to be set by adding datadir=<YOUR_DATADIR> in the [mysqld] section in a mariadb configuration file.

Normal initialization of mariadb can be done as follows:

  1. Initialize the main mysql database, and the data dir as standardized to /var/lib/mysql by running rc-service mariadb setup
  2. Start the main service. At this point there will be no root password set. rc-service mariadb start
  3. Secure the database by running mysql_secure_installation
  4. Setup permissions for managing others users and databases

Configuration

In order to help with the basic configuration of the database engine, MariaDB provides mysql_secure_installation. This script walks you through the basics of securing the database. The options are explained below.

  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 not, just press enter.
  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. For non-production servers just press "n" to setup a root password, which will give you the response ... skipping.
  3. Change the root password? [Y/n] Here you can change the root password, or set one if needed. Press "Y" and enter the new password.
  4. Remove anonymous users? [Y/n] Remove anonymous users created to log in using socket authentication. Unless you're sure you need this, answer "Y" to remove them.
  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. Answer "Y".
  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, answer "Y".
  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".

After the script exits, restart the service with rc-service mariadb restart

To start the database daemon on every boot, run rc-update add mariadb default

Configuration files and customization

Rather than being stored in my.cnf, configuration settings for MariaDB are now organized in separate files. The primary configuration is done by adding files to /etc/my.cnf.d/. User-specific configuration files are stored in ~/.my.cnf. User-specific configuration files are loaded after the system-wide configuration. The locations of the various configuration files are listed below.

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

As previously mentioned, this page describes basic usage of MariaDB. For professional usage, MySQL should also be referenced.

  • The following command will configure the server to accept all incoming connections. This should only be done for development, or if the database is not exposed to the Internet or a sensitive network.

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

  • For simple installations, disabling hostname search can improve performance, but is only useful for local 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

Updating or coming from upgrading

When upgrading between Alpine Linux releases, MariaDB may also have a major version change, and the databases should be upgraded to match. The recommended steps in this process are detailed below.

  1. While it may no longer be strictly necessary, it's useful to backup your databases before upgrading the database version.
  2. Update Alpine Linux and the MariaDB/MySQL packages.
  3. Install mariadb-server-utils by running apk add mariadb-server-utils.
  4. Run mysql_upgrade -u root -p script, and provide the password for the root database user.
  5. Restart the service by running rc-service mariadb restart.

If mysql_upgrade fails because MySQL cannot start, try running MySQL in safemode with mysqld_safe --datadir=/var/lib/mysql/, and then run mysql_upgrade -u root -p again.

See Also

MySQL is the most popular database manager in free software for two simple reasons (which are not the best technical reasons):

  1. It's simple and very easy to use
  2. It is very similar to SQLSERVER and is also used in other systems

In the world of Linux Alpine there is a software that provides it, it is the MariaDB, and in the wiki there are two approaches for its use, the professional one (for servers and deploys) and the fast and simple usage (for developers and/or enthusiasts):

Template:TOC

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 MySQL that is the same as MariaDB but with several modifications focused on production and security!

Installation

The Alpine Linux repositories no longer include the actual MySQL binaries, installing the mysql-* packages will instead install MariaDB.

Installing mariadb will create the user mysql. When the database is initialized, two users will be added to the database: root and mysql. By default these users will only be accessible if you are logged in as the corresponding system user.

apk add mysql mysql-client

Installing the above packages will add the main components of MariaDB to the system: mariadb-cient and mariadb-server. Other available packages are described in the table below, and are listed in order of relevance for a production server.

MySQL name package Since Alpine: Brief usage Related package
mysql v2 a transitional package that installs mariadb mariadb
mysql-client v2 a transitional package that installs the mariadb client 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 for mariadb 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 tool for physical online backups, no longer widely used .
mariadb-server-utils v3.8 server commands not widely used, in past was inside MariaDB package .
mariadb-dev v3.1 development files for MariaDB .
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 version of MariaDB in the Alpine repositories behave like the MySQL tarball. No graphical tools are included.

The datadir located at /var/lib/mysql must be owned by the mysql user and group. The location of the datadir can be changed by editing the mariadb service file in /etc/init.d. The new location will also need to be set by adding datadir=<YOUR_DATADIR> in the [mysqld] section in a mariadb configuration file.

Normal initialization of mariadb can be done as follows:

  1. Initialize the main mysql database, and the data dir as standardized to /var/lib/mysql by running rc-service mariadb setup
  2. Start the main service. At this point there will be no root password set. rc-service mariadb start
  3. Secure the database by running mysql_secure_installation
  4. Setup permissions for managing others users and databases

Configuration

In order to help with the basic configuration of the database engine, MariaDB provides mysql_secure_installation. This script walks you through the basics of securing the database. The options are explained below.

  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 not, just press enter.
  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. For non-production servers just press "n" to setup a root password, which will give you the response ... skipping.
  3. Change the root password? [Y/n] Here you can change the root password, or set one if needed. Press "Y" and enter the new password.
  4. Remove anonymous users? [Y/n] Remove anonymous users created to log in using socket authentication. Unless you're sure you need this, answer "Y" to remove them.
  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. Answer "Y".
  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, answer "Y".
  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".

After the script exits, restart the service with rc-service mariadb restart

To start the database daemon on every boot, run rc-update add mariadb default

Configuration files and customization

Rather than being stored in my.cnf, configuration settings for MariaDB are now organized in separate files. The primary configuration is done by adding files to /etc/my.cnf.d/. User-specific configuration files are stored in ~/.my.cnf. User-specific configuration files are loaded after the system-wide configuration. The locations of the various configuration files are listed below.

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

As previously mentioned, this page describes basic usage of MariaDB. For professional usage, MySQL should also be referenced.

  • The following command will configure the server to accept all incoming connections. This should only be done for development, or if the database is not exposed to the Internet or a sensitive network.

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

  • For simple installations, disabling hostname search can improve performance, but is only useful for local 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

Updating or coming from upgrading

When upgrading between Alpine Linux releases, MariaDB may also have a major version change, and the databases should be upgraded to match. The recommended steps in this process are detailed below.

  1. While it may no longer be strictly necessary, it's useful to backup your databases before upgrading the database version.
  2. Update Alpine Linux and the MariaDB/MySQL packages.
  3. Install mariadb-server-utils by running apk add mariadb-server-utils.
  4. Run mysql_upgrade -u root -p script, and provide the password for the root database user.
  5. Restart the service by running rc-service mariadb restart.

If mysql_upgrade fails because MySQL cannot start, try running MySQL in safemode with mysqld_safe --datadir=/var/lib/mysql/, and then run mysql_upgrade -u root -p again.

See Also

Template loop detected: Production DataBases : mysql

Retrieved from "https://wiki.alpinelinux.org/w/index.php?title=MySQL&oldid=17976"