MySQL ' in place ' upgrade practice, from 5.1 to 5.7

After the translation of the article: use the' in ' method to upgrade directly from 5.0 to 5.7, where I recorded the process of practice.

1. Environment preparation

Database A

Version: 5.1

Character Set: Latin1

Engine: MyISAM

Database volume: approx. 220G

Table Data Volume: approx. 600

Server: centos6.5

2. Upgrade process

have set up the 5.1 version of the data from the library, and so on after the master-slave synchronization, began to upgrade from the library

• There was a problem with the first upgrade and the execution of the mysql_upgrade display table was corrupted.

Before we upgrade, we first check and fix the table (MyISAM engine).

./bin/mysqlcheck-uroot-h
 
 

  
  
 
   
Stop master and slave, record the current location point
 
  
 
 
Stop slave;show slave status;
Record the current location point and configure the Master to never start with the database in the configuration file
650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M01/98/49/wKioL1k5eySBGGVYAAA_w1ir-Gk031.png-wh_500x0-wm_ 3-wmp_4-s_2944313875.png "title=" 13267474-ba54-4bcb-87b5-f6184041b9c4.png "alt=" Wkiol1k5eysbggvyaaa_ W1ir-gk031.png-wh_50 "/>

 
 

  
  
 
   
Close the 5.1 version database
 
  
 
 
650) this.width=650; "Src=" https://s5.51cto.com/wyfs02/M02/98/49/wKioL1k5ezCA7OQFAAAWRCrQagA480.png-wh_500x0-wm_ 3-wmp_4-s_1836053518.png "title=" 26359a4c-c297-4978-8c71-a6a591fa566a.png "alt=" Wkiol1k5ezca7oqfaaawrcrqaga480.png-wh_50 "/>
/etc/init.d/mysqld stop Orcd/usr/local/mysql51./bin/mysqladmin-uroot--socket=/var/lib/mysql.sock-p ' <PASSWORD > ' Shutdown
 
 

  
  
 
   
Modify the 5.7 version configuration file (specifically which parameters have changed, please read the documentation, upgrading to 5.1, upgrading to 5.5 , upgrading to 5.6 or upgrading to 5.7.
 
  
 
 
I commented out the following items:
<innodb related, comment out the configuration for undo, because the undo file can only be created at initialization time, or the innodb_undo_tablespaces can be set to 0, for convenience, there will be problematic configuration comments using the default configuration > #innodb_ data_file_path          = ibdata1:1024m:autoextend# innodb_file_per_table          = 1  #innodb_undo_ Directory          = <datadir> #innodb_undo_logs              = 128   #innodb_ undo_tablespaces         = 3    #innodb_undo_ log_truncate        = 1#innodb_max_undo_log_size   &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;=&NBSP;1G Copying related #gtid_mode                   = on#enforce_gtid_consistency        &nbsP;= true#master_info_repository         = table#relay_ log_info_repository       = table#slave_parallel_workers          = 2< Modify to fit 5.1 version >binlog_checksum                = NONEsql_mode                    = ' NO_AUTO_CREATE_ User,no_engine_substitution '
 
 

  
  
 
   
After the configuration file has been modified, go to the 5.7 version folder, open the database, note the use--skip-grant-tables
 
  
 
 
Cd/usr/local/mysql57./bin/mysqld_safe--defaults-file=.  /MY.CNF--datadir=<datadir>--basedir=. --port=< port>--socket=/var/lib/mysql.sock--skip-grant-tables &
 
 

  
  
 
   
Note: If there is no problem then start complete, the problem I encountered is that Mysqld_safe needs to be executed within the folder (/usr/local/mysql57 )
 
   
Run Mysql_upgrade
 
  
 
 
./bin/mysql_upgrade-uroot--socket=/var/lib/mysql.sock
 
 

  
  
 
   
Simply say the upgrade process,Mysql_upgrade first check the system library and repair, check and install the SYS library, performance library. However, the 5.1 default test library was not removed. Then start checking the business library to fix the incompatible columns. The main hints I encountered during the test were:
 
  
 
 
Note:time/timestamp/datetime columns of old format has been upgraded to the new format.
upgrade The SQL to fix the problem is:
ALTER TABLE ' <DB> '. ' <TABLE> ' force
Since 5.6.6 modified some of the behavior of time/timestamp/datetime , so need to upgrade, about the specific changes please move to the official document

 
 

  
  
 
   
The error that I encountered
 
  
 
 
1. There is a corrupted table and cannot be upgraded. You need to repair tablebefore upgrading, be aware that you should use version 5.1
2. Table does not support upgrade
error:unable to open underlying tables which is differently defined or of Non-myisam type or doesn ' t exist the table engine to check for errors Mrg-myisam. Need to manually re-create, go to the main library to export the table statement, delete 5.7 of the table, and then execute the CREATE TABLE statement can be
 
 

  
  
 
   
Restart database
 
  
 
 
Once you've run mysql_upgrade , you'll need a password to log in again.
./bin/mysqladmin-uroot-h
650) this.width=650; "Src=" https://s3.51cto.com/wyfs02/M02/98/49/wKioL1k5e02R6d2yAAAp42Sp1Mk740.png-wh_500x0-wm_ 3-wmp_4-s_549106543.png "title=" Baa29b7e-52cc-451e-982f-ed5b465983a2.png "alt=" Wkiol1k5e02r6d2yaaap42sp1mk740.png-wh_50 "/>

 
  

  
   
 
    
Check Again
 
   
 
  
./bin/mysqlcheck-uroot-h
The database used does not use triggers, stored procedures, etc., because there is no error in this step. If used, you can decide whether to repair or rebuild according to the information you are prompted
Check that the master and slave are open normally.
650) this.width=650; "Src=" https://s2.51cto.com/wyfs02/M00/98/49/wKiom1k5e17x3wItAABMUAyAfsg017.png-wh_500x0-wm_ 3-wmp_4-s_4290432077.png "title=" 8b9f4264-1744-4dbd-b8cc-71e3f5b648b1.png "alt=" Wkiom1k5e17x3witaabmuayafsg017.png-wh_50 "/>
According to the error message, the relay log should be recorded for the problem, we restart the next master and slave.
Stop Slave;start Slave;
At this time the master and slave has returned to normal, find the solution on the internet is reset slave, if the restart no matter what can only be re-built, fortunately, before the upgrade record location points.

 
   

  
    
 
     
Now the database has been able to provide use, the entire process of check consumption of about 1 hours,upgrade consumption of about 4 hours. This will then be compared to the 'dump' upgrade method. There is also a need for some data-checking work, which is not continued because of the lack of understanding of the data content.
 
    
 
   
This article is from the "Amnesiasun" blog, make sure to keep this source http://amnesiasun.blog.51cto.com/10965283/1933652
MySQL ' in place ' upgrade practice, from 5.1 to 5.7