2

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?

2

2 Answers 2

1

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.
2
  • does that apply only to innodb or the whole thing? Where is the directory where the mysqldata files are?
    – user4951
    Jun 15, 2012 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 Jun 15, 2012 at 12:23
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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