To use MySQL DB on a linux system, we need the MySQL server, client, and optionally, a UI client. For the UI Client, we will use DBeaver, which is a free universal DB client supporting MySQL, SQLite, Postgres, Oracle, and many more. We will create the DB using command line MySQL client, then connect to it using the UI client.
To setup MySQL GA (General Availability version) on Linux (Ubuntu and derivatives), below are the simple steps. Currently, the GA version is 5.6.
1. Download and install the .deb that adds MySQL repository to APT sources:
http://dev.mysql.com/downloads/repo/apt/
Download the .deb that corresponds to the version of Ubuntu. While installing the .deb, select the latest versions for all MySQL components (like Server, Workbench, Utilities), then select Apply from the same component list to end the selection.
2. Update the repository
$ sudo apt-get update
3. Install MySQL server and client
$ sudo apt-get install mysql-server mysql-client-core-5.6
Add root password when prompted during the installation. Also, MySQL server is automatically started at the end of installation. To stop it,
$ sudo service mysql stop
To start/ restart,
$ sudo service mysql start
To check whether started,
$ sudo service mysql status
4. Open MySQL client to create a database. We’ll later connect to this DB from DBeaver.
$ mysql -u root -p
Enter root password when prompted (the same one specified in the previous step).
5. Create the DB. In this example, we create the admissions DB that is used in Stanford’s Databases course.
mysql> create database admissions
Note that the database name is case sensitive in Unix.
6. Quits the MySQL prompt
mysql> \q
7. Install DBeaver as MySQL UI-based client
http://dbeaver.jkiss.org/download/
Download the 64-bit archive (assuming 64-bit OS), not the .deb file, because the .deb requires JRE 7 in PATH, whereas there may be a more recent version of Java in the system (say Java 8). Extract the archive, then add to PATH the base directory (which contains the dbeaver executable).
8. Open DBeaver
$ dbeaver
9. In DBeaver UI, create new MySQL connection with all defaults, user = root, and pwd = root pwd (for MySQL given in step 3). Put database name as the one created in step 5.
10. Connect to the DB, then run any scripts to create schema and data using the SQL Editor inside DBeaver (right-click on connection -> SQL Editor)
Now we are ready to run any queries against this database.
Hello, new world! 