Pages

Tuesday, July 5, 2011

MYSQL : Loading data into MySQL in quicker way


MYSQL : Loading data into MySQL in quicker way
Disabling key checks can help speed up the import.
SET UNIQUE_CHECKS=0;
SET FOREIGN_KEY_CHECKS=0;
Whenever we load data into a MySQL database the following settings can help speed up the import.
innodb_flush_log_at_trx_commit = 0
innodb_support_xa = 0
skip-innodb_doublewrite
If the server doesn’t have replication configured and if the log-bin is enabled,  disable it using SET SQL_LOG_BIN=0 or manually comment log-bin parameter in my.cnf. Similiarly we can disable other logs will also help. These parameters help in reducing frequent syncs to disk…
Cheers!!!!

MYSQL : mysqld server - SOCKET ERROR


MYSQL : mysqld server - SOCKET ERROR


ERROR 2002 (HY000): Can’t connect to local MySQL server through socket '/usr/local/mysql/mysql.sock'


Following are the scenarios for getting socket error - 


1. Check whether mysqld server is running or not, using the below command
ps -ef | grep mysql


If mysqld is running, then check the socket path in the configuration file ( ie.. my.cnf )and give the correct path when connecting.


2. Check the file permission of that socket file. If the file doesn’t have mysql permission, just change permission to mysql using the below command.


chown mysql:mysql /usr/local/mysql/mysql.sock


3. If the socket file is missing then you have to restart your mysqld server. Its automatically create new socket...


Cheers!!!

Monday, June 27, 2011

MYSQL : Listing summary of all Instance Schemas including disk size

MYSQL : Listing summary of all Instance Schemas including disk size


#setting session variable for storing schemaname


SET @schemaname = IFNULL(@schemaname,DATABASE());


#one row schema usage 


SELECT   table_schema,
         SUM(data_length+index_length)/1024/1024 AS total_mb,
         SUM(data_length)/1024/1024 AS data_mb,
         SUM(index_length)/1024/1024 AS index_mb,
         COUNT(*) AS tables,
         CURDATE() AS today
FROM     information_schema.tables
WHERE    table_schema=@schemaname
GROUP BY table_schema;


#Display Engine/Collation Summary


SELECT   table_schema,engine,table_collation,
         COUNT(*) AS tables
FROM     information_schema.tables
WHERE    table_schema=@schemaname
GROUP BY table_schema,engine,table_collation;




#Display Schema Table Usage for each schema


SELECT @schemaname as table_schema, CURDATE() AS today;
SELECT   if(length(table_name)>20,concat(left(table_name,18),'..'),table_name) AS table_name,
         engine,row_format as format, table_rows, avg_row_length as avg_row,
         round((data_length+index_length)/1024/1024,2) as total_mb, 
         round((data_length)/1024/1024,2) as data_mb, 
         round((index_length)/1024/1024,2) as index_mb
FROM     information_schema.tables 
WHERE    table_schema=@schemaname
ORDER BY 6 DESC;

Friday, June 10, 2011

MYSQL :HOW YOU CAN USE INFORMATION_SCHEMA TO BUILD COMMANDS TO SEND BACK TO MYSQL;


HOW YOU CAN USE INFORMATION_SCHEMA TO BUILD COMMANDS TO SEND BACK TO MYSQL;


shell> mysql -N -e "select CONCAT('REPAIR TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM information_schema.tables WHERE ENGINE='MyISAM' AND TABLE_SCHEMA!='mysql' AND TABLE_SCHEMA!='information_schema';" | mysql -uuser -ppassword

Monday, May 16, 2011

MySQL: Correct problems in corrupted MyISAM, ARCHIVE and CSV tables


Correct problems in corrupted MyISAM, ARCHIVE and CSV tables


Following are the steps to recover corrupted tables - for above mentioned engine -


CHECK TABLES table1 ,table2;


REPAIR TABLE table1;


REPAIR TABLE table1 EXTENDED;


REPAIR TABLE table1 USE_FRM;


CHECK TABLE table1;

MySQL: USER CREATION & Setting UP Privileges


MySQL USER CREATION & Setting UP Privileges

To Check Current Privileges Of Any User

SHOW GRANTS;

To Create User

CREATE USER 'mifos_user'@'localhost';

SELECT user, host, password FROM mysql.user WHERE user='mifos_user';

To Grant SELECT , INSERT ,DELETE , UPDATE privileges

GRANT SELECT, INSERT, DELETE, UPDATE ON mifosdb.*
 TO 'mifos_user'@'localhost' IDENTIFIED BY 'mifos_user_pass';

Flush Privileges;

To show grants for particular user

SHOW GRANTS for 'mifos_user'@'localhost';


To Revote grant for particular

REVOKE DELETE, UPDATE ON mifosdb.* FROM 'mifos_user'@'localhost';

To Just Create User & Password Credentials

GRANT USAGE ON *.* TO 'mifos_user'@'localhost'
 IDENTIFIED BY 'NewPass';

To Grant ALL privileges

GRANT ALL ON mifosdb.* TO 'mifos_user'@'localhost'
 IDENTIFIED BY 'NewPass' WITH MAX_CONNECTIONS_PER_HOUR 10;

SHOW GRANTS for 'mifos_user'@'localhost';

To Grant ALL privileges
DROP USER 'mifos_user'@'localhost';

Wednesday, April 13, 2011

MySQL: Profiling

MySQL: Profiling




Hope this you in SQL query tuning using MySQL Profiler
The SQL Profiler is built into the database server and can be dynamically enabled/disabled via the MySQL client utility. To begin profiling one or more SQL queries, simply issue the following command:
mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)
Two things happen once you issue this command. First, any query you issue from this point on will be traced by the server with various performance diagnostics being created and attached to each distinct query. Second, a memory table named profiling is created in the INFORMATION_SCHEMA database for your particular session (not viewable by any other MySQL session) that stores all the SQL diagnostic results. This table remains persistent until you disconnect from MySQL at which point it is destroyed.
Now, simply execute a SQL query:
mysql> select count(*) from client where broker_id=2;
+----------+
| count(*) |
+----------+
|      200 |
+----------+
1 row in set (0.00 sec)
Once the query completes, you can issue the following command to view the SQL profiles that have currently been stored for you:
mysql> show profiles;
+----------+------------+-----------------------------------------------+
| Query_ID | Duration   | Query                                         |
+----------+------------+-----------------------------------------------+
|        0 | 0.00007300 | set profiling=1                               |
|        1 | 0.00044700 | select count(*) from client where broker_id=2 |
+----------+------------+-----------------------------------------------+
2 rows in set (0.00 sec)
You get a quick summary of all your captured SQL plus the total duration that the query took to complete. To get the same diagnostic info, you can also query the memory table that holds your statistical information:
mysql> select sum(duration) from information_schema.profiling where query_id=1;
+---------------+
| sum(duration) |
+---------------+
|      0.000447 |
+---------------+
1 row in set (0.00 sec)
I'll show you why it's good to be able to query the memory-based profiling table later in this article, but for now, let's concentrate on other SHOW commands that you can use to get more detailed diagnostic info about one or more queries that you've profiled. The most basic command is one that lists the steps a profiled query went through to satisfy your SQL request, along with each step's time:
mysql> show profile for query 1;
+--------------------+------------+
| Status             | Duration   |
+--------------------+------------+
| (initialization)   | 0.00006300 |
| Opening tables     | 0.00001400 |
| System lock        | 0.00000600 |
| Table lock         | 0.00001000 |
| init               | 0.00002200 |
| optimizing         | 0.00001100 |
| statistics         | 0.00009300 |
| preparing          | 0.00001700 |
| executing          | 0.00000700 |
| Sending data       | 0.00016800 |
| end                | 0.00000700 |
| query end          | 0.00000500 |
| freeing items      | 0.00001200 |
| closing tables     | 0.00000800 |
| logging slow query | 0.00000400 |
+--------------------+------------+
15 rows in set (0.00 sec)
You can also just issue SHOW PROFILE and exclude the identification of a specific profile number to see the very last profile you created.
Regardless of how you obtain the output, you can see this data is extremely valuable in that it allows you to see where your query spends its time during execution. This is done for all types of queries, and not just SELECT statements as this example shows:
mysql> alter table t engine=myisam;
Query OK, 112050 rows affected (0.64 sec)
Records: 112050  Duplicates: 0  Warnings: 0


mysql> show profiles;
+----------+------------+-----------------------------------------------+
| Query_ID | Duration   | Query                                         |
+----------+------------+-----------------------------------------------+
|        0 | 0.00007300 | set profiling=1                               |
|        1 | 0.00044700 | select count(*) from client where broker_id=2 |
|        2 | 0.00003400 | set profiling=0                               |
|        3 | 0.00007400 | set profiling=1                               |
|        4 | 0.63789700 | alter table t engine=myisam                   |
|        5 | 0.00004000 | set profiling=0                               |
+----------+------------+-----------------------------------------------+
6 rows in set (0.00 sec)


mysql> show profile for query 4;
+----------------------+------------+
| Status               | Duration   |
+----------------------+------------+
| (initialization)     | 0.00002900 |
| checking permissions | 0.00000800 |
| init                 | 0.00004000 |
| Opening table        | 0.00009400 |
| System lock          | 0.00000500 |
| Table lock           | 0.00000700 |
| setup                | 0.00004200 |
| creating table       | 0.00195800 |
| After create         | 0.00010900 |
| copy to tmp table    | 0.52264500 |
| rename result table  | 0.11289400 |
| end                  | 0.00004600 |
| query end            | 0.00000700 |
| freeing items        | 0.00001300 |
+----------------------+------------+
14 rows in set (0.00 sec)
So as you can see in the above profile, the ALTER TABLE statement spends the bulk of its time in the temporary table copy step. Armed with this type of information, you have more insight into the hoops your query is jumping through from start to finish, and therefore, you can then work to tune your queries to help eliminate any identified bottlenecks.
There's more information than just duration that you can get from your profiles - for example, CPU usage (which, unfortunately, is not available on all platforms; the below comes from Linux):
mysql> show profile cpu for query 4;
+----------------------+------------+------------+------------+
| Status               | Duration   | CPU_user   | CPU_system |
+----------------------+------------+------------+------------+
| (initialization)     | 0.00002900 | 0.00000000 | 0.00000000 |
| checking permissions | 0.00000800 | 0.00000000 | 0.00000000 |
| init                 | 0.00004000 | 0.00000000 | 0.00000000 |
| Opening table        | 0.00009400 | 0.00100000 | 0.00000000 |
| System lock          | 0.00000500 | 0.00000000 | 0.00000000 |
| Table lock           | 0.00000700 | 0.00000000 | 0.00000000 |
| setup                | 0.00004200 | 0.00000000 | 0.00000000 |
| creating table       | 0.00195800 | 0.00000000 | 0.00100000 |
| After create         | 0.00010900 | 0.00000000 | 0.00000000 |
| copy to tmp table    | 0.52264500 | 0.55591600 | 0.04199300 |
| rename result table  | 0.11289400 | 0.00199900 | 0.00000000 |
| end                  | 0.00004600 | 0.00000000 | 0.00000000 |
| query end            | 0.00000700 | 0.00000000 | 0.00000000 |
| freeing items        | 0.00001300 | 0.00000000 | 0.00000000 |
+----------------------+------------+------------+------------+





Tuesday, April 12, 2011

MySQL : Recovering CORRUPT TABLES



How to fix corrupt MySQL Tables 
Before you start the process  take MySQL Server is Safe Start - ./mysqld_safe & 
i.e..
mysqladmin -uxxx -pxxx shutdown
mysqld_safe --user=mysql &


1 . For MyISAM tables


To fix a single table, connect to your MySQL database and issue a:
REPAIR TABLE TABLENAME;


To fix everything, go with all databases :
../mysqlcheck --all-databases -uUSERNAME -pPASSWORD -r 
-r indicates ( repair )
I suggest you add this line to your my.cnf config file. It will automatically fix MyISAM tables as soon as they become corrupt  under  : 
[mysqld] 
myisam-recover=backup,force 


2. For InnoDB Tables
InnoDB does not support  repair -
Once you have corrupt InnoDB tables that are preventing your database from starting, you should follow this five step process:


Step 1: Add this line to your /etc/my.cnf configuration file:


[mysqld] 
innodb_force_recovery = 4 


Step 2: Restart MySQL. Your database will now start, but with innodb_force_recovery, all INSERTs and UPDATEs will be ignored. 


Step 3: Dump all tables


Step 4: Shutdown database and delete the data directory. Run mysql_install_db to create MySQL default tables


Step 5: Remove the innodb_force_recovery line from your /etc/my.cnf file and restart the database. (It should start normally now)


Step 6: Restore everything from your backup


There were other 6 recovery modes availables which can be used as per requirements for corrupted innodb tables






Cheers!!!

Monday, March 21, 2011

Friday, March 4, 2011

MySQL: Multiple Instances

To Install multiple mysql instances

MySQL : Alter Table to Add Field Before or After Field which is already exist



MySQL : Alter Table to Add Field Before or After Field which is already exist -




ALTER TABLE tbl_existing ADD column1 integer BEFORE existing_column2;
ALTER TABLE tbl_existing ADD column3 integer AFTER existing_column2;

Thursday, February 3, 2011

Troubleshooting : Mysql replication

One of the most important procedure in setting up master-slave replication on Mysql database successfully is to have a consistent dump or backup of database(s) from the master server by locking all tables during mysqldump so write access is not possible.
master log file and log position will be taken from the master and configure it on the slave server. But it’s not always good times. There are some bad times too. And one of it is sudden stop of your slave and no longer synching with the master database.
I’ll give you a scenario. Before the replication starts, my slave server has an existing database named mifos. Then you setup your replication without any problems and all goes well. Then one time, somebody created a database server named 'mifos' on the master server with a different tables or structure. Then that might confuse now the mysql process. You can see errors like cannot create database etc.
Another common problem also are duplicate entries caused by inconsistent backups that was restored on the slave server.
To check, inspect your mysqld.logs for errors, something like
#less /var/log/mysqld.log
100318  5:34:46 [ERROR] Slave: Error ‘Duplicate entry ‘mifos-2010-01-30′ for key 1′ on query. Default database: ‘mifosdb’. Query: ‘INSERT INTO mifos_loan_schedule(keyword,requested_date,request_count,mifos_id) VALUES (‘mifos’,’2010-01-30”)’, Error_code: 1062
On mysql prompt ,try to check the status of the slave by:
#mysql -u root -p
mysql> show slave status \G;

Slave_IO_Running: Yes
Slave_SQL_Running: No

You will see from that the Slave_SQL_Running is set to No, indicating that the replication is broken.
To repair the replication, here are the steps that you can try:
mysql> SLAVE STOP;
mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql>SLAVE START;
mysql>SHOW SLAVE STATUS \G

mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.10.10
Master_User: slaveuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mifos-bin.000058
Read_Master_Log_Pos: 470394670
Relay_Log_File: mysqld-relay-bin.000004
Relay_Log_Pos: 123918387
Relay_Master_Log_File:mifos-bin.000058
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
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: 123918248
Relay_Log_Space: 5839136723
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
1 row in set (0.00 sec)

As appeared above, Slave_IO_Running is Yes and Slave_SQL_Running is now set to Yes. Slave State is waiting for master to send Event.
From the mysqld log file, a similar output such as this will appear
100318 23:15:45 [Note] Slave SQL thread initialized, starting replication in log ‘mifos-bin.000058′ at position 603115919, relay log ‘./mysqld-relay-bin.000001′ position: 4
100318 23:15:45 [Note] Slave I/O thread: connected to master
‘slaveuser@10.10.10.10:3306′,  replication started in log ‘mifos-bin.000051′ at position 603115919
If you still have duplicate entries, just rerun the process. But how if are too many and doing all over again is quite stupid already. Then you have an option to put the skip errors to mysql configuration
on /etc/my.cnf
add this line under [mysqld]:
slave-skip-errors=1062
Then restart mysql.