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.

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#

Applescript – folder and file counter

Posted by | Posted in Applescript, Code, OS X | Posted on 16-09-2010

set theFolder to (choose folder)
set thePath to POSIX path of theFolder
set fileCount to 0
set folderCount to 0
tell application "Finder"
	set theFileList to every item in folder theFolder
	repeat with x in theFileList
		if not character 1 of ((name of x) as string) is equal to "." then
			if kind of x is equal to "Folder" then -- "folder" depends fromĀ  the finder language 
				set folderCount to folderCount + 1
			else
				set fileCount to fileCount + 1
			end if
		end if
	end repeat
end tell
 
display dialog "There are " & folderCount & " folders andĀ  " & fileCount & " files in " & thePath

Applescript – Change folder color based on size

Posted by | Posted in Applescript, Code, OS X | Posted on 16-09-2010

property theFile : missing value
property theFolder : "test"
property thePosixPath : "/Users/user/Desktop/"
property theAliasPath : "Users:USER:Desktop:"
property theSize : missing value
 
tell application "Finder"
	set theSize to do shell script "du -s -k " & thePosixPath & theFolder & " |awk '{print $1}'"
	if theSize > 700000 then
		set label index of (folder theAliasPath & theFolder) to 2
	end if
end tell

Randomly Choose A Buddies’ Status Message As Your Own – OS X – Applescript

Posted by | Posted in Applescript, Code, OS X | Posted on 10-11-2009

tell application "iChat"
tell application "iChat" to set messagelist to get status message of every buddy
where status is not offline
set msglistcount to count messagelist
set theMessageList to {}
repeat with x from 1 to msglistcount
if item x of messagelist is not "" then
copy item x of messagelist to end of theMessageList
end if
msglistcount = msglistcount - 1
end repeat
set newStatus to some item of theMessageList
set status message to "i'm in your internets stealing your status messages : " & newStatus
end tell

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

A beginners guide to setting up MySQL – Ubuntu

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

MySQL is a very popular and widely used database system that can be used on almost any operating system, but primarly MySQL is used on LAMP (Linux/Apache/MySQL/PHP) systems.

Setting up a MySQL server is fairly easy. If you already know how to work a commmand-line interface, then you are well on your way to setting up a MySQL system. There are (4) things that you need to do, in-order to use a MySQL database system.

1) Install MySQL Server
2) Set a password for the root account
3) Set limitation privileges on the root account
4) Create a database

Installing mysql server via apt-get

$ sudo apt-get install mysql-server

On a default install, the root users does not need a password to access any databases and you can login with no password like this.

$ mysql -u root

If a password is required, use the extra switch -p:

$ mysql -u root -p
Enter password:

To add a root password when there isn’t one. Use the following command. Be sure to change the PASSWORD to your own password and to make sure that it is encapsulated with quotation marks.

mysqladmin -u root password "PASSWORD"

Now that we have the root account locked down with a password, we need to change its accessibility. While you are logged into mysql used these following commands to secure the root account.

mysql> use mysql;
mysql> delete from user where Host like "%";
mysql> grant all privileges on *.* to root@"%.DOMAIN.COM" identified by 'PASSWORD' with grant option;
mysql> grant all privileges on *.* to root@localhost identified by 'PASSWORD' with grant option;
mysql> flush privileges;
mysql> exit;

Now that we can login and feel secure, we create a database. Change DATABASE to any name you’d like.

mysql> create database DATABASE;
Query OK, 1 row affected (0.00 sec)

And that’s it. You can now check that you can connect to the MySQL server using this command:

$ mysql -u USER -p 'PASSWORD' DATABASE_NAME
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 61
Server version: 5.0.75-0ubuntu10.2 (Ubuntu)
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql>

Or, If you want to check your version

mysqladmin -u root -p version
 
Enter password: 
mysqladmin  Ver 8.41 Distrib 5.0.75, for debian-linux-gnu on i486
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
 
Server version          5.0.75-0ubuntu10.2
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
Uptime:                 3 hours 7 min 4 sec
 
Threads: 2  Questions: 6596  Slow queries: 0  Opens: 969  Flush tables: 1  Open tables: 19  Queries per second avg: 0.588

If you need to start|stop|restart MySQL, use the commands in /etc/init.d/mysql

sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start
sudo /etc/init.d/mysql restart

Notice :

preforming the above security options for the root account will break the /etc/init.d/mysql scripts.

sudo ./mysql status
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'
 * 
@:/etc/init.d$ sudo /etc/init.dmysql restart
 * Stopping MySQL database server mysqld
   ...fail!
 * Starting MySQL database server mysqld
   ...done.
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'

To fix this issue, you need to edit the /etc/mysql/debian.cnf file. There are (2) lines that need to be changed. Change the password hash to a new password. Unfortunately, this password is plain text.

# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = XXXXXXXXXXXXX
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
user     = debian-sys-maint
password = XXXXXXXXXXXXXX
socket   = /var/run/mysqld/mysqld.sock
basedir  = /usr

Then you need to log into MySQL and permissions to the “debian-sys-maint” user account.

> GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost' IDENTIFIED BY 'NEWPASSWORD' WITH GRANT OPTION
 
> FLUSH PRIVILEGES;

Now, if for some reason, you overwrite, or toast your root password, You need to reset it. At this point in time, the start|stop features of /etc/init.d/mysql will probably no longer work and you need to kill the process old school style.

ps -aux |grep mysql
 
root      5340  0.0  0.0   1872   548 pts/2    S    05:17   0:00 /bin/sh /usr/bin/mysqld_safe
mysql     5379  0.0  3.5 129412 27116 pts/2    Sl   05:17   0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock
root      5381  0.0  0.0   3052   704 pts/2    S    05:17   0:00 logger -p daemon.err -t mysqld_safe -i -t mysqld
root      5543  0.0  0.1   3336   792 pts/2    R+   05:23   0:00 grep mysql

What you are seeing here is a list of processes that contain the word “mysql”. From left to right you can see there is a USER and the PROCESS ID (4 digits), LOAD variables, RUN time and then the COMMAND. What we are interested in is the (4) digit process ID numbers. In this case, 5340, 5379 and 5381. The 5543 process, as you can see is “grep mysql”. This is our search process ID and most likely doesn’t exist anymore.

In-order to stop those (3) processes, you need to kill them. Literally. You need to use the “kill” command to stop the process from running, and to keep it from relaunching, you add the “-9″. This refers to the type of kill you are preforming. In this case the kill cannot be blocked and won’t relaunch.

# sudo kill -9 5340
# sudo kill -9 5379
# sudo kill -9 5381
kill: No such process

As you can see the 5381 process no longer existed after we killed the 5379 process.

Now that we have mysql completely stopped, we can restart the mysql server and change the root password. To be able to do this start mysql with the “–skip-grant-tables” followed by the “&” character. The skip grant tables will bypass the mysql database and won’t load the current usernames and passwords. the “&” will embed the process in the background, so you can keep working from the same command-shell.

sudo mysqld --skip-grant-tables &
 
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
090720  5:42:50  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
090720  5:42:50  InnoDB: Started; log sequence number 0 115369069
090720  5:42:50 [Note] mysqld: ready for connections.
Version: '5.0.75-0ubuntu10.2'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

Now that MySQL is restarted, go ahead and log back into the server and use the mysql database. Notice, we don’t need a password this time.

mysql -u root
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.75-0ubuntu10.2 (Ubuntu)
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
 
mysql> use mysql;
 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed

Now lets change the root password.

 
mysql> UPDATE user SET password = password('NEW PASSWORD') WHERE user = 'root' AND host='localhost';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

The Rows mached:1, means (1) row was updated and the password was updated successfully!

Flush your privileges and quit.

mysql> FLUSH privileges;
Query OK, 0 rows affected (0.00 sec)
 
mysql> quit;
Bye

Now restart your mysql server to reactivate the accounts and passwords.

sudo /etc/init.d/mysql restart
 * Stopping MySQL database server mysqld
090720  5:52:22 [Note] mysqld: Normal shutdown
090720  5:52:22  InnoDB: Starting shutdown...
090720  5:52:23  InnoDB: Shutdown completed; log sequence number 0 115369069
090720  5:52:23 [Note] mysqld: Shutdown complete
   ...done.
 * Starting MySQL database server mysqld
   ...done.
 * Checking for corrupt, not cleanly closed and upgrade needing tables.
[1]+  Done                    sudo mysqld --skip-grant-tables

Join the mailing list

Check your email and confirm the subscription