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?
A
mysqldumpoutput 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.
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.