mysqldump doesn't backup views as views

mysqldump does not dump views as views, but instead, dumps them as tables. Even then, the tables columns all come out as TINYINT(4). If you're not fortunate enough to have Percona's slick backup tools then you'll need another solution.

Here is an over simplified PHP script to get the job done. Simply pipe the output to a .sql file to get it into a neat SQL script file.

<?php
// Configuration for the DB Connection
$_db_name = "test";
$_db_host = "127.0.0.1";
$_db_user = "root";
$_db_pass = "";
$_db_port = '3306';
// ---- end configuration section
$mysqli = new mysqli($_db_host, $_db_user, $_db_pass, $_db_name, $_db_port);
if(!$mysqli) die("Connection error. Aborting\n");
$result = $mysqli->query("SELECT v.TABLE_NAME, v.VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS `v` WHERE v.TABLE_SCHEMA='test';");
if (!$result) die("The 1st query failed to return results. Aborting.\n");
$row = $result->fetch_all();
foreach($row as $a => $b)
{
    echo "CREATE VIEW `".$b[0]."` AS ".$b[1].";\n\n";
}