Thursday, November 18, 2010

Paul Buchheit, Creator of Gmail, Leaves Facebook for Y Combinator

Monday, November 8, 2010

RockMelt : Brand new browser.



RockMelt :
A brand new browser with brand new browsing experience. Check out the video.
Currently available through Facebook invitation only.

RockMelt is built first and foremost for sharing. You browse with your friends (the first thing you do is log into Facebook and your favorite friends and their latest status updates are always available along the left rail). And there is a big “Share” button up top, which lets you share any webpage, along with images and a preview, on Facebook. You could call it a Facebook browser, but it really is more than that.




The three biggest changes to the browser that you notice with RockMelt is that it is built around friends, feeds, and search results. The left rail is for friend. The right rail is for sites. And the top rail is for search.





http://www.rockmelt.com/

Via Tech crunch:
http://techcrunch.com/2010/11/07/rockmelt-browser-sharing-review/


Saturday, November 6, 2010

MySQL Performance Tuning - GNUnify 2010

Thursday, November 4, 2010

MySQL Query And Index Tuning

Check out this SlideShare Presentation:

Mysql Explain Explained

Check out this SlideShare Presentation:

Explain

Check out this SlideShare Presentation:

Real World Scalability MySQL

Check out this SlideShare Presentation:
Wednesday, November 3, 2010

What does "size" in int(size) of MySQL mean?

I was always wondering what the size of numeric columns in MySQL was. Forgive me if this is obvious to someone else. But for me the MySQL manual lacks a great deal in this field.
Usually you see something like int(11) in CREATE TABLE statements, but you can also change it to int(4).
So what does this size mean? Can you store higher values in a int(11) than in an int(4)?
Let's see what the MySQL manual says:
INT[(M)] [UNSIGNED] [ZEROFILL]
A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.
No word about the M. The entry about BOOL suggests that the size is not there for fun as it is a synonym for TINYINT(1) (with the specific size of 1).
TINYINT[(M)] [UNSIGNED] [ZEROFILL]
A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.
BOOL, BOOLEAN
These types are synonyms for TINYINT(1). A value of zero is considered false. Non-zero values are considered true: [...]
So TINYINT(1) must be different in some way from TINYINT(4) which is assumed by default when you leave the size out1. Still, you can store for example 100 into a TINYINT(1).
Finally, let's come to the place of the manual where there is the biggest hint to what the number means:
Several of the data type descriptions use these conventions:
M indicates the maximum display width for integer types. For floating-point and fixed-point types, M is the total number of digits that can be stored. For string types, M is the maximum length. The maximum allowable value of M depends on the data type.
It's about the display width. The weird thing is, though2, that, for example, if you have a value of 5 digits in a field with a display width of 4 digits, the display width will not cut a digits off.
If the value has less digits than the display width, nothing happens either. So it seems like the display doesn't have any effect in real life.
Now ZEROFILL comes into play. It is a neat feature that pads values that are (here it comes) less than the specified display width with zeros, so that you will always receive a value of the specified length. This is for example useful for invoice ids.
So, concluding: The size is neither bits nor bytes. It's just the display width, that is used when the field has ZEROFILL specified.
If you see any more uses in the size value, please tell me. I am curious to know.
1 See this example:
mysql> create table a ( a tinyint );
Query OK, 0 rows affected (0.29 sec)
mysql> show columns from a;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| a     | tinyint(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.26 sec)

mysql> alter table a change a a tinyint(1);
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into a values (100);
Query OK, 1 row affected (0.00 sec)

mysql> select * from a;
+-----+
| a   |
+-----+
| 100 |
+-----+
1 row in set (0.00 sec)

2 Some code to better explain what I described so clumsily.
mysql> create table b ( b int (4));
Query OK, 0 rows affected (0.25 sec)

mysql> insert into b values (10000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from b;
+-------+
| b     |
+-------+
| 10000 |
+-------+
1 row in set (0.00 sec)

mysql> alter table b change b b int(11);
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from b;
+-------+
| b     |
+-------+
| 10000 |
+-------+
1 row in set (0.00 sec)

mysql> alter table b change b b int(11) zerofill;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from b;
+-------------+
| b           |
+-------------+
| 00000010000 |
+-------------+
1 row in set (0.00 sec)

mysql> alter table b change b b int(4) zerofill;
Query OK, 1 row affected (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from b;
+-------+
| b     |
+-------+
| 10000 |
+-------+
1 row in set (0.00 sec)

mysql> alter table b change b b int(6) zerofill;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from b;
+--------+
| b      |
+--------+
| 010000 |
+--------+
Tuesday, November 2, 2010

Plugin Memcached 20 Study

Check out this SlideShare Presentation:

MySQL and memcached Guide

Check out this SlideShare Presentation:

Introduction to memcached

Check out this SlideShare Presentation:
Monday, November 1, 2010

SphinxSE with MySQL

Check out this SlideShare Presentation:

Sphinx new

Check out this SlideShare Presentation:

Mysql Fulltext Search 1

Check out this SlideShare Presentation:

Performance Tuning Best Practices

Check out this SlideShare Presentation:

Top 20 Design Tips for MySQL Data Architects

Check out this SlideShare Presentation:
Wednesday, October 27, 2010

SetUp and test MySQL Replication in 20 easy steps

We have 2 servers, one of which is a Master and the other which is a Slave. We tell the Master that it should keep a log of every action performed on it. We tell the slave server that it should look at this log on the Master and whenever something new happens, it should do the same thing.

You should follow the instructions below with two console windows open - one for the Master and one for the Slave.

Step 1:

First of all, we need to create a user on the Master server that the Slave will connect as. Say 'slave_user'. Log into mysql as root and create the user:
-- ================================================================================
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'your_password';
FLUSH PRIVILEGES;
-- ================================================================================

Step 2:

Now, we should edit the my.cnf file (usually in /etc/my.cnf), in the [mysqld] section and tell MySQL that it's going to be a Master:
-- ================================================================================
log-bin = /home/mysql/logs/mysql-bin.log
binlog-do-db=replication_test_master
server-id=1
-- ================================================================================

The first line tells MySQL to start writing a log, and tells it where to write the log. Make sure this directory is empty of all replication logs, especially if you're starting again after replication has already been used.

The second line chooses the database to write the log for. You should change this to your database. The third line gives the server an ID (to distinguish it from the Slave).

You should also make sure skip-networking has not been enabled.

Step 3:

You should now restart the Master:
-- ================================================================================
 /etc/init.d/mysql restart
-- ================================================================================

Step 4:

Again, we should change the /etc/my.cnf of the Slave server, in the [mysqld] section:
-- ================================================================================
server-id=2
master-host=10.90.50.62
master-connect-retry=60
master-user=slave_user
master-password=nn@123
replicate-do-db=replication_test_master

relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
-- ================================================================================

Line 1 gives the Slave its unique ID. Line 2, tells the Slave the I.P address of the Master server - so you need to change the I.P here.

The remaining lines set a retry limit, and tell the Slave the user, password and database it needs to replicate. We also tell the slave what to use as its relay log. It's best to set this directly, or MySQL will create the name from the hostname and should you change hostname, replication will fail.

You should also make sure skip-networking has not been enabled.


Step 5:

You should now restart the Slave:
-- ================================================================================
 /etc/init.d/mysql restart
-- ================================================================================

===============================
Getting the data onto the Slave
===============================

On the Master...

I'm assuming you have a live Master server, and an as yet empty Slave server. This stage depends on whether data is constantly being added to the Master. If so, we will have to prevent all database access on the Master so nothing can be added. This means your server will hang during the next step. If no data is being added to the server, you can skip this step. On the Master server, log into MySQL and do the following:

Step 6:

mysql -u root -p (log into MySQL)
-- ================================================================================
FLUSH TABLES WITH READ LOCK;
-- ================================================================================

Step 7:

Now we will use mysqldump to get the data out. So, still on the Master server:
-- ================================================================================
mysqldump replication_test_master -u root -p > /var/tmp/replication_test_master.sql;
gzip /var/tmp/replication_test_master.sql;
-- ================================================================================

Make sure you change my_database to your database name, and my_home_dir to the name of your home directory (or another directory of your choosing). You wll now have a file called database.sql.gz in your home directory. This is a gziped copy of your database.

On the Slave...

Step 8:

Now we need to copy over the gzipped file. On the Slave run the following:
-- ================================================================================
scp root@10.90.50.62:/var/tmp/replication_test_master.sql.gz /var/tmp/
-- ================================================================================

Make sure 128.0.0.1 is the I.P of the Master. This will copy the file from the Master and put it in your home directory on the Slave. Now we just need to import into MySQL:

Step 9:

mysql -u root -p (log into MySQL)
-- ================================================================================
CREATE DATABASE replication_test_master;
-- ================================================================================


Step 10:

-- ================================================================================
gunzip /var/tmp/replication_test_master.sql.gz
mysql -u root -p replication_test_master  </var/tmp/replication_test_master.sql
-- ================================================================================

Lets ready...

On the Master...

Now we're ready to kick things off. We need to find the position the Master is at in the logs. So, log into MySQL and run the following:


Step 11:

mysql -u root -p (log into MySQL)
-- ================================================================================
SHOW MASTER STATUS;
-- ================================================================================

+------------------+----------+-------------------------+------------------+
| File             | Position | Binlog_Do_DB            | Binlog_Ignore_DB |
+------------------+----------+-------------------------+------------------+
| mysql-bin.000002 |      106 | replication_test_master |                  |
+------------------+----------+-------------------------+------------------+


Keep that on-screen.
On the Slave...

Step 12:

Log into MySQL and do the following:

mysql -u root -p (log into MySQL)
-- ================================================================================
slave stop;
CHANGE MASTER TO MASTER_HOST='10.90.50.62', MASTER_USER='slave_user', MASTER_PASSWORD='nn@123', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=106;
slave start;
-- ================================================================================

First we stop the Slave. Then we tell it exactly where to look in the Master log file. We use the values for our previous SHOW MASTER STATUS; command on the Master. You should change 128.0.0.1 to the I.P of the Master, and change the user and password accordingly.

The Slave will now be waiting. So all that's left is to...

Back on the Master...

Step 13:

We should already be logged into MySQL, so all you have to do is:
-- ================================================================================
unlock tables;
-- ================================================================================

To release the tables from lock. Note you only have to do this if you previously ran FLUSH TABLES WITH READ LOCK;

=============================
Checking Replication is Working
=============================

Step 14:

On the master server, issue the following command to see the master status:


mysql -u root -p (log into MySQL)
-- ================================================================================
SHOW MASTER STATUS;
-- ================================================================================

+------------------+----------+-------------------------+------------------+
| File             | Position | Binlog_Do_DB            | Binlog_Ignore_DB |
+------------------+----------+-------------------------+------------------+
| mysql-bin.000002 |      106 | replication_test_master |                  |
+------------------+----------+-------------------------+------------------+


Step 15:

On the backup server, issue the following command to see the slave status:

-- ================================================================================
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.90.50.62
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 823
               Relay_Log_File: slave-relay.000002
                Relay_Log_Pos: 968
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: replication_test_master
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 823
              Relay_Log_Space: 1119
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

-- ================================================================================

==================
Test replication:
==================

Make changes on master tables and see that changes should reflect in slave as well.

lets test it on table "category_master_list_new"

Step 16:

Getting count of records in table category_master_list_new on master

(root@localhost) Wed Oct 27 13:00:42 2010 [replication_test_master]>select count(1) from category_master_list_new;
+----------+
| count(1) |
+----------+
|     8969 |
+----------+
1 row in set (0.00 sec)

Step 17:

Getting count of records in table category_master_list_new on slave

mysql> select count(1) from category_master_list_new;
+----------+
| count(1) |
+----------+
|     8969 |
+----------+
1 row in set (0.00 sec)


Step 18:

Now i am inserting two records in category_master_list_new on master

(root@localhost) Wed Oct 27 13:24:36 2010 [replication_test_master]>insert into category_master_list_new(tagid,tagname,parent_id,is_primary,is_secondary) select tagid,tagname,parent_id,is_primary,is_secondary from category_master_list_new limit 2;
Query OK, 2 rows affected, 0 warning (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0


Step 19:

Lets check the count of table category_master_list_new on master and slave

On Master


(root@localhost) Wed Oct 27 13:28:04 2010 [replication_test_master]>select count(1) from category_master_list_new;                                                                                      

+----------+
| count(1) |
+----------+
|     8971 |
+----------+
1 row in set (0.00 sec)

Step 20:

On Slave

mysql> select count(1) from category_master_list_new;
+----------+
| count(1) |
+----------+
|     8971 |
+----------+
1 row in set (0.00 sec)

See the two record we have inserted in master is been reflected on slave also.

Yay!!!! its working fine.
Friday, October 22, 2010

Show databases

Here we will discuss how to show databases from command prompt.

Here is the syntax.

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
| wordpress          |
+--------------------+
4 rows in set (0.37 sec)

mysql>

Login to MySQL from Command prompt

Here is the steps to start MySQL from command prompt in windows.
1. Click on start and go to programme.
2. Select MySQL and go for "MySQL command line client" under mysql <your mysql version>.
3. MySQL command prompt will be open asking you for password.















4. Enter the password.
5. Now you should see something like..


Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 5.0.24-community-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>


6. Here you go. You are in MySQL command line console :)

Lets start with MySQL

Here we will discuss about MySQL from beginning till the very complex scenarios.
We are going to cover each and every aspects of MySQL in depth.

1. Data types
2. MySQL Engines
3. Stored procedure
4. Functions
5. Transactions
6. Writing complex queries.
7. Query optimization.
8. Use of third party open source pluggin.
9. Use of Popular full text search engine "Sphinx".
10. Dealing with millions of data.
11. Case studies.


I am looking forward for your support.'
Thanks.
 
© Copyright 2010-2012 Learn MySQL All Rights Reserved.
Template powered by Blogger.com.