Moving MySQL databases

[Foxy]

Does anybody have experience in moving a MySql DB from say a server in the UK to a server in USA?

I wish to move such a beast and have no experience so I thought I would ask to avoid some [if any] pitfalls

eg How do I do it? :)

[digitalpoint]

Couple ways to do it...

If you have access to the server as an administrator, you can stop mysql altogether and grab the files (on RedHat the command is):

/etc/init.d/mysql stop

Another way to do it as an administrator is to SSH (or telnet) into the box and run the backup script that comes with mysql:

/usr/bin/mysqlhotcopy [database name] [destination]

Either of those methods will get you access to the raw MySQL files, which you could copy to the new machine and stat the MySQL daemon and it should work (assuming you put them into the right directory for mysqld on the new machine).

If you don't have access to the server itself, you may need to install something like phpmyadmin, do a dump of your database (both structure and data) and then insert it on the new one (also would use phpmyadmin on the new one).

- Shawn

[Tomas]

'luck' has it i had to do something similar this week whilst having no experience whatsoever with mysql.

I struggled for quite a while, untill i installed mysql on my local machine and used the command line interface to connect to the website database

what i did was export the DB to my pc, and then upload it again to the new site. this assumes that the db structure on the new site is already in place and it's the same as the old db.

the following command exports the db to your pc:

SELECT * INTO OUTFILE '<YourFileName>.txt' fields terminated by '*' optionally enclosed by '"' lines terminated by '\r\n' from <TableName>;

to import use :
load data infile '<YourFileName>.txt' into table <TableName> fields terminated by ";" optionally enclosed by '"' lines terminated by '\r\n';

there is a command that dumps the whole db in one go as well, but it didn't work for me, so i used the above solution. Luckily i didn't need to transfer a lot of tables.

Good luck

[Foxy]

Thankyou guys

Just what I wanted

The last thing I needed was to fluff around [if I ever could!] trying to work out titchy problems

Now this is what sets this forum on the plain above all the others - thankyou Shawn once again for the Forum [and the response] and thankyou Thomas for the "luck has it"

Yes :D

PS for Shawn

1. Shawn you were here when I came on line at 7.30am this morning and did not [as far as I know] leave until about 8.45am [00.45am your time] - What do you run on - liquid oxygen?

2. BTW Did you buy that G5?

[digitalpoint]

6 hours sleep. :)

No new desktop Macs until I can get a dual 3Ghz G5 with 30" flat panel (this summer hopefully). Although I do think I'm going to pick up the new 15" Powerbook. I have a 15" Titanium already, but the backlit keyboard is really nice (and everything else too).

- Shawn

[Foxy]

mmmm

I think I will follow your lead with that G5 - this summer too

I haven't looked at the powerbooks - will do - we run our laptops on 15" ibooks - they are cool

[sarahk]

I just had to do that for a client, the mysql dump was 45MB!
I had to do it the hard way with downloading the dump and then uploading, not telnet access.

What I'd do differently next time:
* get the create table script and then the inserts - this may require some manual editing. Some of the old table had columns like "desc" but the generated script failed because desc needed to be written `desc` - easier to clean up one script then run the inserts.
* break the insert script up into parts

[Foxy]

Thanks SarahK

I will let you all know when, or if, more likely, I succeed first time in moving successfully :)

[mopacfan]

Unless you're using innodb database tables, you just copy the entire database folder under /mysql/data/ and paste it into the corresponding folder on the new server. The next time you launch the control center, it will 'register' the database. You may need to create/edit users and permissions, but that's all there is to it.

MN

[mushroom]

If you don't have access to the server itself, you may need to install something like phpmyadmin, do a dump of your database (both structure and data) and then insert it on the new one (also would use phpmyadmin on the new one).

- Shawn
Rather pointless you require root access to the server in order to modify the config file and root access to MySql. :confused:

[digitalpoint]

No you don't... you don't have to have root access to use phpmyadmin.

[mushroom]

No you don't... you don't have to have root access to use phpmyadmin.

I agree but one post said install the other said use .

[digitalpoint]

Well you don't need root to install it either.

[mushroom]

Well you don't need root to install it either.

Are you saying that you can install it and configure it so that it
works without root access or knowing MySql's root password?

[digitalpoint]

Correct... You can install and use it with a secondary login/password.

[onlineshopping]

I am just doing search on it, but really dont find any good results.

[picos]

only moving a mysql database, just use import/export tool of phpmyadmin. you can move ur site properly.