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-serverAdditionally, you can run the secure installation script:
mysql_secure_installationLet’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 updateThe second step is to install MySQL server via the command line:
sudo apt-get install mysql-serverThe 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.7As you can see you shouldn’t install mysql-client separately.
If you need to install MySQL client for some reason you can execute:
sudo apt-get install mysql-clientSo how to use MySQL in Ubuntu?
You can connect to terminal interface using MySQL client:
mysql -h localhost -u root -prootAnd 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>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.
mysql_secure_installationA 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 statusOutput:
● 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[1]: Starting MySQL Community Server...
dec 14 11:29:45 dshvechikov-N552VX systemd[1]: Started MySQL Community Server.MySQL configuration file is located at /etc/mysql/my.cnf on Ubuntu.
Default my.cnf :
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/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_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.pemAs 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:
service mysql stopStart MySQL process:
service mysql startOr restart it as well:
service mysql restartHow 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 --versionOutput:
mysql Ver 14.14 Distrib 5.7.20, for Linux (x86_64) using EditLine wrapperAnother option is to use a mysqladmin tool:
mysqladmin -p -u root versionIt 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.002How 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.7Or you can use a wildcard:
sudo apt-get remove --purge mysql-*Remove configuration folders:
sudo rm -rf /etc/mysql /var/lib/mysqlClean up unused dependencies:
sudo apt-get autoremove
sudo apt-get autocleanHow 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-workbenchNow 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 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:
show 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.