MySQL : mysqldump database backup

MySQL 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
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 set --login-path=emultiskills_MYSQL_mysqlbackup --host=localhost --user=mysqlbackup --password

-- 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 " 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













**********************Clean up ********************************

mysql -u root -p
Mysql@1234
Select host, user from mysql.user;

drop user 'mysqlbackup'@'localhost';
Select host, user from mysql.user;



mysql_config_editor print --all

mysql_config_editor remove --login-path=emultiskills_MYSQL_mysqlbackup

mysql_config_editor print --all



" angled bracket"linux2_MySQLDump.sh