Migrating MySQL or MariaDB to AnubizHost

If source is MySQL 5.7 or 8.0, target either MySQL 8.0 or MariaDB 10.6+ (mostly compatible). MariaDB has better open-source story, MySQL has slightly more Oracle features. For most workloads, MariaDB 10.11 LTS on Debian/Ubuntu is the pragmatic choice.

Install: apt install mariadb-server-10.11. Tune my.cnf: innodb_buffer_pool_size 60-70% of RAM, innodb_log_file_size 1GB, innodb_flush_log_at_trx_commit 1 (durable) or 2 (faster but loses last transactions on crash).

mysqldump --single-transaction --routines --triggers --events \
  --all-databases -h source -u root -p | gzip > mysql-all.sql.gz
gunzip -c mysql-all.sql.gz | mysql -u root

For large databases, dump per-table and import in parallel. --single-transaction gives a consistent snapshot for InnoDB without table locking.

Set server-id=2, log_bin=ON on target. Get a consistent dump with binlog position: mysqldump --master-data=2 --single-transaction --all-databases > full.sql. Import on target, then start replication: CHANGE MASTER TO MASTER_HOST='source', MASTER_USER='repuser', MASTER_PASSWORD='xxx', MASTER_LOG_FILE='binlog.000123', MASTER_LOG_POS=12345; START SLAVE;

Monitor with SHOW SLAVE STATUS\G. Cutover at zero lag - point app connections at the new host.

For multi-TB databases, Percona XtraBackup gives a hot, physical backup that streams to the target faster than logical dumps. Install percona-xtrabackup-80, run xtrabackup --backup --target-dir=/backup/source, transfer, run xtrabackup --prepare and --copy-back on the target. Most useful when database is 500GB+.

Run mysqlcheck --all-databases --check-upgrade to verify schema compatibility. Run ANALYZE TABLE on all tables to refresh stats. Set up nightly logical backups (mysqldump) to AnubizHost storage VPS, retained 7 daily / 4 weekly / 3 monthly. Related reading: database hosting, PostgreSQL migration, RDS Postgres migration.