Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I installed MySql using :

yum install mysql-server

It is working fine from the terminal.I created a database named music and created some tables inside this database . Now I want to port the entire database to some other machine. How do I do that ?

In general where can I find the database files and the table files ? I searched a lot by the keyword music but couldn' find it.

share|improve this question

3 Answers 3

up vote 2 down vote accepted

Now I want to port the entire database to some other machine. How do I do that ?

mysqldump -u root -p music | mysql -u root -p -h <other.machine.ip.addr>

In general where can I find the database files and the table files ?

They are located in the datadir of MySQL:

ls $(awk -F= '/datadir/ { print $2 }' /etc/my.cnf)
share|improve this answer
1  
can't I just carry the directory named music under /var/lib/mysql/ ? –  Suhail Gupta Sep 21 '12 at 7:22
    
Sure, you can. But what happens if you have changed the datadir in /etc/my.cnf? –  quanta Sep 21 '12 at 7:24
    
and how can the method you mentioned, avoid this ? –  Suhail Gupta Sep 21 '12 at 7:29
    
It get the datadir in /etc/my.cnf instead of looking in default location /var/lib/mysql. –  quanta Sep 21 '12 at 7:31
    
my.cnf can also be located in /etc/mysql/my.cnf. you can quite savely carry the directory for a database from /var/lib/mysql/ if you stop mysql before you do so: /etc/init.d/mysql stop. (although a dump is always the clean method, if you want to be safe) –  rubo77 Sep 22 '12 at 1:15

On Debian, the databases are stored in /var/lib/mysql. To port them, you can use mysqldump -A -u root -p > dumpfile and restore them in the new machine by mysql -u root -p < dumpfile or by copying the folders (don't forget to chown the files after transfert).

share|improve this answer
1  
Except he's not using debian. But thankfully, the dir is usually the same. –  Sonassi Sep 21 '12 at 7:11

Depends what type of SQL engine you use. Probably InnoDB which stores all the databases in few files (usually /var/lib/mysql/ibdata*). So for the backup you will need to use mysqldump.

For backup use something like that:

mysqldump --hex-blob -u${BUSER} -p${BPASS} ${database} -r  ${BACKUP_DIR}/${database}.mysql

For restore copy ${BACKUP_DIR}/${database}.mysql on the other machine and execute:

mysql -u${BUSER} -p ${database} < ${database}.mysql

Of course don't forget to create the empty database first CREATE DATABASE name. Also as already suggested you can use -A with mysqldump but that will dump all databases.

share|improve this answer

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.