MySQL

From openSUSE

This article is in need of attention! This article currently does not meet the standard expected on the openSUSE Wiki. You are welcome to help improve this article. For more information see the Wiki Team.

"World's most popular open source database." is MySQL's current claim to fame. It famously hosts some of the biggest databases in the world and is renowned for high performance. It is commonly used as a back-end for dynamic websites and constitute the M the LAMP stack (Linux Apache MySQL PHP).

Installing MySQL

Due the fact that Novell offers the MySQL server and client in their installation repository and that the Build service has now a repository for the newest versions, manual installation is skipped.

MySQL server from the default openSUSE repository

Simply install the MySQL server by selecting the package 'mysql - A True Multiuser, Multithreaded SQL Database Server' in YaST or another package manager. OpenSUSE will now install the MySQL server that is being shipped with the installed openSUSE version. From this point the MySQL server is only installed, but not configured or running. The next step is to make the MySQL server run. See

Go to YaST -> System -> System Services (Runlevel). Wait a few seconds before YaST has examined all the Runlevels. Then select mysqld and press the enable button. YaST will give a warning that a password for MySQL root user (administrator) must be set.

First Start of MySQL in openSUSE

Run :

mysql_secure_installation 

Without this MySQL will deny any access to mysqld (server).
Above script will:

• set a MySQL root password
• remove anonymous users
• disallow MySQL root login remotely
• remove test databases
• reload privileges table

First, make sure MySQL is running.

rcmysql status

If not, type

rcmysql start

And then type

mysql_secure_installation

Now answer questions, and after that you can start using MySQL :-)

From this point openSUSE has a properly configured MySQL server.

Edit the MySQL configuration

The configuration file of MySQL is located at:

/etc/my.cnf

You can edit it as root with your favourite editor (e.g. Kate). When this file is changed, you need to restart MySQL to pick up the changes:

rcmysql restart

MySQL clients

Beside a server, a client is needed to make connection to the MySQL server. OpenSUSE offers several clients from its official repository. The most famous is the shell client from MySQL itself, but also the graphical MySQL query browser from MySQL is offered. A note should be made that it is not necessary to install a client to configure or run the MySQL server. Same way that a MySQL server is not needed for a client. This page will describe the clients offered within the official openSUSE installation repository only.

Basic shell client

This is the regular MySQL client developed by MySQL itself. It runs from a shell like Konsole and has no graphical user interface. Ideal for small adjustments within the MySQL server. This client is called mysql-client in YaST.

MySQL GUI Tools

MySQL developed three MySQL clients which are available in the official SUSE installation repository.

• MySQL Query Browser is fully graphical client to maintain the tables within the MySQL server. It is primarily developed for data management, not for controlling the server. To do that, you need MySQL Admnistrator.
• MySQL Administrator is a powerful tool to check the status of the MySQL server with. Users, health-status, usage, and more can be monitored with the MySQL Administrator. This tool also has an advanced user management en database controller.

phpMyAdmin

PhpMyAdmin is not an application but a website. You need a running webserver with PHP installed to run the phpMyAdmin website. PhpMyAdmin is ideal for webhosters or maintaining the MySQL server over the Internet. If you are using the MySQL server on the localhost or within a local network, it is better to use a graphic client like MySQL Query Browser then to setup a webserver with PHP to run PHPMyAdmin in. This saves the need to install and maintain a webserver and is above all faster, even within a local network, than phpMyAdmin.

Backing up a MySQL database

To back up a MySQL database using mysqldump, connect to the server and type

mysqldump --add-drop-table -h localhost -u username --password=passwd database > database.sql

Do not forget to replace username, passwd, database with your values.

Restoring a MySQL database

mysql -u username -p database < database.sql

openSUSE repositories for the newest MySQL versions

server:/database

See also

• MySQL-5.1 for the latest news on MySQL in openSUSE
• MySQL Documentation
• Official webpage of MySQL

Tools

• MySQL GUI Tools
• phpMyAdmin

Related

• Official webpage of PostgreSQL
• phpPgAdmin