I need to ignore all view in my database and take backup using mysqldump. Currently i am using below option.
--ignore-table=view1 --ignore-table=view2 --ignore-table=view3
Is there any way to take backup omitting all views without specifying all 'view' names.?
If the MySQL user you're using doesn't have access to read Views, then just putting an -f flag on the mysqldump command will skip them. Unfortunately, it prints a warning which might be annoying, but it will otherwise complete the dump.
Try this query:
SELECT CONCAT('mysqldump -u username -ppassword -h host [some options here] `',`TABLE_SCHEMA`,'` `',
`TABLE_NAME`,'` > ',`TABLE_NAME`,'.sql') AS `sql`
FROM `INFORMATION_SCHEMA`.`TABLES`
WHERE `TABLE_TYPE` != 'VIEW'
AND `TABLE_SCHEMA` NOT IN ('INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA','mysql');
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
system("mysqldump -u root --password=\"$password\" $database --skip-triggers ".implode(" ",$views));
?>
