Database Dump/Backup but only to specific records?

Question

This question is about MySQL Backup (or Dump) whereby i want to backup and store information from only information related to a person; SchoolA from the School Table in this example.

So, I should not be able to see any information from other schools using that backup.

I want to give this backup information to that school. However, i do not want them to see information from other schools.

Is there anyway to achieve this?

Answer 1

You would dump each table using the --where option. I wrote a rather gnarly post about 4 years ago : Is it possible to mysqldump a subset of a database required to reproduce a query?

In your case, you can do the following: Suppose the database is called schooldb

Dump by SchoolID

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
OPTS="--lock-all-tables"
SCHOOLID=1
WHERE="tagID in (select tagID from Ownership where studentID in"
WHERE="${WHERE} (select studentID from Students where schoolID = ${SCHOOLID}))"
mysqldump ${MYSQL_CONN} ${OPTS} --where="${WHERE}" schooldb tags > tags.sql

Dump by School Name

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
OPTS="--lock-all-tables"
SCHOOL_NAME=schoolA
WHERE="tagID in (select tagID from Ownership where studentID in"
WHERE="${WHERE} (select studentID from Students where schoolID in"
WHERE="${WHERE} (select schoolID from Schools where name='${SCHOOL_NAME}')))"
mysqldump ${MYSQL_CONN} ${OPTS} --where="${WHERE}" schooldb tags > tags.sql

GIVE IT A TRY !!!

If you are using mysql client only, you could dumping SELECT ... INTO

SELECT B.studentID,B.name,D.tagID,D.data
INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM Schools A
INNER JOIN Students  B ON A.SchoolID  = B.SchoolID
INNER JOIN Ownership C ON B.studentID = C.studentID
INNER JOIN Tags      D ON C.tagID     = D.tagID
WHERE A.name = 'schoolA';

or

SELECT B.studentID,B.name,D.tagID,D.data
INTO OUTFILE '/tmp/result.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM Schools A
INNER JOIN Students  B ON A.schoolID  = B.schoolID
INNER JOIN Ownership C ON B.studentID = C.studentID
INNER JOIN Tags      D ON C.tagID     = D.tagID
WHERE A.schoolID = 1;