How to restore sql database from /var/lib/mysql/* files on another mysql server?

Question

MySQL

The task is to make a copy of a broken mysql database.
When trying to create a mysqldump, the database gives an error: Illegal mix of collations (utf8_unicode_ci,COERCIBLE) and (utf8_general_ci, COERCIBLE
) for operation 'locate'

directives /var/lib/mysql/
Now the question is how to create an sql file from the "cold backup" files or how to deploy this database on another mysql server?

pump out the beaten

Answer 1

You need to make a normal dump.
Or, as they said in the comments, you will have to manually create all the tables first

Answer 2

You need to install mysql of the same or higher version on another server, transfer the files, make sure the permissions are set correctly and start mysql.
But this is unlikely to help you "repair" the base. And not only one database will be copied, but all databases and users.
Well, to remove the dump, in the same way, will not work, as on the source server.
You really need to see where the problem with collations is and fix it.
upd: Just in case: I am writing about copying the entire /var/lib/mysql/, not a separate folder with the database.

Answer 3

When trying to create a mysqldump, the database gives an error:
Illegal mix of collations (utf8_unicode_ci,COERCIBLE) and (utf8_general_ci,COERCIBLE) for operation 'locate'

Adjust @@collation_connection.

The task is to make a copy of a broken mysql database ... how to create an sql file from the "cold backup" files or how to deploy this database on another mysql server?

What about the meaning? if you connect data files in a new location, the errors will not go anywhere.
And so - to create tables in a new location. The structure must match up to commas, and the server version too. The most reasonable thing is to make a backup of a purely structure, without data, and deploy it. And then DISCARD / IMPORT TABLESPACE, or replacing files when the service is stopped and then starting with treatment and overwriting metadata and statistics.