Friday, October 5, 2012

MYSQL Incremental Backup



Blogs by Others:
http://www.sqlpronews.com/2010/0511.html
http://mossiso.com/2012/07/24/backing-up-mysql-with-replication-and-incremental-files-part-1.html
http://clean-clouds.com/2012/03/22/1785/

Leading Authority on MYSQL backups:
http://www.xaprb.com/blog/?s=incremental+backup

Different Methods:
http://dev.mysql.com/doc/refman/5.1/en/backup-methods.html

QUESTIONS TO ASK BEFORE ADOPTING A TECHNIQUE:
http://www.mysqlperformanceblog.com/2009/03/03/10-things-you-need-to-know-about-backup-solutions-for-mysql/

Questions on stackoverflow:
http://stackoverflow.com/questions/4411057/how-to-do-a-incremental-backup-in-mysql

Example Implementation:
http://standish.home3.org/mysql-backup

Cautions and Dangers:
http://www.mysqlperformanceblog.com/2009/05/14/why-mysqls-binlog-do-db-option-is-dangerous/

Commands:
http://linuxcommand.org/man_pages/mysqldump1.html
http://dev.mysql.com/doc/refman/5.1/en/mysqlbinlog.html

Must Knows:
http://dev.mysql.com/doc/refman/5.1/en/replication-rules.html
http://dev.mysql.com/doc/refman/5.1/en/backup-methods.html(3.5*)
http://dev.mysql.com/doc/refman/5.1/en/point-in-time-recovery.html(4*)


NOT SURE:
PHP: http://erlycoder.com/109/php-mysql-incremental-backup-restore-implementation-download-the-script-

PRODUCTS:
FREE:
1.http://www.zmanda.com/backup-mysql.html
usage:http://freelinuxtutorials.com/tutorials/full-incremental-mysql-backup-via-mysql-zrm/

PAID:
1.http://www.mysql.com/products/enterprise/backup.html



Thursday, September 13, 2012

PHP Design Patterns




Resources:

Friday, September 7, 2012

Database migrations using DbDeploy & Phing

Install Phing

The best way to download and install phing is using PEAR. This can be done on Linux or Windows assuming you have the pear script in your PATH with three shell commands.

shell> pear channel-discover pear.phing.info
shell> pear config-set preferred_state beta
shell> pear install [--alldeps] phing/phing

Example Application structure

As an example, we’re going to develop a simple application with the following directory structure.
example/
 |-- db/
 |   `-- deltas/
 |-- deploy/
 |   `-- scripts/  
 |-- library/
 `-- public/
The db directory contains sql files for using and manipulating our database and the deploy directory contains our build scripts that set the migrations in motion. The library directory contains our application code and the public folder will contain scripts and files accessible directly from the web, but will not be the focus of this article.

Build scripts

This section shows you how to develop the build scripts that will run the database migrations. The first file we need to create is a simple configuration file and should be fairly self explanatory. The file is written as key=value, lines beginning with a # are comments. Open your editor and save the following text as deploy/build.properties.
# Property files contain key/value pairs
#key=value

# This dir must contain the local application
build.dir=../

# Credentials for the database migrations
db.host=localhost
db.user=user
db.pass=password
db.name=example

# paths to programs
progs.mysql=/usr/bin/mysql
The next file we are going to create is the deploy/build.xml file. This is the file that tells Phing what we want it to do. I’m not going to go into too much detail describing each part of the build file, there are some comments, but you should consult the Phing Documentation for further details and enhancements.
<?xml version="1.0" ?>
<project name="PurpleMonkey" basedir="." default="build">

<!-- Sets the DSTAMP, TSTAMP and TODAY properties -->
<tstamp/>

<!-- Load our configuration -->
<property file="./build.properties" />

<!-- create our migration task -->
<target name="migrate" description="Database Migrations">  

<!-- load the dbdeploy task -->
<taskdef name="dbdeploy" classname="phing.tasks.ext.dbdeploy.DbDeployTask"/>

<!-- these two filenames will contain the generated SQL to do the deploy and roll it back-->
<property name="build.dbdeploy.deployfile" value="deploy/scripts/deploy-${DSTAMP}${TSTAMP}.sql" />
<property name="build.dbdeploy.undofile" value="deploy/scripts/undo-${DSTAMP}${TSTAMP}.sql" />

<!-- generate the deployment scripts -->
<dbdeploy 
url="mysql:host=${db.host};dbname=${db.name}" 
userid="${db.user}" 
password="${db.pass}" 
dir="${build.dir}/db/deltas" 
outputfile="${build.dir}/${build.dbdeploy.deployfile}" 
undooutputfile="${build.dir}/${build.dbdeploy.undofile}" />

<!-- execute the SQL - Use mysql command line to 
avoid trouble with large files or many statements and PDO -->
<exec
command="${progs.mysql} -h${db.host} -u${db.user}
-p${db.pass} ${db.name} &lt; ${build.dbdeploy.deployfile}" dir="${build.dir}" checkreturn="true" /> </target> </project>
That’s essentially all the magic we need. Now we just need to create our database.

Writing dbdeploy delta scripts

We haven’t actually created our database, so rather than create it the traditional way, we will actually use the migrations to create the initial schema. We’ve not actually decided what our example application does yet, but seeing as most tutorials make blogs, why don’t we give that a bash. We’ll start simple, one table with three columns called post.
Field Type Comment
title VARCHAR(255) The title of our post
time_created DATETIME The time we created our post
content MEDIUMTEXT The content of our post
Dbdeploy works by creating numbered delta files. Each delta files contains simple SQL to both deploy the change and roll it back. The basic layout of a delta file is like so.

-- //

-- Run SQL to do the changes

-- //@UNDO

-- RUN SQL to undo the changes

--//
We are creating our initial schema, so put the following content in db/deltas/1-create_initial_schema.sql
--//

CREATE TABLE `post` (
    `title` VARCHAR(255),
    `time_created` DATETIME,
    `content` MEDIUMTEXT
);

--//@UNDO

DROP TABLE `post`;

--//
Note: recent versions of mysql require space between -- and // 

Migrating the database

We are one step away from running our first migration. To keep track of the current version of the database, dbdeploy requires a table in the database. This is the only time we will have to interact with the mysql client directly.
shell> mysql -hlocalhost -uroot -ppassword example
mysql> CREATE TABLE changelog (
  change_number BIGINT NOT NULL,
  delta_set VARCHAR(10) NOT NULL,
  start_dt TIMESTAMP NOT NULL,
  complete_dt TIMESTAMP NULL,
  applied_by VARCHAR(100) NOT NULL,
  description VARCHAR(500) NOT NULL
);
mysql> ALTER TABLE changelog ADD CONSTRAINT Pkchangelog PRIMARY KEY (change_number,delta_set);
We are now ready to run our first migration and create the initial schema for our application.
shell>cd deploy
shell>phing migrate
All being well, we now have a posts table in our database. But what about an author for our blog posts? We’ll have to add another table and a foreign key from the post table to author table. To do this we create another delta, we call this one db/deltas/2-create_author_and_link_to_post.sql
--//

CREATE TABLE `author` (
    `author_id` INT(10) unsigned auto_increment,
    `name` VARCHAR(255),
    PRIMARY KEY (`author_id`)
);

ALTER TABLE `post` ADD `author_id` INT(10) unsigned NULL;

--//@UNDO

ALTER TABLE `post` DROP `author_id`;

DROP TABLE `author`;

--//
shell> cd deploy
shell> phing migrate

Friday, August 31, 2012

Useful Links for the Month

Installing Nginx for PHP, MySQL

Nginx is a high performance HTTP server. It provides very fast speed of delivering web pages, and uses fewer resources at the same time. With Nginx (Pronounced Engine X) A server with low resources will be able to serve many more clients, due to this, many popular websites use this (To conserve their resources, of course). In this blog post, I am going to discuss how to install Nginx, along with PHP, and MYSQL. You will need to follow very close attention to this article, else, one mistake and you may need to restart all over again.


The first thing you will need to do of course is own a VPS/Dedicated server. You will also need to have an SSH Client (Putty), and your username and password. If you have all of this, the first thing you need to do is login to your panel, and rebuild the server with some version of CentOS (it does not matter what version, really). Once you have done so, login to your SSH client, supplying the required information.


Next, we are going to begin the actual procedure. Follow these steps very closely.

1st We will need to install MYSQL. To do so, copy and paste the command below.

“yum install mysql mysql-server” – Without the quotations of course.


Next, we will set the server to start MYSQL whenever the system is rebooted. This will help significantly, else you will need to login to the console every time you need to reboot, and then you will need to type in a command to start MYSQL. To set the server to start MYSQL when the server boots, follow the steps below.


- Type in, copy “chkconfig – levels 235 mysqld on /etc/init.d/mysqld start”

- Once the above command has been entered, enter “netstat -tap | grep mysql”


The screen should then look something like this (The text).
[root@server1 ~]# netstat -tap | grep mysql
tcp 0 0 *:mysql *:* LISTEN 2388/mysqld
[root@server1 ~]#




Once you get that screen, you will want to reboot your MYSQL server, this will append any changes that you have made. To do so, copy this command “/etc/init.d/mysqld restart”.


Now, you need to actually setup MYSQL (Remember, all you just did was actually install the server). To start the install/configuration process, use this command “mysql_secure_installation”. All you will need to do is set your root password. Once you have done this, you are done with the installation of MYSQL. (Yay!).


When MYSQL is installed, it also installs PHP. The next step is to install the actual server (Nginx). This is very much easier then installing MYSQL.

The HTTP engine has not yet been released for final, for centos, though there are repositorys that may be used with it. To install the repositorys, you will need to type in the command as follows.

cd /etc/yum.repos.d/ 
wget http://centos.karan.org/kbsingh-CentOS-Extras.repo


It should then open a file like:[...]
# pkgs in the -Testing repo are not gpg signed
[kbs-CentOS-Testing]
name=CentOS.Karan.Org-EL$releasever - Testing
gpgcheck=0
gpgkey=http://centos.karan.org/RPM-GPG-KEY-karan.org.txt
enabled=1
baseurl=http://centos.karan.org/el$releasever/extras/testing/$basearch/RPMS/




You will need to set GPGCHECK to 0, and enabled to 1.

Once you have done that, run this command “yum install Nginx”.

Once that is done, we also need to make the server start on boot, to do this type in the following
command.chkconfig --levels 235 nginx on
/etc/init.d/nginx start




Now, reboot the server, and then type in the server IP in your browser. When you do so, you should see just a blank page..if you do everything is working correctly..the page is only blank because you haven’t added any content. If you would like to do so, you should install an FTP server. And set it to “/usr/share/nginx/html/index.html”.


That’s it! Your done!