• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to use mysqldump without CREATE TABLE statements

by admin

mysqldump is a utility program used for making a logical backup by generating a set of SQL statements that can be executed to reproduce the original database table data and object definition. It dumps one or more database for backup or may transfer to another SQL server. It can also generate data output in different formats, such as CSV, XML, or other delimited text files.

By default, output for each dumped table consists of a CREATE TABLE statement that creates the table, followed by a set of INSERT statements that load the contents of the table.

mysqldump can be run in any of three modes:

mysqldump [options] db_name [tbl_name] ...
mysqldump [options] --databases db_name ...
mysqldump [options] --all-databases

MySql Dump

The “–opt” option optimizes table dumping speed and writes a dump file that is optimal for reloading speed. This option turns on –add-drop-table, –add-locks, –create-options, –disable-keys, –extended-insert, –lock-tables, –quick, and –set-charset. This option is enabled by default. To disable it, use –skip-opt.

# mysqldump -u dbuser -p[pwd] --opt databasename > database_backup.sql

MySql Dump without data (only structure)

The “–no-data” option causes table data not to be written. This is useful to dump only the CREATE TABLE statements.

# mysqldump -u dbuser -p[pwd] --no-data databasename > database_backup.sql

or

# mysqldump -u dbuser -p[pwd] -d databasename --no-data > database_backup.sql

MySql Dump without structure (no drop/create table)

The option “–no-create-db” causes ‘CREATE DATABASE’ statements not to be written. (Normally, these are added to the output automatically when –databases or –all-databases are used.)

# mysqldump -u dbuser -p[pwd] --no-create-db --no-create-info databasename >
database_backup.sql

Import SQL file

mysql -u user_name -p database_name < /path/to/file.sql

Filed Under: MariaDB, mysql

Some more articles you might also be interested in …

  1. What is the MySQL Enterprise Monitor?
  2. What is an Arbitrator in MySQL Cluster
  3. MySQL Fails to Start Using systemctl On systemd Linux Distributions
  4. How to install and configure MySQL sys schema
  5. How to Backup a MySQL Cluster
  6. MySQL : Stored Procedure For Rotating the Audit Log
  7. Changing the value of lower_case_table_names in MySQL 8
  8. MySQL Cluster requirements to avoid Single Point of Failure
  9. How to Restrict MySQL User Creation with Blank Password
  10. How to find the size of a MySQL database

You May Also Like

Primary Sidebar

Recent Posts

  • nixos-rebuild Command Examples in Linux
  • nixos-option: Command Examples in Linux
  • nixos-container : Command Examples in Linux
  • nitrogen Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright