MYSQL Database backup and restore in CentOS

Backup

# mysqldump -u root -p dbname > /var/www/html/dbbackup/13oct16.sql

Restore

#mysql -u root -p
password:
mysql>create database dbname;
mysql>GRANT ALL ON dbname.* TO dbusername@localhost IDENTIFIED BY 'dbpassword';
mysql>quit

# mysql -u root -p dbname < /var/www/html/dbbackup/13oct16.sql
password:
-------------------------------------------------------------------------------------------
mysql> select * from mysql.user;

-------------------------------------------------------------------------------------------
//mysql full backup

#!/bin/bash
today=`date +%Y-%m-%d`

mysqldump -uroot -padmin -h localhost --single-transaction --ignore-table=mysql.event --all-databases > /var/www/html/database_dump/bk_$today.sql
-------------------------------------------------------------------------------------------


Make cronjob

#crontab -e


0 6  * * * /usr/bin/sh /var/www/dbbackup/sqlbackup.sh

// this will be done at every day at 6 O'clock

Code

#!/bin/sh

# sqlbackup.sh
DOC="/home/centos/dbbackup/dailydbbackup"

Mdate="$(date +"%d-%m-%Y | %H:%M:%S")"

mysqldump -uroot -ptreecare glpi > /home/centos/dbbackup/dailydbbackup/backup.$Mdate.glpi_Database.sql
find $DOC/*.sql -mtime +5 -exec rm {} \;     //delete file been more then 5 days

mysql>drop database glpi;   // delete database
mysql>drop user root@localhost;   //delete mysql.user

mysql>create database dbname;
mysql>use dbname;
mysql>create table tablename(name varchar(10));
mysql>insert into tablename.dbname values('boobalan');

To access db from everywhere
mysql>select * from mysql.user \G;

CREATE USER 'myuser'@'%' IDENTIFIED BY PASSWORD '*HASH';
GRANT ALL ON mydb.* TO 'myuser'@'%';
GRANT ALL ON mydb TO 'myuser'@'%';
GRANT CREATE ON mydb TO 'myuser'@'%';
FLUSH PRIVILEGES;