Faruque Ahmed : MCP, MCSA, MCSE, MCTS, MCIT, CCNA, OCA, OCP, GCP
---------------
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
Before making any changes, first make sure to stop mysql service
# service mysqld stop
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
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------------------------------
You have many options for creating databases backups. read few options below. For this example we have using databse name “mydb”.
# mysqldump -u root -p mydb > mydb.sql
# mysqldump -u root -p mydb | gzip > mydb.sql.gz
# mysqldump -u root -p mydb tbl_student > tbl_student.sql
# mysqldump -u root -p --databases mydb1 mydb2 mydb3 > mydb1-mydb2-mydb3.sql
# mysqldump -u root -p --all-databases > all-db-backup.sql
# mysqldump -u root -p --no-data mydb > mydb.sql
# mysqldump -u root -p --no-create-info mydb > mydb.sql
# mysqldump -u root -p --xml mydb > mydb.xml
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