MySQL is a popular relational database management system (RDBMS) that uses structured query language (SQL) for processing the data in the database. It is often used as part of a LAMP stack to accommodate a web application such as a CMS, in which case you’ll want to have Apache or another web server already installed and tested.
Use your distribution’s package manager to install MySQL. I also install MySQL Workbench to help with initial setup and testing. Distributions have different names for the appropriate packages; examples include:
- Mandriva 2010.2: mysql
- Ubuntu 12.10: mysql-server, mysql-workbench
Pay attention to any instructions or advisories the installer may give you. MySQL needs to be configured and secured for use, and packages differ on how much configuration is done automatically and how much is left to you. For example, Mageia 2 asks you to start MySQL and run a security script as root.
Insure that the MySQL service is configured to start on boot and is running. Different distributions have different names for this service. For example, Mageia 2 calls it mysqld, while Ubuntu 12.10 calls it mysql.
Run MySQL Workbench. In a modern desktop environment it should have created a user menu entry under Development, or run “mysql-workbench” from a terminal as user. Click on New Server Instance. The Create New Server Instance Profile wizard will open; follow the instructions. You will be asked for the server hostname (usually “localhost”) and MySQL username (for now, “root”). Default schema may be left blank. Password may be, depending on your distribution, either your root password or whatever you entered in mysql_secure_installation. If all goes well, the wizard closes and you are returned to MySQL Workbench’s control panel.
Next, create a new user. Click on Manage Security; a new tab is opened. Click on the Add Account button in the bottom center. In the Login tab, provide a username and password. In the Administrative Roles tab, indicate the minimum privileges needed by the user. When finished, press “Apply”. For production use, be secure and do not use the root user or grant your production user permissions beyond those needed.
TODO: In a production environment, each user should only have the privileges strictly needed. Investigate what is needed and what isn’t. I am now testing a user with the permissions granted by the DBManager role.
When finished, close MySQL Workbench.
If you are migrating or restoring a database, see these instructions.
If you installed MySQL as part of a LAMP server, your next step is to install PHP. Once PHP has been installed, you might prefer to install phpMyAdmin or Adminer to administer MySQL.
These notes were last updated 27 March 2013 while using MySQL 5.5.29 on Ubuntu 12.10.