CODEWITHSUNDEEP
mysqlexport
dole doug
dole doug

Reputation: 36048

mysql dump tables only

In my database I have some tables and views. How can I export all the tables (and not the views) from my database from command line?

Upvotes: 25

Views: 69544

Answers (7)

naveen_sfx
naveen_sfx

Reputation: 747

To ignore a single view from your DB for Dump:

mysqldump -uusrname -ppwd -h hostname --ignore-table=db.view_name db > db.sql

To ignore multiple view from your Db for Dump:

mysqldump -uusrname -ppwd -h hostname --ignore-table=db.view1 --ignore-table=db.view2 db > db.sql

NOTE: to ignore multiple views for dump use --ignore-table option multiple times.

Upvotes: 31

programaths
programaths

Reputation: 891

Updated @ladieu's script:

  • Shell arguments are properly escaped permitting passwords with shell caracters
  • Using PDO instead of mysql specific functions (sometime an IP seems to be resolved against DNS with the mysql_* functions and thus trigger an error)
  • Removed assumption that the script is used locally (added host parameter)
  • Using the "-r" options to avoid encoding issues
  • name the output file .sql
  • rearranged parameters so it does fit in "I want to connect to with user identified by and dump database>
<?php

if (is_array($argv) && count($argv)>3) {
    $host=($argv[1]);
    $user=($argv[2]);
    $password=($argv[3]);
    $database=($argv[4]);

}
else {
    echo "Usage php mysqdump.php <host> <user> <password> <database>\n";
    exit;
}

echo 'connecting to'.$host;

$pdo=new PDO("mysql:host=$host;dbname=$database",$user,$password);

//$link = mysql_connect($host, $user, $password);

//$source = mysql_select_db($database, $link);
$sql = "SHOW FULL TABLES IN `$database` WHERE TABLE_TYPE LIKE 'VIEW';";
$result = $pdo->query($sql);
$views=array();
while ($row = $result->fetch()) {
   $views[]="--ignore-table={$database}.".$row[0];
}
//no views or triggers please
$host=escapeshellarg($host);
$user=escapeshellarg($user);
$password=escapeshellarg($password);
$database=escapeshellarg($database);
echo passthru("mysqldump -h $host -u $user --password=\"$password\" $database --skip-triggers ".implode(" ",$views)." -r $database.sql");

Enjoy !

Upvotes: 3

Sandip Ghosh
Sandip Ghosh

Reputation: 7

Try the following:

mysqldump -h drupdbsvr  -u elt_drupal_dba -pdrupdev-654! clms_admin views_display views_view view_argument view_exposed_filter view_filter view_sort view_tablefield view_view > /opt/dbdump/clms_admin_view.sql

Where the syntax is:-

mysqldump -h hostname -u database_user_name database_password database_name table_name1 table_name2 > path/sql_file.sql

Upvotes: -2

ladieu
ladieu

Reputation: 115

Usage

php mysqldump.php mydatabase myusername mypassword > myoutputfile.sql

This is a pretty old script of mine. Someone could easily adapt this to use PDO if you do not have access to the mysql functions. 

<?php

if (is_array($argv) && count($argv)>3) {
    $database=$argv[1];
    $user=$argv[2];
    $password=$argv[3];
}
else {
    echo "Usage php mysqdump.php <database> <user> <password>\n";
    exit;
}

$link = mysql_connect('localhost', $user, $password);


if (!$link) {
    die('Could not connect: ' . mysql_error());
}

$source = mysql_select_db('$database', $link);
$sql = "SHOW FULL TABLES IN `$database` WHERE TABLE_TYPE LIKE 'VIEW';";
$result = mysql_query($sql);
$views=array();
while ($row = mysql_fetch_row($result)) {
   $views[]="--ignore-table={$database}.".$row[0];
}
//no views or triggers please
echo passthru("mysqldump -u root --password=\"$password\" $database --skip-triggers ".implode(" ",$views));

?>

Upvotes: 0

Green Card
Green Card

Reputation: 379

Backuping a single table from a database

mysqldump -uUSERNAME -pPASWORD DATABASE TABLE_NAME --host=HOST_NAME > c:\TABLE_NAME.sql

Restoring a single table from a database dump

mysql -uUSERNAME -pPASSWORD DATABASE --host=HOST_NAME < c:\TABLE_NAME.sql

Upvotes: 9

tylerl
tylerl

Reputation: 30847

The current implementation mysqldump won't create dumps without views -- and furthermore, (last time I checked) views are actually created twice -- once as a table, then the table is dropped and replaced with a view. So you can't just filter out the "CREATE VIEW" command, unless that behavior has been modified.

However, mysqldump will take a list of tables as parameters following the database name. Something like this:

mysqldump -ujoe -pmysecret joesdb posts tags comments users

Upvotes: 29

soulmerge
soulmerge

Reputation: 75704

You can use mysqldump with the option --ignore-table to exclude the views individually. Or use mysqldump and remove the views with an application/manually. grep might be an option:

grep -v "CREATE VIEW" db.dump > db-without-views.dump

Upvotes: -2

Related Questions