Backing up MySQL database and linux partition in BananaPi

BACK-UP DATABASE

Just Some info on MySQL backup:

For the methods of backing mysql database in order to install or upgrade the operating system of the linux distribution “ubuntu server” OS. There many type of mysql backup type[1]:

• Logical Backup : outputs a database structure in a .sql file by generating the CREATE or INSERT statement. This file can be restored to the database with the help of mysqldump utility. This type of backup only works with data without indexes, thus has a small size. However, its recovery option is slower compared with the alternative, because it should execute all statements one by one.

• Physical Backup : copies database files in the same format in which they are stored on the disk. It is faster than the logical backup but can be restored only to the MySQL server from the same database engine.

• Consistent backup : copies the files at the exact moment of time - only after the server stops running or is blocked. 

It the server backup process was interrupted, backup can be divided into following types:

• Cold backup: blocks access to data during the backup and does not allow making any changes to data. It is simple, fast, and does not impact performance.

• Hot backup: copies files while the database is running. Users can read and manipulate data.

• Warm backup: makes a database copy while it is running. Users can read but cannot write data during the backup. 

Whether data should fully or partially copied, backups can be classified into:

• Full backup: copies all the data from the database. It can be logical or physical. The full backup can be restored on another server.

• Differential backup: copies all the changes that were made since the latest full backup. The differential backup can be restored only after the full backup was restored.

• Incremental backup: copies all the data changes made since the previous backup (either full or differential).

We are backing up the mySQl database with the mysqldump command. Let see what it does:

• It is a logical database backup. Meaning it will be slow and the output is a .sql file

• This file can be used to create tables, objects, and/or insert data that were copied from the database. 

• It could output CSV or XML file formats. 

• To restore the database, the utility executes all SQL statements to recreate the tables and populated them with data that in turn requires a lot of time (SLOW).

Basic Syntax : 

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

It is seems like we have to know the port of mySQL database which usually is 3306. We can connect to the remote and save the backup file on a USB drive. Of course we need to have MySQL installed on our PC. However, we will perform the backup on the remote server directly to the USB card.

MySQL backup:

In this section we will start by identifying the location of the USB. Before we can access the USB drive, we need to mount the device. Most linux automatically mount to the system under the /media directory

sudo fdisk -l

Or 

lsusb

The command above used to display all the connected and new devices on the usb buses. You can check the device name mounted to the /media folder. By change to that /media folder directory, we can see or copy file to that directory. Example:

cd /media     sudo cp filename /media

Verify the media size by using the command below:

de -h /media

Backup command for all-database backup:

mysqldump -u root --p --all-databases > /media/mySQLbackup2023.sql

Linux OS Cloning to image:

After backing up the database, I would like to perform a OS cloning on the database just in case the data was corrupted or backup failed [3]. Using dd the native linux disk cloning tool, we can clone the linux partition to an image [4]. The line use to make the Disk image is as follow: 

dd if=/dev/sdX of=path/to/your-backup.img

Or we could compress your backup with the following code:

dd if=/dev/sdX | gzip -c > path/to/your-backup.img.gz

We can restore the image file using the code below: 

dd if=path/to/your-backup.img of=/dev/sdX

Or we could restore the compressed image file:

gunzip -c /path/to/your-backup.img.gz | dd of=/dev/sdX

With the code above, I want to save the partition linux in to a USB drive as well with the name backupOSUbuntuSERVER.img.gz. Of course, we are making sure the USB device is mounted and we know the partition we are cloning.

dd if=/dev/sdX | gzip -c > /media/backup/myUbuntubackup2023.img.gz

That is it. We can now safely formate the SD card and install new OS into the device.

References

[1] Blog Post of the Mysql backup : [https://blog.devart.com/mysql-backup-tutorial.html] 

[2] Copy file to USB drive on LINUX cmd : [How to Copy Files to USB Using Terminal (linuxopsys.com)]

[3] Cloning linux Disk : [How to Clone Your Linux Hard Drive: 5 Methods (makeuseof.com)]

[4] clone linux os to image : [How to Easily Clone and Restore a Linux Disk Image With dd (makeuseof.com)]