MySQL Upgrade (5.7.23 to 8.0.19)

1. Check for the Upgrade

[paul.f@sys-7 ~]# mysqlsh -A
MySQL JS > util.checkForServerUpgrade("root@localhost:3306");
MySQL JS > \quit

2. Download needed version of MySQL

[paul.f@sys-7 ~]# wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.19-1.el7.x86_64.rpm-bundle.tar

3. Stop MySQL Service

[paul.f@sys-7 ~]# systemctl stop mysqld

4. Remove Current Version of MySQL (mysql-5.7.23)

[paul.f@sys-7 ~]# rpm -qa | grep -i mysql

[paul.f@sys-7 ~]# rpm -e mysql-community-server-5.7.23-1.el7.x86_64 mysql-community-client-5.7.23-1.el7.x86_64 mysql-community-embedded-devel-5.7.23-1.el7.x86_64 mysql-community-common-5.7.23-1.el7.x86_64 mysql-community-embedded-compat-5.7.23-1.el7.x86_64 mysql-community-devel-5.7.23-1.el7.x86_64 mysql-community-libs-5.7.23-1.el7.x86_64 mysql-community-embedded-5.7.23-1.el7.x86_64 mysql-community-libs-compat-5.7.23-1.el7.x86_64 --nodeps

5. Extract the MySQL-8 TAR File

[paul.f@sys-7 ~]# tar -xvf mysql-8.0.19-1.el7.x86_64.rpm-bundle.tar

6. Install Dependencies (if any)

[paul.f@sys-7 ~]# yum install 'perl(JSON)'

7. Install New Version of MySQL (mysql-8.0.19)

[paul.f@sys-7 ~]# rpm -ivh mysql-community-client-8.0.19-1.el7.x86_64.rpm mysql-community-server-8.0.19-1.el7.x86_64.rpm mysql-community-common-8.0.19-1.el7.x86_64.rpm mysql-community-embedded-compat-8.0.19-1.el7.x86_64.rpm mysql-community-devel-8.0.19-1.el7.x86_64.rpm mysql-community-libs-compat-8.0.19-1.el7.x86_64.rpm mysql-community-libs-8.0.19-1.el7.x86_64.rpm mysql-community-test-8.0.19-1.el7.x86_64.rpm

8. Start the MySQL Service

[paul.f@sys-7 ~]# systemctl start mysqld

9. Check the Start & Upgrade Process in the Error Log

[paul.f@sys-7 ~]# cat /etc/my.cnf | grep err


[paul.f@sys-7 ~]# tail -10f /var/log/mysqld.log

MySQL Load Data VS MySQL Shell Utility

1. Install MySQL-Shell
[root@sys-1635 ~]# wget https://cdn.mysql.com//Downloads/MySQL-Shell/mysql-shell-8.0.19-1.el7.x86_64.rpm

[root@sys-1635 ~]# rpm -ivh mysql-shell-8.0.19-1.el7.x86_64.rpm

#Install MySQL 8

2. Loading Data with Traditional load data infile method

[paul.f@sys-1635 ~]$ ls -lrth
-rw-r--r--.  1 paul.f dba          1.8G Jan 31 17:56 DM_Loan_LS.sql

mysql> select now();load data infile '/var/lib/mysql-files/DM_Loan_LS.sql' into table db3.DM_Loan_LS;select now();
+---------------------+
| now()               |
+---------------------+
| 2020-01-31 18:27:17 |
+---------------------+
1 row in set (0.00 sec)

Query OK, 3007281 rows affected, 65535 warnings (1 min 39.92 sec)
Records: 3007281  Deleted: 0  Skipped: 0  Warnings: 201487826

+---------------------+
| now()               |
+---------------------+
| 2020-01-31 18:28:57 |
+---------------------+
1 row in set (0.00 sec)

It took 99.92 seconds 

3. Create a Session
#User must be passed on the connection string
# --mc must be specified to create a mysql session
# \sql to be used to switch to SQL mode

[paul.f@sys-1635 ~]$ mysqlsh -u'root' -p'FRANK@74fr' --mc -A
MySQL Shell 8.0.19

Copyright (c) 2016, 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 '\?' for help; '\quit' to exit.
WARNING: Using a password on the command line interface can be insecure.
Creating a Classic session to 'root@localhost'
Your MySQL connection id is 17
Server version: 8.0.19 MySQL Community Server - GPL
No default schema selected; type \use <schema> to set one.
 MySQL  localhost  JS > \sql
Switching to SQL mode... Commands end with ;

4. Enable local_infile variable

ERROR: The 'local_infile' global system variable must be set to ON in the target server, after the server is verified to be trusted.

mysql> set global local_infile=ON;

5. Import using multi threaded importTable utility in MySQL Shell.

 MySQL  localhost  JS > util.importTable ("/var/lib/mysql-files/DM_Loan_LS.sql", {schema:"db1",threads: 8,showProgress: true})

Importing from file '/var/lib/mysql-files/DM_Loan_LS.sql' to table `db1`.`DM_Loan_LS` in MySQL Server at /var%2Flib%2Fmysql%2Fmysql.sock using 8 threads

Check mysqlsh.log for 1019 more warnings.

100% (1.90 GB / 1.90 GB), 134.71 MB/s

File '/var/lib/mysql-files/DM_Loan_LS.sql' (1.90 GB) was imported in 15.7800 sec at 120.32 MB/s

Total rows affected in db1.DM_Loan_LS: Records: 3007281  Deleted: 0  Skipped: 3007279  Warnings: 204495105

It took 15.78 seconds 

importTable is 6x faster than the single-threaded method and improved the speed tremendously.

How to re-take control of the root user in MySQL ? How to start MySQL with --skip-grant-tables ?

How to re-take control of the root user in MySQL.

DANGER: RISKY OPERATION

• Start session ssh (using root if possible).
• Edit my.cnf file using.

  sudo vi /etc/my.cnf

• Add a line to mysqld block.*

  skip-grant-tables

• Save and exit.
• Restart MySQL service.

  service mysql restart

• Check service status.

  service mysql status

• Connect to MySQL.

  mysql

• Using the mysql database.

  use mysql;

• Redefine user root password.

  UPDATE user SET `authentication_string` = PASSWORD('myNuevoPassword') WHERE `User` = 'root'; 

• Edit file my.cnf.

  sudo vi /etc/my.cnf

• Erase line.

  skip-grant-tables

• Save and exit.
• Restart MySQL service.

  service mysqld restart

• Check service status.

  service mysql status

• Connect to the database.

  mysql -u root -p

• Type a new password when prompted.

This action is very dangerous, it allows anyone to connect to all databases with no restriction without a user and password. It must be used carefully and must be reverted quickly to avoid risks.

MySQL Upgrade (5.7.17 to 5.7.19)

1. Download needed version of MySQL

[paul.f@cd-gf ~]$ wget https://downloads.mysql.com/archives/get/file/mysql-5.7.19-1.el7.x86_64.rpm-bundle.tar

2. Move it to the Destination Server

[paul.f@cd-gf ~]$ scp mysql-5.7.19-1.el7.x86_64.rpm-bundle.tar 10.4.0.15:/home/paul.f/

3. Enter into the Destination Server

[paul.f@cd-gf ~]$ ssh 10.4.0.15

4. Switch as root user

[paul.f@-gf ~]$ sudo -i

5. Check Space Availability

[root@-gf ~]# df -h

6. Stop Slave in MySQL

mysql> stop slave;

7. Stop MySQL Service

[root@-gf paul.f]# systemctl stop mysqld

8. Take Backup of existing my.cnf 

[root@-gf paul.f]# cp /etc/my.cnf /etc/my.cnf_11012018

9. Unlink the Symbolic Link

[root@-gf paul.f]# cd /var/lib/
lrwxrwxrwx  1 root       root         11 Jan 17  2017 mysql -> /data/mysql

[root@-gf lib]# unlink mysql (where 'mysql' is the name of Symbolic Link)

10. Extract the TAR File

[root@db-gf lib]# cd /home/paul.f/

[root@-gf paul.f]# tar xvf mysql-5.7.19-1.el7.x86_64.rpm-bundle.tar

11. Remove the Unwanted Files

[root@db-gf paul.f]# rm -rvf mysql-5.7.19-1.el7.x86_64.rpm-bundle.tar mysql-community-test-5.7.19-1.el7.x86_64.rpm mysql-community-server-minimal-5.7.19-1.el7.x86_64.rpm mysql-community-minimal-debuginfo-5.7.19-1.el7.x86_64.rpm

12. Grep the Currently Installed RPMs

[root@-gf paul.f]# rpm -qa | grep -i mysql

13. Remove Current Version of MySQL

[root@-gf paul.f]# rpm -e mysql-community-server-5.7.17-1.el7.x86_64 mysql-community-client-5.7.17-1.el7.x86_64 mysql-community-libs-5.7.17-1.el7.x86_64 mysql-community-libs-compat-5.7.17-1.el7.x86_64 mysql-community-common-5.7.17-1.el7.x86_64 mysql-community-devel-5.7.17-1.el7.x86_64 --nodeps

14. Install New Version of MySQL

[root@-gf paul.f]# rpm -ivh mysql-community-*

15. Replace New my.cnf with the Backed Up one

[root@-gf paul.f]# cp /etc/my.cnf_11012018 /etc/my.cnf

16. Remove Old Symbolic Link

[root@db-gf paul.f]# cd /var/lib/

[root@-gf lib]# rm -rvf mysql (where 'mysql' is the name of Symbolic Link)

17. Create a Symbolic Link for /data/mysql

[root@-gf lib]# ln -s /data/mysql .

18. Locate MySQL Service

[root@-gf mysql]# locate mysqld.service
/etc/systemd/system/multi-user.target.wants/mysqld.service
/usr/lib/systemd/system/mysqld.service

19. Grep Open Files Limit from my.cnf

[root@-gf mysql]# cat /etc/my.cnf | grep -i open

20. Replace the Open Files Limit Value taken from my.cnf in mysqld.service

[root@-gf mysql]# vim /usr/lib/systemd/system/mysqld.service

21. Reload the daemon

[root@-gf mysql]# systemctl daemon-reload

22. Start MySQL Service

[root@-gf mysql]# systemctl start mysqld

23. Run MySQL Upgrade

[root@-gf mysql]# mysql_upgrade -s

24. Restart MySQL Service

[root@-gf mysql]# systemctl restart mysqld

25. Check Log for Errors

[root@-gf mysql]# tail -100f errorgf.log

26. Start Slave

mysql> start slave;

27. Remove the RPM Files

[root@-gf paul.f]# rm -rvf mysql-community-*