Archive | October 2011

How to use APT(Advanced Packaging Tool) Ubuntu/Debian


To know more about what can we do with the applications/packages i.e. apt-get help.Visit following link & browse through the pages.

http://www.debian.org/doc/manuals/apt-howto/index.en.html#contents

Advertisements

Import tab or space delimited data into MySQL database


If you have data that you need to bring into your MySQL database, there are a few ways to do it. Exporting data out of mysql is another topic, described here.

1. Using the LOAD DATA INFILE SQL statement

For security reasons, no one has the mysql FILE priv, which means you cannot “LOAD DATA INFILE”. You can, however, use a “LOAD DATA LOCAL INFILE” statement as long as you have a mysql prompt on our system and have uploaded the data file to your account here first.

The “LOAD DATA LOCAL INFILE” statement will only work from a MySQL prompt on our local system. It will not work from any web-based tool such as phpMyAdmin, and will never pull a file in directly off your own computer.

To import a file this way, first upload your data file to your home directory on our system with FTP or SCP. Then get a shell prompt on our system, and then a MySQL Monitor prompt so that you can issue the SQL that will import your file.

For example, suppose you have a data file named importfile.csv that contains 3 comma separated columns of data on each line. You want to import this textfile into your MySQL table named test_table, which has 3 columns that are named field1, field2 and field3.

To import the datafile, first upload it to your home directory, so that the file is now located at /importfile.csv on our local system. Then you type the following SQL at the mysql prompt:

LOAD DATA LOCAL INFILE ‘/importfile.csv
INTO TABLE test_table
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’
(field1, filed2, field3);

The above SQL statement tells the MySQL server to find your INFILE on the LOCAL filesystem, to read each line of the file as a separate row, to treat any comma character as a column delimiter, and to put it into your MySQL test_table as columns field1, field2, and field3 respectively. Many of the above SQL clauses are optional and you should read the MySQL documentation on the proper use of this statement.

2. Using a script to parse and import the file

You can also write a script in any programming language that can connect to MySQL (such as PHP) to open your data file, break it up into an array of lines that each represent a row of data, split each line up by the delimiter character (such as a comma ‘,’, tab ‘\t’, semicolon ‘;’, space ‘ ‘, etc.), and then perform invididual MySQL INSERT queries (one INSERT for each line) to insert all your data from the file into the appropriate table fields.

Such scripts are not difficult to write in less than 15 lines and can import data from text files just as effectively as a LOAD DATA LOCAL INFILE command. A working example script written in PHP appears below in the Annotations.

3. Importing a mysqldump

If your data file actually comes from another MySQL database, and not from Excel or any other source, then the most direct way to export and import your data would be to dump out your table or entire MySQL database on the original database server using the mysqldump command, FTP the resulting dump file to your account here, and then import the dump file at a shell prompt.

How to Back Up and Restore a MySQL Database


Back up From the Command Line (using mysqldump)

If you have shell or telnet access to your web server, you can backup your MySQL data by using the mysqldump command. This command connects to the MySQL server and creates an SQL dump file. The dump file contains the SQL statements necessary to re-create the database. Here is the proper syntax:

$ mysqldump –opt -u [uname] -p[pass] [dbname] > [backupfile.sql]
  • [uname] Your database username
  • [pass] The password for your database (note there is no space between -p and the password)
  • [dbname] The name of your database
  • [backupfile.sql] The filename for your database backup
  • [–opt] The mysqldump option

For example, to backup a database named ‘Tutorials’ with the username ‘root’ and with no password to a file tut_backup.sql, you should accomplish this command:

$ mysqldump -u root -p Tutorials > tut_backup.sql

This command will backup the ‘Tutorials’ database into a file called tut_backup.sql which will contain all the SQL statements needed to re-create the database.

With mysqldump command you can specify certain tables of your database you want to backup. For example, to back up only php_tutorials and asp_tutorials tables from the ‘Tutorials’ database accomplish the command below. Each table name has to be separated by space.

$ mysqldump -u root -p Tutorials php_tutorials asp_tutorials > tut_backup.sql

Sometimes it is necessary to back up more that one database at once. In this case you can use the –database option followed by the list of databases you would like to backup. Each database name has to be separated by space.

$ mysqldump -u root -p –databases Tutorials Articles Comments > content_backup.sql

If you want to back up all the databases in the server at one time you should use the –all-databases option. It tells MySQL to dump all the databases it has in storage.

$ mysqldump -u root -p –all-databases > alldb_backup.sql

The mysqldump command has also some other useful options:

–add-drop-table: Tells MySQL to add a DROP TABLE statement before each CREATE TABLE in the dump.

–no-data: Dumps only the database structure, not the contents.

–add-locks: Adds the LOCK TABLES and UNLOCK TABLES statements you can see in the dump file.

The mysqldump command has advantages and disadvantages. The advantages of using mysqldump are that it is simple to use and it takes care of table locking issues for you. The disadvantage is that the command locks tables. If the size of your tables is very big mysqldump can lock out users for a long period of time.

Back up your MySQL Database with Compress

If your mysql database is very big, you might want to compress the output of mysqldump. Just use the mysql backup command below and pipe the output to gzip, then you will get the output as gzip file.

$ mysqldump -u [uname] -p[pass] [dbname] | gzip -9 > [backupfile.sql.gz]

If you want to extract the .gz file, use the command below:

$ gunzip [backupfile.sql.gz]

Restoring your MySQL Database

Above we backup the Tutorials database into tut_backup.sql file. To re-create the Tutorials database you should follow two steps:

  • Create an appropriately named database on the target machine
  • Load the file using the mysql command:
$ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]

Have a look how you can restore your tut_backup.sql file to the Tutorials database.

$ mysql -u root -p Tutorials < tut_backup.sql

To restore compressed backup files you can do the following:

gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]

If you need to restore a database that already exists, you’ll need to use mysqlimport command. The syntax for mysqlimport is as follows:

mysqlimport -u [uname] -p[pass] [dbname] [backupfile.sql]

Bioconductor : Open source software for Bioinformatics


Bioconductor provides tools for the analysis and comprehension of high-throughput genomic data. Bioconductor uses the R statistical programming language, and is open source and open development. It has two releases each year, more than 460 packages, and an active user community.
URL: http://bioconductor.org/

Enable Remote MySql Ubuntu


Set mysql bind address

Before you can access the database from other computers in your network, you have to change its bind address. Note that this can be a security problem, because your database can be accessed by other computers than your own. Skip this step if the applications which require mysql are running on the same machine.

type:

sudo vi /etc/mysql/my.conf

and change the line:

bind-address=localhost 

to your own internal ip address e.g. 192.168.3.209

bind-address=192.168.3.209  

If your ip address is dynamic you can also comment out the bind-address line and it will default to your current ip.

If you try to connect without changing the bind-address you will recieve a “Can not connect to mysql error 10061”.

Then restart mysql service :

sudo service mysql restart

 

GPG error: http://ppa.launchpad.net


When you get GPG errors when running apt-get update on your Ubuntu machine, you have to import the the appropriate keys.

W: GPG error: http://ppa.launchpad.net lucid Release: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 4DEF31B9A9E345C0
W: GPG error: http://ppa.launchpad.net lucid Release: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 0F678A01569113AE
W: GPG error: http://ppa.launchpad.net lucid Release: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 7889D725DA6DEEAA
W: GPG error: http://ppa.launchpad.net lucid Release: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 8CA7A6E8E4FA953A

Just run the following command :

sudo apt-key adv --keyserver keyserver.ubuntu.com --recv-keys KEYID

where KEYID is number shown in error message which is   bold & underlined

& you can do the same for every missing key.

Fix Broken Packages Ubuntu


Few commands to fix broken packages for ubuntu.

  • sudo apt-get update
  • sudo apt-get upgrade
  • sudo apt-get autoclean
  • sudo apt-get clean
  • sudo apt-get autoremove
  • sudo apt-get -f  install
  • sudo dpkg --remove -force --force-remove-reinstreq package_name

					

Configure proxy for a perticular application in windows


Press (windows+r) key combination to open the Run Dialog box:

Type cmd in the Open filed & hit ok button

Then enter the command on command prompt which will be similar to following :

“C:\Program Files\R\R-2.13.0\bin\x64\Rgui.exe”  http_proxy=http://proxy.ibab.ac.in:3128/

where:

–“C:\Program Files\R\R-2.13.0\bin\x64\Rgui.exe”   is an absolute path of the application which requires internet connectivity using proxy

— http_proxy=http://proxy.ibab.ac.in:3128/ indicates proxy address & port

%d bloggers like this: