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
  • 2
  • 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
  • 52,423
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,873
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,631