MySQL Database Backup to FTP Server

MySQL Database Backup to FTP Server – Shell Script

As a system administrator, you need to take backup on daily basis. Backups are very useful to recover data from any crashes or corruption. I have written a simple script to take database backup from MySQL server and upload to FTP server. Being a system administrator, I recommend keeping a remote copy of your every backup.

Create a shell script file and copy the below script. The update all the required values and execute.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

#!/bin/bash ###################################################### #  #  ###################################################### DATE=`date +%d%b%y` LOCAL_BACKUP_DIR="/backup/" DB_NAME="test" DB_USER="root" DB_PASSWORD="your password" FTP_SERVER="ftp.tecadmin.net" FTP_USERNAME="ftp user name" FTP_PASSWORD="ftp user password" FTP_UPLOAD_DIR="/backup/" LOG_FILE=/backup/backup-DATE.log ############### Local Backup  ######################## mysqldump -u $DB_USER  -p$DB_PASSWORD $DB_NAME | gzip  > $LOCAL_BACKUP_DIR/$DB_NAME-$DATE.sql.gz ############### UPLOAD to FTP Server  ################ ftp -n $FTP_SERVER << EndFTP user "$FTP_USERNAME" "$FTP_PASSWORD" binary hash cd $FTP_UPLOAD_DIR #pwd lcd $LOCAL_BACKUP_DIR put "$DB_NAME-$DATE.sql.gz" bye EndFTP if test $? = 0 then     echo "Database Successfully Uploaded to Ftp Server         File Name $DB_NAME-$DATE.sql.gz " > $LOG_FILE else     echo "Error in database Upload to Ftp Server" > $LOG_FILE fi

Setup Details – Edit the above script for the following variable as per your system environment. Place all the values correctly to make script working properly.

·         LOCAL_BACKUP_DIR => Local direction path to store backup

·         DB_NAME => database name

·         DB_USER => database adminitrator user name

·         DB_PASSWORD => database administrator password

·         FTP_SERVER => ftp server ip for hostname

·         FTP_USERNAME => ftp username

·         FTP_PASSWORD => ftp password

·         FTP_UPLOAD_DIR => ftp server backup path

·         LOG_FILE => log file name and location

I hope this script will help you for taking database backup over FTP server.

 

How to Create User and Grant Permission in MySQL

Written by Gaurav | May 5, 2018

How to Create MySQL User and Grant Permission. 

For the good security implementation, make sure to create separate user account rather than root to access database for each application. This will ensure that application can’t access other application’s database. You need MySQL administrator (root) privileges To create user accounts and assign privileges to the database. For your information MySQL root account is different than system root account, there are no relations between them.

1. Create New User in MySQL

Login to the MySQL server with root user with shell access and create a new user named “gaurav”. Below command will allow accessing MySQL server to user gaurav from localhost system only.

mysql> CREATE USER 'gaurav'@'localhost' IDENTIFIED BY 'password';

Now assign the privileges to the specific database. Below command will allow all privileges on database “mydb” to user gaurav.

mysql> GRANT ALL ON mydb.* TO 'gaurav'@'localhost';

After creating user and assigning proper privileges, make sure to reload privileges.

mysql> FLUSH PRIVILEGES;

2. Create MySQL User Remote Accessible

To allow any user to connect MySQL server from the remote system. You need to specify hostname or IP address of the remote system. You can also use % to allow any host

mysql> CREATE USER 'gaurav'@'public Ip' IDENTIFIED BY 'password';

mysql> CREATE USER 'gaurav'@'%' IDENTIFIED BY 'password';

mysql> FLUSH PRIVILEGES;

3. Grant Specific User Permissions in MySQL

Please find below list of frequently used privileges in MySQL user. Visit here to get full list of privileges for MySQL user.

·         ALL [PRIVILEGES] – Grant all privileges to user.

·         CREATE – Grant user to create new databases and tables.

·         DROP – Grant user to delete (drop) databases and tables.

·         DELETE – Grant user to delete rows from tables.

·         ALTER – Grant user to modify table structure.

·         INSERT – Grant user to insert (add) rows into tables.

·         SELECT – Grant user to run select command to read data from tables.

·         UPDATE – Grant user to update data in tables.

·         EXECUTE – Grant user to execute stored routines.

·         FILE – Grant user to access file on server host.

·         GRANT OPTION – Grant user to grant or remove other users’ privileges.

Here, you can specify privileges separated by a comma in place of ALL. For example to allow CREATE, DELETE, INSERT, UPDATE access to ‘gaurav’@’localhost’ on database mydb.

mysql> GRANT CREATE,DELETE,INSERT,UPDATE ON mydb.* TO 'gaurav'@'localhost';

mysql> FLUSH PRIVILEGES;

4. Revoke User Permissions in MySQL

Use REVOKE command to remove any specific privilege from the user. For example to remove DELETE privilege from user ‘gaurav’@’localhost’ on mydb database.

mysql> REVOKE DELETE ON mydb.* TO 'gaurav'@'localhost';

mysql> FLUSH PRIVILEGES;

5. Drop User in MySQL

You can simply drop any user from MySQL using DROP command. For example to delete user ‘gaurav’@’localhost’, use following command.

mysql> DROP USER 'gaurav'@'localhost';

mysql> FLUSH PRIVILEGES;