Take MySql database Backups remotely from command line

Feb 07,2013 No comments yet

If you are a developer it is obvious that you take frequent backups of the project you develop. If you are a web developer chances are that you always login to cpanel or some other dashboard provided by your hosting provider to take backups. Some tools provide scheduled backups on server. This is fine if database is small. But if database is big or if you have many databases , this process gonna take large amount of space on your server. To free space you need to download to your computer and delete remote backups.

If you are this stage and you are searching for better alternative this is the one line command that can save you some time.

mysqldump --host=[hostname] --opt --user=[username] --password=[passwod] [database] --add-drop-table   -r  C:\path\on\your\computer\back-up-name.sql

1	mysqldump --host=[hostname] --opt --user=[username] --password=[passwod] [database] --add-drop-table -r C:\path\on\your\computer\back-up-name.sql

You can take help of scheduler on your computer to take periodic backups of the database on store them on your computer. This way you can save lot of time. remote MySql need to be enabled in few hosts for this scripts to work. Also if mysql environment variable is not set you need to navigate to that folder and run command for this to work.

WordPress: server error

Dec 22,2012 No comments yet

If you try to move wordpress sites between servers you may experience this issue.

HTTP Error 500 (Internal Server Error): An unexpected condition was encountered while the server was attempting to fulfill the request.

I had no idea what causing this. It was working fine with the other server. After a lot of debugging and googling I understood this error might be because PHP memory limit. So I tried to increase memory limit. Again as there are couple ways to increase this variable and not all hosts allow all of them I had to face some issues. below are the possible ways to increase PHP memory limit and get rid of this error to make your site work again.

1. php.ini

By changing following variable you can increase emory limit value in php.ini

memory_limit = 128M ;

1	memory_limit = 128M ;

But chances are that you might be n shared hosting and your hosting provider might not allow you to make any changes directl to php.ini. In that case you can set this in .htaccess or in run time using ini_set variable.

php_value memory_limit 64M

1	php_value memory_limit 64M

Above is the .htaccess way of increasing memory limit. Again some hosts wont allow you to set PHP variables using .htaccess . In this case you may experience Internal server error as those lines code in .htaccess would be treated as invalid. In that case you can change memory limit value using ini_set like below. You can also set a wordpress constant WP_MEMORY_LIMIT in wp-config.php and this is better way.

ini_set('memory_limit', '128M');

define('WP_MEMORY_LIMIT', '164M'); /*wordpress way*/

1

2

3

ini_set('memory_limit', '128M');

define('WP_MEMORY_LIMIT', '164M'); /*wordpress way*/

Cakephp 2.x Auth Component : Change password

Dec 01,2012 No comments yet

Changing password of any user is easy if you are using Auth Component. I have been Using Auth Component for many of my projects developed in Cakephp. Below are the steps for password change functionality of Auth component in CakePHP 2.x.

Create a new function called pass() in the controller .( users controller in my case )

( you can name the function anything. )

    public function pass() {
        if ($this->Auth->user('id')) {   // Just to  make sure User is logged 
            $this->User->id = $this->Auth->user('id');  // Set User Id
            if (!$this->User->exists()) {
                throw new NotFoundException(__('Invalid user'));
            }
            if ($this->request->is('post')) {
                if ($this->User->save($this->request->data)) {
                    $this->Session->setFlash('Password has been changed.');
                } else {
                    $this->Session->setFlash('Password could not be changed.');
                }
            } else {
//                $this->data = $this->User->findById($this->Auth->user('id'));
            }
        }
    }

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

public function pass() {

if ($this->Auth->user('id')) { // Just to make sure User is logged

$this->User->id = $this->Auth->user('id'); // Set User Id

if (!$this->User->exists()) {

throw new NotFoundException(__('Invalid user'));

}

if ($this->request->is('post')) {

if ($this->User->save($this->request->data)) {

$this->Session->setFlash('Password has been changed.');

} else {

$this->Session->setFlash('Password could not be changed.');

}

} else {

// $this->data = $this->User->findById($this->Auth->user('id'));

}

Setting user Id is important here or a new record is created every time you try to change password. You can either pass id through query params or you can get it from Auth. In the above example User Id is taken from Auth.

Now create a view file with function name in the views/{controller_name}/ directory. In this case it would be views/Users/pass.ctp

Create a form to change password in that view file

<div>
    <?php echo $this->Session->flash(); ?>
<?php echo $this->Form->create('User'); ?>
     <fieldset>
          <legend><?php echo __('Change Password'); ?></legend>
     <?php
                echo $this->Form->input('current_password',array('label'=>'Old password','value'=>'','type'=>'password'));
                echo $this->Form->input('password1',array('label'=>'New password','type'=>'password'));
                echo $this->Form->input('password2',array('label'=>'Confirm password','type'=>'password'));
     ?>
     </fieldset>
<?php echo $this->Form->end(__('Submit')); ?>
</div>

1

2

3

4

5

6

7

8

9

10

11

12

13

<div>

<?php echo $this->Session->flash(); ?>

<?php echo $this->Form->create('User'); ?>

<?php

echo $this->Form->input('current_password',array('label'=>'Old password','value'=>'','type'=>'password'));

echo $this->Form->input('password1',array('label'=>'New password','type'=>'password'));

echo $this->Form->input('password2',array('label'=>'Confirm password','type'=>'password'));

?>

</fieldset>

<?php echo $this->Form->end(__('Submit')); ?>

</div>

As we are not redirecting to other method or controller . it is suggested to show message so user can know password is changed.

Now comes the magic in the model. We are asking for current password. And if that is correct we are going to check if both new passwords match. If they match change password and display message.

to the $validate array in your model add this

var $validate = array(
    'current_password' => array(
        'rule' => 'checkCurrentPassword',
        'message' => '...'
    ),
    'password1' => array(
        'rule' => '',
        'message' => '...',
    ),
    'password2' => array(
        'rule' => 'passwordsMatch',
        'message' => '...',
    )
);

1

2

3

4

5

6

7

8

9

10

11

12

13

14

var $validate = array(

'current_password' => array(

'rule' => 'checkCurrentPassword',

'message' => '...'

),

'password1' => array(

'rule' => '',

'message' => '...',

),

'password2' => array(

'rule' => 'passwordsMatch',

'message' => '...',

)

);

So we need to create two functions one for checking current password and other to check if both passwords matches or not.

Magento : get product details

Aug 23,2012 No comments yet

I am working on my first Magento project . I’d say it is not a CMS you would master in a day or two , especially if you are used to work on very well documented CMSs that have good support like WordPress , Drupal. In my project I want to get product details on the product view page. I want to show product details like Product name, description , short description , Price , Special price etc. If you are working on Magento for the first time chances are that you tweak by copying base template files into your package rather than theming from scratch. I learnt it is indeed a good practice unless you need a whole different design or structure. Well if you are tweaking existing file and you are wondering how to get different product details , here are the ones you should look for.

echo $_product->getName();
//get product's short description
echo $_product->getShortDescription();
//get Product's Long Description
echo $_product->getDescription();
//get Product's Regular Price
echo $_product->getPrice();
//get Product's Special price
echo $_product->getSpecialPrice();
//get Product's Url
echo $_product->getProductUrl();
//get Product's image Url
echo $_product->getImageUrl();

1

2

3

4

5

6

7

8

9

10

11

12

13

echo $_product->getName();

//get product's short description

echo $_product->getShortDescription();

//get Product's Long Description

echo $_product->getDescription();

//get Product's Regular Price

echo $_product->getPrice();

//get Product's Special price

echo $_product->getSpecialPrice();

//get Product's Url

echo $_product->getProductUrl();

//get Product's image Url

echo $_product->getImageUrl();

Mysql database administration tutorial – User management

Aug 09,2012 No comments yet

Users in MySQL can be created in two ways. First way is by using CREATE or GRANT statements. Second one is to update or add to ‘user’, ‘grant’ tables of MySQL database (MySQL database is default databases that comes with installation and it holds important data realated to users, privilages and othe databases)

Using CREATE USER and GRANT statements:

 CREATE user1 IDENTIFIED BY password1,user2 IDENTIFIED BY password2,user3 IDENTIFIED BY password3
GRANT ALL PRIVILIGES on &lt;database.table&gt; to 'username'@'hostname' IDENTIFIED BY password

1 2	CREATE user1 IDENTIFIED BY password1,user2 IDENTIFIED BY password2,user3 IDENTIFIED BY password3 GRANT ALL PRIVILIGES on <database.table> to 'username'@'hostname' IDENTIFIED BY password

Priviliges can be limited by specifying in GRANT statement. for eg GRANT SELECT, INSERT, UPDATE ON database.* TO ‘username’@'hostname’ IDENTIFIED BY password;

Maximum number of queries , updates , connections can also be decided usind Grant statement. for eg:- if you feel that a user is abusing resources and you want to limit user connections and no of connections perhour, updates, queries, You can limit it by the following statement.

GRANT SELECT, INSERT, UPDATE ON database.* TO 'username'@'hostname' 
WITH
MAX QUERIES PER HOUR 200
MAX UPDATES PER HOUR 75
MAX CONNECTIONS PER HOUR 10
MAX USER CONNECTIONS 5;

1

2

3

4

5

6

GRANT SELECT, INSERT, UPDATE ON database.* TO 'username'@'hostname'

WITH

MAX QUERIES PER HOUR 200

MAX UPDATES PER HOUR 75

MAX CONNECTIONS PER HOUR 10

MAX USER CONNECTIONS 5;

Revoking privileges

User privileges can be revoked by REVOKE statement.
For eg:-

REVOKE ALL PRIVILEGES ON databse.table FROM useername;
//or
REVOKE UPDATE,DELETE ON databse.table FROM useername;

1

2

3

REVOKE ALL PRIVILEGES ON databse.table FROM useername;

//or

REVOKE UPDATE,DELETE ON databse.table FROM useername;

similarily there are Statements to rename user and set a password for user.
for eg: -

RENAME USER oldusername TO newusername;
SET PASSWORD FOR 'username'@'hostname' = PASSWORD('password');

1 2	RENAME USER oldusername TO newusername; SET PASSWORD FOR 'username'@'hostname' = PASSWORD('password');

Creating user by inserting values in mysql database

User can be created using simple insert commands

INSERT INTO user (host, user) VALUES ('hostname', 'username');

1	INSERT INTO user (host, user) VALUES ('hostname', 'username');

If you want limit or provide any privileges look for colomn in the user table and update ‘Y’ to provide and ‘N’ to revoke privileges. Also there are columns max_questions,max_updates,max_connections and max_user_connections to restrict resources to a user. We can use simple UPDATE , DELETE DML statements on user to modify user data in mysql.user table

Mysql database administration tutorial – Introduction

Aug 08,2012 No comments yet

MySQL, an open source software is a relational database management system.SQL is the most common standardized language used to access databases and is defined by the ANSI/ISO SQL Standard. Current version of the standard is “SQL:2003”

Main features of Mysql are

Written in C and C++.
Tested with a broad range of different compilers.
Works on many different platforms.
Designed to be fully multi-threaded using kernel threads, to easily use multiple CPUs if they are available
Provides transactional and nontransactional storage engines.
Implements in-memory hash tables, which are used as temporary tables.
Works in cross platform.
supports stored procedure, functions, triggers, views, subqueries and partitioning.
Supports replication and clustering for High Availability.

Architecture

Mysql architecture is divided into two levels

1. Application layer
2. Logical Layer

Application layer

This is the layer where users, clients connect to Mysql server to query data. there are three components in this layer

admin interface Like mysqladmin, isamchk etc
Client Interfaces . For eg various Mysql APIs like PHP API
Query interface for users to interact with server using ‘mysql’

Logical Layer

Logical layer is further divided into subsystems.

Query processor
All the D*L statements sent from application layer are parsed and optimized in this Query processing layer
Transaction management
This is where transaction management is sone in mysql .A transaction is a single unit of work that has one or more MySQL commands in it. The transaction manager is responsible for making sure that the transaction is logged and executed atomically.It also executes commit and rollback to revert the transactions

Ensures Atomicity
Avoids Deadlocks
Responsible for Commit & Rollback

Recovery management
Recovery management is to retrive and rollback database to its previous state if necessary. It is done by using logs of system. There are different variables one can tune in configuration settings to configure logs and use them to recover to old state
Storage management
Ultimately storage of data is physically done on some secondary storage like hard disks. But dynamic access of medium is done by mainating some buffers in the main memory. These buffers reciding in main and virtual memory is maintained by buffer manager , which is part of storage manager.

Mysql Database administration tutorial

Mar 05,2012 No comments yet

MySQL! A well known and most popular open source database system needs no introduction to any programmer. MySQL database administration skills are often required by number of programmers and web application developers. This tutorial is targeted for application developers who either wants to improve their administration skills in MySQL or to optimize MySQL database to improve performance of application. This is also targeted to novice MySQL database administrators who wants to improve their knowledge or fill knowledge gaps. It is not the purpose of this tutorial to serve as documentation or complete reference. Please refer MySQL website for complete reference and documentation.

Here is the complete list of topics that are covered in this tutorial.

Introduction,features
MySQL start up scripts
User management
Storage Engines
MySQL client and utility Programs
Server variables
Server Logs
Backup and recovery
Multiple MySQL installations on single computer
Optimization
Buffering and caching
MySQL default databases (information schema, MySQL)
Replication
Conclusion

Take MySql database Backups remotely from command line

WordPress: server error

Cakephp 2.x Auth Component : Change password

Magento : get product details

Mysql database administration tutorial – User management

Mysql database administration tutorial – Introduction

Mysql Database administration tutorial

Recent Posts

Archives

Categories

Find Me on

Friends

Resources