MySQL + SQL · Lesson 122

MySQL Practical Commands

Q: How do I back up a MySQL database?

Use the mysqldump tool from the terminal: mysqldump -u root -p dbname > backup.sql. This writes the whole database (structure and data) to an SQL file you can restore later.

Q: How do I restore a MySQL database from a backup?

Create the database, then pipe the SQL file back in: mysql -u root -p dbname < backup.sql. This runs all the statements in the file to rebuild the tables and data.

Q: How do I create a user and grant privileges in MySQL?

Use CREATE USER and GRANT: CREATE USER 'app'@'localhost' IDENTIFIED BY 'pass'; then GRANT SELECT, INSERT ON db.* TO 'app'@'localhost';. This makes a user with only the permissions it needs.

Q: How do I see information about a table in MySQL?

Use DESCRIBE tablename; for its columns, SHOW CREATE TABLE tablename; for its full definition, and SHOW INDEX FROM tablename; for its indexes. These help you understand an existing table.

Q: How do I import a CSV file into MySQL?

Use LOAD DATA INFILE: LOAD DATA INFILE 'file.csv' INTO TABLE t FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS;. It reads the CSV rows into the table, skipping the header row.

In this lesson: Everyday MySQL commands for practicals and admin work — users, databases, tables, backup, status — with output. In Hindi & English.

Beyond queries

Besides SELECT and friends, you need practical commands to run a real database: inspecting tables, taking backups, importing and exporting data, and managing users. These are the everyday admin tasks every developer meets.

💡 Know how to inspect, back up, import/export, and manage users — not just query.

Inspecting the database

SHOW DATABASES;
SHOW TABLES;
DESCRIBE students;              -- columns and types
SHOW CREATE TABLE students;     -- full definition
SHOW INDEX FROM students;       -- indexes
SELECT COUNT(*) FROM students;  -- how many rows

These let you understand any database you inherit — its tables, their structure, and their size.

Backup and restore

# backup (from the terminal, not the mysql prompt)
mysqldump -u root -p school > backup.sql

# restore into a database
mysql -u root -p school < backup.sql

mysqldump writes the whole database to an SQL file. To restore, create the database and feed the file back in. Always back up before risky changes.

Import and export

-- import a CSV into a table
LOAD DATA INFILE 'students.csv'
INTO TABLE students
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

-- export a query to CSV
SELECT * FROM students
INTO OUTFILE 'out.csv'
FIELDS TERMINATED BY ',';

These move data between MySQL and CSV files, handy for spreadsheets and bulk loads.

Users and privileges

CREATE USER 'app'@'localhost' IDENTIFIED BY 'secret';
GRANT SELECT, INSERT, UPDATE ON school.* TO 'app'@'localhost';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'app'@'localhost';
REVOKE INSERT ON school.* FROM 'app'@'localhost';

Create a dedicated user with only the privileges it needs — never let apps use root. SHOW GRANTS checks what a user can do.

Maintenance commands

SELECT VERSION();              -- MySQL version
SHOW PROCESSLIST;             -- current connections/queries
SHOW STATUS;                  -- server status variables
ANALYZE TABLE students;       -- update table statistics
OPTIMIZE TABLE students;      -- reclaim space, defragment

These help you monitor and tune the server, especially as data grows.

Common mistakes

Running mysqldump at the mysql> prompt. It is a terminal command, not SQL.
Giving apps root access. Create a limited user with GRANT.
Forgetting to back up before big changes. Always dump first.
Ignoring IGNORE 1 ROWS on CSV import. Otherwise the header becomes a data row.

Summary

Inspect with SHOW, DESCRIBE and SHOW CREATE TABLE.
Back up with mysqldump; restore by feeding the SQL file back.
Import/export with LOAD DATA INFILE and INTO OUTFILE.
Manage users with CREATE USER, GRANT and REVOKE.
Monitor and tune with VERSION, PROCESSLIST and OPTIMIZE.

Next, keep the core handy with the MySQL cheat sheet, or manage access in DDL, DML and DQL.

Frequently Asked Questions

How do I back up a MySQL database?

Use the mysqldump tool from the terminal: mysqldump -u root -p dbname > backup.sql. This writes the whole database (structure and data) to an SQL file you can restore later.

How do I restore a MySQL database from a backup?

Create the database, then pipe the SQL file back in: mysql -u root -p dbname < backup.sql. This runs all the statements in the file to rebuild the tables and data.

How do I create a user and grant privileges in MySQL?

Use CREATE USER and GRANT: CREATE USER 'app'@'localhost' IDENTIFIED BY 'pass'; then GRANT SELECT, INSERT ON db.* TO 'app'@'localhost';. This makes a user with only the permissions it needs.

How do I see information about a table in MySQL?

Use DESCRIBE tablename; for its columns, SHOW CREATE TABLE tablename; for its full definition, and SHOW INDEX FROM tablename; for its indexes. These help you understand an existing table.

How do I import a CSV file into MySQL?

Use LOAD DATA INFILE: LOAD DATA INFILE 'file.csv' INTO TABLE t FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS;. It reads the CSV rows into the table, skipping the header row.

💻 Live Code Editor

This page's programs are ready here — run them, edit them, and learn. No installation needed.

Powered by OneCompiler. The code loads into the editor automatically — press Run to see the output. If the editor does not open, open it in a new tab.