MySQL is a widely spread, multi-threaded, multi-user SQL database. For more information about features, see the [http://www.mysql.com/ official homepage].

MySQL is a widely spread, multi-threaded, multi-user SQL database. For more information about features, see the [http://www.mysql.com/ official homepage].

+

+

{{Note|MariaDB is now officially Arch Linux default implementation of MySQL. It is recommended for all users to [[#Upgrade from Oracle MySQL to MariaDB|upgrade]] to MariaDB. Oracle MySQL was dropped to the AUR. See [https://www.archlinux.org/news/mariadb-replaces-mysql-in-repositories/ the announcement].}}

+

== Installation ==

== Installation ==

−

[[pacman|Install]] the {{Pkg|mysql}} package from the [[Official Repositories|official repositories]].

−

After installing MySQL, [[Daemons#Starting_manually|start]] the mysqld daemon and run the setup script:

+

The MySQL implementation chosen by Arch Linux is called [https://mariadb.org/ MariaDB].

+

[[pacman|Install]] {{Pkg|mariadb}} from the [[official repositories]].

{{Tip|If the database (in {{ic|/var/lib/mysql}}) resides in a [[btrfs]] filesystem you should consider disabling [[Btrfs#Copy-On-Write_.28CoW.29|Copy-on-Write]] for the directory before creating any database:

+

{{ic|# chattr +C /var/lib/mysql}}

+

}}

+

+

Start the {{ic|mysqld}} [[daemon]], run the setup script:

# mysql_secure_installation

# mysql_secure_installation

+

and restart the daemon afterwards.

−

Then [[Daemons#Restarting|restart]] MySQL (mysqld). If you want to start MySQL at boot time, see [[Daemons#Starting_on_boot]].

+

Frontends available are {{AUR|mysql-gui-tools}} and {{AUR|mysql-workbench}}.

{{Note|It could be needed to remove the following files from {{ic|/var/lib/mysql}} : {{ic|ib_logfile0}}, {{ic|ib_logfile1}} and {{ic|aria_log_control}} before restarting the daemon in the following procedure.}}

+

+

Users who want to switch will need to stop their current {{ic|mysqld}} daemon, install ''mariadb'', ''libmariadbclient'' or ''mariadb-clients'', restart {{ic|mysqld}}and execute:

+

# mysql_upgrade -p

+

in order to migrate their systems.

+

+

=== On update ===

+

+

You might consider running this command after you have upgraded MySQL and started it:

+

# mysql_upgrade -u root -p

== Configuration ==

== Configuration ==

+

Once you have started the MySQL server, you probably want to add a root account in order to maintain your MySQL users and databases. This can be done manually or automatically, as mentioned by the output of the above script. Either run the commands to set a password for the root account, or run the secure installation script.

Once you have started the MySQL server, you probably want to add a root account in order to maintain your MySQL users and databases. This can be done manually or automatically, as mentioned by the output of the above script. Either run the commands to set a password for the root account, or run the secure installation script.

Line 24:

Line 54:

$ mysql -p -u root

$ mysql -p -u root

−

=== Enable remote access ===

+

=== Disable remote access ===

−

The MySQL server is not accessable from the network by default. To enable listing on TCP port 3306 to allow remote connections, comment out the following line in {{ic|/etc/mysql/my.cnf}}:

+

+

The MySQL server is accessible from the network by default. If MySQL is only needed for the localhost, you can improve security by not listening on TCP port 3306. To refuse remote connections, uncomment the following line in {{ic|/etc/mysql/my.cnf}}:

skip-networking

skip-networking

+

+

You will still be able to log in from the localhost.

=== Enable auto-completion ===

=== Enable auto-completion ===

−

The MySQL client completion feature is disabled by default. To enable it system-wide edit {{ic|/etc/mysql/my.cnf}}, and replace {{ic|no-auto-rehash}} by {{ic|auto-rehash}}. Completion will be enabled next time you run the MySQL client. Please note that enabling this feature can make the client initialization longer.

−

== Upgrading ==

+

{{Note|Enabling this feature can make the client initialization longer.}}

−

You might consider running this command after you have upgraded MySQL and started it:

+

The MySQL client completion feature is disabled by default. To enable it system-wide edit {{ic|/etc/mysql/my.cnf}}, and replace {{ic|no-auto-rehash}} by {{ic|auto-rehash}}. Completion will be enabled next time you run the MySQL client.

−

# mysql_upgrade -u root -p

+

+

=== Using UTF-8 ===

+

+

In the {{ic|/etc/mysql/my.cnf}} file section under the {{ic|mysqld}} group, add:

Add to your {{ic|/etc/mysql/my.cnf}} file under the {{ic|mysqld}} group:

+

tmpdir = /var/lib/mysqltmp

+

+

Then reboot or ( shutdown mysql, mount the tmpdir, start mysql ).

+

+

== Backup ==

+

+

The database can be dumped to a file for easy backup. The following shell script will do this for you, creating a {{ic|db_backup.gz}} file in the same directory as the script, containing your database dump:

See also the official {{ic|mysqldump}} [http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html page] in the MySQL manual.

== Troubleshooting ==

== Troubleshooting ==

+

=== MySQL daemon cannot start ===

=== MySQL daemon cannot start ===

−

If you see something like this:

+

−

:: Starting MySQL [FAIL]

+

If MySQL fails to start and there is no entry in the log files, you might want to check the permissions of files in the directories {{ic|/var/lib/mysql}} and {{ic|/var/lib/mysql/mysql}}. If the owner of files in these directories is not {{ic|mysql:mysql}}, you should do the following:

−

and there is no entry in the log files, you might want to check the permissions of files in the directories {{ic|/var/lib/mysql}} and {{ic|/var/lib/mysql/mysql}}. If the owner of files in these directories is not {{ic|mysql:mysql}}, you should do the following:

+

# chown mysql:mysql /var/lib/mysql -R

−

# chown mysql:mysql /var/lib/mysql -R

+

If you run into permission problems despite having followed the above, ensure that your {{ic|my.cnf}} is copied to {{ic|/etc/}}:

If you run into permission problems despite having followed the above, ensure that your {{ic|my.cnf}} is copied to {{ic|/etc/}}:

−

# cp /etc/mysql/my.cnf /etc/my.cnf

+

# cp /etc/mysql/my.cnf /etc/my.cnf

Now try and start the daemon.

Now try and start the daemon.

−

If you get these messages in your {{ic|/var/lib/mysql/hostname.err}}

+

If you get these messages in your {{ic|/var/lib/mysql/hostname.err}}:

−

[ERROR] Can't start server : Bind on unix socket: Permission denied

+

[ERROR] Can't start server : Bind on unix socket: Permission denied

−

[ERROR] Do you already have another mysqld server running on socket: /var/run/mysqld/mysqld.sock ?

+

[ERROR] Do you already have another mysqld server running on socket: /var/run/mysqld/mysqld.sock ?

Enable at startup

Upgrade from Oracle MySQL to MariaDB

Note: It could be needed to remove the following files from /var/lib/mysql : ib_logfile0, ib_logfile1 and aria_log_control before restarting the daemon in the following procedure.

Users who want to switch will need to stop their current mysqld daemon, install mariadb, libmariadbclient or mariadb-clients, restart mysqldand execute:

# mysql_upgrade -p

in order to migrate their systems.

On update

You might consider running this command after you have upgraded MySQL and started it:

# mysql_upgrade -u root -p

Configuration

Once you have started the MySQL server, you probably want to add a root account in order to maintain your MySQL users and databases. This can be done manually or automatically, as mentioned by the output of the above script. Either run the commands to set a password for the root account, or run the secure installation script.

You now should be able to do further configuration using your favorite interface. For example you can use MySQL's command line tool to log in as root into your MySQL server:

$ mysql -p -u root

Disable remote access

The MySQL server is accessible from the network by default. If MySQL is only needed for the localhost, you can improve security by not listening on TCP port 3306. To refuse remote connections, uncomment the following line in /etc/mysql/my.cnf:

skip-networking

You will still be able to log in from the localhost.

Enable auto-completion

Note: Enabling this feature can make the client initialization longer.

The MySQL client completion feature is disabled by default. To enable it system-wide edit /etc/mysql/my.cnf, and replace no-auto-rehash by auto-rehash. Completion will be enabled next time you run the MySQL client.

Troubleshooting

MySQL daemon cannot start

If MySQL fails to start and there is no entry in the log files, you might want to check the permissions of files in the directories /var/lib/mysql and /var/lib/mysql/mysql. If the owner of files in these directories is not mysql:mysql, you should do the following:

# chown mysql:mysql /var/lib/mysql -R

If you run into permission problems despite having followed the above, ensure that your my.cnf is copied to /etc/: