Users, Privileges & Backups

Why not just use root for everything?

On your own laptop you log in to MySQL as the all-powerful root user, which can do anything. On a real server that is dangerous: if an app connects as root and is ever compromised, the attacker can read, change or drop every database. The professional approach is the principle of least privilege — give each user (and each application) only the exact permissions it needs, and nothing more. MySQL lets you create users and grant fine-grained access for this.

Creating a user and granting access

A privilege is permission to do a specific thing (SELECT, INSERT, UPDATE…) on a specific database or table. You create a user, then GRANT them just the privileges they need. Suppose a reporting tool only ever needs to read the codingclave database:

CREATE USER 'report_user'@'localhost' IDENTIFIED BY 'a-strong-password';

GRANT SELECT ON codingclave.* TO 'report_user'@'localhost';

FLUSH PRIVILEGES;

Line by line: CREATE USER 'report_user'@'localhost' makes a new account that may connect from localhost (the @ part limits where they can connect from), with a password set by IDENTIFIED BY. GRANT SELECT ON codingclave.* gives them only the right to read — SELECT — and codingclave.* means every table in that one database (the * is a wildcard for all tables). FLUSH PRIVILEGES tells MySQL to reload its permission tables so the changes take effect.

An application that needs to read and write its own database would get more privileges, but still scoped to that one database:

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'another-strong-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON codingclave.* TO 'app_user'@'localhost';
FLUSH PRIVILEGES;

This grants the four everyday CRUD privileges — SELECT, INSERT, UPDATE, DELETE — on the app’s database only. Notice what is not granted: DROP, CREATE USER, GRANT, or access to other databases. Even your main application should not connect as root; it should use a scoped user like this.

Inspecting and removing access

You can check what a user can do, take privileges away, or remove a user entirely:

SHOW GRANTS FOR 'report_user'@'localhost';

REVOKE INSERT ON codingclave.* FROM 'app_user'@'localhost';

DROP USER 'report_user'@'localhost';

SHOW GRANTS lists exactly what a user is allowed to do — useful for auditing. REVOKE INSERT ... FROM takes back a specific privilege (here the app user loses the ability to insert, keeping the rest). DROP USER deletes the account altogether. The same FLUSH PRIVILEGES habit applies after changing grants.

Backups with mysqldump

Owning a database means being able to back it up and restore it — before a risky change, to move data to another server, or to recover from disaster. The standard tool is mysqldump. It is run from your terminal/command line, not inside the MySQL prompt, and writes the whole database (structure and data) into a single .sql text file you can keep safe.

mysqldump -u root -p codingclave > codingclave_backup.sql

Reading the command: mysqldump is the program; -u root logs in as root; -p makes it prompt for the password; codingclave is the database to dump; and > codingclave_backup.sql sends the output into a file. That file contains the CREATE TABLE and INSERT statements needed to rebuild the database exactly — open it and you will see plain SQL.

Restoring is the mirror image — you feed the file back into MySQL. First create an empty database to restore into, then pipe the file in:

mysql -u root -p codingclave < codingclave_backup.sql

Here the direction flips: instead of mysqldump writing out with >, the regular mysql client reads in the file with <, running every statement inside it to rebuild the tables and rows. This is exactly how you recover after a mistake or move a database to a new machine.