Резервное копирование и восстановление моего sql

devops

Aug 7, 2023
12 min read

The importance of backing up your database cannot be overstated. In addition to protecting against data loss, backups can also help with disaster recovery and testing. With a backup, you can restore your database to a previous state and recover lost data. Backups can also be used to test new features or upgrades without risking your live data.

In summary, MySQL is a popular open-source RDBMS used for web-based applications. MySQL Backup is the process of creating a copy of the database to protect against data loss. It is important to backup your database to protect against data loss, aid in disaster recovery, and facilitate testing.

In this post, we offer you a rich list of MySQL/ MariaDB backup scripts, and tools to use to backup your database for free.

1- MySQL backup

This is a shell script that allows you to back-up your MySQL databases to anywhere. You can dump and restore simply on the server, dump your database to a local filesystem, SMB server, connect to any docker container and backup your selected database from any container, and many more.

GitHub – databacker/mysql-backup: image to enable automated backups of mysql databases in containers

image to enable automated backups of mysql databases in containers – GitHub – databacker/mysql-backup: image to enable automated backups of mysql databases in containers

2- PHP Backup Utility

PHPBU is a PHP tool that creates and encrypts backups, syncs your backups to other servers or cloud services and assists you monitor your backup creation.

• Backup databases: ArangoDB, Elasticsearch, InfluxDB, Ldap, MongoDB, MySQL, Percona XtraBackup, PostgreSQL, Redis, and MariaDB.
• Compress backup to bzip2, gzip, xz, zip.
• Validate backups
• Encrypting backups
• Sync backups to other locations: Amazon S3, Azure Blob, Dropbox, FTP, SFTP, Yandex disk and more
• Webhook
• Reporting

PHPBU: Open-source Free PHP Database Backup Utility

PHPBU is an essential PHP tool for anyone looking to create and encrypt backups, sync backups to other servers or cloud services and monitor backup creation with ease. One of its key features is its support for creating backups for a wide range of database engines, including but not limited

GitHub – sebastianfeldmann/phpbu: PHP Backup Utility – Creates and encrypts database and file backups, syncs your backups to other servers or cloud services and assists you monitor your backup process

3- BackMeUp

An automated MySQL / MariaDB databases and files backup solution on *nix Machines using Amazon S3, WebDAV (ownCloud / NextCloud etc.), Google Drive and Dropbox.

This script does some simple tasks:

• The script dumps all of your MySQL databases individually.
• The script backs up all of your Web files (e.g: root path of all of your virtual hosts).
• The script compresses your web-root and databases to a single archive.
• The script uploads the compressed archive into a folder in your Dropbox account, Amazon S3 bucket, Mega.nz or to a WebDav solution such as OrnCloud/NextCloud Server.
• If the method is set to dropbox, The script makes sure that you always have the newest Dropbox-Uploader script.
• After the upload, the script cleans up the temporary files (dumps, the archive itself).

GitHub – Ardakilic/backmeup: BackMeUp: An automated MySQL / MariaDB databases and files backup solution on *nix Machines using Amazon S3, WebDAV (ownCloud / NextCloud etc.), Google Drive and Dropbox.

4- Backup Scripts

• For MyDumper: User will define weekly and daily retention times.
• For Mysqldump: User will define weekly and daily retention times.
• For Mysqlbinlog: User will define number of days that the binlog files will be keep it
• For XtraBackup: User will define weekly and daily retention times.

GitHub – nethalo/backup-scripts: Short collection of backup scripts. Mostly on bash

Short collection of backup scripts. Mostly on bash – GitHub – nethalo/backup-scripts: Short collection of backup scripts. Mostly on bash

5- Arkiv

This is a simple yet powerful backup tool designed to back-up local directories and MySQL databases to Amazon S3, and Amazon Glacier.

Arkiv could backup your data on a daily or an hourly basis (you can choose which day and/or which hours it will be launched).
It is written in pure shell, so it can be used on any Unix/Linux machine.

GitHub – Amaury/Arkiv: Backup and archive tool.

Backup and archive tool. Contribute to Amaury/Arkiv development by creating an account on GitHub.

6- MySQLDump for PowerShell

Scripts to use mysqldump on Windows systems. It requires OpenSSL to run.

GitHub – alphatrl/powershell-mysqldump-scripts: Using PowerShell for MySQLdump

Using PowerShell for MySQLdump. Contribute to alphatrl/powershell-mysqldump-scripts development by creating an account on GitHub.

7- Backup Wizard

GitHub – gavuk/backup-wizard: Guided set up of backups using rsnapshot and mysqldump

Guided set up of backups using rsnapshot and mysqldump – GitHub – gavuk/backup-wizard: Guided set up of backups using rsnapshot and mysqldump

8- sql-backup – Backup your MySQL / MariaDB server!

9- mysql-backup

This project is a fork from deitch/mysql-backup, with additional features to back up and restore additional resources along with the database backup.

The rationale behind this is that most applications also have additional resources that make part of the application that also needs to be backed up, not just the database. For example, a WordPress install.

• Backup and restore database and application files.
• Backup to local filesystem, S3 or SMB server.
• Scheduled backups.
• Delayed backup process start: define a delay before doing the first backup, whether time of day or relative to container start time (in seconds).

GitHub – juanluisbaptiste/mysql-backup: Docker image to automate web application backups.

Docker image to automate web application backups. Contribute to juanluisbaptiste/mysql-backup development by creating an account on GitHub.

10- MySQLBackup (Windows/ Powershell)

MySQL Backups on Windows through XAMPP, WAMP or a Standalone MySQL Installation. These scripts use 7Zip command line to compress scripts and MySQL .Net Connector 6.8.8.

GitHub – evanlanester/MySQLBackup: MySQL Backups on Windows through XAMPP, WAMP or a Standalone MySQL Installation

MySQL Backups on Windows through XAMPP, WAMP or a Standalone MySQL Installation – GitHub – evanlanester/MySQLBackup: MySQL Backups on Windows through XAMPP, WAMP or a Standalone MySQL Installation

11- mysqlalldbbackup

A shell script that can take backup of all MySQL database separately and compress them in a folder.

GitHub – shahreare/mysqlalldbbackup: A shell script that can take backup of all mysql database separately and compress them in a folder

12- php-mysqldump

This is a primitive PHP alternative to the mysqldump cli tool. It is possible to export tables (with all rows) and views.

GitHub – snsttr/php-mysqldump: PHP-Script as alternative to the native mysqldump cli tool

PHP-Script as alternative to the native mysqldump cli tool – GitHub – snsttr/php-mysqldump: PHP-Script as alternative to the native mysqldump cli tool

13- myphp-backup

Perform simple and fast MySQL backup/restore using PHP. You can use it to dump a full database or only some tables.

It requires PHP 5.0.5 or later.

GitHub – daniloaz/myphp-backup: Simple and fast MySQL backups using PHP

Simple and fast MySQL backups using PHP. Contribute to daniloaz/myphp-backup development by creating an account on GitHub.

14- Gox

Painless backup (and restore) tool for MySQL based on xtrabackup from Percona.

GitHub – toorop/gox: Painless MySQL backup (and restore)

Painless MySQL backup (and restore). Contribute to toorop/gox development by creating an account on GitHub.

15- kube-backup

Utility container to backup databases and files from containers in a Kubernetes cluster. Currently, it can use kubectl exec to backup database and files from within containers and store the backup files in an AWS S3 bucket.

GitHub – whereisaaron/kube-backup: Kubernetes utility container to back up files and database from other containers

Kubernetes utility container to back up files and database from other containers – GitHub – whereisaaron/kube-backup: Kubernetes utility container to back up files and database from other containers

16- backup-action

Github Action to backup MySQL, MongoDB and PostgreSQL databases.

GitHub – valerianpereira/backup-action: 🗄️ Github Action to backup MySQL, MongoDB and PostgreSQL databases

🗄️ Github Action to backup MySQL, MongoDB and PostgreSQL databases – GitHub – valerianpereira/backup-action: 🗄️ Github Action to backup MySQL, MongoDB and PostgreSQL databases

This tool provides a command that dumps the contents of a MySQL database.

It is the equivalent of mysqldump, with additional features, at the cost of performance (PHP implementation). The main purpose of this tool is to create anonymized dumps, in order to comply with GDPR regulations.

• Data converters (transform the data before it is dumped to the file).
• Recursive table filtering.
• Tables whitelist (only these tables will be included in the dump).
• Tables blacklist (not included in the dump).
• Dump options (compression, output type…).
• Predefined configuration templates (Magento, Drupal, OroCommerce).

GitHub – Smile-SA/gdpr-dump: Utility that creates anonymized database dumps (MySQL only). Provides default config templates for Magento, Drupal and OroCommerce.

18- MySqlBackup.Net

A tool to backup and restore MySQL database in C#/VB.NET/ASP.NET. It runs on MySql.Data.DLL, MySqlConnector.DLL and Devart.Express.MySql.DLL.

GitHub – MySqlBackupNET/MySqlBackup.Net: A tool to backup and restore MySQL database in C#/VB.NET/ASP.NET.

A tool to backup and restore MySQL database in C#/VB.NET/ASP.NET. – GitHub – MySqlBackupNET/MySqlBackup.Net: A tool to backup and restore MySQL database in C#/VB.NET/ASP.NET.

19- DbSync (PHP)

DbSync is a tool for efficiently comparing and synchronizing two or more remote MySQL database tables.

GitHub – mrjgreen/db-sync: PHP library with command line tool for efficiently syncing tables between remote MySQL databases

20- MysqlCG – Mysql Client Gui

A simple script to backup and manage MySQL databases.

GitHub – steffanjensen/MysqlCG: Mysql Client Gui for Bash

Mysql Client Gui for Bash. Contribute to steffanjensen/MysqlCG development by creating an account on GitHub.

21- Export MySQL database and Import it from a dump file in PHP

MySQL is a popular Linux-based database program. As a database, MySQL is a versatile application. It can be used for something as simple as a product database, or as complex as a WordPress website.

This package will help you export a MySQL database and import it from a dump file in PHP.

GitHub – vwedesam/Php-Mysql-Export-Import: Export MySQL database and Import it from a dump file in PHP

Export MySQL database and Import it from a dump file in PHP – GitHub – vwedesam/Php-Mysql-Export-Import: Export MySQL database and Import it from a dump file in PHP

22- Backup (MySQL, MariaDB, MongoDB, PostgreSQL)

Backup is an application to organize file and database backups. It supports compression, encrypted transfer, email reports and command execution before and after a process.

The included features are:

• Simple configuration
• Strong compressions (Bzip2, Gzip)
• Supports MariaDB, MongoDB, MySQL and PostgreSQL databases
• Supports Docker container
• Execute commands before and after
• Secure and encrypted transfers
• Email reports
• Shows backup size and duration

GitHub – bloodhunterd/Backup: Backup is an application to organize file and database backups. It supports compression, encrypted transfer, email reports and command execution before and after a process.

Storing the password

mysqldump
usermysqluser
passwordsecret

Be sure to set the correct permissions to the file (CHMOD 600 for unix), but Windows should do the same thing as your pgpass file and assume it is secure.

Mysqldump parameters

.mysqldump.exe 

You will need to pass your ‘–defaults-file=’ parameter first, as this should always be the first parameter passed if you are using it.

Additionally, unless you are planning on using this dump for a replica instance (from a primary instance), I strongly suggest using ‘–set-gtid-purged=off’.

Your last parameter is the ‘–result-file=’. Yes, same situation here, where you cannot simply use ‘>’.

The script

That covers a quick two part post. Find the script below, or on my github

Background info on issues with parameters I ran into
Official MySQL documentation on mysqldump: https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html
Why --defaults-extra-file option needs to be first: https://stackoverflow.com/questions/3836214/problem-with-mysqldump-defaults-extra-file-option-is-not-working-as-expecte




$user = 

$mysqlpassword = 



# Setup alias for 7zip for easy gziping

$7zipPath = 

Set-Alias Compress-7Zip $7ZipPath



#Location of mysqldump and backup destination

$mysqldumpLocation = 

$backupDest = $($database)



#Use for testing script

$serverlist = @(

@{

hostname = 

databases = @(



)

}

)


#Script to run mysqldump and gzip for each database instance(and db) that are mentioned in the Array(s) above)

 ($server  $serverlist) {

 ($database  $server.databases){

#write logging, in case we automate this in some task scheduler

Start-Transcript $(+$date+$server.hostname) -Append

Write-Host "Running backup for $database"

#Set dump location

$dumpfile = $($database)



#Check if file is already there, if so delete.

(Test-Path $dumpfile){

Remove-Item $dumpfile

Write-Host 

}{

Write-Host "$dumpfile does not exist. Proceeding"

}

#Execute pg_dump with parameters

.\mysqldump.exe --defaults=$mysqlpassword -u $user -h $server.hostname -B $database --set-gtid-purged=OFF --result=$dumpfile


#Variables for 7zip

$Source = $dumpfile

#Check if file is already there, if so delete

$Destination = $($database)

(Test-Path $Destination){

Remove-Item $Destination

Write-Host 

}{

Write-Host "$Destination does not exist. Proceeding"

}

#Compress to gzip at highest compression

Compress-7zip a -mx=5 $Destination $Source


Stop-Transcript

}

}

#Upload to s3 bucket

aws s3 cp \path\to\dumps s3//s3bucket --recursive

mysqldump is a command-line utility provided by MySQL that allows you to create backups of databases. Here is a basic example of how you can use it to back up and restore a MySQL database.

Backing Up a Database

The general command structure for backing up a database using mysqldump is:

Restoring a Database

To restore a database using a dump file, use the MySQL source command in MySQL shell:

This command creates a SQL dump file called mydatabase_backup.sql containing all the SQL commands needed to recreate the database.

To restore this database, you would use:

This command restores the database using the SQL commands in mydatabase_backup.sql.

1. There is no space between `-p` and the password. If your password has special characters, you might need to put the password in quotes.
2. These commands are typically run on a command line interface, such as terminal on Linux or MacOS, or command prompt/powershell on Windows.
3. Make sure that the user specified in the command has the necessary privileges to perform the backup or restore operation.
4. The `mysqldump` and `mysql` commands must be in your system’s PATH for these commands to work.
5. If running in kubernetes, these commands must be executed after connecting to the database container

Remember, always perform regular backups and test restore operations to ensure the integrity and availability of your data.

What is SSH

SSH (Secure Shell) is a method of secure data transmission over a network.

However, it is primarily a protocol, and on top of it, you can not only transmit data but also create SSH tunnels, allowing applications to transfer data over SSH.

How can SSH connections be used for MySQL backups?

1. Backup remote MySQL database over SSH — You can backup a remote DB and get a dump of this DB locally.
2. Backup local MySQL to a remote server over SSH — You can backup a local MySQL DB and send this backup over SSH to a remote server.

Why backup through SSH if direct connection is possible?

mysqldump has a --host parameter that allows you to backup a remote server. So why use SSH?

1. Brute-force attacks — Establishing a connection to the DB and refusing it happens very quickly, allowing for rapid spamming of authorization attempts to guess the password.
2. Exploit — There might be vulnerabilities in the DB authorization that allow access to the DB. Regular DB updates help avoid such risks, but not from zero-day exploits.
3. DDOS attacks — Your DB is responsible for authorization, and a large number of authorization attempts can overload your DB.

Good practice is not to allow access to the MySQL port (3306), so direct connection using mysqldump is only possible in closed networks.

Does it work on Windows?

Starting with Windows 10 and Windows Server 2019 — OpenSSH received official support and can be easily installed. Here is a simple installation guide in a couple of PowerShell commands.

However, PowerShell does not support on-the-fly compression, so it does not allow for full utilization of backup through SSH.

Examples

Note that in the examples below, creating temporary files is not required — the backup is created right where it’s needed, and sometimes it’s even compressed on the fly.

Backup Remote MySQL Database Over SSH

Backup

ssh root@ipaddress "mysqldump -u dbuser -ppassword sakila " > sakila.sql

Restore

ssh root@ipaddress "mysql -u dbuser -ppassword sakila " < sakila.sql

Backup remote MySQL over SSH with remote compression

ssh root@ipaddress "mysqldump -u dbuser -ppassword sakila | gzip -9" > sakila.sql.gz

Restore remote MySQL over SSH with remote uncompression

cat sakila.sql.gz | ssh root@157.230.19.242 "gunzip | mysql -u dbuser -ppassword db1"

In these examples, compression is performed on the same server where the MySQL DBMS is located. This reduces network load but can put a strain on the server with the DBMS. An alternative is to compress the backup after receiving it.

Backup remote MySQL over SSH with local compression

ssh root@157.230.19.242 "mysqldump -u root -p****** sakila" | gzip > sakila.sql.gz

Restore remote MySQL over SSH with local uncompression

gzip -d < sakila.sql.gz | ssh root@157.230.19.242 "mysql -u root -p****** sakila"

On Windows

Backup remote MySQL over SSH on Windows

ssh alex@20.237.171.192 "\`"C:\Program Files\MySQL\MySQL Server 8.2\bin\mysqldump\`" -u root -p****** sakila" > sakila.sql

Restore remote MySQL over SSH on Windows

Get-Content -Path "1.sql" | ssh alex@20.237.171.192 "\`"C:\Program Files\MySQL\MySQL Server 8.2\bin\mysql\`" -u root -p****** db1"

mysqldump through an SSH tunnel

There’s no need to run mysqldump on the remote server and fetch the result back to your own server. Instead, you can create an SSH tunnel that allows you to interact with the database on the remote server as if it were a local server.

ssh -f -N -L 3307:localhost:3306 root@111.122.133.14

Now you can make a backup as if it were a local server

mysqldump -u new_user -ppassword -h 127.0.0.1 -P 3307 --all-databases

How to backup DB via SSH using SqlBak

To create backups of MySQL DBs via SSH using SqlBak, select SSH Tunnel when creating a connection.

In the Windows client, select the appropriate option when creating a connection.

Backup MySQL to a remote server over SSH

You can actually create a backup locally via SSH transfer it to another server. Of course, you can the  command separately.

mysqldump -u root -p****** sakila > sakila.sql 
scp sakila.sql root@157.230.19.242:~/sakila.sql

BUT, you can make this backup without temporary files by transferring it on the fly.

mysqldump -u root -p****** sakila | ssh root@157.230.19.242 "cat > sakila_backup.sql"

As well as restore on the fly

ssh root@157.230.19.242 "cat sakila.sql" | mysql -u root -p****** sakila

The same but with compression, for backup

mysqldump -u root -p****** sakila | gzip | ssh root@157.230.19.242 "cat > sakila.sql.gz"

ssh root@157.230.19.242 "cat sakila.sql.gz" | gunzip | mysql -u root -p****** sakila

How Send a Backup via SSH SqlBak

choosing a destination, FTP, the Protocol SFTP, enter the host, login, password.

There are many an instance where you want to export data from your MySQL database using mysqldump to another destination like a Data Warehouse. You may want to integrate this data with that from other sources and perform a holistic analysis or you may want to format and publish your data in the way you want.

The article helps you to understand the basics of mysqldump, alternative solutions, and real-world applications, which help you to use MySQL effectively.

Steps to Export Database and Tables Using mysqldump

There are three ways in which the mysqldump tool can be used:

1. To Export Tables

In most cases where the intention is a basic backup, full read access privileges should suffice. Next launch a terminal where you will issue the command to backup tables in a database. The command to backup tables is shown below:

mysqldump [options] your_db_name [tbl_name ...]

mysqldump -u your_username -p store orders products > name_of_file.sql

2. To Export Database

The steps to export a database are similar to those of exporting tables with a little change in the command semantics. Once again, make sure you are on the appropriate server and you have valid credentials. The command to export databases is shown below:

mysqldump -u your_username -p --databases db_name1 db_name2 db_name3 > name_of_file.sql

In the command above, the database to be exported is supplied after the –databases option. If multiple databases are to be exported, they are separated by spaces.

3. To Export MySQL Server

To export an entire MySQL server, you can issue the command below from a terminal:

mysqldump -u your_username -p --all-databases

There are some important options that you should be aware of such as the –compatible option, which is used when you want the export to be compatible with other database systems or older MySQL servers.

If you are using PowerShell on Windows, you should use the –result-file option, for example, –result-file=name_of_file.sql to specify the file name so that the output file is in ASCII format so it can load correctly subsequently.

Mysqldump What Does the –quick flag Do?

Copying data from one place to another requires RAM, as it acts as temporary storage or buffer memory for some time. mysqldump also retrieves data and dump it in the table contents row by row. It takes up the entire dataset that needs to be moved in the buffer memory or RAM in a computer.

Exporting large datasets cause problem because RAM cannot load a large amount of data in a single go. To avoid errors and system failures –quick flag helps to read data from tables using a method that doesn’t require RAM to fit large tables in memory

Mysqldump When Using Lock Tables?

Databases needed uptime to support all the necessary activities, handle data requests, and many more. By default, mysqldump command locks all the tables until the backup process is complete but it is not a good option as it will bring the Database offline.

mysqldump locks all the tables of the Database to avoid any changes in data while backing up and for the protection of data integrity. MyISAM tables need locking tables because that doesn’t support transactions.

If you also use MyISAM and InnoDB tables then you can use the –lock-tables command to dump your MyISAM from tables separately.

Mysqldump Command Syntax

Let’s go through the basics of the mysqldump command before we get into how to use it.

mysqldump [options] > file.sql

• options – The options file for mysqldump.
• sql – The backup (dump) file

The MySQL server must be available and operating in order to use the mysqldump command.

How to Generate backup using mysqldump utility?

mysqldump is a command-line program that creates a logical backup of the MySQL database. It generates SQL statements that can be used to rebuild database objects and data. The program can also provide output in XML, delimited text, or CSV.

The only issue that arises while restoring the database is that this command is simple to use. When we create a backup of the MySQL database, it creates a backup file that contains SQL commands that are required to rebuild or restore the database, as I previously said.

When we restore the database, the command now runs all of the SQL statements necessary to build tables and enter data. The restoration process takes a long time to finish if you have a huge database.

The information schema database, performance schema database, and MySQL Cluster ndbinfo database are not dumped by default when using the mysqldump command.

You must specifically supply the database name in the mysqldump command, as well as the —skip-lock-tables option if you want to include the information schema tables.

mysqldump -u [user name] –p [password] [options] [database_name] [tablename] > [dumpfilename.sql]

The Parameters are:

• -u [user name]: This is the username that will be used to connect to the MySQL server. To create a backup with mysqldump, select ‘Select’ to dump tables, ‘Show View’ to dump views, and ‘Trigger’ to dump triggers. If the —single-transaction option is not used, the user must be granted ‘Lock Tables’ access.
• –p [password]: The MySQL user’s valid password [option]: Customize the backup
• [database name] with the following configuration
• option: The name of the database you want to back up
• [name of table]: This is a non-mandatory parameter. If you want to take a backup of specific tables, use the command
• “<” OR “>” to provide the names: This character denotes whether we are creating a database backup or restoring a database. “>” can be used to create a backup and “<” can be used to restore a backup.
• [dumpfilename.sql]: The backup file’s path and name. As previously said, we can generate the backup in XML, delimited text, or SQL format, so we can specify the file extension accordingly.

Generate the backup of a single database

For example, if you wish to create a backup of a single database, use the command below. The command will create a backup of the “sakila” database in the sakila 20200424.sql file, complete with structure and data.

mysqldump -u root -p sakila > C:MySQLBackupsakila_20200424.sql

Let’s examine the backup file to see what’s inside it once it’s been successfully made. Double-click the “sakila 20200424.sql” file in the backup directory.

The backup file, as seen in the image above, contains the numerous T-SQL statements that can be used to recreate the objects.

Download the Whitepaper on Automating Data Integration

Learn the key benefits of automating Data Integration

How to mysqldump Backup Large Database?

In this section, you will learn different methods that you can use to backup large datasets using mysqldump.

Method 1: How to Compress mysqldump Output?

1. One can use the file compression method to compress the database backup into gzip format to reduce the size of data to be backup. You can do the same by running the following command given below:

mysqldump -u root -ppassword wpdb | gzip > wpdb_backup.sql.gz

2. Here, 2 commands are executed together. One is dumping the database name wpdb and another command is to compress the dumped database into gzip format.

Method 2: How to Import the General MySQL Database?

1. Log in to your SQL shell using the command given below.

mysql -u root -p

2. Now, set the network buffer length to a large size as given in the code below.

set global net_buffer_length=1000000;

3. Set the maximum allowed packet size to a large byte number as given in the code below.

set global max_allowed_packet=1000000000;

4. To avoid any delays or errors, disable the foreign key checking by the following command given below.

SET foreign_key_checks = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;

5. Now, import your dump file with the command given below.

source /backup-path/wpdb.sql

6. After this don’t forget to enable the foreign key checking with the given command given below.

SET foreign_key_checks = 1;
SET UNIQUE_CHECKS = 1;
SET AUTOCOMMIT = 1;

Method 3: Separate Databases Into Separate Data Files

1. In this method, one can slit the database backup into separate data files.
2. You can create a new file with a list of all databases using the following command given below.

mysql -u root -ppassword -A --skip-column-names -e"SELECT schema_name FROM
information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql')" >
db_list.txt

3. Now you can create a loop in which all the databases will dump one by one by using mysqldump command given below.

for DB in `cat db_list.txt`
do
mysqldump -u root -ppassword --hex-blob --routines --triggers ${DB} | gzip > ${DB}.sql.gz &
done
wait

How to Use mysqldump Without Password?

1. You have to create a file in your home directory with SQL credentials. Use the given command to create a file.

nano ~/mysql.txt

2. Now add your MySQL credentials as given below.

[mysqldump]
user=root
password=password

3. Save and close the file and every time you can provide a file in place of credentials like in the code given below.

mysqldump --defaults-file=~/mysql.txt wpdb > wpdb_backup.sql

Before wrapping up, let’s cover some basics as well.

What is mysqldump?

The MySQL database engine has a client utility tool called mysqldump that can be used to perform backups of a MySQL database by exporting a “.sql” file type that contains SQL statements that can be executed to recreate the original database.

However, exporting data is not a one-time-only task, and doing it manually can be tedious.

How Do We Use mysqldump?

mysqldump is easy to use and learn. A few basic syntaxes of mysqldump commands are listed below:

mysqldump -u USERNAME -p PASSWORD DBNAME > DBBACKUP.sql

mysqldump -u USERNAME -p PASSWORD --databases DB1 DB2 DB3.. >DBBACKUP.sql

mysqldump -u USERNAME -p PASSWORD --all-databases > ALLDBBACKUP.sql

What are the Benefits of Backing Up Data?

• The importance of having a pristine snapshot of the copy of your data at different points in time cannot be overemphasized for companies or organizations that have systems in production.
• A database installation that does not have a robust backup strategy is a disaster waiting to happen as any number of things could go wrong that could lead to corrupt data or permanent data loss. To avoid this doomsday scenario, organizations usually have a well-laid-down procedure to carry out a periodic backup of data.
• One of the main benefits of regularly backing up data is the ability to restore data operations in the case of a catastrophic failure of the system. Another benefit is data versioning, which means that specific versions of your data are stored when changes occur so that you can go back in time to restore older versions in order to get the state of your ground truths at that point in time.
• The practice of backing up data also means that in the event of a migration to a new server or development environment, data can be transferred without any fear of loss. This brings us to the crux of this article, where you will be introduced to mysqldump and you will use it to perform logical backups of your data.

FAQ on mysqldump

2. How to convert MySQL file to SQL file?
Since MySQL dump files (*.sql) are already SQL files, there is generally no need to convert them to a different SQL format.

5. How to output SQL query to file?
To output an SQL query toa file, you need to use INTO OUTFILE at the end of the SELECT query; make sure to specify the full path and filename for where you want to save the output.

Conclusion

Through this article, you have been able to understand the benefits of performing regular backups of data. You have also been introduced to the mysqldump tool and you used it to MySQL export tables, databases, and an entire MySQL server.

Although this is unarguably a good start as the mysqldump tool is flexible and easy to use, it is not meant to be a fast and scalable backup solution.

This is because while backup may take a reasonable time, it typically takes a longer period to restore data, especially in cases where the data size is massive as is often the case with enterprises. To fill this void, MySQL provides MySQL Enterprise Backup which is a paid solution.

Ofem Eteng is a seasoned technical content writer with over 12 years of experience. He has held pivotal roles such as System Analyst (DevOps) at Dagbs Nigeria Limited and Full-Stack Developer at Pedoquasphere International Limited. He specializes in data science, data analytics and cutting-edge technologies, making him an expert in the data industry.

is a high-performance SQL proxy that runs as a daemon watched by a monitoring process. The process monitors the daemon and restarts it in case of a crash to minimize downtime.

The daemon accepts incoming traffic from MySQL clients and forwards it to backend MySQL servers.

In this blog, we are discussing various methods to backup and restore ProxySQL. Below are backup methods to perform a backup restore of the ProxySQL server.

• Config file backup

Here will consider ProxySQL configured for async replication with Primary and Replica as shown below in topology.

Config file backup:

ProxySQL backup can be performed by the config file. This method is beneficial in creating multiple instances.

Config backup file can be created by using the below commands and it can be used to start new ProxySQL instances.

Mysqldump:

mysqldump must be run with –no-tablespaces , –skip-triggers, –skip-add-locks, and –skip-column-statistics

If mysqldump version 8.0 is used:

• mysqldump also needs the –skip-column-statistics option
• It only works from ProxySQL version 2.0.8 and above

Physical snapshot:

• Create a copy of the data directory

Conclusion

These methods provide solutions for ProxySQL backups and restores, which play a pivotal role in safeguarding the integrity of your data and providing defense against various disasters, hardware malfunctions, data loss, and corruption. Without reliable backups, the consequences of data loss can be severe, as businesses risk operational disruptions, financial losses, damage to their reputation, and could even result in compliance violations. 

If you are interested in more hands-on learning of ProxySQL, Percona’s professional training department offers a one-day, hands-on intensive tutorial covering all things ProxySQL. In the tutorial, we cover the basics of installation and configuration, read-write splitting, async/GR/PXC-based high availability, query firewalling, query whitelisting, results caching, and much, much more. Reach out to us today to schedule your instructor-led class!

How to Install ProxySQL From the Percona Repository

Install Percona Server for MySQL

How to set up a replica for replication in 6 simple steps with Percona XtraBackup

Percona MySQL training pdf

Похожее:

Освойте искусство сравнения баз данных с помощью power shell и db forge studio для sql server Консольный клиент my sql shell Cmd set global Ssh-подключения к windows-серверу из linux mac os с использованием power shell