Backup mysql database command line linux

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

Источник

Как создать резервную копию и восстановить базы данных MySQL с помощью Mysqldump

В этом руководстве объясняется, как создавать резервные копии и восстанавливать базы данных MySQL или MariaDB из командной строки с помощью утилиты mysqldump.

Файлы резервных копий, созданные утилитой mysqldump, в основном представляют собой набор операторов SQL, которые можно использовать для воссоздания исходной базы данных. Команда mysqldump также может создавать файлы в форматах CSV и XML.

Вы также можете использовать утилиту mysqldump для переноса вашей базы данных MySQL на другой сервер MySQL.

Если вы не сделаете резервную копию своих баз данных, программная ошибка или отказ жесткого диска могут иметь катастрофические последствия. Чтобы сэкономить время и нервы, настоятельно рекомендуется принять меры предосторожности и регулярно создавать резервные копии баз данных MySQL.

Синтаксис команды Mysqldump

Прежде чем перейти к использованию команды mysqldump, давайте начнем с обзора основного синтаксиса.

Выражения утилиты mysqldump имеют следующую форму:

• options — Параметры mysqldump
• file.sql — файл дампа (резервной копии)

Чтобы использовать команду mysqldump, сервер MySQL должен быть доступен и запущен.

Резервное копирование единой базы данных MySQL

Наиболее распространенный вариант использования инструмента mysqldump — резервное копирование одной базы данных.

Например, чтобы создать резервную копию базы database_name с именем database_name используя пользовательский root и сохранить ее в файл с именем database_name.sql вы должны выполнить следующую команду:

Вам будет предложено ввести пароль root. После успешной аутентификации начнется процесс дампа. В зависимости от размера базы данных процесс может занять некоторое время.

Если вы вошли в систему как тот же пользователь, которого используете для экспорта, и этот пользователь не требует пароля, вы можете опустить параметры -u и -p :

Резервное копирование нескольких баз данных MySQL

Для резервного копирования нескольких баз данных MySQL с помощью одной команды вам нужно использовать параметр —database за которым следует список баз данных, которые вы хотите —database . Каждое имя базы данных должно быть разделено пробелом.

Приведенная выше команда создаст файл дампа, содержащий обе базы данных.

Резервное копирование всех баз данных MySQL

Используйте параметр —all-databases для резервного копирования всех баз данных MySQL:

Как и в предыдущем примере, приведенная выше команда создаст один файл дампа, содержащий все базы данных.

Резервное копирование всех баз данных MySQL в отдельные файлы

Утилита mysqldump не предоставляет возможности резервного копирования всех баз данных в отдельные файлы, но мы легко достигаем этого с помощью простого цикла FOR bash :

Приведенная выше команда создаст отдельный файл дампа для каждой базы данных, используя имя базы данных в качестве имени файла.

Создание сжатой резервной копии базы данных MySQL

Если размер базы данных очень велик, рекомендуется сжать вывод. Для этого просто направьте вывод в утилиту gzip и перенаправьте его в файл, как показано ниже:

Создать резервную копию с отметкой времени

Если вы хотите хранить более одной резервной копии в одном месте, вы можете добавить текущую дату к имени файла резервной копии:

Приведенная выше команда создаст файл в следующем формате database_name-20180617.sql

Восстановление дампа MySQL

Вы можете восстановить дамп MySQL с помощью инструмента mysql . Общий синтаксис команды следующий:

В большинстве случаев вам потребуется создать базу данных для импорта. Если база данных уже существует, сначала ее нужно удалить.

В следующем примере первая команда создаст базу данных с именем database_name а затем импортирует в нее дамп database_name.sql :

Восстановление единой базы данных MySQL из полного дампа MySQL

Если вы создали резервную копию всех своих баз данных с помощью параметра -all-databases и хотите восстановить одну базу данных из файла резервной копии, содержащего несколько баз данных, используйте параметр —one-database как показано ниже:

Экспорт и импорт базы данных MySQL одной командой

Вместо создания файла дампа из одной базы данных и последующего импорта резервной копии в другую базу данных MySQL вы можете использовать следующий однострочный файл:

Приведенная выше команда передаст вывод клиенту mysql на удаленном хосте и импортирует его в базу данных с именем remote_database_name . Перед запуском команды убедитесь, что база данных уже существует на удаленном сервере.

Автоматизируйте резервное копирование с помощью Cron

Автоматизировать процесс резервного копирования баз данных так же просто, как создать задание cron, которое будет запускать команду mysqldump в указанное время.

Чтобы настроить автоматическое резервное копирование базы данных MySQL с помощью cronjob, выполните следующие действия:

Создайте файл с именем .my.cnf в домашнем каталоге пользователя:

Скопируйте и вставьте следующий текст в файл .my.cnf.

Не забудьте заменить dbuser и dbpasswd на пользователя базы данных и пароль пользователя.

Ограничьте права доступа к файлу учетных данных, чтобы только ваш пользователь имел к нему доступ:

Создайте каталог для хранения резервных копий:

Откройте ваш пользовательский файл crontab:

Добавьте следующее задание cron, которое будет создавать резервную копию базы данных с именем mydb каждый день в 3 часа ночи:

Не забудьте заменить username своим фактическим именем пользователя. Мы также избегаем знаков процента ( % ), потому что они имеют особое значение в crontab.

Вы также можете создать другое задание cron для удаления любых резервных копий старше 30 дней:

Конечно, вам нужно настроить команду в соответствии с местоположением резервной копии и именами файлов. Чтобы узнать больше о команде find, ознакомьтесь с нашим руководством по поиску файлов в Linux с помощью командной строки .

Выводы

Это руководство охватывает только основы, но оно должно стать хорошим началом для всех, кто хочет научиться создавать и восстанавливать базы данных MySQL из командной строки с помощью утилиты mysqldump.

Если вы хотите узнать больше о работе с MySQL из командной строки, ознакомьтесь с нашим руководством по управлению учетными записями пользователей и базами данных MySQL .

Вы также можете проверить руководство о том, как сбросить пароль root MySQL, если вы его забыли.

Если у вас есть какие-либо вопросы или отзывы, не стесняйтесь оставлять комментарии.

Источник

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.

Источник