mysqldump

package module
v0.9.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: May 7, 2024 License: MIT Imports: 10 Imported by: 0

README


mysqldump

A zero-dependency,all data types are supported, high-performance, concurrent mysqldump tool implemented in golang.

Features

  • Supports custom Writer: data can be written to any Writer, such as local files, multiple file storage, remote servers, cloud storage, etc. (default console output).
  • Supports all MySQL data types QuickStart.
  • Support Merge Insert Option in Source to improve data recovery performance
  • Support multi data in one insert
  • Support dump table trigger

QuickStart

Create Table and Insert Test Data

DROP TABLE IF EXISTS `test`;

CREATE TABLE `test` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `char_col` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `varchar_col` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `binary_col` binary(10) DEFAULT NULL,
  `varbinary_col` varbinary(255) DEFAULT NULL,
  `tinyblob_col` tinyblob,
  `tinytext_col` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `text_col` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `blob_col` blob,
  `mediumtext_col` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `mediumblob_col` mediumblob,
  `longtext_col` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `longblob_col` longblob,
  `enum_col` enum('value1','value2','value3') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `set_col` set('value1','value2','value3') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `bit_col` bit(8) DEFAULT NULL,
  `tinyint_col` tinyint NOT NULL DEFAULT '0',
  `bool_col` tinyint(1) NOT NULL DEFAULT '0',
  `boolean_col` tinyint(1) NOT NULL DEFAULT '0',
  `smallint_col` smallint NOT NULL DEFAULT '0',
  `mediumint_col` mediumint NOT NULL DEFAULT '0',
  `int_col` int NOT NULL DEFAULT '0',
  `integer_col` int NOT NULL DEFAULT '0',
  `bigint_col` bigint NOT NULL DEFAULT '0',
  `float_col` float(8,2) NOT NULL DEFAULT '0.00',
  `double_col` double(8,2) NOT NULL DEFAULT '0.00',
  `decimal_col` decimal(10,2) NOT NULL DEFAULT '0.00',
  `dec_col` decimal(10,2) NOT NULL DEFAULT '0.00',
  `date_col` date DEFAULT NULL,
  `datetime_col` datetime DEFAULT NULL,
  `timestamp_col` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `time_col` time DEFAULT NULL,
  `year_col` year DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `test` VALUES (1,'abc','def',0x61626300000000000000,0x646566,0x74696E79626C6F62,'Hello','World',0x776F726C64,'Medium Text',0x4D656469756D426C6F62,'Long Text',0x4C6F6E67426C6F62,'value2','value1,value3',0x66,-128,1,0,-32768,-8388608,-2147483648,-2147483648,-9223372036854775808,1234.56,1234.56,1234.56,1234.56,'2023-03-17','2023-03-17 10:00:00','2023-03-17 14:04:46','10:00:00',2023);

Dump SQL

import (
	"os"

	"github.com/MGSousa/mysqldump"
)

func main() {

	dsn := "root:rootpasswd@tcp(localhost:3306)/dbname?charset=utf8mb4&parseTime=true&loc=Asia%2FShanghai"

	f, _ := os.Create("dump.sql")

	_ = mysqldump.Dump(
		dsn,                          // DSN
		mysqldump.WithDropTable(),    // Option: Delete table before create (Default: Not delete table)
		mysqldump.WithData(),         // Option: Dump Data (Default: Only dump table schema)
		mysqldump.WithTables("test"), // Option: Dump Tables (Default: All tables)
		mysqldump.WithWriter(f),      // Option: Writer (Default: os.Stdout)
	)
}

Output File dump.sql

-- ----------------------------
-- MySQL Database Dump
-- Start Time: 2023-04-21 14:16:56
-- ----------------------------


USE `dc3`;
DROP TABLE IF EXISTS `test`;
-- ----------------------------
-- Table structure for test
-- ----------------------------
CREATE TABLE IF NOT EXISTS `test` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `char_col` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `varchar_col` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `binary_col` binary(10) DEFAULT NULL,
  `varbinary_col` varbinary(255) DEFAULT NULL,
  `tinyblob_col` tinyblob,
  `tinytext_col` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `text_col` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `blob_col` blob,
  `mediumtext_col` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `mediumblob_col` mediumblob,
  `longtext_col` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `longblob_col` longblob,
  `enum_col` enum('value1','value2','value3') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `set_col` set('value1','value2','value3') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `bit_col` bit(8) DEFAULT NULL,
  `tinyint_col` tinyint NOT NULL DEFAULT '0',
  `bool_col` tinyint(1) NOT NULL DEFAULT '0',
  `boolean_col` tinyint(1) NOT NULL DEFAULT '0',
  `smallint_col` smallint NOT NULL DEFAULT '0',
  `mediumint_col` mediumint NOT NULL DEFAULT '0',
  `int_col` int NOT NULL DEFAULT '0',
  `integer_col` int NOT NULL DEFAULT '0',
  `bigint_col` bigint NOT NULL DEFAULT '0',
  `float_col` float(8,2) NOT NULL DEFAULT '0.00',
  `double_col` double(8,2) NOT NULL DEFAULT '0.00',
  `decimal_col` decimal(10,2) NOT NULL DEFAULT '0.00',
  `dec_col` decimal(10,2) NOT NULL DEFAULT '0.00',
  `date_col` date DEFAULT NULL,
  `datetime_col` datetime DEFAULT NULL,
  `timestamp_col` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `time_col` time DEFAULT NULL,
  `year_col` year DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test` VALUES (1,'abc','def',0x61626300000000000000,0x646566,0x74696E79626C6F62,'Hello','World',0x776F726C64,'Medium Text',0x4D656469756D426C6F62,'Long Text',0x4C6F6E67426C6F62,'value2','value1,value3',0x66,-128,1,0,-32768,-8388608,-2147483648,-2147483648,-9223372036854775808,1234.56,1234.56,1234.56,1234.56,'2023-03-17','2023-03-17 10:00:00','2023-03-17 14:04:46','10:00:00',2023);


-- ----------------------------
-- Dumped by mysqldump2
-- Cost Time: 5.81592ms
-- ----------------------------

Source SQL

import (
	"os"

	"github.com/MGSousa/mysqldump"
)

func main() {

	dsn := "root:rootpasswd@tcp(localhost:3306)/dbname?charset=utf8mb4&parseTime=true&loc=Asia%2FShanghai"
	f, _ := os.Open("dump.sql")

	_ = mysqldump.Source(
		dsn,
		f,
		mysqldump.WithMergeInsert(1000), // Option: Merge insert 1000 (Default: Not merge insert)
		mysqldump.WithDebug(),           // Option: Print execute sql (Default: Not print execute sql)
	)
}

Documentation

Index

Constants

View Source 
const DEFAULT_LOG_TIMESTAMP = "2006-01-02 15:04:05"

Variables

This section is empty.

Functions

func Dump 

func Dump(dsn string, opts ...DumpOption) error

func GetDBHostFromDSN 

func GetDBHostFromDSN(dsn string) (string, error)

GetDBHostFromDSN get Hostname from DSN

func GetDBNameFromDSN 

func GetDBNameFromDSN(dsn string) (string, error)

GetDBNameFromDSN get Database name from DSN

func Source 

func Source(dsn string, reader io.Reader, opts ...SourceOption) error

Source 加载 禁止 golangci-lint 检查 nolint: gocyclo

Types

type DumpOption 

type DumpOption func(*dumpOption)

func WithAllDatabases added in v0.9.0 

func WithAllDatabases() DumpOption

WithAllDatabases Export all databases

func WithAllTables 

func WithAllTables() DumpOption

WithAllTables Export all tables

func WithDBs added in v0.9.0 

func WithDBs(databases ...string) DumpOption

WithDBs Export the specified database, mutually exclusive with WithAllDatabases WithAllDatabases has higher priority

func WithData 

func WithData() DumpOption

WithData Export table data

func WithDropTable 

func WithDropTable() DumpOption

WithDropTable Delete table

func WithLogOut added in v0.9.0 

func WithLogOut(logOut bool) DumpOption

Whether to output logs

func WithMultiInsert added in v0.9.0 

func WithMultiInsert(num int) DumpOption

WithMultiInsert Export multi-inserts in one command

func WithTables 

func WithTables(tables ...string) DumpOption

WithTables Export specific tables

func WithUseDb added in v0.9.0 

func WithUseDb() DumpOption

WithUseDb Whether to add a specified library statement. If there are multiple libraries, this setting is invalid.

func WithWriter 

func WithWriter(writer io.Writer) DumpOption

WithWriter Export to specified writer (file, stdOut, etc.)

type SourceOption 

type SourceOption func(*sourceOption)

func WithDebug 

func WithDebug() SourceOption

WithDebug 打印执行的 SQL

func WithDryRun 

func WithDryRun() SourceOption

func WithMergeInsert 

func WithMergeInsert(size int) SourceOption

Source Files

View all Source files

Directories

Path Synopsis
example
dump
source

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.