Database Backup

[lotus18]

Hello World!

I created a database here on my pc using mysql and its database engine is innodb. but when i try to copy the its database file to another pc, it says my tables are not existing. And I checked the phpmyadmin and create another table i cannot see innodb engine, myisam is now the default. what maybe the problem? please guide me.

Rey Sean

[coolsti]

I would not do a database copy in that manner.

I would use the "mysqldump" function to dump the database that you wish to copy to a file, then copy the file to the other machine, and then read in the database to the new machine using the "mysql" function.

When I use mysqldump to dump just one database (as opposed to more than one), I open the file that is created, and add a "USE mydatabase" as the first line, where mydatabase is substituted with the database name, just to make sure I don't accidentally attempt to add the database tables to the wrong database on the target machine.

On the target machine, you may have to first create the database if it isn't already created. Then you can just read in your file using the command line mysql command:

mysql -u myuser -p mydatabase < myfile.sql

where myuser, mydatabase and myfile.sql are substituted for the user name, the database name, and the file name that you created with the mysqldump.

The above works on Linux, I am not sure if you need to change the command line I show above a bit for Windows, as the "< myfile.sql" part is the Linux way of telling mysql to read commands in from the myfile.sql.

[lotus18]

When i was using myisam, it works fine when i transfer the file from one pc to another. but in innodb can't. I don't really know mysqldump but I think this is the right option to backup and restore database. Can you give me a better example? Syntax?

[coolsti]

I can't give you an exact syntax example at the moment, but you can easilly find it in the documentation online. Just Google it.

You feed it arguments that state the user name and password, as your credentials for being allowed to make the dump must be checked. You also feed it an argument saying which database or databases you want to dump. You also have optional arguments on whether the dump will be optimized, etc. And lastly you somehow give the filename that the dump should be made to (which again as with mysql, may differ on Windows from Linux in syntax).

The reason why this is safer than copying tables is because doing the mysqldump writes the data in a form that is just a file of sql statements, which then should be able to be read back into a mysql database, either on the same machine or on another machine, as long as there is not too big a difference in mysql versions. When you just make a copy of tables, as you have done for your MyISAM tables, you are just assuming that the tables contain all the information needed for the databases, and that the tables' file structure is equivalent for both machines. I would suspect that Innodb database tables have something that you are not completely copying when you do it that way. Perhaps other associated files or configurations that you don't know about.