Empty recycle bin for all users on Windows

Before I worked at a Linux only company, but since I changed job I’ve been working some with Windows servers. Something that got me frustrated last night was that somebody had put large files in the recycle bin but not emptied it. This made the server almost run out of space, so after a bit googling I found out that you actually can empty the recycle bin for all users.

This is a easy way to do this:

rd /s c:\$Recycle.Bin
rd /s c:\recycler

If you have multiple drives just change c:\ accordingly. And of course, answer yes (y) to the question that pops up.

A big thanks to the lifehacker blog for posting this.

Get sizes of database and tables in specific database

Often in my day to day work I need to figure out which database on a particular database that take up all the space, one easy way to do this is the following command that you run in your MySQL prompt or preferred MySQL manager:

SELECT table_schema "Database Name", 
       SUM(data_length + index_length) / (1024 * 1024) "Database Size in MB" 
       FROM information_schema.TABLES GROUP BY table_schema;

If you need to know which table in a database run the following query:

SELECT table_name AS "Tables", 
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" 
FROM information_schema.TABLES 
WHERE table_schema = "test"
ORDER BY (data_length + index_length) DESC;

This was inspired from: StackOverflow – How to get the sizes of the tables of a mysql database?

Take backend out of rotation in varnish

When using Varnish and multiple backends it’s sometimes convenient to remove a particular backend without restarting the service Please note that this requires a Varnish version of at least 3.0.3 to work.

To remove a backend issue the following commands:

root@lb-01:~# varnishadm -T localhost:6082 -S /etc/varnish/secret
varnish> backend.set_health backend01 sick
200
varnish>

To re-add it to rotation use the following commands:

root@lb-01:~# varnishadm -T localhost:6082 -S /etc/varnish/secret
varnish> backend.set_health backend01 auto
200
varnish>

backend01 are backend name that can be required by issuing backend.list in the Varnish cli.

Killing specific processes with ps, grep, awk and xargs

Often when you work with Linux you need to kill a bunch of processes matching a certain pattern. This could be accomplished using ps, grep, awk and xargs like this:

ps -aux |grep php-fpm |grep -v grep | awk '{print $2}' | xargs kill

If the process won’t die you could add -9 to kill which can’t be blocked by anything. Of course you need to own the processes you try to kill (or use sudo).

References:
Article on commandlinefu.com

Mitigating the BEAST attack on TLS

There are two researchers that have developed a new type of attack to TLS 1.0/SSL 3.0 protocol that allows them to decrypt client requests on the fly and hijack confidential sessions, for example e-commerce. This is made possible by a known flaw in TLS.

So this is something that needs addressing. It’s basically the same on any web server, make sure the weak ciphers isn’t used.

For apache this is needed to be added to the configuration:

SSLHonorCipherOrder On 
SSLCipherSuite ECDHE-RSA-AES128-SHA256:AES128-GCM-TLSv1-SHA256:RC4:HIGH:!MD5:!aNULL:!EDH

On nginx the syntax are the following:

ssl_protocols SSLv3 TLSv1 TLSv1.1 TLSv1.2;

ssl_ciphers ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-SHA384:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA256:ECDHE-ECDSA-RC4-SHA:ECDHE-RSA-RC4-SHA:ECDH-ECDSA-RC4-SHA:ECDH-RSA-RC4-SHA:ECDHE-RSA-AES256-SHA:RC4-SHA;
ssl_prefer_server_ciphers on;

More information about the BEAST attack:
CVE-2011-3389
New Attack Breaks Confidentiality Model of SSL, Allows Theft of Encrypted Cookies

References:
Mitigating the BEAST attack
Configure SSL on nginx

Delete all e-mail from specific address in postfix

Found this neat thing for deleting all e-mails from a specific user (in this case MAILER-DAEMON):
mailq | grep MAILER-DAEMON | awk ‘{print $1}’ | tr -d ‘*’ | postsuper -d –

This was found at http://tgrove.com/2008/01/15/postfix-delete-all-mailer-daemon-emails/

MySQL Replication

About this How To

This tutorial describes how to set up a database replication between two servers running Ubuntu Server and MySQL. What MySQL replication does is that it allows you to have to identical copy of your database. This tutorial will do some assumptions that is that you want a complete replica of your master including mysql database with user names and passwords.

I use MySQL replication primarily to minimize downtime caused by broken hardware as the slave get the data replicated instantly so that both servers contains the same data. This is not to be implemented instead of a backup, because if you by accident issued DELETE command on your master your slave will get this replicated and delete the data. However the slave can be used to make backups without interfering with users connecting to the master database.

When I wrote this tutorial I used Ubuntu Server 10.04 LTS primarly because this is the latest long term support that has been release. The commands and configuration used in this tutorial should work on other distributions of Linux. The my.cnf changes that needs to be done is the same independent of underlying operating system, but some changed I do in this tutorial is specific as I choose to replicate all databases and not specific databases. I will highlight changes.

I also want to mention that this is not the only way to set up replication, you can choose to just replicate a single database if you want.

Please note that all the line numbers below are consistent with a standard /etc/mysql/my.cnf configuration file that ships with Ubuntu Server 10.04 LTS mysql-server package. If you don’t use this package you can search for the lines within your text editor of choice.

….

Preparation

To be able to follow this tutorial on how to setup MySQL replication you should have two instances of Ubuntu Server running and they should be able to reach each other with ping. You should also know your MySQL root password. It’s a great help if you also are comfortable with editing files in a cli interface on a remote Linux machine.

I got the following IP configuration set up for this tutorial:


192.168.2.10 = MySQL master
192.168.2.11 = MySQL slave

I will reference this in the configuration files, if you don’t use the same RFC1918 IPs for your internal network please change the IPs to reflect your setup.

I also have setup my setup so that I don’t have to provide a password when I start the mysql client on both servers. This is achieved by creating in roots home called .my.cnf that contains the following:


[client]
password = yourrootpassword

….

Step 1 – Configure MySQL master machine

Get an active shell on your MySQL master machine and become the root user.

The first step is to open /etc/mysql/my.cnf in a text editor of your choice on your MYSQL master machine.

When you have open the file do the following change to enable MySQL to listen on any IP configured on your machine. If your database have a public accessible IP (not specified in RFC1918) you should take some action on securing the MySQL port 3306 so that not everyone can connect to it, this will however not been addressed in this tutorial.

To enable MySQL to listen on all IPs on your machine just simply comment out bind-address = 127.0.0.1 so that it looks like this:


# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1

Next step for configuring the master is to activate MySQL binlogs, this is so that every transaction to the server is recorded and could be read by our slave instance and this is done within the same file as we did our last change. The change we need to do is uncomment server-id and log_bin so that it looks like this:


# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name

I usually go with the default for log retention (expire_logs_days) and file size (max_binlog_size). If you want to just replicatie a single database you can add it to binlog_do_db, and if you want to exclude a database from replication you should add it to binlog_ignore_db. Please note that to be able to replicate a single database the database must exist before you add it to binlog_do_db. As I will replicate all databases in my set up I don’t need to create the database before I set up replication.

In the same file as above, add the following after binlog_ignore_db:

innodb_flush_log_at_trx_commit=1
sync_binlog=1

This is added to the configuration to enhance durability and consistency when using InnoDB with transactions in a replication setup.

When you have done all the above changes it’s time to restart the MySQL instance on master machine. In Ubuntu it’s performed with service mysql restart.


root@mysql-master:~# service mysql restart
mysql start/running, process 1434
root@mysql-master:~#

That is the basic configuration of our MySQL master machine, we will issue commands on this machine later on but for now we are done here.

….

Step 2 – Configure MySQL slave machine

Get an active shell on your MySQL slave machine and become the root user.

The first step on sthe slave is the same as for the master, get it to listen on all IPs allocated to the machine. You will achieve this the same way as on the master using your text editor of choice and edit /etc/mysql/my.cnf as follow:


# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1

Next step is also almost identical to the master configuration. In the same file as befor uncomment server-id and change it to 2 and uncomment log_bin so the file looks like this:


# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
server-id = 2
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name

After binlog_ignore_db inser the extra rows I have added below:

innodb_flush_log_at_trx_commit=1
sync_binlog=1
relay-log=/var/log/mysql/mysqld-relay-bin
report-host=192.168.2.10
skip-slave-start

The changes from the master configuration is relay-log, report-host and skip-slave-start. Skip-slave-start is pretty much self explanatory, this prevents the slave from try to start replication when mysql daemon is started. I usually add this because in an event of restart of the MySQL daemon I want to make sure that everything is OK with the files before I start replication again. This to ensure that I’m not missing or have any corrupted data in my replica.

Report-host is pointing to the IP of the MySQL master, this is used to be able to issue SHOW SLAVE HOSTS; on the master to be able to see which slaves that are currently connected to the master. Relay-log is just to keep track of database changes that the master sends to the slave.

With the above changes done it’s time to restart the MySQL instance with service mysql restart:


root@mysql-slave:~# service mysql restart
mysql start/running, process 1335
root@mysql-slave:~#

….

Step 3 – Setting up replication user and getting initial data from MySQL master

Now when we have configured basic configuration on both our master and slave MySQL instance we need to set up a replication user for the slave and also get some basic information from the master and a initial database dump.

First start a mysql client on your mysql master and paste the following information:

mysql> CREATE USER 'slave'@192.168.2.11 IDENTIFIED BY 'somepassword';
Query OK, 0 rows affected (0.05 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@192.168.2.11;
Query OK, 0 rows affected (0.06 sec)

Please note that the IP 192.168.2.11 should be the one you use for your MySQL slave IP. For example if you use 10.10.10.11 for your slave IP replace 192.168.2.11 with this.

The above commands make sure that the slave is granted replication slave on the master to be able to pull binary logs from master to replicate commands from it.

Now it’s time to acquire the master data from the MySQL master. This is done from the MySQL client. It’s important that you don’t close your client session to the master before you have completed every part of this step as this ensures that nothing is written your database. Please enter the following commands in your MySQL client:


mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.11 sec)

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 355 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql>

The data acquired from SHOW MASTER STATUS; is needed when you setup your slave to start replicate from the master.

In another SSH connection to the MySQL master issue the following command as root:


root@mysql-master:~# mysqldump --all-databases --opt --lock-all-tables > mysql_all_databases.sql

On the MySQL master you should now enter UNLOCK TABLES; in the mysql client:

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
mysql>

This will release our read only table lock we setup earlier to be sure that nothing writes to our database before we have gotten our initial database dump. This is crucial because if anything gets written to the database tables after we have acquired the master data we will get data duplicated errors when we start up the replication on the slave.

As I have chosen to replicate all databases from the master to the slave I have to acquire the debian-sys-maint users password from the master and change the configuration for debian.cnf on the slave to be a exact match to the masters.

To do this I simply copy the /etc/mysql/debian.cnf file from the master to the slave with SCP, which I also use to transfer my initial database dump with:


root@mysql-master:~# scp /etc/mysql/debian.cnf andreas@192.168.2.11:~/
andreas@192.168.2.11's password:
debian.cnf 100% 333 0.3KB/s 00:00
root@mysql-master:~#
root@mysql-master:~# scp mysql_all_databases.sql user@192.168.2.11:~/
user@192.168.2.11's password:
mysql_all_databases.sql 100% 489KB 488.6KB/s 00:00
root@mysql-master:~#

….

Step 4 – Start replication on the MySQL slave machine

First we replace our current /etc/mysql/debian.cnf file with the one we copied from the MySQL master and when we have done this we read the database dump file into the MySQL instance on the slave:

root@mysql-slave:~# cp /home/andreas/debian.cnf /etc/mysql/debian.cnf
root@mysql-slave:~# mysql < /home/andreas/mysql_all_databases.sql root@mysql-slave:~# [/plain] When you have restored the initial database dump and copied the debian.cnf file you should restart the MySQL instance on the slave: [plain title="Restart MySQL on slave"] root@mysql-slave:~# service mysql restart mysql start/running, process 1335 root@mysql-slave:~# [/plain] Now it's time to set up the slave so it knows where it should start to replicate from the master. This is done with the CHANGE MASTER TO command in a mysql client on the slave: [sql title="CHANGE MASTER TO on slave"] mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.2.10′,
-> MASTER_USER=’slave’,
-> MASTER_PASSWORD=’somepassword’,
-> MASTER_LOG_FILE=’mysql-bin.000002′,
-> MASTER_LOG_POS=355;
Query OK, 0 rows affected (0.20 sec)
mysql>

[/sql]

When this step is done all that remains except testing is to start the replication and this is done with the following command:


mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql>

If you don’t got any error issuing the last command you should now have a MySQL replication set up running.

….

Step 5 – Test out our set up

Now it’s time to verify that our replication really is working as intended. First we check which schemas (databases) we have on our master:


mysql> show schemas;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)

mysql>

The output tells me that we only have the MySQL default databases. To create a database we enter the following:


mysql> CREATE DATABASE test CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.06 sec)

mysql> USE test;
Database changed
mysql> CREATE TABLE test (data VARCHAR(50));
Query OK, 0 rows affected (0.05 sec)

mysql>

You can verify this with SHOW SCHEMAS; and SHOW TABLES;:


mysql> SHOW SCHEMAS;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
mysql> USE test;
Database changed
mysql> SHOW TABLES;
+----------------+

| Tables_in_test |
+----------------+
| test |
+----------------+
1 row in set (0.00 sec)
mysql>


mysql> SHOW SCHEMAS;

 +--------------------+
 | Database |
 +--------------------+
 | information_schema |
 | mysql |
 | test |
 +--------------------+
 3 rows in set (0.00 sec)
 mysql> USE test;
 Database changed
 mysql> SHOW TABLES;
 +----------------+</em>

| Tables_in_test |
 +----------------+
 | test |
 +----------------+
 1 row in set (0.00 sec)
 mysql>

If you get the same output on the slave and the master your replication is set up and working. You can now test to insert and delete rows in your table on the master and check so that they appear on the slave.

When you are satisfied with the functions of your replicated MySQL set up you can start to load your master with your production data.

Please note that you should not at any time insert or delete anything on the slave, everything should be done on the master. It’s fine thou to use the slave as a read only back end.

….

References

MySQL 5.1 Replication Reference Guide
Howtoforge – MySQL Replication

Currently working on

Hi everyone!

I’m currently working on a article on how to setup MySQL replication. As this is my first article on this site I want to set the standard high and therefor it takes a bit more time than expected to finish.

Stay tuned for a update shortly.

/Andreas