Beyond mysqldump
4.4.2011There are several ways to transfer data from one mysql server to an other. Dumping a full table and re-importing it is rather simple, but sometimes you only want to transfer a selection of data. I will quickly explain the most common ones and then explain how i solved my way out of having neither phpmyadmin nor the privileges of SELECT...INTO OUTFILE.
My use case was the following: A client inadvertedly deleted many entries in a database table. There was no way of finding out what exactly was deleted as the application does not log deletion, so I decided to restore the backup from before the accident happened (yes, we do four backups a day and keep them a couple of weeks, and now I know why). But the last backup where the entries where not yet deleted was a week old and many changes have happened since then, thus we could not simply replace the data. Luckily, there was a lastSave timestamp for that data. Using the following query, I was able to extract all recently modified data from the crippled database for re-inseration after reverting to the backup.
SELECT tableDetails.*
FROM tableDetails
NATURAL JOIN table
WHERE table.lastSave > DATE('2011-03-22') ;
Now how to get the resulting data from one database into another?
If you have phpmyadmin installed, you can dump data into csv or excel files and re-import on the target system. Note that if you go to the export tab, you are just offered to export the full table. But you can export any selection from the SQL result view (in the Query results operations). By the way, if you have to clean up data, your office program can open these files and you can conveniently change them.
For the command line or if you have to script things, you can use mysqldump. See this article on electrictoolbox for an example of having data from just one table and filtering out parts of the data. However, afaik mysqldump does not join tables for filtering. Maybe I could have used an inner select in the where statement instead but the database is huge and it was running in production.
If you have the privileges for SELECT ... INTO OUTFILE and LOAD DATA INFILE (see mysql manual), you can dump the result of any query into a file and read data out of a file. For security reasons, this permission was not available in my case.
Creating SQL INSERT commands out of MySQL output
Inspired by a nice blog post from one Bawdo about rendering the mysql result as a csv file, I adapted his script to create data suitable for an SQL INSERT or REPLACE statement (the later avoids conflicts in case the primary key already exists).
mysql -p -u username database -B -N \
-e "SELECT * FROM table WHERE ... ;" | \
sed 's/\t/","/g;s/^/("/;s/$/"),/;s/\n//g' > tabledata.sql
The mysql switch -B tells mysql to do tab delimiting, -N to omit the column headings. The sed statement converts the tab delimited data into data enclosed with quotation marks, separated by commas and each tuple enclosed with paranthesis.
This is not fully scriptable: You will have to edit tabledata.sql to add the line INSERT INTO table VALUES at the beginning of the file and to change the very last "," after the last ")" to ";" so you get a correct SQL statement.