How do I install MySQL server 8.0 on CentOS 8 Linux server running on Linode and AWS cloud? How do I add and set up a new MySQL user and database account on the newly created CentOS server?
Oracle MySQL server version 8.0 is a free and open-source free database server. It is one of the most popular database system used in web apps and websites on the Internet.
Typically MySQL is part of the LAMP (Linux, Apache/Nginx, MySQL, Perl/Python/PHP) stack. Popular open-source software such as WordPress, MediaWiki, and others profoundly used by MySQL as a database storage engine. Let us see how to install MySQL server version 8.x on CentOS 8 Linux server.
|Requirements||CentOS Linux 8|
|Est. reading time||15m|
First, open the terminal app and then log in to your CentOS server using the ssh command:
$ ssh vivek@centos-8-ec2-box-ip
Now, update CentOS system to apply security updates and fixes on Linux system using the dnf command/yum command:
$ sudo yum update
## or ##
$ sudo dnf update
CentOS-8 - AppStream 21 MB/s | 5.8 MB 00:00 CentOS-8 - Base 14 MB/s | 2.2 MB 00:00 CentOS-8 - Extras 50 kB/s | 8.6 kB 00:00 Dependencies resolved. Nothing to do. Complete!
Luckily our CentOS 8 box comes with MySQL 8 server package. Let us search for it:
$ sudo yum search mysql-server
$ sudo yum module list mysql
And we see:
Last metadata expiration check: 0:02:47 ago on Mon Nov 23 16:26:31 2020. ===================== Name Exactly Matched: mysql-server ====================== mysql-server.x86_64 : The MySQL server and related files
Next, find out version information, run:
$ sudo yum info mysql-server
Here is what we see:
Last metadata expiration check: 0:02:22 ago on Mon Nov 23 16:26:31 2020. Available Packages Name : mysql-server Version : 8.0.21 Release : 1.module_el8.2.0+493+63b41e36 Architecture : x86_64 Size : 22 M Source : mysql-8.0.21-1.module_el8.2.0+493+63b41e36.src.rpm Repository : AppStream Summary : The MySQL server and related files URL : http://www.mysql.com License : GPLv2 with exceptions and LGPLv2 and BSD Description : MySQL is a multi-user, multi-threaded SQL database server. MySQL : is a client/server implementation consisting of a server daemon : (mysqld) and many different client programs and libraries. This : package contains the MySQL server and some accompanying files : and directories.
$ sudo yum install mysql-server
The service name is mysqld.service, and we need to enable it using the following systemctl command:
$ sudo systemctl enable mysqld.service
reated symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.
Start the service and then verify it:
$ sudo systemctl start mysqld.service
$ sudo systemctl status mysqld.service
● mysqld.service - MySQL 8.0 database server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: active (running) since Mon 2020-11-23 16:50:14 UTC; 4s ago Process: 551 ExecStopPost=/usr/libexec/mysql-wait-stop (code=exited, status=0/SUCCESS) Process: 681 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited, status=0/SUCCESS) Process: 601 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mysqld.service (code=exited, status=0/SUCCESS) Process: 577 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS) Main PID: 637 (mysqld) Status: "Server is operational" Tasks: 39 (limit: 24960) Memory: 331.0M CGroup: /system.slice/mysqld.service └─637 /usr/libexec/mysqld --basedir=/usr Nov 23 16:50:13 centos-aws-mysql systemd: Stopped MySQL 8.0 database server. Nov 23 16:50:13 centos-aws-mysql systemd: Starting MySQL 8.0 database server... Nov 23 16:50:14 centos-aws-mysql systemd: Started MySQL 8.0 database server.
All you need to do is type the following command, and it will secure MySQL 8 server installation on CentOS Linux:
$ sudo mysql_secure_installation
Please set the password for root here. New password: Re-enter new password: Estimated strength of the password: 100 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. All done!
The syntax is:
$ sudo systemctl start mysql.service
$ sudo systemctl stop mysql.service
$ sudo systemctl restart mysql.service
To view the MySQL 8 service log as follows using the journalctl command:
$ sudo journalctl -u mysqld.service -xe
$ sudo tail -f /var/log/mysql/mysqld.log
MySQL 8 log file sample entries:
2020-11-23T16:55:19.101316Z 0 [System] [MY-013172] [Server] Received SHUTDOWN from user . Shutting down mysqld (Version: 8.0.21). 2020-11-23T16:55:21.728819Z 0 [Warning] [MY-010909] [Server] /usr/libexec/mysqld: Forcing close of thread 10 user: 'root'. 2020-11-23T16:55:23.083389Z 0 [System] [MY-010910] [Server] /usr/libexec/mysqld: Shutdown complete (mysqld 8.0.21) Source distribution. 2020-11-23T16:56:19.225544Z 0 [System] [MY-010116] [Server] /usr/libexec/mysqld (mysqld 8.0.21) starting as process 524 2020-11-23T16:56:19.237500Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2020-11-23T16:56:19.562441Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2020-11-23T16:56:19.677202Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/lib/mysql/mysqlx.sock 2020-11-23T16:56:19.754024Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2020-11-23T16:56:19.754207Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel. 2020-11-23T16:56:19.780843Z 0 [System] [MY-010931] [Server] /usr/libexec/mysqld: ready for connections. Version: '8.0.21' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution.
So far, so good. You learned how to install, set up, secure, and start/stop the MySQL 8 on CentOS 8 Linux cloud server. It is time to log in as a
mysql root user. The syntax is:
$ mysql -u root -p
$ mysql -u USER -h host -p
$ mysql -u USER -h host -p mysql
Let us type a few SQL commands at the mysql> prompt:
SHOW VARIABLES LIKE "%version%";
Let create a new database called ‘spacedb‘, type at the mysql> prompt:
CREATE DATABASE spacedb;
Next, we are going to create a new user named ‘mars‘ for our database called ‘spacedb’ as follows:
CREATE USER 'mars'@'%' IDENTIFIED BY 'User_Password_Here';
Finally, give permissions:
GRANT SELECT, INSERT, UPDATE, DELETE ON spacedb.* TO 'mars'@'%';
Of course, we can grant ALL PRIVILEGES too as follows:
GRANT ALL PRIVILEGES ON spacedb.* TO 'mars'@'%';
See MySQL 8 users and their grants/permissions as follows:
SELECT user,host FROM mysql.user;
SHOW GRANTS for mars;
Test new user settings and DB as follows:
mysql -u mars -p spacedb
mysql -u mars -h localhost -p spacedb
- -u mars; : User name for login
- -h localhost : Connect to server named localhost
- -p : Prompt for password
- spacedb : Connect to database named spacedb
Let us see default config file using the cat command:
# cat /etc/my.cnf.d/mysql-server.cnf
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysql/mysqld.log pid-file=/run/mysqld/mysqld.pid
Want to allow remote connections to your MySQL server? Edit the /etc/my.cnf.d/mysql-server.cnf and append the following line under [mysqld]:
bind_address = 0.0.0.0
WARNING: See MySQL documentation for a detailed explanation for tuning options as to each server and set up is unique. Do not set up values blindly. I provide them as a starting point for optimizing MySQL 8 installation and values depending upon available RAM, CPU cores, server load and other circumstances.
Set InnoDB settings:
default_storage_engine = InnoDB innodb_buffer_pool_instances = 1 innodb_buffer_pool_size = 512M innodb_file_per_table = 1 innodb_flush_log_at_trx_commit = 0 innodb_flush_method = O_DIRECT innodb_log_buffer_size = 16M innodb_log_file_size = 512M innodb_stats_on_metadata = 0 innodb_read_io_threads = 64 innodb_write_io_threads = 64
# UPD key_buffer_size = 32M low_priority_updates = 1 concurrent_insert = 2 # UPD max_connections = 100 back_log = 512 thread_cache_size = 100 thread_stack = 192K interactive_timeout = 180 wait_timeout = 180
Buffer settings UPD:
join_buffer_size = 4M read_buffer_size = 3M read_rnd_buffer_size = 4M sort_buffer_size = 4M
Edit and config logging if needed (by default slow_query disabled):
log_queries_not_using_indexes = 1 long_query_time = 5 #slow_query_log = 0 #slow_query_log_file = /var/log/mysql/mysql_slow.log
This is useful for mysqldump command to make backups:
[mysqldump] quick quote_names max_allowed_packet = 64M
Are you using MySQL 8 server remotely? Do you have Apache/Nginx/PHP/Python/Perl app on another server? Then open port for everyone:
$ sudo firewall-cmd --zone=public --add-service=mysql --permanent
Only allow access from 192.168.1.0/24 CIDR:
$ sudo firewall-cmd \
--add-rich-rule 'rule family="ipv4" \
source address="192.168.1.0/24" \
service name="mysql" accept' --permanent
The above is fine grained firewalld access rules to restrict access to MySQL 8 server to VLAN users only. See how to set up a firewall using FirewallD on CentOS 8 Linux for more info.
And there you have it, Oracle MySQL server version 8.x set up and running correctly on a CentOS Linux 8 server with Firewalld config. Further, you learned how to add a new database, user, and password for your project including MySQL 8 server tuning options.