Rory,
First of all, you are correct for wanting to monitor what gets created in you databases. While we all implement steps to prevent mistakes, you cannot assume that the mistakes won't creep in. I do a very similar thing as most of our infrastructure demands UTF8.
The following queries are good for checking stats:
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME, COUNT(0) AS COUNT FROM information_schema.SCHEMATA GROUP BY DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME;
# to filter schema use SCHEMA_NAME in the where clause
SELECT TABLE_COLLATION, COUNT(0) AS COUNT FROM information_schema.TABLES WHERE TABLE_COLLATION IS NOT NULL GROUP BY TABLE_COLLATION;
# to filter schema use TABLE_SCHEMA in the where clause
SELECT CHARACTER_SET_NAME, COLLATION_NAME, COUNT(0) AS COUNT FROM information_schema.COLUMNS WHERE CHARACTER_SET_NAME IS NOT NULL AND COLLATION_NAME IS NOT NULL GROUP BY CHARACTER_SET_NAME, COLLATION_NAME;
# to filter schema use TABLE_SCHEMA in the where clause
The following queries would be good from a cron job that emails you if any results are found:
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE DEFAULT_CHARACTER_SET_NAME NOT LIKE '%utf8%' OR DEFAULT_COLLATION_NAME NOT LIKE '%utf8%';
# to filter schema use SCHEMA_NAME in the where clause
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_COLLATION IS NOT NULL AND TABLE_COLLATION NOT LIKE '%utf8%';
# to filter schema use TABLE_SCHEMA in the where clause
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE CHARACTER_SET_NAME NOT LIKE '%utf8%' OR COLLATION_NAME NOT LIKE '%utf8%';
# to filter schema use TABLE_SCHEMA in the where clause
These queries work for MySQL >= 5.0. If you need help writing the cron job, let me know.
I also have a problem with my developers (mostly the Ruby on Rails team) creating InnoDB tables when they don't need them. To keep this under wraps I monitor the staging database with a cron that calls:
SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE ENGINE = 'InnoDB';
In order to connect to the mysql server you must provide credentials. You can specify them in a configuration file, pass them via the command line, or simply create account that doesn't require credentials.
Of course the no-password option should never be used, the pass-by command line isn't great because anyone who can run ps may be able to see the command line.
The recommended option is to create a mysql configuration file with the credentials in it and then protect that file with filesystem permissions so only your backup user can access it.
You being able to login to the mysql server while logged in interactively as root seems to suggest that you either don't have a root password set, or that you have a configuration file that is not being found by your script. If you have a .my.cnf you may need to manually point to it. If your root account doesn't have a password set then I would strongly encourage you to fix that.
Update (2016-06-29) If you are running mysql 5.6.6 or greater, you should look at the mysql_config_editor tool that allows you to store credentials in an encrypted file. Thanks to Giovanni for mentioning this to me.
Best Answer
If you don't have access to mysql anymore you can reset the mysql root password in a ssh session (as root) by first stopping the mysqld socket process by killing the .pid file. Don't use kill -9.
Now create a text file with the following Mysql query. Each on a new line. Save it to something like /home/me/mysql-init
Now start mysql with the following command:
You just regained access to mysql with the root user and a new root password. Don't forget to delete the mysql-init file.
With this login information you can show all databases on the server. First login in mysql with the new login information:
Now view all databases:
And now you can dump any database you want into a file by using:
Or just all databases: