1
---------------
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
Step 2:
Create a cron job for executing this script to run every first minute of every hour.
Give execute permission to backup.sh
#!/bin/bash
YEAR=`date +%Y`
MONTH=`date +%m`
DAY=`date +%d`
HOUR=`date +%H`
mkdir -p $YEAR/$MONTH/$DAY/$HOUR
mysqldump -u root -p12345 db_name > $YEAR/$MONTH/$DAY/$HOUR/backup_db.sql
# chmod +x /root/backup.sh
Save and Exit.
Now our system takes automatic take backup of database db_name and save to /rootdirectory.
---------------------xxxxxxxxxxxxxxxxxxxxxxxxx------------------------------
Backup and Restore a MySQL Database
Options for Creating MySQL Databases Backup
You have many options for creating databases backups. read few options below. For this example we have using databse name “mydb”.
1. Full Database Backup in Plain .sql File
# mysqldump -u root -p mydb > mydb.sql
2. Full Database Backup in Archive .sql.gz File
# mysqldump -u root -p mydb | gzip > mydb.sql.gz
3. Backup Single Table Only
# mysqldump -u root -p mydb tbl_student > tbl_student.sql
4. Backup Multiple Databases
# mysqldump -u root -p --databases mydb1 mydb2 mydb3 > mydb1-mydb2-mydb3.sql
5. Backup All Databases
# mysqldump -u root -p --all-databases > all-db-backup.sql
6. Backup Database Structure Only (no data)
# mysqldump -u root -p --no-data mydb > mydb.sql
7. Backup Database Data Only (no table structure)
# mysqldump -u root -p --no-create-info mydb > mydb.sql
8. Backup MySQL Database in XML Format
# mysqldump -u root -p --xml mydb > mydb.xml
How to Restore MySQL Backup
For restoring databases from backup is quite simple. We use mysql command for it. For example following command will restore all backup from mydb.sql to mydb database.
# mysql -u root -p mydb < mydb.sql
----------
Type
# crontab -e
1 * * * * root /root/backup.sh