How To Take Backup of MySQL on Centos 7.

Step 1 : We are going to use mysqldump to create a backup dump of the database, to backup all the databases, create backup folder.
[ mkdir /var/lib/mysql/backup]

Step 2 : Lets check the databases size:
[ mysql -u root -p SELECT table_schema AS “Database”, SUM(data length + index length) / 1024 / 1024 / 1024 AS “Size (GB)” FROM information_schema.TABLES GROUP BY table_schema ; ]

Step 3: In case of a large database this can take a long time, you might want to install pv progress bar to get some information during, the data will be piped through the progress bar, for example =|pv -W.
[ yum install pv ]

Step 4: Now Lets create some Database dumps, the basic is to dump all the database.
[ bmysqldump -u root -p –all-databases | pv -W > /var/lib/mysql/backup/all-database.sql ]

Step 5: Make sure the dump is created.
[ ls -1 -s /var/lib/mysql/backup/ ]

Step 6: To create zipped backup.
[ mysqldump –single-transaction -u root -p ‘Database name’ | gzip | pv -W > /var/lib/mysql/backup/’Database Name’.sql.gz ]

Step 7: To restore all the databases.
[ mysql -u root -p –all-databases < /var/lib/mysql/backup/all-database.sql ]

Step 8: To restore specific database.
[ mysql -u root -p ‘Database name’ < /var/lib/mysql/backup/’Database name’.sql ]

Step 9: To restore from zipped backup/dump.
[ zcat /var/lib/mysql/backup/’Database Name’.sql.gz | mysql -u root -p ‘Database Name’ ]