How to change MySQL default storage engine on Rocky Linux 8.6
- 00:40 cat /etc/os-release
- 00:57 rpm -qa | grep mysql
- 01:14 yum install mysql-server*
- 01:55 systemctl start mysqld.service
- 02:14 systemctl enable mysqld
- 02:27 mysql_secure_installation
- 02:59 mysql -u root -p
- 03:21 show engines;
- 04:02 systemctl stop mysqld.service
- 04:14 cd /etc/my.cnf.d/
- 04:19 ls -la
- 04:23 vim mysql-server.cnf
- 05:15 systemctl restart mysqld.service
- 05:28 mysql -u root -p
- 05:40 show engines;
{{postValue.id}}
To change MySQL default storage engine on Rocky Linux 8.6
Introduction
Storage engines are software modules used by database management systems to create, read, and update data in databases. In MySQL, there are two types of storage engines: transactional and non-transactional. The default storage engine for MySQL prior to version 5.5 was MyISAM.
Installation Procedure:
Step 1 : Check the OS version by using the below command
[root@linuxhelp ~]# cat /etc/os-release
NAME="Rocky Linux"
VERSION="8.6 (Green Obsidian)"
ID="rocky"
ID_LIKE="rhel centos fedora"
VERSION_ID="8.6"
PLATFORM_ID="platform:el8"
PRETTY_NAME="Rocky Linux 8.6 (Green Obsidian)"
ANSI_COLOR="0;32"
CPE_NAME="cpe:/o:rocky:rocky:8:GA"
HOME_URL="https://rockylinux.org/"
BUG_REPORT_URL="https://bugs.rockylinux.org/"
ROCKY_SUPPORT_PRODUCT="Rocky Linux"
ROCKY_SUPPORT_PRODUCT_VERSION="8"
REDHAT_SUPPORT_PRODUCT="Rocky Linux"
REDHAT_SUPPORT_PRODUCT_VERSION="8"
Step 2: Need to check whether the MySQL package has installed before by using the below command
[root@linuxhelp ~]# rpm -qa | grep mysql
Step 3 : Now install the MySQL package by running the below command
[root@linuxhelp ~]# yum install mysql-server*
Rocky Linux 8 - AppStream 6.3 kB/s | 4.8 kB 00:00
Rocky Linux 8 - AppStream 3.8 MB/s | 11 MB 00:02
Rocky Linux 8 - BaseOS 5.4 kB/s | 4.3 kB 00:00
Rocky Linux 8 - BaseOS 3.3 MB/s | 7.9 MB 00:02
Rocky Linux 8 - Extras 4.6 kB/s | 3.5 kB 00:00
Rocky Linux 8 - Extras 14 kB/s | 12 kB 00:00
Extra Packages for Enterprise Linux 8 - x86_64 8.8 kB/s | 9.1 kB 00:01
Extra Packages for Enterprise Linux 8 - x86_64 3.6 MB/s | 13 MB 00:03
Extra Packages for Enterprise Linux Modular 8 - x86_64 8.2 kB/s | 9.9 kB 00:01
Remi's Modular repository for Enterprise Linux 8 - x86_64 679 B/s | 833 B 00:01
Remi's Modular repository for Enterprise Linux 8 - x86_64 357 kB/s | 1.1 MB 00:03
Safe Remi's RPM repository for Enterprise Linux 8 - x86_64 686 B/s | 833 B 00:01
Safe Remi's RPM repository for Enterprise Linux 8 - x86_64 510 kB/s | 2.3 MB 00:04
Dependencies resolved.
=====================================================================================================
Package Arch Version Repository Size
=====================================================================================================
Installing:
mysql-server x86_64 8.0.26-1.module+el8.4.0+652+6de068a7 appstream 25 M
Installing dependencies:
mariadb-connector-c-config noarch 3.1.11-2.el8_3 appstream 14 k
mecab x86_64 0.996-1.module+el8.3.0+242+87d3366a.9 appstream 392 k
mysql x86_64 8.0.26-1.module+el8.4.0+652+6de068a7 appstream 12 M
mysql-common x86_64 8.0.26-1.module+el8.4.0+652+6de068a7 appstream 133 k
mysql-errmsg x86_64 8.0.26-1.module+el8.4.0+652+6de068a7 appstream 597 k
protobuf-lite x86_64 3.5.0-13.el8 appstream 148 k
Enabling module streams:
mysql 8.0
Transaction Summary
=====================================================================================================
Install 7 Packages
Total download size: 38 M
Installed size: 195 M
Is this ok [y/N]: y
Downloading Packages:
(1/7): mariadb-connector-c-config-3.1.11-2.el8_3.noarch.rpm 77 kB/s | 14 kB 00:00
(2/7): mysql-common-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64.rpm 851 kB/s | 133 kB 00:00
(3/7): mecab-0.996-1.module+el8.3.0+242+87d3366a.9.x86_64.rpm 1.0 MB/s | 392 kB 00:00
(4/7): mysql-errmsg-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64.rpm 960 kB/s | 597 kB 00:00
(5/7): protobuf-lite-3.5.0-13.el8.x86_64.rpm 579 kB/s | 148 kB 00:00
(6/7): mysql-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64.rpm 3.5 MB/s | 12 MB 00:03
(7/7): mysql-server-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64.rpm 3.9 MB/s | 25 MB 00:06
-----------------------------------------------------------------------------------------------------
Total 5.0 MB/s | 38 MB 00:07
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : mariadb-connector-c-config-3.1.11-2.el8_3.noarch 1/7
Installing : mysql-common-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64 2/7
Installing : mysql-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64 3/7
Installing : mysql-errmsg-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64 4/7
Installing : protobuf-lite-3.5.0-13.el8.x86_64 5/7
Installing : mecab-0.996-1.module+el8.3.0+242+87d3366a.9.x86_64 6/7
Running scriptlet: mecab-0.996-1.module+el8.3.0+242+87d3366a.9.x86_64 6/7
Running scriptlet: mysql-server-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64 7/7
Installing : mysql-server-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64 7/7
Running scriptlet: mysql-server-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64 7/7
ValueError: File context for /var/log/mysql(/.*)? already defined
Verifying : mariadb-connector-c-config-3.1.11-2.el8_3.noarch 1/7
Verifying : mecab-0.996-1.module+el8.3.0+242+87d3366a.9.x86_64 2/7
Verifying : mysql-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64 3/7
Verifying : mysql-common-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64 4/7
Verifying : mysql-errmsg-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64 5/7
Verifying : mysql-server-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64 6/7
Verifying : protobuf-lite-3.5.0-13.el8.x86_64 7/7
Installed:
mariadb-connector-c-config-3.1.11-2.el8_3.noarch
mecab-0.996-1.module+el8.3.0+242+87d3366a.9.x86_64
mysql-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64
mysql-common-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64
mysql-errmsg-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64
mysql-server-8.0.26-1.module+el8.4.0+652+6de068a7.x86_64
protobuf-lite-3.5.0-13.el8.x86_64
Complete!
Step 4 : After the installation, Starting the MySQL service by using the below command
[root@linuxhelp ~]# systemctl start mysqld.service
Step 5 : Next enable the mysql by running the below command
[root@linuxhelp ~]# systemctl enable mysqld
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.
Step 6: Next run the secure installation command to secure the MySQL database by using the below command
[root@linuxhelp ~]# mysql_secure_installation
Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
VALIDATE PASSWORD COMPONENT 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 component?
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!
Step 7 : Now need to login the MySQL shell by using the below command
[root@linuxhelp ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.26 Source distribution
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.
Step 8: Next run the following command in MySQL shell and exit
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
mysql> exit
Bye
Step 9 : After that stop the MySQL service by running the below command
[root@linuxhelp ~]# systemctl stop mysqld.service
Step 10 : Get into the config directory and list the files by running the below command
[root@linuxhelp ~]# cd /etc/my.cnf.d/
[root@linuxhelp my.cnf.d]# ls -la
total 24
drwxr-xr-x. 2 root root 95 Sep 26 15:52 .
drwxr-xr-x. 147 root root 8192 Sep 26 15:53 ..
-rw-r--r--. 1 root root 295 May 16 2021 client.cnf
-rw-r--r--. 1 root root 565 Sep 21 2021 mysql-default-authentication-plugin.cnf
-rw-r--r--. 1 root root 612 Sep 21 2021 mysql-server.cnf
Step 11 : Open the MySQL server configuration for changing the default by running the below command
[root@linuxhelp my.cnf.d]# vim mysql-server.cnf
Step 12 : After changing need to restart the mysql service by running the below command
[root@linuxhelp my.cnf.d]# systemctl restart mysqld.service
STep 13:Once the restart is completed need to login to the Mysql shell by running the below command
[root@linuxhelp my.cnf.d]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 Source distribution
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.
Step 14 :Finally need to check the default changes is applied or not
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | DEFAULT | MyISAM storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
Conclusion:
We have reached the end of this article. In this guide, we have walked you through the steps required to change MySQL default storage engine on rocky linux 8.6. Your feedback is much welcome.
Comments ( 0 )
No comments available