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;

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;