Cara menggunakan mysql backup to cloud

WEB DEVELOPMENT:

A complete solution with step-by-step instructions

Summary:

The main benefit of reading this article is that you will learn how to use mysqldump to backup and restore your database. This includes performing a one-time backup, creating a remote backup service, transferring the backup, and restoring your database from the backup. This will protect your database in the event of hardware or software failure, data corruption, and human-caused events, such as malicious attacks or deletion of data. However, this cannot protect your database from breaches that are caused by improper access privileges, weak passwords, and unencrypted data.

We will use Linode as our cloud service provider because their products are easy to use, affordable, scalable, and their customer service is outstanding. We will use Nginx as our web server because their product is free, handles high website traffic, and powers 60% of the top 100k websites in the world.

Promotion: This referral link provides a $100 credit for Linode

# How to Put a High Performance Website on the Internet:
01. How to Host a Website in the Cloud
02. How to Protect a Website From Threats
03. How to Optimize a Website to Load in 1 Second
04. How to Add a Database to a Website
05. How to Protect a Database from Threats
06. How to Backup a Database in the Cloud
07. How to Scale a Website to Handle High Traffic
08. How to Add a Node.js Application to a Website
09. How to Add a Python Application to a Website
10. How to Add a Payment Method to a Website

Create the Virtual Machine:

The Virtual Machine is an emulated computer system with its own CPU, memory, storage, and network interface that exists on the physical hardware of our cloud service provider. This allows us to install our web server, host our website, and upgrade our hardware as needed for a small monthly cost. In this section, we will create the virtual machine that will contain our website using the least expensive monthly plan available.

# create virtual machine
1. create an account on linode
2. click "create" button
3. click "linode" menu item# select linux distribution
1. click image dropdown menu in "choose a distribution" section
2. click "ubuntu 20.04 LTS" menu item# select datacenter to store virtual machine
1. click region dropdown menu in "region" section
2. select same region as your virtual machines from before# select monthly plan
1. click "shared cpu" tab in "plan" section
2. click "nanode 1gb" radio button# finish virtual machine
1. scroll down to "linode label" section
2. enter "database-backup" into "linode label" text field
3. enter password into "root password" text field
4. click "create linode" button
5. wait until linoide finishes

Install the Mysqldump Utility:

The Mysqldump utility is program that backs up our MySQL database. This allows us to generate a MySQL file that contains the SQL statements that we need to recreate the structure and data in our database. In this section, we will install the Mysqldump utility by installing the MySQL-Client package.

# open console as root user on linode
1. open "linodes" page on linode
2. click "database-backup" linode
3. click "launch lish console" link
4. press "return" key
5. enter "root" into "login" prompt
6. enter your password into "password" prompt
7. press "return" key
8. paste commands into console# update package information
sudo apt-get update# install mysql-client
sudo apt-get install --yes mysql-client

Enable the Uncomplicated Firewall:

The Uncomplicated Firewall is a program that protects your computer from attacks by blocking the incoming and outgoing network traffic based on IP address and port information. This allows us to restrict our network traffic to the IP addresses and ports that we permit in our firewall rules. In this section, we will block all incoming network traffic by executing a command.

# enable firewall
sudo ufw enable

Create the User Account:

The User Account is an entity that can access files and perform commands. It makes it harder for attackers to hack our web server by requiring them to crack our username before they can even try to figure out our password. In this section, we will create a user account with administrative privileges.

# change "placeholder" to unique username
username="placeholder"# create new user on virtual machine
sudo adduser $username# choose password for new user
1. enter password for user account
2. press "return"
3. re-enter password
4. press "return"
5. press "return" to enter the default value
6. enter "y" to confirm information
7. press "return"# add admin group to sudo file
sudo groupadd admin# grant administrative permissions to new user
sudo usermod -aG admin $username# switch to new user
sudo su $username

Disable the Root Login:

The Root User is a user account with unlimited access to the entire system that comes preinstalled with every Linux-based operating system. This provides attackers with half of the information they need to hack our web server. In this section, we will prevent the root user from accessing our virtual machine by changing the setting in our SSH configuration file.

# disable root login
sudo sed "s|PermitRootLogin yes|PermitRootLogin no|g" -i /etc/ssh/sshd_config# restart ssh
sudo service ssh restart

Disable the Password Authentication:

The Password is an authentication method that becomes a threat when the password is too weak or reused with many different accounts. This allows attackers to potentially crack the password through trial and error. In this section, we will prevent users from accessing our virtual machine using password authentication by changing the setting in our SSH configuration.

# disable password authentication
sudo sed "s|PasswordAuthentication yes|PasswordAuthentication no|" -i /etc/ssh/sshd_config# restart ssh
sudo service ssh restart

Create the MySQL User:

The MySQL User is a user account that can potentially destroy our entire database. This usually occurs when a new database administrator grants the “all privileges” option to their MySQL users out of convenience. In this section, we will prevent this by creating our MySQL user with the absolute minimum amount of privileges that are required to backup our database.

# open console as root user on linode
1. open "linodes" page on linode
2. click "database" linode
3. click "launch lish console" link
4. press "return" key
5. enter "root" into "login" prompt
6. enter your password into "password" prompt
7. press "return" key
8. paste commands into console# download backup mysql file
sudo curl -o /etc/mysql/backup_user.sql https://gist.githubusercontent.com/david-littlefield/77cd5ab402d16dc54fb2328d7c3e80aa/raw# store mysql non-root user
user="backup"# change "placeholder" to strong password
password="placeholder"# add user to mysql file
sudo sed "s|#user_placeholder#|$user|g" -i /etc/mysql/backup_user.sql# add password to mysql file
sudo sed "s|#password_placeholder#|$password|g" -i /etc/mysql/backup_user.sql# change "placeholder" to mysql root user password
root_password="placeholder"# open mysql
sudo mysql -u root -p$root_password# run crud mysql file
source /etc/mysql/backup_user.sql# exit mysql
exit# remove crud mysql file
sudo rm /etc/mysql/backup_user.sql

Create the Backup Schedule:

The Crontab is a program that automatically executes a set of commands at specific dates and times. This allows us to create a routine backup of our database that reoccurs at the scheduled time interval. In this section, we will backup our database one-time every day by creating a crontab file.

# get ip address of "database" linode
1. open "linodes" page on linode
2. click "database" linode
3. find ip address under "ip addresses" label
4. write down ip address# open console as non-root user on linode
1. open "linodes" page on linode
2. click "database-backup" linode
3. click "launch lish console" link
4. press "return" key
5. enter your unique username into "login" prompt
6. enter your password into "password" prompt
7. press "return" key
8. paste commands into console# download bash file that backs up mysql
sudo curl -o /etc/mysql/mysqldump.sh https://gist.githubusercontent.com/david-littlefield/05c95818cabbd2cd29e0e5780f6a7a44/raw# store mysql non-root user from earlier
user="backup"# change "placeholder" to mysql non-root user password
password="placeholder"# change "placeholder" to ip address of "database" linode
host="placeholder"# store database name
database="website"# store mysql port number
port="5000"# add user to mysqldump configuration file
sudo sed "s|#user_placeholder#|$user|g" -i /etc/mysql/mysqldump.cnf# add password to mysqldump configuration file
sudo sed "s|#password_placeholder#|$password|g" -i /etc/mysql/mysqldump.cnf# add host to bash file
sudo sed "s|#host_placeholder#|$host|g" -i /etc/mysql/mysqldump.sh# add database to bash file
sudo sed "s|#database_placeholder#|$database|g" -i /etc/mysql/mysqldump.sh# add port to bash file
sudo sed "s|#port_placeholder#|$port|g" -i /etc/mysql/mysqldump.sh# only allow root user to access bash file
sudo chmod 600 /etc/mysql/mysqldump.sh# schedule backup to run one-time per day
sudo echo -e "1 \* \* \* \* exec /bin/bash /etc/mysql/mysqldump.sh" > ~/mysql# move mysql crontab file to "cron.d" directory
sudo mv ~/mysql /etc/cron.d/mysql

Create the Backup File:

The Backup is a copy of our database that protects us from data loss. This allows us to restore our data in the event of hardware or software failure, data corruption, and human-caused events, such as malicious attacks or deletion of data. In this section, we will implement this by creating a web server, preparing the web server, and creating a routine backup service.

# create backup file one-time
sudo bash /etc/mysql/mysqldump.sh# verify backup file exists
sudo ls /etc/mysql/backup/$(date +%Y)/$(date +%m)/$(date +%d)/

Open the SSH Port:

The SSH Port is the port that the SSH Protocol uses to establish the secure connection between our linodes. This port is closed by our firewall on our “database” linode. In this section, we will open our SSH port by adding a firewall rule to our firewall, and by enabling the password authentication.

# get ip address of "database" linode
1. open "linodes" page on linode
2. click "database-backup" linode
3. find ip address under "ip addresses" label
4. write down ip address# open console as non-root user on linode
1. open "linodes" page on linode
2. click "database" linode
3. click "launch lish console" link
4. press "return" key
5. enter your unique username into "login" prompt
6. enter your password into "password" prompt
7. press "return" key
8. paste commands into console# change "placeholder" to ip address of "database-backup" linode
ip_address="placeholder"# enable ssh port from ip address of "database-backup" linode
sudo ufw allow from $ip_address to any port 22# enable password authentication 
sudo sed "s|PasswordAuthentication no|PasswordAuthentication yes|" -i /etc/ssh/sshd_configcreate ".ssh" directory
sudo mkdir -p ~/.ssh/

Transfer the Backup File:

The “database” linode doesn’t allow remote access from the root user. This makes it much harder for attackers to access our database by requiring them to have hacked into our cloud service provider’s data center beforehand. However, this prevents us from restoring our database from our “database-backup” linode remotely because the mysqldump utility requires root access. In this section, we will establish a secure connection between our linodes, disable the password authentication, and transfer our backup file.

# open console as non-root user on linode
1. open "linodes" page on linode
2. click "database-backup" linode
3. click "launch lish console" link
4. press "return" key
5. enter your unique username into "login" prompt
6. enter your password into "password" prompt
7. press "return" key
8. paste commands into consolecreate ".ssh" directory
sudo mkdir -p ~/.ssh/# create ssh key
sudo ssh-keygen -t rsa -b 2048 -f ~/.ssh/id_rsa_database# create ssh key without a passphrase
1. press "return" to use no passphrase
2. press "return" to confirm# set restricted file permissions
sudo chmod 600 ~/.ssh/id_rsa_database.pub# change "placeholder" to non-root user on "database" linode
user="placeholder"# add ip address to ssh configuration file
sudo echo -e "host database\n    hostname $host\n    identityfile ~/.ssh/id_rsa_database\n" >> ~/config# move ssh config file to ".ssh" directory 
sudo mv ~/config ~/.ssh/config# copy ".ssh" directory to "root" directory
sudo cp -r ~/.ssh/ /root/.ssh/# add ssh key to "authorized_keys" file
sudo cat ~/.ssh/id_rsa_database.pub | ssh $user@database "cat >> ~/.ssh/authorized_keys"# connect to "database" linode
sudo ssh $user@database# disconnect from "database" linode
exit# disable password authentication
sudo sed "s|PasswordAuthentication yes|PasswordAuthentication no|" -i /etc/ssh/sshd_config# transfer backup mysql file to "database" linode
sudo scp -i ~/.ssh/id_rsa_database /etc/mysql/backup/$(date +%Y)/$(date +%m)/$(date +%d)/backup.sql $user@$host:~/backup.sql

Restore the Database with the Backup File:

The MySQL program has the ability to replicate the structure and data in our database from our backup file. This allows us to recreate our database from scratch or overwrite our existing corrupted database. In this section, we will delete our existing database, recreate it, and remove our backup file.

# open console as non-root user on "database" linode
1. open "linodes" page on linode
2. click "database" linode
3. click "launch lish console" link
4. press "return" key
5. enter your unique username into "login" prompt
6. enter your password into "password" prompt
7. press "return" key
8. paste commands into console# open mysql
sudo mysql -u root -p$root_password# delete "website" database
drop database website;# create "website" database
create database website;# select "website" database
use website;# confirm data deleted
select * from records;# exit mysql
exit# store database name
database="website"# restore database from backup
sudo mysql -u root -p$root_password $database < ~/backup.sql# open mysql
sudo mysql -u root -p$root_password# select "website" database
use website;# confirm data restored
select * from records;# exit mysql
exit# remove backup mysql file
sudo rm ~/backup.sql