How to install and configure MySQL on Ubuntu 18.04 LTS - jbilander/HowTos GitHub Wiki
How to install and configure MySQL on Ubuntu 18.04 LTS
first become root:
jbilander@zeus:~$ sudo -s
[sudo] password for jbilander:
root@zeus:~#
root@zeus:~# apt update
install MySQL:
root@zeus:~# apt install mysql-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
libaio1 libcgi-fast-perl libcgi-pm-perl libencode-locale-perl libevent-core-2.1-6 libfcgi-perl libhtml-parser-perl libhtml-tagset-perl libhtml-template-perl libhttp-date-perl
libhttp-message-perl libio-html-perl liblwp-mediatypes-perl libtimedate-perl liburi-perl mysql-client-5.7 mysql-client-core-5.7 mysql-common mysql-server-5.7 mysql-server-core-5.7
Suggested packages:
libdata-dump-perl libipc-sharedcache-perl libwww-perl mailx tinyca
The following NEW packages will be installed:
libaio1 libcgi-fast-perl libcgi-pm-perl libencode-locale-perl libevent-core-2.1-6 libfcgi-perl libhtml-parser-perl libhtml-tagset-perl libhtml-template-perl libhttp-date-perl
libhttp-message-perl libio-html-perl liblwp-mediatypes-perl libtimedate-perl liburi-perl mysql-client-5.7 mysql-client-core-5.7 mysql-common mysql-server mysql-server-5.7
mysql-server-core-5.7
0 upgraded, 21 newly installed, 0 to remove and 0 not upgraded.
Need to get 21.0 MB of archives.
After this operation, 162 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
Secure the installation by running the mysql_secure_installation script:
root@zeus:~# mysql_secure_installation
Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?
Press y|Y for Yes, any other key for No:
Please set the password for root here.
New password:
Re-enter new password:
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
All done!
root@zeus:~#
Now, login to MySQL as root from the terminal:
root@zeus:~# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.24-0ubuntu0.18.04.1 (Ubuntu)
Copyright (c) 2000, 2018, 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>
Check variables settings:
mysql> show variables where variable_name like 'ch%' or variable_name like 'col%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| check_proxy_users | OFF |
| collation_connection | utf8_general_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+--------------------------+----------------------------+
12 rows in set (0.01 sec)
mysql>
We can see that some settings uses latin1 here. I want utf8 and utf8_unicode_ci on all places as default. Exit from mysql:
mysql> quit
Bye
root@zeus:~#
edit my.cnf with your favorite editor, I use vim:
root@zeus:~# vi /etc/mysql/my.cnf
Add these lines, save and exit:
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci; SET NAMES utf8;'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
Restart MySQL for the changes to take effect:
root@zeus:~# systemctl restart mysql
Now, login to MySQL as root from the terminal once again:
root@zeus:~# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.24-0ubuntu0.18.04.1 (Ubuntu)
Copyright (c) 2000, 2018, 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>
Check variables settings:
mysql> show variables where variable_name like 'ch%' or variable_name like 'col%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| check_proxy_users | OFF |
| collation_connection | utf8_unicode_ci |
| collation_database | utf8_unicode_ci |
| collation_server | utf8_unicode_ci |
+--------------------------+----------------------------+
12 rows in set (0.00 sec)
mysql>
Now, create a database to verify it is created with the correct collation and character set:
mysql> create database junk;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| junk |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> show create database junk;
+----------+---------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------------------------------+
| junk | CREATE DATABASE `junk` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */ |
+----------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Nice!, utf8 and utf8_unicode_ci is used as default now.
To add a user that can access this database from php, java etc, we can add it like this:
mysql> create user 'user123'@'localhost' identified with mysql_native_password by 'your_password_here';
Query OK, 0 rows affected (0.00 sec)
Of course replace user123 and your_password_here with your own preferred username/password.
Grant privileges to the junk database for this user:
mysql> grant all privileges on junk.* to 'user123'@'localhost' identified by 'your_password_here';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Flush privileges for the new config to take effect:
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
We can check that the new user is indeed added to the mysql user table:
mysql> use mysql;
Database changed
mysql> mysql> select host,user from user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| localhost | debian-sys-maint |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
| localhost | user123 |
+-----------+------------------+
5 rows in set (0.00 sec)
mysql>
Now quit and we will verify that we can login with the new user and access the junk database:
mysql> quit
Bye
root@zeus:~#
root@zeus:~# mysql -u user123 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.24-0ubuntu0.18.04.1 (Ubuntu)
Copyright (c) 2000, 2018, 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>
Change to the junk database:
mysql> use junk;
Database changed
mysql>
Let's try and create a table:
mysql> create table `people` (`Id` int(11) auto_increment not null, `Name` varchar(255) not null, primary key (`Id`));
Query OK, 0 rows affected (0.00 sec)
mysql> describe people;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| Id | int(11) | NO | PRI | NULL | auto_increment |
| Name | varchar(255) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> show create table people;
| Table | Create Table
| people | CREATE TABLE `people` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`Name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)
Nice, we can see that the table was created with utf8 and utf8_unicode_ci
Now, let's insert som data:
mysql> insert into people (Name) values ('Lisa');
Query OK, 1 row affected (0.00 sec)
mysql> insert into people (Name) values ('John');
Query OK, 1 row affected (0.00 sec)
mysql> select * from people;
+----+------+
| Id | Name |
+----+------+
| 1 | Lisa |
| 2 | John |
+----+------+
2 rows in set (0.00 sec)
mysql>
That was all for demoing, now I will drop this database and remove the user123, so let's quit and login with root to do this:
mysql> quit
Bye
root@zeus:~# mysql -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.24-0ubuntu0.18.04.1 (Ubuntu)
Copyright (c) 2000, 2018, 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>
Drop the user user123:
The DROP USER statement removes one or more MySQL accounts and their privileges. It removes privilege rows for the account from all grant tables.
mysql> drop user 'user123'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> use mysql;
Database changed
mysql> select host,user from user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| localhost | debian-sys-maint |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+------------------+
4 rows in set (0.00 sec)
The user user123 is deleted, now drop the database junk:
mysql> drop database junk;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
root@zeus:~#
That's all folks!
Of course you can tune MySQL with a lot of different settings to increase performance, but not shown here.
Extra:
If you want 5.7 to behave as older 5.5 when it comes to group by, disable the only_full_group_by setting. Add this line to your my.cnf file under [mysqld] and restart mysql:
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION