Lessons Upgrading MySQL DB 5.1 to Percona 5.7

I just recently upgraded a database server that were previously running MySQL 5.1 (standard, Sun/Oracle version) into Percona Server 5.7. A few quirks notable enough to warrant this blog post.

Planning and Preparation

A Percona blog post (mysql-upgrade-best-practices) stated that the best way to upgrade with such huge difference in major version (5.1 to 5.7) is to do a full logical dump for all database except mysql, dump user and grants, uninstall database and remove datafiles, then install new version and import the logical dump and grants. But alas the database we are going to upgrade is so big and the IO subsystem became some sort of bottleneck when doing logical dump, our colleagues tried to do mysqldump and it tooks more than 2 days to run, prompting us to cancel the backup (otherwise it would interfere with workday application usage of the database).  Reading the blog I noted that :
  1. for major version upgrade, using logical dump  and restore dump is the safest way to go.
  2. for minor version upgrade, in-place upgrade is possible.
  3. for major version upgrade, do not skip versions. I deduced that the sequence is : 5.1 ⇨ 5.5 ⇨ 5.6 ⇨  5.7
  4. two mandatory reading for my upgrade  - http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previous-series.html and http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html
  5. another two mandatory reading for percona server : https://www.percona.com/doc/percona-server/5.5/upgrading_guide_51_55.html#changes-in-server-configuration and https://www.percona.com/doc/percona-server/5.6/changed_in_56.html
The main thing is to be careful. In IT world, to be careful is to do backups before you do something big.
Backing up is mandatory for any upgrade task relating to production system. Our database server is running on VMWare platform, so we prepared to do a snapshot before doing the upgrade. But alternative backup is in order, so we installed Percona Xtrabackup to create such backup. Another option is to use mydumper (see https://www.percona.com/blog/2015/11/12/logical-mysql-backup-tool-mydumper-0-9-1-now-available/), but in the past I were having problems compiling it so I avoid it.

Percona Xtrabackup

Being an open-source software, it is strange that the Percona Xtrabackup PDF manual is not easily found. Need to register your name, address, company, et cetera, just to get a hold of the PDF version of the manual. Anyway, the quirk is, sometimes the manual said 'innobackupex' and sometimes said 'xtrabackup'. Checking the installed file reveals that innobackupex is symbolic-link to xtrabackup.. seems that the two are now interchangeable, but no such statement found in the PDF manual. 
Quoting the Percona Xtrabackup 2.4.8 PDF manual in chapter Ten :
innobackupex is the tool which provides functionality to backup a whole MySQL database instance using the xtrabackup in combination with tools like xbstream and xbcrypt. 
The paragraph above is confusing, because seems that the correct way to do backup is by using innobackupex. But in the web version of the manual we get :

innobackupexinnobackupex is the symlink for xtrabackup. innobackupex still supports all features and syntax as 2.2 version did, but is now deprecated and will be removed in next major release.

Seems that the web version is the best one to follow, unfortunately I noticed this after completing the backup task.
Our backup command is like this : 
xtrabackup --backup --compress --compress-threads=4  --target-dir=/targetdir/compressed/ --user=root --password=xxxxxx
One quirk is xtrabackup doesn't like the internal innodb engine (error : Built-In InnoDB 5.1 is not supported in this release), so I need to add these in /etc/my.cnf and restart the db before doing the xtrabackup command :

ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.so

Replacing MySQL 5.1 with Percona Server 5.1

To replace mysql 5.1, we uninstall them and then install percona server. Both (mysql and percona server) are using my.cnf. The steps are :
  1. stop mysqld service (note: mysqld is the Oracle-based service, percona-based service in RHEL is mysql)
  2. ensure backups are done, if not, do create one backup.
  3. uninstall mysql by : yum remove mysql mysql-server
  4. install percona server by : yum install Percona-Server-client-51 Percona-Server-server-51 (refer to the steps in yum-related installation in https://www.percona.com/doc/percona-server/5.1/installation.html)
These steps quite straightforward, but Percona Server 5.1 don't like the innodb engine plugin that were installed before (Error: The option ignore-builtin-innodb is incompatible with Percona Server with XtraDB), forcing me to remove/comment these two  lines :


#ignore-builtin-innodb#plugin-load=innodb=ha_innodb_plugin.so
After that and starting the mysql service (not mysqld), all is well.

Upgrading Percona Server 5.1 to 5.5

  1. stop mysql service
    • service mysql stop
  2. check installed packages 
    • rpm -qa | grep Percona-Server
  3. uninstall 
    • rpm -qa | grep Percona-Server | xargs rpm -e --nodeps
  4. install 5.5 version by 
    • yum install Percona-Server-server-55 Percona-Server-client-55
  5. run in skip grant tables mode:
    • /usr/sbin/mysqld --skip-grant-tables --user=mysql &
  6. then do the actual upgrade process:
    • mysql_upgrade
  7. stop and start :
    • service mysql stop
    • service mysql start
The process run smoothly with no quirks.

Upgrading Percona Server 5.5 to 5.6


For 5.5 ⇨  5.6 upgrade, similar steps are found in https://www.percona.com/doc/percona-server/5.6/upgrading_guide_55_56.html :
  1. stop mysql service
    • service mysql stop
  2. uninstall by 
    • rpm -qa | grep Percona-Server | xargs rpm -e --nodeps
  3. install by
    • yum install Percona-Server-server-56 Percona-Server-client-56
  4. run in skip grant tables mode:
    • /usr/sbin/mysqld --skip-grant-tables --user=mysql &
  5. then do the actual upgrade process:
    • mysql_upgrade
  6. stop and start :
    • service mysql stop
    • service mysql start
In step 4, the server refused to start because of the unknown 'log_slow_queries' option in /etc/my.cnf. Seems that in 5.6 this is replaced by slow_query_log_file (see https://stackoverflow.com/questions/10755151/mysql-what-is-the-difference-between-slow-query-log-vs-log-slow-queries), so I replace log_slow_queries with slow_query_log_file.
After that resolved, we proceed to step 5 we found another error :
mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect
This error pops up when I tried to run mysql_upgrade. Seems this is a known bug (https://bugs.mysql.com/bug.php?id=72896) that  didn't  get fixed, in which mysql_upgrade calls flush privileges which rereads grant tables . Our solution is to use alternate syntax to execute mysql_upgrade :
mysql_upgrade -u root -p 
 After doing that, all is well.

Upgrading Percona Server 5.6 to 5.7

The reference for the 5.6 ⇨ 5.7  upgrade is https://www.percona.com/doc/percona-server/5.7/upgrading_guide_56_57.html. Which essentially the same with 5.5 ⇨ 5.6 upgrade, so I would not duplicate here. But there is a few major difference :

  • By default, mysql_upgrade will convert tables that are using  date,time, and timestamp to the MySQL 5.6.4 format (note that the 5.6 upgrade process does not issue a warning about these  tables, and doesn't suggest conversion process either). The new binary date/time format are more space-efficient and allows extension types (such as TIMESTAMP(4)) with fractional seconds. Refer to https://www.percona.com/blog/2016/04/27/upgrading-to-mysql-5-7-focusing-on-temporal-types/, the majority running time of mysql_upgrade are now spent converting these tables (shown as ALTER TABLE ... FORCE). A workaround to prevent this is to run mysql_upgrade with -s flag / --upgrade-system-tables flag, which does not upgrade data tables.
  • Default sql_mode is now includes  ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES. The only_full_group_by now fixes enforces group by to be in proper form, but unfortunately this breaks many sloppy SQLs that previously allowed to run. The strict_trans_tables changes the mysql behavior on INSERT and UPDATE queries regarding invalid field contents.

Conclusion

In-place upgrades from 5.1 to 5.7 are indeed feasible, especially when the data size is quite large and mysqldump takes too much time to run. For backups, Percona Xtrabackup will perform the task with better speed than mysqldump (but with binary backup as a result).





Comments

Anonymous said…
Coin Casino - Play Free Games No Deposit
Coin Casino gives you a chance 바카라사이트 to enjoy your favorite casino games with a bonus of up to 500x when 인카지노 you sign up. No deposit required, just play and 메리트 카지노 주소 have fun
January 29, 2022 at 4:17 AM
Post a Comment

Popular posts from this blog

Long running process in Linux using PHP

Background To do stuff, I usually create web-based applications written in PHP. Sometimes we need to run something that takes a long time, far longer than the 10 second psychological limit for web pages. A bit of googling in stack overflow found us this  http://stackoverflow.com/questions/2212635/best-way-to-manage-long-running-php-script , but I will tell the similar story with a different solution. One of the long running tasks that need to be run is a Pentaho data integration transformation. Difficulties in long running PHP scripts I encountered some problems when trying to make PHP do long running tasks : PHP script timeout. This could be solved by running set_time_limit(0); before the long running tasks. Memory leaks. The framework I normally use have a bit of memory issues, this can be solved either by patching the framework (ok, it is a bit difficult to do, but I did something similar in the past ) or splitting the data to process into several batches. And if you
Read more

Reverse Engineering Reptile Kernel module to Extract Authentication code

Image
 This time I will show how to (partially) reverse engineer a linux kernel module. The linux kernel module is larger than usual kernel module because it is actually a Reptile-based rootkit dropped by some hacker.  Part 1. Get basic module information First we gather basic module info by using modinfo : [root@lb lkrg-0.9.1]# modinfo falc0n filename:       /lib/modules/3.10.0-1127.10.1.el7.x86_64/kernel/sdc/falc0n.ko intree:         Y license:         GPL retpoline:       Y rhelversion:     7.8 srcversion:     81F508029A53F7490CCDB44 depends:         vermagic:       3.10.0-1127.10.1.el7.x86_64 SMP mod_unload modversions   We could also refer to the kernel module file not yet installed in the OS : [root@lb lkrg-0.9.1]# file falc0n.ko falc0n.ko: ELF 64-bit LSB relocatable, x86-64, version 1 (SYSV), BuildID[sha1]=1bde6abf4732eb620983032a47fbcf07689ece11, not stripped [root@lb lkrg-0.9.1]# modinfo falc0n.ko filename:       /root/lkrg-0.9.1/falc0n.ko intree:         Y license:         GPL retp
Read more

SAP System Copy Lessons Learned

Image
Background Earlier this year I was part of a team that does System Copy for a 20 terabyte plus SAP ERP RM-CA System. And just now I am involved in doing two system copy in just over one week, for much lesser amount of data. I think I would note some lessons learned from the experience in this blog. For the record, we are migrating from HP/UX and AIX to Linux x86 platform. Things that go wrong First, following the System Copy guide carefully is quite a lot of work - mainly because some important stuff are hidden in references in the guide. And reading a SAP note that are referenced in another SAP note, that are referenced in Installation Guide.. is a bit too much. Let me describe what thing goes wrong. VM Time drift The Oracle RAC Cluster have time drift problem, killing one instance when the other is shutting down. The cure for our VMWare-based Linux database server is hidden in SAP Note 989963 "Linux VMWARE Timing", which is basically add a tinker panic 0 in the
Read more