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

5
  • Do you want a specific student or just all students from schoolA ??? Jul 7, 2015 at 5:28
  • Information in Tags table from all students in a School.
    – Weiest
    Jul 7, 2015 at 5:32
  • What exact data are you dumping ??? Jul 7, 2015 at 5:59
  • Please tell us what operating system you are using : Windows or Linux ??? Jul 7, 2015 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, 2015 at 6:06

1 Answer 1

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;
4
  • 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, 2015 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, 2015 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, 2015 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. Jul 7, 2015 at 14:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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