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

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

Reset root password in Ubuntu (or Debian)

Posted by | Posted in Command-Line, Guides, Linux, Ubuntu | Posted on 28-07-2009

When you lose/misplace/forget your root password on a RedHat-based linux distro, resetting the root password isn’t too difficult. I’ve added those instructions at the bottom in case you are interested.

In Debian distros (which includes Ubuntu) you are required to have the root password in order to change the root password (even when you boot into single user mode, as you would with Redhat versions). While this adds a level of security, it also adds a level of annoyance when you have lost your password and are in a pinch.

Not to worry though, there is a way around (albeit, not as easy).

The first steps are the same for either Redhat distros or Ubuntu. Reboot the server, and get into the boot menu by pressing ESC to enter the menu:

ESC to enter the menu

Next you’ll need to choose which kernel you’re going to edit (it doesn’t really matter, but I usually choose the first in the list that is *not* recovery mode):

Select kernel to edit

Type ‘e’ to edit that line, and it will take you to another menu, which brings you to the line you’ll edit:

Edit kernel line

Scroll down to the ‘kernel’ like, and hit ‘e’ again. This will insert the kernel line into the grub> prompt, where you can add/edit the boot instructions. Yours will not be identical to the example below, but should have the same components:

 kernel /boot/vmlinuz-2.6.17-11-386 root=UUID=7d2d4dd5-f7fd-45ad-8bc9-51b1b284fcaa ro quiet splash

You will want to edit that to :

 grub> kernel /boot/vmlinuz-2.6.17-11-386 root=UUID=7d2d4dd5-f7fd-45ad-8bc9-51b1b284fcaa init=/bin/bash

When finished, hit enter to go back to the previous screen, and hit ‘b’ to boot that newly edited line.

After it boots, you’ll be at a shell prompt, but it isn’t writable! So any changes you make (like changing the root password) won’t hold through the reboot. So you’ll need to mount your root directory (which is simply labeled: / ) by typing the following command:

mount -o remount,rw /

Be sure to note the spacing in that command

Next you’ll need to change the root password. Since you are in the root prompt, it will change the password for user ‘root’ by default. I’ve added the root user below so you can see how to change other user passwords as well (simply replace ‘root’ with another user):

 passwd root

Enter the new password (one that you’ll remember, but is sufficiently secure).

It should reboot as it is now, but if you want to be safe you can reset the drive to read only:

mount -o remount,ro /

Then type:

 shutdown -r now

When your server reboots, you should now be able to log in as root with your newly set password. Granted, this will only work if you are using the cli (or have already set to allow root login through the settings in gnome/kde).

To perform this same task on a RedHat linux distro (RedHat, Fedora, CentOS, etc.) you follow the same instructions as above, except you add the word ‘single’ instead of ‘init=/bin/bash’ from above. Then continue to boot.

When you boot that kernel, it will drop you into a root prompt, at which point you can type passwd to change your password (with passwd, just like above). Next time you reboot your server, you will have root access with your newly formed password.

Backing up and Restoring your MySQL database – Ubuntu

Posted by | Posted in Bash, Code, Command-Line, Guides, Linux, MySQL, Ubuntu | Posted on 23-07-2009

If you manage any type of databases, you know the importance of backing up your data. Whether you have file corruption from a failing hard drive, a run away script or from a hacking attempt; loosing your databases can and will break your business. If you’ve never backed up your database, now is a good time to start.

The easiest way to backup your MySQL database is with “/usr/bin/mysqldump”. You’ll need to access this application via command. Whether it is from the a console or an SSH connection.

mysqldump – a database backup program
mysqldump [options] [db_name [tbl_name ...]]

There are three general ways to invoke mysqldump:

mysqldump [options] db_name [tables]
mysqldump [options] --databases db_name1 [db_name2 db_name3...]
mysqldump [options] --all-databases

By default, the mysqldump command will dump the contents of your database directly into your terminal and not into a file. This output can be redirected anyway and to anywhere you like. But it is commonly used in this manner:

mysqldump -u [username] -p [password] [databasename] > [backup.sql]
mysqldump -u root -p PASSWORD DATABASE > DATABASE-BACKUP-DATE.sql

[username] – your database username
[password] – the password for your database
[databasename] – the name of your database
[backup.sql] – the file to which the database contents needs to be directed to

If you don’t want to use your password in the command line, you can always prompt for your password by excluding it from your command.

mysqldump -u root -p DATABASE > DATABASE-BACKUP-DATE.sql
Enter password:

If you forgot your password, you’ll get an error like this.

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect

Or if you didn’t enter a password when one is needed, you’ll get this error.

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect

If you’d like the convenience of having the database automatically delete the old database before re-writing the content you can use the “–add-drop-table” feature.

 mysqldump --add-drop-table -u root -p PASSWORD DATABASE > DATABASE-BACKUP-DATE.sql

If you need to only backup specific tables of the database you can use this command.

mysqldump --add-drop-table -u root -p PASSWORD DATABASE TABLE> DATABASE-TABLE-BACKUP-DATE.sql

The syntax for the command is:

mysqldump -u [username] -p [password] [databasename] [table1 table2 ....]

[tables] – This is a list of tables to backup. Each table is separated by a space.

If you are a server administrator and need to backup multiple databases at the same time, you can use the next command. You just have to be sure that the “-u” user account has permission to read those databases.

mysqldump -u root -p PASSWORD --databases DATABASE1 DATABASE2 DATABASE3 > DATABASE-1-2-3-BACKUP-DATE.sql

Or if you just want to back up ALL the databases at one, use the “–all-databases” parameter to backup all the databases in the server in one easy step.

mysqldump --all-databases> ALL-DATABASES-DATE.sql

How to back up only the data structure.

While in development, most programmers only need to back up their database structure, not their test data. To do this, use the “–no-data” parameter.

mysqldump --no-data --databases DATABASE1 DATABASE2 DATABASE3 > DATABASE-1-2-3-Structure-Bakcup-DATE.sql

If you have large databases and not enough hard drive space to store them in the regular SQL, text format, you can compressing the backup file on the fly.

mysqldump --all-databases | bzip2 -c >all-databases-backup-date.sql.bz2
 
mysqldump --all-databases | gzip >all-databases-backup-date.sql.gz

If you are away from your servers, or have multiple servers that you manage, you can still backup your databases while you are away. Use the bash script below in conjunction with a cron-tab to automate your backups.

#!/bin/sh
date=`date -I`
mysqldump --all-databases | gzip > /var/mysql_backup/mysql-backup-all-$date.sql.gz

How to restore your databases.

If the unforeseen happens and you need to totally rebuild your databases, you can do so easily from the mysqldump SQL files.

mysql -u root -p PASSWORD DATABASE < DATABASE-BACKUP-DATE.sql

Mysql Synopsis:

mysql -u [username] -p [password] [database_to_restore] < [backup_file.sql]

Restoring from the compressed files.

gunzip < DATABASE-BACKUP.sql.gz | mysql -u root -p PASSWORD DATABASE

Or do 2 at a time.

cat DATABASE-BACKUP-1.sql DATABASE-BACKUP-2.sql | mysql -u root -p PASSWORD

Join the mailing list

Check your email and confirm the subscription