Mysql How to disable dirty reads

Mysql How to enable transaction-isolation READ-COMMITTED

1)
Goto mysql prompt and check transaction-isolation setting
mysql> SELECT @@global.tx_isolation, @@session.tx_isolation, @@tx_isolation;
+-----------------------+------------------------+-----------------+
| @@global.tx_isolation | @@session.tx_isolation | @@tx_isolation  |
+-----------------------+------------------------+-----------------+
| REPEATABLE-READ       | REPEATABLE-READ        | REPEATABLE-READ |
+-----------------------+------------------------+-----------------+
1 row in set (0.00 sec)

2)
Goto 

#vim /etc/mysql/my.cnf

3)
Add following line under [mysqld] section
transaction-isolation = READ-COMMITTED

Example
=====

[mysqld]

transaction-isolation = READ-COMMITTED

4)
Restart mysql
#sudo /etc/init.d/mysql restart

5)
Goto mysql prompt and check transaction-isolation setting
mysql> SELECT @@global.tx_isolation, @@session.tx_isolation, @@tx_isolation;
+-----------------------+------------------------+----------------+
| @@global.tx_isolation | @@session.tx_isolation | @@tx_isolation |
+-----------------------+------------------------+----------------+
| READ-COMMITTED        | READ-COMMITTED         | READ-COMMITTED |
+-----------------------+------------------------+----------------+
1 row in set (0.00 sec)

How to reproduce MySQL ERROR 1205 Lock wait timeout exceeded

How to reproduce MySQL ERROR 1205 Lock wait timeout exceeded

1)
Goto mysql Prompt and check default value of innodb_lock_wait_timeout

mysql> show variables like 'innodb_lock_wait_timeout';

2)
Open /etc/mysql/my.cnf and add

[mysqld]

innodb_lock_wait_timeout=3

3)
Restart mysql

#/etc/init.d/mysql restart

 
4)
Goto mysql Prompt and check the new value of innodb_lock_wait_timeout

mysql> show variables like 'innodb_lock_wait_timeout';

5)
Open a mysql prompt and select a database, begin a transaction, run a update query (don't commit the transacion).

mysql> use mydb1

mysql> begin;

mysql> update mytable set name="blabla" where id=2;

6)
Open another mysql prompt and select same database, Try to delete the rows which were updated in first mysql prompt.

mysql> use mydb1

mysql> begin;

mysql> delete from mytable where id=2;

How To Backup and restore MySQL databases using MySQL Workbench

How To Backup and restore MySQL databases using MySQL Workbench

A)
How To Backup MySQL databases using MySQL Workbench
----------------------------------------------------

1) Open MySQL Workbench

2) Create "New Server Instance"
* Click on "New Server Instance"

3) Open Backup/Restore window
* Click on "Manage Import/Export"

4) Click on "Data Export"
* Select Database or tables.
* Select one export option.
* Click on "Start Export" Button.

B)
How To Restore MySQL databases using MySQL Workbench
----------------------------------------------------

1) Open MySQL Workbench

2) Open Backup/Restore window
* Click on "Manage Import/Export"

3) Click on "Data Import/Restore"
* Select one import option.
* Click on "Start Import" Button.

How to create ER Diagram from existing Database using MySQL WorkBench

How to create ER Diagram from existing Database using MySQL WorkBench

How To use mysql workbench to browse database

How To use mysql workbench to browse database table data

How To Install mysql query browser in ubuntu

How To Install mysql query browser in ubuntu 12.10

Download
http://mysql.ntu.edu.tw/Downloads/MySQLGUITools/mysql-gui-tools-5.0r12-linux-x86_64.tar.gz

#tar -xzf mysql-gui-tools-5.0r12-linux-x86_64.tar.gz

#cd mysql-gui-tools-5.0






#./mysql-query-browser --update-paths .

#./mysql-query-browser

How To Install mysql workbench on ubuntu

How To Install mysql workbench on ubuntu 12.10

#sudo add-apt-repository ppa:olivier-berten/misc

#sudo apt-get update

sudo apt-get update

sudo apt-get update

sudo apt-get update

#sudo apt-get install mysql-workbench

#mysql-workbench &

mysql-workbench &

Can not connect to remote MySQL server Solved

Can not connect to remote MySQL server Solved

------------------------- Error-1
$ mysql -uroot -ppassword --host=192.168.1.108
_mysql_exceptions.OperationalError: (2003, "Can't connect to MySQL server on '192.168.1.108' (111)")

Solution
===========
In /etc/mysql/my.cnf comment fllowing line.
#bind-address = 127.0.0.1


------------------------- Error-2
$ mysql -uroot -ppassword --host=192.168.1.108
_mysql_exceptions.OperationalError: (1130, "Host 'localhost.local' is not allowed to connect to this MySQL server")

Solution
===========
Run following commands in Mysqlserver.

$ mysql -uroot -ppassword


mysql> CREATE USER 'root'@'localhost.local' IDENTIFIED BY "password";
Query OK, 0 rows affected (0.00 sec)


mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost.local';
Query OK, 0 rows affected (0.00 sec)

How to mysqldump without CREATE TABLE statements

MySql Dump

# mysqldump -u dbuser -p[pwd] --opt databasename > database_backup.sql

MySql Dump without data (only structure)

# mysqldump -u dbuser -p[pwd] --no-data databasename > database_backup.sql
or
# mysqldump -u dbuser -p[pwd] -d databasename --no-data > database_backup.sql

MySql Dump without structure (no drop/create table)

# mysqldump -u dbuser -p[pwd] --no-create-db --no-create-info databasename >
database_backup.sql

Import SQL file

mysql -u user_name -p  database_name < /path/to/file.sql

how directly login to mysql database without password prompt

how directly login to mysql database without password prompt

#mysql -Dmy_database -uroot -p123456 <-------

we can use this command to login to a mysql database and do some operations, using shell script.

------------------ Shell Script

1)create a file mysql_login.sh

2)Then type following line in it .
mysql -Dmy_database -uroot -p123456

3)save file

4)#chmod 777 mysql_login.sh

5)./mysql_login.sh <------ To run script

---------------------

how restore mysql database from dump file

how restore mysql database from dump file

#mysql -uroot -p123456 <----- Entering into Mysql prompt

mysql>

mysql> create database my_new_database; <----- Creating database 'my_new_database'

#mysql -uroot -p123456 my_new_database < /root/batabase_dump.sql <------ Restoring database from dump file

Initializing MySQL database

[root@localhost ~]# service mysqld status

mysqld is stopped

----------------------

[root@localhost ~]# service mysqld start

Initializing MySQL database:  Installing MySQL system tables...

090914 16:42:11 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295

090914 16:42:11 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295

OK

Filling help tables...

090914 16:42:11 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295

090914 16:42:11 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295

OK

To start mysqld at boot time you have to copy

support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'

/usr/bin/mysqladmin -u root -h localhost.localdomain password 'new-password'

Alternatively you can run:

/usr/bin/mysql_secure_installation

which will also give you the option of removing the test

databases and anonymous user created by default.  This is

strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:

cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl

cd mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available on the web at

http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com

                                                           [  OK  ]

Starting MySQL:                                            [  OK  ]

----------------------

[root@localhost ~]# service mysqld status

mysqld (pid 4296) is running...

----------------------

[root@localhost ~]# mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2

Server version: 5.0.67 Source distribution

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

mysql> 

-----------------------

How create Mysql User, Database and set privileges to user

How create Mysql User, Database and set privileges to user

[saju@localhost ~]$ mysql or mysql -u root  or mysql -u root -p

mysql> create user 'saju'@'localhost' identified by '123456';

mysql> create database if not exists `sajudb`;

mysql> grant all on sajudb.* to saju@localhost identified by "123456" with grant option;

[saju@localhost ~]$ mysql sajudb -u saju -p

mysql> show databases

mysql> use sajudb