Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In MySQL?

Say I have a local database. Now I want the remote database to mimic the local database.

One way I can think of is to simply dump the database, upload and let things work.

Well, database is large.

Another I can think of is connecting straight to the database and fill the line one by one with a dedicated vb.net program.

Sometimes I just want to create a back up database in the same server.

How does the pro do so?

share|improve this question
    
Check Rolando's answers. One for example: Difference between backup methods: Oracle and MySQL –  ypercube Jun 15 '12 at 10:54
    
And this is even better I think: How can I move a database from one server to another? –  ypercube Jun 15 '12 at 10:55
add comment

2 Answers

up vote 1 down vote accepted

You don't even need mysqldump if you're moving a whole database schema, and you're willing to stop the first database (so it's consistent when being transfered)

  1. Stop the database (or lock it)

  2. Go to the directory where the mysql data files are.

  3. Transfer over the folder (and its contents) over to the new server's mysql data directory

  4. Start back up the database
  5. On the new server, issue a 'create database' command.'
  6. Re-create the users & grant permissions.
share|improve this answer
    
does that apply only to innodb or the whole thing? Where is the directory where the mysqldata files are? –  Jim Thio Jun 15 '12 at 10:39
    
Execute this query SHOW VARIABLES LIKE 'datadir'; to get /Datadir/ path and copy entire /datadir/ as innodb tablespace files resides outside database directory where as MyISAM files resides inside database directory –  Mahesh Patil Jun 15 '12 at 12:23
add comment

for large database you need to use command line to (clean) dump local database and after that in remote server to import (with ssh connection and also command line mysql-import or directly after connect to mysql), you need to sepcify your environment (windows, mac..)

some sources:

-http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html

-http://www.lullabot.com/blog/importexport-large-mysql-databases

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.