mysql administration notes

MySQL server on debian administration notes .

Install on debian
debian-sys-maint user
Passwordless administration commands or SQL statements
Configuration files and MySQL system variables
Using the standard client for an elementary database exploration.
Binary Backups
mysqldump --Logical Backups.
Master-Slave Replication.

Now, with the debian MySQL server packet installation
# apt-get install mysql-server
you get the MySQL server version 5.5 along with its prerequisites, basic MySQL tools like the standard MySQL client and the Perl DBI, and some other stuff like mailx.
heirloom-mailx libaio1 libdbd-mysql-perl libdbi-perl libhtml-template-perl
libmysqlclient18 mysql-client-5.5 mysql-common mysql-server-5.5 mysql-server-core-5.5

The debian-sys-maint user.

The debian package adds the debian-sys-maint MySQL user that can do pretty much everything if logging in locally.
mysql> show grants for 'debian-sys-maint'@'localhost';
| Grants for debian-sys-maint@localhost                                                                                                              |
| GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY PASSWORD '*0123456789ABCDEF12346789082F1970A47EDCBA' WITH GRANT OPTION |
1 row in set (0.00 sec)

mysql> select Host,Super_priv,Create_priv,Grant_priv,Drop_priv from mysql.user where user='debian-sys-maint';
| Host      | Super_priv | Create_priv | Grant_priv | Drop_priv |
| localhost | Y          | Y           | Y          | Y         |
1 row in set (0.01 sec)

The debian-sys-maint user has a "random" password stored at /etc/mysql/debian.cnf.
# ls -l /etc/mysql/debian.cnf 
-rw------- 1 root root 333 Oct 23 16:04 /etc/mysql/debian.cnf

Skip the Password Prompts.

The debian-sys-maint user is used by system scripts, but it is convenient for an administrator to use it in his commands and scripts.
# mysqladmin --defaults-file=/etc/mysql/debian.cnf create yo
# mysqladmin --defaults-file=/etc/mysql/debian.cnf drop yo
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'yo' database [y/N] y
Database "yo" dropped

Another way to skip the password prompt when running a SQL command.
# mysql -u root -p"password" -e "command;"
As far as I know since at least version 5.1.41-3 and upwards commands like the above, do not reveal your password in the current processes snapshot (ps)
root     30510  0.0  0.1  40280  2696 pts/0    S+   07:41   0:00          \_ mysql -u root -px xxxxxx

Configuration files and MySQL system variables.

Default options are read from the following files in the given order: /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

Within the MySQL configuration or option files we may define groups for which we want to set options. A Group name often matches a MySQL helper program name. An options group named "group" starts with [group] in the options or configuration files.

The debian debian-package puts the configuration files in /etc/mysql.

The database table and DB specific options files are stored in /var/lib/mysql

An easy way to display MySQL system variables and their values.
# mysqladmin --defaults-file=/etc/mysql/debian.cnf variables

A quick reference of the MySQL daemon options along with some MySQL system variables and their values.
# mysqld --h -v

MySQL has many logging options (look for log in the MySQL system variables).
General query log keeping is expensive and disabled by default. It may may be enabled permanently in the configuration file /etc/mysql/my.cnf
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1

As of version 5.1, general log keeping may be enabled or disabled at runtime.
# mysql -u root -p"root_paswd"
mysql> show variables like 'general%';
| Variable_name    | Value                         |
| general_log      | OFF                           |
| general_log_file | /var/lib/mysql/anaxagoras.log |
2 rows in set (0.00 sec)
mysql> SET GLOBAL general_log=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'general%';
| Variable_name    | Value                         |
| general_log      | ON                            |
| general_log_file | /var/lib/mysql/anaxagoras.log |
2 rows in set (0.00 sec)
mysql> SET GLOBAL general_log=0;
Query OK, 0 rows affected (0.08 sec)
mysql> quit
# cat /var/lib/mysql/anaxagoras.log 
/usr/sbin/mysqld, Version: 5.5.40-0+wheezy1 ((Debian)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
141026 13:21:22    37 Query show variables like 'general%'
141026 13:24:35    37 Query SET GLOBAL general_log=0

mysqladmin and a few usage examples.
mysqladmin is an agent-client suitable for administering MySQL servers.

Check whether the server is alive.
# mysqladmin --defaults-file=/etc/mysql/debian.cnf ping
mysqld is alive

See status.
# mysqladmin --defaults-file=/etc/mysql/debian.cnf status
Uptime: 10189  Threads: 2  Questions: 172  Slow queries: 0  Opens: 171  Flush tables: 1  Open tables: 41  Queries per second avg: 0.016

To view an extended status try.
# mysqladmin --defaults-file=/etc/mysql/debian.cnf extended-status |less

List processes.
# mysqladmin --defaults-file=/etc/mysql/debian.cnf processlist
| Id | User             | Host      | db | Command | Time | State | Info             |
| 40 | root             | localhost |    | Sleep   | 1940 |       |                  |
| 47 | debian-sys-maint | localhost |    | Query   | 0    |       | show processlist |

Kill process with ID 40 and show processes (proc).
# mysqladmin --defaults-file=/etc/mysql/debian.cnf kill 40 proc
| Id | User             | Host      | db | Command | Time | State | Info             |
| 46 | debian-sys-maint | localhost |    | Query   | 0    |       | show processlist |

Reload the MySQL server mysql database grant tables.
# mysqladmin --defaults-file=/etc/mysql/debian.cnf reload
# mysqladmin -u root -p"root_passwd" flush-privileges

Clear MySQL status variables in a MySQL instance running on host
# mysqladmin -h -u root -p"0210_root_passwsd" flush-status

Shutdown MySQL server.
# mysqladmin --defaults-file=/etc/mysql/debian.cnf ping
mysqld is alive
# mysqladmin --defaults-file=/etc/mysql/debian.cnf shutdown
# mysqladmin --defaults-file=/etc/mysql/debian.cnf ping 2>/dev/null
# echo $?

Start MySQL server.
# /etc/init.d/mysql start
[ ok ] Starting MySQL database server: mysqld ..
[info] Checking for tables which need an upgrade, are corrupt or were 
not closed cleanly..

Use the standard client in an elementary database exploration.

Log in to MySQL as the root using the standard mysql client.
# mysql -u root -p"root_passwd"

Show databases.
mysql> show databases;
| Database           |
| information_schema |
| foodb              |
| mysql              |
| performance_schema |
4 rows in set (0.00 sec)
The mysql , information_schema and performance_schema databases come with the MySQL server and they are used by the MySQL server in its operation. The mysql database holds information about users,servers,plugins,timezone,etc and the users may write to it (eg: this is how you add a MySQL user ). The information_schema (read-only to the users) stores information about all the other databases that MySQL maintains. The performance_schema database is used by the MySQL system to provide low level execution monitoring.

Use the foodb database.
mysql> use foodb;

Show all tables in the foodb database.
mysql> show tables;
| Tables_in_foodb  |
| exits            |
1 row in set (0.00 sec)

Describe the schema of the 'exits' table in the 'foodb' DB.
mysql> describe exits;
| Field      | Type             | Null | Key | Default | Extra |
| su         | int(10) unsigned | NO   | PRI | NULL    |       |
| first_test | datetime         | YES  |     | NULL    |       |
| last_test  | datetime         | YES  |     | NULL    |       |
3 rows in set (0.00 sec)

Find out the number of rows in the table exits.
mysql> SELECT COUNT(*) FROM exits;
| COUNT(*) |
|   260472 |
1 row in set (0.00 sec)

mysql> quit

Binary Database Backup.
If a database contains only MyISAM tables (*.frm,*.MYD,or *.MYI) and the db.opt file you may simply copy it.

Lock tables before copying.
# mysql --defaults-file=/etc/mysql/debian.cnf -e "LOCK TABLES foodb.exits READ;"

The database tables are in /usr/lib/mysql. To copy.
# cp -rp /var/lib/mysql/foodb /bak/mysql/foodb

Unlock tables after copying.
# mysql --defaults-file=/etc/mysql/debian.cnf -e "UNLOCK TABLES;"

To restore the foodb to the same or another MySQL server
copy it to /var/lib/mysql.
# cp -rp  /bak/mysql/foodb /var/lib/mysql/foodb
If you are copying to another MySQL server and you are missing or you do not want to mess with the old /var/lib/mysql/mysql you may want to create a user for the foodb.
mysql> grant all on foodb.* to foodbuser;
mysql> set password for foodbuser = password('foodbuser_passwd');

When I did copy the foodb DB directory to another MySQL server everything worked fine, the origin MySQL server was version 5.1.41-3, and the destination MySQL was version 5.5.40-0.


A more robust way for doing binary backups of MyISAM tables is mysqlhotcopy --a Perl script that comes with the standard MySQL distribution.
Eg: to copy foodb to another MySQL server using mysqlhotcopy.
dest# mkdir /var/lib/mysql/foodb
orig#mysqlhotcopy --method='scp' --user=root --password=mysqlrootpasswd foodb root@

mysqldump --Logical Backups

Good for all storage engines. Logical Backups are text files that contain SQL statements used to restore schemata and data.

Dump backup of foodb in a file.
orig# mysqldump -u root -p"root_passwd" foodb > foodb.sql

Restore the foodb to another MySQL server.
dest# mysqladmin --defaults-file=/etc/mysql/debian.cnf create foodb
dest# mysql --defaults-file=/etc/mysql/debian.cnf foodb < foodb.sql

Master-Slave Replication

Prepare Master ( anaxagoras ) and Slave ( democritus ) MySQL servers.
Enable binary logging, set a server ID number and listen on all interfaces.

Master --host anaxagoras
add in /etc/mysql/my.cnf -configuration group mysqld
server-id               = 11
log_bin                 = /var/log/mysql/mysql-bin.log
bind-address            =
sync_binlog             = 1  
binlog_do_db            = foodb

Restart MySQL
anaxagoras# service mysql restart

Punch Firewall holes.

Slave --host democritus
Add in /etc/mysql/my.cnf -configuration group mysqld
server-id               = 12
log_bin                 = /var/log/mysql/mysql-bin.log
binlog_do_db            = foodb

Restart MySQL
democritus# service mysql restart 

Create a user for replication on the master.
anaxagoras# mysql -u root -p"root_passwd"
mysql> CREATE USER 'repuser'@'';
mysql> SET PASSWORD FOR 'repuser'@'' = password('repuser_password');
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repuser'@'';

Obtain master's binary log coordinates.
mysql> USE foodb
Query OK, 0 rows affected (0.17 sec)

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
| mysql-bin.000003 |      107 | foodb        |                  |
1 row in set (0.00 sec)

Copy the database with mysqldump , mysqlhotcopy , a cold copy, or something else.

Cold Super Copy.
Copy raw data files to the slave MySQL.
If you are using any InnoDB tables, shutdown MySQL
anaxagoras# mysqladmin --defaults-file=/etc/mysql/debian.cnf shutdown 
anaxagoras# rsync -avz -e ssh /var/lib/mysql/ root@democritus:/var/lib/mysql
anaxagoras# scp /etc/mysql/debian.cnf root@democritus:/etc/mysql/

Remove master' s lock.

Configure the slave MySQL server and start the replication.
democritus# mysql -u root -p"root_passwd"
mysql> CHANGE MASTER TO MASTER_HOST='anaxagoras', MASTER_USER='repuser', MASTER_PASSWORD='repuser_password', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=107;

See the status in the slave MySQL server.

mysqlcheck --A MySQL tables maintance program.

Check all tables in all databases.
# mysqlcheck --defaults-file=/etc/mysql/debian.cnf -A

Analyze tables in database foodb.
# mysqlcheck --defaults-file=/etc/mysql/debian.cnf --analyze foodb

Optimize tables in database foodb.
# mysqlcheck --defaults-file=/etc/mysql/debian.cnf -0 foodb

Repair tables in foodb. Make backups of the tables before 'repairing' them.
# mysqlcheck --defaults-file=/etc/mysql/debian.cnf --debug-info --auto-repair foodb

MySQL administration notes

DELETE tun interfaces

A quick note on killing a bug in a previous recipe and deleting protocol 41 tunnel interfaces in linux.

datun is an interface used as one of the edges in a 6in4 tunnel set with
ip tunnel add datun mode sit remote local ttl 64 
ip link set datun up

seen as
# ifconfig datun
datun Link encap:IPv6-in-IPv4 

and taken down with
# ip link set datun down
at the 6in4 tunnels to the IPv6 Internet how-to, even in places we needed to delete the tunnel instead of putting it down, causing all kinds of errors and confusion.

To delete a tunnel interface.
ip tunnel del datun

Instead of "restarting" the 6in4 tunnel it may be better to destroy it and set it again.
# ip tunnel del datun
# /etc/network/if-up.d/

delete tun interface

tripwire notes

Yet another tripwire ( as in the open source file integrity checker for Unix Systems ) how-to for debian , like tripwire ... but, hopefully, easier to follow.

Assuming you trust your repositories, your distribution, etc
# apt-get install tripwire
and then click the no, no, and OK buttons.

Ideally, the tripwire binaries and the tripwire database are stored in a read only medium that can be mounted as read-write for updates. I would use an SD card or some other medium that I can set "mechanically" to read-only. Some administrators put the binaries and the DB in an NFS.I think that putting the binaries and the DB in an NFS would increase the attack surface. If you are not in the mood or do not have the resources to take the extra steps to secure further the integrity of the tripwire binaries and the tripwire DB at least save copies of the files and their cryptographically secure checksums in other hosts.

In debian the tripwire binaries are statically linked and located in /usr/sbin and the DB is located in /var/lib/tripwire.
# sha256sum /usr/sbin/tripwire |tee ~/twsums
0e4791bb58dfc4095dba902621b72111d61bf1838d77aff4ae00d3c7432d5739  /usr/sbin/tripwire
# sha256sum /usr/sbin/tw* |tee -a ~/twsums
bc01ac66aa421d2e5324983150bea573b2e2d3ee004293501b0dcc4ce1560898  /usr/sbin/twadmin
e1b097eaf28f3ec54114cba7cc82a1ab4122a9fb82590422d9820711c884e5e9  /usr/sbin/twprint
# sha256sum /usr/sbin/siggen |tee -a ~/twsums 
e5e72b264f9b4fa86aa88e0f893b6031457e30b510f28bcb31ea1296b38566bd  /usr/sbin/siggen

Tripwire uses $HOSTNAME a lot in the configuration and policy files. Make sure that you are happy with hostname, if not change the hostname before continuing the tripwire configuration.

Create a site key.
# cd /etc/tripwire/
# twadmin --generate-keys --site-keyfile site.key
# chmod 400 site.key
The site key is used to secure the integrity of the tripwire configuration files.

Create a local key.
# twadmin --generate-keys --local-keyfile `hostname`-local.key
# chmod 400 *cal.key
The local key is used to protect the integrity of the local tripwire database.

Create and sign tw.cfg --the tripwire configuration file.
# stor twcfg.txt
# vi twcfg.txt 
# twadmin --create-cfgfile --cfgfile tw.cfg --site-keyfile site.key twcfg.txt
Please enter your site passphrase: 
Wrote configuration file: /etc/tripwire/tw.cfg

Create and sign tw.pol --the tripwire policy file.
# stor twpol.txt
# vi twpol.txt
# twadmin --create-polfile --cfgfile tw.cfg --site-keyfile site.key twpol.txt
Please enter your site passphrase: 
Wrote policy file: /etc/tripwire/tw.pol

Make the policy and configuration files readable and writable only by the root user.
# chmod 600 *txt
# chmod 600 *cfg
# chmod 600 *pol

Initialize the tripwire database.
# tripwire --init
Please enter your local passphrase: 
Parsing policy file: /etc/tripwire/tw.pol
Generating the database...
*** Processing Unix File System ***
# ...
Wrote database file: /var/lib/tripwire/anaxagoras.twd
The database was successfully generated.

Test that tripwire can send email.
# tripwire --test --email

Check integrity and produce report.
# tripwire --check

View report.
# twprint -m r --twrfile /var/lib/tripwire/report/hostname-latest.twr |less

It is highly unlikable that you are using all the files in the "Root config files" rulename in the debian default twpol.txt. Also you may want to adjust the "Devices & Kernel information" rulename since /proc (meaning recursive /proc/*) may be too much to track in normal servers.

Adjust the tripwire policy and initialize a new tripwire database.
# stor twpol.txt
# vi twpol.txt
# twadmin -m P -S site.key twpol.txt 
# tripwire --init

Check for integrity, create a report and OK changes if any.
Once the editor opens look for [x] and delete the x if you are not OK with that change.
# tripwire --check --interactive
Integrity check complete.
Please enter your local passphrase: 
Wrote database file: /var/lib/tripwire/anaxagoras.twd

You may enter an `interactive` mode from a report as well. eg:
# tripwire --update --twrfile /var/lib/tripwire/report/hostname-date-time.twr
and again look for [x] and delete the x if you are not OK with that change.

Email alerts.
To email an alert we need to add an emailto definition to at least one rulename.
So we need to update the tripwire policy. eg:
# Critical Libraries
  rulename = "Root file-system libraries",
  severity = $(SIG_HI),
  emailto = root,
  emailto = systembot@ares.ipduh.rox
        /lib                    -> $(SEC_BIN) ;

If /lib is changed an alert will be sent to root and systembot@ares.ipduh.rox.

Check integrity, produce report and email alerts.
# tripwire --check --email-report

The debian package sets a cronjob that creates reports and emails alerts daily.
#!/bin/sh -e


[ -x $tripwire ] || exit 0

umask 027

$tripwire --check --quiet --email-report

View the tripwire database.
# twprint -m d --print-dbfile |less

View tripwire information for a file eg:/var/test
# twprint -m d --print-dbfile /lib/test

The system used in this how-to.
ii  tripwire                                      amd64        file and directory integrity checker
# cat /etc/issue /etc/debian_version 
Debian GNU/Linux 7 \n \l


The Design and Implementation of Tripwire: A File System Integrity Checker

Tripwire how-to

apache disable SSLv3

Notes on disabling SSLv3 in apache.

Test if SSLv3 is available.
$ openssl s_client -connect -ssl3

In Debian SSLv2 is disabled by default but SSLv3 is available.

# grep SSLProtocol /etc/apache2/mods-available/ssl.conf
SSLProtocol all -SSLv2

To disable SSLv3 add '-SSLv3' in /etc/apache2/mods-available/ssl.conf
# vi /etc/apache2/mods-available/ssl.conf

If you are using SSL Virtual Hosts you may need to add
SSLProtocol All -SSLv2 -SSLv3
in each VirtualHost definition.

Restart Apache
# /etc/init.d/apache2 restart

Test again if SSLv3 is disabled.
$ openssl s_client -connect -ssl3
140330958718632:error:14094410:SSL routines:SSL3_READ_BYTES:sslv3 alert handshake failure:s3_pkt.c:1258:SSL alert number 40
140330958718632:error:1409E0E5:SSL routines:SSL3_WRITE_BYTES:ssl handshake failure:s3_pkt.c:596:

apache disable SSLv3

libguestfs notes

Libguestfs basics.

# apt-get install libguestfs-tools 
# apt-get install guestfish


The libguestfs Filesystem Interactive SHell.

An example: explore, read and write to disk image file within the libguestfs VM.
# guestfish --rw -a /home/vm/anaxagoras.qcow2
> run
 100% ⟦▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓⟧ 00:00
> list-filesystems
/dev/vda1: ext4
/dev/vda2: unknown
/dev/vda5: swap
> mount /dev/vda1 /

Add a file to the disk image file system.
> touch /etc/guestfish_play
> edit /etc/guestfish_play
> quit


Display files in a virtual machine.

# virsh list
 Id    Name                           State
 9     anaxagoras                     running

# virt-cat anaxagoras /etc/issue
Debian GNU/Linux 7 \n \l
# virt-cat anaxagoras /etc/hostname


Mount a guest filesystem on the host using FUSE and libguestfs

# apt-get install guestmount

Mount rw a filesystem contained in a disk image file.
# mkdir /mnt/anax
# guestmount -a /home/vm/anaxagoras.qcow2 -m /dev/vda1 --rw /mnt/anax/

# cat /mnt/anax/etc/guestfish_play
# echo "hi kosme" > /mnt/anax/etc/guestfish_play
# mv /mnt/anax/etc/guestfish_play /mnt/anax/etc/guestmount_play
# umount /mnt/anax

guestmount is and looks traditionally-scriptable. However, guestfish is as or more scriptable. Also, libguestfs has cute C , Perl and Python APIs.


List free space on virtual filesystems.
# virt-df anaxagoras
Filesystem                           1K-blocks       Used  Available  Use%
anaxagoras:/dev/sda1                  19751804     840608   17907832    5%


List filesystems, partitions, block devices, LVM in a virtual machine or a disk image file.

# virt-filesystems --long --parts --blkdevs -a /home/vm/anaxagoras.qcow2 -h
Name       Type       MBR  Size  Parent
/dev/sda1  partition  83   19G   /dev/sda
/dev/sda2  partition  05   1.0K  /dev/sda
/dev/sda5  partition  82   880M  /dev/sda
/dev/sda   device     -    20G   -


# virt-filesystems --long -h --all -a anaxagoras.qcow2 
Name       Type        VFS      Label  MBR  Size  Parent
/dev/sda1  filesystem  ext4     -      -    19G   -
/dev/sda2  filesystem  unknown  -      -    1.0K  -
/dev/sda5  filesystem  swap     -      -    880M  -
/dev/sda1  partition   -        -      83   19G   /dev/sda
/dev/sda2  partition   -        -      05   1.0K  /dev/sda
/dev/sda5  partition   -        -      82   880M  /dev/sda
/dev/sda   device      -        -      -    20G   -


List filesystems in a virtual machine or disk image.

# virt-list-filesystems anaxagoras.qcow2 
# virt-list-filesystems anaxagoras


Resize a virtual disk image file.

Eg: Expand the 20GB anaxagoras qcow2 disk image file to a 30GB qcow2 disk image file.
# truncate -r anaxagoras.qcow2 anaxagoras30G.qcow2
# truncate -s +10G anaxagoras30G.qcow2
# virt-resize --expand /dev/sda1 anaxagoras.qcow2 anaxagoras30G.qcow2 
Examining anaxagoras.qcow2 ...

Summary of changes:

/dev/sda1: This partition will be resized from 19.1G to 29.1G.  The 
    filesystem ext4 on /dev/sda1 will be expanded using the 'resize2fs' 

/dev/sda2: This partition will be left alone.

Setting up initial partition table on anaxagoras30G.qcow2 ...
Copying /dev/sda1 ...
 100% ⟦▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓⟧ 00:00
Copying /dev/sda2 ...
 100% ⟦▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓⟧ 00:00
Expanding /dev/sda1 using the 'resize2fs' method ...

Resize operation completed with no errors.  Before deleting the old 
disk, carefully check that the resized disk boots and works correctly.

Test resized image.
# cd /etc/libvirt/qemu/
# stor anaxagoras.xml
# virsh
virsh # edit anaxagoras
virsh # define anaxagoras.xml
virsh # start anaxagoras
virsh # quit
# ssh anaxagoras
root@anaxagoras:~# df -h
Filesystem                                              Size  Used Avail Use% Mounted on
rootfs                                                   29G  822M   27G   3% /
udev                                                     10M     0   10M   0% /dev
tmpfs                                                   202M  172K  202M   1% /run
/dev/disk/by-uuid/8ca4bd34-120c-45ff-bd0b-86d8de552d10   29G  822M   27G   3% /
tmpfs                                                   5.0M     0  5.0M   0% /run/lock
tmpfs                                                   579M     0  579M   0% /run/shm

More virt-.* tools.
virt-alignment-scan    virt-filesystems       virt-ls                virt-tar-in
virt-cat               virt-format            virt-make-fs           virt-tar-out
virt-clone             virt-host-validate     virt-pki-validate      virt-viewer
virt-convert           virt-image             virt-rescue            virt-win-reg
virt-copy-in           virt-inspector         virt-resize            virt-xml-validate
virt-copy-out          virt-install           virt-sparsify          
virt-df                virt-list-filesystems  virt-sysprep           
virt-edit              virt-list-partitions   virt-tar               

The system used.
# cat /etc/debian_version /etc/issue
Debian GNU/Linux 7 \n \l

libguestfs basics

mount qcow disk image files

Notes on mounting qcow disk image files.
Use this method to mount qcow2 disk image files you trust.

Load the network block device -- nbd -- module with partition support.
# modprobe nbd max_part=8

See - list nbd devices.
# ls /dev/nbd*
/dev/nbd0  /dev/nbd10  /dev/nbd12  /dev/nbd14  /dev/nbd2  /dev/nbd4  /dev/nbd6 /dev/nbd8
/dev/nbd1  /dev/nbd11  /dev/nbd13  /dev/nbd15  /dev/nbd3  /dev/nbd5  /dev/nbd7 /dev/nbd9

Make sure that the qcow2 disk image is not used by a virtual machine.

Connect a qcow2 disk image to the Qemu Disk Block Device Server.
# qemu-nbd -c /dev/nbd0 /home/vm/anaxagoras.qcow2

List nbd0* devices
# ls /dev/nbd0*
/dev/nbd0  /dev/nbd0p1 /dev/nbd0p2  /dev/nbd0p5

Mount partitions.
# mkdir /mnt/imgs
# mount /dev/nbd0p1 /mnt/imgs/

Check mounted partition.
# ls /mnt/imgs/
bin   dev  home        lib    lost+found  mnt  proc  run   selinux  sys  usr  vmlinuz
boot  etc  initrd.img  lib64  media   opt  root  sbin  srv     tmp  var

Unmount and Clean up.
# umount /dev/nbd0p1
# mount |grep nbd

Disconnect from the Qemu Disk Block Device Server.
# qemu-nbd -d /dev/nbd0
# ls /dev/nbd0*

Unload nbd.
# modprobe -r nbd

The system used.
# cat /etc/debian_version /etc/issue
Debian GNU/Linux 7 \n \l

# uname -r

Mount qcow2 files in the host

debian on debian KVM II

An attempt to simplify an older debian on debian KVM how-to.

The system.
# cat /etc/debian_version /etc/issue
Debian GNU/Linux 7 \n \l

# uname -r
# grep "model\ name" /proc/cpuinfo -m1
model name : Intel(R) Core(TM) i5-4590 CPU @ 3.30GHz
# egrep "vmx|svm" /proc/cpuinfo -c

Install qemu-kvm, libvirt-bin and virtinst
# apt-get update
# apt-get install qemu-kvm libvirt-bin virtinst

Create a bridge (containing) to the host's physical interface

Create a debian guest that uses a virtual interface attached to a bridge interface named b0.
# mkdir /home/vm
# virt-install --connect qemu:///system -n anaxagoras -r 2048 -vcpus=1 --disk path=/home/vm/anaxagoras.qcow2,size=20 -c /insigdato/OS.iso/debian-7.6.0-amd64-netinst.iso --vnc --noautoconsole --os-type linux --description anaxagoras --network=bridge:b0 --hvm

To console into the new KVM guest from another host
( assuming you are working on a remote host ).

Find out where the KVM guest VNC console socket is in the KVM host.
# netstat -putan|grep kvm
tcp        0      0*               LISTEN      7499/kvm        

Set up SSH socket forwarding in another host
$ ssh -lroot -L 5900:localhost:5900
where is the KVM_host IP address.

and console into the forwarded socket.
$ vncviewer localhost
5900 is the default port. For ports above 5900 use port_number-5900 to find out the vncviewer `port`.


I don 't like vnet0, vnet1 etc and prefer better names for the virtual interfaces attached to the bridge b0. To give a more descriptive name to the virtual interface and possibly circumvent a few issues I had in previous versions of virtinst and libvirt.
# virsh
virsh # edit anaxagoras
<target dev='anaxagoras'/>
in <interface ...     --Naming, the second toughest problem in CS :)

Set the KVM guest to autostart.
virsh # autostart anaxagoras
Domain anaxagoras marked as autostarted

and start it.
virsh # start anaxagoras
Domain anaxagoras started

List running guests.
virsh # list 
 Id    Name                           State
 2     anaxagoras                     running

virsh # exit

Inspect the ethernet bridge.
# brctl show
bridge name bridge id         STP enabled interfaces
b0          8000.40167e6d6745 yes         anaxagoras

bridged KVM guest how-to

bridging for kvm

A basic layer 2 bridging how-to for virtualization like KVM in debian.

Many times, in KVM hosts we need to bridge the host's physical network interface with the virtual network interfaces used by the KVM guests.

Install the bridge utilities
# apt-get install bridge-utils

List network interfaces
# ip a|grep ":\ "
1: lo:  mtu 16436 qdisc noqueue state UNKNOWN 
2: eth0:  mtu 1500 qdisc pfifo_fast state UP qlen 1000

Inspect the ethernet bridge(s)
# brctl show
bridge name bridge id  STP enabled interfaces
None yet.

Create a bridge instance that you can access from an interface named b0.
# brctl addbr b0

Show bridge
# brctl show
bridge name bridge id  STP enabled interfaces
b0  8000.000000000000 no  

You may add the physical interface(s) to the bridge.
# brctl addif b0 eth0
However, do not try it if you are working on a remote host.
See below how to adjust the interfaces file instead.

Delete b0
# brctl delbr b0

Adjust /etc/network/interfaces to create a "persistent" bridge and restart networking.
# vi /etc/network/interfaces
# /etc/init.d/networking restart

An example /etc/network/interfaces file where the host has the IP address and the interface to the bridge is called b0.
auto lo
iface lo inet loopback

auto eth0
iface eth0 inet manual

auto b0
iface b0 inet static
        bridge_ports eth0
        bridge_stp on           #spanning tree 
        bridge_waitport 0       #no delay before a port becomes available
        bridge_fd 0             #no forwarding delay
        bridge_maxwait 0

Inspect bridge.
# brctl show
bridge name bridge id         STP enabled interfaces
b0          8000.40167e6d6745 yes         eth0

List network interfaces.
# ip a|grep ":\ "
1: lo:  mtu 16436 qdisc noqueue state UNKNOWN 
2: eth0:  mtu 1500 qdisc pfifo_fast master b0 state UP qlen 1000
4: b0:  mtu 1500 qdisc noqueue state UP 

Add a KVM host (anaxagoras) and inspect the bridge.
# brctl show
bridge name bridge id         STP enabled interfaces
b0          8000.40167e6d6745 yes         anaxagoras

The system used.
# cat /etc/issue /etc/debian_version 
Debian GNU/Linux 7 \n \l

# uname -r

L2 bridging for KVM

directadmin mysql open files limit

directadmin mysql open_files_limit notes

The directadmin `root` mysql password is called da_admin and you may find its password at
# ls -l /usr/local/directadmin/conf/mysql.conf
-r-------- 1 diradmin diradmin 30 Nov  5  2013 /usr/local/directadmin/conf/mysql.conf
# cat /usr/local/directadmin/conf/mysql.conf

Log in to the mysql server
# mysql -u da_admin -p 
Enter password:

Find out current open files limit
mysql> show variables like 'open%';
| Variable_name    | Value |
| open_files_limit | 1024  |
1 row in set (0.00 sec)

mysql> exit;

Set limit to 10240
# echo "open_files_limit = 10240" >> /etc/my.cnf

Restart the mysql daemon
# /etc/init.d/mysqld restart

Check new open_files_limit
# mysql -u da_admin -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.9 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'open%';
| Variable_name    | Value |
| open_files_limit | 10240 |
1 row in set (0.00 sec)

mysql> exit

directadmin mysql open_files_limit

Files as storage devices for KVM guests

A note on adding extra raw files for extra storage to KVM guests.

Create the "empty" "image" 1GB file named vm4_xtra.img
# dd if=/dev/zero of=/home/vm/vm4_xtra.img bs=1M count=1024

For larger files you would not want to use dd. Use fallocate instead eg:
# fallocate -l 50G vm4_xtra.img

Backup KVM host configuration
# cd /etc/libvirt/qemu/
# stor vm4.xml 
devz:vm4.xml is at ./stor/vm4.xml.0

Add the new virtual drive to the KVM guest configuration. eg:
    <disk type='file' device='disk'>
      <driver name='qemu' type='raw'/>
      <source file='/home/vm/vm4_xtra.img'/>
      <target dev='hda' bus='ide'/>
    <address type='drive' controller='0' bus='1' unit='0'/>


Redefine KVM guest
# virsh
virsh # define /etc/libvirt/qemu/vm4.xml 
Domain vm4 defined from /etc/libvirt/qemu/vm4.xml

Start KVM guest
virsh # start vm4
Domain vm4 started
virsh # quit

Log into the KVM guest and list drives
# fdisk -l
The new virtual HD should be /dev/sdb

Partition /dev/sdb
# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0x54ac0969.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

Command (m for help): n
Partition type:
   p   primary (0 primary, 0 extended, 4 free)
   e   extended
Select (default p): p
Partition number (1-4, default 1): 1
First sector (2048-2097151, default 2048): 
Using default value 2048
Last sector, +sectors or +size{K,M,G} (2048-2097151, default 2097151): 
Using default value 2097151

Command (m for help): t
Selected partition 1
Hex code (type L to list codes): 83

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.

# mkfs.ext4 /dev/sdb1

Adjust root reserved blocks percentage
# tune2fs -m 0 /dev/sdb1
tune2fs 1.42.5 (29-Jul-2012)
Setting reserved blocks percentage to 0% (0 blocks)

# mkdir /vm4_xtra
# mount /dev/sdb1 /vm4_xtra/

Adjust fstab
# echo "/dev/sdb1  /vm4_xtra  ext4  defaults  0  2" >> /etc/fstab

The system used
# cat /etc/issue /etc/debian_version
Debian GNU/Linux 6.0 \n \l

# uname -r

adding file storage devices in KVM guests

mongoDB on debian wheezy notes

Notes on setting up MongoDB on Debian Wheezy

The system
# cat /etc/issue /etc/debian_version 
Debian GNU/Linux 7 \n \l


import the debian repositories key
# apt-key adv --keyserver --recv 7F0CEB10

add the debian wheezy repository to the apt sources
# echo "deb wheezy/mongodb-org/3.0 main" > /etc/apt/sources.list.d/mongodb-org-3.0.list

update repositories
# apt-get update

Install the latest release of mongodb-org-shell , mongodb-org-server , mongodb-org-mongos and mongodb-org-tools
# apt-get install mongodb-org

Hold mongodb-org packages
# echo "mongodb-org hold" | dpkg --set-selections
# echo "mongodb-org-server hold" | dpkg --set-selections
# echo "mongodb-org-shell hold" | dpkg --set-selections
# echo "mongodb-org-mongos hold" | dpkg --set-selections
# echo "mongodb-org-tools hold" | dpkg --set-selections
# grep -A 1 "Package: mongodb-org" /var/lib/dpkg/status
Package: mongodb-org-mongos
Status: hold ok installed
Package: mongodb-org-tools
Status: hold ok installed
Package: mongodb-org-server
Status: hold ok installed
Package: mongodb-org-shell
Status: hold ok installed
Package: mongodb-org
Status: hold ok installed

Create an Administrator
# mongo
> use admin
> db.createUser(
... {
... user: "admin" ,
... pwd: "passwd" ,
... roles: [ { role: "userAdminAnyDatabase", db: "admin" } ]
... }
... )

Enable authentication and change the IP address the mongo server daemon binds to

# egrep -A 1 "bind|security" /etc/mongod.conf 

  authorization: enabled 

you may use bindIp: to to bind the MongoDB daemon to all the system IP addresses

restart the mongodb server
# service mongod stop
# service mongod start

Log in as admin locally
# mongo --host --port 27017 -u "admin" -p "passwd" --authenticationDatabase "admin"

Log in from a remote host
You better uninstall the official debian repositories mongo stuff and install mongo-org-shell from I did not encounter any issues using wheezy repositories on jessie hosts. Many "Error: 18 { code: 18, ok: 0.0, errmsg: "auth fails" } " errors are caused when old mongo clients or driver-libraries are trying to talk to new versions of mongodb servers.
# apt-get remove mongodb mongodb-clients mongodb-dev mongodb-server
# echo "deb wheezy/mongodb-org/3.0 main" > /etc/apt/sources.list.d/mongodb-org-3.0.list
# apt-get update
# apt-get install mongodb-org-shell
# echo "mongodb-org-shell hold" | dpkg --set-selections
and finally login from a remote host
$ mongo -u admin -p passwd
MongoDB shell version: 3.0.7
connecting to:

Create a database
use amongodb

Create a user that can read and write on the amongodb
> db.createUser(
        ... { user: "amongouser" ,
        ... pwd: "somepasswd" ,
        ... roles: [ {  role: "readWrite", db: "amongodb" } ]
        ... }
        ... )

and login from a remote host
$ mongo -u amongouser -p somepasswd

MongoDB on debian notes

addressbooks across thunderbird or icedove profiles or installations

A note on moving addressbooks across thunderbird or icedove profiles or installations ( so I do not need to jabber it :)

If you are trying to recover an icedove or thunderbird address book from backups,
backup and then export to *.LDIF at least one of you default address books
( 'Personal Address Book'(abook.mab) and 'Collected Addresses'(history.mab) )

You will find the abook.mab and history.mab files in ~/.icedove/randomprofilename.default/
or ~/.thunderbird/randomprofilename.default/

If it is a new installation you may skip the step above and just

name the address-book you want to import abook.mab or history.mab,
put the *.mab in an icedove or thunderbird profile
and check it out with
$ icedove --addressbook
you may export it to an *.ldif which can be imported to thunderbird or icedove

if you want to have old and new address-books in one installation you may concatenate old and new *.ldifs and then import them ( hmm, err, I don 't know how it handles duplicates )
... just import *.ldifs as additional address-books

( .mab files are weird -- a classic case of over-complication )


Moving address books across icedove or thunderbird profiles or installations

USB storage in OpenWrt

USB storage in OpenWrt

The openwrt system
# cat /etc/openwrt_version 
# uname -a
Linux OpenWrt 3.3.8 #1 Sat Mar 23 16:49:30 UTC 2013 mips GNU/Linux

Look for usb related kernel modules
# lsmod |grep usb
ledtrig_usbdev          2032  0 
usbcore                99200  4 ohci_hcd,ledtrig_usbdev,ehci_hcd
usb_common               480  1 usbcore
nls_base                4640  1 usbcore

Install USB storage support
# opkg install kmod-usb-storage

Available file systems and their OpenWrt packages
# opkg list "kmod-fs*"
kmod-fs-autofs4 - 3.3.8-1 - Kernel module for AutoFS4 support
kmod-fs-btrfs - 3.3.8-1 - Kernel module for BTRFS support
kmod-fs-cifs - 3.3.8-1 - Kernel module for CIFS support
kmod-fs-exportfs - 3.3.8-1 - Kernel module for exportfs. Needed for some other modules.
kmod-fs-ext4 - 3.3.8-1 - Kernel module for EXT4 filesystem support
kmod-fs-hfs - 3.3.8-1 - Kernel module for HFS filesystem support
kmod-fs-hfsplus - 3.3.8-1 - Kernel module for HFS+ filesystem support
kmod-fs-isofs - 3.3.8-1 - Kernel module for ISO9660 filesystem support
kmod-fs-minix - 3.3.8-1 - Kernel module for Minix filesystem support
kmod-fs-msdos - 3.3.8-1 - Kernel module for MSDOS filesystem support
kmod-fs-nfs - 3.3.8-1 - Kernel module for NFS support
kmod-fs-nfs-common - 3.3.8-1 - Common NFS filesystem modules
kmod-fs-nfsd - 3.3.8-1 - Kernel module for NFS kernel server support
kmod-fs-ntfs - 3.3.8-1 - Kernel module for NTFS filesystem support
kmod-fs-reiserfs - 3.3.8-1 - Kernel module for ReiserFS support
kmod-fs-udf - 3.3.8-1 - Kernel module for UDF filesystem support
kmod-fs-vfat - 3.3.8-1 - Kernel module for VFAT filesystem support
kmod-fs-xfs - 3.3.8-1 - Kernel module for XFS support


Setup was unable to create a new partition or locate an existing system partition

Setup was unable to create a new partition or locate an existing system partition ... setup log ... while installing windows 7 ...

Well, it appears that the windows installer is crazy.

The following procedure worked for me and I was able to install Windows from a USB drive.

Fireup a Command Prompt during the installation
( Repair Your Computer -> Command Prompt -> ... )

Enter diskpart and list your disk(s)
DISKPART>list disk
Figure out the drive you want Windows installed eg: 0

now clean the disk you are about to install windows ( eg: disk 0 )
DISKPART>select disk 0


select "Install now"
Insert any other USB drive on a USB port and pretend you want to load a driver for the HD ( "Load Driver" )
Click "Browse"
Make sure you see the new USB drive
That's it, now take off the dummy USB drive

hit the "x" button to close the window,
then hit the "Install Now" button and start the installation process again.
When you arrive at "Where do you want to install Windows?", select the unallocated space and hit "Next" ( let the windows installer to setup the partitions for you )

Windows 7 installation: Setup was unable to create a new partition or locate an existing system partition ....

windows 7 disable IPv6

To disable all IPv6 components except the IPv6 loopback interface add to the Registry Key

an 0xFF DisabledComponents DWORD.

To Confirm that IPv6 is disabled everywhere but the loopback if.
C:\Windows\system32>reg query HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Servic
es\Tcpip6\Parameters /v DisabledComponents

    DisabledComponents    REG_DWORD    0xff

ipconfig /all

netsh interface ipv6 show neighbors

If you want to disabled just Teredo or just ISATAP you could use the following netsh commands.

Disable Teredo Tunneling
netsh interface teredo set state disabled

Disable ISATAP - RFC5214
C:\Windows\system32>netsh interface isatap set state disabled

Microsoft Help article and related wizards

windows 7 --disable-IPv6

Start winpcap manually

Start winpcap manually.
C:\Windows\system32>net start npf

The NetGroup Packet Filter Driver service was started successfully.

See if winpcap is "running" --loaded
C:\Windows\system32>sc query npf

        TYPE               : 1  KERNEL_DRIVER
        STATE              : 4  RUNNING
                                (STOPPABLE, NOT_PAUSABLE, IGNORES_SHUTDOWN)
        WIN32_EXIT_CODE    : 0  (0x0)
        SERVICE_EXIT_CODE  : 0  (0x0)
        CHECKPOINT         : 0x0
        WAIT_HINT          : 0x0

Start winpcap manually.

unrar nonfree debian

unrar-free fails often to extract and decompress files from rar archives

You don't need to uninstall unrar-free in order to install unrar-nonfree but ...
# apt-get remove unrar-free

Add non-free repositories eg:
deb squeeze main non-free contrib
deb-src squeeze main non-free contrib

Install unrar-nonfree
# apt-get build-dep unrar-nonfree
# mkdir unrar-nonfree
# apt-get source unrar-nonfree
# apt-get source -b unrar-nonfree
# dpkg -i unrar_3.9.10-1_amd64.deb
# cd ..
# rm -r unrar-nonfree

unrar unrar-nonfree debian

read only to read-and-write filesystem

Remount as read-write a read only file-system.

( Is common for VMs with full virtual disks to come up with their user-space file-systems mount as ro (read-only). )

Check where the root or the file-system that you want to mount as read-write(rw) is mounted.
# mount
/dev/sda1 on / type ext3 (rw)

/proc/mounts and the mount command may report rw even if the file-system is mounted as ro.

Remount root as read-write.
# mount -o remount,rw /dev/sda1 /

Remount as read-write a filesystem in linux

fix grub after updating windows 10 in a dual boot Ubuntu-Windows10 system

Fix grub after updating windows 10 in a dual boot Ubuntu-Windows10 system

grub rescue> ls
grub rescue> (hd0,msdos2) (hd0,msdos3) (hd0,msdos4)
grub rescue> ls (hd0,msdos2)/boot/grub
If the above listing returns stuff you found the right partition

grub rescue> set prefix=(hd0,msdos2)/boot/grub
grub rescue> set root=(hd0,msdos2)
grub rescue> insmod normal
grub rescue> normal
grub rescue> insmod linux

grub rescue> boot

now the grub menu should come up

boot into Ubuntu and install boot-repair
$ sudo -s
# add-apt-repository ppa:yannubuntu/boot-repair
# apt-get update
# apt-get install -y boot-repair

boot repair
# boot-repair
the first option worked for me

grub rescue dual boot Ubuntu-Windows10 system after windows10 update