Archive

Archive for October, 2012

MySQL – Moving the datadir

October 29, 2012 Leave a comment

Problem:
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.

Solution:
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
from

datadir = /var/lib/mysql 

to

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              |
+--------------------+
So 
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
owners.

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

mysql> 

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

Source:
http://rajshekhar.net/blog/archives/90-Moving-the-MySQLs-datadir-directory..html

Advertisements
Categories: MySQL Tags:

Recovering root password

October 26, 2012 1 comment

Problem:
You have forgotten the root password for a debian machine.

Solution:
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.

Source
http://ivan.kanis.fr/recovering-root-password-on-debian-and-grub2.html
http://www.debiantutorials.org/reset-root-password-debian-gnulinux-etch-286.html

Categories: debian Tags:

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

October 25, 2012 Leave a comment

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

Solution:
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.

Source:
https://forums.mysql.com/read.php?35,84004,224693#msg-224693

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

exitstatus=$?
if [ $exitstatus = 0 ]; then
    status="0"
    while [ "$status" -eq 0 ]  
    do
        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 
            application1) 
                whiptail --title "Testing" --msgbox "In first option" 8 78
            ;;
            application2)
                whiptail --title "Testing" --msgbox "In second option" 8 78
            ;;
            *) whiptail --title "Testing" --msgbox "You cancelled or have finished." 8 78
                status=1
                exit
            ;;
        esac
        exitstatus1=$status1
    done
else
    whiptail --title "Testing" --msgbox "You chose not to start." 8 78
    exit 
fi

Whiptail Menu example

python – Adding tab completion to the interactive prompt

October 13, 2012 Leave a comment

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

Solution:
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
readline.parse_and_bind('tab:complete')
# history file
histfile = os.path.join(os.environ['HOME'], '.pythonhistory')
try:
    readline.read_history_file(histfile)
except IOERROR:
    pass
atexit.register(readline.write_history_file, histfile)
del os, histfile, readline, rlcompleter

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

PYTHONSTARTUP=~/.pythonstartup
export PYTHONSTARTUP

3. Add to your ~/.bashrc

source ~/.bash_profile

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

$ env | grep PYTHONSTARTUP
PYTHONSTARTUP=/home/<user>/.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.re
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.

Source:
Expert Python Programming Book by Tarek Ziade

Categories: python