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

How to insert csv file data into MySQL database

MySQL have this load data function, where we can insert data to a table from external files like csv, txt etc.
The process is quite simple, 1st lets login to mysql via terminal/console.

mysql -u root -p

Enter your MySQL password. Change root to any username(MySQL users) you would wish to login to MySQL.

mysql>

Now lets run the command.

mysql> LOAD DATA LOCAL INFILE ‘/var/www/site/site_users.csv’
INTO TABLE site_users FIELDS
TERMINATED BY ‘;’
ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’
IGNORE 1 LINES;

You will see this if command ran successfully:

Query OK, 370 rows affected (0.00 sec)
Records: 370  Deleted: 0  Skipped: 0  Warnings: 0

In the 1st line, we load a file from local dir and that is the reason we used ‘LOCAL’.
2nd line will insert the data into site_users table.
In the 3rd line we tell the command to break the fields by ; . You can check your csv file in txt format that whether its ; or comma(,).
4th line means that data is enclosed with the double quotes.
And lines are terminated by \n.
Last line will ignore the 1st line, which usually represents the data headers.

One thing i would like to mention here, make sure that the table fields and csv columns sequence matches.
Like name, address, phone are in the table, csv will also be name, address, phone. Which means data will be inserted into the right field.
And yeh column name can be changed, that does not matter like in table first_name and in csv name. What i mean to say here is that the sequence of fields need to match that right data goes to the right field.

That’s it.

Connect to database with PDO in PHP

We can connect to MySQL the easy way or the other way by writing a MySQL wrapper, that’s upto the programmer. In this post, i will not go for any wrapper or class but will share my approach with PDO, which itself is OO.

In the code, i declare a variable this is to switch for local and live server easily, if you don’t want it just remove that piece of code.

[cc lang=”php” escaped=”true” nowrap=”false” noborder=”true” line_numbers=”false” lines=”42″ tab_size=”4″]
<?php
$live = 0 ; // 1=live, 0=local

if($live === 0) :
$hostname = ‘localhost’;
$username = ‘admin’;
$password = ‘passlocal’;
$database = ‘local_db’;

elseif($live === 1) :

$hostname = ‘localhost’;
$username = ‘admin’;
$password = ‘passlive’;
$database = ‘live_db’;
endif;

if (defined(‘PDO::ATTR_DRIVER_NAME’)):
if(extension_loaded (‘PDO’)):
if(extension_loaded (‘pdo_mysql’)):
try {
$dbh = new PDO(“mysql:host=$hostname;dbname=$database”, $username, $password, array(PDO::MYSQL_ATTR_INIT_COMMAND => “SET NAMES utf8”));
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//echo ‘Connected to database<br />’;

}
catch(PDOException $e)
{
echo $e->getMessage();
}
else:
echo “PDO support is available and is loaded, but it seems that pdo mysql extension is not loaded.”;
endif;
else:
echo “PDO support is available, but it seems that it is not loaded.”;
endif;
else:
echo “Sorry, but it seems that PDO support is not available.”;
endif;
?>[/cc]
In the 1st few lines of if statement we check that whether POD driver is available if yes, is it loaded?. By default PHP already load this module and it is installed by default. but this is in case some thing is wrong. You can uncomment the echo line to make sure you are connected with the database.

And that’s it..

Show posts from your SMF fourm in your home/index page

SMF forum is quit popular open source forum application used by many websites as their main bulletin board. Some time we need to grab posts,topics from the board and show them on our home/index page or anywhere on our site. To make the things much easier, SMF provides all this in one script where we call a function with arguments and dress it up according to our needs. File is SSI.php and can be found in the root folder of the forum. You can also add extra parameters/piece of code to your SSI.php. In this post i will show you how to retrieve posts from SMF board with additional information like board, post author, date & time plus top poster and board statistics.

[cc lang=”php” escaped=”true” nowrap=”false” noborder=”true” line_numbers=”true” lines=”40″ tab_size=”4″]
$PostList = ssi_recentPosts(8, null, null, ‘array’);
[/cc]
The above piece of code will pull the last 8 posts from the forum db, as you can see i used array this is because i want to dress up my code like open links in new tab/window etc.

[cc lang=”php” escaped=”true” nowrap=”false” noborder=”true” line_numbers=”true” lines=”40″ tab_size=”4″]
foreach ($PostList as $Post) {
//echo ‘<pre>’, print_r($Post), ‘</pre>’;
echo “<tr>”;
echo “<div class=’forum_posts’>”;
echo ‘<td width=”350px”><a target=”_blank” href=’, $Post[‘href’], ‘>’, $Post[‘subject’], ‘</a></td>’,
‘<td width=”250px”><a target=”_blank” href=’, $Post[‘board’][‘href’], ‘>’,$Post[‘board’][‘name’],'</a></td>’,
‘<td width=”100px”><a target=”_blank” href=’, $Post[‘poster’][‘href’], ‘>’, $Post[‘poster’][‘name’], ‘</a></td>’,
‘<td width=”150px”>’,$Post[‘time’],'</td>’;
echo “</div>”;
echo “</tr>”;
}
unset($PostList);
[/cc]
The above code will loop the 8 results and print it on the page with post subject, board, poster name and date.

Now the additional stuff like top poster and board statistics:

[cc lang=”php” escaped=”true” nowrap=”false” noborder=”true” line_numbers=”true” lines=”40″ tab_size=”4″]
<strong>Top Poster : </strong><?php ssi_topPoster();?> <hr id=’line-hr’>
<strong>Forum Stats : </strong><?php ssi_boardStats(); ?> <hr id=’line-hr’>
[/cc]
Above are the main code which will pull the things for you, now lets put the whole code in one place with html and its css.

Full code:

[cc lang=”php” escaped=”true” nowrap=”false” noborder=”true” line_numbers=”true” lines=”40″ tab_size=”4″]
<h2>My Forum Recent Happenings!</h2>
<strong>Recent Posts : </strong>
<table class=’main’>
<tr>
<th>Post</th><th>Board</th><th>Posted by</th><th>Time</th>
</tr>

<?php
$PostList = ssi_recentPosts(8, null, null, ‘array’);
foreach ($PostList as $Post) {

echo “<tr>”;
echo “<div class=’forum_posts’>”;

echo ‘<td width=”350px”><a target=”_blank” href=’, $Post[‘href’], ‘>’, $Post[‘subject’], ‘</a></td>’,
‘<td width=”250px”><a target=”_blank” href=’, $Post[‘board’][‘href’], ‘>’,$Post[‘board’][‘name’],'</a></td>’,
‘<td width=”100px”><a target=”_blank” href=’, $Post[‘poster’][‘href’], ‘>’, $Post[‘poster’][‘name’], ‘</a></td>’,
‘<td width=”150px”>’,$Post[‘time’],'</td>’;

echo “</div>”;
echo “</tr>”;
}
unset($PostList);
?>
</table>
<strong>Top Poster : </strong><?php ssi_topPoster();?> <hr id=’line-hr’>
<strong>Forum Stats : </strong><?php ssi_boardStats(); ?> <hr id=’line-hr’>
[/cc]
You can put this code any where you want in you pages, just to make sure include the css below in the head tag of the header file(home/index etc etc) like:

[cc lang=”html” escaped=”true” nowrap=”false” noborder=”true” line_numbers=”true” lines=”40″ tab_size=”4″]
<link rel=”stylesheet” href=”style.css” type=”text/css” media=”screen” />
[/cc]
CSS file code:

[cc lang=”css” escaped=”true” nowrap=”false” noborder=”true” line_numbers=”true” lines=”60″ tab_size=”4″]
/* Table Layout */
table.main {
margin-top: .5em;
margin-bottom: .5em;
margin-left: .0em;
margin-right: .0em;
background: whitesmoke;
border-collapse: collapse;
font-size: 13px;
font-family: Arial;
}
table.main tr:hover {
background: #E6E6E6 !important;
}
table.main td {
border: 0px silver solid;
padding: 0.5em;
text-align:left;
border-bottom:1px silver solid;
}
table.main th {
border: 0px silver solid;
border-bottom:2px silver solid;
padding: 0.5em;
background: gainsboro;
text-align: left;
}
table.main caption {
margin-left: inherit;
margin-right: inherit;
}
/* Table Laout Ends */

/* Hr */
#line-hr {
height:1px;
margin-top:7px;
margin-bottom:7px;
background-color: silver;
border: 0px solid;
}
/* Hr Ends */

/* Forum Posts */
.forum_posts {
margin-top: 5px;
margin-bottom: 5px;
border-bottom: 0px solid #000;
}
.forum_posts a:link {
text-decoration: underline;
}
.forum_posts a:hover {
text-decoration: none;
color: #ea4900;
}
.forum_posts #time {
float: right;
}
/* Forum Posts Ends */
[/cc]

Save this file as style.css and include it in your head tag of the home/index page.

And that’s it, we are done… any question in mind use the comment section or you can ask in the SMF forum.