Today i have found out that one of the DB Servers showing special characters like "â€¢" instead of bullet points or other normal characters, although the same database is working find in some other servers.
First i started by checking the main server character set by running below query:
SHOW VARIABLES LIKE 'character_set_server'
SHOW VARIABLES LIKE 'collation%';
SHOW VARIABLES LIKE 'c%'; SHOW CREATE TABLE TableName ;
You can use below to check specific database if it has special configuration:
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '
The result came utf8 and collation is utf8_general_ci which they look fine and that what you are looking for, if it's other charset then you may consider change to utf8, or at least similar to the other server which is working fine, for more charset diagnosis you can check this too.
Second, check the MySQL configuration file my.cnf for all charset configuration like below ones:
collation_server = utf8_general_ci
character_set_server = utf8
init_connect ='SET NAMES utf8'
If changed then restart the mysql service by running one of the below:
service mysql restart
Third, be sure that while you doing the mysqldump or restore not to use special parameters to force the Latin or other charset like below example:
Also you may try -r instead of > while doing the mysqldump like below:
mysqldump -uroot -p database -r utf8.dump