List MySql database size with a query

The query:

SELECT table_schema "Data Base Name",
sum( data_length + index_length ) / 1024 /
1024 "Data Base Size in MB",
sum( data_free )/ 1024 / 1024 "Free Space in MB"
FROM information_schema.TABLES
GROUP BY table_schema ;

The mysql console:

mysql> SELECT table_schema "Data Base Name",
-> sum( data_length + index_length ) / 1024 /
-> 1024 "Data Base Size in MB",
-> sum( data_free )/ 1024 / 1024 "Free Space in MB"
-> FROM information_schema.TABLES
-> GROUP BY table_schema ;
+--------------------+----------------------+------------------+
| Data Base Name | Data Base Size in MB | Free Space in MB |
+--------------------+----------------------+------------------+
| information_schema | 0.00878906 | 0.00000000 |
| mysql | 0.61361027 | 0.00000000 |
| performance_schema | 0.00000000 | 0.00000000 |
| test | 0.01562500 | 10.00000000 |
+--------------------+----------------------+------------------+
4 rows in set (0.00 sec)

Install MySql generic binary on Linux

I want to have the latest GA version of MySql, the 5.5.x instead of the old 5.1 release. 5.5.x has many performance improvements, from x2 to x8, according to an article of Guiseppe Maxia, a MySql expert. Go to the download page and get the tar gizipped binary version of Mysql. I downloaded this one:

# apt-get install libaio1
# wget http://dev.mysql.com/get/Downloads/MySQL-5.5/mysql-5.5.15-linux2.6-x86_64.tar.gz/from/http://mysql.mirrors.ovh.net/ftp.mysql.com/
# mv index.html mysql.tar.gz
# tar xzfv mysql.tar.gz
# mv mysql-5.5.15-linux2.6-x86_64/ mysql-5.5.15
# mv mysql-5.5.15/ /usr/local/
# cd /usr/local/
# ln -s mysql-5.5.15/ mysql
# cd mysql
# chown -R mysql .
# chgrp -R mysql .
# scripts/mysql_install_db --user=mysql
# cp support-files/mysql.server /etc/init.d/mysql.server
# cp support-files/my-medium.cnf /etc/my.cnf
# chmod +x /etc/init.d/mysql.server
# update-rc.d mysql.server defaults
# /etc/init.d/mysql.server start
Starting MySQL.. *
# /etc/init.d/mysql.server restart
# bin/mysql -uroot

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set password=PASSWORD("your_passwd") where user='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

Done! Simple isn't it?

Reference: http://dev.mysql.com/doc/refman/5.5/en/binary-installation.html

conman: automate your server configuration

Probably you already know about Puppet and Chef and probably you felt overwhelmed when learning one of the the two server configuration management. I want to show you conman a simpler and easier approach then Puppet and Chef. Of course it has very basic features but it could be easily extended. So install conman:

gem install conman

conman has ingredients and recipes. Recipes contains ingredients. An ingredient is a class extending the Ingredient class having methods with commands. Here you can see and example of a class which can install or remove the apache2 package:

class Apache2Ingredient < Ingredient
   def install
       `sudo apt-get install apache2`
   end

   def remove
   `sudo apt-get remove apache2`
   end
end

The you can include you new ingredient in your recipe:

class MyRecipe < Recipe
     apache2 :install
end

apache2 method search and load the Apache2Ingredient, then calls the install method. Simple!

You can run the recipe using the command line:

conman -i /path/to/ingredients /path/to/recipes/my_recipe.rb

or using Ruby:

Conman.init :ingredients => '/path/to/ingredients/' Conman.run '/path/to/recipes/my_recipe.rb'

conman does not know about packages, apt-get, rpm... but I think it can be easily implemented. Another important thing that is missing is the possibility to run remotely the recipes; a SSH session using a Ruby library could do the trick (execute the command remotely).

Link: https://github.com/moocode/conman