"how to backup big database in ubuntu" Code Answer

Step 1:
Using MySQL dump command
Ubuntu comes with a nice command called ‘mysqldump’.  We are going to use the command as shown below to backup our database. Replace the username, database_name and backup_file_name wit the correct values. Also, enter your database password when prompted to do so:

$ mysqldump -u username -p database_name > backup_file_name.sql
For example,  to backup a database named ‘wp_database’ to a file name ‘wp_database_bk.sql’ we are going to use the command below. Please note wp_user is the username used to log in on the wp_database:

$ mysqldump -u wp_user -p wp_database > wp_database_bk.sql
The command above will create a mysql backup under the path “/home//wp_database_bk.sql”

Step 2:
Restoring MySQL database
You can restore any MySQL database using the command below:

$ mysql -u username -p database_name < backup_name.sql
For instance, to restore our wp_database_bk.sql to a database named wp_database_1, we run the command below:

$ mysql -u wp_user -p wp_database_1 < wp_database_bk.sql
Step 3:
Automating the backup process
Creating manual backups in a production environment can be tedious. Luckily, there is a Utility called automysqlbackup that we can download from the Ubuntu’s repository. The package uses cron jobs to schedule backups at different intervals without manual intervention.

To download the utility, run the command below:

$ sudo apt-get install automysqlbackup
Then to take on-demand backups, run the command below:

$ sudo automysqlbackup
You can list the content of the daily backup folder by running the command below:

$ sudo ls -a /var/lib/automysqlbackup/daily
You can customize automysqlbackup utility by editing its configuration file located at “/etc/default/automysqlbackup” by running the command below:

$ sudo nano /etc/default/automysqlbackup
The utility organizes the MySQL backup files pretty well under the “/var/lib/automysqlbackup” directory.

freestar
The parameters of the said command are as follows.

[username] : A valid MySQL username.
[password] : A valid MySQL password for the user.
[database_name] : A valid Database name you want to take backup.
[dump_file.sql]: The name of the backup dump file you want to generate.
How to Backup a Single MySQL Database?
To take a backup of a single database, use the command as follows. The command will dump the database [rsyslog] structure with data onto a single dump file called rsyslog.sql.

# mysqldump -u root -ptecmint rsyslog > 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 into a single file called rsyslog_syslog.sql.

# mysqldump -u root -ptecmint --databases rsyslog syslog > rsyslog_syslog.sql
How to Backup All MySQL Databases?
If you want to take a backup of all databases, then use the following command with the option –all-database. The following command takes the backup of all databases with their structure and data into a file called all-databases.sql.

# mysqldump -u root -ptecmint --all-databases > all-databases.sql
How to Backup MySQL Database Structure Only?
If you only want the backup of the 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.

# mysqldump -u root -ptecmint -–no-data rsyslog > rsyslog_structure.sql
How to Backup MySQL Database Data Only?
To backup database data without structure, use the option –no-create-info with the command. This command takes the database [rsyslog] Data into a file rsyslog_data.sql.

# mysqldump -u root -ptecmint --no-create-db --no-create-info rsyslog > rsyslog_data.sql
How to Backup a Single Table of Database?
With the below command you can take a backup of a single table or specific tables of your database. For example, the following command only takes a backup of the wp_posts table from the database wordpress.

# mysqldump -u root -ptecmint wordpress wp_posts > wordpress_posts.sql
How to Backup Multiple Tables of Database?
If you want to take a backup of multiple or certain tables from the database, then separate each table with space.

# mysqldump -u root -ptecmint wordpress wp_posts wp_comments > wordpress_posts_comments.sql
How to Backup Remote MySQL Database
The below command takes the backup of the remote server [172.16.25.126] database [gallery] into a local server.

# mysqldump -h 172.16.25.126 -u root -ptecmint gallery > gallery.sql