Secure your MySQL database to prevent data loss

[Taiga]

Hi there!
I hope you'll like my tutorial, it's still incomplete and will be updated in the future once I've some more free time!
If you have any questions please post them in the help section.​

Table of contents

1. Introduction
2. Backups
   • Full Datababase backup
   • Row Backup
3. Limit user access
   • Never use ROOT
   • Create mutiple user accounts for each server(s)
   • Limit access for users

1. Introduction
You might have seen it before, people posting topics about their server that got hacked and a lot of their data has been lost or damaged. They didn't know about backups and how to use them.

Today I'll introduce you into the concept of making backups and a way of securing your database server better. I'm not going to go full in-depth about it but I'm going to give you a brief explanation on how to set-up and improve your security for your server.

2. Backups
We will take a look into backups in this part, a backup is making an extra copy of the existing data so you can restore any data if needed.


2.1 Full Database backup
This is a full copy of your database at the moment you click on export. This is a file that contains all the data which is stored in your database as an SQL file. You can use this file to restore the whole database with one click. It isn't actually making a backup of it but it's more like exporting your database to a file which you can use to restore your database.

You can do this by using a many tools, I'll show you how to export it in phpmyadmin and with mysql workbench.

2.1.1 PhpMyAdmin
The first step you've to do once you've logged in into your server is selecting the database. You can do this by clicking on it on the right side.


Once you've done that you'll see that the treeview on the right side has opened and it shows the database rows. The next step is to select export which is located in the top of the right panel.


You'll see a page like the one below, once it's loaded you can see a few options for the export, it says quick and custom, the custom one allows you to select all options which includes what rows of the database have to be exported out as a 'copy'. For now we choose Quick and press go. A file will start to download, no worries! It's the copy of your database in a script form which you can run on any MySQL server if you want to restore your data back to the point where you clicked on the go button.



2.1.2 MySQL Workbench
Will be added in the future!

2.2 Row Backup
Will be added in the future!

3. Limit user access
Will be added in the future so make sure you come back!