DEV Community

Rupak Basnet
Rupak Basnet

Posted on

SQL DUMP CLI |||

#procedures #cli #sql #sqldump

Showing how to use the SQL CLI to dump, backup, and restore a MySQL database, including tables, triggers, stored procedures, and events. This is perfect for developers who want a complete mysqldump workflow.

Dump Only Tables + Data

mysqldump -u root -p capital_test_db > backup_tables.sql
Enter fullscreen mode Exit fullscreen mode

backup_tables.sql → contains tables and their data only

Dump Full Database (Tables + Data + Triggers + Procedures + Events)

mysqldump -u root -p \
  --routines \
  --triggers \
  --events \
  capital_test_db > backup_full.sql
Enter fullscreen mode Exit fullscreen mode

backup_full.sql → contains everything

Verify Dump Contents

Check triggers, procedures, events (for full dump only) 

grep -E "CREATE TRIGGER|CREATE PROCEDURE|CREATE EVENT" backup_full.sql
Enter fullscreen mode Exit fullscreen mode

Create Target Database

mysql -u root -p
CREATE DATABASE capital_clone;
exit;
Enter fullscreen mode Exit fullscreen mode

Import Dump into Target Database

For tables + data only 

mysql -u root -p capital_clone < backup_tables.sql
Enter fullscreen mode Exit fullscreen mode

For full database 

mysql -u root -p capital_clone < backup_full.sql
Enter fullscreen mode Exit fullscreen mode

Optional: Enable Event Scheduler

mysql -u root -p
SET GLOBAL event_scheduler = ON;
exit;
Enter fullscreen mode Exit fullscreen mode

capital_clone now has tables + data, or full database depending on which dump you imported.

Top comments (0)