Shulou(Shulou.com)06/01 Report--

Recently, a lot of MySQL loopholes have been scanned. There is no good way to upgrade to the latest version first. When combing through the MySQL database information, it is found that there is a set of zabbix using MySQL version 5.1.71, the latest version is 5.7.17, the version span is too large, I do not know whether the direct upgrade is feasible. The amount of data in the library is about 15 gigabytes, so I want to back up first, and then upgrade with mysql_upgrade. If it really doesn't work, create a new library to import the data.

The following are the upgrade steps tested on the virtual machine, and some problems were encountered, but fortunately, the upgrade was successful in the end, indicating that this path is still feasible.

Operating system version RHEL 6.7 the old MySQL version 5.1.71, the new MySQL version 5.7.17.

1. View the old version of MySQL and create a test database and test table

Mysql > select version (); +-+ | version () | +-+ | 5.1.71-community | +-+ mysql > create database zx;Query OK, 1 row affected (0.00 sec) mysql > show databases +-+ | Database | +-+ | information_schema | | mysql | | test | | zx | +-+ 4 rows in set (0.00 sec) mysql > use zx;Database changedmysql > create table test_upgrade (id int) Query OK, 0 rows affected (0.07 sec). MySQL > select count (*) from test_upgrade;+-+ | count (*) | +-+ | 33554432 | +-+ 1 row in set (33554432 sec)

Now there are 33554432 pieces of data in the test_upgrade table.

2. Stop the old MySQL and backup the data

[root@rhel6 mysql] # service mysql stopShutting down MySQL.. SUCCESS! # here back up data directly with cp [root@rhel6 lib] # cp-R mysql mysql_ bak [root @ rhel6 lib] # du-sk mysql*250880 mysql250880 mysql_bak

3. Install the new Msql software. What is downloaded here is the compiled and decompressed software that can be used.

[root@rhel6 ~] # tar-zxvf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz # after decompression, move to / opt directory [root@rhel6 ~] # mv mysql-5.7.17-linux-glibc2.5-x86_64 mysql-5.7.17 [root@rhel6 ~] # mv mysql-5.7.17 / opt [root@rhel6 ~] # cd / opt [root@rhel6 opt] # chown-R mysql:mysql mysql-5. 7.17/ [root@rhel6 opt] # lltotal 4drwxr-xr-x. 9 mysql mysql 4096 Mar 22 21:07 mysql-5.7.17

4. Modify my.cnf

Mainly modify basedir parameters:

[root@rhel6 opt] # vi / etc/my.cnfbasedir=/opt/mysql-5.7.17

5. Start a new MySQL

[root@rhel6 mysql-5.7.17] #. / bin/mysqld_safe &

1) start error 1:

2017-03-22T13:12:39.131998Z 0 [ERROR] InnoDB: The Auto-extending innodb_system data file'. / ibdata1' is of a different size 640 pages (rounded down to MB) than specified in the .cnf file: initial 768 pages Max 0 (relevant if non-zero) pageslinks 2017-03-22T13:12:39.132028Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error2017-03-22T13:12:39.733953Z 0 [ERROR] Plugin 'InnoDB' init function returned error.2017-03-22T13:12:39.733986Z 0 [ERROR] Plugin' InnoDB' registration as a STORAGE ENGINE failed.2017-03-22T13:12:39.733994Z 0 [ERROR] Failed to initialize plugins.2017-03-22T13:12:39.734000Z 0 [ERROR] Aborting

The solution is to add the following parameters to the [mysqld] section of my.cnf

Innodb_data_file_path = ibdata1:10M:autoextend

Start again

2) start error 2:

2017-03-22T13:16:03.374717Z 0 [ERROR] unknown option'--skip-locking'2017-03-22T13:16:03.374735Z 0 [ERROR] Aborting

The solution is to comment out the relevant parameter skip-locking from my.cnf

Start again

3) start error 3:

2017-03-22T13:18:20.278752Z 0 [ERROR] Fatal error: mysql.user table is damaged. Please run mysql_upgrade.2017-03-22T13:18:20.278954Z 0 [ERROR] Aborting

5.7 unable to read mysql.user table of 5.1. The solution uses the-- skip-grant-tables parameter to skip authorization verification.

Start again

[root@rhel6 mysql-5.7.17] #. / bin/mysqld_safe-skip-grant-tables&

Started successfully

2017-03-22T13:20:23.919677Z 0 [Note]. / bin/mysqld: ready for connections.Version: '5.7.17' socket:' / var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)

6. Perform the upgrade

[root@rhel6 mysql-5.7.17] #. / bin/mysql_upgrade-urootChecking if update is needed.Checking server version.Running queries to upgrade MySQL server.Checking system database.mysql.columns_priv OKmysql.db OKmysql.engine_cost OKmysql.event OKmysql.func OKmysql.general_log OKmysql.gtid_executed OKmysql.help_category OKmysql.help_keyword OKmysql.help_ Relation OKmysql.help_topic OKmysql.host OKmysql.innodb_index_stats OKmysql.innodb_table_stats OKmysql.ndb_binlog_index OKmysql.plugin OKmysql.proc OKmysql.procs_priv OKmysql.proxies_priv OKmysql.server_cost OKmysql.servers OKmysql.slave_master_info OKmysql.slave_relay_log_info OKmysql.slave_worker_info OKmysql.slow_log OKmysql.tables_priv OKmysql.time _ zone OKmysql.time_zone_leap_second OKmysql.time_zone_name OKmysql.time_zone_transition OKmysql.time_zone_transition_type OKmysql.user OKUpgrading the sys schema.Checking databases.sys.sys_config OKzx.test_upgrade OKUpgrade process completed successfully.Checking if update is needed.

7. Restart MySQL and verify

[root@rhel6 mysql-5.7.17] #. / bin/mysqladmin-uroot-p shutdown [root@rhel6 mysql-5.7.17] #. / bin/mysqld_safe & mysql > select version (); +-+ | version () | +-+ | 5.7.17 | +-+ 1 row in set (0.00 sec) mysql > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | sys | | test | | zx | +-+ 6 rows in set (0) .00 sec) mysql > select count (*) from zx.test_upgrade +-+ | count (*) | +-+ | 33554432 | +-+ 1 row in set (sec)

After the verification passes, delete the old version of the MySQL software.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.