---------------

 BACKUP MYSQL DATABASE TO A FILE?

Backing up your database is a very important system administration task, and should generally be run from a cron job at scheduled intervals. We will use the mysqldump utility included with mysql to dump the contents of the database to a text file that can be easily re-imported.

Syntax:

# mysqldump -h localhost -u root -pmypassword database_name > dumpfile_name.sql

Example:

# mysqldump -h localhost -u root -pPa55w0rD database110 > backup_file.sql

This will give you a text file containing all the commands required to re-create the database.

--------------------x--------------------------

Change Default MySQL Data Directory

1. Stop MySQL

Before making any changes, first make sure to stop mysql service

# service mysqld stop

2. Change Data Directory

Now copy default MySQL data directory (/var/lib/mysql) to other location as per your requirement. Also set the required MySQL ownership on new directory location. As per below command, we are relocating data directory to /data/mysql.

# cp -rap /var/lib/mysql /data/mysql # chown mysql.mysql /data/mysql

Now edit MySQL default configuration file #  vi /etc/my.cnf and update values of datadir and socket variable.

Change From: datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock  Change To:  datadir=/data/mysql socket=/data/mysql/mysql.sock

3. Start MySQL

After making all above changes. finally start MySQL service. Now it will use new data directory path

# service mysqld start

----------

Automatic take MySQL Database Backup Using CRONTAB

This article describes how to set up a cron job that automatically backs up a MySQL database to a file at specific time periods.

Suppose I want to take db backup every hours of everyday. So create a .sh file according to below script.

Step 1:

I created file in /root/backup.sh