Archive for October, 2012

MySQL – Moving the datadir

October 29, 2012 Leave a comment

My default mysql location is /var/lib/mysql and I needed to restore a database that takes up a few GB of data. My root partition where /var is does not have enough space.

Move the location of mysql’s datadir. Follow the steps below. (I had upgraded mysql and had made some changes as noted in this earlier post)

1. Stop mysql

root@computer:/home/user# /etc/init.d/mysql stop
Stopping MySQL database server: mysqld.

2. Edit my.cnf
In my case I had renamed it to some thing else. So I put the file back.

root@computer:/home/user# cd /etc/mysql
root@computer:/etc/mysql# cp my.cnf.issues my.cnf

Now edit the line

datadir = /var/lib/mysql 


datadir = /home/user/mysqldatadir

3. Copy the contents of /var/lib/mysql to the new location /home/user/mysqldatadir.
I did this a few times so I am guessing that you only need to copy the folder that has the default mysql database. The one that normally shows up when you have a fresh mysql install. As shown below.

mysq> show databases;
| Database           |
| information_schema |
| mysql              |
root@computer# cd /var/lib/mysql
root@computer:/var/lib/mysql# ls
debian-5.1.flag  ibdata1  ib_logfile0  ib_logfile1  mysql  mysql_upgrade_info  carsdb

Now copy mysql folder to the new datadir location.
Also copy mysql_upgrade_info file.
Try and preserve the file permissions. The other files ie ibdata1 and ib_logfile* will be created. the folder carsdb is database that I do not need moved. (I intend to copy that over by first dumping the data using mysqldump.)

root@computer:/var/lib/mysql# cp -prv mysql /home/user/mysqldatadir/
root@computer:/var/lib/mysql# cp -prv mysql_upgrade_info /home/user/mysqldatadir/

4. Now change the permissions of the new datadir to belong to mysql

root@computer:/home/user# chown mysql:mysql  mysqldatadir/

5. Start up mysql

root@computer:/var/lib/mysql# cd /home/user/
root@computer:/home/user# /etc/init.d/mysql start
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..

root@computer:/home/user# ls -al mysqldatadir/
drwxrwxrwx  5 mysql   mysql       4096 Oct 26 16:25 .
drwxr-xr-x 81 user user     4096 Oct 26 16:24 ..
-rw-rw----  1 mysql   mysql   10485760 Oct 26 16:24 ibdata1
-rw-rw----  1 mysql   mysql    5242880 Oct 26 16:24 ib_logfile0
-rw-rw----  1 mysql   mysql    5242880 Oct 26 15:54 ib_logfile1
drwx------  2 mysql   mysql       4096 Jun 22 15:55 mysql
-rw-------  1 mysql   mysql          6 Jun 22 15:55 mysql_upgrade_info

6. Nothing else should change. So your previous users are still valid.

user@computer:~$ mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 57
Server version: 5.1.63-0+squeeze1 (Debian)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.


7. If mysql fails to start have a look at the syslog. Should be near the end of the file.

root@computer:/home/user# less /var/log/syslog


Categories: MySQL Tags:

Recovering root password

October 26, 2012 1 comment

You have forgotten the root password for a debian machine.

1. On the grub book screen type ‘e’ for edit.
2. Edit the line starting with linux, at the end add init=/bin/bash. Hit enter when done. Then hit ‘b’ to boot it.
3. Now you should be at a prompt. You need to mount as read writable your root partition so that the changes you make can take effect. Type

root@comp# mount -o remount,rw /
root@compp# passwd

4. Enter the new password twice. Then type

root@comp# sync
root@comp# reboot

(if the reboot does not work just power off)
5. Start up the machine and see if you can log in as root.


Categories: debian Tags:

MySQL – ERROR 1030 (HY000): Got error 28 from storage engine

October 25, 2012 Leave a comment

Got this error when trying to restore a database.
ERROR 1030 (HY000): Got error 28 from storage engine

Turns out that I was running out of space on /var partition where mysql is. The database I was restoring takes up about 3 GB, 1.1 GB was not enough. So the restore starts but does not end.

user@computer$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1      19G  3.1G   15G  18% /
tmpfs                 2.0G     0  2.0G   0% /lib/init/rw
udev                   10M   64K   10M   1% /dev
tmpfs                 2.0G     0  2.0G   0% /dev/shm
/dev/sda5     9.2G  4.2G  4.6G  48% /home
/dev/sda7     100G   94G  1.1G  99% /var

Just needed to free up space. Fortunately, I was able to delete the mysql bin longs that were taking up almost 80GB of space.

user@computer$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1      19G  3.1G   15G  18% /
tmpfs                 2.0G     0  2.0G   0% /lib/init/rw
udev                   10M   64K   10M   1% /dev
tmpfs                 2.0G     0  2.0G   0% /dev/shm
/dev/sda5     9.2G  4.2G  4.6G  48% /home
/dev/sda7     100G   13G   83G  14% /var

Once that was done, I was able to restore the database.


Categories: MySQL

python – Good Logging Practice in Python

October 18, 2012 1 comment
Categories: Interesting, python Tags:

Interesting – Geek and Poke – Yes

October 17, 2012 Leave a comment
Categories: geek and poke, Interesting

Whiptail – Using a menu

October 16, 2012 2 comments

Problem: I needed a menu.

Solution: Here is a sample of what I went with.

#! /bin/bash
whiptail --title "Backups" --yesno --defaultno "Start the sample program." 8 78

if [ $exitstatus = 0 ]; then
    while [ "$status" -eq 0 ]  
        choice=$(whiptail --title "Testing" --menu "Make a choice" 16 78 5 \
        "Application 1" "Start backing up the first application." \
        "Application 2" "Start backing up the second application." 3>&2 2>&1 1>&3) 
        # Change to lower case and remove spaces.
        option=$(echo $choice | tr '[:upper:]' '[:lower:]' | sed 's/ //g')
        case "${option}" in 
                whiptail --title "Testing" --msgbox "In first option" 8 78
                whiptail --title "Testing" --msgbox "In second option" 8 78
            *) whiptail --title "Testing" --msgbox "You cancelled or have finished." 8 78
    whiptail --title "Testing" --msgbox "You chose not to start." 8 78

Whiptail Menu example

python – Adding tab completion to the interactive prompt

October 13, 2012 Leave a comment

You want tab completion in the interactive prompt *but* you do not want to use ipython (Why?).

1. Create a file called .pythonstartup on your home directory. Then add the text below.

# python startup file
import readline
import rlcompleter
import atexit
import os
# tab completion
# history file
histfile = os.path.join(os.environ['HOME'], '.pythonhistory')
except IOERROR:
atexit.register(readline.write_history_file, histfile)
del os, histfile, readline, rlcompleter

2. Add the following to your ~/.bash_profile file.


3. Add to your ~/.bashrc

source ~/.bash_profile

4. Open a new terminal and check if PYTHONSTARTUP is enabled.

$ env | grep PYTHONSTARTUP

5. Finally fire up python on the prompt.

$ python
Python 2.6.6 (r266:84292, Dec 27 2010, 00:02:40) 
[GCC 4.4.5] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import csv
>>> csv.<tab>
csv.Dialect              csv.__doc__              csv.__subclasshook__(
csv.DictReader           csv.__file__             csv.__version__
csv.DictWriter           csv.__format__(          csv.excel
csv.Error(               csv.__getattribute__(    csv.excel_tab
csv.QUOTE_ALL            csv.__hash__(            csv.field_size_limit(
csv.QUOTE_MINIMAL        csv.__init__(            csv.get_dialect(
csv.QUOTE_NONE           csv.__name__             csv.list_dialects(
csv.QUOTE_NONNUMERIC     csv.__new__(   
csv.Sniffer              csv.__package__          csv.reader(
csv.StringIO(            csv.__reduce__(          csv.reduce(
csv._Dialect(            csv.__reduce_ex__(       csv.register_dialect(
csv.__all__              csv.__repr__(            csv.unregister_dialect(
csv.__class__(           csv.__setattr__(         csv.writer(
csv.__delattr__(         csv.__sizeof__(          
csv.__dict__             csv.__str__(             
>>> csv.

Expert Python Programming Book by Tarek Ziade

Categories: python