Count the total number of records on a MySQL database or per table
SQL November 11th, 2010
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
Tags: MySQL
Duplicate a MySQL database on a single line
SQL November 11th, 2010
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.
Tags: MySQL
Install Munin on Ubuntu Server
Linux, Ubuntu April 6th, 2010
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:
- Munin on Ubuntu Documentation
- Monitor Servers and Clients using Munin in Ubuntu
- Munin on Debian Help
Cambiar la contraseña de un usuario en MySQL
SQL July 29th, 2009
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
About
