- Drop me a line! Contact me here.
Splitting an SQL dump
Splitting an SQL dump
SQL databases are a great way to store data for your website. Using a database you can quickly access lots of data in a logical and efficient manner, and you can easily edit at data with having to delve into html or text files. There are many arguments why a database is practically an essential part of even a small web site today.
As a web site grows so does the database with it. And sometimes there will be occasions when you may wish to backup the database for a website in the form of an SQL dump file. This is a file generated by either the database itself or a database management tool such as PHPMyAdmin. If your database is particularly large the resulting file may be many megabytes in size.
Unfortunately uploading very large files is not always an option if the particular settings for a server have been configured in such a way that only smaller files are allowed. Because of this if it sometimes difficult to transfer a large SQL dump file to a webserver in order to recreate the database from it. For this reason it is possible that we may need to split the file into smaller chunks.
Breaking apart a file containing thousands of SQL statements is not a job anyone would relish, so it would be much preferable to attack the file using a script.
We open the file using the text read setting as we don't need to do anything fancy with it, we're just going to read it line by line. We will also need to open 2 other files. The first is a file to write any table structure SQL to. This is so that we can recreate the database tables before trying to insert any data, and be sure that all the data inserts will have tables available to be inserted into. The other file we need to open is the first of the data files.
The next stage of splitting the file is to step through our SQL file line by line.
As we step through the lines we keep a count of how many bytes have been written to our data file. If it is above a threshold we need to close the data file and open a new one.
The next thing to do is test the SQL line. If it begins with INSERT we need to write it to the data file, if it begins with # then we drop it, and if it begins with anything else we write it to the tables file.
This will only work with MySQL SQL dump files.
As a web site grows so does the database with it. And sometimes there will be occasions when you may wish to backup the database for a website in the form of an SQL dump file. This is a file generated by either the database itself or a database management tool such as PHPMyAdmin. If your database is particularly large the resulting file may be many megabytes in size.
Unfortunately uploading very large files is not always an option if the particular settings for a server have been configured in such a way that only smaller files are allowed. Because of this if it sometimes difficult to transfer a large SQL dump file to a webserver in order to recreate the database from it. For this reason it is possible that we may need to split the file into smaller chunks.
Breaking apart a file containing thousands of SQL statements is not a job anyone would relish, so it would be much preferable to attack the file using a script.
<?php
$file = fopen("dump.sql", "r");
?>
We open the file using the text read setting as we don't need to do anything fancy with it, we're just going to read it line by line. We will also need to open 2 other files. The first is a file to write any table structure SQL to. This is so that we can recreate the database tables before trying to insert any data, and be sure that all the data inserts will have tables available to be inserted into. The other file we need to open is the first of the data files.
<?php
$tables = fopen("tables.sql", "w");
$data = fopen("data_".++$d.".sql", "w");
?>
The next stage of splitting the file is to step through our SQL file line by line.
<?php
while (!feof($file)) {
//Do stuff
}
?>
As we step through the lines we keep a count of how many bytes have been written to our data file. If it is above a threshold we need to close the data file and open a new one.
<?php
if ($count >= $maxsize) {
fclose($data);
$data = fopen("data_".++$d.".sql", "w");
$count = 0;
}
?>
The next thing to do is test the SQL line. If it begins with INSERT we need to write it to the data file, if it begins with # then we drop it, and if it begins with anything else we write it to the tables file.
<?php
$line = fgets($file, 100000);
if (substr($line,0,6) == "INSERT") {
fwrite($data,$line);
$count += strlen($line);
} elseif {
substr($line,0,1) == "#") {
//SQL Comment - Do nothing
} else {
fwrite($tables,$line);
}
?>
This will only work with MySQL SQL dump files.
Comments
Comments are not currently being accepted for this article.
Sidebar
- © Ooer.com Chris Neale 2007
- PHPGD.com
- Powered by PHP
- Database by MySQL
- DB Queries: 7
- DB Time: 0.0399 seconds
