Tag: mysqldump

mysqldump: how to use a specific port

To use a specific port with the mysqldump command, you can provide the --port (or -P) option followed by the port number you want to use. The --port option specifies the TCP/IP port number to use when connecting to the MySQL server.

When using -P remember to use an uppercase P, because the lowercase option -p refers to the password.

Here’s the general syntax of using mysqldump with a specific port:

mysqldump --port=PORT_NUMBER -u USERNAME -p DATABASE_NAME > dump.sql

Replace the following placeholders:

  • PORT_NUMBER: The specific port number you want to use (e.g., 3306).
  • USERNAME: Your MySQL username.
  • DATABASE_NAME: The name of the database you want to dump.
  • dump.sql: The name of the file where you want to save the database dump.

After executing this command, you’ll be prompted to enter your MySQL password. Once you provide the correct password, the mysqldump command will connect to the MySQL server using the specified port and create a dump of the specified database.

Foto von André Carvalho auf Unsplash

 

mysqldump: how to exclude or include tables

mysqldump is a command-line tool used for creating database backups in MySQL. By default, mysqldump includes all tables of the specified database when creating the dump. In some cases, it is useful to exclude some of the tables or even include only some of them. For me, this helped to exclude one of the biggest tables to reduce the backup execution time and file size.

Exclude a single table

To exclude a single table, the argument --ignore-table followed by the table name is used:

mysqldump my_database --ignore-table=my_table > my_backup.sql

This will create a file my_backup.sql containing the sql schema and data of all tables in the database my_database except my_table.

Exclude multiple tables

To exclude multiple tables, simply add the argument --ignore-table for all the tables you want to exclude:

mysqldump my_database --ignore-table=my_table --ignore-table=another_table > my_backup.sql

This will create a file my_backup.sql containing the sql schema and data of all tables in the database my_database except my_table and another_table.

Include a single table

To include only a single table into the dump, add the name of the table to the command:

mysqldump my_database my_table > my_backup.sql

This will create a file my_backup.sql containing the sql schema and data of the table my_table from the database my_database.

Include multiple tables

To include multiple tables into the dump, add the names of the tables to the command:

mysqldump my_database my_table another_table > my_backup.sql

This will create a file my_backup.sql containing the sql schema and data of the tables my_table and another_table from the database my_database.

A complete documentation of mysqldump is available at mysql.com.

Photo by benjamin lehman on Unsplash.