RE: Upgrading How To
Reindl, I am sorry, in my original post, I forgot to mention that the OLD box and the NEW box are the same physical machine. I need to be able to save all data into files on a memstick or portable disc and restore them to the newly staged machine (with the new version of mysql). -Grant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Upgrading How To
Hi Grant, On 12/26/2014 11:18 AM, Grant Peel wrote: Reindl, I am sorry, in my original post, I forgot to mention that the OLD box and the NEW box are the same physical machine. I need to be able to save all data into files on a memstick or portable disc and restore them to the newly staged machine (with the new version of mysql). -Grant There are a few file-level storage changes between 5.x (where x 6) and 5.6 that you may need to resolve before the upgrade. Examples: * 5.6 will not read any tables that were physically created in a version older than 5.0 and never rebuilt using a newer version. * the YEAR(2) data type is no longer supported. * pre 4.1 passwords - If you are upgrading from version 5.1 or older, you will need to update their hashes or configure 5.6 to recognize the older hashes as valid. The user authentication system in 5.6 is more advanced than in earlier versions. Several features are removed as of 5.6 http://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html#mysql-nutshell-removals Many defaults were changed starting with 5.6. These and other things to consider before a move to 5.6 (like the SQL Mode and timestamp behaviors) are all listed here: http://dev.mysql.com/doc/refman/5.6/en/upgrading-from-previous-series.html mysql_upgrade will update the system tables in the `mysql` database and run a CHECK TABLE ... FOR UPGRADE on all your tables but it cannot handle all of the possible upgrade issues you may encounter due to the other things about the server that may have changed. Reindl's technique with the rsync is just like what you are doing with your full-image save/restore. His is just optimized for operating between two live machines. You are also very strongly encouraged to test the upgrade to 5.6 on a lab box long before you push it into production. This will give you the chance to find any of those new 5.6 changes that your clients may not be ready to handle. Yours, -- Shawn Green MySQL Senior Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Upgrading How To
Shawn all, Thank you for taking to time to reply. So, to be clear, what I understand from your post is that replacing the new build's grant/system tables with the archived ones from the previous version, generally works fine, upgrade issues not withstanding. This is the answer I was hoping for. FWIW, I have already tested using some sample databases from the old version to the new one. Also, I'm not sure I mentioned, but I am moving from 5.1.39 to 5.6.17. I have already ran into the password hash issues on a number of tables, but, other than that things seem fine. Any other comments are welcome. -G
Re: Upgrading How To
Am 26.12.2014 um 20:52 schrieb Grant Peel: Shawn all, Thank you for taking to time to reply. So, to be clear, what I understand from your post is that replacing the new build's grant/system tables with the archived ones from the previous version, generally works fine, upgrade issues not withstanding. This is the answer I was hoping for. FWIW, I have already tested using some sample databases from the old version to the new one. Also, I'm not sure I mentioned, but I am moving from 5.1.39 to 5.6.17. I have already ran into the password hash issues on a number of tables, but, other than that things seem fine. that should be in general fine, i would recommend a scipted optimize table for any tables on the old machine before starting for two reasons * the data to transfer will be smaller * all old tables will be for sure rebuilt and not in 5.0 format the rsync works also fine with a USB stick, ext4 format preferred because owner / permissions, but that can be fixed easily in any case on the new machine per chmod / chown the only difference in your case is that you have a larger downtime if the hot rsync followed by a cold one with a USB stick as destination is noticeable faster needs to be tested, maybe have a empty datadir on the destination and transfer all data is faster then the checksumming signature.asc Description: OpenPGP digital signature
Upgrading How To
Hi all, I was wondering if anyone knows of a concise tutorial on how to upgrade (by moving from one box (old) to another box (new) mysql in a virtual environment (many mysql users, many databases). Example: Mysql 5.x setup on freebsd 8.x (x86/32b), call this box A. Want to move to a new box: Mysql 5.6.17 on freebsd 9.3 (x86/32b) call this box B. 1) How does one correctly move the users and all the permissions (grant tables), 2) How does one move the data. 3) Assume lots of backups have been done and there is no risk of permanently loosing data. Also, move/copy to be done using files (to memstick or external disk or NFS), not using ssh directly (rcopy etc). 4) Basically, dumpt the data and users and perms and put it on a new box J I suspect this question is trivial to a lot of admins out there, but, I suspect it would be helpful to many out there. Happy Holidays! -Grant
Re: Upgrading How To
Am 25.12.2014 um 16:01 schrieb Grant Peel: I was wondering if anyone knows of a concise tutorial on how to upgrade (by moving from one box (old) to another box (new) mysql in a virtual environment (many mysql users, many databases). Mysql 5.x setup on freebsd 8.x (x86/32b), call this box A. Want to move to a new box: Mysql 5.6.17 on freebsd 9.3 (x86/32b) call this box B. * setup the new box * stop mysqld on the old * rsync /var/lib/mysqld to the new one * start mysql on the new one * mysql_upgrade -u root -p * enter root pwd * done to keep downtime as low as possible rsync one or two times *hot* without stop mysqld on the old machine so the last rsync only transfer diffs - doing that for 12 years now from MySQl 3.x to 5.5 between Windows, MacOSX and Linux systems in all directions rsync params: --force --delete-after -tPrlpogEAX signature.asc Description: OpenPGP digital signature