Re: Migration from 32-bit to 64-bit MySQL
learnt On Wed, Apr 30, 2008 at 5:18 AM, Mihail Manolov [EMAIL PROTECTED] wrote: Hi all, I feel obliged to report on my success with migration from 32bit to 64bit platform. Last Sunday I braced myself and migrated 3 servers (one master and two slaves) with approximately 100GB data each by simply rsyncing the data files. It took about 1 hour total downtime. Everything looks great so far. I ran lots of tests, especially on currency columns and all tests were successfull. It is fair to note that I don't have any FLOAT columns in my databases. I have mixed table environment (MyISAM and InnoDB tables). Running MySQL 4.1.24, Linux binaries. Let me know if you have any questions. Mihail On Apr 25, 2008, at 12:48 PM, Mihail Manolov wrote: I am in process of planning 32 to 64 migration as well. I googled the following, but it could be only relevant to a specific application: It should be noted that, when switching between 32bit and 64bit server using the same data-files, all the current major storage engines (with one exception) are architecture neutral, both in endian-ness and bit size. You should be able to copy a 64-bit or 32-bit DB either way, and even between platforms without problems for MyISAM, InnoDB and NDB. For other engines it doesn't matter (CSV, MEMORY, MERGE, BLACKHOLE and FEDERATED) either the engine doesn't have a disk storage format or the format they use is text based (CSV) or based on MyISAM (MERGE; and therefore not an issue). The only exception is Falcon, which is only available in MySQL 6.0. It is generally recommended from MySQL that a dump and reload of data for absolute compatibility for any engine and major migration. The googled link: http://wikis.sun.com/display/WebStack/MySQL64bitARC Any comments on this? Mihail On Apr 25, 2008, at 12:03 PM, Olaf Stein wrote: Probably not AFAIK it should work in theory if you have no floating point columns but I would not try it. Why cant you take a dump, you can do it table by table, you will have some downtime though. One option might be to use a 64bit slave and make that the master and then add more 64 slaves. On 4/25/08 11:57 AM, Mike [EMAIL PROTECTED] wrote: On Fri, Apr 25, 2008 at 11:45 AM, Olaf Stein [EMAIL PROTECTED] wrote: As long as you use dumps to restore your databases on the new 64bit system (instead of the binary files) you should be fine Olaf I have so much data that we can't take a mysqldump of our database. The directory tared is about 18GB. I just use the other method by just copying over the data directory. Do you think the data will be intact if a just copy over the data directory? -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Migration from 32-bit to 64-bit MySQL
Hi all, I feel obliged to report on my success with migration from 32bit to 64bit platform. Last Sunday I braced myself and migrated 3 servers (one master and two slaves) with approximately 100GB data each by simply rsyncing the data files. It took about 1 hour total downtime. Everything looks great so far. I ran lots of tests, especially on currency columns and all tests were successfull. It is fair to note that I don't have any FLOAT columns in my databases. I have mixed table environment (MyISAM and InnoDB tables). Running MySQL 4.1.24, Linux binaries. Let me know if you have any questions. Mihail On Apr 25, 2008, at 12:48 PM, Mihail Manolov wrote: I am in process of planning 32 to 64 migration as well. I googled the following, but it could be only relevant to a specific application: It should be noted that, when switching between 32bit and 64bit server using the same data-files, all the current major storage engines (with one exception) are architecture neutral, both in endian-ness and bit size. You should be able to copy a 64-bit or 32-bit DB either way, and even between platforms without problems for MyISAM, InnoDB and NDB. For other engines it doesn't matter (CSV, MEMORY, MERGE, BLACKHOLE and FEDERATED) either the engine doesn't have a disk storage format or the format they use is text based (CSV) or based on MyISAM (MERGE; and therefore not an issue). The only exception is Falcon, which is only available in MySQL 6.0. It is generally recommended from MySQL that a dump and reload of data for absolute compatibility for any engine and major migration. The googled link: http://wikis.sun.com/display/WebStack/MySQL64bitARC Any comments on this? Mihail On Apr 25, 2008, at 12:03 PM, Olaf Stein wrote: Probably not AFAIK it should work in theory if you have no floating point columns but I would not try it. Why cant you take a dump, you can do it table by table, you will have some downtime though. One option might be to use a 64bit slave and make that the master and then add more 64 slaves. On 4/25/08 11:57 AM, Mike [EMAIL PROTECTED] wrote: On Fri, Apr 25, 2008 at 11:45 AM, Olaf Stein [EMAIL PROTECTED] wrote: As long as you use dumps to restore your databases on the new 64bit system (instead of the binary files) you should be fine Olaf I have so much data that we can't take a mysqldump of our database. The directory tared is about 18GB. I just use the other method by just copying over the data directory. Do you think the data will be intact if a just copy over the data directory?
Re: Migration from 32-bit to 64-bit MySQL
Mike wrote: Hi! I would like to move from 32-bit to 64-bit MySQL within the next year. Unfortunately, there is not a lot of documentation on migration or anything else regarding 64bit MySQL. Dump the database, restore on the 64-bit box, and all should be well What is the difference between 32-bit and 64-bit? Is this a good idea? Can it be done? What would make this go wrong? Addressing more RAM currently is your only gain... On a 32-bit system, you tend to only have 2.5-2.7GB of RAM available to MySQL (your OS needs the remainder) Kind Regards -- Colin Charles, Community Relations Manager, APAC Database Group, Sun Microsystems Inc., http://www.mysql.com/ Tel: +6012 204 3201 (Malaysia) | colincharles (Skype) Web: http://www.bytebot.net/blog/ MySQL Forge: http://forge.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from 32-bit to 64-bit MySQL
On 4/26/08 Mihail Manolov wrote: Hi! MyISAM (MERGE; and therefore not an issue). The only exception is Falcon, which is only available in MySQL 6.0. While I haven't had a chance to read the wiki link you posted, as I write this email offline, it should be noted that Falcon previews even (currently available on forge.mysql.com/wiki/) are only built for 64-bit There are no 32-bit binaries built for Falcon Falcon, the engine itself, was designed to shine on 64-bit platforms. By the time Falcon is GA (heck, even now when its not GA), people will not be buying 32-bit hardware any longer kind regards -- Colin Charles, Community Relations Manager, APAC Database Group, Sun Microsystems Inc., http://www.mysql.com/ Tel: +6012 204 3201 (Malaysia) | colincharles (Skype) Web: http://www.bytebot.net/blog/ MySQL Forge: http://forge.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from 32-bit to 64-bit MySQL
another option is to do a mysqldump and pipe it over to the new server, e.g: mysqldump --opt 32bitserver | mysql --host=64bithost -C 64bitserver - Original Message From: Mike To: B. Keith Murphy Cc: mysql list Sent: Friday, 25 April, 2008 5:33:49 PM Subject: Re: Migration from 32-bit to 64-bit MySQL On Fri, Apr 25, 2008 at 12:08 PM, B. Keith Murphy wrote: Olaf Stein wrote: Probably not AFAIK it should work in theory if you have no floating point columns but I would not try it. Why cant you take a dump, you can do it table by table, you will have some downtime though. One option might be to use a 64bit slave and make that the master and then add more 64 slaves. On 4/25/08 11:57 AM, Mike wrote: On Fri, Apr 25, 2008 at 11:45 AM, Olaf Stein wrote: As long as you use dumps to restore your databases on the new 64bit system (instead of the binary files) you should be fine Olaf I have so much data that we can't take a mysqldump of our database. The directory tared is about 18GB. I just use the other method by just copying over the data directory. Do you think the data will be intact if a just copy over the data directory? Seriously, 18 gb isn't too big to do a mysqldump. And I really wouldn't advise you trying to do a binary copy. You are just asking for trouble. Plan ahead and you can do this on a slave without any problem, import the data on the new server and sync it back up without any problems. -- Keith Murphy I know you can take a mysqldump and copy over the data directory.. I not sure what you mean by binary copy. Can you please explain? We have one database in memory that why we are moving over to 64bit. I'm planing like a year ahead of time. ___ Yahoo! For Good. Give and get cool things for free, reduce waste and help our planet. Plus find hidden Yahoo! treasure http://green.yahoo.com/uk/earth-day/ __ Sent from Yahoo! Mail. A Smarter Email http://uk.docs.yahoo.com/nowyoucan.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from 32-bit to 64-bit MySQL
Mike wrote: I would like to move from 32-bit to 64-bit MySQL within the next year. Unfortunately, there is not a lot of documentation on migration or anything else regarding 64bit MySQL. My current setup consists of one master and two slaves (all using 32bit and MySQL 5.0). I am looking to add a 64bit slave to the mix. What is the difference between 32-bit and 64-bit? Is this a good idea? Can it be done? What would make this go wrong? I have made this migration on multiple servers. It has never been any trouble. Your biggest gain would probably be the ability to address more RAM. I would just dump the database from the 32-bit platform and import it into the 64-bit server. Keith -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from 32-bit to 64-bit MySQL
On Fri, 25 Apr 2008, B. Keith Murphy [EMAIL PROTECTED] wrote: I would just dump the database from the 32-bit platform and import it into the 64-bit server. By dump do you mean mysqldump, or some other process? -- Tim McDaniel, n00b, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from 32-bit to 64-bit MySQL
As long as you use dumps to restore your databases on the new 64bit system (instead of the binary files) you should be fine Olaf On 4/25/08 11:23 AM, Mike [EMAIL PROTECTED] wrote: I would like to move from 32-bit to 64-bit MySQL within the next year. Unfortunately, there is not a lot of documentation on migration or anything else regarding 64bit MySQL. My current setup consists of one master and two slaves (all using 32bit and MySQL 5.0). I am looking to add a 64bit slave to the mix. What is the difference between 32-bit and 64-bit? Is this a good idea? Can it be done? What would make this go wrong? - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from 32-bit to 64-bit MySQL
On Fri, Apr 25, 2008 at 11:45 AM, Olaf Stein [EMAIL PROTECTED] wrote: As long as you use dumps to restore your databases on the new 64bit system (instead of the binary files) you should be fine Olaf I have so much data that we can't take a mysqldump of our database. The directory tared is about 18GB. I just use the other method by just copying over the data directory. Do you think the data will be intact if a just copy over the data directory?
Re: Migration from 32-bit to 64-bit MySQL
Probably not AFAIK it should work in theory if you have no floating point columns but I would not try it. Why cant you take a dump, you can do it table by table, you will have some downtime though. One option might be to use a 64bit slave and make that the master and then add more 64 slaves. On 4/25/08 11:57 AM, Mike [EMAIL PROTECTED] wrote: On Fri, Apr 25, 2008 at 11:45 AM, Olaf Stein [EMAIL PROTECTED] wrote: As long as you use dumps to restore your databases on the new 64bit system (instead of the binary files) you should be fine Olaf I have so much data that we can't take a mysqldump of our database. The directory tared is about 18GB. I just use the other method by just copying over the data directory. Do you think the data will be intact if a just copy over the data directory? - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you.
Re: Migration from 32-bit to 64-bit MySQL
Olaf Stein wrote: Probably not AFAIK it should work in theory if you have no floating point columns but I would not try it. Why cant you take a dump, you can do it table by table, you will have some downtime though. One option might be to use a 64bit slave and make that the master and then add more 64 slaves. On 4/25/08 11:57 AM, Mike [EMAIL PROTECTED] wrote: On Fri, Apr 25, 2008 at 11:45 AM, Olaf Stein [EMAIL PROTECTED] wrote: As long as you use dumps to restore your databases on the new 64bit system (instead of the binary files) you should be fine Olaf I have so much data that we can't take a mysqldump of our database. The directory tared is about 18GB. I just use the other method by just copying over the data directory. Do you think the data will be intact if a just copy over the data directory? Seriously, 18 gb isn't too big to do a mysqldump. And I really wouldn't advise you trying to do a binary copy. You are just asking for trouble. Plan ahead and you can do this on a slave without any problem, import the data on the new server and sync it back up without any problems. -- Keith Murphy editor: MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from 32-bit to 64-bit MySQL
Mike wrote: I have so much data that we can't take a mysqldump of our database. The directory tared is about 18GB. Worst-case expansion for SQL data from binary to text format is about 5:1, which applies mainly to numeric data, not text. That's only 90 GB; I carry a bigger hard drive in my backpack, which I use for moving files between machines. Heck, my iPod holds more than that. You don't even have to store a second copy of the data. You can do something like pipe the mysqldump through a tool like nc (netcat) from the old machine to the new. With a decent GigE network connection between the two, the transfer should complete in about an hour. Add in a little data compression and you can probably cut that in half. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migration from 32-bit to 64-bit MySQL
Every statement should be executed on the slave from the masters binary log so in my opinion you should be ok On 4/25/08 12:20 PM, Mike [EMAIL PROTECTED] wrote: That what I want to do, but I'm not sure if the data will propagate right. Because of lack of documentation for 64bit. On Fri, Apr 25, 2008 at 12:03 PM, Olaf Stein [EMAIL PROTECTED] wrote: Probably not AFAIK it should work in theory if you have no floating point columns but I would not try it. Why cant you take a dump, you can do it table by table, you will have some downtime though. One option might be to use a 64bit slave and make that the master and then add more 64 slaves. On 4/25/08 11:57 AM, Mike [EMAIL PROTECTED] wrote: On Fri, Apr 25, 2008 at 11:45 AM, Olaf Stein [EMAIL PROTECTED] wrote: As long as you use dumps to restore your databases on the new 64bit system (instead of the binary files) you should be fine Olaf I have so much data that we can't take a mysqldump of our database. The directory tared is about 18GB. I just use the other method by just copying over the data directory. Do you think the data will be intact if a just copy over the data directory? - Confidentiality Notice: The following mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information. The recipient is responsible to maintain the confidentiality of this information and to use the information only for authorized purposes. If you are not the intended recipient (or authorized to receive information for the intended recipient), you are hereby notified that any review, use, disclosure, distribution, copying, printing, or action taken in reliance on the contents of this e-mail is strictly prohibited. If you have received this communication in error, please notify us immediately by reply e-mail and destroy all copies of the original message. Thank you.
Re: Migration from 32-bit to 64-bit MySQL
That what I want to do, but I'm not sure if the data will propagate right. Because of lack of documentation for 64bit. On Fri, Apr 25, 2008 at 12:03 PM, Olaf Stein [EMAIL PROTECTED] wrote: Probably not AFAIK it should work in theory if you have no floating point columns but I would not try it. Why cant you take a dump, you can do it table by table, you will have some downtime though. One option might be to use a 64bit slave and make that the master and then add more 64 slaves. On 4/25/08 11:57 AM, Mike [EMAIL PROTECTED] wrote: On Fri, Apr 25, 2008 at 11:45 AM, Olaf Stein [EMAIL PROTECTED] wrote: As long as you use dumps to restore your databases on the new 64bit system (instead of the binary files) you should be fine Olaf I have so much data that we can't take a mysqldump of our database. The directory tared is about 18GB. I just use the other method by just copying over the data directory. Do you think the data will be intact if a just copy over the data directory?
Re: Migration from 32-bit to 64-bit MySQL
On Fri, Apr 25, 2008 at 12:08 PM, B. Keith Murphy [EMAIL PROTECTED] wrote: Olaf Stein wrote: Probably not AFAIK it should work in theory if you have no floating point columns but I would not try it. Why cant you take a dump, you can do it table by table, you will have some downtime though. One option might be to use a 64bit slave and make that the master and then add more 64 slaves. On 4/25/08 11:57 AM, Mike [EMAIL PROTECTED] wrote: On Fri, Apr 25, 2008 at 11:45 AM, Olaf Stein [EMAIL PROTECTED] wrote: As long as you use dumps to restore your databases on the new 64bit system (instead of the binary files) you should be fine Olaf I have so much data that we can't take a mysqldump of our database. The directory tared is about 18GB. I just use the other method by just copying over the data directory. Do you think the data will be intact if a just copy over the data directory? Seriously, 18 gb isn't too big to do a mysqldump. And I really wouldn't advise you trying to do a binary copy. You are just asking for trouble. Plan ahead and you can do this on a slave without any problem, import the data on the new server and sync it back up without any problems. -- Keith Murphy I know you can take a mysqldump and copy over the data directory. I not sure what you mean by binary copy. Can you please explain? We have one database in memory that why we are moving over to 64bit. I'm planing like a year ahead of time.
Re: Migration from 32-bit to 64-bit MySQL
I am in process of planning 32 to 64 migration as well. I googled the following, but it could be only relevant to a specific application: It should be noted that, when switching between 32bit and 64bit server using the same data-files, all the current major storage engines (with one exception) are architecture neutral, both in endian-ness and bit size. You should be able to copy a 64-bit or 32-bit DB either way, and even between platforms without problems for MyISAM, InnoDB and NDB. For other engines it doesn't matter (CSV, MEMORY, MERGE, BLACKHOLE and FEDERATED) either the engine doesn't have a disk storage format or the format they use is text based (CSV) or based on MyISAM (MERGE; and therefore not an issue). The only exception is Falcon, which is only available in MySQL 6.0. It is generally recommended from MySQL that a dump and reload of data for absolute compatibility for any engine and major migration. The googled link: http://wikis.sun.com/display/WebStack/MySQL64bitARC Any comments on this? Mihail On Apr 25, 2008, at 12:03 PM, Olaf Stein wrote: Probably not AFAIK it should work in theory if you have no floating point columns but I would not try it. Why cant you take a dump, you can do it table by table, you will have some downtime though. One option might be to use a 64bit slave and make that the master and then add more 64 slaves. On 4/25/08 11:57 AM, Mike [EMAIL PROTECTED] wrote: On Fri, Apr 25, 2008 at 11:45 AM, Olaf Stein [EMAIL PROTECTED] wrote: As long as you use dumps to restore your databases on the new 64bit system (instead of the binary files) you should be fine Olaf I have so much data that we can't take a mysqldump of our database. The directory tared is about 18GB. I just use the other method by just copying over the data directory. Do you think the data will be intact if a just copy over the data directory?
Re: Migration from 32-bit to 64-bit MySQL
Mike wrote: I not sure what you mean by binary copy. Can you please explain? A binary copy means copying the MySQL data directory directly, rather than do a mysqldump, which converts the data to text format. The text dump is converted back to binary format for disk storage on loading it back into the new database. That conversion through a machine-neutral format is why it's always guaranteed to work. Moving binary data between machines only works when both machines play by the same rules. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
