[Mifos-developer] copying binary MySQL data files from Windows to Linux
Adam Feuer
Here's how to copy the MySQL binary data files from a Windows to
Ubuntu Linux server. This assumes that the Ubuntu server uses the
version of MySQL that comes with Ubuntu (installed via the package
manager.) If that isn't how you installed mysql, then let me know, and
we'll update the instructions.
1. Stop the Windows MySQL server. This might be from the Services
panel of Computer Management (Start > Control Panel > Computer
Management > Services) or via the command line, depending on how you
installed it.
2. Stop the Linux MySQL server. This is probably via the command line:
sudo /etc/init.d/mysql stop
3. Find the Windows MySQL data directory. This is usually under the
MySQL installation directory; for instance:
C:\\mysql-5.0.51b-win32\data
4. Use Winzip to zip this directory. Name the zip file something
descriptive, like production-mysql-data-dir-2009-07-12.zip
5. Copy the zip file to the Linux box. There are several ways to do
this, one way is to install Putty PSCP
(http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html) -
this is a Windows scp program. Use pscp to copy the zip file to Linux
- from a command prompt, type
pscp.exe C:\Temp\production-mysql-data-dir-2009-07-12.zip
your...@your.linux.server.com:
This assumes that the data file is in
C:\Temp\production-mysql-data-dir-2009-07-12.zip and your login on the
linux server is 'yourlogin' and the server's Internet hostname is
your.linux.server.com.
6. Once the data file has been transfered, login in the the Ubuntu
Linux box via a command line (ssh or terminal).
7. Make a tempory directory to hold the files:
mkdir temp
cd temp
8. Unzip the data file:
unzip ../production-mysql-data-dir-2009-07-12.zip
9. The mysql data directory is located here: /var/lib/mysql Now move
the existing data directory somewhere safe:
sudo mv /var/lib/mysql /tmp
10. Move the data files directory to the new location:
sudo mv $HOME/temp/data /var/lib/mysql
11. Make sure the permissions are ok on the new data directory:
sudo chown -R mysql:mysql /var/lib/mysql
sudo chmod -R ug+rw /var/lib/mysql
12. Start the MySQL server:
sudo /etc/init.d/mysql start
13. Wait a minute, then check the mysql log to make sure it started ok:
less /var/log/mysql.log
less /var/log/mysql.err
14. You are done!
Let me know if these instructions aren't clear or if you have questions!
cheers
adam
--
Adam Feuer <adamf at pobox dot com>
------------------------------------------------------------------------------
Enter the BlackBerry Developer Challenge
This is your chance to win up to $100,000 in prizes! For a limited time,
vendors submitting new applications to BlackBerry App World(TM) will have
the opportunity to enter the BlackBerry Developer Challenge. See full prize
details at: http://p.sf.net/sfu/Challenge
Krishnan M
| Hi Adam, While i am not entirely convinced about copying MySQL's data directory over from one server to another (for reasons not discussed here), if we do intend to follow this strategy, perhaps we should invoke the services of that magic command "dd" This can then be done using intermediate media for transfer between the boxes, or even connecting the two boxes together and mounting one of the drives so dd can work its magic over the network i suspect that Naganand is facing practical problems with the size of the data (around 80 GIG). i am not sure that a WinZip or psftp is upto the task, but this is child's play for a tar zcvf ... i might be able to try this out using a similar setup tomorrow, and if i do, i shall post the results and a HOW-TO. There are precedents to this approach, as a quick Google search shows. regards, krishnan --- On Mon, 13/7/09, Adam Feuer <ad...@pobox.com> wrote: |
See the Web's breaking stories, chosen by people like you. Check out Yahoo! Buzz.
Adam Feuer
> While i am not entirely convinced about copying MySQL's data directory over
> from one server to another (for reasons not discussed here),
Krishnan,
We verified with MySQL Support that this can be done between Windows
and Linux. MySQL on these platforms have binary-compatible data files.
> if we do intend
> to follow this strategy, perhaps we should invoke the services of that magic
> command "dd"
I recommend you do not do this. While it is possible to do that, dd is
mean to copy and write whole disks, and the possibility of making a
mistake and wrecking a disk is there. It's not there via tar or zip.
Both tar and zip can handle large files. It is true that zipping or
tarring them will take hours, but the advantage of doing it this way
is we can be sure all the files will arrive intact.
> i suspect that Naganand is facing practical problems with the size of the
> data (around 80 GIG). i am not sure that a WinZip or psftp is upto the task,
> but this is child's play for a tar zcvf ...
Tar is an alternative. I don't know what is installed on the Windows
MySQL server box, however, and I am sure you can easily get and
install WinZip or use the Windows native zip. Does the box have
cygwin? If so, then tar is an option. If it has cygwin, let me know,
and I will update the instructions.
-adam
Adam Feuer
The purpose of doing this is to speed up the time to take to get
replication from Windows to Linux caught up - so it is an optional
step.
Another way to do this would be to:
1. Turn on binary logging on the Windows server (should be already on)
2. Take a database backup of the Windows production server
3. Restore the database backup to the Linux server
4. When the restore is done, stop the Windows server.
5 . Apply the Windows binary logs to the Linux server.
6. Set up replication
7. Start both servers and let the replication catch up.
This doesn't involve zipping - you just need to copy the database
backup and the binary log file to the Linux box.
If you want detailed instructions for this, let me know.
-adam
Todd Farmer
A couple of notes:
> 2. Take a database backup of the Windows production server
>
First, assuming you are using mysqldump to backup the Windows production
server, you can actually take the backup from the linux machine - this
will eliminate the need to do an additional expensive network copy later on.
Second, you might want to use the --single-transaction and
--master-data=2 options for mysqldump. These will encapsulate the
backup in a single transaction (useful to ensure consistency of data in
InnoDB tables when the backup can take a while to execute, and the
database is being used) and output the coordinates of the binary log
(useful to determine where to start replication on a slave when that
backup is used to restore data), respectively.
http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_single-transaction
http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_master-data
> 3. Restore the database backup to the Linux server
>
As a variation on the above, you could backup locally on the Windows
machine, and feed the backup through the mysql client to the Linux machine.
All this said, a logical (SQL-based) backup is almost surely going to be
larger than a raw backup of the binary data files. If time spent
sending across the network is the limiting factor, you're probably going
to be better off with the raw data files.
Todd Farmer
Adam Feuer
> A couple of notes:
>> 2. Take a database backup of the Windows production server
> First, assuming you are using mysqldump to backup the Windows production
> server, you can actually take the backup from the linux machine - this
> will eliminate the need to do an additional expensive network copy later on.
Todd,
Do you mean, run the mysqldump on Linux, but dump the Windows server's database?
-adam
--
Adam Feuer <adamf at pobox dot com>
------------------------------------------------------------------------------
Todd Farmer
>
> Do you mean, run the mysqldump on Linux, but dump the Windows server's database?
>
host (given an account with necessary privileges):
bash> mysqldump -h windows-hostname -uroot -ppass --single-transaction
--master-data=2 mifos > backup.sql
It beats dumping the database locally on the Windows machine and copying
off to the Linux box, in many cases - especially when you have run the
backup through compression utilities on both ends, also.
Todd Farmer
Keith Randall
a different machine than the database itself. This compresses the
network traffic between the two servers.
Also, it wouldn't hurt to compress the backup before saving it -
compression/decompression is faster than disk I/O. Just replace ">
backup.sql" with "| gzip > backup.sql.gz"