1

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.

3 Answers 3

2

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)
5
  • 1
    can't I just carry the directory named music under /var/lib/mysql/ ? Sep 21, 2012 at 7:22
  • Sure, you can. But what happens if you have changed the datadir in /etc/my.cnf?
    – quanta
    Sep 21, 2012 at 7:24
  • and how can the method you mentioned, avoid this ? Sep 21, 2012 at 7:29
  • It get the datadir in /etc/my.cnf instead of looking in default location /var/lib/mysql.
    – quanta
    Sep 21, 2012 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, 2012 at 1:15
0

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).

1
  • 1
    Except he's not using debian. But thankfully, the dir is usually the same. Sep 21, 2012 at 7:11
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .