Create user in mysql

Create an admin user who can access anything from anywhere

mysql> grant all privileges on *.* to 'admin'@'%' identified by 'password';

Create an user who can access any database from a network colcation

mysql> grant all privileges on *.* to 'admin'@'192.168.%' identified by 'password';

*If somehow this doesn't work, execute this
update mysql.user set Host='192.168.%' where User='admin';

Create an user who can only access from localhost
mysql> grant all privileges on *.* to 'admin'@'localhost' identified by 'password';

Create an user who can access only one fix database
mysql> grant all privileges on dbname.* to 'admin'@'localhost' identified by 'password';

Here is the description of every word in the above command

grant all privileges - its granting permission(so it creates user also)
on dbname.* - its dbname and table name access restriction (*.* means all db, dbname.* means only one datase)
to 'admin'@'localhost' - its first quoted string is username, and 2nd quoted string is host access, who can connect to the mysql db, in the current only, only localhost host users would be allowed to connect
identified by 'password'; - its the password which is required to connect the mysql db server

Note : 
Here do not get confused with "bind-address" configuration in mysql configuraiton file, which actually provides binding access, click here to read more about "bind-address" and access point

How to setup replication (Master Slave) in MySQL

I'll start the article by assuming that there are two MySQL server ready and we just need to do the configuration setup to start the replication.

Go to Master Server

1. Make all the tables engine = innodb 

As only innodb engines have binary logging feature which is essentially used for replication. Binary logging must be enabled on the master because the binary log is the basis for sending data changes from the master to its slaves. If binary logging is not enabled, replication will not be possible. MyIsam does not support binary logging.

Use following command to convert all the tables to InnoDB

mysql > SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') as ExecuteTheseSQLCommands

FROM information_schema.tables WHERE table_schema = 'db_name' 

ORDER BY table_name DESC;

2. Start binary logging on Master and Assign server Id (Server ID assigning is necessary, If you omit server-id (or set it explicitly to its default value of 0), a master refuses connections from all slaves).

edit the file /etc/mysql/my.cnf

[mysqld]

log-bin=mysql-bin

server-id=101

*For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should use innodb_flush_log_at_trx_commit=1 and sync_binlog=1 in the master my.cnf file.

3. Create a slave user on Master DB

mysql> grant REPLICATION SLAVE on *.* to 'slave'@'IP_ADDRSS_OF_SLAVE' identified by 'slavePassword';

mysql> flush privileges;

4. Restart Master DB

and check 

mysql> show master status;

You can also check if mysql-bin log files are getting created on not, where you have given path of data to be stored, may be at /var/lib/mysql

5. Take a dump of database

 mysqldump -uroot -proot --single-transaction --master-data --databases db1,db2 > all_db.sql

And transfer the file on slave Machine

Go to slave Machine

6. Assign Server Id on slave DB

[mysqld]

server-id=102

7. Restart Slave DB

8. Import the dump file in database

mysql -uroot -proot < all_db.sql

9. Make this slave listen to Master

mysql> CHANGE MASTER TO MASTER_HOST='MASTE_HOST_IP_ADDRESS',MASTER_USER='slave',MASTER_PASSWORD='slavePassword';

mysql> flush privileges;

10. slave start

mysql > slave start;

mysql > show slave status;

DONE :)

Some troubleshoots and points:

1. You can configure on slave mysql configuration that which all database or even which all tables you want to replicate or do not want to replicate
   http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html
2. If replication fails due to data consistency issue means, data already exist in slave and master is still trying to push (may be due to several kind of issue), you can change the slave configuration to move ahead

mysql > change MASTER TO MASTER_LOG_POS=desired_position;
mysql > change MASTER TO Master_Log_File='mysql-bin._desired_bin_log_file'

Reference : http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html

Mysql Database Configuration, Access settings, Innodb configuration, Log slow query

To bind the server Access point
-----------------------------------
By default it is binded to localhost or 127.0.0.1
Open /etc/mysql/my.cnf fine

So lets say you have 4-5 machines from which you want to access mysql DB from any of the machine, but you do not want anyone from outside to access this,
bind-address will be Local LAN Ip Address.
bind-address = Local LAN IP Address

If you want only local machine to access mysql DB
bind-address            = 127.0.0.1

If you want to make it public, remove the bind-address line.

Logging the slow queries
-----------------------------
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 1


Logging the queries which are not using index
---------------------------------------------------
log-queries-not-using-indexes


Changing the InnoDB configuration
----------------------------------------
Buffer Pool Size is the memory which you provide to mysql server program.

innodb_buffer_pool_size=5120M
innodb_lock_wait_timeout=20
innodb_rollback_on_timeout

max_allowed_packet : 
The max_allowed_packet variable limits the size of a single result set. In the [mysqld] section, any normal connection can only get that much worth of data in a single query. In mysqldump you typically produce "extended INSERT" queries, where you list multiple rows within the same INSERT command. It's better, then, to have this variable set high. In mysqld max_allowed_packet could be 16M (to be safe, because it doesn't uses memory until required), in mysqldump, max_allowed_packet  could be 128M or may be 512M, depends on your machine and requirement.

If you want mysqldump to work fast
---------------------------------------

[mysqldump]
quick
quote-names
max_allowed_packet  =  64M (Increase this value, default is 16M)

* You can also take take dump faster by passing as a command argument
$ mysqldump -u root -p --max_allowed_packet=512M dbname > dbname.sql

More Ideas on MySQL performance tuning
1. https://blogs.oracle.com/luojiach/entry/mysql_innodb_performance_tuning_for
2. http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/

Install MySql on Mac 10.6 Snow Leopard

Download:
http://www.simonwhatley.co.uk/installing-mysql-on-mac-osx-10-6-snow-leopard
http://dev.mysql.com/downloads/mysql/5.1.html#macosx-dmg

And follow the instructions, it will install the mysql at following location /usr/local/mysql

$ cd /usr/local/mysql
$ sudo scripts/mysql_install_db --user=mysql
$ cd bin
$ ./mysqladmin -u root password root
$ ./mysql -u root -proot
$ PATH=$PATH:/usr/local/mysql/bin
$ export PATH
$ mysql -u root -proot

Done :)

Make your development system up on Mac

This post is about persisting the experience while installing Mac 10.6.3 (SnowLeopard) on my personal computer.

First of all you should decide if you want to install by using binary then I have no idea. I can tell you to install so many things easily using MacPort software manager.

So first of all follow this link to download and install MacRport http://www.macports.org/install.php

Basic command usages for MacPort
---------------------------
port list variant:no_ssl
port uninstall name:sql
port echo depof:mysql5
port echo apache*
port install mysql5
--------------------------

Now you need Apache + PHP + Mysql
Step 1: sudo port selfupdate
Step 2: sudo port install gawk
Step 3: sudo port install nawk (For me this step was failed, but I gave a damn !!!)
Step 4: sudo port install php5 +apache2 +mysql5-server

Now use the command below to setup the mysql db.
$ sudo /opt/local/lib/mysql5/bin/mysql_install_db --user=mysql

You can change mysql root password using this command
$ /opt/local/lib/mysql5/bin/mysqladmin -u root password 'new-password'

Next, you can configure apache and mysql to start automatically by enter the command below:-
$ sudo launchctl load -w /Library/LaunchDaemons/org.macports.apache2.plist
$ sudo launchctl load -w /Library/LaunchDaemons/org.macports.mysql5.plist

Now you need to configure apache to load php file, open /opt/local/apache2/conf/httpd.conf and add this 2 line:-
LoadModule php5_module modules/libphp5.so
AddType application/x-httpd-php .php

Once saved, you may restart your apache

To start your apache2 and mysql5 manually, type the command below:-
$ sudo /opt/local/etc/LaunchDaemons/org.macports.apache2/apache2.wrapper start
$ sudo /opt/local/etc/LaunchDaemons/org.macports.mysql5/mysql5.wrapper start


Now you'll want Java
Java is usually installed with Mac OS and XCode (and yes you need to install XCode at the very very first thing of above all, you can locate XCode in any of your given DVD with Mac, or you can download also). You can usually find java and javac commands are usually working. The working java home would be usually at /Library/Java/Home

Eclipse : Download it from here http://eclipse.org/downloads/

Tomcat :
Step 1: Download it from here http://tomcat.apache.org/download-60.cgi
Step 2: After downloading tomcat, extract the file and go to bin
Step 3: Create a file setenv.sh and write this line JAVA_HOME=/Lbrary/Java/Home (or whatever java home you have)
Step 4: Save the file, and run "chmod 777 setenv.sh" and you are ready with tomcat
Step 5: ./startup.sh will run the tomcat, you can see the log at ~tomcat_home/logs/catalina.out

JDBC

Four JDBC driver types.

Type 1: JDBC-ODBC Bridge plus ODBC Driver:

The first type of JDBC driver is the JDBC-ODBC Bridge. It is a driver that provides JDBC access to databases through ODBC drivers. The ODBC driver must be configured on the client for the bridge to work. This driver type is commonly used for prototyping or when there is no JDBC driver available for a particular DBMS.

Type 2: Native-API partly-Java Driver:

The Native to API driver converts JDBC commands to DBMS-specific native calls. This is much like the restriction of Type 1 drivers. The client must have some binary code loaded on its machine. These drivers do have an advantage over Type 1 drivers because they interface directly with the database.

Type 3: JDBC-Net Pure Java Driver:

The JDBC-Net drivers are a three-tier solution. This type of driver translates JDBC calls into a database-independent network protocol that is sent to a middleware server. This server then translates this DBMS-independent protocol into a DBMS-specific protocol, which is sent

to a particular database. The results are then routed back through the middleware server and sent back to the client. This type of solution makes it possible to implement a pure Java client. It also makes it possible to swap databases without affecting the client.

Type 4: Native-Protocol Pur Java Driver

These are pure Java drivers that communicate directly with the vendor’s database. They do this by converting JDBC commands directly into the database engine’s native protocol. This driver has no additional translation or middleware layer, which improves performance tremendously.

1. Is there any limitation for no of statments executed with in batchupdate?

No, any number of statements we can write in the batch update but all the updates only means (insert,delete,update) not select

2. What happens when we execute "Class.forname("Driver class name");"

By the end of the execution of Class.forName("Driver
class"); the driver class should be loaded into the memory
but also
1. The driver class should be initialized
2. Should be registered with the driver manager class

The above two operations are not done by forName() . So a pure Static() block is defined in which the above two tasks are manipulated and by which we are able to get connection, immediately after loading the driver class without writing any code to initialize the driver class.

3. What are the Statements in JDBC?

1. Statement: Each time you fire a query it compile and executes and returns the result set
2. PreparedStatement: It parses, compile and optimize the query and keep it, next time when query fired, it picks the compiled query and excute. Also you send dynamic sql statements.
3. Callable Statement: To execute stored procedure like pl/sql