database


Using automysqlbackup to create regular backups of your database

This works on Debian and Ubuntu, but also on other distributions. The package can be installed via apt: apt-get install automysqlbackup For yum (Fedora, CentOS, RedHat, etc.) there's usually also a package provided. Next you need to edit the configuration file /etc/default/automysqlbackup. There are other options, the most important ones are: USERNAME=root PASSWORD=…….. DBHOST=localhost DBNAMES=`mysql –defaults-file=/etc/mysql/debian.cnf […]


MySQL Query to calculate DB and Table sizes

Calculate sizes of all your databases in MB: SELECT table_schema "DB Name", SUM( data_length + index_length) / 1024 / 1024 "DB Size" FROM information_schema.TABLES GROUP BY table_schema; Calculate table sizes for a specific database: SELECT TABLE_NAME, table_rows, data_length, index_length,  round(((data_length + index_length) / 1024 / 1024),2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = "PUT_YOUR_DATABASE_NAME_HERE";