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
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