MySQL Back-up: mysqldump with each database in its own SQL File

0 0
Read Time:2 Minute, 2 Second

Take a mysqldump back-up to separate files

To take a back-up, run the mysqldump tool on each available database.

$ mysql -N -e 'show databases' | while read dbname; do mysqldump --complete-insert --routines --triggers --single-transaction "$dbname" > "$dbname".sql; done

The result is a list of all your database files, in your current working directory, suffixed with the .sql file extension.

$ ls -alh *.sql

-rw-r--r-- 1 root root  44M Aug 24 22:39 db1.sql
-rw-r--r-- 1 root root  44M Aug 24 22:39 db2.sql

If you want to write to a particular directory, like /var/dump/databases/, you can change the output of the command like this.

$ mysql -N -e 'show databases' | while read dbname; do mysqldump --complete-insert --routines --triggers --single-transaction "$dbname" > /var/dump/databases/"$dbname".sql; done

Mysqldump each database and compress the SQL file

If you want to compress the files, as you’re taking them, you can run either gzip or bzip on the resulting SQL file.

$ mysql -N -e 'show databases' | while read dbname; do mysqldump --complete-insert --routines --triggers --single-transaction "$dbname" > "$dbname".sql; [[ $? -eq 0 ]] && gzip "$dbname".sql; done

The result is again a list of all your databases, but gzip‘d to save diskspace.

$ ls -alh *.gz

-rw-r--r--  1 root root  30K Aug 24 22:42 db1.sql.gz
-rw-r--r--  1 root root 1.6K Aug 24 22:42 db1.sql.gz

This can significantly save you on diskspace at the cost of additional CPU cycles while taking the back-up.

Import files to mysql from each .SQL file

Now that you have a directory full of database files, with the database name in the SQL file, how can you import them all again?

The following for-loop will read all files, strip the “.sql” part from the filename and import to that database.

Warning: this overwrites your databases, without prompting for confirmation. Use with caution!

$ for sql in *.sql; do dbname=${sql/.sql/}; echo -n "Now importing $dbname ... "; mysql $dbname < $sql; echo " done."; done

The output will tell you which database has been imported already.

$ for sql in *.sql; do dbname=${sql/.sql/}; echo -n "Now importing $dbname ... "; mysql $dbname < $sql; echo " done."; done 

Now importing db1 ...  done.
Now importing db2 ...  done.

These are very simple one-liners that come in handy when you’re migrating from server-to-server.

Loading

Happy
Happy
100 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %
Print Friendly, PDF & Email

About Author

  • Related Posts

    Average Rating

    5 Star
    0%
    4 Star
    0%
    3 Star
    0%
    2 Star
    0%
    1 Star
    0%

    Incase you missed

    Best Places to get Accounting Internship Opportunities

    Best Places to get Accounting Internship Opportunities

    Top Kenyan Based Companies to apply for internship

    Top Kenyan Based Companies to apply for internship

    Credit Scores Quickk FAQs

    Credit Scores Quickk FAQs

    Credit Score Ranges: What Do They Mean?

    Credit Score Ranges: What Do They Mean?

    RANDOMIZING PHONE NUMBERS IN EXCEL

    RANDOMIZING PHONE NUMBERS IN EXCEL

    [Updated 2024] – Passport Application FOR CHILDREN ONLY(PERSONS UNDER 18 YEARS)

    [Updated 2024] – Passport Application FOR CHILDREN ONLY(PERSONS UNDER 18 YEARS)

    [Updated 2024] -Passport Application FOR ADULTS ONLY-PERSONS OVER 18 YEARS

    [Updated 2024] -Passport Application FOR ADULTS ONLY-PERSONS OVER 18 YEARS

    Understanding Tires Load Index vs Load Range on Car Tires

    Understanding Tires Load Index vs Load Range on Car Tires

    Computer Bus: Video

    Computer Bus: Video

    Computer Bus

    Computer Bus

    Types of Computer Network Topology Explained With Diagrams

    Types of Computer Network Topology Explained With Diagrams

    The Functions of PCI Slots

    The Functions of PCI Slots

    Complementary Metal Oxide Semiconductor – CMOS

    Complementary Metal Oxide Semiconductor – CMOS

    The Computer Motherboard and Its Components Explained

    The Computer Motherboard and Its Components Explained

    AI in Education is Best Experiences

    AI in Education is Best Experiences

    Harnessing the Power of Wind Energy

    Harnessing the Power of Wind Energy

    The Golden Gate’s Timeless Majesty

    The Golden Gate’s Timeless Majesty

    Rise of Competitive Video Gaming

    Rise of Competitive Video Gaming

    Ultimate Sports Ground Experience

    Ultimate Sports Ground Experience

    Global Ocean Cleanup Initiatives

    Global Ocean Cleanup Initiatives

    The Enduring Appeal of Gaming Classics

    The Enduring Appeal of Gaming Classics

    The Art of Mindful Eating

    The Art of Mindful Eating

    Classic Video Games Making a Comeback

    Classic Video Games Making a Comeback

    Sustainable Travel for Eco-Friendly Tourism

    Sustainable Travel for Eco-Friendly Tourism

    Mind-Body Connection for Meditation

    Mind-Body Connection for Meditation

    Future of Work Like Remote Collaboration Tools

    Future of Work Like Remote Collaboration Tools

    Innovations in 3D Printing

    Innovations in 3D Printing

    Digital Detox Unplug and Reconnect

    Digital Detox Unplug and Reconnect

    Crafting Engaging Audio Experiences

    Crafting Engaging Audio Experiences

    Advancements in Digital Camera

    Advancements in Digital Camera

    Advanced progress in the workplace

    Advanced progress in the workplace

    Navigating the Landscape of Cryptocurrency Trends

    Navigating the Landscape of Cryptocurrency Trends

    Exploring Deep Sea Exploration

    Exploring Deep Sea Exploration

    Plant Based Alternatives Reshaping Food

    Plant Based Alternatives Reshaping Food

    Space tourism soars into the forefront of exploration

    Space tourism soars into the forefront of exploration

    Fashion Forward: Sustainable Fashion Brands

    Fashion Forward: Sustainable Fashion Brands

    AI in Healthcare: Game-Changer or Ethical Dilemma?

    AI in Healthcare: Game-Changer or Ethical Dilemma?

    VR Trends Reshaping Entertainment

    VR Trends Reshaping Entertainment

    Tech Giants Unite for Sustainability

    Tech Giants Unite for Sustainability

    Sleep Study Unveils New Insights

    Sleep Study Unveils New Insights

    How to Download Your p9 Form from Ghris Portal

    How to Download Your p9 Form from Ghris Portal

    How to Download Previous Payslips from Ghris

    How to Download Previous Payslips from Ghris

    Steps on Downloading and Print GHRIS Payslip Online

    Steps on Downloading and Print GHRIS Payslip Online

    Link a Business on Ecitizen Portal

    Link a Business on Ecitizen Portal

    How to Update the Beneficial Ownership Information

    How to Update the Beneficial Ownership Information

    How to Reset Epson L382 Printer

    How to Reset Epson L382 Printer

    List Of Accredited TVET Institutions In Kenya – Ms Excel Search Form

    List Of Accredited TVET Institutions In Kenya – Ms Excel Search Form

    List Of Accredited TVET Institutions In Kenya

    List Of Accredited TVET Institutions In Kenya

    UNIT TITLE : INTEGRATED SCIENCE

    UNIT TITLE	: INTEGRATED SCIENCE

    How to install zip extension in php 8.0/8.1 in Cyberpanel

    How to install zip extension in php 8.0/8.1 in Cyberpanel

    Top 10 tourist centers in kenya with brief notes

    Top 10 tourist centers in kenya with brief notes

    What is Rental Income?:KRA Itax Services

    What is Rental Income?:KRA Itax Services

    GETTING STARTED ON KRA PIN WHY & HOW TO ACQUIRE ONE

    GETTING STARTED ON KRA PIN WHY & HOW TO ACQUIRE ONE

    Kenyan Passport : Submission and Biometrics capture

    Kenyan Passport : Submission and Biometrics capture

    Kenyan Passport : Requirements for Lost passports

    Kenyan Passport : Requirements for Lost passports

    Kenyan Passport : Requirements for Mutilated Passports

    Kenyan Passport : Requirements for Mutilated Passports

    Kenyan Passport : Requirements for Replacement of passports

    Kenyan Passport : Requirements for Replacement of passports

    Kenyan Passport: Requirements for New (First time) applications

    Kenyan Passport: Requirements for New (First time) applications

    Dolibarr FEATURES Main application/modules (all optional)

    Dolibarr FEATURES Main application/modules (all optional)

    REASONS FOR CHOOSING DOLIBARR AS YOUR Open Source ERP & CRM for business

    REASONS FOR CHOOSING DOLIBARR AS YOUR Open Source ERP & CRM for business