• 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. Backup and Restore Of Group Replication Node ( MySQL 8.0 )
  2. MySQL Server 8.0 – How to create a REPLICATION SLAVE using MEB
  3. MySQL Backup stuck at “Starting to lock all the tables”
  4. “Access denied for user ‘username’@’hostname’ (using password: YES)” – Error while connecting MySQL with PHP
  5. MySQL Cluster requirements to avoid Single Point of Failure
  6. MySQL – How to Set Maximum Rates for Connections and Queries
  7. How To Shut Down a Node in MySQL Cluster
  8. How to find the size of a MySQL database
  9. How to Restart a MySQL Cluster without downtime
  10. MySQL – How to undo (rollback) a set of SQL statements

You May Also Like

Primary Sidebar

Recent Posts

  • ctags: Generates an index (or tag) file of language objects found in source files for many popular programming languages
  • csvtool: Utility to filter and extract data from CSV formatted sources
  • csvstat: Print descriptive statistics for all columns in a CSV file
  • csvsql: Generate SQL statements for a CSV file or execute those statements directly on a database

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright