MySQL is a multi-user, multi-threaded SQL database server that uses a client/server implementation consisting of a server daemon (mysqld) and many different client programs and libraries.
This tutorial will guide you through the installation of MySQL 8.0 on an Oracle Linux 8 server. The following are the steps you’ll follow to install MySQL 8.0 on Oracle Linux 8.
Install MySQL 8.0 on Oracle Linux 8
Follow the steps below to install, configure, and start MySQL 8.0 on Oracle Linux 8. Since the installation is done from the YUM repository, updating the MySQL database server is an easy process.
Step 1: Update your system
Ensure your system is updated.
sudo yum updateConfirm list of a packages to be updated and hit the y key if you’re okay with it. Then reboot your machine:
sudo systemctl rebootConfirm the current release of Oracle Linux 8 you’re running:
$ cat /etc/oracle-release
Oracle Linux Server release 8.2Step 2: Install MySQL 8.0 on Oracle Linux 8
MySQL 8.0 package is available on the AppStream repository of the Oracle Linux operating system.
$ sudo dnf module list mysql
Last metadata expiration check: 0:06:21 ago on Fri 29 May 2020 10:27:56 PM UTC.
Oracle Linux 8 Application Stream (x86_64)
Name Stream Profiles Summary
mysql 8.0 [d] client, server [d] MySQL Module
Hint: [d]efault, [e]nabled, [x]disabled, [i]nstalled
To Install MySQL 8.0 on Oracle Linux 8 server, run the command below.
sudo dnf install @mysql:8.0Accept the installation of MySQL 8.0 on Oracle Linux 8.0:
Dependencies resolved.
========================================================================================================================================================
Package Architecture Version Repository Size
========================================================================================================================================================
Installing group/module packages:
mysql-server x86_64 8.0.17-3.module+el8.0.0+5253+1dce7bb2 ol8_appstream 22 M
Installing dependencies:
mariadb-connector-c-config noarch 3.0.7-1.el8 ol8_appstream 13 k
mecab x86_64 0.996-1.module+el8.0.0+5253+1dce7bb2.9 ol8_appstream 397 k
mysql x86_64 8.0.17-3.module+el8.0.0+5253+1dce7bb2 ol8_appstream 11 M
mysql-common x86_64 8.0.17-3.module+el8.0.0+5253+1dce7bb2 ol8_appstream 143 k
mysql-errmsg x86_64 8.0.17-3.module+el8.0.0+5253+1dce7bb2 ol8_appstream 557 k
perl-Carp noarch 1.42-396.el8 ol8_baseos_latest 30 k
perl-Encode x86_64 4:2.97-3.el8 ol8_baseos_latest 1.5 M
perl-Errno x86_64 1.28-416.el8 ol8_baseos_latest 76 k
perl-Exporter noarch 5.72-396.el8 ol8_baseos_latest 34 k
perl-File-Path noarch 2.15-2.el8 ol8_baseos_latest 38 k
perl-File-Temp noarch 0.230.600-1.el8 ol8_baseos_latest 63 k
perl-Getopt-Long noarch 1:2.50-4.el8 ol8_baseos_latest 63 k
perl-HTTP-Tiny noarch 0.074-1.el8 ol8_baseos_latest 58 k
perl-IO x86_64 1.38-416.el8 ol8_baseos_latest 141 k
perl-MIME-Base64 x86_64 3.15-396.el8 ol8_baseos_latest 31 k
perl-PathTools x86_64 3.74-1.el8 ol8_baseos_latest 90 k
perl-Pod-Escapes noarch 1:1.07-395.el8 ol8_baseos_latest 20 k
perl-Pod-Perldoc noarch 3.28-396.el8 ol8_baseos_latest 88 k
perl-Pod-Simple noarch 1:3.35-395.el8 ol8_baseos_latest 213 k
perl-Pod-Usage noarch 4:1.69-395.el8 ol8_baseos_latest 34 k
perl-Scalar-List-Utils x86_64 3:1.49-2.el8 ol8_baseos_latest 68 k
perl-Socket x86_64 4:2.027-3.el8 ol8_baseos_latest 59 k
perl-Storable x86_64 1:3.11-3.el8 ol8_baseos_latest 98 k
perl-Term-ANSIColor noarch 4.06-396.el8 ol8_baseos_latest 46 k
perl-Term-Cap noarch 1.17-395.el8 ol8_baseos_latest 23 k
perl-Text-ParseWords noarch 3.30-395.el8 ol8_baseos_latest 18 k
perl-Text-Tabs+Wrap noarch 2013.0523-395.el8 ol8_baseos_latest 24 k
perl-Time-Local noarch 1:1.280-1.el8 ol8_baseos_latest 33 k
perl-Unicode-Normalize x86_64 1.25-396.el8 ol8_baseos_latest 82 k
perl-constant noarch 1.33-396.el8 ol8_baseos_latest 25 k
perl-interpreter x86_64 4:5.26.3-416.el8 ol8_baseos_latest 6.3 M
perl-libs x86_64 4:5.26.3-416.el8 ol8_baseos_latest 1.6 M
perl-macros x86_64 4:5.26.3-416.el8 ol8_baseos_latest 72 k
perl-parent noarch 1:0.237-1.el8 ol8_baseos_latest 20 k
perl-podlators noarch 4.11-1.el8 ol8_baseos_latest 118 k
perl-threads x86_64 1:2.21-2.el8 ol8_baseos_latest 61 k
perl-threads-shared x86_64 1.58-2.el8 ol8_baseos_latest 48 k
protobuf-lite x86_64 3.5.0-7.el8 ol8_appstream 150 k
Installing module profiles:
mysql/server
Enabling module streams:
mysql 8.0
Transaction Summary
========================================================================================================================================================
Install 39 Packages
Total download size: 44 M
Installed size: 249 M
Is this ok [y/N]: yYou can confirm package details.
$ rpm -qi mysql-server
Name : mysql-server
Version : 8.0.17
Release : 3.module+el8.0.0+5253+1dce7bb2
Architecture: x86_64
Install Date: Fri 29 May 2020 10:36:33 PM UTC
Group : Unspecified
Size : 144963659
License : GPLv2 with exceptions and LGPLv2 and BSD
Signature : RSA/SHA256, Fri 16 Aug 2019 01:39:42 PM UTC, Key ID 82562ea9ad986da3
Source RPM : mysql-8.0.17-3.module+el8.0.0+5253+1dce7bb2.src.rpm
Build Date : Fri 16 Aug 2019 01:33:41 PM UTC
Build Host : jenkins-10-147-72-125-37856c20-4ffa-4820-bdad-6f8ce19058d8.appad1iad.osdevelopmeniad.oraclevcn.com
Relocations : (not relocatable)
Vendor : Oracle America
URL : http://www.mysql.com
Summary : The MySQL server and related filesStep 3: Configure MySQL 8.0 on Oracle Linux 8
Start and enable mysql daemon service to start at system boot.
sudo systemctl enable --now mysqldService status query should return as running.
systemctl status mysqld
Secure Database Server by:
- Setting a secure root user password
- Removing the anonymous users from the database
- Disallow remote login as root user
- Delete test database
Run the command below:
$ sudo 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: y
There are three levels of password validation policy:
LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
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!
To check your password policy requirements, run the command:
mysql> SHOW VARIABLES LIKE 'validate_password%';
Test that you can login successfully as root user:
$ 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.17 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> QUIT
ByeYou now have a running MySQL 8 database running on Oracle Linux 8 system.
Grant Remote Access:
- Open mysql-server.cnf file inside /etc/my/my.cnf.d folder and add the following under [mysqld] section:
bind-address = 0.0.0.0
# skip-networking
- Restart mysqld service: sudo systemctl restart mysqld
- Logon locally as root user to mysql: mysql -u root -p
- Run the command: GRANT ALL ON database_name.* TO user_name@'ip_address' IDENTIFIED BY 'user_password'; Replace ip_address by % to allow the user to login from any ip address.
Migrate MySQL Database Between 2 Servers
Now that you have a basic grasp of MySQL, let’s try to understand the steps to migrate MySQL database between 2 servers. Below are the steps you can follow to migrate MySQL database between 2 servers:
1) Backup the Data
The first step to migrate the MySQL database is to take a dump of the data that you want to transfer. To do that, you will have to use mysqldump command. The basic syntax of the command is:
mysqldump -u [username] -p [database] > dump.sqlIf the database is on a remote server, either log in to that system using ssh or use -h and -P options to provide host and port respectively.
mysqldump -P [port] -h [host] -u [username] -p [database] > dump.sqlThere are various options available for this command, let’s go through the major ones as per the use case.
A) Backing Up Specific Databases
mysqldump -u [username] -p [database] > dump.sqlThis command dumps specified databases to the file.
You can specify multiple databases for the dump using the following command:
mysqldump -u [username] -p --databases [database1] [database2] > dump.sqlYou can use the –all-databases option to backup all databases on the MySQL instance.
mysqldump -u [username] -p --all-databases > dump.sqlB) Backing Up Specific Tables
The above commands dump all the tables in the specified database, if you need to take backup of some specific tables, you can use the following command:
mysqldump -u [username] -p [database] [table1] [table2] > dump.sqlC) Custom Query
If you want to backup data using some custom query, you will need to use the where option provided by mysqldump.
mysqldump -u [username] -p [database] [table1] --where="WHERE CLAUSE" > dump.sql
Example: mysqldump -u root -p testdb table1 --where="mycolumn = myvalue" > dump.sqlNote:
- By default, mysqldump command includes DROP TABLE and CREATE TABLE statements in the created dump. Hence, if you are using incremental backups or you specifically want to restore data without deleting previous data, make sure you use the –no-create-info option while creating a dump.
mysqldump -u [username] -p [database] --no-create-info > dump.sql- If you need to just copy the schema but not the data, you can use –no-data option while creating the dump.
mysqldump -u [username] -p [database] --no-data > dump.sql2) Copy the Database Dump on the Destination Server
Once you have created the dump as per your specification, the next step is transferring the data dump file to the destination server. You will have to use scp command for that.
Scp -P [port] [dump_file].sql [username]@[servername]:[path on destination]Examples:
scp dump.sql root@130.243.18.62:/var/data/mysql
scp -P 3306 dump.sql root@130.243.18.62:/var/data/mysql3) Restore the Dump
The last step is restoring the data on the destination server. MySQL command directly provides a way to restore to dump data to MySQL.
mysql -u [username] -p [database] < [dump_file].sqlExample:
mysql -u root -p testdb < dump.sqlIf your dump includes multiple databases, don’t specify the database in the above command.
mysql -u root -p < dump.sql