MySQL : Backup all MySQL databases uing mysqldump and compress it
How to backup all MySQL databases on Linux as single dump by using mysqldump and compress it
********************************************************************************************
Requirement : Backup All mysql databases and compress the dump file once backup completes.
Syntax
*******
mysqldump --user=mysqlbackup --password='Password#123' --all-databases | gzip "angled bracket"/mysqlbackup/AllDatabases.sql.gz
mysqldump
*********
The mysqldump client is a backup program was written by Igor Romanenko.
It can be used to dump a database or a collection of databases for backup .
The dump typically contains SQL statements to create the table, populate it, or both.
However, mysqldump can also be used to generate files in CSV, other delimited text, or XML format.
Create MYSQL backup user
*************************
mysql -u root -p Mysql@1234
MYSQL"angled bracket"
drop user 'mysqlbackup'@'localhost';
drop user 'mysqlbackup'@'%';
Select host, user from mysql.user;
CREATE USER 'mysqlbackup'@'localhost' IDENTIFIED BY 'Password@123';
GRANT SELECT, SHOW VIEW, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER ON . TO 'mysqlbackup'@'localhost';
GRANT LOCK TABLES ON . TO 'mysqlbackup'@'localhost';
Select host, user from mysql.user;
Passwordless login using a string
*********************************
$
mysql_config_editor print --all
mysql_config_editor remove --login-path=emultiskills_MYSQL_mysqlbackup
mysql_config_editor set --login-path=emultiskills_MYSQL_mysqlbackup --host=localhost --user=mysqlbackup --password
Password@123
-- It prompts for the password.
-- the user/pass are saved encrypted in your /home/mysql/.mylogin.cnf
mysql --login-path=emultiskills_MYSQL_mysqlbackup
MYSQL BACKUP SHELL SCRIPT
*************************
vi linux2_MySQLDump.sh
#!/bin/bash
export DUMP_PATH=/mysql/backup/
DATE=`date +%d%b%Y`
mysqldump --login-path=emultiskills_MYSQL_mysqlbackup --all-databases | gzip "angled bracket" ${DUMP_PATH}/linux2_all_MySQLdump_${DATE}.sql
RC1=$?
if [ $RC1 -ne 0 ];then
echo "Backup unsuccessful"
else
echo "Backup successful"
echo "Deleting the below old backup files"
find ${DUMP_PATH} -name "linux2_all_MySQLdump_*" -type f -mtime +3 -exec ls {} \;
if [ $? = 0 ];then
find ${DUMP_PATH} -name "linux2_all_MySQLdump_*" -type f -mtime +3 -exec rm {} \;
fi
fi
sh -x linux2_MySQLDump.sh