Backup MySQL data into CSV with PHP [Part-3]

In the previous article we discuss how to “Backup MySQL data into CSV” in terminal. In this post we will discuss how to create csv file with PHP and force it to download. We will be pulling data from MySQL database. 1st of all we will need connection to database, for that we will use this script. Change the necessary details and save it as conn.php. Now lets start with the csv.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
<?php
require_once 'conn.php';

$sql = $dbh->prepare("SELECT u.name, u.email, u.phone, u.city, s.filename FROM_UNIXTIME(s.sub_time, '%M %D %Y - %l:%i %p') from users u join submissions s on u.uid=s.uid where s.uid != '' and s.filename != '' order by s.sub_time desc");
$sql->execute();
$row = $sql->rowCount();

if ($row > 0){

$columns = array('NAME','EMAIL','PHONE','CITY',’FILENAME’,'TIME');

$filep = fopen('php://output', 'w');
if ($filep) {
$file = uniqid().'_'.time().'.csv';
header("Content-Type: text/csv");
header("Content-Disposition: attachment; filename=$file");
header("Pragma: no-cache");
header("Expires: 0");
fputcsv($filep, $columns);

while ($rows = $sql->fetch(MYSQL_NUM)) {
fputcsv($filep, $rows);
}
die;
}
}
?>

The script is quite straight forward and simple to use/follow. Little bit explanation: we are joining two tables here and then checking if any record found with rowCount function, if so we proceed to create csv. The array store the headers which will be column headers in the csv file. Giving a unique name to the file we will download and then using PHP fputcsv function to write data, quite easy and painless.

Continuation:

Part-1: Setup web server on Amazon AMI or CentOS

Part-2: Backup MySQL data into CSV

Part-4: Backup and Restore MySQL databases

Part-5: Speed up your website loading time by using PHP APC

Backup MySQL data into CSV [Part-2]

In my last article i showed you how to “Setup web server on Amazon AMI or CentOS“, to continue that article further i will discuss how to backup MySQL data into CSV format. So let’s start, login into your machine through terminal and login to MySQL server and choose database by command ‘use databasename‘.  Let’s dump a table to csv:

SELECT * INTO OUTFILE ‘/var/www/html/users.csv’ FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ ESCAPED BY ‘\\’ LINES TERMINATED BY ‘\n’ FROM users;

The above command will output all the columns and data into a csv file which will be saved in /var/www/html, change this according to your need.

Now let’s say we have multiple tables and they are connected with each other through keys. For example user table with votes table.

SELECT ‘Name’, ‘Email’, ‘Time’,’Phone’,’City’ UNION SELECT u.name, u.email, FROM_UNIXTIME(v.vote_time, ‘%M %D %Y – %l:%i %p’) AS timestamp, u.phone, u.city INTO OUTFILE ‘/var/www/html/users_votes.csv’ FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ ESCAPED BY ‘\\’ LINES TERMINATED BY ‘\n’ from users u join votes v on u.uid=v.uid;

We are joining two tables here on uid, which is user id. Name,Time,Phone,Email,City will be the column headers in CSV for each column selected from database. vote_time i assume is stored in db as unix timestamp. That is quite straight and simple, nothing fancy there.

Of course you can join many tables to output your desire data. That’s it for now. In the next article i will show you how to save/download data in CSV format/file using PHP and MySQL with fputcsv function. Stay tune.

Continuation:

Part-1: Setup web server on Amazon AMI or CentOS

Part-3: Backup MySQL data into CSV with PHP

Part-4: Backup and Restore MySQL databases

Part-5: Speed up your website loading time by using PHP APC

How to create live bootable usb via terminal in Linux

Before creating a live usb from terminal, you have to format your usb and make sure you are formatting/writing to the correct device. In terminal we will use dd command for creating a live bootable usb from any distro iso. 1st of all, lets find out our usb device:

sudo fdisk -l

That will list down your storage devices.

fdisk-terminal

You can see from the screen shot, mine is detected as sdc, yours can be sdc, sdf or sdb etc.

NOTE: Before formatting, please backup your files from the usb as it will completely erase the device.

Now if you want to format this device, check out this link. After formatting, run the fdisk command again just to be on the safe side.

Now type the following command while changing the paths:

dd if=/path/to/iso-file of=/dev/sdc

bs can be added to the end of the command, bs{byte size}. bs=4096 OR bs=2048

dd if=/path/to/iso-file of=/dev/sdc bs=2048

Remember to not include the integer for the device, as it is representing the partition like sdc1 or sdc2 etc. Just use the device name like /dev/sdc or /dev/sdf.

Good luck!

Setup web server on Amazon AMI or CentOS [Part-1]

If you are using Amazon aws with ec2 instances, then i assume you already have the pem file to connect to the instances. Normally you will have different instances for your projects. I normally setup either Amazon AMI or Cent OS, prefer Amazon AMI as it is also based on Cent OS. Cent OS is best and easy OS for servers. I love the configuration and level of interaction it provides.
Let’s say you want to install a web server with MySQL and PHP, run the following command:

sudo yum install httpd mysql-server php php-mysql mysql php-xml php-pdo php-odbc php-soap php-common php-cli php-mbstring php-bcmath php-ldap php-imap php-gd mod_ssl

That will install all the necessary modules and packages to run a fully functional server.
In some cases we don’t need/want a web server, just database server will do it:

sudo yum install mysql-server mysql

That will only install database server on the instance.
Now let’s say we dont need either PHP or MySQL:

sudo yum install httpd

Some time we don’t need database server on the same web server instances:

sudo yum install httpd php php-xml php-mysql php-pdo php-odbc php-soap php-common php-cli php-mbstring php-bcmath php-ldap php-imap php-gd mod_ssl

That will install all necessay packages excluding MySQL.

Now if you have a MySQL server, for sure you would like to make it secure, that is easy:

sudo /usr/bin/mysql_secure_installation

The above command will ask you some questions, answer the questions carefully and you will get through it.
Don’t forget to remove anon users and test database while running the above command.

If you have .htaccess file and want to utilize mod rewrite, enable it in /etc/httpd/conf/httpd.conf in the server directory section:

AllowOverride None
to
AllowOverride All

To auto restart your server and database server in case the system is restarted:

sudo chkconfig httpd on
sudo chkconfig mysqld on

Manually start the servers:

sudo service httpd start
sudo service mysqld start

Some interesting information i will share in upcoming articles: Load balancer for multiple instances, configuring SSL on load balancer, creating databases and changing password, Dumping databases and saving table/database to csv and save only database schema. And separating your database server from web servers with load balancer and adding the instances to database for security purpose to read/write into database instance.

So stay tune, there are more and plenty to come.

Continuation:

Part-2: Backup MySQL data into CSV

Part-3: Backup MySQL data into CSV with PHP

Part-4: Backup and Restore MySQL databases

Part-5: Speed up your website loading time by using PHP APC

Configure switchable graphics in Elementary/Ubuntu [Luna/12.04]

Currently running Elementary Luna on my laptop with switchable graphic cards. Laptop some time goes hot near the touch pad area with fan noise and hotness. Did a bit research and find out that it was ATI card ON by default. Battery usage was 1 to 1.30 hours on ATI by default. I have ATI 6600M with Intel integrated graphic card. From my previous openSUSE experiments, tried some solutions and can say things are much better now with Intel card[switched]. Battery life goes from 1/1.30 to 4/5 hours dramatically, no more heat issue and noise. Temperature dropped to around 50C for CPU and around 47C for HDD, which i still think is a bit high(working on it). So here is what i did.

Just for monitoring purpose, install lm-sensors and hddtemp. Sudo apt-get install in terminal will do it.

sensors-hddtemp

Now edit the following file with sudo(assumed terminal is open):

sudo vim /etc/rc.local

Add the following lines before exit 0.

chown -R $USER:$USER /sys/kernel/debug
echo OFF > /sys/kernel/debug/vgaswitcheroo/switch

Reboot, and see if that helps. You can view in terminal which graphic card is active now:

sudo cat /sys/kernel/debug/vgaswitcheroo/switch

IGD = Intel
DIS = ATI [Discrete]
Sample result:

0:IGD:+:Pwr:0000:00:02.0
1:DIS: :Off:0000:01:00.0

Even can switch back to ATI [need to logout and login for this to take affect, discrete card will be active(ATI)](not tested):

echo DIS > /sys/kernel/debug/vgaswitcheroo/switch

To turn OFF the in-active card:

echo OFF > /sys/kernel/debug/vgaswitcheroo/switch

I will do more research on this to minimize the heat. Will share my findings here. Stay tune.

Restore/Re-install grub in Elementary/Ubuntu [Luna/12.04]

There are some cases, where we play with the system and suddenly realizes that we messed up some thing. This post is about grub in Elementary Luna based on Ubuntu 12.04. So the process is the same for both. Lets a take a simple scenario that you have messed up your grub and now when you boot your system you see this error:

error: no file found: …
grub rescue>

This can be different, like: error: no device found. So now the questions is how to restore it. Well, this is simple. Yes, i know there are many tuts and threads about this issue over the net, but i did not find most of them very easy to follow OR is confusing. I will follow the easy way. So lets begin, shall we?.

Boot your system from Live CD of Elementary/Ubuntu. Open terminal and type:

sudo fdisk -l

Sample output:

Disk /dev/sda: 500.1 GB, 500107862016 bytes
255 heads, 63 sectors/track, 60801 cylinders, total 976773168 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x4f431832

Device Boot      Start         End      Blocks   Id  System
/dev/sda1            2048    83970047    41984000    7  HPFS/NTFS/exFAT
/dev/sda2        83970048    84174847      102400    7  HPFS/NTFS/exFAT
/dev/sda3   *    84174848   190670847    53248000   83  Linux
/dev/sda4       190672894   976773119   393050113    5  Extended
/dev/sda5       190672896   317648895    63488000    7  HPFS/NTFS/exFAT
/dev/sda6       317650944   968376319   325362688   83  Linux
/dev/sda7       968378368   976773119     4197376   82  Linux swap / Solaris

From the above command, note the partition where your OS is installed. Now lets create a directory to mount the root partition in it.

sudo mkdir /media/sda3

You can create this directory anywhere you want, like in your home directory etc. sda3 is the partition where your system is installed. Not necessary, you use sda3, can be any named directory, like /media/temp etc. Now lets mount the partition in the directory we created:

sudo mount /dev/sda3 /media/sda3

After mounting, we will install the grub by running this command:

sudo grub-install --root-directory=/media/sda3 /dev/sda

The /dev/sda  is your hard disk name, which is shown from the output of fdisk -l. And that’s it, grub is installed. Reboot and things will be normal this time.

If every thing is cool, skip the following lines. The following are just extra steps in case some one face it.

——————————————————————————————

If you see the following error after booting:

Error: unknown command ‘gfxmode’

No worries, in latest Ubuntu 12.04, grub relies on a function in linux script which is in /etc/grub.d/ProxifiedScripts. BTW, this error is not a show stopper, you can still boot to your OS. The best way to avoid is install grub-customizer and enable ‘script code’.

sudo add-apt-repository ppa:danielrichter2007/grub-customizer
sudo apt-get update
sudo apt-get install grub-customizer

Open grub customizer and tick the check box in front of ‘script code‘. And that will do it.

grub-customizer

——————————————————————————————

Good luck!