Installing MySQL on Linux

Installing MySQL on Linux

MySQL (5.7) on CentOS 7

Installing the requirements

To install MySQL on your Linux CentOS 7 machine we have to install some packages to ensure you can install MySQL. Use the following commands:

yum -y install epel-release
yum -y upgrade 
reboot

Because MySQL is not the standard database of CentOS anymore, we have to install the MySQL-yum-repository. In this example we will setup the MySQL repository to use the MySQL 5.7 version. Analyze will not work with MySQL version 8.Execute the following commands:

rpm -Uhv https://repo.mysql.com/mysql80-community-release-el7-3.noarch.rpm
yum-config-manager --disable mysql80-community
yum-config-manager --enable mysql57-community

Installing MySQL

yum install mysql-community-server

After installing the MySQL database you have to create a database schema for Analyze and secure the newly installed MySQL installation. It is also wise to add a separate user and password for the Analyze database.

Before we start securing the database, we lower the password difficulty for the mysql system. You can do this, but you do not have to.

Open you /etc/my.cnf and add the following to your configuration file:

validate_password_policy=LOW
sql_mode=""
max_allowed_packet=16M

To secure your installation, first start your database server.

systemctl start mysqld

Check the MySQL log file if everything is started and there are no errors. In this log file you can also find the initial password for the MySQL environment. This password we need for securing the installation.

Now open the file /var/log/mysql.log.

2019-10-24T12:01:41.957110Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-10-24T12:01:44.545204Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-10-24T12:01:44.752286Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-10-24T12:01:45.026103Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 0c9594f1-f656-11e9-95e5-080027ae565a.
2019-10-24T12:01:45.056644Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-10-24T12:01:46.912897Z 0 [Warning] CA certificate ca.pem is self signed.
2019-10-24T12:01:47.408415Z 1 [Note] A temporary password is generated for root@localhost: =u6N4jEjY9Vt
2019-10-24T12:01:51.283723Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-10-24T12:01:51.286277Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.28) starting as process 441 ...
2019-10-24T12:01:51.343465Z 0 [Note] InnoDB: PUNCH HOLE support available
2019-10-24T12:01:51.343499Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-10-24T12:01:51.343509Z 0 [Note] InnoDB: Uses event mutexes
2019-10-24T12:01:51.343519Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2019-10-24T12:01:51.343528Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-10-24T12:01:51.343537Z 0 [Note] InnoDB: Using Linux native AIO
2019-10-24T12:01:51.344003Z 0 [Note] InnoDB: Number of pools: 1
2019-10-24T12:01:51.344143Z 0 [Note] InnoDB: Using CPU crc32 instructions
2019-10-24T12:01:51.346001Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2019-10-24T12:01:51.368538Z 0 [Note] InnoDB: Completed initialization of buffer pool
2019-10-24T12:01:51.370799Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2019-10-24T12:01:51.384459Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2019-10-24T12:01:51.503465Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-10-24T12:01:51.503539Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-10-24T12:01:51.567686Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2019-10-24T12:01:51.570307Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2019-10-24T12:01:51.570327Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2019-10-24T12:01:51.570573Z 0 [Note] InnoDB: Waiting for purge to start
2019-10-24T12:01:51.621526Z 0 [Note] InnoDB: 5.7.28 started; log sequence number 2629051
2019-10-24T12:01:51.621948Z 0 [Note] Plugin 'FEDERATED' is disabled.
2019-10-24T12:01:51.626949Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2019-10-24T12:01:51.628756Z 0 [Note] InnoDB: Buffer pool(s) load completed at 191024  8:01:51
2019-10-24T12:01:51.676988Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2019-10-24T12:01:51.677044Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2019-10-24T12:01:51.678862Z 0 [Warning] CA certificate ca.pem is self signed.
2019-10-24T12:01:51.678920Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2019-10-24T12:01:51.690157Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2019-10-24T12:01:51.690201Z 0 [Note] IPv6 is available.
2019-10-24T12:01:51.690209Z 0 [Note]   - '::' resolves to '::';
2019-10-24T12:01:51.690224Z 0 [Note] Server socket created on IP: '::'.
2019-10-24T12:01:51.704301Z 0 [Note] Event Scheduler: Loaded 0 events
2019-10-24T12:01:51.704440Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.28'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)

All seems to be ok. Now for securing the environment we need the initial password. As you look through the log, you find it, somewhat, at the top of the log:

A temporary password is generated for root@localhost: =u6N4jEjY9Vt

If your database server has started correctly, you can secure your installation.

# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root:
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 50
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y

New password:

Re-enter new password:

Estimated strength of the password: 50
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
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.

Your database can now be accessed only via a localhost connection (or 127.0.0.1 as a binding address) via the root user.

Now create a database for Analyze, use the following commands to do so:

# mysql -u root -p
Enter password: <Enter your password here>
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, 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> create database `analyzedb`;
Query OK, 1 row affected (0.00 sec)

mysql> ^D
Bye

Now it is time to create an user for the analayzedb database. This user will have full privileges because the actual tables are maintained via Hibernate.

As per version 5.7 of MySQL tool mysql_setpermission is no longer on our systems. You have to create the user for you analyzedb database yourself and give it the correct priviliges. You can use MySQL Workbench for it or you can use some SQL-statements to accomplish this.

To start MySQL after a reboot we have to perform two commands, MySQL will be added to the auto start sequence of the Linux machine.

systemctl enable mysqld