MySQL

How to change the number of concurrency threads in MySQL

To change the number of concurrency threads in MySQL

Open my.cnf and set the innodb_thread_concurrency.

innodb_thread_concurrency = 20

it allow 20 concurrent threads.

To set this to unlimited
innodb_thread_concurrency = 0

How to change the Max connection in MySQL

To change the mysql max connections

open my.cnf and change max_connections to the required number of connections.

For example: To set maximum 300 connections

max_connections = 300

If you don't know where the my.cnf exists in Linux/Unix box, use find command.

find / -name my.cnf

MySQL Database - Truncate , Drop, Delete difference

There is database statements truncate, delete and drop. These used to delete record/records and table. We have to use these based on the need.

Truncate: To delete the records in the table. It resets the auto increment field - it delete the entry in auto increment sequence table. It gives you brand new table structure without records

Delete: Delete the records based on condition. If you want to delete specific records based on condition then choose this.

Drop : It removes table structure and records.

MySQL - How to set password for the root

Go to MySQL bin directory and use mysqladmin command to set password

mysqladmin -u root password sasikala

then login as

mysql -u root -psasikala

To change the root password

mysqladmin -u root -pold_password password new_password

The new password will be set to the root user.

MySQL Limit equivalent in oracle

In MySQL , we use limit clause to get the range of records from query. For example to get 11 th to 20th records.

select * from projects limit 11,20;

The rownum is the oracle equivalent of limit in the MySQL.

select * from projects where rownum>10 and rownum<=20;

MySQl : Flush Privileges statement

When mysqld starts the privileges like database, table, column and user privileges loaded into memory. Database, table and column privileges take into effect once. But the user privileges get effect when the user connects next time.

Use of Flush Privileges

It informs the server to re-read the grant tables. Normally we use this statement

Syndicate content