MYSQL command to take a backup of the database in Redhat linux 7.2

UNIX for Advanced & Expert Users
1

I am new to mysql database ,

we have a mysql database running on linux , and we use mysql database for bugzilla, so we wanted to take a backup .

what is the command for taking the entire database backup
from the command prompt with all options.

Thanks in advance
Bache Gowda

2

Hi,

Use the mysqldump command...

The argument for a full backup is -A... do mysqldump --help.

3

Thanks, my another problem is how to make sure backup file is proper or not.
I userd the command to take backup us mysqldump -A -p --password=***** >back.sql

backup.sql file created but file having lot of junk character. How to make sure?

Bache Gowda

4

It's not suppose to have "junk", because it's a text file... maybe the command is corrupted or you have some weird data on your tables...
Use mysqlcheck to check your DBs before the dump...

5

Here is a way to email a backup using bash, perl, and crontab.

mysqlbackup

#!/bin/sh 
mysqldump -uroot -ppwd --opt db1 > /sqldata/db1.sql 
mysqldump -uroot -ppwd --opt db2 > /sqldata/db2.sql 
cd /sqldata/ 
tar -zcvf sqldata.tgz *.sql 
cd /scripts/ 
perl emailsql.cgi

Use perl to send a MIME email.

emailsql.cgi

#!/usr/bin/perl -w 
use MIME::Lite; 
$msg = MIME::Lite->new( 
From => 'mysqlbackup@yoursite.com', 
To => 'you@yoursite.com', 
Subject => 'sqldata.tgz MySQL backup!', 
Type => 'text/plain', 
Data => "Hi You,\n MySQL database backups.");
 
$msg->attach(Type=>'application/x-tar', 
Path =>"/sqldata/sqldata.tgz", 
Filename =>"sqldata.tgz"); 

$msg->send;

Use crontab -e to edit your schedule to run at 2 am every day.

0 2 * * * * /myscripts/mysqlbackup