My home computer is running mysql. For updating (with reinstallation) I made backup /var/lib/mysql . Now I put mysql 5.7, previously it was 5.6. How to import old databases from backup?
UPD: judging by the answer, I just need to “migrate the base”.

  • mysql database upgrade itself. Why import them? - strangeqargo
  • @strangeqargo: If so, write the answer. - val

1 answer 1

in modern mysql Linux, with an upgrade, he himself is engaged in the migration of a database ( proof )

therefore, it is worth making a backup before the upgrade, but you only need to worry about "migrating the database"

  1. you for some reason have moved the database files somewhere and now you are thinking how to migrate them from the backup
  2. found sudden errors when working with the new version of mysql

in the first case - you can use the migration script, but, IMHO, in order not to break your head, it is easier to demolish the new mysql, put the old one, copy the database files to the old location and roll back the new mysql again.

perhaps (and it would not be surprising) if mysqld itself checks the versions of files and migrates them itself without any scripts - I didn’t check - if it works out - check and inform

The classic method of transferring (and, in fact, migrating) databases is bad only because in 5.7, as I recall, defaults changed when working with dates (see here ), so when migrating this way from older versions of mysql, problems may arise.

if you have to migrate databases larger than 1GB, you may need to think about a complicated scheme -

  • Suppose there are two additional mysql servers / instances, both receive data on replication, you get auto backup.
  • when upgrading, you migrate the bases on the backup server, replication data from the old server is rolled there,
  • You switch production to a new server and upgrade the old one. it turns out almost zero simple service.
  • the third instance replicates with a lag of, say, 15 minutes, so if someone is stupidly or out of tune with drop databse, you will have time to save the latest data. Another thing is that, let's say, WordPress should not have rights to the drop database, but nevertheless, if someone has access to ssh, then there is a chance that he will be able to pull out the passwords to the database

true, if you have large bases, you should already think about hiring a DB admin