Steps and pitfalls of upgrading linux mysql5.5 to mysql5.7

Linux MySQL 5.5 upgraded to MySQL 5.7

Below I will share a simple step to upgrade MySQL. Later, the editor of 123WORDPRESS.COM has compiled several supplements for you to refer to.

1. Download mysql5.7.32

Official download address: https://dev.mysql.com/downloads/mysql/

Unzip

tar xvf mysql.tar.gz mysql/

2. Enter the old mysql bin directory and export the mysql data

./mysql -uroot -pxxxx -h127.0.0.1 -P3306 </home/mysql5.5/bin/all.sql

3. Enter the new mysql root directory and create a data directory to store data

mkdir data

4. For the newly unpacked database, first execute the initialization command

cd bin
./mysql_install_db --basedir=../ --datadir=../data

PS: The above upgrade is on Cent OS, but when I upgraded Kylin, I found that I did not recognize the above statement and needed to replace it with the following initialization statement

./mysqld --defaults-file=../my.cnf --basedir=../ --datadir=../data --user=root --initialize

5. Set up skip password login

Add skip-grant-tables to [mysqld] in my.cnf
my.cnf is the configuration file for the database

6. Start the service

./mysqld --defaults-file=../my.cnf --basedir=../ --datadir=../data --user=root

7. Set a password

Log in to mysql in a new command line window

./mysql -uroot
use mysql;
update mysql.user set authentication_string=password('xxxx') where user='root';

8. Stopping Services

Comment out skip-grant-tables in the configuration file

9. Start the service

./mysqld --defaults-file=../my.cnf --basedir=../ --datadir=../data --user=root

10. New command line login

./mysql -uroot -pxxxx
use mysql;

At this time: No matter what command you run, you will always be prompted with "You must reset your password using ALTER USER statement before executing this statement."
Then execute the following statement

SET PASSWORD = PASSWORD('xxxx');

11. Set other IP addresses to connect to MySQL

use mysql;
grant all privileges on *.* to "root"@"%" identified by "xxxx";

12. Import historical data

cd bin
./mysql -uroot -pxxxx -h127.0.0.1 -P3306 </home/mysql5.5/bin/all.sql

13. Perform the update

cd bin
./mysql_upgrade

14. After the upgrade, query statements may report errors. You can execute the following statements

set @@sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
set sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

The following are the additions from other netizens

Upgrading MySQL from 5.5 to 5.7

Recently I have a task to upgrade MySQL from version 5.5 to 5.7. I have never upgraded the database before, so I can only move forward by trial and error. I found a lot of information online. There are only two ways to upgrade.

1. Upgrade method

1. Logical Upgrade: Use mysqldump to directly export SQL files, then create a new MySQL5.7 database, and import the exported SQL files into the new database. Of course, if this method is used, it is recommended not to use mysqldump but mydumper. Because our company has a large amount of data, we did not use this method to process it.

2. In-Place Upgrade: This upgrade method is simple and fast. It directly replaces the original MySQL installation directory and /etc/my.cnf configuration file, and uses the mysql_upgrade script to complete the upgrade of the system table.

2. Environment Introduction

Our MySQL cluster uses Atlas read-write separation, and there are 4 database servers under MySQL-proxy:

master: 172.16.100.1

slave1:172.16.100.2

slave2:172.16.100.3

slave3:172.16.100.4

Current database version: "5.5.21

Configuration file: /etc/my.cnf

Data directory: /data/mysql

Operating system version: CentOS6.9

3. Upgrade ideas

Because the online business cannot be interrupted, we decided to kick one of the slaves out of the cluster first and upgrade it to MySQL 5.7. Then, we added the MySQL 5.7 device to the cluster. After the data was synchronized, we switched the master and slave nodes and upgraded the other slave nodes.

4. Practice

4.1. Log in to mysql-proxy and kick one of the slaves out of the cluster

mysql -uuser -ppwd -h127.0.0.1 -P234

View the backend proxy library

mysql> SELECT * FROM backends;

+-------------+-------------------+-------+------+
| backend_ndx | address | state | type |
+-------------+-------------------+-------+------+
| 1 | 172.16.100.1:3306 | up | rw |
| 2 | 172.16.100.2:3306 | up | ro |
| 3 | 172.16.100.3:3306 | up | ro |
| 4 | 172.16.100.4:3306 | up | ro |
+-------------+-------------------+-------+------+

5 rows in set (0.00 sec)
Kick service 172.16.100.4 out of the cluster
mysql> remove backend 4

4.2. Replace the installation directory of MySQL

Go to the official website of mysql and download the mysql5.7 version mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz

Unzip mysql5.7

tar -zxf mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz -C /usr/local/

If your previous mysql directory was created as a link, you only need to cancel the link and re-link it.

cd /usr/local/

unlink mysql

ln -s mysql-5.7.23-linux-glibc2.12-x86_64 mysql

If your previous mysql directory is not linked, then rename it and link another mysql directory.

cd /usr/local/

mv mysql mysql5.5

ln -s mysql-5.7.23-linux-glibc2.12-x86_64 mysql (It is recommended to use this method as much as possible, which is more convenient for upgrading)

4.3. Start mysql service

After MySQL 5.7, there is no need to initialize the database, that is, there is no need to execute the mysql_install_db command.

/usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --basedir=/usr/local/mysql --user=mysql --skip-grant-tables --skip-networking &

ps -ef |grep mysql

If there is an error, check the log information

4.4. Update the system table data dictionary information. The command is as follows:

/usr/loca/mysql/bin/mysql_upgrade

The output is:

There may be some error prompts "ALTER TABLE ** FROCE", don't worry about these errors, MySQL will automatically repair them later. The repair time may be a little long, depending on your database situation. It took me more than two hours to upgrade this time.

Finally, if the output is Upgrade process completed successfully, it means that the upgrade is successful.

4.5. Restart MySQL after the upgrade

/usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --basedir=/usr/local/mysql --user=mysql &

Check whether the upgrade is complete. If the upgrade is successful, the output is as follows

This installation of MySQL is already upgraded to 5.7.23, use --force if you still need to run mysql_upgrade

4.6.Re-configure the master-slave configuration

Redo the master-slave configuration

mysql> change master to master master_host="172.16.100.1",master_user="***",master_passowrd="***",master_log_file=" mysql-bin.000004", master_log_pos=1083;

mysql> start slave;

mysql> show slave status\G;

If there are two yess, it means it is normal.

4.7 Joining a read-write split cluster

Go to the mysql-proxy device

mysql -uuser -ppwd -h127.0.0.1 -P234

mysql> ADD SLAVE 172.16.100.4

mysql> select * from backends;

If the state is up, it means that the cluster has been successfully joined.

postscript:

This was my first time upgrading the database, and I wasn’t sure if the overall idea was right, so I tested it in a test environment first, and then after testing it for a few days and seeing no problems, I did the upgrade online. For reference only.

The pitfalls encountered during the upgrade from MySQL 5.5 to 5.7

The story goes like this. Our company has several old MySQL versions of 5.5. Recently, the project has been upgraded and several fields have been added. When using spring-data-jpa to automatically refresh the table structure, an error was reported because the MySQL version 5.5 does not support more than two current_timestamps as the default value in a table. So you should consider upgrading the database

First check the version on the server

Look at the source again

Oh, by the way, our company is using AWS's EC2, so all the sources are from amz

I tried yum update mysql-server and it didn't help, so I continued.

I read a lot of things online and they all said to uninstall the old version of the database first and then install the new version of the database. What should I do with my data then? To be on the safe side, I backed up the database first, one database at a time. I saw that the MySQL database also had a lot of users and permissions, so I backed up MySQL as well and then started working.

1. Uninstall the database

yum remove mysql-server

But the installation here seems to be different, so I uninstalled all the ones that yum found just now.

Remember to turn off the MySQL service before uninstalling, otherwise after installing the new version, use mysql --version to check whether it is 5.7 MySQL login or 5.5 and then restart the service and various problems will occur. Because some files were not deleted, the newly installed database looked very strange. . .

2. Install a new database

yum search mysql57

First see what versions are available, there is nothing to say about this.

yum install msyql57-server.x86_64

It's ok to install it, let's see what is installed

One strange thing is that mysql-config still depends on 5.5, but I didn't find any problems when using it.

3. Configuration

After installation, modify the following my.cnf

My server is in /etc/my.cnf. I don't know if other images have changed.

When configuring, I suddenly remembered that the data storage space is still there. I configured it to see if it can be used.

So I added this line in my.cnf (many configurations in 5.5 are deprecated, and the previous my.cnf must be deleted, otherwise an error will be reported when starting)

After configuration, start the service decisively

service mysqld start

Sure enough, I reported an error

Checked the startup log

[ERROR] Fatal error: mysql.user table is damaged. Please run mysql_upgrade.

ERROR: Zend_Db_Adapter_Exception: SQLSTATE[HY000] [2002] No such file or directoryAdditionally, an exception has occurred while trying to report this error: Zend_ExceptionNo entry is registered for key 'translate' (Abstract.php:144)

Damn, what should I do? I searched on Google and found this article: https://support.plesk.com/hc/en-us/articles/115001659169-MySQL-fails-to-start-mysql-user-table-is-damaged

The specific solutions are as follows

a. Log in to the server host

b. Add skip-grant-tables in /etc/my.cnf file under [mysqld] section means to enable safe mode (no password verification or anything)

c. service mysqld start

d.mysql_upgrade

e. service mysqld restart

I executed mysql_upgrade

I checked the official documentation, and the general meaning is that many things are not compatible after the upgrade. The official provides this script to repair the previous workspace

It was a seamless upgrade. All users in the MySQL database were still in the previous database and were not affected.

The upgrade is now complete

This is the end of this article about the detailed steps of upgrading Linux MySQL 5.5 to MySQL 5.7. For more relevant content about upgrading MySQL 5.5 to 5.7, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!