SimpleBackups

Database Backup Script for MySQL and Dumping to Amazon S3

You can automate the creation of backup and storing it to Amazon S3 within a few minutes. Below bullets brief about what you are going to learn in this part of the article:

• Create a script that automates the MySQL backup directory creation
• Upload/sync the backups with Amazon S3
• Cron will run this command every day (to back up)

Step 1: Generating a shell script which will dump the MySQL database

SimpleBackups

# Find current msqldump binary
which mysqldump

# Check mysqldump version
mysqldump --version

# Get mysqdump man
mysqldump --version

# Dump Specific MySQL Tables from a database

SimpleBackups

# Reference: https://simplebackups.com/blog/how-to-show-all-mysql-databases/

# How to show a list MySQL databases with mysqlshow
mysqlshow -u YOUR_USER_NAME -p

# Show a list MySQL databases with SHOW DATABASES
mysql -u user -p
SHOW DATABASES

# Filter a list of MySQL databases

SimpleBackups

# Reference: https://simplebackups.com/blog/how-to-create-and-grant-permissions-to-a-mysql-user/

# How to create a User in MySQL
CREATE USER ‘username’@’hostname’ IDENTIFIED BY ‘password’;

# How to show the list of users in MySQL
SELECT * FROM mysql.user;

# Show currently logged MySQL users
SELECT current_user();

SimpleBackups

# Reference: https://simplebackups.com/blog/remote-mysql-database-backup-via-ssh-tunneling/

# Setting up an SSH tunnel interactively
ssh -L 3306:mysql-server:3306 username@mysql-server

# How use mysqldump via SSH tunnel
mysqldump -P 3336  -u dbuser -p password database_name > dumpfile.sql

# How to restore a MySQL backup via the command line through an SSH tunne
mysql -P 3336 -u dbuser -p password database_name < dumpfile.sql