2

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?

enter image description here

  • Do you want a specific student or just all students from schoolA ??? – RolandoMySQLDBA Jul 7 '15 at 5:28
  • Information in Tags table from all students in a School. – Weiest Jul 7 '15 at 5:32
  • What exact data are you dumping ??? – RolandoMySQLDBA Jul 7 '15 at 5:59
  • Please tell us what operating system you are using : Windows or Linux ??? – RolandoMySQLDBA Jul 7 '15 at 5:59
  • Just the records themselves are important, it's like an attendance system. I am using a Ubuntu MySQL database where i've information being fed in while a PHP application displays information – Weiest Jul 7 '15 at 6:06
2

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;
  • I'm not used to this method as i'm rather new to the database programming however is there a MySQL version scripting example or how do i try this script? – Weiest Jul 7 '15 at 5:55
  • Weirdly i'm having "Error Code: 1. Can't create/write to file '/var/www/html/result.csv' (Errcode: 13) 0.015 sec" even though i'm using root@% with full rights.. – Weiest Jul 7 '15 at 6:59
  • This means your /tmp partition has run out of space and the file can't be created, or for whatever reason the mysqld process cannot write to that directory because of permission problems. Sometimes this is the case when selinux rains on your parade. Any operation that requites a "temp file" will go into the /tmp directory by default. The name you're seeing is just some internal random name. – oNare Jul 7 '15 at 11:49
  • The /tmp example in my answer was cut-and-pasted from the Documentation (dev.mysql.com/doc/refman/5.6/en/select-into.html). You are right it is permissions. You would have to play games with the secure_file_priv option or you could run mysqld as root to get around such restrictions. – RolandoMySQLDBA Jul 7 '15 at 14:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.