MySQL upgrade detailed steps (including Percona)

MySQL Upgrade Steps MySQL 5.1.72 upgrade to MySQL 5.5.36

In view of the trouble I have encountered during the upgrade, I think it is necessary to make some details clear so as not to cause misunderstanding. Feel the upgrade steps on the official document are relatively simple to write, it is easy to confuse, feel the same as I did with the Official document learning Custom install MySQL as the egg hurts. It may be that the thinking of foreign sex is too big for me.

database situation: I tested Linux Generic x86_64 version, but also customized installation to the database directory.

Detailed upgrade Steps

1.MySQL 5.1.72 Boot Instance ins1

Suppose you already have MySQL 5.1.72 installed and successfully launched an instance of ins1. The software can be downloaded on the website MySQL 5.1.72.

2. Back up the INS1 data and configuration files my1.cnf

No matter how many times you have previously worked on the upgrade step, back up the data before upgrading it is necessary! I am here to test data, directly backed up by the mysqldump tool, if it is a production library, you can use the Percona company's online backup tool for backup.

Replicate a copy of the MY1.CNF to another location for backup, preventing the upgrade from failing to recover ins1 quickly.

3. Close Ins1

Mysqladmin-uroot-p-S $sockdir  

4. Ins1 the original data directory via MySQL 5.5.36 boot instance, named Ins2

1) Modify the my1.cnf to change some parameters that are incompatible with the high version or some obsolete parameters to a higher version acceptable parameter.

2) start with a high version of MySQL 5.5.36来, and remember not to start with a lower version of MySQL, so the my1.cnf changes will also make sense.

Because the higher version of the system library MySQL database and the lower version of MySQL database will have some tables are incompatible, so in order to let the high version of MySQL can start the lower version of the system data, need to add parameter--skip-grant-tables to ignore MYSQ L Database Authorization form (grant tables) validation. Also, after you start the MySQL instance ins2 by ignoring the authorization table, you can add the parameter--skip-networking to block the clients that are accessed over the network in order to secure the MySQL instance.

--skip-grant-tables--skip-networking &

5. Executive Mysql_upgrade

After launching the instance Ins2 successfully, upgrade the INS2 via the bin directory mysql_upgrade command for the high version of MySQL 5.5.36

Mysql_upgrade-uroot-p-S $socket _path

What this step actually does (from the manual):

Mysqlcheck--all-databases--check-upgrade--auto-repairmysql < Fix_priv_tablesmysqlcheck--all-databases-- Check-upgrade--fix-db-names--fix-table-names

If you see OK on the last side, that means it's successful.

... mysql.time_zone_transition_type                    okmysql.user                                         okpercona51.ptablet                                  okrunning ' Mysql_fix_privilege_ Tables ' ... Ok

6. Restart MySQL instance ins2

Binary package with MySQL 5.5.36 (not MySQL 5.1.72)

Mysqladmin-uroot-p-S $sockdir  shutdown#这次正常启动, no additional parameters are required. mysqld_safe--defaults-file= $cnf _path &

Percona server 5.1.61 upgrade to Percona server 5.5.39

Percona Server is a new, free MySQL version of Percona, which is improved based on some of the performance flaws that exist in MySQL. Their upgrade document is three words to describe is "too simple", in order to take some steps to clear, but also for me to take a little detour, I still take my upgrade step detailed list, for later review it.

This upgrade I started from the installation of Percona Server instance, because the Percona configuration file and MySQL is still partially different, the novice with the binary file installation, you may need to find the execution file.

1. Install Percona Server 5.1.61 instance P1

1) Download Percona Server 5.1.61

2) Unzip the file and go to the installation directory.

I'm here,/home/devops/percona-server-5.1.61.

3) file According to configuration file : Percona5.1.txt install system Library Directory

This directory is for my own installation directory, parameters need to be adjusted according to their own OS. The main points to note are the following several parameters:

#这两个参数在高版本的参数文件中需要调整ledir           =/home/devops/percona-server-5.1.61/libexeclanguage                

After the profile is finished, install the system library. Execute command:

./bin/mysql_install_db--defaults-file=/home/devops/cnf/mysql4308.cnf--basedir=/home/devops/ percona-server-5.1.61

4) Verify that the system library is successfully installed

To verify that the system library is installed successfully, you can see if there are Mysql,test folders under the Data-dir directory specified in the parameters file, and if there are many system files under the MySQL folder that are at the end of frm, MYI, MYD.

5) Launch Instance P1

$ Cd/home/devops/percona-server-5.1.61/bin./mysqld_safe--defaults-file=/home/devops/cnf/mysql4308.cnf PS- EF | grep 4308 Verification is successful, if not, please look at the MySQL error log, if there is a problem with the parameters.

2. Back up the 5.1 parameter file, modify the parameter file to meet the parameter requirements of Percona 5.5 (important!! ）

Backup MYSQL4308.CNF: $ cp mysql4308.cnf MYSQL4308_BAK.CNF. Modify the MYSQL4308.CNF to meet the Percona Server 5.5.36 parameter requirements, the specific differences can be viewed Percona document, I have the main changes in the operation of the following several parameters:

Ledir           =/home/devops/percona/percona5.5.39/binlc-messages-dir         =/home/devops/percona/percona5.5.39/share

Modified parameter file file :P ercona5.5.txt

3. Close the instance P1 and launch the instance (skip-grant-tables) with Percona Server 5.5.39 P2

1) Download Percona Server 5.5.39 http://www.percona.com/redir/downloads/Percona-Server-5.5/Percona-Server-5.5.39-36.0/ Binary/tarball/percona-server-5.5.39-rel36.0-697.linux.x86_64.tar.gz, unzip.

2) Restart MySQL

This is the first time to use the high version of the MySQL software to start the lower version of the data, to avoid the authorization table validation, need to add skip-grant-tables parameters.

Cd/home/devops/percona/percona5.5.39/bin./mysqladmin-uroot-p-s/home/devops/mysql4308/mysqld.sock  shutdown. /mysqld_safe--defaults-file=/home/devops/cnf/mysq4308.cnf--skip-grant-tables--skip-networking &

Note: The last step in the execution of Mysqld_safe, it is likely that the parameter file parameters can not be recognized as a result of a startup error, please correct the error log as prompted.

4. Execute the Mysql_upgrade of Percona Server 5.5.36

./mysql_upgrade-uroot-p-s/home/devops/mysql4308/mysqld.sock

After execution, if the following condition occurs, the OK is indicated.

... mysql.user                                         okpercona51.ptablet                                  okrunning ' mysql_fix_privilege_tables ' ... Ok

5. Close the instance P2 and restart MySQL

Cd/home/devops/percona/percona5.5.39/binmysqladmin-uroot-p-s/home/devops/mysql4308/mysqld.sock Shutdown./mysqld _safe--DEFAULTS-FILE=/HOME/DEVOPS/CNF/MYSQ4308.CNF &

6. Verify that it is successful

mysql> status--------------./mysql  Ver 14.14 distrib 5.5.39-36.0, for Linux (x86_64) using ReadLine 5.1....Server Version:         5.5.39-36.0-log Percona Server (GPL), Release 36.0, Revision 697 ....

You can see that the version is 5.5.39-36.0.

Percona Server 5.1.61 upgrade to MySQL 5.5.36

Perhaps because Percona and MySQL are inconsistent with the way the system tablespace is interpreted, I found that I was unable to start an instance of Percona Server 5.1.61 with MySQL 5.5.36来 after the upgrade. The error log indicates that the shared table space cannot be opened, temporarily not so cross-software upgrade, later when idle can look at the difference between the two.

At the moment just know Percona to MySQL to do some performance optimization and expansion.

Percona5.1.txt

[Client]Port= 4308Socket=/home/devops/mysql4308/mysqld.sock[Mysqld_safe]User=Devopsnice= 0#ledir=/home/devops/percona/percona5.5.39/binledir=/home/devops/percona-server-5.1.61/libexec#ledir=/home/mysql/#mysqld=@home_mysql_mysql_libexec_mysqld[mysqld]Server-id= 2949bind-address=Localhostport= 4308Pid-file=/home/devops/mysql4308/mysqld.pidsocket=/home/devops/mysql4308/mysqld.sockbasedir=/home/devops/percona/percona-server-5.1.61DataDir=/home/devops/mysql4308innodb_data_home_dir=/home/devops/mysql4308innodb_log_group_home_dir=/home/devops/mysql4308tmpdir=/home/devops/mysql4308log-error=/home/devops/mysql4308/mysqld.logslow_query_log= 2Slow_query_log_file=/home/devops/mysql4308/mysql-slow.log Log_bin=/home/devops/mysql4308/mysql-bin.logbinlog_format=Mixedrelay-log=Mysqld-relay-binuser=Devopslanguage=/home/devops/percona-server-5.1.61/share/mysql/english#lc-messages-dir=/home/devops/percona/percona5.5.39/sharetable_cache= 512Long_query_time= 5max_connections= 300Query_cache_type= 0Character-set-server=Utf8#default-character-set=Utf8default-storage-engine=innodbskip-external-lockingexpire_logs_days= 7max_binlog_size=100mmax_allowed_packet=16m# InnoDB plugininnodb_buffer_pool_size=256minnodb_data_file_path=ibdata1:64m:autoextendinnodb_autoextend_increment= 16Innodb_log_files_in_group= 2innodb_log_file_size=64minnodb_lock_wait_timeout= 5Innodb_flush_method=O_directinnodb_flush_log_at_trx_commit= 0innodb_file_per_table= 1Innodb_file_format=Barracudainnodb_file_format_check=On#innodb_adaptive_checkpoint=Estimate#innodb_expand_import= 1innodb_read_io_threads= 8#innodb_stats_method=Nulls_unequal#innodb_strict_mode= 1innodb_thread_concurrency= 12innodb_write_io_threads= 3innodb_read_io_threads= 3innodb_io_capacity= 200#innodb_adaptive_flushing=false# perconainnodb_page_size=8k#innodb_extra_rsegments=8#innodb_use_purge_thread=4#innodb_stats_update_need_lock=0#innodb_fast_checksum=1#log_slow_verbosity=full#userstat_running=1#enable_query_response_time_stats=1# Percona for Ssd#innodb_adaptive_checkpoint=3#innodb_flush_neighbor_pages=0[mysqldump]Quickquote-namesmax_allowed_packet=16mdefault-character-set=UTF8[MySQL]Default-character-set= UTF8

View Code

Percona5.5.txt

View Code

MySQL upgrade detailed steps (including Percona)