How to add TXT(SPF, DKIM) records for sub domain in Route 53 on Amazon

If you already followed this article and created a Route 53 record for your sub domain, that means we can proceed to add TXT records now. Please note that i assume that you use three different services for all this like Amazon for hosting, Godaddy/Name/NameCheap etc for domain, MailJet/SendGrid/ElasticEmail etc for emails send out.

Now for example, i want to send my users a welcome email when they join my blog [no-reply@blog.anl4u.com]. As you can see blog.anl4u.com. Now if you try to insert a SPF record for blog sub domain in TXT section of your hosted domain DNS area. Most probably it will throw an error, saying that there is already an entry for blog in CNAME section. Considering that you are using load balancer and added blog sub domain in the CNAME section for that load balancer.

So to overcome this issue, simply create a Route 53 record for your sub domain and attach your load balancer to that sub domain. Follow this article to create a Route 53 record.

Let’s add SPF record 1st, click on the Create Record Set button. Leave the name field as it is(it will show your sub domain). From the Type select TXT, in the Value field paste the text you get from the Email provider for SPF with quotes and save the entry.

route53-2

Create a DKIM entry now. In the Name field add this ‘mailjet_domainkey’ without quotes. As you can see, i am using mailjet. You can change that to yours. Choose TXT from the Type drop down. Input the long line in the value field with quotes and save it. Now you will have 5 records NS, SOA, A, 2 TXT.

route53-1

I assume you already added the NS records for your sub domain by following the article mentioned above. Now go to your Email sender service and validate the SPF and DKIM entries.

route53-4

And that’s it, happy sending emails.

Create Route 53 record for load balancer on Amazon

So you have few instances attached to a load balancer on Amazon and want to create a Route 53 entry for it to use the Name Server(NS) entries instead of the load balancer long A record which is not accepted by A record in domains DNS managers. Login to your AWS console area and click on Route 53 from the services dropdown menu. Click on the Create Hosted Zone button. Input your domain or sub domain name in the Domain Name field and save the entry by clicking Create Hosted Zone button.

route53-3

Once created, click on Go to Record Sets. And there you will see two records are created by default SOA(Start of authority) and NS(Name Servers).

Now click on the Create Record Set button and leave the Name field as it is. Select A record from the Type drop down. Select Yes from the Alias tab and choose the load balancer you want to attach and save the record. Now you have three records, SOA, NS and A record.

route53-5

Next copy the NS records and add them in the NS section of your domain. If you have added any CNAME for load balancer, you can safely remove that. Wait for the DNS propagation and check back your site later. You can use dig command in terminal to get the information about the domain.

In the next article i will show you how to add TXT(SPF and DKIM) records for a sub domain in route53. Stay tune.

Update (20-03-2013) : Check out this article for how to configure/add SPF and DKIM.

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

In some cases if you are running WordPress OR some other database extensive application on micro instance, you will experience slow page loading on various occasions.
There is a nice solution to that, install PHP APC to cache most the PHP code for fast loading.

I assume that you are already on the server console through SSH:

sudo su

1st let’s install few needed dependencies with php pear:

yum install php-pear httpd-devel php-devel

Now time to install APC by pecl command:

pecl install apc

Add a line to apc.ini to load the module:

echo “extension=apc.so” > /etc/php.d/apc.ini

Restart the httpd server:

service httpd restart

And that’s it, you will notice quite a difference now. The execution time per page will now reduce dramatically. I have tested it on a micro instance for a WordPress site, and i can confirm that the site loads fast now.

Continuation:

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

Part-2: Backup MySQL data into CSV

Part-3: Backup MySQL data into CSV with PHP

Part-4: Backup and Restore MySQL databases

Backup and Restore MySQL databases [Part 4]

In the last article we discuss how to backup data in csv format via PHP and command line. In this part we will backup MySQL databases via mysqldump command. I assume you are logged in to your Amazon instance.

How to backup:

1- Normal database backup

mysqldump -u root -p databasename > /var/www/html/databasename.sql

Enter your root password, change database name to the database you want to backup. Change the path from /var/www/html to your path if necessary. Change databasename.sql to your database.sql[any name can be given].

2- Backup MySQL database without data

mysqldump -d -u root -p databasename > /var/www/html/databasename_wo_data.sql

Same as 1 except it will dump database without any data.

3- Backup MySQL database with one table

mysqldump -u root -p databasename tablename > /var/www/html/database_table.sql

Here we will have one addition, enter the table name which we want to backup.

4- Backup MySQL database with one table without data

mysqldump -d -u root -p databasename tablename > /var/www/html/database_table_wo_data.sql

Same as 3 except this will dump it without any data.

How to restore:

mysql -u root -p databasename < /home/ec2-user/databasename.sql

Enter root password, change databasename and path to the backup sql file. Remember to create the database 1st if your sql file does not have the create database command. Login to MySQL via: mysql -u root -p and create database:

create database databasename default character set utf8;

That will do it, just change the database name.

Continuation:

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

Part-2: Backup MySQL data into CSV

Part-3: Backup MySQL data into CSV with PHP

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

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.

[cc lang=”php” escaped=”true” nowrap=”false” noborder=”true” line_numbers=”true” lines=”40″ tab_size=”4″]
<?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;
}
}
?>
[/cc]
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