ibdata1 mysql file

Posted by | Posted in Code, Command-Line, Linux, MySQL, Ubuntu | Posted on 31-07-2014

Recently I discovered that my ibdata1 file with in /var/lib/mysql was filling up my webserver hard drive. After reading on several forums, it was discovered that the ibdata1 file contains all of the InnoDB file structure as-well-as all of the data. You can not delete this file and have mysql recreate it. You will lose all of your MySQL InnoDB information.

Instead, you’ll have to backup all of your InnoDB databases, drop all of your InnoDB databases, stop the mysql server, remove the corresponding files, restart myssql, recreate your databases and then restore all your databases.

Below, I’ll demonstrate the steps that I took to accomplish this task.

Backing up your InnoDB MySQL Database

mysqldump -u USER --password=PASS DATABASE > DATABASE_DATE.sql

Drop your MySQL InnoDB Database

mysql -u USER --password=PASS
drop database DATABASE;
exit;

Stop MySQL Server

sudo service mysql stop

Remove the offending files

sudo rm /var/lib/mysql/ibdate1
sudo rm /var/lib/mysql/ib_logfile0
sudo rm /var/lib/mysql/ib_logfile1

Restart MySQL

sudo service mysql start

Recreate your InnoDB MySQL Database

mysql -u USER --password=PASS
create database DATABASE;
exit;

Restore your database from backup.

cat DATABASE_DATE.sql | mysql -u USER --password=PASS -D DATABASE

Now you should be all set and your ibdata1 file will be MUCH smaller.

Problem upgrading from Ubuntu 10.10 to 11.04 on Mac Pro no bootable device found

Posted by | Posted in Command-Line, OS X, Ubuntu | Posted on 14-11-2011

Recently, I had an issue upgrading my Mac Pro from Ubuntu 10.10 to 11.04. After the install and the initial reboot I received a “No Bootable device found”. This isn’t the first time that this has happened to me during a Mac Pro Ubuntu upgrade. Many tech boards had offered the reset your PRAM or reset your Open Firmware as the solution to re-detecting the ubuntu partition. All had failed. Resetting your PRAM and your Open firmware won’t tell the system that “X” partition is bootable. Ultimately it came down to re-blessing the ubuntu partition with in OS X.

I used a Mac OS X startup disk to gain access to a terminal session to run the bless command.

/usr/sbin/bless --device /dev/disk2s1 --setBoot --legacy

Creating a SSH Tunnel

Posted by | Posted in Command-Line, Guides, Linux, OS X, Server, SSH, Tunneling, Ubuntu | Posted on 01-02-2011

To traverse firewalls that block incoming SSH connections or to access a computer with a non routable IP address, you can create an SSH tunnel. When creating a SSH tunnel, you’ll generate the tunnel on the host system and connect it to another system.

With the SSH command, you’ll use the “-R” flag, that allows reversible communications or a reverse tunnel.

This command that I normally use:

It states, create a reversible SSH tunnel from this localhost port 22 to USER@SERVER on port 10000.

 
/usr/bin/ssh -R 10000:localhost:22 USER@SERVER

You’ll need to leave this connection open otherwise the tunnel will collapse. Normally I execute a program at the other end to keep the connection alive. Some SSHd configurations will drop connections due to inactivity.

To connect to the tunnel from USER@SERVER:

use ssh to connect to your USER account on the host server via port 10000.

 
/usr/bin/ssh USER@localhost -p 10000

this will give you the login prompt at the host server. User your login information or you can also setup SSH Keys.

How to get rid of cron warnings

Posted by | Posted in Bash, Code, Command-Line, Cron, Linux, OS X, Server, Ubuntu | Posted on 31-01-2011

If a cron job executes with unmanaged warnings for data, you’ll generate LOTS of emails to your account. Here is an easy way to redirect all that extraneous data.

Just add “>> /dev/null 2>$1″ to the end of each of your cron jobs.

 
0,10,20,30,40,50 * * * * /path/to/script >> /dev/null 2>&1

PHP and $argv

Posted by | Posted in Code, Command-Line, Functions, Guides, Linux, OS X, PHP, Server, Ubuntu | Posted on 31-01-2011

I’ve been adding $argv functionality to all of my PHP maintenance scripts. I am doing this, so I don’t have to edit a variable each time I want to execute it.

A PHP $argv example.

 
#!/usr/bin/php
< ? PHP
 
#	test.php
#	demonstration of $argv
#	Dan McCoy
#	January 31 2011
 
$command = $argv[1];
 
$GLOBALS['script'] = basename($argv[0]);
$GLOBALS['ver'] = "1.0";
$GLOBALS['file'] = $argv[2];
 
function help() {
	echo "Help for ". $GLOBALS['script'] ."\n";
 
	echo "-h \t Help prompt \n";
	echo "-v \t Version ". $GLOBALS['ver'] ."\n";
 
	echo "-g \t Grab file from X \n";
	echo "-f \t Reformat data from X \n";
	echo "-l \t Load data from X \n";
 
}// end help
 
function grab(){
	@exec('/opt/local/bin/wget http://webserver.com/page.php -O '. $GLOBALS['file'],$retval);
	return "Grab completed ". $retval[0] ." \n";
}// end grab
 
function reformat() {
	$fp = $GLOBALS['file'];
	$file = fopen($fp,'r');
	$data = fread($file,filesize($fp));
	fclose($file);
 
	$data = str_replace("<br /><br />","\n\n",$data);
	$data = str_replace("<br />","\n",$data);
	$data = strip_tags($data);
	$data = str_replace("\t","",$data);
 
	$fp = $GLOBALS['file'];
	$file = fopen($fp,'w');
	fwrite($file,$data);
	fclose($file);
 
	return "Reformating completed \n";
}// end reformat
 
function load() {
        $fp = $GLOBALS['file'];
        $file = fopen($fp,'r');
        $data = fread($file,filesize($fp));
        fclose($file);
	return $data;
}
 
function delete() {
	$ret = @unlink($GLOBALS['file']);
 
	if ($ret==TRUE){
		return "File has been deleted \n";
	}else{
		return "There was an ERROR deleting ". $GLOBALS['file']."\n";        
	}
}// end delete
 
function switch_default() {
	echo "Command not found \n";
	help();
}
 
switch($command) {
	case "-h":
	        echo help();
	break;
 
	case "-g":
        	echo grab();
	break;
 
	case "-f":
		echo reformat();
	break;
 
	case "-l":
		echo load();
	break;
 
	case "-d":
		echo delete();
	break;
 
	default:
		switch_default();
	break;	
}// end switch
 
? >

Executing more then 1 command at once

Posted by | Posted in Bash, Code, Command-Line, Cron, Linux, OS X, Server, Ubuntu | Posted on 30-01-2011

Many times, I have to execute sequential commands inorder to complete a task. Below is an example of how to do this.

/path/to/command argv1 && /path/to/command argv2

I normally use this sequential order when pulling raw data from a source and then reformatting it for my needs.

15 * * * * /usr/local/bin/pullsource grab && /usr/local/bin/pullsource reformat

Change the default crontab editor – Bash

Posted by | Posted in Bash, Code, Command-Line, Cron, Guides, Linux, OS X | Posted on 30-01-2011

You can change your default crontab (crontab -e) by setting the EDITOR environment variable in BASH:

hostname# which vi
/usr/bin/vi
hostname# export EDITOR=/usr/bin/vi
hostname# set | grep EDITOR
EDITOR=/usr/bin/vi
_=EDITOR
hostname#

Forbidden Error with .htaccess files in WordPress

Posted by | Posted in Apache, FollowSymlinks, Httpd, Linux, RewriteBase, RewriteCond, RewriteEngine, RewriteRule, Wordpress | Posted on 24-03-2010

I’ve been using WordPress for a number of years and decided to switch my permalink structure. It originally was set to the default setting of ?p=100. I wanted to increase my SEO so I changed it to the current format Year / Month / Day / Post-Name. When I switch over, wordpress automatically created a .htaccess file in my webroot folder and locked me out of my website with a Forbidden Error.

Forbidden
 
You don't have permission to access / on this server.

The .htaccess file that was created looked like this.

< IfModule mod_rewrite.c >
RewriteEngine On
RewriteBase /
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . /index.php [L]
< / IfModule >

After chatting with my web administrator, we came to the conclusion that FollowSymlinks in Apache was off be default. I needed to manually turn it on. The new .htaccess file looks like this.

< IfModule mod_rewrite.c >
Options +FollowSymlinks
RewriteEngine On
RewriteBase /
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . /index.php [L]
< / IfModule >

Additional Links:

Apache RewriteEngine
WordPress Codex for Permalinks

Syncing Date and Time with a NTP Server – Ubuntu

Posted by | Posted in Command-Line, Linux, Ubuntu | Posted on 23-09-2009

If you have several servers to manage, having them set with the same date and time is very important.

NTP stands for Network Time Protocol.

ntpdate – set the date and time via NTP

sudo ntpdate NTPServerAddress

or if you want to use ubuntu’s time server.

sudo ntpdate ntp.ubuntu.com

If you want to use multiple time servers, you can use a command like this. Where the new time server follows the address of your current server.

sudo ntpdate ntp.ubuntu.com time.apple.com

Links:
NTP Pool Project
Ubuntu Time Synchronization with NTP

Activating Mod_Userdir.c in Apache2 – Ubuntu

Posted by | Posted in Command-Line, Guides, Linux, Ubuntu | Posted on 16-09-2009

This morning, I was working on allowing users of my web server to create web pages with in their home directory. Normally this is indicated by a URL of http://server.com/~useraccount and is activated by default. But during the Apache install process the module for modifying the user directory wasn’t activated.

List of things you’ll need to do.

1) Add the module to your virtual host.
2) Enable the module in apache2
3) Restart apache2
4) Create a web directory folder in your home folder
5) make sure your permissions are correct

Step 1, adding the module to your virtual host.

Normally in Ubuntu, your enabled virtual hosts (or virtual hosts that are current running) are located in /etc/apache2/sites-enabled/your_domain_naming_scheme. For this example, I’ll always name my virtual hosts after their qualified domain name (qdn).

sudo nano /etc/apache2/sites-enabled/server.com

With in your < virtualhost > < / virtualhost > tags, you need to add and If Module statement. An If Module states, that if the module exists in the apache lib directory, to go ahead and load it into ram. The common use of the UserDir statement is command and then directory. So in this case UserDir public_html. public_html is the directory that will need to be created in each user’s home directory in order for apache to map the URL to file systems correctly. In this case the URL of http://server.com/~dmccoy will be mapped to the file directory path of /home/dmccoy/public_html/.

        < IfModule mod_userdir.c >
                UserDir html
        < /IfModule >

a complete virtual host might look something like this.

NameVirtualHost *:80
< VirtualHost server.com:80 >
        ServerAdmin dmccoy@server.com
 
        DocumentRoot /var/www/server.com
        ServerName server.com
        ServerAlias server
 
        < Directory />
                Options FollowSymLinks
                AllowOverride None
        < /Directory>
        < Directory /var/www/server.com/ >
                Options Indexes FollowSymLinks MultiViews
                AllowOverride None
                Order allow,deny
                allow from all
                # This directive allows us to have apache2's default start page
               # in /apache2-default/, but still have / go to the right place
              #RedirectMatch ^/$ /apache2-default/
        < /Directory >
 
        < IfModule mod_userdir.c >
                UserDir html
        < /IfModule >
 
        ScriptAlias /cgi-bin/ /usr/lib/cgi-bin/
        < Directory "/usr/lib/cgi-bin" >
                AllowOverride None
                Options ExecCGI -MultiViews +SymLinksIfOwnerMatch
                Order allow,deny
                Allow from all
        < /Directory >
 
        ErrorLog /var/log/apache2/server.com-error.log
# Possible values include: debug, info, notice, warn, error, crit,
        # alert, emerg.
        LogLevel warn
 
        CustomLog /var/log/apache2/server.com-access.log combined
        ServerSignature On
 
   Alias /doc/ "/usr/share/doc/"
    < Directory "/usr/share/doc/" >
        Options Indexes MultiViews FollowSymLinks
        AllowOverride None
        Order deny,allow
        Deny from all
        Allow from 127.0.0.0/255.0.0.0 ::1/128
    < /Directory >
< /VirtualHost >

Step 2, Once you’ve told the virtual host to load the UserDir module, you’ll need to make sure it is enabled in apache2. You can find this out by looking in the /etc/apache2/mods-enabled/ directory.

Both of these files will need to be in the mods-enabled directory.

userdir.conf
userdir.load

If they are not, make sure they are in the mods-available directory located at /etc/apache2/mods-available/. If they are in the mods-available directory, all you need to do is symbolically link them into the mods-enabled directory.

sudo ln -s /etc/apache2/mods-available/userdir.conf /etc/apache2/mods-enabled/userdir.conf
sudo ln -s /etc/apache2/mods-available/userdir.load /etc/apache2/mods-enabled/userdir.load

Step 3, You’ll need to restart apache2 in order to activate the userdir module.

sudo /etc/init.d/apache2 restart

Step 4, Now that the userdir module is active you can now add the “public_html” directory to any user that wants a website.

Normal command line steps might look something like this.

For yourself

 
cd ~
mkdir public_html
echo "test" > public_html/index.html

Or for another account

 
sudo mkdir /home/USER/public_html
sudo echo "test" > home/USER/public_html/index.html

Step 5, You should now be able to type in your browser http://server.com/~dmccoy and see “test” if you get a Forbidden error, you’ll need to change the permissions on your home directory folder and your public_html folder.

 
chmod 701 /home/dmccoy
chmod 705 /home/dmccoy/public_html

Repeat steps 4 and 5 for every account that would like web access.

Apache 2 Module mod_userdir
Apache 1.3 Module mod_userdir
Per-user web directories

Join the mailing list

Check your email and confirm the subscription