How to create a backup copy of not only data and tables, but also functions and procedures?

[GMELUM]

  mysql-master-backup:
    image: databack/mysql-backup
    container_name: mysql-backup
    restart: always
    environment:
      SINGLE_DATABASE: true
      DB_SERVER: mysql
      DB_PORT: 3306
      DB_USER: root
      DB_PASS: $MYSQL_PASSWORD
      DB_NAMES: $MYSQL_DATABASE
      DB_DUMP_FREQ: $INTERVAL
      DB_DUMP_TARGET:  "s3://$AWS_BUCKET/dumps/$MYSQL_DATABASE"
      AWS_ACCESS_KEY_ID: $AWS_ACCESS_KEY_ID
      AWS_SECRET_ACCESS_KEY: $AWS_SECRET_ACCESS_KEY
      AWS_REGION: $AWS_REGION
      AWS_ENDPOINT_URL: $AWS_ENDPOINT
      COMPRESSION: bzip2
    command: dump

[deitch]

Honestly, had not thought about it until now, mainly because no one asked. But it is a good thing to add. The legacy version did mysqldump without --routines. The more modern version would need the right templates added, but that shouldn't be too radically difficult.

Are you interesting in trying your hand at a PR?

[shaunakv1]

@deitch Just want to add a +1 for this feature. Our backups need functions too.

[shaunakv1]

@deitch Just trying to see if I can help move this forward. I am not a go dev so a bit uncertain doing a full pull request, but with some help from Github Copilot and ChatGPT here's a new version of dump.go file . It includes changes to dump triggers, procedures and functions.

Does this make sense to you?

pkg/database/mysql/dump.go

package mysql

import (
	"bytes"
	"context"
	"database/sql"
	"errors"
	"fmt"
	"io"
	"text/template"
	"time"
)

// Data struct to configure dump behavior
type Data struct {
	Out                 io.Writer
	Connection          *sql.DB
	IgnoreTables        []string
	MaxAllowedPacket    int
	LockTables          bool
	Schema              string
	Compact             bool
	Host                string
	SuppressUseDatabase bool
	Charset             string
	Collation           string

	tx         *sql.Tx
	headerTmpl *template.Template
	footerTmpl *template.Template
	err        error
}

type metaData struct {
	DumpVersion   string
	ServerVersion string
	CompleteTime  string
	Host          string
	Database      string
	Charset       string
	Collation     string
}

const (
	Version                 = "0.6.0"
	defaultMaxAllowedPacket = 4194304
)

const headerTmpl = `-- Go SQL Dump {{ .DumpVersion }}
--
-- Host: {{.Host}}    Database: {{.Database}}
-- ------------------------------------------------------
-- Server version	{{ .ServerVersion }}

`

const footerTmpl = `-- Dump completed on {{ .CompleteTime }}`

const footerTmplCompact = ``

func (data *Data) Dump() error {
	meta := metaData{
		DumpVersion: Version,
		Host:        data.Host,
		Database:    data.Schema,
	}

	if data.MaxAllowedPacket == 0 {
		data.MaxAllowedPacket = defaultMaxAllowedPacket
	}

	if err := data.getTemplates(); err != nil {
		return err
	}

	if err := data.selectSchema(); err != nil {
		return err
	}

	if err := data.begin(); err != nil {
		return err
	}
	defer func() {
		_ = data.rollback()
	}()

	if err := data.getCharsetCollections(); err != nil {
		return err
	}

	if err := meta.updateMetadata(data); err != nil {
		return err
	}

	if err := data.headerTmpl.Execute(data.Out, meta); err != nil {
		return err
	}

	tables, err := data.getTables()
	if err != nil {
		return err
	}

	// Lock all tables before dumping if present
	if data.LockTables && len(tables) > 0 {
		var b bytes.Buffer
		b.WriteString("LOCK TABLES ")
		for index, table := range tables {
			if index != 0 {
				b.WriteString(",")
			}
			b.WriteString("`" + table.Name() + "` READ /*!32311 LOCAL */")
		}

		if _, err := data.Connection.Exec(b.String()); err != nil {
			return err
		}

		defer func() {
			_, _ = data.Connection.Exec("UNLOCK TABLES")
		}()
	}

	for _, name := range tables {
		if err := data.dumpTable(name); err != nil {
			return err
		}
	}

	// Dump triggers
	if err := data.dumpTriggers(); err != nil {
		return err
	}

	// Dump procedures and functions
	if err := data.dumpProceduresAndFunctions(); err != nil {
		return err
	}

	if data.err != nil {
		return data.err
	}

	meta.CompleteTime = time.Now().UTC().Format("2006-01-02 15:04:05")
	return data.footerTmpl.Execute(data.Out, meta)
}

// New function to dump triggers
func (data *Data) dumpTriggers() error {
	rows, err := data.tx.Query("SHOW TRIGGERS FROM `" + data.Schema + "`")
	if err != nil {
		return err
	}
	defer rows.Close()

	for rows.Next() {
		var triggerName, event, timing, statement, sqlMode, definer, table, charset sql.NullString
		var created sql.NullTime
		if err := rows.Scan(&triggerName, &event, &timing, &statement, &sqlMode, &definer, &table, &created, &charset); err != nil {
			return err
		}
		if !triggerName.Valid || !statement.Valid {
			continue
		}
		_, err := data.Out.Write([]byte(fmt.Sprintf("CREATE TRIGGER `%s` %s %s ON `%s` FOR EACH ROW %s;\n",
			triggerName.String, timing.String, event.String, table.String, statement.String)))
		if err != nil {
			return err
		}
	}
	return nil
}

// New function to dump procedures and functions
func (data *Data) dumpProceduresAndFunctions() error {
	queries := []string{
		"SHOW PROCEDURE STATUS WHERE Db = '" + data.Schema + "'",
		"SHOW FUNCTION STATUS WHERE Db = '" + data.Schema + "'",
	}
	for _, query := range queries {
		rows, err := data.tx.Query(query)
		if err != nil {
			return err
		}
		defer rows.Close()

		for rows.Next() {
			var name, typeDef, otherFields sql.NullString
			if err := rows.Scan(&name, &typeDef, &otherFields); err != nil {
				return err
			}
			if name.Valid && typeDef.Valid {
				createQuery := fmt.Sprintf("SHOW CREATE %s `%s`", typeDef.String, name.String)
				var createStmt sql.NullString
				if err := data.tx.QueryRow(createQuery).Scan(&createStmt); err != nil {
					return err
				}
				if createStmt.Valid {
					_, err := data.Out.Write([]byte(createStmt.String + "\n"))
					if err != nil {
						return err
					}
				}
			}
		}
	}
	return nil
}

// The rest of the original helper functions (getTemplates, getTables, etc.) are preserved as in the original file.

[deitch]

See #434