Transfering a MySQL database

**Rynoguill** · Apr 27, 2004, 08:32

This may be an easy and maybe even silly question indeed, but i have to ask it anyway. i have *moderate* mysql experience, and what i mean by that is i know how to create and alter tables, and i can put stuff in them and get stuff out, no prob. im much more used to access, and for most of my projects, access works just fine.

heres my question though. with access, i have an mdb file that i just move from development to production and it works. do i have that same kind of file with mysql? or do i have to connect the two servers or something? is this what replication is for or is that just backup?

this may be newbie questions, ill admit, but the documentation isnt very clear.

Thanks in advance,
mark

**stymiee** · Apr 27, 2004, 09:50

you'll need to do a mysql dump: http://dev.mysql.com/doc/mysql/en/mysqldump.html

**JRMillion** · Apr 27, 2004, 16:15

if you have phpmyadmin running on both machines you can just do an export. ask it to save as a text file, it will contain all the SQL to rebuild the database.
then just past the text(or pick the file) into the SQL windows on the remote copy of phpmyadmin.

**Rynoguill** · Apr 28, 2004, 05:11

Originally Posted by JRMillion

if you have phpmyadmin running on both machines you can just do an export. ask it to save as a text file, it will contain all the SQL to rebuild the database.
then just past the text(or pick the file) into the SQL windows on the remote copy of phpmyadmin.

i figured there was something out there like that. but heres the problem, im not using php. im a coldfusion kinda guy. i do have the mysql control center, and its a great product, especially for a beta, but i didnt see anything in there. is there any other (hopefully free) utilities that do this?

**r937** · Apr 28, 2004, 05:37

mysql-front

absolutely gorgeous app

price is good, too

does a dump as easy as you please

contents look like this:

Code:

# MySQL-Front Dump 2.5
#
# Host: database.example.com   Database: testmetodeath
# --------------------------------------------------------
# Server version 4.0.16-standard-log

#
# Table structure for table 'orderbydemo'
#

CREATE TABLE orderbydemo (
  foo int(11) default NULL,
  bar int(11) default NULL,
  SequenceOrder int(11) default NULL
) TYPE=MyISAM;

#
# Dumping data for table 'orderbydemo'
#

INSERT INTO orderbydemo VALUES("302", "402", "100012");
INSERT INTO orderbydemo VALUES("703", "802", "100009");
INSERT INTO orderbydemo VALUES("903", "801", "100006");
INSERT INTO orderbydemo VALUES("303", "702", "100011");
INSERT INTO orderbydemo VALUES("604", "903", "100012");
INSERT INTO orderbydemo VALUES("101", "808", "100008");
INSERT INTO orderbydemo VALUES("202", "303", "100004");
INSERT INTO orderbydemo VALUES("306", "702", "100010");
INSERT INTO orderbydemo VALUES("307", "802", "100013");
INSERT INTO orderbydemo VALUES("206", "905", "100005");

**Rynoguill** · Apr 28, 2004, 05:45

ill look into that rudy, but is there anything else you know of thats free?

and just for conversations sake, if i was going to do this from the comand line, or the old fashioned way, how do you even go about doing this?

**r937** · Apr 28, 2004, 05:55

what's a command line?

**Rynoguill** · Apr 28, 2004, 06:09

Originally Posted by r937

what's a command line?

are you joking? i cant tell..

**Rynoguill** · Apr 28, 2004, 11:03

i did it just like this:

Code:

mysqldump --opt [db_name] | mysql --host=[RemoteServerIPAddress] --user=[username] --password=[password] -C [db_name]

where the things in brackets [] are values you fill in.

is there a better way anyone?

**asp_funda** · May 2, 2004, 13:47

If you have MySQL on your webserver, then you must've got phpMyAdmin or something like that to manage your MySQL databases through browser.

And if that's the case, then it must've some sort of export or dump option. phpMyAdmin has it by name of EXPORT. Its a tab on the top.

**r937** · May 2, 2004, 13:56

Originally Posted by Rynoguill

are you joking? i cant tell..

not really

i don't have a command line on my computer

just keyboard, mouse, and monitor

**asp_funda** · May 2, 2004, 13:59

**Rynoguill** · May 3, 2004, 05:43

Originally Posted by asp_funda

If you have MySQL on your webserver, then you must've got phpMyAdmin or something like that to manage your MySQL databases through browser.

And if that's the case, then it must've some sort of export or dump option. phpMyAdmin has it by name of EXPORT. Its a tab on the top.

im still a little ignorant here, but i think youre refering to something you would get in a managed hosting environment. im on a dedicated server that i completely control as if it was my machine. i installed mysql on it myself. so would i still have the phpmyadmin, even though i dont have php?

lol rudy, so are you saying you never do anything from the command line?

**r937** · May 3, 2004, 05:53

yes, that's what i'm saying

i don't have a command line

**asp_funda** · May 3, 2004, 11:12

Originally Posted by Rynoguill

im still a little ignorant here, but i think youre refering
to something you would get in a managed hosting
environment. im on a dedicated server that i
completely control as if it was my machine. i installed
mysql on it myself. so would i still have the
phpmyadmin, even though i dont have php?

Even dedicated servers are of 2 types if I'm ot wrong. Managed & Un-managed. You can have a dedicated server managed & serviced by someone else, possibly the people from whom you've taken it. Un-managed servers are those whose everything is you & you do all the hardwork.
I think you have the latter case.
Anyway, you need to have PHP to get phpMyAdmin working. PHP is free & available at www.php.net. Also, if you have second thoughts about installing it, I suggest you look for some kind of MySQL database management script in ColdFusion(I guess that you have it installed). Try at www.sourceforge.net, its the first place to look for Open Source scripts & phpMyAdmin is also available there. Then your next stop should be www.freshmeat.net.
Or look at HotScripts & then try Google.

**Rynoguill** · May 3, 2004, 11:41

right, its unmanaged, i have full control, its basicly my computer. and honstly, i really dont want to put php on it just yet, mainly cause i really dont know much about it and dont have a need for it.

ill just stick with my command from the command line.

**asp_funda** · May 4, 2004, 10:31

as far as I know, you have full control on managed
dedicated servers too, they are your computers too.
The fact is that managed solutions are expensive than
unmanaged ones, b'coz you are charged the
maintenance costs.

Well, as you don't want to install PHP, you can look into some
ColdFusion equivalent of phpMyAdmin. I found javaMyAdmin &
jspMyAdmin for MySQL at SourceForge.net, so maybe you can
find cfMyAdmin or coldfusionMyAdmin, something done in CF to
manage MySQL.

**r937** · May 4, 2004, 11:35

i'm using mysql-front on my desktop to manage my mysql database in phoenix arizona

and if you knew how clumsy i am when it comes to installing software, that's saying a lot right there, that i was able to install it and connect it to the right port and everything (heck, i don't hardly even know what ports are)

**Rynoguill** · May 5, 2004, 05:57

Originally Posted by r937

i'm using mysql-front on my desktop to manage my mysql database in phoenix arizona

and if you knew how clumsy i am when it comes to installing software, that's saying a lot right there, that i was able to install it and connect it to the right port and everything (heck, i don't hardly even know what ports are)

rudy, that is awesome. i love this mysqlfront! ability to see source sql of tables, and the whole nine yards. i liked my mysql control center, but i love this! thanks so much for the link!

**r937** · May 5, 2004, 11:14

any time

The SitePoint Forums have moved.

User Tag List

Thread: Transfering a MySQL database

Thread Tools

Display

Transfering a MySQL database

Bookmarks

Bookmarks

Posting Permissions