MySQL

From Gentoo Wiki
Jump to:navigation Jump to:search
This page contains changes which are not marked for translation.

MySQL is a popular, free software relational database management system. It is often used in conjunction with web applications (such as many PHP sites), but has gained many more enterprise-level features since its start in 1994.

An alternative fork and drop-in replacement is MariaDB.

Installation

USE flags

Before installing dev-db/mysql, carefully consider the USE flags that influence the package deployment and features. The following table gives an overview of the package's supported USE flags:

USE flags for dev-db/mysql A fast, multi-threaded, multi-user SQL database server

+perl Add optional support/bindings for the Perl language
+server Build the server program
cjk Add CJK support for InnoDB fulltext search using app-text/mecab
client-libs Build the client libraries from the server package instead of the C Connector packages (not recommended)
cracklib Support for cracklib strong password checking
debug Enable extra debug codepaths, like asserts and extra output. If you want to get meaningful backtraces see https://wiki.gentoo.org/wiki/Project:Quality_Assurance/Backtraces
experimental Build experimental features aka "rapid" plugins
jemalloc Use dev-libs/jemalloc for memory management
latin1 Use LATIN1 encoding instead of UTF8
numa Enable NUMA support using sys-process/numactl (NUMA kernel support is also required)
profiling Add support for statement profiling (requires USE=community).
router Build the MySQL router program
selinux !!internal use only!! Security Enhanced Linux support, this must be set by the selinux profile or breakage will occur
static !!do not set this during bootstrap!! Causes binaries to be statically linked instead of dynamically
static-libs Build static versions of dynamic libraries as well
systemtap Build support for profiling and tracing using dev-debug/systemtap
tcmalloc Use the dev-util/google-perftools libraries to replace the malloc() implementation with a possibly faster one
test Install upstream testsuites for end use.

Emerge

Once the proper USE flags have been set, install MySQL:

root #emerge --ask dev-db/mysql

Configuration

Files

Configuration files:

  • /etc/mysql/my.cnf
  • /etc/mysql/mysql.d/50-distro-server.cnf
  • /etc/mysql/mysql.d/50-distro-client.cnf

Command history:

  • /root/.my.cnf - Potentially stores the root database user's password.
  • ~/.mysql_history - Each user's SQL history. Note: Unless disabled, history is recorded here. This includes passwords in plain text.

Data directory:

  • /var/lib/mysql - MySQL's default data directory. This can be adjusted in the 50-distro-client.cnf file above. Databases will be stored here.

Service

OpenRC

To have the database(s) started automatically at boot, add the mysql init script to the default runlevel:

root #rc-update add mysql default

After configuring the database, which is mentioned later in this document, start the mysql service:

root #rc-service mysql start

Preliminary configuration

The dev-db/mysql package handles the preliminary setup of MySQL through the --config option:

root #emerge --config dev-db/mysql

This will create a database, set proper permissions on the database, and assist in creating a good root password (this is for the MySQL root account, which is not related to the Linux root account).

To purge anonymous users and test databases from the installation, run mysql_secure_installation after the preliminary setup:

root #mysql_secure_installation

In-database configuration

When the database is up and running, connect to it using the mysql client application.

user $mysql -u root -p -h localhost
Enter root password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.5.1
  
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
  
mysql>

From this point, a session to the MySQL instance is open, allowing for queries and administrative commands to be serviced.

Erasing command history

By default MySQL logs every action, including leaving plain text passwords in its history file.

To remove the history file:

root #rm /root/.mysql_history

Alternatively, history logging can be permanently disabled with the following:

root #ln -sf /dev/null /root/.mysql_history

Removal

Removing an old database

By default, the /var/lib/mysql directory is used as the SQL data directory; once databases have been created, they will be stored here. To remove old databases and start fresh, this directory can be renamed or removed so that a new database can be created.

root #mv /var/lib/mysql /var/lib/mysql.bak
root #mkdir /var/lib/mysql
root #chown mysql:mysql /var/lib/mysql

See also