Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm moving a single MySQL database (~10GB uncompressed) from one server to another on the same network. The current MySQL version is 5.1.41 and the new version is 5.5.24. The database contains both MyISAM and InnoDB tables. Is it possible to use this method:

  1. Shut down MySQL on both servers
  2. Copy the /data directory from old server to new server
  3. Start new server

I realize this basic question has probably been asked 1000 times before but most of the ones I've seen don't mention changing versions & supporting both MyISAM and InnoDB.

share|improve this question
add comment

3 Answers 3

up vote 2 down vote accepted

If you are changing versions, DO NOT MOVE THE mysql SCHEMA.

Why should you not move the mysql folder? It has to do with the authentication privileges.

The number of columns in mysql.user is different from version to version

If you run desc mysql.user

  • You will see 31 rows for MySQL 4.1
  • You will see 37 rows for MySQL 5.0
  • You will see 39 rows for MySQL 5.1
  • You will see 42 rows for MySQL 5.5

I wrote about this before

It is OK to move everything else. On the new machine that has MySQL 5.5.24, do this:

mv /var/lib/mysql /var/lib/mysql/mysql55
mkdir /var/lib/mysql
<scp or rsync /var/lib/mysql of MySQL 5.1.41 over to /var/lib.mysql of MySQL 5.5.24>
rm -f /var/lib/mysql/mysql/*
cp /var/lib/mysql/mysql55/* /var/lib/mysql/mysql/*
chown -R mysql:mysql /var/lib/mysql
service mysql start

So, the question remains:

How do you move the User Privileges in the old MySQL 5.1.41 to MySQL 5.5.24 ???

There are two ways to do this starting on the MySQL 5.1.41 machine:

METHOD #1 : Use pt-show-grants

This Percona Toolkit program move print out the User Permission in Pure SQL. You could run the result output into a Text File. Then, execute the Text File in MySQL 5.5.24. End of Story.

pt-show-grants ... > MySQLUserGrants.sql

METHOD #2 : Emulate pt-show-grants

I made my own technique for pt-show-grants

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

Either way, move MySQLUserGrants.sql over to the MySQL 5.5.24 machine and execute the script

I wrote about this before : importing myisam 5.0 database into a 5.5 innodb server

share|improve this answer
    
This is great! I actually don't care about existing user permissions. How would I do this and just create a new user with full permissions on the new server? –  bloudermilk Jun 13 '12 at 20:25
    
root@localhost should not have a password upon installation. It should have full privileges already. Just login as follows: mysql -uroot -plocalhost. Once in, run SHOW GRANTS; it should say you have full privs. You will need to assign a password. Just run SET PASSWORD = PASSWORD('myn3wp4$$w0rd'); –  RolandoMySQLDBA Jun 13 '12 at 20:28
    
Rolando, I have to disagree with you having performed the upgrade dozens of times from 5.1 to 5.5. mysql_upgrade will resolve the discrepancies and the changes are backwards compatible. There is no need to do this. –  Aaron Brown Jun 15 '12 at 1:35
add comment

Having done a 5.1->5.5 upgrade several times, here's how you upgrade from 5.1 to 5.5 with a second server.

  1. Shut down the 5.1 instance
  2. Copy the data directory (often /var/lib/mysql) from the 5.1 server to the 5.5 server (via rsync, scp, CDROM, bittorrent, typewriter, whatever)
  3. Make sure the data directory and all files on the 5.5 are owned by the mysql user
  4. Start MySQL 5.5
  5. Check the error log for errors. You will probably see some missing fields in various tables in the mysql database.
  6. run mysql_upgrade
  7. stop MySQL 5.5
  8. start MySQL 5.5
  9. Check the error log again. Those errors should be resolved.
  10. Have a margarita.

There is no need for any additional complexity unless you have unresolved errors in the log.

share|improve this answer
    
I know, maybe my method is a little paranoid. +1 for your solution. –  RolandoMySQLDBA Jun 15 '12 at 2:36
add comment

Moving major versions it is recommended you do a mysqldump and reload. Minor versions are considered OK but you should run the provided mysql_upgrade binary.

The fastest way to accomplish this assumes the servers are connected via VPN so you have encryption handled for you at that level. rsyncing things across a VPN will add CPU overhead of reencrypting things. To prep on the new server do a fresh setup using mysql_install_db

On the new server first run

nc -l 12345 | mysql -u root -p 

That will just hang and listen on port 12345 until you do this next step on the old server

mysqldump -u root -p --databases db1 db2 db3 | nc new-server-hostname 12345 

Doing a reload like this will give you the added benefit of defragging your tables. This example also implies you don't mind having the 5.1 DB locked up while the dump is occurring.

share|improve this answer
    
It is odd that the MySQL docs suggest this completely unnecessary step. –  Aaron Brown Jun 15 '12 at 1:36
    
Even if you feel it's unnecessary to do a dump/reload during an upgrade is still a nice time to do some house cleaning and defrag your tables. –  atxdba Jun 15 '12 at 14:24
    
Sure, if you can tolerate that much of an outage. I'd prefer to do the same thing with optimize table, though. It will be much faster. –  Aaron Brown Jun 15 '12 at 14:27
    
Dump from a slave, start slaving, only downtime is what ever your setup needs to point apps to the new server during it's promotion to master. –  atxdba Jun 15 '12 at 14:46
    
That's true, but it can take days to create this replica on any reasonably sized data set using mysqldump/load and you have to ensure that you keep enough binary logs around. It's far simpler and faster to just copy the binary files, either from a cold backup (downtime) or a hot/warm backup (Xtrabackup/filesystem snapshots). The OP did ask for the "fastest way." :) If you want to defrag your tables, run multiple parallel OPTIMZE TABLE across the tables on the new server. –  Aaron Brown Jun 15 '12 at 20:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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