#mariadb

MySQL and MariaDB notes: backup and restore, maintenance, deduplication and migration — commands that hold up in production.

• Jan 31, 2009

  Compress a mysqldump backup — gzip, zstd, xz (2026)

  Pipe mysqldump directly into a compressor so you never write the huge uncompressed .sql to disk. gzip is universal; zstd is the modern default (fast and small); xz squeezes hardest. Restore by piping the decompressor back into mysql.

• Jun 9, 2007

  MySQL: find and remove duplicate rows (2026)

  The old ALTER IGNORE TABLE … ADD UNIQUE trick was removed in MySQL 5.7. The portable way to dedupe now is a self-join DELETE keeping the lowest id; MySQL 8 / MariaDB 10.2+ add a cleaner window-function version. Then add a UNIQUE index so duplicates can't come back.

• Aug 31, 2006

  MySQL: set or reset the AUTO_INCREMENT value (2026)

  ALTER TABLE … AUTO_INCREMENT = N sets the next id a table will hand out. It won't go below the current max id, behaves differently across storage engines on restart, and interacts with auto_increment_increment on replicated/Galera setups. Here's the full picture.