To automatically backup a MySQL database:
The necessary conditions for any form of backup is to find the ideal time of day to complete the task without interfering with the running systems or interfering with users. In this note, you must take into account the size of the database along with the disk I / O speed - this becomes exponentially more important as the database grows (another factor that comes to mind is the number of disks, an alternative disk in which the database is not stored, increase the speed due to the fact that the heads do not perform both reading and writing). To preserve this readable information, I’m going to assume that the database has a size of (100 MB) and the environment works - it works from 9 am to 3 pm without real stress or other working systems outside working hours.
The first step is to log in to the local machine with root privileges. Once in the root shell, the MySQL user will be created with read-only privileges. To do this, enter the MySQL shell using the command:
mysql -uroot -ppassword
Then, the user needs to create read-only privileges in the database to be copied. In this case, a specific database should not be assigned to the user if the script or process will be used later. To create a user with full read privileges, enter these commands in the MySQL shell:
grant SELECT on *.* TO backupdbuser@localhost IDENTIFIED BY ' backuppassword'; FLUSH PRIVILEGES;
With the MySQL user created, it is safe to exit the MySQL shell and return to the root shell using exit. From here we need to create a script that we want to run with our backup commands, this is easy to do with BASH. This script can be stored anywhere, since we will use the cron task to run the script at night, for this example we will place the script in the new directory that we create, called "backupscripts". "To create this directory, use this command in the root shell:
mkdir /backupscripts
We also need to create a directory to store our backups locally. We will call this directory "backuplogs". Run this command in the root shell to create the directory:
mkdir /backuplogs
The next step is to log into your remote computer with root credentials and create a “backup user” using the command:
useradd -c "backup user" -p backuppassword backupuser
Create a directory for your backups:
mkdir /backuplogs/
Before logging out, take the IP address of the remote host for future use with the command:
ifconfig -a
eth0 is the standard interface for a wired network connection. Pay attention to this IP_ADDR.
Finally, exit the remote server and return to the original host.
Then we will create a file that is our script on our local machine, and not on the remote computer. We will use VIM (do not hold it against me if you are a fan of nano or emacs, but I will not list how to use VIM to edit the file here) first create the file and use mysqldump, database backup. We will also use scp to compress the file for storage after creating the database. Read the file in STDOUT for instructions. Finally, check files older than 7 days. Delete them. To do this, your script will look like this:
vim /backupscripts/mysqldbbackup.sh
When using this script, we will need to set ssh keys so that we do not ask for a password every time our script is run. We will do this using SSH-keygen and the command:
ssh-keygen -t rsa
Enter the password in the invitation - this creates your private key. Do not report this.
The file you need to provide is your public key, it is saved in the current_home / .ssh / id_rsa.pub file. The next step is to transfer this public key to your remote host. To get the key, use the command:
cat current_home/.ssh/id_rsa.pub
copy the line to a file. Then ssh to your remote server using the command:
ssh backupuser@remotehostip
Enter your password, and then edit the /.ssh/authorized_keys file. Paste the line obtained from the id_rsa.pub file into the authorized_keys file. Write the changes to the file using the editor, and then exit the editor. Exit your remote server and verify that the RSA keys worked, trying to log in to the remote server again using the previous ssh command. If the password is not set, it works correctly. Log out of the remote server again.
The last thing we need to do is create a cron job to run it every night after users log out. Using crontab, we will edit the current user file (root) to avoid all resolution problems. * Note. This can have serious consequences if there are errors in your scripts, including data deletion, security vulnerabilities, etc. - double-check all your work and make sure you trust your own system, if this is not possible, then the alternative user and permissions should be installed on the current server *. To edit the current crontab, we will issue the command:
crontab -e
In the crontab editor (it will open in the default text editor), we will run our script every night at 12:30. Enter a new line in the editor:
30 0 * * * bash / backupscripts / mysqldbbackup.sh
Save this file and exit the editor. For your cronjob to work correctly, we need to restart the crond service. To do this, run the command:
/etc/init.d/crond restart