Mysql 5.7.17 Upgrade

Before that, mysql server has been installed and set up, just for normal use. Now the scanning software detects mysql vulnerabilities, so it needs to be upgraded. We can download the latest version of the database on mysql official website, so as to get the latest corrections and patches, avoid vulnerabilities on the server and reduce security risks.

Download the latest MySQL installation package on mysql's official website:

https://dev.mysql.com/downloads/mysql/

The downloads are as follows:

mysql-community-common-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-5.7.17-1.el6.x86_64.rpm
mysql-community-server-5.7.17-1.el6.x86_64.rpm
mysql-community-client-5.7.17-1.el6.x86_64.rpm
mysql-community-server-5.7.17-1.el6.x86_64.rpm
mysql-community-devel-5.7.17-1.el6.x86_64.rpm

Backup mysql database

Use mysqldump to back up mysql database:

Mysqldump-u < DB administrator username > - P mydb > 2017-02-17_mydb.sql

Keep these database files properly to avoid unnecessary loss.

There is another way to backup database files directly:

mv /var/lib/mysql/mydb /var/lib/mydb_2017-02-17_bak

Delete old database versions

Use mysql - version to view the version of the current database.

Delete mysql database:

rpm -qa|grep mysql | xargs rpm -e --nodeps

Query mysql again if deletion is complete:

rpm -qa|grep mysql

Install the new mysql database

1. Use yum or rpm to install the RPM package of mysql database, and install it sequentially:

yum install mysql-community-common-5.7.17-1.el6.x86_64.rpm
yum install mysql-community-libs-5.7.17-1.el6.x86_64.rpm
yum install mysql-community-server-5.7.17-1.el6.x86_64.rpm
yum install mysql-community-client-5.7.17-1.el6.x86_64.rpm
yum install mysql-community-server-5.7.17-1.el6.x86_64.rpm
yum install mysql-community-devel-5.7.17-1.el6.x86_64.rpm

2. Modify the password of mysql root user
Here are two ways to change the root password:
(1) Modify by Order
Mysql 5.7 default root user password is not empty, using mysql-uroot-p connection failed, for root users, 5.7 has set the default password, stored in the / root/.mysql_secret file:

[root@quota ~]# cat /root/.mysql_secret 
# Password set for user 'root@localhost' at 2017-02-17 10:23:53 
k(heCR:wAUtY

Log in with the initial password, change the root user's password to 123456 by alter user's `root'@'localhost'identified by `123456', then exit and log in again with the root user and the password just set.

(2) Modify the password by following the new table

1. Modify vim/etc/my.cnf and add skip-grant-tables=1 to the [mysqld] summary
 No password validation is required to set up mysql connections.

2. Restart mysql service: service mysqld restart

3. Log in with root user: mysql-uroot-p 

4. Switch to mysql database and follow the password of root user in the new user table:

update user set authentication_string = '123456', password_expired = 'N', password_last_changed = now() where user = 'root';

Prior to version 5.7, the password field was password, and the password field in version 5.7 was authentication_string.

5. Exit mysql, modify / etc/my.cnf, delete skip-grant-tables=1 field
 6. Restart mysql service: service mysqld restart. Log in with a new password.

Upgrade mysql database and tables

1. First reset the mysql database directory and delete the previous database directory:
Mv/var/lib/mysql/var/lib/mysql_20170217, and then execute the following commands:

/usr/bin/mysql_install_db --user=mysql --datadir=/var/lib/mysql

Restore user database

1. Create database users first
Mysql-uroot-p, connect to database and create dbuser user:

CREATE USER dbuser@localhost IDENTIFIED BY 'dbuser';

mysql> CREATE USER dbuser@localhost IDENTIFIED BY 'dbuser';
Query OK, 0 rows affected (0.00 sec)

Set user rights:

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> update user set host='%' where user='dbuser';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

%Represents that all servers are connected.

2. Creating a database
Mysql-udbuser-p connects to the database and uses dbuser to create the database:

CREATE DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Note: Please pay attention to the encoding method of the database to prevent the occurrence of random code.

3. Restore backup user data
Exit database connection, restore table structure and table data:

[root@quota mysql]# mysql -uroot -p pac < /tmp/20170216_pac-db.sql 
Enter password: 

The database was restored successfully.

Setting up mysqld service self-startup

Set mysql boot-up self-start:

[root@quota pac]# chkconfig mysqld on

# chkconfig --list mysqld
mysqld  0:off   1:off   2:on    3:on    4:on    5:on    6:off

Restart the web server test connection

Success!!!