Installing & Configuring MySQL on Ubuntu

Installing MySQL on Ubuntu using the command line is the same as on Ubuntu server.

The short answer is:

Additionally, you can run the secure installation script:

Let’s take a deeper look how to install MySQL on Ubuntu, how to check MySQL version, how to access database as admin and how to remove it completely.

How To Install MySQL on Ubuntu Using Terminal

First of all, we need to update package list to be sure, that we’re going to install the newest stable MySQL version.

The second step is to install MySQL server via the command line:

The system will ask you to prompt root password during installation:

install MySQL on Ubuntu

This will install the following packages:

As you can see you shouldn’t install mysql-client separately.

If you need to install MySQL client for some reason you can execute:

So how to use MySQL in Ubuntu?

You can connect to terminal interface using MySQL client:

And you’ll see command line MySQL interface:

User root and password root is default values.

It’s not secure to keep it as it is.

So we need to run MySQL secure installation script.

It’s a part of MySQL configuration process on Ubuntu.

A script will do the following:

  • setup VALIDATE PASSWORD plugin
  • remove anonymous users
  • disallow root login remotely
  • remove test database and access to it

You should approve each step, so don’t care you can choose what you need.

One more way to check that MySQL server is running:

Output:

MySQL configuration file is located at /etc/mysql/my.cnf on Ubuntu.

Default my.cnf :

It doesn’t include all configuration settings from /etc/mysql/conf.d/ and /etc/mysql/mysql.conf.d/ directories.

Most of the settings are in /etc/mysql/mysql.conf.d/mysqld.cnf.

Default mysqld.cnf:

[mysqld]

# # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp lc-messages-dir = /usr/share/mysql skip-external-locking # # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. bind-address = 127.0.0.1 # # * Fine Tuning # key_buffer_size = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam-recover-options = BACKUP #max_connections = 100 #table_cache = 64 #thread_concurrency = 10 # # * Query Cache Configuration # query_cache_limit = 1M query_cache_size = 16M # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. # As of 5.1 you can enable the log at runtime! #general_log_file = /var/log/mysql/mysql.log #general_log = 1 # # Error log – should be very few entries. # log_error = /var/log/mysql/error.log # # Here you can see queries with especially long duration #log_slow_queries = /var/log/mysql/mysql-slow.log #long_query_time = 2 #log-queries-not-using-indexes # # The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. #server-id = 1 #log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M #binlog_do_db = include_database_name #binlog_ignore_db = include_database_name # # * InnoDB # # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. # Read the manual for more InnoDB related options. There are many! # # * Security Features # # Read the manual, too, if you want chroot! # chroot = /var/lib/mysql/ # # For generating SSL certificates I recommend the OpenSSL GUI “tinyca”. # # ssl-ca=/etc/mysql/cacert.pem # ssl-cert=/etc/mysql/server-cert.pem # ssl-key=/etc/mysql/server-key.pem

As you can see you can configure the connection, number of threads, query cache etc.

You can copy your MySQL configuration to ~/.my.cnf to set user-specific options.

The configuration files must end with “.cnf” otherwise, they’ll be ignored.

How To Start, Stop and Restart MySQL on Ubuntu

Using service command you can stop the MySQL process:

Start MySQL process:

Or restart it as well:

How Do I Know MySQL Version?

There are at least 2 ways how to check which version of MySQL on Ubuntu am I running.

The simplest one is:

Output:

Another option is to use a mysqladmin tool:

It provides extended information about Ubuntu version, MySQL version, connection, protocol.

How To Uninstall MySQL on Ubuntu

If you don’t need MySQL anymore on your machine you can ask me:

“How do I remove MySQL?”.

Easy. You should execute remove command and mention all installed MySQL packages:

Or you can use a wildcard:

Remove configuration folders:

Clean up unused dependencies:

How To Install MySQL Workbench

MySQL Workbench is a graphical user interface for MySQL database.

Execute the following terminal command to install MySQL Workbench:

Now it’s installed and icon available in your application list.

So click it or run through the command line:

mysql workbench

To configure database connection you need to open menu DatabaseConnect to Database.

Select “locale instance 3306” in Stored Connection.

Set hostname (localhost by default), port (3306 by default), username and password and press “OK”.

mysql workbench connect to database

Now you’re connected to your local MySQL server.

Let’s try to execute simple SQL query.

For example, I want to show available databases:

mysql workbench execute query

As you can see MySQL Workbench found 4 rows.

So it’s working fine and you can use it.

If you have any question about installing, removing, configuring MySQL database or about MySQL workbench feel free to ask me in comments.

Tags:
Scroll Up