Restoring a single table from a full mysqldump file

Yesterday I needed to restore one table from larger database dump file. To begin with I found out which lines I needed end with sed i got my data.

sed -n 955,991p data.sql > new_data.sql

Pretty easy, but in a large file you have to work your way to find the line you need. The solution I found was more specific and works like a charm. The first version of the command was a rather inaccurate because it was missing the "DROP TABLE IF EXIST" for the table you are restoring and you need to edit out few lines from the bottom of the new file before using it in order to prevent deleting the next table, "address". leaves "DROP TABLE IF EXIST" for the next table at the end of the new file.

sed -n -e '/CREATE TABLE.*`person`/,/CREATE TABLE/p' data.sql > new_data.sql

Leves this at the end og the new file.

--
-- Table structure for table `address`
--

DROP TABLE IF EXISTS `address`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `address` (

Then I came a cross better solution to get your table data. While this is the backup layout.

--
-- Table structure for table `person`
--

DROP TABLE IF EXISTS `person `;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `person` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` varchar(225) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `person`
--

LOCK TABLES `person` WRITE;
/*!40000 ALTER TABLE `person` DISABLE KEYS */;
INSERT INTO `person` VALUES (1,'Emma'),(2,'Noah'),(3,'Olivia'),(4,'Liam'),(5,'Sophia');
/*!40000 ALTER TABLE `person` ENABLE KEYS */;
UNLOCK TABLES;

As you can see "UNLOCK TABLES" is the last line, so that is what we look for in the sed ccommand.

sed -n -e '/DROP TABLE IF EXISTS.*`person`/,/UNLOCK TABLES/p' data.sql > new_data.sql

Now your new file should be ready to use, but I recommend that you check before you use.

--cheers

Using Python, MySQL and UTF-8

I have been spending way to long time figuring out how I can encode my data to utf8 when fetching records from MySQL database. All the time I was focusing on encoding the output, resulting in error like this one.

UnicodeDecodeError: 'ascii' codec can't decode byte 0xe1 in position 1: 
ordinal not in range(128)

The solution I finally found is to set the character set when I prepare the MySQL connection

con.set_character_set('utf8')

Ending up in my code like this.

!/usr/bin/env python
# -*- coding: utf-8 -*-
import MySQLdb as mdb

def getname(p):
    con = mdb.connect('hostname', 'username', ' password', 'databasname')
    con.set_character_set('utf8')
    sql = "SELECT cellname1 from tablename where cellname2='%s'"%(str(p))
    cur = con.cursor()
    cur.execute(sql)
    row = cur.fetchone()
    if not row:
        return 'n/a'
    else:
        return str(row[0])
    cur.close()

Now everything works like a charm.

Export MySQL data to CSV file

As much as importing data into MySQL is helpful, you also might like to export your data to CSV file format that is widely supported. You can open your data with spreadsheet like Excel, Libreoffice Calc, Google sheets and more. There are various techniques of how to export a MySQL table to a CSV file, but I'm going to demonstrate two methods.

Method 1: MySQL

Following SQL query will dump all the records from a table called "person" into the file /tmp/export_person.csv as a CSV file.

SELECT * from person INTO OUTFILE '/tmp/export_person.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table;

Take note of that the directory must be writable by the MySQL database server. Also that it will not overwrite the file if it already exists.

Method 2: Command line

You can also use command line in Linux to get your report. To get the same data as the example above

$ mysql -u dbusername -pdbpassword exampledb -B -e "select * from \'person\';" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > export_person.csv

• mysql : the executable file (should be where MySQL is installed)
• -u dbusername : a switch and option for the username to execute the SQL with
• -p dbpassword : a switch and option for the password of the user
• exampledb : the database to run the SQL against
• -B : instructs that the output should be tab delimited (we will convert this to commas later in the command)
• -e “the sql statement here” : the SQL statement to run returning your data
• | sed ’s/\t/”,”/g;s/^/”/;s/$/”/;s/\n//g’ : sed is a Unix stream processor, essentially allow for transformations in this case. Here we have four sed commands that change the tabs to double quotes, adds double quotes to the beginning and end of each line and adds a new line marker at the end of each line.
• > export_person.csv : outputs the results to the file named export_person.csv

I hope that this is somewhat helpful.

See also Import csv to MySQL

Installing LAMP (Linux, Apache, MySQL and PHP) On Linux

I'm running XLinux on my laptop and need to install webserver with php and MySQL, LAMP. (Linux, Apache, MySQL, PHP). Really thats a simple task, and I'm going do demostrate how

Step one, Install Apache

Open your terminal CTRL+ALT+T and enter following line.

sudo apt-get install apache2

Test Apache using this address in browser http://localhost/ and you will see a message saying:

It works!
This is the default web page for this server.
The web server software is running but no content has been added, yet.

Step two, install PHP

sudo apt-get install php5 libapache2-mod-php5

Restart Apache

sudo /etc/init.d/apache2 restart

Now we can test php by adding to the terminal

sudo gedit /var/www/test.php

This will open up a file called test.php
Add following line

<?php
phpinfo();
?>

Open http://localhost/test.php in browser, you should see info about your php

Step three, install MySQL

Open the Terminal and then copy/paste or type this line

sudo apt-get install mysql-server

And it's done. You can now access your MySQL server like this

mysql -u root -p

--cheers

Import csv to MySQL with php

I have small project at work where I need to import data from over 80 excel document and from more than one sheet from each into database. And I also needed to enter data from the web form to the database at the same time to identify each port with equipment and location. After converting sheet from .xls to .cvs I import the file by upload it trough the web form.
Not very hard task but I had some issues that I came a cross.
There is a simple method to import a file to database. From the command line I would do this.

mysql > LOAD DATA INFILE 'filename' INTO TABLE tablename FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' (field1,field2,field3);

Ok, this was easy but. In my case it seams that the file neaded to be in /var/lib/mysql/databasename for this command to work and I did not figure out how to get the file there when I uploaded it with php, guess it's permission issue and i'm not going to change them. Adding LOCAL to the command is the way to import the file from my directory, but it considered an administrative task and it returned error.

In the connection parameters I had to add "false,128" to get it to work.

$connect = mysql_connect("localhost","user","pass",false,128);

mysql_select_db("database",$connect);

 mysql_query("LOAD DATA LOCAL INFILE 'upload/$filename' INTO TABLE database FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\\n' (field1,field2,field3) set field4='value1',field5='value2';") ;

Now it works like a charm.
--cheers

See also how to export MySQL data to csv file