nmcgann/mysql_backup.class.php

## mysql_backup.class.php
<?php
/*
 * PHP backup class. Based on an old script updated to use mysqli_ routines.
 * Extended and bugfixed - now handles NULL's, doesn't quote numbers, handles utf-8,
 * creates multiple inserts and breaks to a new one after a defined number, creates gzip
 * or zip files, generates foriegn key disable and table lock/unlock statements.
 * Handles Views, Triggers, Procedures and Functions.
 * Optionally removes DEFINER from views/procs/funcs and DB names from views.
 *
 * Thanks to Phil Rodgers (https://github.com/placeposition) for spotting the field type
 * issues and coming up with the fix that I extended.
 *
 * Thanks to https://github.com/sequelpro/sequelpro/issues/313 for the clues of how
 * to fix the create view before referenced tables issue.
 *
 * Neil McGann. 16th March 2017
 *
 * The MIT License (MIT)
 *
 * Copyright (c) 2015 Neil McGann
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in
 * all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 * THE SOFTWARE.
 *
 * Parameters:
 * $config = [];
 * $config['db_host'] = 'MY_DB_HOST';
 * $config['db_user'] = 'MY_DB_USER';
 * $config['db_password'] = 'MY_DB_PASSWORD';
 * $config['db_name'] = 'MY_DB_NAME';
 * $config['db_port'] = 3306;
 * $config['remove_definer'] = true; //removes definer from views, triggers, procedures and functions
 * $config['remove_db_name_from_views'] = true; //views can use the full `db`.`table`.`field` format which is awkward
 * $config['new_insert_after_n'] = 1000; //break insert after this number of records
 * $config['include_triggers'] = true;
 * $config['include_procedures'] = true;
 * $config['include_functions'] = true;
 *
 * Usage:
 * $backupObj = new MysqlBackup($config);
 * $dump = backupDb(); //no param for all tables, or an array of names, or comma separated list.
 * $dump is all the returned sql statements.
 *
 * Class throws exceptions on errors.
 *
 * Interesting things to do: Convert to PDO (I have only needed mysql so far &
 * I run this from a cli script);
 *
 */

    class MysqlBackup{

        public $errorMessage;
        public $config;
        public $link;

        public function __construct($config = array()){
            if(!is_array($config) || empty($config)){
                $this->errorMessage = 'MysqlBackup - config parameters missing.';
                throw new \Exception($this->errorMessage);
            }
            //defaults
            $config['include_triggers'] = isset($config['include_triggers']) ? $config['include_triggers'] : true;
            $config['include_procedures'] = isset($config['include_procedures']) ? $config['include_procedures'] : true;
            $config['include_functions'] = isset($config['include_functions']) ? $config['include_functions'] : true;
            $config['new_insert_after_n'] = isset($config['new_insert_after_n']) ? $config['new_insert_after_n'] : 1000;
            $config['remove_definer'] = isset($config['remove_definer']) ? $config['remove_definer'] : true;
            $config['remove_db_name_from_views'] = isset($config['remove_db_name_from_views']) ? $config['remove_db_name_from_views'] : true;
            $config['db_port'] = isset($config['db_port']) ? $config['db_port'] : 3306;

            $this->config = $config;

            //generate exceptions for errors
            mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

            $link = @mysqli_connect($this->config['db_host'], $this->config['db_user'],
                    $this->config['db_password'], $this->config['db_name'], $this->config['db_port']);

            if($link === false) {
                $this->errorMessage = "MysqlBackup - failed to open database connection: ".mysqli_connect_error()."\n";
                throw new \Exception($this->errorMessage);
            }

            if (!@mysqli_set_charset($link, "utf8")) {
                $this->errorMessage = sprintf("MysqlBackup -  Error loading character set utf8: %s\n", mysqli_error($link));
                throw new \Exception($this->errorMessage);
            }

            $this->link = $link;
        }

        public function __destruct(){
            mysqli_close($this->link);
        }

        /*
         * backup the entire db OR just a table (comma separated list)
         */
        public function backupDb($tables = '*') {

            $return = '';
            $return_view = '';

            //if wildcard, get all of the tables including views
            if($tables == '*') {
                $tables = array();
                $result = mysqli_query($this->link, 'SHOW TABLES');
                while($row = mysqli_fetch_row($result)) {
                      $tables[] = $row[0];
                }

            } else {
                   $tables = is_array($tables) ? $tables : explode(',', $tables);
                   $tables = array_map('trim', $tables);
            }

            //interogate table fields to check type so data escaping can be done correctly
            $fields = array();

            foreach ($tables as $table) {

                if (!isset($fields[$table])) {
                    $fields[$table] = array();
                }

                $result = mysqli_query($this->link, 'SHOW COLUMNS FROM `'.$table.'`');
                $f = 0;

                while ($row = mysqli_fetch_row($result)) {
                    //numeric is default
                    $fields[$table][$f] = false;

                    if (preg_match('/char|text|blob|binary|enum|set|date|time|year/i', $row[1])){
                        //string
                        $fields[$table][$f] = 1;
                    }else if(stripos($row[1], 'bit') !== false){
                        //bit
                        $fields[$table][$f] = 2;
                    }
                    $f++;
                }
            }

            //get all VIEWS
            $views = [];
            $result = mysqli_query($this->link, "SHOW FULL TABLES WHERE TABLE_TYPE = 'VIEW'");
            while($row = mysqli_fetch_row($result)) {
                  $views[] = $row[0];
            }

            $return .= "# ************************************************************\n";
            $return .= "# Database: {$this->config['db_name']}\n";
            $return .= "# Generation Time: ". date('Y-m-d H:i:s')."\n";
            $return .= "# ************************************************************\n\n";

            //temp disable foreign key checks
            $return .= "SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;\n";
            $return .= "SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;\n";
            $return .= "SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;\n";
            $return .= "SET NAMES utf8;\n";
            $return .= "SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;\n";
            $return .= "SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';\n";
            $return .= "SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;\n\n";

            //cycle through tables creating schema then data dump
            foreach($tables as $table) {

                if(in_array($table, $views)){
                   //VIEWS

                    //This uses the same approach as mysqldump. A temporary table with the same column names
                    //is created first as a placemarker. After all other tables have been created each view
                    //is created properly after deleting the temporary table. This handles the case of
                    //views referring to other views having to be defined in the exact correct order as
                    //there is always a dummy temporary table of the same name present to stand in.

                    $return .= "# Dump of view $table - CREATE TEMPORARY TABLE \n";
                    $return .= "# ------------------------------------------------------------\n";

                    $return .= "DROP TABLE IF EXISTS `".$table."`;\n";
                    $return .= "DROP VIEW IF EXISTS `".$table."`;\n";

                    //get the resulting columns of the created view and make a temporary table
                    $return .= "CREATE TABLE `".$table."` (";

                    $result = mysqli_query($this->link, "SHOW COLUMNS FROM `" . $table ."`");

                    while($row3 = mysqli_fetch_row($result)){
                        $return .= "\n`".$row3[0]."` tinyint NOT NULL,";
                    }
                    $return = substr($return, 0, -1); //remove comma
                    $return .= ") ENGINE=MyISAM;\n\n";

                    //Get the proper view definition
                    $result = mysqli_query($this->link, 'SHOW CREATE TABLE `' . $table .'`');
                    $row2 = mysqli_fetch_row($result);

                    $return_view .= "# Dump of view $table\n";
                    $return_view .= "# ------------------------------------------------------------\n";
                    $return_view .= "DROP TABLE IF EXISTS `".$table."`;\n";

                    //a view - later versions of mysql need an explicit drop view
                    $code = $row2[1];
                    //optionally remove db name from `db`.`table`.`field` format
                    if($this->config['remove_db_name_from_views']){
                        $code = preg_replace('/(\s+|,|\()`'.$this->config['db_name'].'`\./', '$1', $code);
                    }
                    //optionally remove definer
                    if($this->config['remove_definer']){
                        $return_view .= preg_replace('/^(.*?)DEFINER=`.*?`@`.*?`(.*)/','$1$2', $code). ";\n\n";
                    }else{
                        $return_view .= $code . ";\n\n";
                    }

                } else {
                    //TABLES
                    $return .= "# Dump of table $table\n";
                    $return .= "# ------------------------------------------------------------\n";

                    $return .= "DROP TABLE IF EXISTS `".$table."`;\n";

                    $result = mysqli_query($this->link, 'SHOW CREATE TABLE `' . $table .'`');
                    $row2 = mysqli_fetch_row($result);

                    $return .= "\n" . $row2[1] . ";\n\n";
                }

                //views don't need the data dump
                if(!in_array($table, $views)) {
                    //not a view - do the full table dump

                    $result = mysqli_query($this->link,'SELECT * FROM `'.$table.'`');
                    $num_fields = mysqli_num_fields($result);

                    $return .= 'LOCK TABLES `' . $table . '` WRITE;' . "\n";
                    $return .= 'ALTER TABLE `' . $table . '` DISABLE KEYS;' . "\n";

                    $i=0;
                    while($row = mysqli_fetch_row($result)) {
                        if($i++ == 0){
                            $return .= 'INSERT INTO `'.$table.'` VALUES(';
                        }else {
                            $return .= ",\n(";
                        }
                        for($j = 0; $j < $num_fields; $j++) {

                            //special handling for nulls
                            if(is_null($row[$j])){
                                $return .= 'NULL';
                            }else {
                                //no need to escape numeric
                                if($fields[$table][$j] === false){
                                    //numeric
                                    $return .= $row[$j];
                                }else if ($fields[$table][$j] === 1){
                                    //string
                                    $row[$j] = addslashes($row[$j]); //ok on utf8
                                    //$row[$j] = mb_addslashes($row[$j],'UTF-8');
                                    $row[$j] = str_replace(array("\n","\r"),array("\\n","\\r"), $row[$j]);

                                    $return .= "'".$row[$j]."'";
                                }else if ($fields[$table][$j] === 2){
                                    //bit type
                                    $return .= sprintf("b'%b'", $row[$j]);
                                }
                            }
                            if ($j < ($num_fields-1)) {
                                $return .= ',';
                            }
                        }

                        $return .= ")";

                        //force a new insert generated every N rows
                        if($i > $this->config['new_insert_after_n']){
                            $i = 0;
                            $return .= ";\n";
                        }
                    }

                    $return .= ";\n";

                    $return .= "ALTER TABLE `". $table . "` ENABLE KEYS;\n";
                    $return .= "UNLOCK TABLES;\n\n";
                }

                //DUMP TRIGGERS FOR TABLE
                if($this->config['include_triggers']){
                    $triggers = array();
                    $result = mysqli_query($this->link, "SHOW TRIGGERS LIKE '$table'");

                    while($row = mysqli_fetch_row($result)) {
                          $triggers[] = $row[0];
                    }

                    if(!empty($triggers)){
                        $return .= "DELIMITER $$\n\n";

                         foreach($triggers as $trigger){

                            $return .= "# Dump of TRIGGER $trigger\n";
                            $return .= "# ------------------------------------------------------------\n";
                            $return .= "DROP TRIGGER IF EXISTS `$trigger`$$\n";

                            $result = mysqli_fetch_array ( mysqli_query ($this->link, "SHOW CREATE TRIGGER $trigger"));
                            $code = $result[2];
                            //remove definer
                            $return .= ($this->config['remove_definer']) ? preg_replace('/^(.*?)DEFINER=`.*?`@`.*?`(.*)/','$1$2',$code) : $code;
                            $return .= "$$\n";
                        }
                        $return .= "DELIMITER ;\n\n";
                    }
                }
            }

            //DUMP PROCEDURES
            if($this->config['include_procedures']){
                $procedures = array();
                $result = mysqli_query($this->link, "SHOW PROCEDURE STATUS WHERE Db = '{$this->config['db_name']}'");

                while($row = mysqli_fetch_row($result)) {
                      $procedures[] = $row[1];
                }

                if(!empty($procedures)){
                    $return .= "DELIMITER $$\n\n";
                    foreach($procedures as $procedure){

                        $return .= "# Dump of PROCEDURE $procedure\n";
                        $return .= "# ------------------------------------------------------------\n";
                        $return .= "DROP PROCEDURE IF EXISTS `$procedure`$$\n";
                        $result = mysqli_fetch_array ( mysqli_query ($this->link, "SHOW CREATE PROCEDURE $procedure"));
                        $code = $result[2];
                        //remove definer
                        $return .= ($this->config['remove_definer']) ? preg_replace('/^(.*?)DEFINER=`.*?`@`.*?`(.*)/','$1$2', $code) : $code;
                        $return .= "$$\n";
                    }
                    $return .= "DELIMITER ;\n\n";
                }
            }
            //DUMP FUNCTIONS.
            if($this->config['include_functions']){
                $functions = array();
                $result = mysqli_query($this->link, "SHOW FUNCTION STATUS WHERE Db = '{$this->config['db_name']}'");

                while($row = mysqli_fetch_row($result)) {
                      $functions[] = $row[1];
                }

                if(!empty($functions)){
                    $return .= "DELIMITER $$\n\n";
                    foreach($functions as $function){

                        $return .= "# Dump of FUNCTION $function\n";
                        $return .= "# ------------------------------------------------------------\n";
                        $return .= "DROP FUNCTION IF EXISTS `$function`$$\n";
                        $result = mysqli_fetch_array ( mysqli_query ($this->link, "SHOW CREATE FUNCTION $function"));
                        $code = $result[2];
                        //remove definer
                        $return .= ($this->config['remove_definer']) ? preg_replace('/(.*?)DEFINER=`.*?`@`.*?`\s*(.*)/','$1$2', $code) : $code;
                        $return .= "$$\n";
                    }
                    $return .= "DELIMITER ;\n\n";
                }
            }

            //add views in last so all tables have been created first
            $return .= $return_view;

            //restore foreign key checks
            $return .= "SET SQL_NOTES=@OLD_SQL_NOTES;\n";
            $return .= "SET SQL_MODE=@OLD_SQL_MODE;\n";
            $return .= "SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;\n";
            $return .= "SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;\n";
            $return .= "SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;\n";
            $return .= "SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;\n";

            return $return;

        }

        //standard addslashes is ok on UTF8 - this mb version is slow, so don't use unless necessary
        //https://gist.github.com/yuya-takeyama/402780
        public function mb_addslashes($input, $enc = NULL)
        {
            if (is_null($enc)) {
                $enc = mb_internal_encoding();
            }
            $len = mb_strlen($input, $enc);
            $result = '';
            for ($i = 0; $i < $len; $i++)
            {
                $char = mb_substr($input, $i, 1, $enc);
                if (strlen($char) === 1) {
                    $char = addslashes($char);
                }
                $result .= $char;
            }
            return $result;
        }

    } //eoc

/* end */
	<?php
	/*
	* PHP backup class. Based on an old script updated to use mysqli_ routines.
	* Extended and bugfixed - now handles NULL's, doesn't quote numbers, handles utf-8,
	* creates multiple inserts and breaks to a new one after a defined number, creates gzip
	* or zip files, generates foriegn key disable and table lock/unlock statements.
	* Handles Views, Triggers, Procedures and Functions.
	* Optionally removes DEFINER from views/procs/funcs and DB names from views.
	*
	* Thanks to Phil Rodgers (https://github.com/placeposition) for spotting the field type
	* issues and coming up with the fix that I extended.
	*
	* Thanks to https://github.com/sequelpro/sequelpro/issues/313 for the clues of how
	* to fix the create view before referenced tables issue.
	*
	* Neil McGann. 16th March 2017
	*
	* The MIT License (MIT)
	*
	* Copyright (c) 2015 Neil McGann
	*
	* Permission is hereby granted, free of charge, to any person obtaining a copy
	* of this software and associated documentation files (the "Software"), to deal
	* in the Software without restriction, including without limitation the rights
	* to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
	* copies of the Software, and to permit persons to whom the Software is
	* furnished to do so, subject to the following conditions:
	*
	* The above copyright notice and this permission notice shall be included in
	* all copies or substantial portions of the Software.
	*
	* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
	* IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
	* FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
	* AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
	* LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
	* OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
	* THE SOFTWARE.
	*
	* Parameters:
	* $config = [];
	* $config['db_host'] = 'MY_DB_HOST';
	* $config['db_user'] = 'MY_DB_USER';
	* $config['db_password'] = 'MY_DB_PASSWORD';
	* $config['db_name'] = 'MY_DB_NAME';
	* $config['db_port'] = 3306;
	* $config['remove_definer'] = true; //removes definer from views, triggers, procedures and functions
	* $config['remove_db_name_from_views'] = true; //views can use the full `db`.`table`.`field` format which is awkward
	* $config['new_insert_after_n'] = 1000; //break insert after this number of records
	* $config['include_triggers'] = true;
	* $config['include_procedures'] = true;
	* $config['include_functions'] = true;
	*
	* Usage:
	* $backupObj = new MysqlBackup($config);
	* $dump = backupDb(); //no param for all tables, or an array of names, or comma separated list.
	* $dump is all the returned sql statements.
	*
	* Class throws exceptions on errors.
	*
	* Interesting things to do: Convert to PDO (I have only needed mysql so far &
	* I run this from a cli script);
	*
	*/

	class MysqlBackup{

	public $errorMessage;
	public $config;
	public $link;

	public function __construct($config = array()){
	if(!is_array($config) \|\| empty($config)){
	$this->errorMessage = 'MysqlBackup - config parameters missing.';
	throw new \Exception($this->errorMessage);
	}
	//defaults
	$config['include_triggers'] = isset($config['include_triggers']) ? $config['include_triggers'] : true;
	$config['include_procedures'] = isset($config['include_procedures']) ? $config['include_procedures'] : true;
	$config['include_functions'] = isset($config['include_functions']) ? $config['include_functions'] : true;
	$config['new_insert_after_n'] = isset($config['new_insert_after_n']) ? $config['new_insert_after_n'] : 1000;
	$config['remove_definer'] = isset($config['remove_definer']) ? $config['remove_definer'] : true;
	$config['remove_db_name_from_views'] = isset($config['remove_db_name_from_views']) ? $config['remove_db_name_from_views'] : true;
	$config['db_port'] = isset($config['db_port']) ? $config['db_port'] : 3306;

	$this->config = $config;

	//generate exceptions for errors
	mysqli_report(MYSQLI_REPORT_ERROR \| MYSQLI_REPORT_STRICT);

	$link = @mysqli_connect($this->config['db_host'], $this->config['db_user'],
	$this->config['db_password'], $this->config['db_name'], $this->config['db_port']);

	if($link === false) {
	$this->errorMessage = "MysqlBackup - failed to open database connection: ".mysqli_connect_error()."\n";
	throw new \Exception($this->errorMessage);
	}

	if (!@mysqli_set_charset($link, "utf8")) {
	$this->errorMessage = sprintf("MysqlBackup - Error loading character set utf8: %s\n", mysqli_error($link));
	throw new \Exception($this->errorMessage);
	}

	$this->link = $link;
	}

	public function __destruct(){
	mysqli_close($this->link);
	}

	/*
	* backup the entire db OR just a table (comma separated list)
	*/
	public function backupDb($tables = '*') {

	$return = '';
	$return_view = '';

	//if wildcard, get all of the tables including views
	if($tables == '*') {
	$tables = array();
	$result = mysqli_query($this->link, 'SHOW TABLES');
	while($row = mysqli_fetch_row($result)) {
	$tables[] = $row[0];
	}

	} else {
	$tables = is_array($tables) ? $tables : explode(',', $tables);
	$tables = array_map('trim', $tables);
	}

	//interogate table fields to check type so data escaping can be done correctly
	$fields = array();

	foreach ($tables as $table) {

	if (!isset($fields[$table])) {
	$fields[$table] = array();
	}

	$result = mysqli_query($this->link, 'SHOW COLUMNS FROM `'.$table.'`');
	$f = 0;

	while ($row = mysqli_fetch_row($result)) {
	//numeric is default
	$fields[$table][$f] = false;

	if (preg_match('/char\|text\|blob\|binary\|enum\|set\|date\|time\|year/i', $row[1])){
	//string
	$fields[$table][$f] = 1;
	}else if(stripos($row[1], 'bit') !== false){
	//bit
	$fields[$table][$f] = 2;
	}
	$f++;
	}
	}

	//get all VIEWS
	$views = [];
	$result = mysqli_query($this->link, "SHOW FULL TABLES WHERE TABLE_TYPE = 'VIEW'");
	while($row = mysqli_fetch_row($result)) {
	$views[] = $row[0];
	}

	$return .= "# ************************************************************\n";
	$return .= "# Database: {$this->config['db_name']}\n";
	$return .= "# Generation Time: ". date('Y-m-d H:i:s')."\n";
	$return .= "# ************************************************************\n\n";

	//temp disable foreign key checks
	$return .= "SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;\n";
	$return .= "SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;\n";
	$return .= "SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;\n";
	$return .= "SET NAMES utf8;\n";
	$return .= "SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;\n";
	$return .= "SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';\n";
	$return .= "SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;\n\n";

	//cycle through tables creating schema then data dump
	foreach($tables as $table) {

	if(in_array($table, $views)){
	//VIEWS

	//This uses the same approach as mysqldump. A temporary table with the same column names
	//is created first as a placemarker. After all other tables have been created each view
	//is created properly after deleting the temporary table. This handles the case of
	//views referring to other views having to be defined in the exact correct order as
	//there is always a dummy temporary table of the same name present to stand in.

	$return .= "# Dump of view $table - CREATE TEMPORARY TABLE \n";
	$return .= "# ------------------------------------------------------------\n";

	$return .= "DROP TABLE IF EXISTS `".$table."`;\n";
	$return .= "DROP VIEW IF EXISTS `".$table."`;\n";

	//get the resulting columns of the created view and make a temporary table
	$return .= "CREATE TABLE `".$table."` (";

	$result = mysqli_query($this->link, "SHOW COLUMNS FROM `" . $table ."`");

	while($row3 = mysqli_fetch_row($result)){
	$return .= "\n`".$row3[0]."` tinyint NOT NULL,";
	}
	$return = substr($return, 0, -1); //remove comma
	$return .= ") ENGINE=MyISAM;\n\n";

	//Get the proper view definition
	$result = mysqli_query($this->link, 'SHOW CREATE TABLE `' . $table .'`');
	$row2 = mysqli_fetch_row($result);

	$return_view .= "# Dump of view $table\n";
	$return_view .= "# ------------------------------------------------------------\n";
	$return_view .= "DROP TABLE IF EXISTS `".$table."`;\n";

	//a view - later versions of mysql need an explicit drop view
	$code = $row2[1];
	//optionally remove db name from `db`.`table`.`field` format
	if($this->config['remove_db_name_from_views']){
	$code = preg_replace('/(\s+\|,\|\()`'.$this->config['db_name'].'`\./', '$1', $code);
	}
	//optionally remove definer
	if($this->config['remove_definer']){
	$return_view .= preg_replace('/^(.?)DEFINER=`.?`@`.?`(.)/','$1$2', $code). ";\n\n";
	}else{
	$return_view .= $code . ";\n\n";
	}

	} else {
	//TABLES
	$return .= "# Dump of table $table\n";
	$return .= "# ------------------------------------------------------------\n";

	$return .= "DROP TABLE IF EXISTS `".$table."`;\n";

	$result = mysqli_query($this->link, 'SHOW CREATE TABLE `' . $table .'`');
	$row2 = mysqli_fetch_row($result);

	$return .= "\n" . $row2[1] . ";\n\n";
	}

	//views don't need the data dump
	if(!in_array($table, $views)) {
	//not a view - do the full table dump

	$result = mysqli_query($this->link,'SELECT * FROM `'.$table.'`');
	$num_fields = mysqli_num_fields($result);

	$return .= 'LOCK TABLES `' . $table . '` WRITE;' . "\n";
	$return .= 'ALTER TABLE `' . $table . '` DISABLE KEYS;' . "\n";

	$i=0;
	while($row = mysqli_fetch_row($result)) {
	if($i++ == 0){
	$return .= 'INSERT INTO `'.$table.'` VALUES(';
	}else {
	$return .= ",\n(";
	}
	for($j = 0; $j < $num_fields; $j++) {

	//special handling for nulls
	if(is_null($row[$j])){
	$return .= 'NULL';
	}else {
	//no need to escape numeric
	if($fields[$table][$j] === false){
	//numeric
	$return .= $row[$j];
	}else if ($fields[$table][$j] === 1){
	//string
	$row[$j] = addslashes($row[$j]); //ok on utf8
	//$row[$j] = mb_addslashes($row[$j],'UTF-8');
	$row[$j] = str_replace(array("\n","\r"),array("\\n","\\r"), $row[$j]);

	$return .= "'".$row[$j]."'";
	}else if ($fields[$table][$j] === 2){
	//bit type
	$return .= sprintf("b'%b'", $row[$j]);
	}
	}
	if ($j < ($num_fields-1)) {
	$return .= ',';
	}
	}

	$return .= ")";

	//force a new insert generated every N rows
	if($i > $this->config['new_insert_after_n']){
	$i = 0;
	$return .= ";\n";
	}
	}

	$return .= ";\n";

	$return .= "ALTER TABLE `". $table . "` ENABLE KEYS;\n";
	$return .= "UNLOCK TABLES;\n\n";
	}

	//DUMP TRIGGERS FOR TABLE
	if($this->config['include_triggers']){
	$triggers = array();
	$result = mysqli_query($this->link, "SHOW TRIGGERS LIKE '$table'");

	while($row = mysqli_fetch_row($result)) {
	$triggers[] = $row[0];
	}

	if(!empty($triggers)){
	$return .= "DELIMITER $$\n\n";

	foreach($triggers as $trigger){

	$return .= "# Dump of TRIGGER $trigger\n";
	$return .= "# ------------------------------------------------------------\n";
	$return .= "DROP TRIGGER IF EXISTS `$trigger`$$\n";

	$result = mysqli_fetch_array ( mysqli_query ($this->link, "SHOW CREATE TRIGGER $trigger"));
	$code = $result[2];
	//remove definer
	$return .= ($this->config['remove_definer']) ? preg_replace('/^(.?)DEFINER=`.?`@`.?`(.)/','$1$2',$code) : $code;
	$return .= "$$\n";
	}
	$return .= "DELIMITER ;\n\n";
	}
	}
	}

	//DUMP PROCEDURES
	if($this->config['include_procedures']){
	$procedures = array();
	$result = mysqli_query($this->link, "SHOW PROCEDURE STATUS WHERE Db = '{$this->config['db_name']}'");

	while($row = mysqli_fetch_row($result)) {
	$procedures[] = $row[1];
	}

	if(!empty($procedures)){
	$return .= "DELIMITER $$\n\n";
	foreach($procedures as $procedure){

	$return .= "# Dump of PROCEDURE $procedure\n";
	$return .= "# ------------------------------------------------------------\n";
	$return .= "DROP PROCEDURE IF EXISTS `$procedure`$$\n";
	$result = mysqli_fetch_array ( mysqli_query ($this->link, "SHOW CREATE PROCEDURE $procedure"));
	$code = $result[2];
	//remove definer
	$return .= ($this->config['remove_definer']) ? preg_replace('/^(.?)DEFINER=`.?`@`.?`(.)/','$1$2', $code) : $code;
	$return .= "$$\n";
	}
	$return .= "DELIMITER ;\n\n";
	}
	}
	//DUMP FUNCTIONS.
	if($this->config['include_functions']){
	$functions = array();
	$result = mysqli_query($this->link, "SHOW FUNCTION STATUS WHERE Db = '{$this->config['db_name']}'");

	while($row = mysqli_fetch_row($result)) {
	$functions[] = $row[1];
	}

	if(!empty($functions)){
	$return .= "DELIMITER $$\n\n";
	foreach($functions as $function){

	$return .= "# Dump of FUNCTION $function\n";
	$return .= "# ------------------------------------------------------------\n";
	$return .= "DROP FUNCTION IF EXISTS `$function`$$\n";
	$result = mysqli_fetch_array ( mysqli_query ($this->link, "SHOW CREATE FUNCTION $function"));
	$code = $result[2];
	//remove definer
	$return .= ($this->config['remove_definer']) ? preg_replace('/(.?)DEFINER=`.?`@`.?`\s(.*)/','$1$2', $code) : $code;
	$return .= "$$\n";
	}
	$return .= "DELIMITER ;\n\n";
	}
	}

	//add views in last so all tables have been created first
	$return .= $return_view;

	//restore foreign key checks
	$return .= "SET SQL_NOTES=@OLD_SQL_NOTES;\n";
	$return .= "SET SQL_MODE=@OLD_SQL_MODE;\n";
	$return .= "SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;\n";
	$return .= "SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;\n";
	$return .= "SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;\n";
	$return .= "SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;\n";

	return $return;

	}

	//standard addslashes is ok on UTF8 - this mb version is slow, so don't use unless necessary
	//https://gist.github.com/yuya-takeyama/402780
	public function mb_addslashes($input, $enc = NULL)
	{
	if (is_null($enc)) {
	$enc = mb_internal_encoding();
	}
	$len = mb_strlen($input, $enc);
	$result = '';
	for ($i = 0; $i < $len; $i++)
	{
	$char = mb_substr($input, $i, 1, $enc);
	if (strlen($char) === 1) {
	$char = addslashes($char);
	}
	$result .= $char;
	}
	return $result;
	}

	} //eoc

	/* end */