How to take MySQL database backup...

MySQL database backup:-

This article describes how you can take the backup of MySQL databases. MySQL is default database in Linux Operating System. It’s a great database engine. If your database server goes down, you could lose your important data, so you must always have backup of it. Command mysqldump is easy command for taking mysql database backup, it’s a Linux shell mysql command. You need root access of server to run mysqldump command. There are two ways of MySQL database backup. i.e. manually and automatically.

Manual Method:- Logged in server as root user and run the below command.

[root@localhost~]# mysqldump --opt -u admin -p databasename > databasename.sql

Enter password:

After the run above command enter the mysql admin password.

Where

-u = User

admin = Mysql user name

-p = Password

Automatic Method:- This method is depends on cron job (Scheduler).

You can set the automatic method using cron job. By default crontabs rpm is installed in Linux server.

STEP 1:- Check crond service is running in server or not? If it is not, start it.

[root@localhost ~]# service crond status

crond (pid 1416) is running...

[root@localhost ~]# service crond start

Starting crond: [ OK ]

STEP 2:- Then set the scheduler on desire time. Edit the crontab and put the script in it and save the same.

[root@localhost ~]# crontab -e

55 23 * * * mysqldump --opt --user=admin --password='xyzabc123' databasename | gzip > /root/mysqlbackup/databasename.sql.gz

As per above example backup of database will automatically start on 23:55:00 and store it in /root/mysqlbackup/ directory (Folder).

STEP 3:- Run the below command and check the cron job list.

[root@localhost ~]# crontab -l

55 23 * * * mysqldump --opt --user=admin --password='xyzabc123' databasename | gzip > /root/mysqlbackup/databasename.sql.gz

That's it...