Tag Archives: MySQL

Count the total number of records on a MySQL database or per table

To count the total number of records on a MySQL database, we could run the following command:

SELECT SUM(TABLE_ROWS) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{tablename}';

If we need to know the number of records per each table, we can run:

SELECT TABLE_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{tablename}';

Source: Stack Overflow

Duplicate a MySQL database on a single line

On this article, it is shown a very convenient way to duplicate a complete MySQL database using a single line of code:

$ mysqladmin create {dest_db_name} -u {username} --password={password} && \
mysqldump -u {username} --password={password} {source_db_name} | \
mysql -u {username} --password={password} {dest_db_name}

Where:

  • {dest_db_name}: Name of the destination database.
  • {source_db_name}: Name of the database to be duplicated.
  • {username}: MySQL username.
  • {password}: MySQL password.

If we need to duplicate it to another server, we would do something like this:

$ mysqladmin create {dest_db_name} -u {dest_username} --password={dest_password} -h {dest_server} && \
mysqldump -u {source_username} --password={source_password} -h {source_server} {source_db_name} | \
mysql -u {dest_username} --password={dest_password} -h {dest_server} {dest_db_name}

Where:

  • {source_db_name}: Name of the source database.
  • {source_server}: Source MySQL server.
  • {source_username}: Source MySQL username.
  • {source_password}: Source MySQL password.
  • {dest_db_name}: Name of the destination database.
  • {dest_server}: Destination MySQL server.
  • {dest_username}: Destination MySQL username.
  • {dest_password}: Destination MySQL password.

Install Munin on Ubuntu Server

Munin is a small tool for monitoring resources on servers. I think it is very useful, specially on small VPS, that needs to save resources. Reports are written as HTML files, so we will need a Web Server like Apache to see this reports.

First, we install it and add some extra plugins:

$ sudo apt-get install munin munin-plugins-extra

Now, we can make some changes to the default configuration, located at /etc/munin/munin.conf. For example, we can change any of the paths where Munin works:

dbdir /var/lib/munin
htmldir /var/www/munin
logdir /var/log/munin
rundir /var/run/munin

Specially, the htmldir path, where all the reports are written to see through Apache or the one you are using. Remember to move the directory /var/www/munin to where you wanted if you change that configuration line. We can protect this directory with an htaccess file to only give access to some users.

We can configure email notifications if a change occur (like from a OK situation to a WARNING). To do this, we just need to uncomment or add the following line:

contact.someuser.command mail -s "Munin notification" your@email.com

By default, Munin will monitor localhost, but we can add other boxes (clients), these machines will only need to install munin-node package.

Then, we can enable some plugins (more plugins can be found here and here). To do this, we need to create a symbolic link per each plugin we want to activate. I’m going to enable apache and mysql modules, but you are free to enable the modules you need:

$ cd /etc/munin/plugins
$ sudo ln -s /usr/share/munin/plugins/apache_* .
$ sudo ln -s /usr/share/munin/plugins/mysql_* .

Each time a module is enable or disable, we need to restart the service, so we can do the following:

$ sudo /etc/init.d/munin-node restart

Also, it is recommended to reassign all files on the htmldir to munin user and group by doing:

$ sudo chown munin.munin -R /var/www/munin

And then, to avoid waiting 5 minutes until munin cron runs again, we force it by:

$ sudo /usr/bin/munin-cron --force-root

If we are not completely satisfied with the default template, we can modify it, they are HTML files (with some minor special template tags). Anyone with some knowledge of HTML and CSS can do that. We can even download other already created templates (I have found some errors on that template’s JavaScript, I hope I’ll get some time to post the modified template, in the meanwhile, if anyone need it, please drop me a line to send you the files).

Finally, as this post is not as complete as I would like, I leave some links that may help:

Cambiar la contraseña de un usuario en MySQL

Hay varios métodos para cambiar la contraseña de un usuario en MySQL, pero este es el que me parece más seguro ya que no deja rastro de la contraseña antigua ni nueva en el historial de comandos (por lo menos en bash).

Primero, nos logueamos al shell de MySQL con el usuario principal:

$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 111
Server version: 5.0.75-0ubuntu10 (Ubuntu)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Escogemos la base de datos mysql:

use mysql;

Cambiamos los registros de la tabla user que almacenan la contraseña del usuario que queramos:

update user set password=PASSWORD("nuevacontraseña") where user='usuario';

Si quisieramos dejar al usuario con la contraseña en blanco, hacemos lo siguiente:

update user set password=NULL where user='usuario';

Limpiamos y volvemos a cargar los privelegios:

flush privileges;

Y salimos:

mysql> quit

tinyint en MySQl y SQL Server

El tipo de dato tinyint tiene una pequeña diferencia entre ambos motores de BD. Lo común es que siempre ocupa un (1) byte (28 valores posibles, osea, 256). Lo que difiere es que en MySQL se puede marcar como signed o unsigned, lo que hace que pueda o no tomar valores negativos. Cuando es signed, el rango de valores posibles va de -128 hasta 127, y en caso de unsigned, puede tomar desde 0 a 255.

En cambio, en SQL Server (por lo menos en las versiones 2005 y 2008) solo puede tomar valores positivos. Por ello, el rango que puede tomar es de 0 a 255.

Pueden ver la documentación de este tipo de dato aquí para SQL Server 2005 y aquí para MySQL.