I have a script that does just that: a backup of MariaDB / MySQL and the entire PostgreSQL and rsync the file to another location.

#!/bin/bash

BASE_DIR=/tmp/backups
TMP_DIR="$BASE_DIR/$(date +%Y%m%d-%H%M%S)-$RANDOM"
DEST_DIR=/mnt/backups/database

if [ -d $TMP_DIR ] ; then
        rm -rf $TMP_DIR
fi;

mkdir -p $TMP_DIR
cd $TMP_DIR

pg_dumpall -U postgres | gzip > postgresql.sql.gz
mysqldump --all-databases -u root | gzip > mysql.sql.gz

if [ -d $DEST_DIR ] ; then
        rsync -a $BASE_DIR/ $DEST_DIR
fi;

And this script is invoked once a day by cron:

$ crontab -l
@daily /usr/local/bin/backup

There are a thousand ways. Take a look at this opensource project that can solve your problem:

link

Or read this blog that suggests 10 different ways.

link

I'm not a MySQL expert, but here's my answer, being a bit more simplistic, and I think it's more correct as well.

To perform a backup with the online bank, you must have binary logging enabled. Otherwise you will have a problem in integrity between the tables in the restore process (transactions that were in progress during the dump process)

$ mysqldump --all-databases -F > /tmp/backup.mysql-full.sql
$ mysqladmin flush-logs

Then you should copy the binary logs generated during this process. To restore this backup, you must apply the dump and the logs:

$ mysql < backup.mysql-full.sql
$ mysqlbinlog mysql.bin.XXX > dump.XX1.sql
$ mysql -f < dump.XX1.sql

$ mysqlbinlog mysql.bin.XX2 > dump.XX2.sql
$ mysql    < dump.XXX.sql
# e por ai vai... aplicando cada binary log.

Important

I do not remember why, but if I am not mistaken the user grants are not saved in this dump, so you need to generate them manually too! I use this shell function that I picked up on some other stack question here.

mygrants()
{
  mysql -B -N $@ -e "SELECT DISTINCT CONCAT(
    'SHOW GRANTS FOR ''', user, '''@''', host, ''';'
    ) AS query FROM mysql.user" | \
  mysql $@ | \
  sed 's/\(GRANT .*\)/;/;s/^\(Grants for .*\)/##  ##/;/##/{x;p;x;}'
}

Reminder

The dump backup does not copy your configuration file my.cnf , so be sure to copy it manually!

Alternative

I use the percona xtrabackup tool (freeware) to back up the online bank.
But there is no backup in .sql, it's practically a copy of datafiles + binary logs. I did backup and restore tests and it worked perfectly. In addition to having a much better performance than the dump because it is a "raw" copy. But there also has the disadvantage of not having it in TXT format.

For reference, I back up a database that runs the Zabbix monitoring tool, with 7 GB of data (the size of the mysql folder where the data resides). The backup is done in a maximum of 3 minutes. Considering it's a virtual machine (VmWare ESXi) on a desktop, I think it's wonderful this time.

Alternative 2

In my scripts, before I was using xtrabackup, but as my bank grew I started to have some negative performance effects on the environment during the backup. I researched alternative methods and started using snapshots, copying a full image of the database.

The process of locking the bank, generating the snapshot and releasing again takes no more than 5 seconds. For this I used the snapshot feature of LVM I perform the following steps (very simplified version):

• In MySQL, I force a flush of data to the disk and lock any record in the datafiles:
  FLUSH TABLES WITH READ LOCK
• Gero snapshot on the mount point of the MySQL datafiles:
  lvcreate --snapshot myvg/lvmysql -n lv_snap1 -L 10G
• I release the write to MySQL again UNLOCK TABLES
• Snapshot FS amount
  mount /dev/myvg/lv_snap1 /mnt/bkp
• I rsync the files to my backup server
• I unmount the FS and remove the created snapshot.
• On the target server I still activate MySQL there and run a check before sending it to the final backup.

It takes some care in using snapshot, as it has a useful "life" that is the space allotted to it. This useful life is the time you have to copy the content of it to another place and then destroy the snapshot.

There is a (very good, I recommend) class that does this

MySqlBackup.Net

It's very simple to understand and use, and you can configure countless things, such as which tables you want to back up, whether you want to back up the settings, and so on.

In linux you can use crontab to perform this task daily.

Type:

crontab -e

Add the line, replacing the values with that of your server:

0 23 * * * mysqldump -h localhost -u usuario -psenha meuBanco > backup_meuBanco_$(date +%s).sql 2>&1

Save (ESC: wq ENTER) and ready, every day at 11 o'clock your dump script will save your database.

In Amazon server instances aws ( aws.net . For linux, I installed # AWS-CLI and set up the security keys ( iam ). From there I created the script below to perform the backup. If you wish, you can also set up custody of backups in the s3 that it purges automatically.

#!/bin/bash
##########################################
# Backup database MySQL enviando para o S3
# Criado por Andre Mesquita
##########################################

#VARIAVEIS
DATAHORA='date +%Y%m%d-%H%M'
FILESDIR='cd /sites/tmp'
TAR=/bin/tar
RM=/bin/rm

IPBANCO="127.0.0.1"
USERBANCO='seu usuario'
SENHABANCO='sua senha'
NOMEDOBANCO='nome do database'
NOMEDOBUCKET='meusbackups'

#Entrando no diretorio de backup
$FILESDIR

#Executa o backup do banco de dados
echo 'Realizando o backup do banco de dados...'
mysqldump --host=$IPBANCO --user=$USERBANCO --password=$SENHABANCO --databases $NOMEDOBANCO >  ./backup_$NOMEDOBANCO.sql

#Compactando o backup
echo 'Compactando arquivos do site...'
$TAR zcf $NOMEDOBANCO_database.bkp_$DATAHORA.tar.gz ./backup_$NOMEDOBANCO.sql

echo 'Enviando para o respositorio de backups...'
aws s3 cp ./$NOMEDOBANCO_database.bkp_$DATAHORA.tar.gz s3://$NOMEDOBUCKET/$NOMEDOBANCO_database.bkp_$DATAHORA.tar.gz

echo 'Excluindo o script descompactado...'
$RM $NOMEDOBANCO_database.bkp_$DATAHORA.tar.gz
$RM backup_$NOMEDOBANCO.sql

echo ' '
echo ' Operacao finalizada. '
echo ' '

I hope it helps.

I do not know if this is what you need, but it follows a complete code to make bkp from your database, just save the code in a php file, create a folder called "db_bkp" and do not forget to fill in the data access to the database. inside your hosting server will have an option called "cron task" or something like that, just go there and type the address for that file and you're done

<?php
ini_set('display_errors',1); ini_set('display_startup_erros',1); error_reporting(E_ALL);//force php to show any error message

backup_tables(DB_HOST,DB_USER,DB_PASS,DB_NAME);//don't forget to fill with your own database access informations

function backup_tables($host,$user,$pass,$name)
{
    $link = mysqli_connect($host,$user,$pass);
    mysqli_select_db($link, $name);
        $tables = array();
        $result = mysqli_query($link, 'SHOW TABLES');
        $i=0;
        while($row = mysqli_fetch_row($result))
        {
            $tables[$i] = $row[0];
            $i++;
        }
    $return = "";
    foreach($tables as $table)
    {
        $result = mysqli_query($link, 'SELECT * FROM '.$table);
        $num_fields = mysqli_num_fields($result);
        $return .= 'DROP TABLE IF EXISTS '.$table.';';
        $row2 = mysqli_fetch_row(mysqli_query($link, 'SHOW CREATE TABLE '.$table));
        $return.= "\n\n".$row2[1].";\n\n";
        for ($i = 0; $i < $num_fields; $i++)
        {
            while($row = mysqli_fetch_row($result))
            {
                $return.= 'INSERT INTO '.$table.' VALUES(';
                for($j=0; $j < $num_fields; $j++)
                {
                    $row[$j] = addslashes($row[$j]);
                    if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
                    if ($j < ($num_fields-1)) { $return.= ','; }
                }
                $return.= ");\n";
            }
        }
        $return.="\n\n\n";
    }
    //save file
    $handle = fopen('db_bkp/db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');//Don' forget to create a folder to be saved, "db_bkp" in this case
    fwrite($handle, $return);
    fclose($handle);
    echo "bkp efetuado com sucesso";//Sucessfuly message
}
?>