• 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. Understanding MySQL Pluggable Authentication
  2. How To Create a Local Yum Repository for MySQL Enterprise Packages
  3. Beginners Guide to MySQL User Management
  4. MySQL: How to Set Account Resource Limits
  5. What is an Arbitrator in MySQL Cluster
  6. How To Skip a Transaction on MySQL replication slave When GTIDs Are Enabled
  7. How to gather information on the MySQL 8 Roles and Privileges assigned to a user without using SHOW GRANTS
  8. How to Set Space limits for MySQL for database/schema/table
  9. Granting All Privileges On All databases Except One Specific Table in MySQL
  10. How To Purge Audit Logs in MySQL

You May Also Like

Primary Sidebar

Recent Posts

  • JavaFX ComboBox: Set a value to the combo box
  • Nginx load balancing
  • nginx 504 gateway time-out
  • Images preview with ngx_http_image_filter_module

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright