Category: MySQL

mysqldump: how to use a specific port

wine barrels in a wine cellar

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:

Bash
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

hd without cover

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:

Bash
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:

Bash
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:

Bash
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:

Bash
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.

 

MySQL error: Cannot truncate a table referenced in a foreign key constraint

pharmacist cabinet

By default, you cannot TRUNCATE a table that has foreign key constraints applied on it. This is to keep the data consistent over multiple tables that are linked by constraints. Nevertheless, it might be necessary to truncate all data from a table.

Here are a few options you can consider to resolve this issue:

Solution 1 – Disable Foreign Key Checks

The fastest way to clean the table is to disable FOREIGN_KEY_CHECKS. Temporarily disabling foreign key checks allows you to truncate the table without encountering the error. However, this should be done with caution as it can lead to data integrity issues if not handled properly.

SQL
SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE TABLE `table1`;
TRUNCATE TABLE `table2`;
SET FOREIGN_KEY_CHECKS = 1;

Solution 2 – Delete Rows Instead of Truncating

Instead of truncating the table, you can delete rows from it. Deleting rows one by one will trigger the foreign key constraint checks, allowing MySQL to maintain referential integrity. You can use the DELETE statement with a WHERE clause to remove specific rows.

SQL
DELETE FROM your_table WHERE condition;

Solution 3 – Drop and Recreate Foreign Key Constraints

If it’s acceptable for your application, you can drop the foreign key constraints referencing the table you want to truncate, truncate the table, and then recreate the foreign key constraints.

SQL
ALTER TABLE referencing_table DROP FOREIGN KEY constraint_name;
TRUNCATE TABLE your_table;
ALTER TABLE referencing_table ADD CONSTRAINT constraint_name FOREIGN KEY (column_name) REFERENCES your_table(column_name);

Photo by Jan Antonin Kolar on Unsplash

MySQL: Tabellen in andere Datenbank kopieren

Mit nachstehenden MySQL-Befehlen lässt sich eine Tabelle recht einfach in eine andere Datenbank kopieren. In diesem Beispiel werden die Daten von db1 in db2 kopiert.

Zunächst muss die neue Tabelle (mit gleicher Struktur) erstellt werden:

CREATE TABLE db2.newTable LIKE db1.oldTable

Danach können die Daten in die neue Tabelle kopiert werden:

ALTER TABLE db2.newTable DISABLE KEYS
INSERT INTO db2.newTable SELECT * FROM db1.oldTable
ALTER TABLE db2.newTable ENABLE KEYS

MySQL-Variablen für Spaltennamen (und andere Identifier) verwenden

Variablen lassen sich in MySQL hervorragend für Strings, Zahlenwerte oder auch binäre Daten verwenden. Möchte man diese Variablen jedoch zum Adressieren von Spalten, Tabellen oder Datenbanken verwenden, dann erfordert dies ein etwas anderes Vorgehen. Dieser Artikel gibt eine kurze Zusammenfassung, wie man MySQL-Variablen in Abfragen einsetzt.

MySQL: INSERT … ON DUPLICATE KEY UPDATE …

MySQL bietet die Möglichkeit, beim Ausführen eines INSERT INTO ein UPDATE auszuführen, falls es beim Einfügen des Datensatzes zu einem dublicate key kommt. Dies lässt sich mit folgender Eingabe erzielen:

INSERT INTO <table> (<field1>, <field2>) VALUES (<value1>, <value2>)
ON DUPLICATE KEY UPDATE <field2> = <field2> + 1;

Sollte es bei der Ausführung der INSERT-Anweisung zu einem doppelten Eintrag in einer als PRIMARY KEY oder UNIQUE KEY definierten Spalte kommen, dann wendet MySQL die UPDATE-Anweisung auf den Datensatz an, der zu dem doppelten Inhalten führt.

Spalte einer MySQL-Tabelle verschieben

Die Spalte einer MySQL-Tabelle lässt sich folgendermaßen an eine andere Position verschieben:

ALTER TABLE <tabelle> MODIFY <spaltenname> tinyint(1) DEFAULT '0'
AFTER <spaltenname_vor_neuer_position>

Wichtig ist hierbei auch die Angabe der Spaltendefinitionen. Eingaben wie “tinyint(1) DEFAULT ‘0’” müssen also den Definitionen der Spalte entsprechen und beim Verschieben mit angegeben werden.