Get table or DB size im Megabyte

Sometimes it is really useful to know how large a certain database table has become. With the following SQL statement you get a list of all tables of the currently connected database with their rounded size in MB: SELECT table_name AS “Table”, round(((data_length + index_length) / 1024 / 1024), 2) “Size in MB” FROM information_schema.TABLES […]

Get current user, mysql version and other status information

To get information about the currently connected user and more MySQL status information, you can call the following command if you are already logged in: This will give you an output like that: ————– mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1 Connection id: 1044395 Current database: Current user: niceguy@localhost SSL: Not in […]

Check and optimize tables in MySQL

To check and optimize tables of a MySQL database: $> mysqlcheck -os <db_name> -u<username> -p To achieve the same for all databases of the respective MySQL server you can run the following command: $> mysqlcheck -Aos -u<username> -p Further information about check and optimize tables in MySQL To get a few more facts about how […]

Find out where my.cnf is located

To find out where the MySQL configuration (my.cnf) is stored, you can use the strace command: $> strace mysql “;” 2>&1 | grep my.cnf You will get an output showing all the paths the MySQL deamon searches for the my.cnf upon startup like that: stat(“/etc/my.cnf”, 0x7fffecb75210) = -1 ENOENT (No such file or directory) stat(“/etc/mysql/my.cnf”, {st_mode=S_IFREG|0644, st_size=3505, […]