Installing MySQL on Ubuntu using the command line is the same as on Ubuntu server.
The short answer is:
sudo apt-get update sudo apt-get install mysql-server
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.
sudo apt-get update
The second step is to install MySQL server via the command line:
sudo apt-get install mysql-server
The system will ask you to prompt root password during installation:
This will install the following packages:
mysql-client-5.7 mysql-client-core-5.7 mysql-common mysql-server mysql-server-5.7 mysql-server-core-5.7
As you can see you shouldn’t install
If you need to install MySQL client for some reason you can execute:
sudo apt-get install mysql-client
So how to use MySQL in Ubuntu?
You can connect to terminal interface using MySQL client:
mysql -h localhost -u root -proot
And you’ll see command line MySQL interface:
mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.7.20-0ubuntu0.16.04.1 (Ubuntu) Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
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:
service mysql status
● mysql.service - MySQL Community Server Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled) Active: active (running) since tu 2017-12-14 11:29:45 EET; 12min ago Main PID: 26604 (mysqld) Tasks: 28 Memory: 146.6M CPU: 839ms CGroup: /system.slice/mysql.service └─26604 /usr/sbin/mysqld dec 14 11:29:44 dshvechikov-N552VX systemd: Starting MySQL Community Server... dec 14 11:29:45 dshvechikov-N552VX systemd: Started MySQL Community Server.
MySQL configuration file is located at
/etc/mysql/my.cnf on Ubuntu.
!includedir /etc/mysql/conf.d/ !includedir /etc/mysql/mysql.conf.d/
It doesn’t include all configuration settings from
Most of the settings are in
[mysqld_safe] socket= /var/run/mysqld/mysqld.sock nice= 0 [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
service command you can stop the MySQL process:
service mysql stop
Start MySQL process:
service mysql start
Or restart it as well:
service mysql restart
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:
mysql Ver 14.14 Distrib 5.7.20, for Linux (x86_64) using EditLine wrapper
Another option is to use a
mysqladmin -p -u root version
It provides extended information about Ubuntu version, MySQL version, connection, protocol.
mysqladmin Ver 8.42 Distrib 5.7.20, for Linux on x86_64 Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Server version5.7.20-0ubuntu0.16.04.1 Protocol version10 ConnectionLocalhost via UNIX socket UNIX socket/var/run/mysqld/mysqld.sock Uptime:21 min 37 sec Threads: 1 Questions: 3 Slow queries: 0 Opens: 107 Flush tables: 1 Open tables: 26 Queries per second avg: 0.002
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:
sudo apt-get remove --purge mysql-server mysql-client mysql-common mysql-client-core-5.7 mysql-server-core-5.7
Or you can use a wildcard:
sudo apt-get remove --purge mysql-*
Remove configuration folders:
sudo rm -rf /etc/mysql /var/lib/mysql
Clean up unused dependencies:
sudo apt-get autoremove sudo apt-get autoclean
How To Install MySQL Workbench
MySQL Workbench is a graphical user interface for MySQL database.
Execute the following terminal command to install MySQL Workbench:
sudo apt-get install mysql-workbench
Now it’s installed and icon available in your application list.
So click it or run through the command line:
To configure database connection you need to open menu Database – Connect to Database.
Select “locale instance 3306” in Stored Connection.
Set hostname (localhost by default), port (3306 by default), username and password and press “OK”.
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:
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.