backup databases

[hossein]

Hello,
i want to backup all databases in my server every 4 hour,
any body can help me to solve this problm ?
thank you

 

[mageshm]

Hi hossein,

For your requirement you need to done this by shell script.

Create a file “mysql_backup.sh” on your server and put the below code. Assign the executable permission on your shell file. like 755

All database on cpanel server.
===============================

#!/bin/bash

# Add your backup dir location, password, mysql location and mysqldump location
DATE=$(date +%d-%m-%Y)
BACKUP_DIR="/backup/test-backup"
MYSQL_USER="root"
MYSQL_PASSWORD="***"
MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump

# To create a new directory into backup directory location
mkdir -p $BACKUP_DIR/$DATE

# get a list of databases
databases=`$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema)"`

# dump each database in separate name
for db in $databases; do
echo $db
$MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/$DATE/$db.sql.gz"
done

single database on cpanel server.
==================================

#!/bin/bash
password="***"
date_format=`date +%d-%m-%Y`
mysqldump -u root -p [password] 2daygeek | gzip -9 > /backup/db/yourdbname$date_format.sql.gz
find /backup/db/test* -mtime +5 -exec rm {} \;

cron job for every 4 hour.

==========================
To run the shell file every 4 hour you need to set the cron job like below.

0 */4 * * * /bin/mysql_backup.sh

 

[cPanelMichael]

Hello

There are no native options to backup all databases every four hours. The previous post should help you setup the cron job.

Thank you.

 

[hossein]

Hi hossein,

For your requirement you need to done this by shell script.

Create a file “mysql_backup.sh” on your server and put the below code. Assign the executable permission on your shell file. like 755

All database on cpanel server.
===============================

#!/bin/bash

# Add your backup dir location, password, mysql location and mysqldump location
DATE=$(date +%d-%m-%Y)
BACKUP_DIR="/backup/test-backup"
MYSQL_USER="root"
MYSQL_PASSWORD="***"
MYSQL=/usr/bin/mysql
MYSQLDUMP=/usr/bin/mysqldump

# To create a new directory into backup directory location
mkdir -p $BACKUP_DIR/$DATE

# get a list of databases
databases=`$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema)"`

# dump each database in separate name
for db in $databases; do
echo $db
$MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --databases $db | gzip > "$BACKUP_DIR/$DATE/$db.sql.gz"
done

single database on cpanel server.
==================================

#!/bin/bash
password="***"
date_format=`date +%d-%m-%Y`
mysqldump -u root -p [password] 2daygeek | gzip -9 > /backup/db/yourdbname$date_format.sql.gz
find /backup/db/test* -mtime +5 -exec rm {} \;

cron job for every 4 hour.

==========================
To run the shell file every 4 hour you need to set the cron job like below.

0 */4 * * * /bin/mysql_backup.sh

Hello mageshmn ,
backups save to wich folder ?
may i use remote to move backups to ftp server ?

 

[cPanelMichael]

The example provided would backup all databases to /backup/test-backup, but you could modify the script to change that. You would have to add an additional step to transfer your backups to a remote server using a utility such as SFTP or Rsync.

Thank you.

 

[YasIT]

Hello

It is also simple to back up all of the databases on a server with command :

mysqldump --all-databases > all_databases.sql

But when restore full backups(files,logs and ...) cpanel and restore sql with "mysql -u root -p passwordroot < all_databases.sql" on another server all websites cannot connect to database but size databases in MySQL Databases is correct and login in to phpmyadmin

so, If I use my this shell This problem will be repeated?

Please help , thank you.

 

[hossein]

Hello

It is also simple to back up all of the databases on a server with command :

mysqldump --all-databases > all_databases.sql

But when restore full backups(files,logs and ...) cpanel and restore sql with "mysql -u root -p passwordroot < all_databases.sql" on another server all websites cannot connect to database but size databases in MySQL Databases is correct and login in to phpmyadmin

so, If I use my this shell This problem will be repeated?

Please help , thank you.

Hello Yas It
din't care about it ,
when you are using above shell script all databases exported from my sql ,
i'm check all thing good and database worked on any server...
could you test 1 database on another server ? give us out put of logs...
thank you

 

[YasIT]

Hello

you testing with single database , i dump all databases!

 

[hossein]

Hello

you testing with single database , i dump all databases!

i was test with all databases.

 

[hossein]

thanks every body to help me