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.

Suhail Gupta
  • 159
  • 1
  • 9

3 Answers3

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)
quanta
  • 51,413
  • 19
  • 159
  • 217
  • 1
    can't I just carry the directory named _music_ under `/var/lib/mysql/` ? – Suhail Gupta Sep 21 '12 at 07:22
  • Sure, you can. But what happens if you have changed the `datadir` in `/etc/my.cnf`? – quanta Sep 21 '12 at 07:24
  • and how can the method you mentioned, avoid this ? – Suhail Gupta Sep 21 '12 at 07:29
  • It get the `datadir` in `/etc/my.cnf` instead of looking in default location `/var/lib/mysql`. – quanta Sep 21 '12 at 07: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 01: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).

Dom
  • 6,743
  • 1
  • 20
  • 24
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.

golja
  • 1,621
  • 10
  • 14