How to use mysqldump to back up specific tables or databases?
Aug 04, 2025 am 06:34 AM
To back up a specific table or database, you can use the mysqldump command; 1. Back up a single database: mysqldump -u [username] -p [database_name] > backup_file.sql; 2. Back up multiple databases: mysqldump -u [username] -p --databases db1 db2 > multi_backup.sql; 3. Back up all databases: mysqldump -u [username] -p --all-databases > full_server_backup.sql; 4. Back up a specific table: mysqldump -u [username] -p [database_name] table1 table2 > tables_backup.sql; 5. Match backup tables through pattern: combine mysql commands and xargs to filter tables such as log_; 6. Use --single-transaction, --routines and other options to enhance backup consistency and integrity; 7. Restore backup: mysql -u [username] -p [database_name]
To back up specific tables or databases using mysqldump , you can use command-line options to target exactly what you want to export. mysqldump is a powerful utility that generates SQL scripts containing CREATE and INSERT statements to recreate the data and structure.
Here's how to use it effectively for different backup scenarios:
1. Back Up a Single Database
To back up an entire database:
mysqldump -u [username] -p [database_name] > backup_file.sql
Example:
mysqldump -u root -p myapp_db > myapp_backup.sql
This exports all tables, views, and routines in myapp_db into myapp_backup.sql .
2. Back Up Multiple Databases
Use the --databases option to back up multiple databases:
mysqldump -u [username] -p --databases db1 db2 db3 > multi_backup.sql
Example:
mysqldump -u root -p --databases sales inventory > business_data.sql
This includes CREATE DATABASE statements in the output, so when restoring, the databases will be created automatically if they don't exist.
3. Back Up All Databases on the Server
To back up every database (including system databases like mysql , information_schema , etc.):
mysqldump -u [username] -p --all-databases > full_server_backup.sql
Use this with caution—it can produce large files and may include sensitive system data.
4. Back Up Specific Tables from a Database
To dump only certain tables from a database:
mysqldump -u [username] -p [database_name] table1 table2 > tables_backup.sql
Example:
mysqldump -u root -p myapp_db users orders > user_order_backup.sql
This backs up only the users and orders tables from myapp_db .
?? Note: You cannot use wildcards (
%or*) directly in table names with standardmysqldump, but you can combine it with shell scripting or use--tablesto preventmysqldumpfrom interpreting the first argument as a database name.
5. Back Up Tables Matching a Pattern (Advanced)
Since mysqldump doesn't support wildcards, you can use a shell script to select tables by name pattern.
Example: Back up all tables starting with "log_" in a database
DB="myapp_db" USER="root" PASSWORD="your_password" mysql -u"$USER" -p"$PASSWORD" -D"$DB" -Bse "SHOW TABLES LIKE 'log_%'" | \ xargs mysqldump -u"$USER" -p"$PASSWORD" "$DB" > log_tables_backup.sql
This uses mysql to list tables matching 'log_%' , then pipes the list to mysqldump .
6. Useful Options to Enhance Backups
You can add several options to customize the dump:
-
--single-transaction: Good for InnoDB; ensures consistency without locking tables. -
--routines: Includes stored procedures and functions. -
--triggers: Includes triggers (enabled by default). -
--events: Includes event scheduler events. -
--add-drop-table: AddsDROP TABLEbefore eachCREATE TABLE. -
--lock-tables=false: Avoids table locks (useful for mixed storage engines).
Example with options:
mysqldump -u root -p --single-transaction --routines --triggers \ myapp_db users orders > backup_with_routines.sql
7. Restoring a mysqldump Backup
To restore:
mysql -u [username] -p [database_name] < backup_file.sql
Make sure the database exists (or use --databases / --all-databases for auto-creation).
Summary of Common Commands
| Task | Command |
|---|---|
| Single database | mysqldump -u user -p db_name > backup.sql
|
| Multiple databases | mysqldump -u user -p --databases db1 db2 > backup.sql
|
| All databases | mysqldump -u user -p --all-databases > full.sql
|
| Specific tables | mysqldump -u user -p db_name tbl1 tbl2 > tables.sql
|
| Tables by pattern | Use mysql xargs in a script |
Basically, mysqldump is flexible for both full and selective backups. Just specify the database and table names directly, and add options for consistency and completeness. It's a go-to tool for logical backups in MySQL environments.
The above is the detailed content of How to use mysqldump to back up specific tables or databases?. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undress AI Tool
Undress images for free
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
PHP and PDO: How to perform database backup and restore operations
Jul 29, 2023 pm 06:54 PM
PHP and PDO: How to perform database backup and restore operations When developing web applications, database backup and restore are very important tasks. As a popular server-side scripting language, PHP provides a wealth of libraries and extensions, among which PDO (PHP Data Objects) is a powerful database access abstraction layer. This article will introduce how to use PHP and PDO to perform database backup and restore operations. Step 1: Connect to the database Before actual operation, we need to establish a connection to the database. Use PDO pair
How to use ThinkPHP6 to implement database backup and recovery
Jun 20, 2023 pm 07:25 PM
In the process of developing business systems, the database is a very important part. Therefore, backing up and restoring the database is a very necessary operation. This article will combine examples of the ThinkPHP6 framework to introduce how to use ThinkPHP6 to implement database backup and recovery. 1. Database backup 1.1 Environment preparation Before performing database backup, you need to confirm the following points: 1. You need to set the bin directory address of the mysql database and add its path to the system Path variable; 2. Mysqld needs to be installed
PHP and Memcached database backup and recovery
May 15, 2023 pm 09:12 PM
With the rapid development of the Internet, large-scale MySQL database backup and recovery has become one of the essential skills for major enterprises and websites. With the widespread application of Memcached, how to back up and restore Memcached has also become an important issue. As one of the main languages ??for web development, PHP has unique advantages and skills in handling backup and recovery of MySQL and Memcached. This article will introduce in detail the implementation method of PHP processing MySQL and Memcached backup and recovery.
Analysis of project experience on MySQL database backup and recovery performance optimization
Nov 02, 2023 am 08:53 AM
In the current Internet era, the importance of data is self-evident. As one of the core components of Internet applications, database backup and recovery work is particularly important. However, as the amount of data continues to increase and business requirements become increasingly complex, traditional database backup and recovery solutions can no longer meet the high availability and high performance requirements of modern applications. Therefore, optimizing the backup and recovery performance of MySQL database has become an urgent problem that needs to be solved. In practice, we have adopted a series of project experiences to effectively improve MySQL data
How to use thinkorm to implement database backup and restore
Jul 28, 2023 pm 02:05 PM
Title: Using ThinkORM to realize database backup and restoration Introduction: In the development process, database backup and restoration is a very important task. This article will introduce how to use the ThinkORM framework to implement database backup and restoration, and provide corresponding code examples. 1. Background introduction During the development process, we usually use databases to store and manage data. The principle of database backup and restore is to perform regular backups of the database so that the data can be quickly restored in the event of database problems or data loss. With the help of
Database backup, optimization and recovery of Pagoda Panel
Jun 21, 2023 am 09:45 AM
In today's online world, websites have become an important carrier for every enterprise, organization or individual to display their brands, services, products, etc. In order to ensure the normal operation and security of the website, we need to continuously back up and optimize the database. and recovery. As a server management software with simple operation, rich functions and beautiful interface, Pagoda Panel is also quite excellent in database management and has important functions such as backup, optimization and recovery. This article will focus on the database backup, optimization and recovery functions of Pagoda Panel and related concerns.
How to backup database in Golang?
Jun 01, 2024 am 11:56 AM
Backing up your database in Golang is crucial to protecting your data. You can use the database/sql package in the standard library, or a third-party package such as github.com/go-sql-driver/mysql. Specific steps include: Connect to the database. Create a file to store the backup data. Use the Dump function or Exporter to back up the database to a file.
Project experience analysis of MySQL database backup and recovery strategy
Nov 02, 2023 pm 06:23 PM
Project experience analysis of MySQL database backup and recovery strategy Summary: MySQL database, as an open source and stable and reliable relational database management system, is widely used in various enterprise projects. Database backup and recovery is an important task to ensure data security and availability. This article will share some practical experience in MySQL database backup and recovery strategies accumulated in the project. Introduction: For any enterprise, data is one of the most important assets, and the database is the core system for saving, managing and processing this data.
