RSS

Category Archives: mysql

[mysql | tip] Custom import huge table in phpmyadmin

For importing, huge table in phpmyadmin without using sqlyog or file uploading through ftp and even if bigdump couldn’t upload your huge file then there is an easy way around to achieve the target by custom exporting the table first into number of files by first checking out the maximum file size to be allowed for importing file.

By custom export, it means to say exporting thousands of rows in a single file as shown in following screen-shots:

custom export

custom export

OR

custom export

custom export

and thus exporting table in number of files and then import all files one by one.
While moving vBulletin post table, this really helped me 🙂

Advertisements
 
Leave a comment

Posted by on December 8, 2011 in mysql

 

Tags: , , , , ,

[mysql][error][solution] mysqldump creates empty file

Mysqldump command is used to take backup of the database. I faced an issue of empty backup file creation while running mysqldump command through php script. To debug this issue following steps are there:

1) Search mysqldump.exe or simply mysqldump in your WAMP, MAMP, LAMP, XAMPP etc directory.
2) First of all run the command in command line window (prompt). For instance mysqldump is residing at c:/lamp/bin/mysql/mysql5.5.8/bin, then from command prompt run

c:/lamp/bin/mysql/mysql5.5.8/bin/mysqldump > 
c:/lamp/backups/backupfilename.sql

and the backup file will be created with following content
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] –databases [OPTIONS] DB1 [DB2 DB3…]
OR     mysqldump [OPTIONS] –all-databases [OPTIONS]
For more options, use mysqldump –help
3) For taking backup of specific database some parameters are to be specified. Following command is there for taking backup of a specific database

c:/lamp/bin/mysql/mysql5.5.8/bin/mysqldump 
--opt --host=$server --user=$user --password=$password 
$database_name > c:/lamp/backups/backupfilename.sql

Thus, you are done with taking backing from command line by using mysqldump command.

4) Same command is used there within php script for taking backup. Exec() is the php function used for running such commands. In a php file, paste the following code

<?php
exec("c:/lamp/bin/mysql/mysql5.5.8/bin/mysqldump --opt 
--host=$server --user=$user --password=$password 
$database_name > c:/lamp/backups/backupfilename.sql");
?>

And the backup file will be created. To print the error code use

string exec ( string $command [, array &$output [, int &$return_var ]] )

and $return_var will have the error code.

5) Last step is to generalize this command for WAMP, MAMP, LAMP, XAMPP etc as per the location of mysqldump.exe file. Recursively trace all the directories within WAMP, MAMP, LAMP, XAMPP etc in order to find mysqldump.exe path.

Following code is there to do so.

function getDirectory( $path = '.'){

$ignore = array( 'cgi-bin', '.', '..','htdocs','www' );
$dh = @opendir( $path );
while( false !== ( $file = readdir( $dh ) ) ){
if( !in_array( $file, $ignore ) ){
if( is_dir( "$path/$file" ) ){
getDirectory( "$path/$file");
} else {
if($file == 'mysqldump')
{
$_SESSION['mysqldump_location'] = $path.'/';
break;
}
}
}
}
closedir( $dh );
}
$_SESSION['mysqldump_location'] = '';
getDirectory($root);
$src = $root."/backup.sql";
$cmd = $_SESSION['mysqldump_location']."mysqldump 
--opt --host=$host --user=$user --password=$pass $name >"
.$src;
exec($cmd,$output,$rtn);

Thats it 🙂

 
1 Comment

Posted by on November 18, 2011 in mysql

 

Tags: , , , ,

 
%d bloggers like this: