Backup all mysql databases linux

MySQL Backup and Restore Commands for Database Administration

This article shows you several practical examples on how to perform various backup operations of MySQL databases using mysqldump command and also we will see how to restore them with the help of mysql and mysqlimport command in Linux.

mysqldump is a command-line client program, it is used to dump local or remote MySQL database or collection of databases for backup into a single flat file.

How to Backup and Restore MySQL Database

We assume that you already have MySQL installed on Linux system with administrative privileges and we assume that you already have a small amount of knowledge on MySQL. If you don’t have MySQL installed or don’t have any exposure to MySQL then read our articles below.

How to Backup MySQL Database?

To take a backup of MySQL database or databases, the database must exist in the database server and you must have access to it. The format of the command would be.

The parameters of the said command as follows.

1. [username] : A valid MySQL username.
2. [password] : A valid MySQL password for the user.
3. [database_name] : A valid Database name you want to take backup.
4. [dump_file.sql] : The name of backup dump file you want to generate.

How to Backup a Single MySQL Database?

To take a backup of single database, use the command as follows. The command will dump database [rsyslog] structure with data on to a single dump file called rsyslog.sql.

How to Backup Multiple MySQL Databases?

If you want to take backup of multiple databases, run the following command. The following example command takes a backup of databases [rsyslog, syslog] structure and data in to a single file called rsyslog_syslog.sql.

How to Backup All MySQL Databases?

If you want to take backup of all databases, then use the following command with option –all-database. The following command takes the backup of all databases with their structure and data into a file called all-databases.sql.

How to Backup MySQL Database Structure Only?

If you only want the backup of database structure without data, then use the option –no-data in the command. The below command exports database [rsyslog] Structure into a file rsyslog_structure.sql.

How to Backup MySQL Database Data Only?

To backup database Data only without structure, then use the option –no-create-info with the command. This command takes the database [rsyslog] Data into a file rsyslog_data.sql.

How to Backup Single Table of Database?

With the below command you can take backup of single table or certain tables of your database. For example, the following command only take backup of wp_posts table from the database wordpress.

How to Backup Multiple Tables of Database?

If you want to take backup of multiple or certain tables from the database, then separate each table with space.

How to Backup Remote MySQL Database

The below command takes the backup of remote server [172.16.25.126] database [gallery] into a local server.

How to Restore MySQL Database?

In the above tutorial we have seen the how to take the backup of databases, tables, structures and data only, now we will see how to restore them using following format.

How to Restore Single MySQL Database

To restore a database, you must create an empty database on the target machine and restore the database using msyql command. For example the following command will restore the rsyslog.sql file to the rsyslog database.

If you want to restore a database that already exist on targeted machine, then you will need to use the mysqlimport command.

In the same way you can also restore database tables, structures and data. If you liked this article, then do share it with your friends.

If You Appreciate What We Do Here On TecMint, You Should Consider:

TecMint is the fastest growing and most trusted community site for any kind of Linux Articles, Guides and Books on the web. Millions of people visit TecMint! to search or browse the thousands of published articles available FREELY to all.

If you like what you are reading, please consider buying us a coffee ( or 2 ) as a token of appreciation.

We are thankful for your never ending support.

Источник

Backup and Restore MySQL Databases With Mysqldump In Linux

One of the most important task of a database administrator is backing up and restoring databases. MySQL is a popular database server and provides tool named mysqldump for backup and restore operations. We will look different usage types of mysqldump in this tutorial.

Syntax

Connect Remote Database Server

By default mysqldump will try to connect local MySQL server database. Remote databases server can be specified with -h option as IP address or domain name. In this example we will try to connect host named poftut1 .

Backup Single Database

Mysqldump have a lot of usage possibilities but the most popular one is backing up single database. This will only backup single specified database. We will specify user name and password with database name. Username will be specified with -u options and password specified with -p option. We will specify database name after these information without an option.

Backup Single Database

By default backup output is written to the standard output. But in this example we have redirected the output to the file named mysql.sql . the backup file is an sql file which holds sql schemas and data.

Backup Multiple Databases

We can backup multiple databases just adding more databases to the end of the mysqldump command. In this example we will backup databases mysql , sys . We will use —databases option.

Backup Multiple Databases

Backup All Databases

In previous example we have specified multiple databases for backup. If we need to backup all databases in the database server specifying them one by one is error prone work. We can use —all-databases command to backup all databases without specifying them one by one.

Backup All Databases

Backup Single Table

Sometimes our application databases uses only single table. All transactions are done in a single database. In this situation we may need backup single table. We can specify the table name after the database name which contains the table. In this example we will backup table named general_log which resides in database mysql

Backup Single Table

Backup To Remote

Backup on the local directory is practical but some times we may need to backup the databases to the remote server. There are different ways to backup remote directory like scp, file share etc. We prefer a pratical one where we will use ssh command to redirect to backup content to the remote server. We provide password of the database server in command line. In this example we will copy database named mysql to the remote system or server named ubu2 with shh by using cat > big.sql command.

Check remote backup file can be done with following command.

Backup To Remote

Backup Only Database Schema

The backup of databases, tables will take copy of the schema of databases, tables and currently stored data on these tables and databases. If the data is test data which is not important for us or if we want to create clone of the current database and tables without data we can omit the data. We wil use —no-data or -d option to specify this.

Backup Only Database Schema

Compress Backup

Creating backups take time and storage. Especially if we have more than one backup there will be need a lot of storage. As we know the backup files are sql format by default. Sql is a text file and have a high compression rate. We can write backup as compressed format which will make storage usage very effective. We will use gzip to enable msqldump compression.

Compress Backup

Compress Server Client Transmission

Another useful performance option is compression transmission between mysqldump client and MySQL server. As there may be a bulk of data while transmitting them through network compressing is a good habit for network performance . We will use -C or —compress options to enable compression.

Force Backup Operation

While taking backup some times errors or warnings occurs. These warnings and errors can interrupt the backup process b$ mysql -h poftut1 -u root -p -f option to force and omit the errors and warnings.

Restore and Import Database

We will use mysql tool to restore database. We assume that backup is in Sql format. We will redirect the backup file to the mysql command. While using mysql command we need to specify the username with -u and password with -p options.

Restore Remote Database

In order to restore database to the remote database server we will specify the remote databases server IP address or hostname. In order to specify IP address or hostname we will use -h option. In this example we use remote database server named poftut1

Источник

How To Back Up MySQL Databases with Linux Command Line and Automate with Cron

It’s important to make frequent automated backups of your MySQL databases should you ever accidentally alter data or suffer a hack. In this guide we will learn how to use mysqldump to export databases and use crontab to automate the entire process.

1. Prepare MySQL Backup Folder

There is no particular recommended folder to back up to in Linux so you can choose this yourself. In this guide, we are saving backups to /var/www_backups/ . Ideally you would store these on an offsite server, but in this guide we will focus on creating backups locally.

Begin by creating your backup folder.

If you’re not currently logged in to Linux as root, you should change the owner of the backup folder otherwise your mysqldump tests will fail with a permissions error. $(whoami) will fetch the currently logged in user. You can always change the owner back to root once you’ve finish testing commands.

2. mysqldump Client Utility

The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server.

Here’s the syntax of a typical mysqldump command.

• -u [username] = the MySQL user that has the necessary privileges to perform database dumps.
• –p[password] = the user password. Note there is no space between -p and the password.
• [database_name] = the database name
• > = output dump to
• /path/to/[database_name].sql – path and name of dump file. [database_name] can be whatever you want.

Important Note On –p[password]

For the purposes of this guide, we will be showing some mysqldump examples using the –p[password] option. However, you are discouraged from using this as the entire mysqldump command (including password) can be viewed by any other user on the system with a simple ps ax command while the dump is running.

You should instead store your password in

/.my.cnf and then you can omit the –p[password] option from your commands. If you are running the command in cron, use the option —defaults-extra-file=/path/to/.my.cnf so mysqldump knows where to fetch the password. See more in Step 8 at the end of this guide.

3. MySQL Back Up Examples

Back up All Databases

We can use the —all-databases option to dump all MySQL databases. In the following example, we are dumping all databases to /var/www_backups/ using the root user. The dump file is called all-databases.sql but you can change this to whatever you want. Usually you will need to use the root MySQL account to gain access to all databases.

Back up a Single Database

To back up a single MySQL database, simply replace [database_name] with your own. The dump file in this example is called [database_name].sql but you can change this to whatever you want. We are using the root user in this example, though you could use any user that has access to that particular database.

Back up Multiple Databases

To back up more than one MySQL database, use the —databases option followed by the database names, separated by a space. The dump file in this example is called [database_names].sql but you can change it to whatever you want.

Back up a Single Table

To back up a single table from a MySQL database, simply enter the table name after the database name. The dump file in this example is called [table_name].sql but you can change it to whatever you want.

Back up Multiple Tables

To back up multiple tables from a MySQL database, simply enter the table names after the database name, separated by a space. The dump file in this example is called [table_names].sql but you can change this to whatever you want.

Back up a Remote Database

To back up a remote MySQL database, simply use the -h option followed by the remote IP address or host name.

Back up a Database with Compression

To back up a MySQL database with compression, we can ‘pipe’ the output to gzip using | gzip -c > .

You can also add the verbose option to see compression progress, useful for very large tasks. Example gzip -c —verbose >

4. Restore MySQL

Use mysql to restore .sql files to the database. Here’s the syntax of a typical MySQL restore.

• -u [username] = the MySQL user, usually root , that has the necessary privileges to perform database imports.
• –p[password] = the user password. Note there is no space between -p and the password.
• [database_name] = the database name
• = import dump to
• /path/to/[database_name].sql – path and name of dump

5. Automated MySQL Backups

cron is a service in Linux used to schedule automated commands. These are stored in a cron table called crontab .

To open crontab , run:

If you are prompted to choose an editor, choose nano.

Scroll to the bottom of the file and add your cron schedule and mysqldump command. In the example below, we are backing up a database daily with gzip compression. Gzip will reduce the size of large .sql files suitable for storing backups.

00 01 * * * will run the command at 1AM every 24 hours and overwrite any current .sql.gz files. As a test, you can change this to run in the next 3 minutes. If your time now is 16:30, enter 33 16 * * * for it to run at 16:33. (For more information on how to configure cron schedules, see Step 6 below).

Save and close crontab to initiate cron. (if using nano , press CTRL + X , press Y and then press ENTER )

Wait for your test cron to run and list the backup folder until you see your .sql.gz file.

If your .sql.gz file doesn’t appear after a while, make sure your command and crontab time are correct. You can also check the cron log with:

Once you’ve verified the cron is running, you can change the schedule back to your preferred time.

6. MySQL Backup Frequency and Retention

In the previous step, we learned how to configure cron to run every 24 hours and overwrite our sql.gz file. However, you may want to retain multiple backups spread out over a week or longer.

We will now learn how to configure schedules in crontab and include a timestamp in the filename to allow for more organized archiving of multiple files.

Crontab schedule

The default crontab entry begins with 5 stars followed by a command, which will run once a minute. You can change these to suit your exact schedule by minute, hour, day of month, month, and day of week.

Timestamps in Filename

To better manage multiple sql.gz files, it’s recommend that you append a timestamp to the filename. We can do this using the date variable in Linux.

Let’s test the date variable using echo . To echo the current date in the format YYYY-MM-DD :

To get the number of the day of the week (0 to 6, where 0 is Sunday and 6 is Saturday)

Above we can see the day is 2 for Tuesday.

You can also echo the name of day of the week:

For a full list of control characters supported by the date command, see: Linux Shell Script Date Format

We can use these date control characters in crontab to give our sql.gz files unique filenames.

In the next step we will show some examples and backup scenarios using crontab schedules and timestamps in filenames.

7. MySQL Automated Backup Examples

Just a Daily MySQL Backup

If you just want a daily MySQL backup of a database, use the crontab below to create a backup at 1am every morning. The sql.gz file will be overwritten daily.

7-day rolling MySQL backup

In this scenario, we will run a MySQL database backup at 1am and keep a copy for each day of the week. This is the same backup we have for this website.

This will initiate a backup every 1am. This part `date +\%a` will add the day of the week to the filename (Mon, Tue, Wed, etc). This is so you have a sql.gz file for each of the last 7 days and don’t have to worry about purging old copies. Note that in order to use the % symbol in crontab , it must be escaped with \ , otherwise the cron will fail.

Here is a list of our folder showing the DevAnswers’ WordPress database backup for each day of the week. Older backups are overwritten automatically.

12-month rolling MySQL backup

Another cron we have configured for this website is a monthly MySQL database backup going back one year.

This part `date +\%b` will add the month name (e.g ‘Jan’) in the sql.gz filename so you have a backup every month of the year.

Here is a list of our backup folder showing the monthly backup. As the years roll on, the older sql.gz files will simply be overwritten.

8. Storing the MySQL Password in my.cnf

You are discouraged from using the –p[password] option as the entire mysqldump command (including password) can be viewed by any other user on the system with a simple ps ax command while the dump is running.

You should instead store your password in

/.my.cnf and if using cron use the option —defaults-extra-file=/path/to/.my.cnf .

8.1. Edit my.cnf

The my.cnf file is hidden in your home directory, usually /home/username/.my.cnf

Open my.cnf in nano. (Note that

denotes the home directory for the currently logged in user).

Enter in the following two lines replacing YOUR_PASSWORD_HERE with your own.

Save and exit (press CTRL + X , press Y and then press ENTER )

You should also set permissions for my.cnf to 600 .

You can now run mysqldump commands without the –p[password] option.

8.2. Crontab and –defaults-extra-file

Because cron runs as root, you must use the —defaults-extra-file option so mysqldump knows where to fetch your password. Just replace /home/username/.my.cnf with the path to your own my.cnf file. This crontab example runs at 1AM every morning and compresses the dump to a gzip.

9. Offsite Backups

Now that you have your MySQL backups stored locally in /var/www_backups , you should consider an offsite backup. For example, you could configure SFTP access to the /var/www_backups directory and then run an SFTP cron job on another server to pull these backups nightly.

What Next?

Now that you have your MySQL database backed up safely, you might also want to back up your web document root.

• Automatically Back Up Your Web Server Doc Root with Tar and Cron

Let me know if this helped. Follow me on Twitter, Facebook and YouTube, or 🍊 buy me a smoothie.

p.s. I increased my AdSense revenue by 200% using AI 🤖. Read my Ezoic review to find out how.

13 replies

Leave a reply

Why am I getting the small backup file? After automating MySQL Backup, I am only getting 20B of the backup database while original Database is bigger than that.

Because it’s compressed with gzip and the file will be a lot smaller.

Thank you! But when I Unzipped it I got empty file(0KB). Any advice how I can handle this?

Note that for step 8.2, it is essential to put the –defaults-extra-file option before any of the other options.

Very well explained. Thank you

Interesting indeed. Do you have and example for the offsite sftp part perhaps?

It’s on the list! 🙂

Very nice! it answers all my question. Perfect!

Note that typing password in command or placing in cron file is not recommended for security reasons, as any unprivileged user can see command by ‘ps ax’ or in command history. with mysqldump you may place the password in

Thanks for that. The article has since been updated.

how to stop warning messages about using passwords on the command line?

Источник