josephbolus · August 24, 2025 02:28
diff --git a/backup_mysqlsh_simple.sh b/backup_mysqlsh_simple.sh
 #!/bin/bash

 # Simple MySQL Shell Backup Script - Direct equivalent to mysqldump version
 # Usage: ./backup_mysqlsh_simple.sh [password]

 BACKUP_DIR="/path/to/backup"  # Change as needed
 mkdir -p "$BACKUP_DIR" || exit 1
 DATE=$(date +%Y%m%d_%H%M%S)
 BACKUP_SUBDIR="${BACKUP_DIR}/backup_${DATE}"

 # Get non-system databases as JavaScript array
 DB_ARRAY=$(mysql -u root -p$1 -NBe "
    SELECT CONCAT('[\"', GROUP_CONCAT(schema_name SEPARATOR '\",\"'), '\"]')
    FROM information_schema.schemata 
    WHERE schema_name NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');" 2>/dev/null) || { 
    echo "Failed to list databases"; 
    exit 1; 
 }

 echo "Backing up databases to: $BACKUP_SUBDIR"

 # Execute MySQL Shell dump
 mysqlsh --user=root --password=$1 --no-wizard -e "
    util.dumpSchemas($DB_ARRAY, '$BACKUP_SUBDIR', {
        consistent: true,           // equivalent to --single-transaction
        includeTriggers: true,       // equivalent to --triggers
        includeRoutines: true,       // equivalent to --routines  
        includeEvents: true,         // equivalent to --events
        compression: 'gzip',         // built-in compression
        showProgress: true
    })" || { echo "Backup failed"; exit 1; }

 # Create tarball (MySQL Shell creates a directory, not a single file)
 tar -czf "${BACKUP_SUBDIR}.tar.gz" -C "$BACKUP_DIR" "$(basename "$BACKUP_SUBDIR")"
 rm -rf "$BACKUP_SUBDIR"

 echo "Backup completed: ${BACKUP_SUBDIR}.tar.gz"
 echo "Size: $(du -h ${BACKUP_SUBDIR}.tar.gz | cut -f1)"

 # To restore: 
 # tar -xzf ${BACKUP_SUBDIR}.tar.gz
 # mysqlsh --user=root --password -e "util.loadDump('backup_${DATE}')"
diff --git a/Complete MySQL Export Guide.md b/Complete MySQL Export Guide.md
diff --git a/complete_mysql_backup.sh b/complete_mysql_backup.sh
 #!/bin/bash

 # Usage: ./complete_mysql_backup.sh yourpassword

 BACKUP_DIR="/path/to/backup"  # Change as needed
 mkdir -p "$BACKUP_DIR" || exit 1
 DATE=$(date +%Y%m%d_%H%M%S)
 BACKUP_FILE="${BACKUP_DIR}/complete_backup_${DATE}.sql.gz"

 # Get non-system databases
 DB_LIST=$(mysql -u root -p$1 -NBe "SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');" 2>/dev/null) || { echo "Failed to list databases"; exit 1; }

 # Start the backup file (compress on-the-fly)
 {
  echo "-- Complete MySQL Backup Generated on $(date)"
  echo "-- "
  echo ""

  # Export users and grants
  echo "-- Users and Privileges"
  mysql -u root -p$1 -NBe "SELECT CONCAT('CREATE USER IF NOT EXISTS \'', user, '\'@\'', host, '\' IDENTIFIED WITH \'', plugin, '\' AS \'', authentication_string, '\';') FROM mysql.user WHERE user NOT IN ('mysql.sys', 'mysql.session', 'mysql.infoschema');" 2>/dev/null || { echo "Failed to export users"; exit 1; }
  echo ""

  mysql -u root -p$1 -NBe "SELECT CONCAT('SHOW GRANTS FOR \'', user, '\'@\'', host, '\';') FROM mysql.user WHERE user NOT IN ('mysql.sys', 'mysql.session', 'mysql.infoschema');" | mysql -u root -p$1 -N 2>/dev/null | grep '^GRANT' || { echo "Failed to export grants"; exit 1; }
  echo ""
  echo "FLUSH PRIVILEGES;"
  echo ""

  # Export all non-system databases with objects
  echo "-- All User Databases, Routines, Triggers, and Events"
  mysqldump -u root -p$1 --databases $DB_LIST --triggers --routines --events --single-transaction --master-data=2 2>/dev/null || { echo "Failed to dump databases"; exit 1; }

 } | gzip > "$BACKUP_FILE"

 echo "Backup completed: $BACKUP_FILE"
diff --git a/mysql_backup.sh b/mysql_backup.sh
 #!/bin/bash

 # Complete MySQL Backup Automation Script
 # Save as: mysql_backup.sh

 # Configuration
 BACKUP_DIR="/backup/mysql"
 MYSQL_USER="root"
 MYSQL_PASS="password"
 RETENTION_DAYS=7
 EMAIL="[email protected]"
 LOG_FILE="/var/log/mysql_backup.log"

 # Functions
 log_message() {
    echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" >> "$LOG_FILE"
 }

 send_notification() {
    echo "$1" | mail -s "MySQL Backup Notification" "$EMAIL"
 }

 # Main backup process
 main() {
    log_message "Starting MySQL backup"
    
    # Create backup
    BACKUP_FILE="${BACKUP_DIR}/mysql_$(date +%Y%m%d_%H%M%S).sql"
    
    if mysqldump -u $MYSQL_USER -p$MYSQL_PASS \
        --all-databases \
        --routines \
        --triggers \
        --events \
        --single-transaction \
        > "$BACKUP_FILE" 2>> "$LOG_FILE"; then
        
        # Compress backup
        gzip "$BACKUP_FILE"
        log_message "Backup completed: ${BACKUP_FILE}.gz"
        
        # Remove old backups
        find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
        log_message "Old backups cleaned up"
        
        send_notification "MySQL backup completed successfully"
    else
        log_message "Backup failed!"
        send_notification "MySQL backup FAILED - check logs"
        exit 1
    fi
 }

 # Run main function
 main
	#!/bin/bash

	# Simple MySQL Shell Backup Script - Direct equivalent to mysqldump version
	# Usage: ./backup_mysqlsh_simple.sh [password]

	BACKUP_DIR="/path/to/backup" # Change as needed
	mkdir -p "$BACKUP_DIR" \|\| exit 1
	DATE=$(date +%Y%m%d_%H%M%S)
	BACKUP_SUBDIR="${BACKUP_DIR}/backup_${DATE}"

	# Get non-system databases as JavaScript array
	DB_ARRAY=$(mysql -u root -p$1 -NBe "
	SELECT CONCAT('[\"', GROUP_CONCAT(schema_name SEPARATOR '\",\"'), '\"]')
	FROM information_schema.schemata
	WHERE schema_name NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');" 2>/dev/null) \|\| {
	echo "Failed to list databases";
	exit 1;
	}

	echo "Backing up databases to: $BACKUP_SUBDIR"

	# Execute MySQL Shell dump
	mysqlsh --user=root --password=$1 --no-wizard -e "
	util.dumpSchemas($DB_ARRAY, '$BACKUP_SUBDIR', {
	consistent: true, // equivalent to --single-transaction
	includeTriggers: true, // equivalent to --triggers
	includeRoutines: true, // equivalent to --routines
	includeEvents: true, // equivalent to --events
	compression: 'gzip', // built-in compression
	showProgress: true
	})" \|\| { echo "Backup failed"; exit 1; }

	# Create tarball (MySQL Shell creates a directory, not a single file)
	tar -czf "${BACKUP_SUBDIR}.tar.gz" -C "$BACKUP_DIR" "$(basename "$BACKUP_SUBDIR")"
	rm -rf "$BACKUP_SUBDIR"

	echo "Backup completed: ${BACKUP_SUBDIR}.tar.gz"
	echo "Size: $(du -h ${BACKUP_SUBDIR}.tar.gz \| cut -f1)"

	# To restore:
	# tar -xzf ${BACKUP_SUBDIR}.tar.gz
	# mysqlsh --user=root --password -e "util.loadDump('backup_${DATE}')"
Option	Purpose	Required?
`--all-databases`	Exports all databases including `mysql` (users/passwords)	Yes
`--routines`	Includes stored procedures and functions	Yes
`--triggers`	Includes all triggers	Yes
`--events`	Includes scheduled events	Yes
`--single-transaction`	Ensures consistent backup for InnoDB tables	Recommended
`--master-data=2`	Adds binary log position as comment for replication/recovery	Recommended
Feature/Option	mysqldump	MySQL Shell
Output Format	Single SQL file	Directory with multiple files
Consistent Backup	`--single-transaction`	`consistent: true`
Include Triggers	`--triggers`	`includeTriggers: true`
Include Routines	`--routines`	`includeRoutines: true`
Include Events	`--events`	`includeEvents: true`
Binary Log Position	`--master-data=2`	Automatically captured in metadata
Compression	Pipe to gzip (`\| gzip`)	`compression: 'gzip'` (built-in)
Data Format	Text-based SQL statements	Binary/JSON format
Restore Speed	Slower (SQL parsing)	Faster (optimized binary)
Parallel Processing	Single-threaded	Multi-threaded (`threads: N`)
Progress Indication	No built-in progress	`showProgress: true`
Chunking Large Tables	Not available	Automatic chunking
Include Users	`--all-databases` includes mysql DB	`includeUsers: true` (with dumpInstance)
Exclude Schemas	`--ignore-database=name`	`excludeSchemas: ['name1', 'name2']`
Character Set	`--default-character-set`	`defaultCharacterSet: 'utf8mb4'`
File Size	Larger (SQL text)	Smaller (binary + better compression)
Restore Command	`mysql -u root -p < dump.sql`	`util.loadDump('directory')`
Version Compatibility	Manual handling	`compatibility: ['strip_restricted_grants']`
Memory Usage	`--quick` for low memory	Automatic optimization
Lock Tables	`--lock-tables=false`	Handled automatically
	#!/bin/bash

	# Usage: ./complete_mysql_backup.sh yourpassword

	BACKUP_DIR="/path/to/backup" # Change as needed
	mkdir -p "$BACKUP_DIR" \|\| exit 1
	DATE=$(date +%Y%m%d_%H%M%S)
	BACKUP_FILE="${BACKUP_DIR}/complete_backup_${DATE}.sql.gz"

	# Get non-system databases
	DB_LIST=$(mysql -u root -p$1 -NBe "SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys');" 2>/dev/null) \|\| { echo "Failed to list databases"; exit 1; }

	# Start the backup file (compress on-the-fly)
	{
	echo "-- Complete MySQL Backup Generated on $(date)"
	echo "-- "
	echo ""

	# Export users and grants
	echo "-- Users and Privileges"
	mysql -u root -p$1 -NBe "SELECT CONCAT('CREATE USER IF NOT EXISTS \'', user, '\'@\'', host, '\' IDENTIFIED WITH \'', plugin, '\' AS \'', authentication_string, '\';') FROM mysql.user WHERE user NOT IN ('mysql.sys', 'mysql.session', 'mysql.infoschema');" 2>/dev/null \|\| { echo "Failed to export users"; exit 1; }
	echo ""

	mysql -u root -p$1 -NBe "SELECT CONCAT('SHOW GRANTS FOR \'', user, '\'@\'', host, '\';') FROM mysql.user WHERE user NOT IN ('mysql.sys', 'mysql.session', 'mysql.infoschema');" \| mysql -u root -p$1 -N 2>/dev/null \| grep '^GRANT' \|\| { echo "Failed to export grants"; exit 1; }
	echo ""
	echo "FLUSH PRIVILEGES;"
	echo ""

	# Export all non-system databases with objects
	echo "-- All User Databases, Routines, Triggers, and Events"
	mysqldump -u root -p$1 --databases $DB_LIST --triggers --routines --events --single-transaction --master-data=2 2>/dev/null \|\| { echo "Failed to dump databases"; exit 1; }

	} \| gzip > "$BACKUP_FILE"

	echo "Backup completed: $BACKUP_FILE"
	#!/bin/bash

	# Complete MySQL Backup Automation Script
	# Save as: mysql_backup.sh

	# Configuration
	BACKUP_DIR="/backup/mysql"
	MYSQL_USER="root"
	MYSQL_PASS="password"
	RETENTION_DAYS=7
	EMAIL="[email protected]"
	LOG_FILE="/var/log/mysql_backup.log"

	# Functions
	log_message() {
	echo "$(date '+%Y-%m-%d %H:%M:%S') - $1" >> "$LOG_FILE"
	}

	send_notification() {
	echo "$1" \| mail -s "MySQL Backup Notification" "$EMAIL"
	}

	# Main backup process
	main() {
	log_message "Starting MySQL backup"

	# Create backup
	BACKUP_FILE="${BACKUP_DIR}/mysql_$(date +%Y%m%d_%H%M%S).sql"

	if mysqldump -u $MYSQL_USER -p$MYSQL_PASS \
	--all-databases \
	--routines \
	--triggers \
	--events \
	--single-transaction \
	> "$BACKUP_FILE" 2>> "$LOG_FILE"; then

	# Compress backup
	gzip "$BACKUP_FILE"
	log_message "Backup completed: ${BACKUP_FILE}.gz"

	# Remove old backups
	find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
	log_message "Old backups cleaned up"

	send_notification "MySQL backup completed successfully"
	else
	log_message "Backup failed!"
	send_notification "MySQL backup FAILED - check logs"
	exit 1
	fi
	}

	# Run main function
	main