First of all, don't just take my word for it! Test my suggestion out with this:
select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' charset=utf8;') from information_schema.TABLES WHERE TABLE_SCHEMA != 'information_schema' limit 10; select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' alter column ',COLUMN_NAME,' charset=utf8;') from information_schema.COLUMNS WHERE TABLE_SCHEMA != 'information_schema' limit 10;
If you feel good with the outcome of that, remove the limit clauses and save the output to an SQL script or, get fancy and pipe the output directly to mysql similar to what I demonstrate here. That would look like this:
mysql -B -N --host=prod-db1 --user=admin --password=secret -e "select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' charset=utf8;') from information_schema.TABLES WHERE TABLE_SCHEMA != 'information_schema'; select CONCAT('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' alter column ',COLUMN_NAME,' charset=utf8;') from information_schema.COLUMNS WHERE TABLE_SCHEMA != 'information_schema';" | mysql --host=prod-db1 --user=admin --password=secret
When you start thinking about using valid SQL to generate valid SQL, it changes the whole game. You will be amazed by how many uses you find for it.
If your end goal is to separate the servers then I would suggest running a separate instance on a different port and using standard replicaton between the two
quick google turns out this
alternatively you can use triggers to replicate, something along the lines of
CREATE TRIGGER replicatetable AFTER INSERT ON db1.table
FOR EACH ROW BEGIN
INSERT INTO db2.table(field1,field2, ...) values (db1.table.field1,db1.table.field2, ...);
END;
similar for UPDATE and DELETE
Best Answer
On MariaDB there are a total of 28 grants:
To show all users with all grants:
To check if it's true, you can check with result from
and compare :)