4

I'm bit green on MySQL upgrades, but my question has to do with single schemas (dbs). I have some hosted databases on a server using MySQL 5.1 Community, and I currently use mysqldump to back those databases up. When I go to restore them into the MySQL 5.7 server, do I need to run an upgrade on those databases to bring them "up to speed"?

The mysql_upgrade command seems to be related to all database within an instance, and I will be moving dbs one at a time into the server that has had dbs created from scratch there, as well as other dbs that have been moved to it from the previous version (5.1).

Improve this question
4
  • back them up using mysqldump on 5.1 and restore them on 5.7 and then you don't have to run mysql_upgrade.
    – Nawaz Sohail
    Dec 8, 2015 at 8:22
  • That was my plan, but does that leave me simply running a 5.1 formatted database in a 5.7 instance, or does the restore process make all the necessary translations? My 5.1 instance has the standard options declared, and the 5.7 is using the new default of InnoDB File-Per-Table Tablespaces. Any issues there?
    – SilkyTech
    Dec 8, 2015 at 18:47
  • shouldn't be an issue rather it is standard procedure to take backup using mysqldump and restore and if InnoDB File-Per-Table is enabled it will take effect as desired. Hope it helps
    – Nawaz Sohail
    Dec 9, 2015 at 10:33
  • difference could be in sql_mode settings and legacy table structure. Such as - 0000-00-00 00:00:00 default dates. By default they disabled in 5.7, so or upgrade schemas before, or set sql_mode = 0 and upgrade after
    – a_vlad
    Nov 23, 2016 at 9:44

1 Answer 1

Reset to default
3

Some things, such as microsecond in TIMESTAMP, will automatically be available because the CREATE TABLE on 5.7 will use the new format. innodb_file_per_table has a new default, so the tables will be laid out differently. That should not be a problem.

mysql_upgrade is for converting the mysql tables and for ALTERing to accommodate microseconds, etc, in an existing mysql instance where you are updating the software, not where you are loading the data.

Coming forward from 4.0 can cause troubles with changed syntax, the addition of CHARACTER SET, etc. But 5.1 is not that much different for what you are doing than 5.7.

There have been lots of changes/additions to mysql, information_schema, and performance_schema. So, be sure not to dump and reload them.

Use pt-show-grants if you need to carry GRANTs forward. Then check the results.

Do you have specific examples of things that concern you?

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.