If you, like me, do not trust automation, this is how I have handled the problem.
First Stop digging!
Start with altering the default charset of new tables by changing the DB definition(like in all other answers):
ALTER DATABASE database_name
CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
Then generate sql to change the default charset for new columns of all existing tables:
SELECT concat("ALTER TABLE `",table_schema,"`.`",table_name,"` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_swedish_ci;") as _sql
WHERE table_schema like "database_name" and TABLE_TYPE="BASE TABLE"
GROUP BY table_schema, table_name ;
Now we can handle the "legacy"
List character datatypes you are using:
select distinct data_type from information_schema.columns where table_schema = "database_name" and CHARACTER_SET_NAME is not null;
For me that list was "varchar" and "text"
List character_SETS_ in use:
select distinct character_set_name from information_schema.columns where table_schema = "database_name";
This gives me "utf8", "latin1", and "utf8mb4" which is a reason I do not trust automation, the latin1 columns risk having dirty data.
Now you can make a list of all columns you need to update with:
select table_name, column_name, data_type, character_set_name, collation_name
where table_schema = "database_name" and CHARACTER_SET_NAME is not null AND CHARACTER_SET_NAME <> "utf8mb4"
group by table_name, , data_type, character_set_name, collation_name;
Tables containing only utf8 or utf8mb4 could be converted with "CONVERT TO CHARACTER SET" as Mathias and MrJingles describes above, but then you risk MySQL changing the types for you, so you may be better of running "CHANGE COLUMN" instead since that gives you control of exactly what happens.
If you have non-utf8 columns these questions may give inspiration about checking the columns data: https://stackoverflow.com/q/401771/671282
Since you probably know what you expect to have in most of the columns something like this will probably handle most of them after modifying the non-ascii chars allowed to suit your needs:
SELECT distinct section FROM table_name WHERE column_name NOT REGEXP '^([A-Za-z0-9åäöÅÄÖ&.,_ -])*$';
When the above did not fit I used the below that have a bit "fuzzier" maching:
CONVERT(CONVERT(column_name USING BINARY) USING latin1) AS latin1,
CONVERT(CONVERT(column_name USING BINARY) USING utf8) AS utf8
WHERE CONVERT(column_name USING BINARY) RLIKE CONCAT('[', UNHEX('C0'), '-', UNHEX('F4'), '][',UNHEX('80'),'-',UNHEX('FF'),']') limit 5;
This query matches any two characters that could start an utf8-character, thus allowing you to inspect those records, it may give you a lot of false positives.
The utf8 conversion fails returning null if there is any character it can not convert, so in a large field there is a good chance of it not being useful.