MySQL - How to use mysqldump

MySQL - How to use mysqldump

Disclaimer:
The information provided in this guide is for educational and informational purposes only. It is provided “as is” without any representations or warranties, express or implied. You are using this information at your own risk. The author assumes no responsibility for any loss, damage, or issues arising from the use or misuse of the content provided.

NOTE: MacOS does not come with Homebrew.  It must be installed if you want to use it.

From  Source MySQL Server/Host:

# mysqldump --set-gtid-purged=OFF -u <username> -p -h <source_host> <database_name> ><dump_filename>.sql

  1. -u <username> : MySQL user (e.g., root), this mysql user must have proper permissions to the specify database.
  2. -p: prompts for password
  3. -h <source_host> : hostname or IP of SOURCE server (omit if local)
  4. <database_name> : the name of the database you want to dump
  5. ><dump_filename> : saves output to a filename <dump_filename>, NOTE: don't forget the redirect symbol, '>' which means to direct standard output to.
Example:
  1. <username> = root
  2. <source_host>,  hostname is local so we can omit -h and specifying hostname altogether
  3. <database_name> is 'testdb'
  4. <dump_filename> is 'testdb_dump' (or testdb_dump.sql)

Example Command Syntax: mysqldump --set-gtid-purged=OFF -u root -p testdb >testdb_dump.sql

This produces the file, testdb_dump.sql, which you can copy over to another MySQL use and use to load the contents into.
You can also use this file to restore the database 'testdb' back to the point in time.


To the TARGET MySQL Server/Host:

Copy the dumped file to a target host where you want to load the contents to.  You can also restore the content back to the source host as a target host too.

# mysql -u <username> -p -h <target_host> <database_name> < /path/to/<dump_filename>.sql
    1. -u <username> : MySQL user (e.g., root), this mysql user must have proper permissions.
    2. -p: prompts for password
    3. -h <target_host> : hostname or IP of TARGET server (omit if local)
    4. <database_name> : the name of the database you want to restore
    5. < <dump_filename> : saves output to a filename <dump_filename>, NOTE: don't forget the symbol, '<' which means read from.

    • Related Articles

    • MySQL - Common Command Syntaxes

      Disclaimer: The information provided in this guide is for educational and informational purposes only. It is provided “as is” without any representations or warranties, express or implied. You are using this information at your own risk. The author ...

    • MySQL - Installing Latest Percona mysql on Debian 12

      Disclaimer: The information provided in this guide is for educational and informational purposes only. It is provided “as is” without any representations or warranties, express or implied. You are using this information at your own risk. The author ...

    • MySQL - Setup multi-threaded replication

      Disclaimer: The information provided in this guide is for educational and informational purposes only. It is provided “as is” without any representations or warranties, express or implied. You are using this information at your own risk. The author ...

    • MySQL - How to Clone All Data from DONOR MySQL server to another using MySQL Clone Plugin

      Disclaimer: The information provided in this guide is for educational and informational purposes only. It is provided “as is” without any representations or warranties, express or implied. You are using this information at your own risk. The author ...

    • MySQL - Enable SSL connection between MySQL hosts using self-signed SSL

      Disclaimer: The information provided in this guide is for educational and informational purposes only. It is provided “as is” without any representations or warranties, express or implied. You are using this information at your own risk. The author ...