- this is a works-for-me level tutorial. I'm not primarily a network or security guy, so if you're working on TNBTWMBHS (uh, that's The Next Big Thing Which Must Be Highly Secure), do some more homework: http://dev.mysql.com/tech-resources/articles/securing_mysql_windows.html
- my setup is two Windows XP boxes, but the MySQL-specific points should be the same regardless of your OS. This reflects a very basic setup -- windows firewall as the firewall on both boxes, workgroup networking, etc.
- I'm using "primary" as the machine you're transferring from, and "secondary" as the machine you're transferring to
There are two ways to do this. The most straightforward approach doesn't take any setup but it has manual steps to perform each time you move the database. The more convenient approach takes a little bit of tweaking with the firewall and MySQL users, but it enables you to subsequently replicate the database with a single command.
Most straightforward: backup the database, transfer the file, restore the database1. From a command line on the primary machine:
mysqldump --add-drop-table -uMYSQLUSERNAME -pMYSQLPASSWORD DATABASENAME > backup.sql
2. move the file to the secondary machine
3. From a command line on the secondary machine:
mysql -uMYSQLUSERNAME -pMYSQLPASSWORD DATBASE <backup.sql
IMPORTANT NOTE: the sql which myqldump generates creates both table structure and data, but NOT the database itself. If this is the first time transferring you will probably need to create the target database on the secondary machine. From the command line:
mysqlThis is very straightforward, but if you do the transfer very much you'll get tired of the manual steps. Fortunately there's a more convenient method, it just takes a little more setup.
> create database DATABASENAME;
Most convenient: direct transfer of database from primary to secondary machine1. you must open a port in the secondary machine's windows firewall. Assuming you're running standard windows firewall, go to:
start -> control panel --> windows firewall --> Exceptions Tab--> add program. Call it MySQL, and specify port 3306
2. also on the secondary machine, you must grant access within mysql itself. From the command line:
myusql -uMYSQLUSERNAME -pMYSQLPASSWORD
> use mysql;
> grant all on *.* to 'USERNAME'@'HOSTNAME' identified by 'PASSWORD' with grant option;
Let's break that down a bit:
- the USERNAME is the MySql user. If you are just getting started, you can use root, but make sure you change it to a more secure setup once you get everything figured out.
- HOSTNAME is the computer you are transferring from. To find out what your hostname is, (back on the primary box) open a command prompt and type hostname.
- PASSWORD is the password which the remote user on the primary box will use. It doesn't seem to need to correspond to the actual password of that MySQL user, but I set it the same to minimize confusion.
- So for example, if your primary machine is called "mydesktop", and you have a mysql user called "general" there, and "general" has a nice secure password, the command would be: grant all on *.* to 'general'@'mydesktop' identified by 'gx1868fj93d' with grant option;
3. Now to actually do the transfer. Back on the primary machine, form the command line:
mysqldump --add-drop-table -uMYSQLUSERNAME -pMYSQLPASSWORD DATABASENAME | mysql -hSECONDARYHOSTNAME -uMYSQLUSERNAME -pMYSQLPASSWORD -C DATABASENAME
So for example, with a user “general” and your database “the_next_ebay”, transferring to a machine called “mylaptop”:
mysqldump --add-drop-table -ugeneral -pgx1868fj93d the_next_ebay | mysql -hmylaptop -ugeneral -pgx1868fj93d -C the_next_ebay
The SECONDARYHOSTNAME is the hostname of the transfer-to box. Again, to get the hostname, open a command line (on the secondary box) and type hostname.
Once you have the first two steps set up, it takes only a single command to move a copy of your database to the other machine. You can drop this into a batch script so that moving your database becomes a one-click affair.