How to Install and Configure Latest MySQL on CentOS

The Installing and Upgrading MySQL chapter of MySQL documentation provides thorough and detailed information for reference on how to install and initialize settings for MySQL server (hereinafter referred to as MySQL) on Linux systems. If you do not have time to read that and need to install and use MySQL in a limited time, this article is a quick guide for you to install and configure MySQL in best practices.

We will use MySQL Community Server 8.0.28 as an example to show how to download, install, initialize and start MySQL service on CentOS. The full process has been tested as working on CentOS 7, CentOS 8, and CentOS Stream 8.

A root user role is required to follow the commands in this article. If you are not the root user, please make sure to add “sudo” before each command, or switch to the root user by command below:

sudo -s

We will use yum command for any package related operation. Please note that on CenOS 8, CentOS Stream 8 and later versions of CentOS, yum command is a symbolic link to dnf binary and was replaced by dnf command.

1 Choose installation method

There are several ways to install MySQL on Linux, including but not limited to:

  • Use package manager (like YUM/DNF) to download and install automatically.
  • Install manually from generic binary distribution.
  • Install manually from source compilation.

All these ways have pros and cons. Using package manager is easy and fast, but all files will be messed up with system built-in programs which would carry a conflict risk, and it also cannot be customized for additional features. Compiling from source code supports customization for various functionalities, but you need to manage all the dependencies by yourself, and it also asks for server of high performance.

We will install MySQL from generic binary distribution as a compromised choice. You may find this solution from MySQL document “Installing MySQL on Unix/Linux Using Generic Binaries“. This way installation files will be isolated from system built-in software libraries, and we do not need to spend too much time to compile source code.

2 Download MySQL

We will download the minimal install distribution, which excludes debug binaries and is stripped of debug symbols, making it significantly smaller than the regular binary distribution.

We need to make sure two things before using this minimal version:

  1. glibc (GNU C Library) version must be equal to or higher than 2.17.
  2. The OS bitness is x86_64.

To check glibc version:

ldd --version

To check OS bitness:

uname -m

If all good, we will download the binary distribution from:

For “Select Operating System”, please select “Linux – Generic“. For “Select OS Version”, please select “Linux – Generic (glibc 2.17) (x86, 64-bit)“.

Choose “Compressed TAR Archive, Minimal Install” to click download, and copy the link from “No thanks, just start my download.” in the coming page.

First let us change the directory to system local source folder, then we will download the package by curl command using the link we copied:

cd /usr/local/src
curl -LO https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.28-linux-glibc2.17-x86_64-minimal.tar.xz

3 Install MySQL

First we will unpack the package by tar command, and then move it to program files directory with folder name renamed to “mysql” (/usr/local/mysql):

tar -xvf mysql-8.0.28-linux-glibc2.17-x86_64-minimal.tar.xz
mv mysql-8.0.28-linux-glibc2.17-x86_64-minimal /usr/local/mysql

For CentOS 7, please install below MySQL dependencies:

yum install -y libaio numactl-libs

For CentOS 8 and CentOS Stream 8, please install dependencies below:

dnf install -y libaio ncurses-compat-libs

So far we have finished the MySQL installation. If this is your first time to install MySQL, to make it easier to execute MySQL commands later, please add the path of MySQL executable file to system environment variable PATH by commands below:

echo 'export PATH="/usr/local/mysql/bin:$PATH"' >> ~/.bashrc
source ~/.bashrc

Now you may check the MySQL version:

mysql --version

4 Configure MySQL

By configuration, we make three jobs done:

  1. Create a dedicated account for MySQL Service.
  2. Specify the data directory for MySQL.
  3. Add database user for remote access from client end.

4.1 Create Dedicated Account

We will create a dedicated account (here is “mysql”) to run MySQL service and store database files.

By running command below, we will create a system account named “mysql” that does not have login permission to server:

useradd mysql -s /sbin/nologin

4.2 Initialize Database

By default, data directory is initialized under the same directory as MySQL program, which is not a good practice. By using command option –datadir, we specify the data directory as home directory of “mysql” user (/home/mysql), which is seperate from program files.

Commands below will initialize database to the home directory of mysql:

sudo -u mysql mkdir /home/mysql/data
mysqld --initialize --user=mysql --datadir=/home/mysql/data

When the initialization is done, an administrator root account will be generated with default password. Please copy and save this password, it will be used to log in MySQL later.

If you are bothered to remember this temporary password, you may use –initialize-insecure to replace the –initialize in above command, which will generate an empty password.

For security issue, please make sure you will change this temporary password later.

4.3 Test to start Database

Before trying to start database, please make sure all legacy database files are removed by command below (if not sure to remove, you may backup to other location):

rm -rf /etc/my.cnf*

We will use script called mysql.server to start MySQL. To get to this file, let us go to support-files directory of MySQL:

cd /usr/local/mysql/support-files

In mysql.server, we need to change the value of basedir and datadir according to what we used in previous steps:

basedir=/usr/local/mysql
datadir=/home/mysql/data

You may use commands below to update the file automatically:

sed -i 's/^\(basedir=\)$/\1\/usr\/local\/mysql/' mysql.server
sed -i 's/^\(datadir=\)$/\1\/home\/mysql\/data/' mysql.server

Now we can start MySQL as a role of mysql user:

sudo -u mysql ./mysql.server start

If no error comes, you will see message below telling that MySQL is started successfully and running properly:

Starting MySQL. SUCCESS!

4.4 Change Password

During database initialization, we created an administrator user called “root”, who has top authentication. We must change password for this root user.

First we will log in to MySQL as a role of root user:

mysql -u root -p

The password we wrote down in previous step 4.2 will be used here for “Enter password:” (you may just press Enter if using empty password). After that you will see MySQL welcome message:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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>

After mysql> in screen, please copy below SQL command, with yourpassword replaced with the real password, and execute to change password:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'yourpassword';

The password was updated successfully:

Query OK, 0 rows affected (0.01 sec)

To test whether the new password is in use, you may use QUIT command to log out, and log in again with the new password to verify.

4.5 Create Local Account

We can use administrator root account to add any account for database and control the permission for the added account. For SQL statements for database account, please refer to chapter “Account Management Statements” of SQL statements from MySQL official documentation.

The “Local Account” here means the database account for local application. Of course you may use administrator root account for that, but it is NOT recommended for security issue.

We recommend to use separate account for each database you have. If your application name is myapp, you may use below two commands to create account for it (replace yourpassword with your real password):

CREATE USER 'myapp'@'localhost' IDENTIFIED BY 'yourpassword';
GRANT ALL ON myapp.* TO 'myapp'@'localhost';

By now the account created is for local access only, for remote access we need to create remote account.

4.6 Create Remote Account

The “Remote Account” we talked about here is the account that connects to database via IP address of server host. If no need to use database remotely, or no need to manage database from MySQL client end, you may ignore this section.

Example below will create an account to manage all host databases from any MySQL client you use. If the account is to manage the database of certain application, you may specify the database name in SQL statement when grand privileges to account, like we did in step 4.5.

First please log in to MySQL using administrator root account, then execute two commands below to create remote account for connecting database from client end (replace yourpassword with your real password).

CREATE USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'yourpassword';
GRANT ALL ON *.* TO 'root'@'%';

The difference between local account and remote account is the host name after @. Here we used wildcard characters % to match any host name or any IP address. For more usage of wildcard character, please refer to MySQL documentation.

Please do not confuse the root account created here with the administrator root account. They are different accounts. MySQL will identify which root account automatically by host name or IP address. If it is from host name “localhost” or IP address 127.0.0.1, it will be recognized as administrator account, or else the new created root account.

We used same name “root” on purpose to illustrate the difference between ‘root’@’localhost’ and ‘root’@’%’. You may use completely different name to avoid possible confusion, like myapp, johndoe.

Please pay attention that caching_sha2_password is the default authentication plugin since MySQL 8.0. This is okay for local access, but if you are using remote client access, it is better to declare mysql_native_password as the authentication plugin in SQL statement when create remote account.

5 Auto-restart MySQL

We started MySQL in previous step to test running. It will be stopped during system downtime or server maintenance. To make sure MySQL get restarted each time with system restart, we need to pass it as a service to operation system to manage.

First we will stop the MySQL started manually before:

cd /usr/local/mysql/support-files
sudo -u mysql ./mysql.server stop

In CentOS, we use systemd to manage all services in system. To ask it to manage MySQL for us, we need to create a config file.

Use any editor you like to create a file called mysql.service to the directory below (here we use vi editor):

vi /etc/systemd/system/mysql.service

Copy the content below and save to the new created file (for vi editor: press ESC + input :wq + press Enter):

[Unit]
Description=MySQL database server
After=syslog.target
After=network.target

[Service]
Type=forking
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/support-files/mysql.server start
ExecStop=/usr/local/mysql/support-files/mysql.server stop
ExecReload=/usr/local/mysql/support-files/mysql.server reload

[Install]
WantedBy=multi-user.target

Let systemd reload the new configuration file:

systemctl daemon-reload

Now we can start MySQL service:

systemctl start mysql

To check the MySQL running status:

systemctl status mysql

If status is Active: active (running), and no error or warning comes, MySQL is running fine on system.

At last, we will enable to let MySQL start automatically with system boot:

systemctl enable mysql

So far, we have finished all steps on MySQL installation and configuration on CentOS.

6 Upgrade MySQL

To upgrade MySQL to a new version released in future, you only need to follow the step1, step2, and step3 of this guide. Since installation files and database files are in separate location, we can rewrite the old binary distribution with the new one with no problem.

7 Conclusion

After all steps above, there are files and information that should be useful for future maintenance, or used by other softwares. We listed them as below for your reference.

Path of MySQL database files:

/home/mysql/data

Path of MySQL Socket file:

/tmp/mysql.sock

Path of system configuration file for MySQL:

/etc/systemd/system/mysql.service

If you have any problem to follow this guide, please leave a comment.

Leave a comment

Your email address will not be published. Required fields are marked *