Generating MySQL backup from command-line

Hello guys,

I am not very expert with MySQL and I need some help.

I have a MySQL 5.x database running into a hosting and now I want to bring it to a new instance on my private network. I installed MySQL 8 and use HeidiSQL to export the database from Host targeting to my local instance (running on Windows). It worked perfect. Thanks to the HeidiSQL.

Now I want to create a powershell script to generate a daily mysqldump backup and I have some issues on my script, and I would like to reproduce on the script the exact same mysqldump command that HeidiSQL does when we export it to a file. I was looking into the github to try to understand the mysqldump part, but I am not sure if I am doing the same thing, since it is not generating the same result.

mysqldump 
  --skip-opt 
  --host=127.0.0.1 
  --port=3306 
  --default-character-set=utf8 
  --user=root 
  --password=***** 
  --protocol=tcp 
  --skip-triggers 
  --skip-extended-insert "my_database_schema" > "2022-10-09-database-backup.sql"

After running this and comparing with what HeidiSQL does, I am getting a script with fine encodings but I have some columns in some tables that are TEXT data-type and they are not been generated in my script as HeidiSQL does. Another point, I don't know why, but generating from HeidiSQL the final script for my database has a size os 24MB while generating from my mysqldump statement is 54MB. If I change my column to VARCHAR the scripts generates it perfectly, and I think I am going to do it.

Am I doing something wrong? Can you guys give me some advise? Or even if it is possible to run HeidiSQL from command-line to generate this backup for me?

Thank you!

I would recommend to remove especially the --skip-opt and --skip-extended-insert options, as well as a few others:

mysqldump
  --host=127.0.0.1
  --port=3306
  --user=...
  --password=...
  --result-file=name="2022-10-09-database-backup.sql"
  my_database

Normally, mysqldump creates multiples rows per one INSERT command. Using --skip-extended-insert disables that, resulting in the larger file you mentioned. Also, importing such non-optimized dumps is extremely slow when it has a large number of rows.

The protocol option is tcp by default - leave it away. The character-set should be defined by the tables and columns - also leave that away. And finally the skip-triggers is not what you want, unless you don't want triggers to be dumped.

I have some columns in some tables that are TEXT data-type and they are not been generated in my script as HeidiSQL does

Now what does that mean? How do they look? Probably removing the charset option solves that, not sure.

Hello @ansgar,

Sorry, I was making a confusion about TEXT. Your answer helped me with the correct instruction on how to use mysqldump command line. When I run it using ... > file.sql is generates a large file with some issues on special chars. When using the argument --result-file it generates a smaller file and respect special chars.

I have a new questions just to make sure I am doing the correct thing:

The external database is in MySQL 5 which the charset is latin1 and collation is latin1_swedish_ci. When I create the local database on my MySQL 8 (running on a Windows Server 2012) using HeidiSQL it created using the charset utf8mb4 and collation utf8mb4_0900_ai_ci. The data on the database is based on pt-BR, which contains a lot of special chars like á, é, ã, õ, ç etc. My application connects to this database and it looks good. It can persist data without problems and save the special chars. What do you recommend? Create a local database with the latin charset and collation or it is fine to keep with the utf8's? (I just use portuguese on database, I am not going to persist other languages like chinese, japonese, russian etc).

Thank you very much for the anwser ansgar.

I recommend keeping utf8mb4. Most applications nowadays are utf8-aware. Using latin1 feels like a downgrade.

Ok, I will keep the utf8 then. Something weird is that when I generate the file with mysqldump, and open on Visual Studio Code, it says the generated file is encoded with UTF16 LE but everything looks good, I don't know if it is expected. Should I face a problem with it if I need to restore the dump?

Thanks Ansgar, just sent you a small donation (from Brazil). Att,

I would test the restore process once, at least. I cannot tell you from here if that succeeds. It should, but don't rely on me :)

Thanks for your donation!