Andrea485 Andrea485 - 1 year ago 71
MySQL Question

Backup restore mysql from query of single table

I have a database with three tables .
I want to create a backup file of some records in the second table :

mysqldump --opt --user=${USER} --password=${PASS} --databases ${DATABASE} --where="id = $1" mydb Table2 > FILE.sql

the problem is in the restore by using this code .

mysql --user=${USER} --password=${PASS} ${DATABASE} < FILE.sql

It deletes the entire database and inserts I only selected records from the previous code .
I wish only the selected records restore without deleting the rest .

Answer Source

You are using the [--databases][1] flag which is used when dumping several databases and should not be used when you want to dump only a single table.

Dump several databases. Normally, mysqldump treats the first name argument on the command line as a database name and following names as table names. With this option, it treats all name arguments as database names. CREATE DATABASE and USE statements are included in the output before each new database.

Avoid using that argument and for good measure use --no-create-info or simply -t instead to make sure create table / drop table commands are not in the dump file

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download