2
  • My development environment has XAMPP, which means I have MariaDB installed.
  • The production has MySQL 8 installed.
  1. When I transfer new schema changes from MariaDB to MySQL, it works mostly. I have to fiddle sometimes.

  2. But my main problem is when I backup the MySQL schema and data and try to restore it to my PC, well I have given up. It requires too many changes. Many issues are related to character sets.

Is there something I can do to make #2 easier? Maybe some rules about what to avoid on MySQL.

Rohit Gupta
  • 2,116
  • 8
  • 19
  • 25

2 Answers2

2

Yes, if you can use mariadb-dump or MariaDB's mysqldump to backup the MySQL schema instead of MySQL's mysqldump, then you should be able to import that dump/SQL file into MariaDB.

mariadb-dump/mysqldump can be run against a remote host, so you don't have to install it on the database server itself, although you'll need to have remote access to the 3306 port (or whichever other port MySQL runs on).

XAMPP seems to still have an old version of MariaDB (10.4), so therefore the utility is still called mysqldump.

I don't have Windows, but I downloaded the XAMPP zip file for Windows, and based on that I think a dump command would look something like:

cd xampp\mysql\bin
mysqldump.exe -h your-remote-hostname --single-transaction --default-character-set=utf8mb3 -u admin_backup -p > D:\mybackup.sql

Tweak that as needed.

(If the mysqldump that comes with the current version of XAMPP doesn't work with your MySQL 8.0 server, then you could try installing a newer version of MariaDB server (e.g. 10.11 or 11.4) locally on your Windows machine and use its mariadb-dump instead. Anecdotally, I was able to successfully dump the MySQL "sakila" database from a MySQL server 8.0.41 using mariadb-dump from MariaDB 10.11, although this was on Linux.)

Other potential solutions that might work would be to try using mysqldump from an older MySQL version, such as 5.7 or even 5.6. Or try using the backup feature in a tool such as HeidiSQL.

dbdemon
  • 6,964
  • 4
  • 21
  • 40
0

On @Akina's suggestion I have decided to try an use MAMP instead of XAMPP.

I think, long term this will be better.

I did have to do a conversion. I will write a script to do it.

-- From:

CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci

-- To:

CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
Rohit Gupta
  • 2,116
  • 8
  • 19
  • 25