Mysql Dump

Create a backup of a MySQL database.

A fork of mysqldump. Added features:

• Dump stored procedure and functions.
• File compression using 7zip.
• Ability to set password for compressed file.

Installation

$ npm install @yanisalfian/mysqldump

If you're using this package in typescript, you should also

$ npm install @types/node

Usage

import mysqldump from 'mysqldump';
// or const mysqldump = require('mysqldump')

// dump the result straight to a file
mysqldump({
    connection: {
        host: 'localhost',
        user: 'root',
        password: '123456',
        database: 'my_database',
    },
    dumpToFile: './dump.sql',
});

// dump the result straight to a compressed file
mysqldump({
    connection: {
        host: 'localhost',
        user: 'root',
        password: '123456',
        database: 'my_database',
    },
    dumpToFile: './dump.sql',
    compressFile: true, //output file will be ./dump.sql.7z
    compressFilePassword: 'your_password', //optional
});

// return the dump from the function and not to a file
const result = await mysqldump({
    connection: {
        host: 'localhost',
        user: 'root',
        password: '123456',
        database: 'my_database',
    },
});

Result

The returned result contains the dump property, which is split into schema and data.

export default interface DumpReturn {
    /**
     * The result of the dump
     */
    dump : {
        /**
         * The concatenated SQL schema dump for the entire database.
         * Null if configured not to dump.
         */
        schema : string | null
        /**
         * The concatenated SQL data dump for the entire database.
         * Null if configured not to dump.
         */
        data : string | null
        /**
         * The concatenated SQL trigger dump for the entire database.
         * Null if configured not to dump.
         */
        trigger : string | null
    }
    tables : Table[]
}

Options

All the below options are documented in the typescript declaration file:

/// <reference types="node" />

export interface ConnectionOptions {
	/**
	 * The database host to connect to.
	 * Defaults to 'localhost'.
	 */
	host?: string;
	/**
	 * The port on the host to connect to.
	 * Defaults to 3306.
	 */
	port?: number;
	/**
	 * The database to dump.
	 */
	database: string;
	/**
	 * The DB username to use to connect.
	 */
	user: string;
	/**
	 * The password to use to connect.
	 */
	password: string;
	/**
	 * The charset to use for the connection.
	 * Defaults to 'UTF8_GENERAL_CI'.
	 */
	charset?: string;
	/**
	 * SSL configuration options.
	 * Passing 'Amazon RDS' will use Amazon's RDS CA certificate.
	 *
	 * Otherwise you can pass the options which get passed to tls.createSecureContext.
	 * See: https://nodejs.org/api/tls.html#tls_tls_createsecurecontext_options
	 */
	ssl?: 'Amazon RDS' | null | {
		/**
		 * Optionally override the trusted CA certificates. Default is to trust the well-known CAs curated by Mozilla.
		 */
		ca?: string | Buffer;
		/**
		 * Optional cert chains in PEM format.
		 */
		cert?: string | Buffer;
		/**
		 * Optional cipher suite specification, replacing the default.
		 */
		ciphers?: string;
		/**
		 * Optional PEM formatted CRLs (Certificate Revocation Lists).
		 */
		crl?: string | Array<string>;
		/**
		 * Attempt to use the server's cipher suite preferences instead of the client's.
		 */
		honorCipherOrder?: boolean;
		/**
		 * Optional private keys in PEM format.
		 */
		key?: string | Buffer;
		/**
		 * Optional shared passphrase used for a single private key and/or a PFX.
		 */
		passphrase?: string;
		/**
		 * Optional PFX or PKCS12 encoded private key and certificate chain.
		 */
		pfx?: string | Buffer;
		/**
		 * DO NOT USE THIS OPTION UNLESS YOU REALLY KNOW WHAT YOU ARE DOING!!!
		 * Set to false to allow connection to a MySQL server without properly providing the appropraite CA to trust.
		 */
		rejectUnauthorized?: boolean;
	};
}
export interface SchemaDumpOptions {
	/**
	 * True to include autoincrement values in schema, false otherwise.
	 * Defaults to true.
	 */
	autoIncrement?: boolean;
	/**
	 * True to include engine values in schema, false otherwise.
	 * Defaults to true.
	 */
	engine?: boolean;
	/**
	 * True to run a sql formatter over the output, false otherwise.
	 * Defaults to true.
	 */
	format?: boolean;
	/**
	 * Options for table dumps
	 */
	table?: {
		/**
		 * Guard create table calls with an "IF NOT EXIST"
		 * Defaults to true.
		 */
		ifNotExist?: boolean;
		/**
		 * Drop tables before creation (overrides `ifNotExist`).
		 * Defaults to false.
		 */
		dropIfExist?: boolean;
		/**
		 * Include the `DEFAULT CHARSET = x` at the end of the table definition
		 * Set to true to include the value form the DB.
		 * Set to false to exclude it altogether.
		 * Set to a string to explicitly set the charset.
		 * Defaults to true.
		 */
		charset?: boolean | string;
	};
	view?: {
		/**
		 * Uses `CREATE OR REPLACE` to define views.
		 * Defaults to true.
		 */
		createOrReplace?: boolean;
		/**
		 * Include the `DEFINER = {\`user\`@\`host\` | CURRENT_USER}` in the view definition or not
		 * Defaults to false.
		 */
		definer?: boolean;
		/**
		 * Include the `ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}` in the view definition or not
		 * Defaults to false.
		 */
		algorithm?: boolean;
		/**
		 * Incldue the `SQL SECURITY {DEFINER | INVOKER}` in the view definition or not
		 * Defaults to false.
		 */
		sqlSecurity?: boolean;
	};
}
export interface TriggerDumpOptions {
	/**
	 * The temporary delimiter to use between statements.
	 * Set to false to not use delmiters
	 * Defaults to ';;'.
	 */
	delimiter?: string | false;
	/**
	 * Drop triggers before creation.
	 * Defaults to false.
	 */
	dropIfExist?: boolean;
	/**
	 * Include the `DEFINER = {\`user\`@\`host\` | CURRENT_USER}` in the view definition or not
	 * Defaults to false.
	 */
	definer?: boolean;
}
export interface ProcedureDumpOptions {
	/**
	 * The temporary delimiter to use between statements.
	 * Set to false to not use delmiters
	 * Defaults to ';;'.
	 */
	delimiter?: string | false;
	/**
	 * Drop triggers before creation.
	 * Defaults to false.
	 */
	dropIfExist?: boolean;
	/**
	 * Include the `DEFINER = {\`user\`@\`host\` | CURRENT_USER}` in the view definition or not
	 * Defaults to false.
	 */
	definer?: boolean;
}
export interface FunctionDumpOptions {
	/**
	 * The temporary delimiter to use between statements.
	 * Set to false to not use delmiters
	 * Defaults to ';;'.
	 */
	delimiter?: string | false;
	/**
	 * Drop triggers before creation.
	 * Defaults to false.
	 */
	dropIfExist?: boolean;
	/**
	 * Include the `DEFINER = {\`user\`@\`host\` | CURRENT_USER}` in the view definition or not
	 * Defaults to false.
	 */
	definer?: boolean;
}
export interface DataDumpOptions {
	/**
	 * True to run a sql formatter over the output, false otherwise.
	 * Defaults to true.
	 */
	format?: boolean;
	/**
	 * Include file headers in output
	 * Defaults to true.
	 */
	verbose?: boolean;
	/**
	 * Use a read lock during the data dump (see: https://dev.mysql.com/doc/refman/5.7/en/replication-solutions-backups-read-only.html)
	 * Defaults to false.
	 */
	lockTables?: boolean;
	/**
	 * Dump data from views.
	 * Defaults to false.
	 */
	includeViewData?: boolean;
	/**
	 * Maximum number of rows to include in each multi-line insert statement
	 * Defaults to 1 (i.e. new statement per row).
	 */
	maxRowsPerInsertStatement?: number;
	/**
	 * True to return the data in a function, false to not.
	 * This is useful in databases with a lot of data.
	 *
	 * We stream data from the DB to reduce the memory footprint.
	 * However note that if you want the result returned from the function,
	 * this will result in a larger memory footprint as the string has to be stored in memory.
	 *
	 * Defaults to false if dumpToFile is truthy, or true if not dumpToFile is falsey.
	 */
	returnFromFunction?: boolean;
	/**
	 * A map of tables to additional where strings to add.
	 * Use this to limit the number of data that is dumped.
	 * Defaults to no limits
	 */
	where?: {
		[k: string]: string;
	};
}
export interface DumpOptions {
	/**
	 * The list of tables that you want to dump.
	 * Defaults to all tables (signalled by passing an empty array).
	 */
	tables?: Array<string>;
	/**
	 * True to use the `tables` options as a blacklist, false to use it as a whitelist.
	 * Defaults to false.
	 */
	excludeTables?: boolean;
	/**
	 * Explicitly set to false to not include the schema in the dump.
	 * Defaults to including the schema.
	 */
	schema?: false | SchemaDumpOptions;
	/**
	 * Explicitly set to false to not include data in the dump.
	 * Defaults to including the data.
	 */
	data?: false | DataDumpOptions;
	/**
	 * Explicitly set to false to not include triggers in the dump.
	 * Defaults to including the triggers.
	 */
	trigger?: false | TriggerDumpOptions;
	/**
	 * Explicitly set to false to not include procedure in the dump.
	 * Defaults to including the procedure.
	 */
	procedure?: false | ProcedureDumpOptions;
	/**
	 * Explicitly set to false to not include procedure in the dump.
	 * Defaults to including the procedure.
	 */
	function?: false | FunctionDumpOptions;
}
export interface Options {
	/**
	 * Database connection options
	 */
	connection: ConnectionOptions;
	/**
	 * Dump configuration options
	 */
	dump?: DumpOptions;
	/**
	 * Set to a path to dump to a file.
	 * Exclude to just return the string.
	 */
	dumpToFile?: string | null;
	/**
	 * Should the output file be compressed (7z)?
	 * Defaults to false.
	 */
	compressFile?: boolean;
	/**
	 * Set the password of compressed file
	 * Defaults to null.
	 */
	compressFilePassword?: string | null;
}
export interface ColumnList {
	/**
	 * Key is the name of the column
	 */
	[k: string]: {
		/**
		 * The type of the column as reported by the underlying DB.
		 */
		type: string;
		/**
		 * True if the column is nullable, false otherwise.
		 */
		nullable: boolean;
	};
}
export interface Table {
	/**
	 * The name of the table.
	 */
	name: string;
	/**
	 * The raw SQL schema dump for the table.
	 * Null if configured to not dump.
	 */
	schema: string | null;
	/**
	 * The raw SQL data dump for the table.
	 * Null if configured to not dump.
	 */
	data: string | null;
	/**
	 * The list of column definitions for the table.
	 */
	columns: ColumnList;
	/**
	 * An ordered list of columns (for consistently outputing as per the DB definition)
	 */
	columnsOrdered: Array<string>;
	/**
	 * True if the table is actually a view, false otherwise.
	 */
	isView: boolean;
	/**
	 * A list of triggers attached to the table
	 */
	triggers: Array<string>;
}
export interface DumpReturn {
	/**
	 * The result of the dump
	 */
	dump: {
		/**
		 * The concatenated SQL schema dump for the entire database.
		 * Null if configured not to dump.
		 */
		schema: string | null;
		/**
		 * The concatenated SQL data dump for the entire database.
		 * Null if configured not to dump.
		 */
		data: string | null;
		/**
		 * The concatenated SQL trigger dump for the entire database.
		 * Null if configured not to dump.
		 */
		trigger: string | null;
		/**
		 * The concatenated SQL procedure dump for the entire database.
		 * Null if configured not to dump.
		 */
		procedure: string | null;
		/**
		 * The concatenated SQL function dump for the entire database.
		 * Null if configured not to dump.
		 */
		function: string | null;
	};
	tables: Array<Table>;
}
export default function main(inputOptions: Options): Promise<DumpReturn>;

export as namespace mysqldump;

export {};

The MIT License

Dumping procedure and function

Make sure that the user has privilege SHOW_ROUTINE to make it work.

Contributing

Local Installation

Make sure to first install all the required development dependencies:

yarn
// or
npm install .

Linting

We use eslint in conjunction with typescript-eslint-parser for code linting.

PRs are required to pass the linting with no errors and preferrably no warnings.

Testing

Tests can be run via the test script - yarn test / npm test.

Additionally it's required that you do a build and run your test against the public package to ensure the build doesn't cause regressions - yarn run test-prod / npm run test-prod.

PRs are required to maintain the 100% test coverage, and all tests must pass successfully.