3

I am hosted on a Media Temple DV 3.5 server and looking to upgrade from mysql 5.0.90 to the latest mysql so I can use triggers and foreign keys. I am a bit nervous to do so because I don't have a 'test' server to match my production server.

We have full server backups so in the event something goes wrong, I know I can always revert but I am curious, is there a way to backup ALL mysql data (all databases, all users, everything), run the mysql update with Yum package manager (we have CentOS 5.3) and then import the missing data if any exists once upgraded?

Improve this question

2 Answers 2

Reset to default
5

A mysqldump output file is valid SQL, so you can mess around in the file and pick and choose what you want.

So, yes, if data theoretically disappeared you could selectively replace it from the dump.

Improve this answer
3
  • including the users and privileges?
    – JM4
    Sep 23, 2011 at 21:00
  • Yes, including database users and permissions, if you have the mysql database besides the application database.
    – Mircea Vutcovici
    Sep 23, 2011 at 21:03
  • @JM4 Sure - they're stored in the mysql database. Back it up, or just use the --all-databases option of mysqldump.
    – Shane Madden
    Sep 23, 2011 at 21:04
4

Please be very careful with upgrading. I have never relied on mysql's upgrade scripts. I script myself.

Please click here for how to mysqldump an entire database as one file, separate databases, separate tables, and even how to dump the SQL Grants in SQL rather that with a mysqldump.

If you do a full mysqldump of everything, you cannot import mysql.user into a higher version of mysql because the mysql.user is different among major releases.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .