Mysqldump all tables from database in separate files - Revision history

MilosZ at 10:23, 5 April 2024

2024-04-05T10:23:24Z

MilosZ at 15:51, 9 October 2020

2020-10-09T15:51:36Z

MilosZ: Created page with " #!/bin/bash USER=myuser PASSWORD=mypwd DATABASE=nameofdb for i in $(mysql -uUser -pPASSWORD DATABASE -e "show tables;"|grep -v Tables_in_);do my..."

2020-10-09T15:50:21Z

Created page with "<syntaxhighlight lang="bash"> #!/bin/bash USER=myuser PASSWORD=mypwd DATABASE=nameofdb for i in $(mysql -uUser -pPASSWORD DATABASE -e "show tables;"|grep -v Tables_in_);do my..."

New page

<syntaxhighlight lang="bash">
#!/bin/bash
USER=myuser
PASSWORD=mypwd
DATABASE=nameofdb

for i in $(mysql -uUser -pPASSWORD DATABASE -e "show tables;"|grep -v Tables_in_);do mysqldump -u$USER -p$PASSWORD $DATABASE $i > /destination/dir/$i".sql";done

tar -cjvf "backup_mysql_"$(date +'%Y%m%d')".tar.bz2" /destination/dir/*.sql
</syntaxhighligh>

← Older revision		Revision as of 10:23, 5 April 2024
Line 8:		Line 8:

	tar -cjvf "backup_mysql_"$(date +'%Y%m%d')".tar.bz2" /destination/dir/*.sql		tar -cjvf "backup_mysql_"$(date +'%Y%m%d')".tar.bz2" /destination/dir/*.sql
		+	</syntaxhighlight>
		+
		+	# version 2
		+	<syntaxhighlight lang="bash">
		+	#!/bin/bash
		+
		+	# MySQL credentials and database information
		+	USER="your_mysql_username"
		+	PASSWORD="your_mysql_password"
		+	DATABASE="your_database_name"
		+	HOST="localhost"
		+
		+	# Backup directory
		+	BACKUP_DIR="/path/to/your/backup/directory"
		+
		+	# Date format to append to the filename
		+	DATE=$(date +%Y%m%d%H%M%S)
		+
		+	# MySQL dump command, don't forget to replace mysql with mysqldump if you're using MariaDB
		+	MYSQLDUMP="/usr/bin/mysqldump"
		+
		+	# Check if backup directory exists
		+	if [ ! -d "$BACKUP_DIR" ]; then
		+	mkdir -p "$BACKUP_DIR"
		+	fi
		+
		+	# Get a list of all tables in the database
		+	tables=$(mysql -u $USER -p$PASSWORD -h $HOST $DATABASE -e 'SHOW TABLES;' \| awk '{ print $1}' \| grep -v '^Tables' )
		+
		+	for table in $tables; do
		+	echo "Dumping $table"
		+	$MYSQLDUMP --user=$USER --password=$PASSWORD --host=$HOST $DATABASE $table > "$BACKUP_DIR/${DATABASE}_${table}_$DATE.sql"
		+	done
		+
		+	echo "Backup completed!"
	</syntaxhighlight>		</syntaxhighlight>

← Older revision		Revision as of 15:51, 9 October 2020
Line 8:		Line 8:

	tar -cjvf "backup_mysql_"$(date +'%Y%m%d')".tar.bz2" /destination/dir/*.sql		tar -cjvf "backup_mysql_"$(date +'%Y%m%d')".tar.bz2" /destination/dir/*.sql
−	</~~syntaxhighligh~~>	+	</syntaxhighlight>