Suhail Kaleem » MySql

MySql

mysql host is blocked because of many connection errors

0
13 years ago
by suhailkaleem in ,

Error:

“host is blocked because of many connection errors unblock with ‘mysqladmin flush-hosts’”

to fix this increase ‘max_connect_errors’ which is 10 by default.

If you can afford to restart the MySQL server, enter below to your my.cnf and restart the MySQL service.

[mysqld]

max_connect_errors = 1000

 

If you can’t afford to restart the MySQL server, follow below steps.

In MySQL pormpt,

set global max_connect_errors = 1000 ;

 

How to dump large mysql files [ 430 MB ]

0
15 years ago
by suhailkaleem in

I had a mysql sql  file of 430 MB.

phpmyadmin had size limit so it did not work
the i tried mysql tool MySQL Query Browser.
but that failed as well it will work for like 50 Mb sql file , for a file large as 430 Mb it wont simply load the sql script
i googled , searched  to find a solution but did not  find any .
then i had an idea, why not split the sql files  into pieces 50 MB each and then execute it one by one it worked but it  was  time consuming  here is what i did

1 – i downloaded  : http://www.pc-tools.net/win32/filesplit/

2 – split my 430 Mb  file to 9 files 50 MB each

3 – open each file in wordpad checked the last  sql query  to see weather it was cut off or not  and yes it was i just copied the last partial line to a new file  and then open next one and cut first partial line and saved it  to the file and then looked at end of second file and also find a partial  query there did the same thing for all 9 files

and then  used MySQL Query Browser to execute each sql file to my mysql database

i know this is time consuming but it did work
if some body has better solution let me know.

MSSQL DATENAME() ALTERNATIVE FOR MYSQL

0
15 years ago
by suhailkaleem in , ,

The mysql equilevent of

DATENAME(WEEKDAY,GETDATE())

is

DATE_FORMAT(NOW(),’%W’)

DATE_FORMAT(NOW(),'%W, %M %e, %Y @ %h:%i %p')
#yields 'Sunday, September 20, 2008 @ 12:45 PM'

MySQL DATE_FORMAT() Letter Representations

Specifier Description
%a Abbreviated weekday name (Sun..Sat)
%b Abbreviated month name (Jan..Dec)
%c Month, numeric (0..12)
%D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d Day of the month, numeric (00..31)
%e Day of the month, numeric (0..31)
%f Microseconds (000000..999999)
%H Hour (00..23)
%h Hour (01..12)
%I Hour (01..12)
%i Minutes, numeric (00..59)
%j Day of year (001..366)
%k Hour (0..23)
%l Hour (1..12)
%M Month name (January..December)
%m Month, numeric (00..12)
%p AM or PM
%r Time, 12-hour (hh:mm:ss followed by AM or PM)
%S Seconds (00..59)
%s Seconds (00..59)
%T Time, 24-hour (hh:mm:ss)
%U Week (00..53), where Sunday is the first day of the week
%u Week (00..53), where Monday is the first day of the week
%V Week (01..53), where Sunday is the first day of the week; used with %X
%v Week (01..53), where Monday is the first day of the week; used with %x
%W Weekday name (Sunday..Saturday)
%w Day of the week (0=Sunday..6=Saturday)
%X Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V
%x Year for the week, where Monday is the first day of the week, numeric, four digits; used with %v
%Y Year, numeric, four digits
%y Year, numeric (two digits)
%% A literal “%” character
%x x, for any “x” not listed above
Go to Top