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

Follow up the previous translated article: use the'In place' method to upgrade directly from 5. 0 to 5. 7, and record my practice here.

1. Environmental preparation

Database A

Version: 5.1

Character set: latin1

Engine: MyISAM

Database size: about 220g

Table data: about 600

Server: centos6.5

2. Upgrade process

The slave library of version 5.1 data has been built, and after the master-slave synchronization, the slave database will be upgraded.

Encountered a problem during the first upgrade and performed mysql_upgrade to show table corruption.

Before upgrading, we first check and repair the table (MyISAM engine).

. / bin/mysqlcheck-uroot-h-P-p-r-A

Stop master and slave, record the current location point

Stop slave;show slave status

Record the current location point and configure the master in the configuration file to never start with the database

Shut down the version 5.1 database

/ etc/init.d/mysqld stop orcd / usr/local/mysql51./bin/mysqladmin-uroot-- socket=/var/lib/mysql.sock-paired 'shutdown

Modify the version 5.7configuration file (for more information on which parameters have changed, please read the documentation, upgrading to 5.1,5.5,5.5,5.6or upgrading to 5.7for upgrading to.

I commented out the following items:

# innodb_data_file_path = ibdata1:1024M:autoextend#innodb_file_per_table = 1 innodbb _ undo_directory = # innodb_undo_logs = 128 # innodb_undo_tablespaces = 3 # innodb_undo_log_truncate = 1#innodb_max_undo_log_size = 1G replication related # gtid_mode = on#enforce_ Gtid_consistency = true#master_info_repository = TABLE#relay_log_info_repository = TABLE#slave_parallel_workers = 2binlog_checksum = NONEsql_mode = 'NO_AUTO_CREATE_USER NO_ENGINE_SUBSTITUTION'

After the configuration file is modified, go to the version 5.7 folder, open the database, and pay attention to using-- skip-grant-tables

Cd / usr/local/mysql57./bin/mysqld_safe-defaults-file=../my.cnf-datadir=-basedir=. -- port=

-socket=/var/lib/mysql.sock-skip-grant-tables &

NOTE: start to complete if there is no problem. The problem I encounter is that mysqld_safe needs to be executed in the folder (/ usr/local/mysql57).

Run mysql_upgrade

. / bin/mysql_upgrade-uroot-- socket=/var/lib/mysql.sock

Briefly talking about the upgrade process, mysql_upgrade first checks the system library and repairs, checks and installs the sys library and the performance library. However, the default test library of 5.1 has not been removed. Then start checking the business library to fix incompatible columns. The main tips I encountered in the test were:

Note: TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.

The sql for upgrade to fix this problem is:

ALTER TABLE ``.`` FORCE

Since some behaviors of TIME/TIMESTAMP/DATETIME have been modified after 5.6.6, it needs to be upgraded. Please refer to the official file for specific changes.

The mistake encountered

1. There is a damaged table and cannot be upgraded. Repair table is required before upgrading. Note that version 5.1 should be used.

two。 Table does not support upgrade

Error: the table engine used by Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist to check for errors is MRG-MYISAM. If you need to recreate it manually, go to the main library to export the table creation statement, delete the 5.7 table first, and then execute the create table statement.

Restart the database

After running mysql_upgrade, you need a password to log in again.

. / bin/mysqladmin-uroot-h-P-p shutdown./bin/mysqld_safe-- defaults-file=../my.cnf-- datadir=-- basedir=. -- port=

-socket=/var/lib/mysql.sock-skip-grant-tables &

Check again

. / bin/mysqlcheck-uroot-h-P-p-A

The database used does not use triggers, stored procedures, etc., because there is no error at this step. If used, you can decide whether to repair or rebuild according to the prompt.

Check to see if the master and slave are open properly.

According to the error message, we restart the master and slave for the problem that should be recorded by relay log.

Stop slave;start slave

At this time, the master and slave has returned to normal, the solution to find the Internet is reset slave, if the restart does not work, it can only be rebuilt, fortunately, the location points were recorded before the upgrade.

Now that the database is ready for use, it takes about 1 hour for check and about 4 hours for upgrade. It will then be compared with the 'dump' upgrade method. Later, some data checking work was needed, but it did not continue because of the lack of understanding of the data content.

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.