Resetting a MySQL root password

Resetting a forgotten MySQL root password is a pretty straight forward task to complete assuming you have sudo or root access to the server. It is important to note that by performing this procedure, MySQL will be down till you complete everything. So be sure to do this during a time when it will not impact your business.

First, stop the MySQL service

service mysqld stop # RHEL clones
service mysql stop # Ubuntu / Debian distro

Now its time to bring MySQL back up in safe mode. This means we will start MySQL, but we are simply skipping the user privileges table:

sudo mysqld_safe --skip-grant-tables &

Time to log into MySQL and switch to the MySQL database:

mysql -uroot
use mysql;

Now reset the root password and flush privileges:

update user set password=PASSWORD("enternewpasswordhere") where User='root';
flush privileges;
quit

Once you have completed that, its time to take MySQL out of safe mode and start it back up normally. NOTE: Be sure MySQL is fully stopped before trying to start the service again. You may need to kill -9 the process if its being stubborn.

Stop MySQL:

service mysqld stop # RHEL clones
service mysql stop # Ubuntu / Debian distro

Verify that the MySQL process is no longer running. If they are, you may have to kill -9 the process:

ps -waux |grep mysql

Start MySQL back up:

service mysqld start # RHEL clones
service mysql start # Ubuntu / Debian distro

Finally, test out logging in to ensure its now working properly:

mysql -uroot -p

Keeping multiple web servers in sync with rsync

People looking to create a load balanced web server solution often ask, how can they keep their web servers in sync with each other? There are many ways to go about this: NFS, lsync, rsync, etc. This guide will discuss a technique using rsync that runs from a cron job every 10 minutes.

There will be two different options presented, pulling the updates from the master web server, and pushing the updates from the master web server down to the slave web servers.

Our example will consist of the following servers:

web01.example.com (192.168.1.1) # Master Web Server
web02.example.com (192.168.1.2) # Slave Web Server
web03.example.com (192.168.1.3) # Slave Web Server

Our master web server is going to the single point of truth for the web content of our domain. Therefore, the web developers will only be modifying content from the master web server, and will let rsync handle keeping all the slave nodes in sync with each other.

There are a few prerequisites that must be in place:
1. Confirm that rsync is installed.
2. If pulling updates from the master web server, all slave servers must be able to SSH to the master server using a SSH key with no pass phrase.
3. If pushing updates from the master down to the slave servers, the master server must be able to SSH to the slave web servers using a SSH key with no passphrase.

To be proactive about monitoring the status of the rsync job, both scripts posted below allow you to perform a http content check against a status file to see if the string “SUCCESS” exists. If something other then SUCCESS is found, that means that the rsync script may have failed and should be investigated. An example of this URL to monitor would be is: 192.168.1.1/datasync.status

Please note that the assumption is being made that your web server will serve files that are placed in /var/www/html/. If not, please update the $status variable accordingly.

Using rsync to pull changes from the master web server:

This is especially useful if you are in a cloud environment and scale your environment by snapshotting an existing slave web server to provision a new one. When the new slave web server comes online, and assuming it already has the SSH key in place, it will automatically grab the latest content from the master server with no interaction needed by yourself except to test, then enable in your load balancer.

The disadvantage with using the pull method for your rsync updates comes into play when you have multiple slave web servers all running the rsync job at the same time. This can put a strain on the master web servers CPU, which can cause performance degradation. However if you have under 10 servers, or if your site does not have a lot of content, then the pull method should work fine.

Below will show the procedure for setting this up:

1. Create SSH keys on each slave web server:

ssh-keygen -t dsa

2. Now copy the public key generated on the slave web server (/root/.ssh/id_dsa.pub) and append it to the master web servers, /root/.ssh/authorized_keys2 file.

3. Test ssh’ing in as root from the slave web server to the master web server
# On web02

ssh [email protected]

4. Assuming you were able to log in to the master web server cleanly, then its time to create the rsync script on each slave web server. Please note that I am assuming your sites documentroot’s are stored in /var/www/vhosts. If not, please change the script accordingly and test!

mkdir -p /opt/scripts/
vi /opt/scripts/pull-datasync.sh

#!/bin/bash
# pull-datasync.sh : Pull site updates down from master to front end web servers via rsync

status="/var/www/html/datasync.status"

if [ -d /tmp/.rsync.lock ]; then
echo "FAILURE : rsync lock exists : Perhaps there is a lot of new data to pull from the master server. Will retry shortly" > $status
exit 1
fi

/bin/mkdir /tmp/.rsync.lock

if [ $? = "1" ]; then
echo "FAILURE : can not create lock" > $status
exit 1
else
echo "SUCCESS : created lock" > $status
fi

echo "===== Beginning rsync ====="

nice -n 20 /usr/bin/rsync -axvz --delete -e ssh [email protected]:/var/www/vhosts/ /var/www/vhosts/

if [ $? = "1" ]; then
echo "FAILURE : rsync failed. Please refer to solution documentation" > $status
exit 1
fi

echo "===== Completed rsync ====="

/bin/rm -rf /tmp/.rsync.lock
echo "SUCCESS : rsync completed successfully" > $status

Be sure to set executable permissions on this script so cron can run it:

chmod 755 /opt/scripts/pull-datasync.sh

Using rsync to push changes from the master web server down to slave web servers:

Using rsync to push changes from the master down to the slaves also has some important advantages. First off, the slave web servers will not have SSH access to the master server. This could become critical if one of the slave servers is ever compromised and try’s to gain access to the master web server. The next advantage is the push method does not cause a serious CPU strain cause the master will run rsync against the slave servers, one at a time.

The disadvantage here would be if you have a lot of web servers syncing content that changes often. Its possible that your updates will not be pushed down to the web servers as quickly as expected since the master server is syncing the servers one at a time. So be sure to test this out to see if the results work for your solution. Also if you are cloning your servers to create additional web servers, you will need to update the rsync configuration accordingly to include the new node.

Below will show the procedure for setting this up:

1. To make administration easier, its recommended to setup your /etc/hosts file on the master web server to include a list of all the servers hostnames and internal IP’s.

vi /etc/hosts
192.168.1.1 web01 web01.example.com
192.168.1.2 web02 web02.example.com
192.168.1.3 web03 web03.example.com

2. Create SSH keys on the master web server:

ssh-keygen -t dsa

3. Now copy the public key generated on the master web server (/root/.ssh/id_dsa.pub) and append it to the slave web servers, /root/.ssh/authorized_keys2 file.

4. Test ssh’ing in as root from the master web server to each slave web server
# On web01

ssh [email protected]

5. Assuming you were able to log in to the slave web servers cleanly, then its time to create the rsync script on the master web server. Please note that I am assuming your sites documentroot’s are stored in /var/www/vhosts. If not, please change the script accordingly and test!

mkdir -p /opt/scripts/
vi /opt/scripts/push-datasync.sh

#!/bin/bash
# push-datasync.sh - Push site updates from master server to front end web servers via rsync

webservers=(web01 web02 web03 web04 web05)
status="/var/www/html/datasync.status"

if [ -d /tmp/.rsync.lock ]; then
echo "FAILURE : rsync lock exists : Perhaps there is a lot of new data to push to front end web servers. Will retry soon." > $status
exit 1
fi

/bin/mkdir /tmp/.rsync.lock

if [ $? = "1" ]; then
echo "FAILURE : can not create lock" > $status
exit 1
else
echo "SUCCESS : created lock" > $status
fi

for i in ${webservers[@]}; do

echo "===== Beginning rsync of $i ====="

nice -n 20 /usr/bin/rsync -avzx --delete -e ssh /var/www/vhosts/ [email protected]$i:/var/www/vhosts/

if [ $? = "1" ]; then
echo "FAILURE : rsync failed. Please refer to the solution documentation " > $status
exit 1
fi

echo "===== Completed rsync of $i =====";
done

/bin/rm -rf /tmp/.rsync.lock
echo "SUCCESS : rsync completed successfully" > $status

Be sure to set executable permissions on this script so cron can run it:

chmod 755 /opt/scripts/push-datasync.sh

Now that you have the script in place and tested, its now time to set this up to run automatically via cron. For the example here, I am setting up cron to run this script every 10 minutes.

If using the push method, put the following into the master web servers crontab:

crontab -e
# Datasync script
*/10 * * * * /opt/scripts/push-datasync.sh

If using the pull method, put the following onto each slave web servers crontab:

crontab -e
# Datasync script
*/10 * * * * /opt/scripts/pull-datasync.sh

Dirty disks

Many companies these days are making use of hosting providers to house their critical compute infrastructure. There are many financial benefits to doing this as the costs of running a data center far exceeds the costs of leasing servers from a hosting provider.

But as with many things, there is always another side of the story that must be considered, your environments security. Dedicated and cloud hosting providers reuse their hardware often as clients may lease the hardware for only a short time. The question you must ask yourself is: What do these providers do with the hard drives once a server has been removed from the clients account before they lease that server to another client? Do they have documentation proving their compliance with the DOD_5220.22-M standard for secure data removal?

Just because an operating system has been re-installed, that does not mean the data is permanently wiped. In theory, one could argue that your data is never fully removed from a drive unless you properly destroy the drive, which can be an expensive operation, but the DOD_5220.22-M standard provides a set of reasonable guidelines for rendering the data unrecoverable for many situations.

Extracting the data from a recycled server is not all that complicated for your average system administrator. In fact, using one technique, its a simple one liner using dd and strings. So the next time you are curious about your hosting providers security practices, try running the following on the server or cloud server and see what fragments of data you can recover by:

dd if=/dev/sda1 bs=1M skip=5000 | strings

Review the output. If you see data on here from other users, the disks are dirty and that hosting provider is not properly sanitizing the hard drives before leasing the server to another client. This has huge implications, especially if you need to comply with security standards such as HIPAA, FISMA, PCI compliance or any of the other standards out there.

So the moral of the story is, always do your homework before choosing a dedicated or cloud hosting provider. Ask about their security procedures, ask them about their PCI, HIPAA, FISMA, ISO 27001, SSAE 16, etc. Never take anything at face value. Ask them for their report on compliance.

When it comes to your security stance, paranoia is your greatest defense.

Using AIDE for file integrity monitoring (FIM) on CentOS

PCI-DSS 3.1 section 10.5.5 has the following requirement:

Use file-integrity monitoring or change-detection software on logs to ensure that existing log data cannot be changed without generating alerts (although new data being added should not cause an alert).

For large solutions, I would suggest using a well known tool such as Tripwire Enterprise. However many small to mid size companies that have a small footprint within their card holder data environment (CDE), may not be able to afford this. So what can companies use to meet this requirement? Implement AIDE (Advanced Intrusion Detection Environment).

Taken from the projects website, AIDE creates a database from the regular expression rules that it finds from the config file(s). Once this database is initialized it can be used to verify the integrity of the files.

AIDE is a very simple (yet powerful) program that runs from cron checking your files (typically once a night), and it will scan your system looking for any changes in the directories its monitoring. There are a number of different ways to use this program, but I’ll outline one that I like to use.

My requirements:
1. I want the reports to run nightly.
2. All change reports are emailed to me so I can archive them for a year offsite.
3. Have the database automatically commit the additions, deletions, and changes to baseline each time its ran.

In the event my system was compromised, I want to ensure that the malicious user was not able to modify, or delete my previous reports. Therefore, I choose not to store them on the machine. Its true that once the malicious user gained access to my system, they could change my AIDE config on me, but at least my previous reports will be intact which should help me when determining what malicious changes this user made to my server. Please note that I am making an assumption here that you are already backing up your system nightly, which would include your AIDE database! If you do not currently have a backup strategy in place, get one. Tools such as AIDE helps identify what files a malicious user may have changed, but if they completely crippled the system, you will need to restore from backups.

Setting up AIDE is fairly straight forward. It exists in most of package repositories out there including most variants of Linux and BSD.

On Linux based systems, you can install it by:

[[email protected] ~]# yum install aide

Once you have AIDE installed, the default configuration from the upstream provider should give you a reasonable default aide.conf. But what if you wanted to add your website documentroot to this so you can keep track of what files are changing on your website? Well, we simple add the directory to the aide.conf by including:

[[email protected] ~]# vim /etc/aide.conf
...
/var/www/vhosts/domain.com
...

Now AIDE will be keeping track of our website. But adding your site may lead to very noisy reports because most websites implement caching. So this now becomes a balancing act to exclude directories that change often, yet retain enough of your sites critical content. We could just leave the entire directory in AIDE, but I know I personally don’t want to read a change report that contains 1,000 changes every day. So in the case of this wordpress site, I exclude the cache directory by appending the following to my custom configuration:

[[email protected] ~]# vim /etc/aide.conf
...
/var/www/vhosts/domain.com
!/var/www/vhosts/domain.com/web/wp-content/cache
...

The “!” means NOT to monitor that specific directory. You will need to run AIDE a few times and fine tune the configuration before you get a report that is useful for your specific needs.

On CentOS, I had to change the following setting in /etc/aide.conf for the initialization to work:

[[email protected] ~]# vim /etc/aide.conf
...
# Whether to gzip the output to database
gzip_dbout=no
...

Once you have your configuration tuned for your specific purposes, you first my initialize the database to create a baseline before you can start getting change reports. You do this by:

[[email protected] ~]# aide --init
[[email protected] ~]# mv -f /var/lib/aide/aide.db.new /var/lib/aide/aide.db

Now, try making a basic change to /etc/hosts, then run a check on AIDE to see if it detects the change:

[[email protected] ~]# aide --check

If you are like me and would prefer not to have to log into 10 servers a day to run and view the reports, you can configure cron to run the report, and email you the results daily, while committing the changes to baseline. If you choose to go this route, it is critical that you review your change reports as they come in because we are essentially committing every change to the baseline. Here is how I configure cron:

[[email protected] ~]# crontab -e
# Perform daily change report
0 3 * * * /usr/sbin/aide --update | mail -s "AIDE Audit Report : web01.example.com" [email protected]

# Initialize the AIDE database once a day:
30 3 * * *  nice -19 /usr/sbin/aide --init;mv -f /var/lib/aide/aide.db.new /var/lib/aide/aide.db

Posted below is an example report that AIDE would send me via email daily:

AIDE found differences between database and filesystem!!
Start timestamp: 2012-09-13 01:24:05

Summary:
Total number of files: 57620
Added files: 1
Removed files: 1
Changed files: 1

---------------------------------------------------
Added files:
---------------------------------------------------

added: /var/spool/cron/root

---------------------------------------------------
Removed files:
---------------------------------------------------

removed: /etc/.aide.conf.swp

---------------------------------------------------
Changed files:
---------------------------------------------------

changed: /etc/aide.conf

--------------------------------------------------
Detailed information about changes:
---------------------------------------------------

File: /etc/aide.conf
Size : 2381 , 2390
Mtime : 2012-09-13 01:24:05 , 2012-09-13 01:24:05
Ctime : 2012-09-13 01:24:05 , 2012-09-13 01:24:05
MD5 : b+qbBDYEPesd+NCR1VRQHQ== , rG5pNPghdweedpU/c0ieHw==
RMD160 : T081ixhqik4efC3dfeCOBDCKpP4= ,
qe8MV0eteklAKmlZ5LTubaOUNKo=
SHA256 : g4jstEtfU8BNu+43jkrxJc9Cpr2SABZj ,
a65iaV54XR4vu8/zbA4Tdfe2U+W5uPNY

So this reports tells me that root’s crontab was added, a swap file for aide.conf was removed, and I updated the /etc/aide.conf recently.

Please remember that utilizing a tool to provide file integrity monitoring is only one part of a defense in depth strategy. There is no silver bullet for system security, but every layer you add will increase your security footprint which helps you with taking a proactive approach to security.

Using logrotate for custom log directories with wildcards

Logrotate is a useful application for automatically rotating your log files. If you choose to store certain logs in directories that logrotate doesn’t know about, you need to create a definition for this.

In the example listed near the bottom of the post, we are showing how we can rotate our logs that are stored in /home/sites/logs. These logs are for Apache access and error logs. But also has a third log file that ends in .logs.

If you ran through this quickly, one might simply create a definition as a wildcard, such as:
/home/sites/logs/*

Unfortunately, logrotate will read this literally and now will rotate compressed log files that were already in rotation, leaving you with a mess in your log directories like this:

/home/sites/logs/http-access.log.1.gz
/home/sites/logs/http-access.log.1.gz.1
/home/sites/logs/http-access.log.1.gz.1.1
/home/sites/logs/http-access.log.1.gz.1.1.1
/home/sites/logs/http-access.log.1.gz.1.1.1.1

And it just goes down hill from there. This exact thing happened to me cause I forgot to read the man page which clearly stated:

 Please use wildcards with caution. If you specify *, log rotate will
rotate all files, including previously rotated ones. A way around this
is to use the olddir directive or a more exact wildcard (such as
*.log).

So using wildcards are still acceptable, but use them with caution. As I had three types of files to rotate in this example, I found that I can string them together as follows:

/home/sites/logs/*.error /home/sites/logs/*.access /home/sites/logs/*.log

I’ll post the example configuration below that was implemented on a FreeBSD solution for logging this custom directory:

cat /usr/local/etc/logrotate.conf
# see "man log rotate" for details
# rotate log files weekly
daily

# keep 4 weeks worth of backlogs
rotate 30

# create new (empty) log files after rotating old ones
create

# uncomment this if you want your log files compressed
compress

# RPM packages drop log rotation information into this directory
# include /usr/local/etc/logrotate.d

# system-specific logs may be configured here
/home/sites/logs/*.error /home/sites/logs/*.access /home/sites/logs/*.log {
daily
rotate 30
sharedscripts
postrotate
/usr/local/etc/rc.d/apache22 reload > /dev/null 2>/dev/null
endscript
}

Now lets test this out to confirm the logs will rotate properly by running:

logrotate -f /usr/local/etc/logrotate.conf
logrotate -f /usr/local/etc/logrotate.conf
logrotate -f /usr/local/etc/logrotate.conf

When you check your logs directory, you should now see the files rotating out properly:

/home/sites/logs/http-access.log
/home/sites/logs/http-access.log.1.gz
/home/sites/logs/http-access.log.2.gz
/home/sites/logs/http-access.log.3.gz