Re: [Ilugc] mysql upgrade 5.1.73 to 5.5.46

[Arun Khan]

On Thu, Oct 22, 2015 at 6:50 PM, Ajeesh Kannan  wrote:
>
> My serve is using the following bin files.
>
> ]# rpm -qa | grep mysql
> mysql-5.1.73-3.el6_5.x86_64
> mysql-libs-5.1.73-3.el6_5.x86_64
> mysql-server-5.1.73-3.el6_5.x86_64
> mysql-devel-5.1.73-3.el6_5.x86_64
>
> So I need to replace all these bin files with matching 5.5 binary file ?
>

What do you mean by replace?

Use your distro's package manager to upgrade your MySQL packages.
Some one has already mentioned Percona; they build RPM/DEB packages
for all the major distributions out there.

*Before* doing this exercise on your production server, I would
strongly recommend that you carry out exercise on a prototype server
(using a copy of your production data) and make sure you work out all
the kinks (if any) in the upgrade process.   Since it is production, I
would suggest do not go beyond 5.6.x.  It has been vetted in the field
whereas 5.7 (not released yet) may still have some quirks post
release.

-- Arun Khan
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] mysql upgrade 5.1.73 to 5.5.46

[P.R.Karthik]

Hi,

1) Shutdown the MySQL and have a backup of data directory.
2) Replace the MySQL binary files.
3) start the MySQL server with the old data directory.
4) Run the mysql_upgrade to fix the internal tables.

Go with mysql 5.1 to MySQL 5.5. Then move on to MySQL 5.6. MySQL 5.7 will
be GA by next week.

Consider using Percona Server if there is a heavy need and client accepts.
You can also try MariaDB 5.5 if you need more benefit from the Optimizer.

Regards,
KarthiK.P.R

On Wed, Oct 21, 2015 at 12:43 PM, Arun Khan  wrote:

> On Tue, Oct 20, 2015 at 11:26 PM, Ajeesh Kannan 
> wrote:
> > Hi,
> >
> > Is there any good document to refer mysql server upgrade from 5.1.73 to
> > mysql 5.5.46.
> >
>
> Don't know about docs but take *backup* of your data before attempting
> the upgrade!
>
> Off the cuff - why stop at 5.5.46?  GA 5.6.x is also available from
> Percona's site and 5.7, with performance improvement (claimed by
> Oracle), is set to be released in a month or so.
>
> IIRC, MySQL site has release notes and/or docs for upgrades.  I would
> suggest you do the migration in smaller steps  i.e. 5.1 to 5.2 and so
> forth.
>
> -- Arun Khan
> ___
> ILUGC Mailing List:
> http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
> ILUGC Mailing List Guidelines:
> http://ilugc.in/mailinglist-guidelines
>
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] mysql upgrade 5.1.73 to 5.5.46

[Ajeesh Kannan]

On Thu, Oct 22, 2015 at 6:50 PM, Ajeesh Kannan 
wrote:

>
>
> On Thu, Oct 22, 2015 at 4:40 PM, P.R.Karthik  wrote:
>
>> Hi,
>>
>> 1) Shutdown the MySQL and have a backup of data directory.
>> 2) Replace the MySQL binary files.
>> 3) start the MySQL server with the old data directory.
>> 4) Run the mysql_upgrade to fix the internal tables.
>>
>> Go with mysql 5.1 to MySQL 5.5. Then move on to MySQL 5.6. MySQL 5.7 will
>> be GA by next week.
>>
>> Consider using Percona Server if there is a heavy need and client accepts.
>> You can also try MariaDB 5.5 if you need more benefit from the Optimizer.
>>
>> Regards,
>> KarthiK.P.R
>>
>> On Wed, Oct 21, 2015 at 12:43 PM, Arun Khan  wrote:
>>
>> > On Tue, Oct 20, 2015 at 11:26 PM, Ajeesh Kannan <
>> ajeeshbkan...@gmail.com>
>> > wrote:
>> > > Hi,
>> > >
>> > > Is there any good document to refer mysql server upgrade from 5.1.73
>> to
>> > > mysql 5.5.46.
>> > >
>> >
>> > Don't know about docs but take *backup* of your data before attempting
>> > the upgrade!
>> >
>> > Off the cuff - why stop at 5.5.46?  GA 5.6.x is also available from
>> > Percona's site and 5.7, with performance improvement (claimed by
>> > Oracle), is set to be released in a month or so.
>> >
>> > IIRC, MySQL site has release notes and/or docs for upgrades.  I would
>> > suggest you do the migration in smaller steps  i.e. 5.1 to 5.2 and so
>> > forth.
>> >
>> > -- Arun Khan
>> > ___
>> > ILUGC Mailing List:
>> > http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
>> > ILUGC Mailing List Guidelines:
>> > http://ilugc.in/mailinglist-guidelines
>> >
>> ___
>> ILUGC Mailing List:
>> http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
>> ILUGC Mailing List Guidelines:
>> http://ilugc.in/mailinglist-guidelines
>>
>
>
> Thank you Karthik,
>
> My serve is using the following bin files.
>
> ]# rpm -qa | grep mysql
> mysql-5.1.73-3.el6_5.x86_64
> mysql-libs-5.1.73-3.el6_5.x86_64
> mysql-server-5.1.73-3.el6_5.x86_64
> mysql-devel-5.1.73-3.el6_5.x86_64
>
> So I need to replace all these bin files with matching 5.5 binary file ?
>
>
Or i would need to replace each and every files.

~]$ rpm -ql mysql-5.1.73-3.el6_5.x86_64
/usr/bin/msql2mysql
/usr/bin/my_print_defaults
/usr/bin/mysql
/usr/bin/mysql_config
/usr/bin/mysql_find_rows
/usr/bin/mysql_waitpid
/usr/bin/mysqlaccess
/usr/bin/mysqladmin
/usr/bin/mysqlbinlog
/usr/bin/mysqlcheck
/usr/bin/mysqldump


-- 
With Regards,
Ajeesh B.Kannan
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

[Ilugc] MySQL leap second works ?

[Bala Kumar]

Hi everyone,
  During the Monthly Meet - Aug 8-2015 - I raised an open question
regarding leap
seconds and how are they handled in DB's. Everyone had our opinions and
most of the
attendees suggested it is taken care by the OS.

Interestingly looks like Leap second is handled by DB as well because of
the usage of
*now()* function. Found this link interesting
https://dev.mysql.com/doc/refman/5.5/en/time-zone-leap-seconds.html.

In recent versions of MySQL leap second 59:60 is reverted to 59:59 and
because of this
decrement there can be ambiguities in our where queries. For accurate query
results MySQL
suggests using *UNIX_TIMESTAMP(timestamp_column).*

Eg:
  = mysql SELECT a, ts, UNIX_TIMESTAMP(ts) FROM t1;
  +--+-++
  | a | ts | UNIX_TIMESTAMP(ts) |
  +--+-++ |
  | 1 | 2008-12-31 23:59:59 |1230767999 |
  | 2 | 2008-12-31 23:59:59 |1230768000 |
  +--+-++
  2 rows in set (0.00 sec)

  Querying for the leap second -
  = SELECT * FROM t1 WHERE ts = '2008-12-31 23:59:60';*
 Empty set, 2 warnings (0.00 sec)
 Returns empty set, because it is stored as 23:59:59 as shown in first
query.

  Using UNIX_TIMESTAMP in the condition we get -
  = mysql *SELECT * FROM t1 WHERE UNIX_TIMESTAMP(ts) = 1230768000;
+--+-+
| a| ts  |
+--+-+
|2 | 2008-12-31 23:59:59 |
+--+-+
1 row in set (0.00 sec)

  Interesting part is that for 59:59 we get only one result and not the
expected two rows.
  = mysql *SELECT * FROM t1 WHERE ts = '2008-12-31 23:59:59';*
+--+-+
| a| ts  |
+--+-+
|1 | 2008-12-31 23:59:59 |
+--+-+
1 row in set (0.00 sec)

I Took this Leap Second example from MySQL doc and when I tried it on my
machine, this leap
second behaviour was not working - very recently someone have commented the
same in the doc.
Please let me know if the example actually works for anyone else ?

Also http://www.postgresql.org/docs/9.1/static/functions-datetime.html says
PostgreSQL uses UT1 because leap seconds are not handled. Interested to
know if there
are any such MySQL like decrements for leap seconds in PostgreSQL too.

Thanks,
Bala.
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] mysql replication over ssl - aws vs rackspace

[Ajeesh Kannan]

On Mon, Aug 3, 2015 at 5:08 PM, Balasubramaniam Natarajan 
bala150...@gmail.com wrote:

 If I understand correctly you are trying to do replication over two service
 providers of AWS and Rackspace.  If that is the case you need to use the
 Fully qualified domain name for setting your masterserver rather than 
 masterserver.  More over the replication user should have access to coming
 from that IP address.

 On Mon, Aug 3, 2015 at 3:36 AM, Ajeesh Kannan ajeeshbkan...@gmail.com
 wrote:

  Hi,
 
  I am getting the following error while connecting mysql replication over
  ssl connection.
 
  [root@slave server ]# mysql -u replicationuser -p -hmasterserver
  --ssl-ca /etc/ssl/certs/mysql/ca-cert.pem --ssl-cert
  /etc/ssl/certs/mysql/slave-public.pem --ssl-key
  /etc/ssl/certs/mysql/slave-private.pem
 
  *ERROR 2026 (HY000): SSL connection error*
 
 
  mysql slave server is located on : *aws* [ ec2 instance ]
  mysql master server is located on : *RackSpace* [ cloud server ]
 
  on rackspace :- any traffic is allowed from that machine to host
 
  on master
   mysql show variables like '%ssl%';
  +---++
  | Variable_name | Value  |
  +---++
  | have_openssl  | YES|
  | have_ssl  | YES|
  | ssl_ca| /etc/mysql/ca-cert.pem |
  | ssl_capath||
  | ssl_cert  | /etc/mysql/server-cert.pem |
  | ssl_cipher||
  | ssl_key   | /etc/mysql/server-key.pem  |
  +---++
 
 
  Using the same ssl key I can connect replication with in aws/rack space
  servers.
 


That is for better understanding i have added those names.
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

[Ilugc] mysql replication over ssl - aws vs rackspace

[Ajeesh Kannan]

Hi,

I am getting the following error while connecting mysql replication over
ssl connection.

[root@slave server ]# mysql -u replicationuser -p -hmasterserver
--ssl-ca /etc/ssl/certs/mysql/ca-cert.pem --ssl-cert
/etc/ssl/certs/mysql/slave-public.pem --ssl-key
/etc/ssl/certs/mysql/slave-private.pem

*ERROR 2026 (HY000): SSL connection error*


mysql slave server is located on : *aws* [ ec2 instance ]
mysql master server is located on : *RackSpace* [ cloud server ]

on rackspace :- any traffic is allowed from that machine to host

on master
 mysql show variables like '%ssl%';
+---++
| Variable_name | Value  |
+---++
| have_openssl  | YES|
| have_ssl  | YES|
| ssl_ca| /etc/mysql/ca-cert.pem |
| ssl_capath||
| ssl_cert  | /etc/mysql/server-cert.pem |
| ssl_cipher||
| ssl_key   | /etc/mysql/server-key.pem  |
+---++


Using the same ssl key I can connect replication with in aws/rack space
servers.

-- 
With Regards,
Ajeesh B.Kannan
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] mysql replication over ssl - aws vs rackspace

[Balasubramaniam Natarajan]

Okay then in your mysql conf file under the client directive did you add
the certs ?

[client]
ssl-ca = path to cert
ssl-cert = path to cert
ssl-key = path to cert

On Mon, Aug 3, 2015 at 10:43 AM, Ajeesh Kannan ajeeshbkan...@gmail.com
wrote:

 On Mon, Aug 3, 2015 at 5:08 PM, Balasubramaniam Natarajan 
 bala150...@gmail.com wrote:

  If I understand correctly you are trying to do replication over two
 service
  providers of AWS and Rackspace.  If that is the case you need to use the
  Fully qualified domain name for setting your masterserver rather than 
  masterserver.  More over the replication user should have access to
 coming
  from that IP address.
 
  On Mon, Aug 3, 2015 at 3:36 AM, Ajeesh Kannan ajeeshbkan...@gmail.com
  wrote:
 
   Hi,
  
   I am getting the following error while connecting mysql replication
 over
   ssl connection.
  
   [root@slave server ]# mysql -u replicationuser -p -hmasterserver
   --ssl-ca /etc/ssl/certs/mysql/ca-cert.pem --ssl-cert
   /etc/ssl/certs/mysql/slave-public.pem --ssl-key
   /etc/ssl/certs/mysql/slave-private.pem
  
   *ERROR 2026 (HY000): SSL connection error*
  
  
   mysql slave server is located on : *aws* [ ec2 instance ]
   mysql master server is located on : *RackSpace* [ cloud server ]
  
   on rackspace :- any traffic is allowed from that machine to host
  
   on master
mysql show variables like '%ssl%';
   +---++
   | Variable_name | Value  |
   +---++
   | have_openssl  | YES|
   | have_ssl  | YES|
   | ssl_ca| /etc/mysql/ca-cert.pem |
   | ssl_capath||
   | ssl_cert  | /etc/mysql/server-cert.pem |
   | ssl_cipher||
   | ssl_key   | /etc/mysql/server-key.pem  |
   +---++
  
  
   Using the same ssl key I can connect replication with in aws/rack space
   servers.
  
 

 That is for better understanding i have added those names.
 ___
 ILUGC Mailing List:
 http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
 ILUGC Mailing List Guidelines:
 http://ilugc.in/mailinglist-guidelines




-- 
Regards,
Balasubramaniam Natarajan
http://blog.etutorshop.com
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] mysql replication over ssl - aws vs rackspace

[Balasubramaniam Natarajan]

If I understand correctly you are trying to do replication over two service
providers of AWS and Rackspace.  If that is the case you need to use the
Fully qualified domain name for setting your masterserver rather than 
masterserver.  More over the replication user should have access to coming
from that IP address.

On Mon, Aug 3, 2015 at 3:36 AM, Ajeesh Kannan ajeeshbkan...@gmail.com
wrote:

 Hi,

 I am getting the following error while connecting mysql replication over
 ssl connection.

 [root@slave server ]# mysql -u replicationuser -p -hmasterserver
 --ssl-ca /etc/ssl/certs/mysql/ca-cert.pem --ssl-cert
 /etc/ssl/certs/mysql/slave-public.pem --ssl-key
 /etc/ssl/certs/mysql/slave-private.pem

 *ERROR 2026 (HY000): SSL connection error*


 mysql slave server is located on : *aws* [ ec2 instance ]
 mysql master server is located on : *RackSpace* [ cloud server ]

 on rackspace :- any traffic is allowed from that machine to host

 on master
  mysql show variables like '%ssl%';
 +---++
 | Variable_name | Value  |
 +---++
 | have_openssl  | YES|
 | have_ssl  | YES|
 | ssl_ca| /etc/mysql/ca-cert.pem |
 | ssl_capath||
 | ssl_cert  | /etc/mysql/server-cert.pem |
 | ssl_cipher||
 | ssl_key   | /etc/mysql/server-key.pem  |
 +---++


 Using the same ssl key I can connect replication with in aws/rack space
 servers.

 --
 With Regards,
 Ajeesh B.Kannan
 ___
 ILUGC Mailing List:
 http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
 ILUGC Mailing List Guidelines:
 http://ilugc.in/mailinglist-guidelines




-- 
Regards,
Balasubramaniam Natarajan
http://blog.etutorshop.com
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] Mysql user's SSL connection status

[P.R.Karthik]

You can force all the the users to use SSL by enabling ssl-only on the
mysql config ( /etc/my.cnf ) and enabling SSL in user creation ( REQUIRE
SSL ). Make sure that you application has the client certs installed.

The below query will provide you the list of users who do not have SSL
( REQUIRE
SSL ).

 select user,host,ssl_type from mysql.user where ssl_type='';

Regards,
KarthiK.P.R

On Mon, May 11, 2015 at 12:14 PM, Balasubramaniam Natarajan 
bala150...@gmail.com wrote:

 On Mon, May 11, 2015 at 10:43 AM, Ashish Yadav gwalash...@gmail.com
 wrote:

 Thanks for the reply Ashish.


  How did you create the MySQL users in the server?
  You have to forced the user to use SSL at the time creation only.
 
 
 Invariable of having REQUIRE SSL on [1]. If your client is supplied with
 certs. It will attempt to switch over to mysql over SSL, if the server
 supports it.  At least that is what I hope it is.  So if I were to create a
 user without REQUIRE SSL and try to login.  The switching happens to SSL
 and I can type in \s at the mysql   prompt to verify it.  How can I
 verify logging in as root if the person logging in as iamsecure is riding
 over SSL or not ?

 [1]
 GRANT ALL PRIVILEGES ON *.* TO ‘iamsecure’@’%’ IDENTIFIED BY ‘dingdingding’
 REQUIRE SSL;

 For that you can use Wireshark and see the password is indeed encrypted or
  not.
 
  Even Wireshark is beating around the bush :-(

 --
 Regards,
 Balasubramaniam Natarajan
 http://blog.etutorshop.com
 ___
 ILUGC Mailing List:
 http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
 ILUGC Mailing List Guidelines:
 http://ilugc.in/mailinglist-guidelines

___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] Mysql user's SSL connection status

[Balasubramaniam Natarajan]

On Mon, May 11, 2015 at 3:20 AM, P.R.Karthik prk...@gmail.com wrote:

 You can force all the the users to use SSL by enabling ssl-only on the
 mysql config ( /etc/my.cnf ) and enabling SSL in user creation ( REQUIRE
 SSL ). Make sure that you application has the client certs installed.

 The below query will provide you the list of users who do not have SSL
 ( REQUIRE
 SSL ).

  select user,host,ssl_type from mysql.user where ssl_type='';

 Regards,
 KarthiK.P.R


I guess my question is not phrased properly.

1. Enable mysql on *serverA* with SSL support.
2. Create a user remotesecure to login without the REQUIRE SSL to login
remotely.
3. Log in using remotesecure from *serverB* by given it the appropriate SSL
certs.
4. At the mysql prompt type \s  I can see that I am riding over SSL.
5. Now open another connection to mysql on *serverA* from *serverC* as root
user without giving it SSL certs and type in \s you can see that you are
riding on clear text.
6. What is the command I need to type in *serverC *connection to see that
equivalent output of step4 ?

-- 
Regards,
Balasubramaniam Natarajan
http://blog.etutorshop.com
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] Mysql user's SSL connection status

[Ashish Yadav]

Hi,

On Sun, May 10, 2015 at 7:46 PM, Balasubramaniam Natarajan 
bala150...@gmail.com wrote:

 Hi

 When I enable SSL based connection to mysql server I can type \s at the
 mysql prompt to know if I am riding on top of an ssl connection to my mysql
 server.


How did you create the MySQL users in the server?
You have to forced the user to use SSL at the time creation only.

After that you have to create client's certificate and
configure client side's /etc/my.cnf appropriately to enable SSL using
those certificate.

You can also refer below link for more detail,

http://lowendbox.com/blog/getting-started-with-mysql-over-ssl/



 How can I ensure that the other users who had logged in to my mysql server
 is also logged in via the SSL pipe line.  The round about method would be
 for me to capture pcap and see.  However I would like to know if there is a
 show status command for other users who has logged in.  My Google kufu is
 not getting me much.


For that you can use Wireshark and see the password is indeed encrypted or
not.


--Regards
Ashishkumar S. Yadav
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] mysql backup not working in /etc/cron.daily and /etc/cron.hourly

[Arun Khan]

On Fri, Apr 24, 2015 at 2:29 PM, Ajeesh Kannan ajeeshbkan...@gmail.com wrote:
 Hi,

 my backup script is working when this run manually and in cron jobs. But
 the script is not taking mysql dump when this is added in /etc/cron.hourly


put your own one line cron file in /etc/cron.d  (read 'man 5 crontab')
to call your script.

 ++
 while read x ; do
 mysqldump ${x}  /home/backup/TEST/${x}_`date +%d-%m-%Y`.sql
 if [ $? -eq 0 ]

string comparison should be quoted, numeric comparison should be unquoted
e.g  if [ $? -eq 0 ]

 echo $x backup is OK  $STATUSFILE
 else
 echo # WARNING: ERROR #  $x backup failed  $STATUSFILE
 fi
 done/etc/cron.hourly/dbfile

Why is the content of the 'dbfile' and why have put it in /etc/cron.hourly?

Alternately, you can declare a local variable x=db1 db2 db3 dnN  in
the script itself and use
the for n in ${x}; do blah blah; done

 ++

 Also tried with mysqldump ${x}  /home/backup/TEST/${x}_`date +%d-%m-%Y`.sql

The symbol '%' has a special meaning to cron so you need to escape it
i.e use \.   (RTFM 'man 5 crontab').
BTW a lot of people trip on  % symbol in cron tab entries.

HTH
-- Arun
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

[Ilugc] mysql backup not working in /etc/cron.daily and /etc/cron.hourly

[Ajeesh Kannan]

Hi,

my backup script is working when this run manually and in cron jobs. But
the script is not taking mysql dump when this is added in /etc/cron.hourly

++
while read x ; do
mysqldump ${x}  /home/backup/TEST/${x}_`date +%d-%m-%Y`.sql
if [ $? -eq 0 ]
then
echo $x backup is OK  $STATUSFILE
else
echo # WARNING: ERROR #  $x backup failed  $STATUSFILE
fi
done/etc/cron.hourly/dbfile
++

Also tried with mysqldump ${x}  /home/backup/TEST/${x}_`date +%d-%m-%Y`.sql
-- 
With Regards,
Ajeesh B.Kannan
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] mysql slave performance

[Ajeesh Kannan]

Hi Karthik,

Thank you for your update.

My mysql server is running with old version 5.1 and it is hard to upgrade
at the moment.

On Mon, Mar 30, 2015 at 5:48 PM, P.R.Karthik prk...@gmail.com wrote:

 Hi ,

 The slave lag for a few seconds is common. It might be heavy writes on
 master delaying the replication. As replication in MySQL is single threaded
 it can happen ( Hope you are not using Multi threaded replication in MySQL
 5.6 ).

 What is the binglog format ?
 Is the queries are tuned along with MySQL settings ?
 Any notable queries in slow query log ?


 Regards,
 KarthiK.P.R
 MySQL DBA

 On Wed, Mar 25, 2015 at 5:10 PM, Arun Khan knu...@gmail.com wrote:

  On Wed, Mar 25, 2015 at 4:46 PM, Ajeesh Kannan ajeeshbkan...@gmail.com
  wrote:
   Hi,
  
   I am using the slave connection using the internal LAN IP. Number of
 cpu
   cores in slave is higher than master server.
 
  List guidelines -- do *not* top post.
 
  Look at number of transactions pending replication @ primary and
  compare those number at the replica.
 
  -- Arun Khan
  ___
  ILUGC Mailing List:
  http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
  ILUGC Mailing List Guidelines:
  http://ilugc.in/mailinglist-guidelines
 
 ___
 ILUGC Mailing List:
 http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
 ILUGC Mailing List Guidelines:
 http://ilugc.in/mailinglist-guidelines




-- 
With Regards,
Ajeesh B.Kannan
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] mysql slave performance

[P.R.Karthik]

Hi Ajeesh,

MySQL 5.1 should be upgraded as there is no bug fixes on MySQL 5.1.
Consider moving to MySQL 5.5 or MySQL 5.6. It will be just an binary
upgrade in MySQL and the underlying data do not any change. Please let me
know if you need any help in mysql upgrade too.

Regards,
KarthiK.P.R

On Tue, Mar 31, 2015 at 2:04 PM, Ajeesh Kannan ajeeshbkan...@gmail.com
wrote:

 Hi Karthik,

 Thank you for your update.

 My mysql server is running with old version 5.1 and it is hard to upgrade
 at the moment.

 On Mon, Mar 30, 2015 at 5:48 PM, P.R.Karthik prk...@gmail.com wrote:

  Hi ,
 
  The slave lag for a few seconds is common. It might be heavy writes on
  master delaying the replication. As replication in MySQL is single
 threaded
  it can happen ( Hope you are not using Multi threaded replication in
 MySQL
  5.6 ).
 
  What is the binglog format ?
  Is the queries are tuned along with MySQL settings ?
  Any notable queries in slow query log ?
 
 
  Regards,
  KarthiK.P.R
  MySQL DBA
 
  On Wed, Mar 25, 2015 at 5:10 PM, Arun Khan knu...@gmail.com wrote:
 
   On Wed, Mar 25, 2015 at 4:46 PM, Ajeesh Kannan 
 ajeeshbkan...@gmail.com
   wrote:
Hi,
   
I am using the slave connection using the internal LAN IP. Number of
  cpu
cores in slave is higher than master server.
  
   List guidelines -- do *not* top post.
  
   Look at number of transactions pending replication @ primary and
   compare those number at the replica.
  
   -- Arun Khan
   ___
   ILUGC Mailing List:
   http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
   ILUGC Mailing List Guidelines:
   http://ilugc.in/mailinglist-guidelines
  
  ___
  ILUGC Mailing List:
  http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
  ILUGC Mailing List Guidelines:
  http://ilugc.in/mailinglist-guidelines
 



 --
 With Regards,
 Ajeesh B.Kannan
 ___
 ILUGC Mailing List:
 http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
 ILUGC Mailing List Guidelines:
 http://ilugc.in/mailinglist-guidelines

___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] mysql slave performance

[P.R.Karthik]

Hi ,

The slave lag for a few seconds is common. It might be heavy writes on
master delaying the replication. As replication in MySQL is single threaded
it can happen ( Hope you are not using Multi threaded replication in MySQL
5.6 ).

What is the binglog format ?
Is the queries are tuned along with MySQL settings ?
Any notable queries in slow query log ?


Regards,
KarthiK.P.R
MySQL DBA

On Wed, Mar 25, 2015 at 5:10 PM, Arun Khan knu...@gmail.com wrote:

 On Wed, Mar 25, 2015 at 4:46 PM, Ajeesh Kannan ajeeshbkan...@gmail.com
 wrote:
  Hi,
 
  I am using the slave connection using the internal LAN IP. Number of cpu
  cores in slave is higher than master server.

 List guidelines -- do *not* top post.

 Look at number of transactions pending replication @ primary and
 compare those number at the replica.

 -- Arun Khan
 ___
 ILUGC Mailing List:
 http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
 ILUGC Mailing List Guidelines:
 http://ilugc.in/mailinglist-guidelines

___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] mysql slave performance

[Arun Khan]

On Wed, Mar 25, 2015 at 4:46 PM, Ajeesh Kannan ajeeshbkan...@gmail.com wrote:
 Hi,

 I am using the slave connection using the internal LAN IP. Number of cpu
 cores in slave is higher than master server.

List guidelines -- do *not* top post.

Look at number of transactions pending replication @ primary and
compare those number at the replica.

-- Arun Khan
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] mysql slave performance

[Ajeesh Kannan]

Hi,

I am using the slave connection using the internal LAN IP. Number of cpu
cores in slave is higher than master server.

On Wed, Mar 25, 2015 at 11:15 AM, Arun Khan knu...@gmail.com wrote:

 On Tue, Mar 24, 2015 at 5:42 PM, Ajeesh Kannan ajeeshbkan...@gmail.com
 wrote:
  Hi
 
  My mysql slave server is showing Second Behind Master values 30 to 100 .
 At
  this time there is no mysql backup is running. I would like to make my
  slave server should be up to date. But upon checking I can see that the
  Second Behind Master on a particular time frame for all the day. Disk
  resource, load mysql process are all normal too.
 

 A couple of setups we have, the replication is almost real time (on
 LAN) - the client transaction loads are light.

 Ensure network connectivity is not the problem.

 -- Arun Khan
 ___
 ILUGC Mailing List:
 http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
 ILUGC Mailing List Guidelines:
 http://ilugc.in/mailinglist-guidelines




-- 
With Regards,
Ajeesh B.Kannan
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

[Ilugc] mysql slave performance

[Ajeesh Kannan]

Hi

My mysql slave server is showing Second Behind Master values 30 to 100 . At
this time there is no mysql backup is running. I would like to make my
slave server should be up to date. But upon checking I can see that the
Second Behind Master on a particular time frame for all the day. Disk
resource, load mysql process are all normal too.


-- 
With Regards,
Ajeesh B.Kannan
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] mysql slave performance

[Arun Khan]

On Tue, Mar 24, 2015 at 5:42 PM, Ajeesh Kannan ajeeshbkan...@gmail.com wrote:
 Hi

 My mysql slave server is showing Second Behind Master values 30 to 100 . At
 this time there is no mysql backup is running. I would like to make my
 slave server should be up to date. But upon checking I can see that the
 Second Behind Master on a particular time frame for all the day. Disk
 resource, load mysql process are all normal too.


A couple of setups we have, the replication is almost real time (on
LAN) - the client transaction loads are light.

Ensure network connectivity is not the problem.

-- Arun Khan
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] Mysql SSL segmentation fault

[Balasubramaniam Natarajan]

On Wed, Jan 7, 2015 at 9:16 PM, Arun Khan knu...@gmail.com wrote:


 Did you stumble upon this or was there any hint in any log files that
 you were going to look into?


no hint from log file.

-- 
Regards,
Balasubramaniam Natarajan
http://blog.etutorshop.com
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] Mysql SSL segmentation fault

[Arun Khan]

On Wed, Jan 7, 2015 at 12:49 PM, Balasubramaniam Natarajan
bala150...@gmail.com wrote:
 Okay finally figured out what the problem was.

Wunderbar.


 Redhat mysql-server or mysql-client version 5.1.73 would work with PKCS#8,
 However mysql-client-5.5 on debian wheezy would not work with PKCS#8 I had
 to convert them to PKCS#1 for them to work as shown in [1].


Did you stumble upon this or was there any hint in any log files that
you were going to look into?

 openssl rsa -in key_in_pkcs1_or_pkcs8.pem -out key_in_pkcs1.pem

 [1]
 http://askubuntu.com/questions/194074/enabling-ssl-in-mysql


Thanks for sharing the solution - tis a rarity nowadays.

-- Arun Khan
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] Mysql SSL segmentation fault

[Balasubramaniam Natarajan]

Okay finally figured out what the problem was.

Redhat mysql-server or mysql-client version 5.1.73 would work with PKCS#8,
However mysql-client-5.5 on debian wheezy would not work with PKCS#8 I had
to convert them to PKCS#1 for them to work as shown in [1].

openssl rsa -in key_in_pkcs1_or_pkcs8.pem -out key_in_pkcs1.pem

[1]
http://askubuntu.com/questions/194074/enabling-ssl-in-mysql

-- 
Regards,
Balasubramaniam Natarajan
http://blog.etutorshop.com
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] Mysql SSL segmentation fault

[Balasubramaniam Natarajan]

On Tue, Jan 6, 2015 at 12:51 PM, Arun Khan knu...@gmail.com wrote:


 Hmmm.   You mean mysql client version 5.1 on Wheezy?   Seg fault could
 also be caused by corrupted files and/or bad memory.  apt-get purge
 mysql client and reinstall.



This is what the stock provides and it fails over SSL connection.
Redhat (Santiago) mysql-server version is 5.1.73
Debian Wheezy mysql client version is 5.4.x



 mysqld (server) also writes log files in /var/lib/mysql.   Not sure
 about the client but see if you find anything in /var/lib/mysql.



I will check on this today.

-- 
Regards,
Balasubramaniam Natarajan
http://blog.etutorshop.com
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] Mysql SSL segmentation fault

[Arun Khan]

On Sun, Jan 4, 2015 at 10:22 PM, Balasubramaniam Natarajan
bala150...@gmail.com wrote:
 On Sun, Jan 4, 2015 at 8:27 PM, Arun Khan knu...@gmail.com wrote:

 Is the MySQL server (RHEL) and client (Deb Wheezy)
 the same version?   It should not matter though.


 Nope, For Redhat server they only support 5.1.73, however debian client is
 up on 5.4.x...

 I tried using the same mysql client version on debian however even that
 resulted in the same Segmentation Fault.


Hmmm.   You mean mysql client version 5.1 on Wheezy?   Seg fault could
also be caused by corrupted files and/or bad memory.  apt-get purge
mysql client and reinstall.

   Anything related to
 the segfault in the log files (Deb Wheezy)?


mysqld (server) also writes log files in /var/lib/mysql.   Not sure
about the client but see if you find anything in /var/lib/mysql.

-- Arun Khan
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] Mysql SSL segmentation fault

[Arun Khan]

On Wed, Dec 31, 2014 at 1:05 PM, Balasubramaniam Natarajan
bala150...@gmail.com wrote:
 Hi

 Has any one had success with configuring these from stalk.

I guess, you meant stock (stalk has a different meaning).

CentOS server + CentOS client -- yes.


 Mysqld server which supports SSL on a Redhat box (Santiago) and then have
 mysql client from a debian (Wheezy) connect in over SSL without any problem
 ?

 When I tried doing it I ended up with Segmentation fault on my debian
 machine.  However when I used another Redhat box(Santiago) for mysql client
 I was successful in establishing the connection over SSL.  Could someone in
 the group pour their SME opinion on this issue ?

I am not an SME.   Is the MySQL server (RHEL) and client (Deb Wheezy)
the same version?   It should not matter though.  Anything related to
the segfault in the log files (Deb Wheezy)?   Alternately, setup the
Deb Wheezy MySQL server and see if client can connect w/o seg
faulting.

-- Arun Khan
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] Mysql SSL segmentation fault

[Balasubramaniam Natarajan]

On Sun, Jan 4, 2015 at 8:27 PM, Arun Khan knu...@gmail.com wrote:


 I guess, you meant stock (stalk has a different meaning).

 Oops, I ment what's available in the official repo as you said stock


 CentOS server + CentOS client -- yes.

 My Redhat mysql server and Redhat mysql client has not problem connecting
over ssl.



 I am not an SME.


Subject Matter Expert (SME)


 Is the MySQL server (RHEL) and client (Deb Wheezy)
 the same version?   It should not matter though.


Nope, For Redhat server they only support 5.1.73, however debian client is
up on 5.4.x...

I tried using the same mysql client version on debian however even that
resulted in the same Segmentation Fault.


   Anything related to
 the segfault in the log files (Deb Wheezy)?


I did not see probably even with the verbose flag on the client side on the
terminal.


 Alternately, setup the
 Deb Wheezy MySQL server and see if client can connect w/o seg
 faulting.

 I will try this.
  http://www.ae.iitm.ac.in/mailman/listinfo/ilugc


-- 
Regards,
Balasubramaniam Natarajan
http://blog.etutorshop.com
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

[Ilugc] Mysql SSL segmentation fault

[Balasubramaniam Natarajan]

Hi

Has any one had success with configuring these from stalk.

Mysqld server which supports SSL on a Redhat box (Santiago) and then have
mysql client from a debian (Wheezy) connect in over SSL without any problem
?

When I tried doing it I ended up with Segmentation fault on my debian
machine.  However when I used another Redhat box(Santiago) for mysql client
I was successful in establishing the connection over SSL.  Could someone in
the group pour their SME opinion on this issue ?

-- 
Regards,
Balasubramaniam Natarajan
http://blog.etutorshop.com
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

[Ilugc] MySQL - unable to connect to MySQL db from another host on same LAN

[Arun Khan]

I have 2 AWS instances (CentOS 6.5 64bit AMI)
(a) Apache, PHP, mysql client package  (IP = A, alias name aws-appsrv)
(b) mysql-server (IP = B alias name aws-dbsrv).

I have configured mysql server on B to accept connections on 3306 from A.
I have also configured mysql server on B to accept connections for
'root' user from A both with IP number (A) and alias name
(aws-appsrv).

On mysql server (B)
mysql select user,host,grant_priv from user;
+--+++
| user | host   | grant_priv |
+--+++
| root | localhost  | Y  |
| root | aws-appsrv | Y  |
| root | 127.0.0.1  | Y  |
| root | 10.0.0.244 | Y  |
+--+++

On aws-appsrv (A), when I connect to mysql server on B as 'root' user,
I get the following error.

# mysql -u root -h aws-dbsrv -D mysql -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'aws-appsrv' (using
password: YES)

I have searched for a solution but have not come across one that meets
my use case scenario.

What am I missing?

-- Arun Khan
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] MySQL - unable to connect to MySQL db from another host on same LAN

[Balasubramaniam Natarajan]

On Wed, Aug 6, 2014 at 12:09 PM, Arun Khan knu...@gmail.com wrote:

 I have 2 AWS instances (CentOS 6.5 64bit AMI)
 (a) Apache, PHP, mysql client package  (IP = A, alias name aws-appsrv)
 (b) mysql-server (IP = B alias name aws-dbsrv).

 I have configured mysql server on B to accept connections on 3306 from A.
 I have also configured mysql server on B to accept connections for
 'root' user from A both with IP number (A) and alias name
 (aws-appsrv).

 On mysql server (B)
 mysql select user,host,grant_priv from user;
 +--+++
 | user | host   | grant_priv |
 +--+++
 | root | localhost  | Y  |
 | root | aws-appsrv | Y  |
 | root | 127.0.0.1  | Y  |
 | root | 10.0.0.244 | Y  |
 +--+++

 On aws-appsrv (A), when I connect to mysql server on B as 'root' user,
 I get the following error.

 # mysql -u root -h aws-dbsrv -D mysql -p
 Enter password:
 ERROR 1045 (28000): Access denied for user 'root'@'aws-appsrv' (using
 password: YES)

 I have searched for a solution but have not come across one that meets
 my use case scenario.

 What am I missing?



1. Could you try logging in locally by adding an appropriate grant
statement ?

2. Try using the FQDN for the grant statement on mysql
server.
 GRANT ALL ON YourDB.* TO 'root'@
'mysql_root_password';
 FLUSH PRIVILEGES;

-- 
Regards,
Balasubramaniam Natarajan
http://blog.etutorshop.com
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] MySQL - unable to connect to MySQL db from another host on same LAN

[Arun Khan]

On Wed, Aug 6, 2014 at 12:16 PM, Balasubramaniam Natarajan
bala150...@gmail.com wrote:
 On Wed, Aug 6, 2014 at 12:09 PM, Arun Khan knu...@gmail.com wrote:
 1. Could you try logging in locally by adding an appropriate grant
 statement ?

 2. Try using the FQDN for the grant statement on mysql
 server.
  GRANT ALL ON YourDB.* TO 'root'@
 'mysql_root_password';
  FLUSH PRIVILEGES;


I have already done the following before trying to connect

GRANT ALL PRIVILEGES ON *.* TO 'root'@'A' WITH GRANT OPTION;

where A is the IP address and

GRANT ALL PRIVILEGES ON *.* TO 'root'@'aws-appsrv' WITH GRANT OPTION;

where aws-appsrv is resolved by /etc/hosts with the line.

Aaws-appsrv

-- Arun Khan
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] MySQL - unable to connect to MySQL db from another host on same LAN

[Saravanakumar Karunanithi]

Does your security group allow the mysql port 3306?

is IPTables blocking the port?

check using the following command



*telnet aws-appsrv 3306  *


On Wed, Aug 6, 2014 at 12:09 PM, Arun Khan knu...@gmail.com wrote:

 I have 2 AWS instances (CentOS 6.5 64bit AMI)
 (a) Apache, PHP, mysql client package  (IP = A, alias name aws-appsrv)
 (b) mysql-server (IP = B alias name aws-dbsrv).

 I have configured mysql server on B to accept connections on 3306 from A.
 I have also configured mysql server on B to accept connections for
 'root' user from A both with IP number (A) and alias name
 (aws-appsrv).

 On mysql server (B)
 mysql select user,host,grant_priv from user;
 +--+++
 | user | host   | grant_priv |
 +--+++
 | root | localhost  | Y  |
 | root | aws-appsrv | Y  |
 | root | 127.0.0.1  | Y  |
 | root | 10.0.0.244 | Y  |
 +--+++

 On aws-appsrv (A), when I connect to mysql server on B as 'root' user,
 I get the following error.

 # mysql -u root -h aws-dbsrv -D mysql -p
 Enter password:
 ERROR 1045 (28000): Access denied for user 'root'@'aws-appsrv' (using
 password: YES)

 I have searched for a solution but have not come across one that meets
 my use case scenario.

 What am I missing?

 -- Arun Khan
 ___
 ILUGC Mailing List:
 http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
 ILUGC Mailing List Guidelines:
 http://ilugc.in/mailinglist-guidelines




-- 
Thanks  Regards,
Saravanakumar Karunanithi
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] MySQL - unable to connect to MySQL db from another host on same LAN

[Arun Venkataswamy]

On Wed, Aug 6, 2014 at 12:09 PM, Arun Khan knu...@gmail.com wrote:

 I have 2 AWS instances (CentOS 6.5 64bit AMI)
 (a) Apache, PHP, mysql client package  (IP = A, alias name aws-appsrv)
 (b) mysql-server (IP = B alias name aws-dbsrv).

 I have configured mysql server on B to accept connections on 3306 from A.
 I have also configured mysql server on B to accept connections for
 'root' user from A both with IP number (A) and alias name
 (aws-appsrv).

 On mysql server (B)
 mysql select user,host,grant_priv from user;
 +--+++
 | user | host   | grant_priv |
 +--+++
 | root | localhost  | Y  |
 | root | aws-appsrv | Y  |
 | root | 127.0.0.1  | Y  |
 | root | 10.0.0.244 | Y  |
 +--+++

 On aws-appsrv (A), when I connect to mysql server on B as 'root' user,
 I get the following error.

 # mysql -u root -h aws-dbsrv -D mysql -p
 Enter password:
 ERROR 1045 (28000): Access denied for user 'root'@'aws-appsrv' (using
 password: YES)

 I have searched for a solution but have not come across one that meets
 my use case scenario.

 What am I missing?



Is the mysql server listening on all interfaces?
I think by default it listens only on localhost or 127.0.0.1

Another naive point, just to be sure : Have you opened up port 3306 in the
firewall?

Arun Venkataswamy
http://wondroussky.blogspot.in/
http://wondrouscode.blogspot.in/

கற்றது கைமண் அளவு, கல்லாதது உலகளவு - ஔவையார்
Known is a drop, Unknown is an ocean
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] MySQL - unable to connect to MySQL db from another host on same LAN

[Arun Khan]

On Wed, Aug 6, 2014 at 12:36 PM, Saravanakumar Karunanithi
akk.saravanaku...@gmail.com wrote:
 Does your security group allow the mysql port 3306?

Yes.


 is IPTables blocking the port?

No. there is no rule.


 check using the following command


 *telnet aws-appsrv 3306  *

Yes this works.  Checked all of the above before posting.

Also, please avoid top posting.

-- Arun Khan
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] MySQL - unable to connect to MySQL db from another host on same LAN

[Arun Khan]

On Wed, Aug 6, 2014 at 12:38 PM, Arun Venkataswamy arun...@gmail.com wrote:

 Is the mysql server listening on all interfaces?
 I think by default it listens only on localhost or 127.0.0.1


Yes, bind_address=0.0.0.0  (allow connection from any IP number).

 Another naive point, just to be sure : Have you opened up port 3306 in the
 firewall?

AWS, has a pretty neat concept called Security Groups  wherein you
can define inbount and outbound traffic.

Checked that as well, inbound to 3306 is allowed.
 I can telnet aws-appsrv 3306 from a different node and get connection
and prompt for password.

-- Arun Khan
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

[Ilugc] MySQL - unable to connect to MySQL db from another host on same LAN

[Baskar K]

Hi,
Though sounds silly, have you set the password

Thanks
baskar

On Wed, Aug 6, 2014 at 12:26 PM, Arun Khan knu...@gmail.com wrote:

 On Wed, Aug 6, 2014 at 12:16 PM, Balasubramaniam Natarajan
 bala150...@gmail.com wrote:
  On Wed, Aug 6, 2014 at 12:09 PM, Arun Khan knu...@gmail.com wrote:
  1. Could you try logging in locally by adding an appropriate grant
  statement ?
 
  2. Try using the FQDN for the grant statement on mysql
  server.
   GRANT ALL ON YourDB.* TO 'root'@
  'mysql_root_password';
   FLUSH PRIVILEGES;
 

 I have already done the following before trying to connect

 GRANT ALL PRIVILEGES ON *.* TO 'root'@'A' WITH GRANT OPTION;

 where A is the IP address and

 GRANT ALL PRIVILEGES ON *.* TO 'root'@'aws-appsrv' WITH GRANT OPTION;

 where aws-appsrv is resolved by /etc/hosts with the line.

 Aaws-appsrv

 -- Arun Khan

___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] MySQL - unable to connect to MySQL db from another host on same LAN

[Arun Khan]

On Wed, Aug 6, 2014 at 4:18 PM, Baskar K baskar...@gmail.com wrote:
 Hi,
 Though sounds silly, have you set the password

Yes.  I have also solved the OP.  see the other original thread.

-- Arun Khan
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] MySQL - unable to connect to MySQL db from another host on same LAN

[Arun Khan]

SOLVED.

On Wed, Aug 6, 2014 at 1:04 PM, Arun Khan knu...@gmail.com wrote:
 On Wed, Aug 6, 2014 at 12:38 PM, Arun Venkataswamy arun...@gmail.com wrote:

 Is the mysql server listening on all interfaces?
 I think by default it listens only on localhost or 127.0.0.1


 Yes, bind_address=0.0.0.0  (allow connection from any IP number).

 Another naive point, just to be sure : Have you opened up port 3306 in the
 firewall?

 AWS, has a pretty neat concept called Security Groups  wherein you
 can define inbount and outbound traffic.

 Checked that as well, inbound to 3306 is allowed.
  I can telnet aws-appsrv 3306 from a different node and get connection
 and prompt for password.

One of my Member of Technical Staff found the solution.
Ref. URL http://www.mysqltutorial.org/mysql-grant.aspx

Specifically the following commands in a 'mysql -u root -p' session

CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';
create database userdb;
GRANT ALL userdb.* TO 'username'@'hostname' WITH GRANT OPTION;
flush privileges;

-- Arun Khan
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] [MYSQL-QUERY] - missing numbers in the Column ?

[Raju s]

On Mon, Jul 22, 2013 at 7:32 PM, Sathia S sathia2...@gmail.com wrote:

 I think we cant do it in mysql. Because of string 'S'.
 But found some link if id is number
 http://stackoverflow.com/questions/1389605/sql-find-missing-ids-in-a-table

 But we can do it with some scripting language like ruby or python to solve
 the issue.

 Getting all ids and generate ids of array from (S001.. S00N) and compare
 those array. May be its not best solution. But we can achieve through some
 scripting language.


 On Mon, Jul 22, 2013 at 7:21 PM, Siva prabu gsivapr...@gmail.com wrote:

  My table:-
 
  +---+
   webno
  ---+
 
  |S001|
  |S002|
  |S003|
  |S005|
  |S006|
  |S009|
  |S010|
 
 
  How to find the missing numbers in the Column webno for the series...
 
 
  i need like
 
 
  **
  S004
  S008
 
  **
 
 
  **
 
  * regards, *
 
  *| Sivaprabu. G **|*
 


Like we can create a virtual table and compare,

select cast(concat('S',lpad(sno,3,'0')) as char(6)) as missing from (SELECT
@row := @row + 1 as sno FROM (select 0 union all select 1 union all select
3 union all select 4 union all select 5 union all select 6 union all select
6 union all select 7 union all select 8 union all select 9) t,(select 0
union all select 1 union all select 3 union all select 4 union all select 5
union all select 6 union all select 6 union all select 7 union all select 8
union all select 9) t2,(select 0 union all select 1 union all select 3
union all select 4 union all select 5 union all select 6 union all select 6
union all select 7 union all select 8 union all select 9) t3, (select 0
union all select 1 union all select 3 union all select 4 union all select 5
union all select 6 union all select 6 union all select 7 union all select 8
union all select 9) t4, (SELECT @row:=0) as b where @row1000) as a where
a.sno  not in (select distinct b.no from (select b.*,if(@mn=0,@mn:=b.no,@mn)
as min,(@mx:=b.no) as max from (select a.webno,substring(a.webno,2,4)+0 as
no from web as a) as b,(select @mn:=0,@mx:=0) as x order by no) as b) and
a.sno between @mn and @mx;

Any drawbacks in this?

Thanks
Raju
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] [MYSQL-QUERY] - missing numbers in the Column ?

[Sathia S]

I think we cant do it in mysql. Because of string 'S'.
But found some link if id is number
http://stackoverflow.com/questions/1389605/sql-find-missing-ids-in-a-table

But we can do it with some scripting language like ruby or python to solve
the issue.

Getting all ids and generate ids of array from (S001.. S00N) and compare
those array. May be its not best solution. But we can achieve through some
scripting language.


On Mon, Jul 22, 2013 at 7:21 PM, Siva prabu gsivapr...@gmail.com wrote:

 My table:-

 +---+
  webno
 ---+

 |S001|
 |S002|
 |S003|
 |S005|
 |S006|
 |S009|
 |S010|


 How to find the missing numbers in the Column webno for the series...


 i need like


 **
 S004
 S008

 **


 **

 * regards, *

 *| Sivaprabu. G **|*

 * |*ABOUT~ME http://about.me/gsivaprabu* **|*
  ** http://about.me/gsivaprabu
 ___
 ILUGC Mailing List:
 http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
 ILUGC Mailing List Guidelines:
 http://ilugc.in/mailinglist-guidelines




-- 
--
Regards
sathia

Here I share my experiments with open source.
http://www.sathia27.wordpress.com
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc
ILUGC Mailing List Guidelines:
http://ilugc.in/mailinglist-guidelines

Re: [Ilugc] MySql data vanishes when tomcat is restarted

[Natarajan V]

On Tue, Aug 28, 2012 at 6:08 PM, Raju s raju...@gmail.com wrote:
 We have found that MySql is going to autocommit=false mode and storing the
 data in buffer.
 This we checked thro' other connection. When we made con.commit then the
 data is stored in MySql
 We have not initiated any transaction or set autocommit=false. How MySql
 switches to that mode?


If I understand correctly, you loose data when you have simultaneous
users. If that is so, then, please check if you have any static
classes / singleton objects / Enum Singletoms? It might not be a MySQL
problem, but a Java coding issue.

--
Natarajan.
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] MySql data vanishes when tomcat is restarted

[Raju s]

 On Tue, Aug 28, 2012 at 6:08 PM, Raju s raju...@gmail.com wrote:
  On Sat, Jul 28, 2012 at 12:26 PM, Raju s raju...@gmail.com wrote:
 
  In our application the data is entered continuously and stored in MySql.
  Due to some reason if Tomcat is restarted the data entered previously
 say
  2 hrs before the restart
  vanished from MySql. Till this restart we are able to view or modify the
  data and the data is stored in MySql
 
  On Fri, Jul 27, 2012 at 9:39 PM, Balasubramaniam Natarajan 
  bala150...@gmail.com wrote:
 
  On Fri, Jul 27, 2012 at 9:33 PM, Raju s raju...@gmail.com wrote:
 
  
   We don't use transaction /rollback.
   The default is commit
   Tomcat or MySql log we could not find any error
  
  
 
  We have found that MySql is going to autocommit=false mode and storing
 the
  data in buffer.
  This we checked thro' other connection. When we made con.commit then the
  data is stored in MySql
  We have not initiated any transaction or set autocommit=false. How MySql
  switches to that mode?
  Any help please.
 
  Thanks
  Raju

On Tue, Aug 28, 2012 at 6:43 PM, Girish Venkatachalam 
girishvenkatacha...@gmail.com wrote:

 How about switching to postgres? Or sqlite?


We can think of this later,as we cannot tell him that to solve this problem
change the database.
 we have to show the client that we have solved the problem.

Raju


___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] MySql data vanishes when tomcat is restarted

[Raju s]

On Sat, Jul 28, 2012 at 12:26 PM, Raju s raju...@gmail.com wrote:

 In our application the data is entered continuously and stored in MySql.
 Due to some reason if Tomcat is restarted the data entered previously say
 2 hrs before the restart
 vanished from MySql. Till this restart we are able to view or modify the
 data and the data is stored in MySql

 On Fri, Jul 27, 2012 at 9:39 PM, Balasubramaniam Natarajan 
 bala150...@gmail.com wrote:

 On Fri, Jul 27, 2012 at 9:33 PM, Raju s raju...@gmail.com wrote:

 
  We don't use transaction /rollback.
  The default is commit
  Tomcat or MySql log we could not find any error
 
 

 We have found that MySql is going to autocommit=false mode and storing the
data in buffer.
This we checked thro' other connection. When we made con.commit then the
data is stored in MySql
We have not initiated any transaction or set autocommit=false. How MySql
switches to that mode?
Any help please.

Thanks
Raju
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] MySql data vanishes when tomcat is restarted

[Girish Venkatachalam]

How about switching to postgres? Or sqlite?

On Tue, Aug 28, 2012 at 6:08 PM, Raju s raju...@gmail.com wrote:
 On Sat, Jul 28, 2012 at 12:26 PM, Raju s raju...@gmail.com wrote:

 In our application the data is entered continuously and stored in MySql.
 Due to some reason if Tomcat is restarted the data entered previously say
 2 hrs before the restart
 vanished from MySql. Till this restart we are able to view or modify the
 data and the data is stored in MySql

 On Fri, Jul 27, 2012 at 9:39 PM, Balasubramaniam Natarajan 
 bala150...@gmail.com wrote:

 On Fri, Jul 27, 2012 at 9:33 PM, Raju s raju...@gmail.com wrote:

 
  We don't use transaction /rollback.
  The default is commit
  Tomcat or MySql log we could not find any error
 
 

 We have found that MySql is going to autocommit=false mode and storing the
 data in buffer.
 This we checked thro' other connection. When we made con.commit then the
 data is stored in MySql
 We have not initiated any transaction or set autocommit=false. How MySql
 switches to that mode?
 Any help please.

 Thanks
 Raju
 ___
 ILUGC Mailing List:
 http://www.ae.iitm.ac.in/mailman/listinfo/ilugc



-- 
Gayatri Hitech
http://gayatri-hitech.com
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] MySql data vanishes when tomcat is restarted

[Raju s]

On Fri, Jul 27, 2012 at 9:39 PM, Balasubramaniam Natarajan 
bala150...@gmail.com wrote:

 On Fri, Jul 27, 2012 at 9:33 PM, Raju s raju...@gmail.com wrote:

 
  We don't use transaction /rollback.
  The default is commit
  Tomcat or MySql log we could not find any error
 
 
 Are there any cronjob which is running ?

 Type w at the command prompt to see how many people are logged on to the
 system.

 --
 Regards,
 Balasubramaniam Natarajan


No cronjobs are running either in user or root
w command gives 4 people are logged in
1 root
3 staff

Thanks
Raju
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] MySql data vanishes when tomcat is restarted

[Balasubramaniam Natarajan]

On Sat, Jul 28, 2012 at 12:26 PM, Raju s raju...@gmail.com wrote:

 On Fri, Jul 27, 2012 at 9:39 PM, Balasubramaniam Natarajan 
 bala150...@gmail.com wrote:


 No cronjobs are running either in user or root
 w command gives 4 people are logged in
 1 root
 3 staff

 Are these four people authorized to be on the system ?

Try to increase the verbosity of the mysql server logging and try to
restart apache and look at the logs may be you can get a clue.

-- 
Regards,
Balasubramaniam Natarajan
www.etutorshop.com/moodle/
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] MySql data vanishes when tomcat is restarted

[Raju s]

On Sat, Jul 28, 2012 at 5:24 PM, Balasubramaniam Natarajan 
bala150...@gmail.com wrote:

 On Sat, Jul 28, 2012 at 12:26 PM, Raju s raju...@gmail.com wrote:

  On Fri, Jul 27, 2012 at 9:39 PM, Balasubramaniam Natarajan 
  bala150...@gmail.com wrote:
 
 
  No cronjobs are running either in user or root
  w command gives 4 people are logged in
  1 root
  3 staff
 
  Are these four people authorized to be on the system ?

 Try to increase the verbosity of the mysql server logging and try to
 restart apache and look at the logs may be you can get a clue.

 --
 Regards,
 Balasubramaniam Natarajan

Yes all these four people are authorized.
Further to our search, this happens only 2 or more people entering the data
if only one person is doing the data entry, data is not lost when tomcat is
restarted.

Thanks
Raju
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

[Ilugc] MySql data vanishes when tomcat is restarted

[Raju s]

Hi,

In our application the data is entered continuously and stored in MySql.
Due to some reason if Tomcat is restarted the data entered previously say 2
hrs before the restart
vanished from MySql. Till this restart we are able to view or modify the
data and the data is stored in MySql
as it can be viewed directly through Query browser.

Version
Java 1.5
Tomcat 5.5.7
MySql 5.0.75
OS  RHEL 6

What can be the reason?

Raju
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] MySql data vanishes when tomcat is restarted

[Rajagopal Swaminathan]

Greetings,

On Fri, Jul 27, 2012 at 8:45 PM, Raju s raju...@gmail.com wrote:
 Hi,

 Due to some reason if Tomcat is restarted the data entered previously say 2
 hrs before the restart
 vanished from MySql.
 What can be the reason?


Check your application whether it commits the data.

Give some more details as to what logs say for tomcat, mysql etc.


-- 
Regards,

Rajagopal
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] MySql data vanishes when tomcat is restarted

[Raju s]

On Fri, Jul 27, 2012 at 8:50 PM, Rajagopal Swaminathan 
raju.rajs...@gmail.com wrote:

 Greetings,

 On Fri, Jul 27, 2012 at 8:45 PM, Raju s raju...@gmail.com wrote:
  Hi,
 
  Due to some reason if Tomcat is restarted the data entered previously
 say 2
  hrs before the restart
  vanished from MySql.
  What can be the reason?
 

 Check your application whether it commits the data.

 Give some more details as to what logs say for tomcat, mysql etc.


 -

We don't use transaction /rollback.
The default is commit
Tomcat or MySql log we could not find any error

Raju
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] MySql data vanishes when tomcat is restarted

[Balasubramaniam Natarajan]

On Fri, Jul 27, 2012 at 9:33 PM, Raju s raju...@gmail.com wrote:


 We don't use transaction /rollback.
 The default is commit
 Tomcat or MySql log we could not find any error


Are there any cronjob which is running ?

Type w at the command prompt to see how many people are logged on to the
system.

-- 
Regards,
Balasubramaniam Natarajan
www.etutorshop.com/moodle/
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] MySql data vanishes when tomcat is restarted

[Rajagopal Swaminathan]

Greetings,

On Fri, Jul 27, 2012 at 9:33 PM, Raju s raju...@gmail.com wrote:
 We don't use transaction /rollback.
 The default is commit
 Tomcat or MySql log we could not find any error

Perhaps turning up the verbosity level of mysql may help?


-- 
Regards,

Rajagopal
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

[Ilugc] Mysql Database replication

[Ganesh Kumar]

Hi Guys,

I am new to mysql database replication process. I want to replicate
database to my vps server to local machine. (2-way sync)

\- Any DDL/DML operation made on vps server all changes replicate local database
\- In local database any DDL/DML operation done through local server
update to VPS server.

How to implement Guys, please give your valuable comments.

-Ganesh.

Did I learn something today? If not, I wasted it.
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] Mysql Database replication

[kenneth gonsalves]

On Wed, 2012-06-06 at 13:05 +0530, Ganesh Kumar wrote:
 How to implement Guys, please give your valuable comments.

tell us what you have tried and what problems you faced.
-- 
regards
Kenneth Gonsalves

___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] Mysql replication Vs mysql cluster management

[Murugan, Naresh Kumar]

Hi,

Replication:

Replication enables data from one MySQL database server (the master) to be 
replicated to one or more MySQL database servers (the slaves)
That means datas are mirrored in another instances by several time difference.


Clustering :

It is general concept of load balancing,traffic routing . In database if the 
situation of high query count ( as select ,update,delete ) database will not be 
accessible. Due to the reason of high availability and fault tolearance having 
cluster concept in MYSQL.

Regards,
M.Naresh Kumar
-Original Message-
From: ilugc-boun...@ae.iitm.ac.in [mailto:ilugc-boun...@ae.iitm.ac.in] On 
Behalf Of Karthikeyan Venkatraman
Sent: Monday, May 07, 2012 12:45 PM
To: ILUG-C
Subject: [Ilugc] Mysql replication Vs mysql cluster management

Hi,


I have a doubt in mysql options.


Difference between mysql replication (option available in phpmyadmin) VS
mysql cluster management - ndb version 7.2.5


Pl clarify...

Thanks,
V. Karthick


http://vkarthickeyan.wordpress.com
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc


Think green - keep it on the screen.

This e-mail and any attachment is for authorised use by the intended 
recipient(s) only. It may contain proprietary material, confidential 
information and/or be subject to legal privilege. It should not be copied, 
disclosed to, retained or used by, any other party. If you are not an intended 
recipient then please promptly delete this e-mail and any attachment and all 
copies and inform the sender. Thank you.


___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] MySQL database sync

[ashwin kesavan]

On 29 October 2011 11:06, Arun Venkataswamy arun...@gmail.com wrote:
 Thanks all for pitching in to help!
 I am not in a position to change the database software from MySQL.

 I guess the only way to do it is programmatically record all the changes at
 the master side (all SQL update/insert/delete commands) on a per branch
 basis and send that information for playback at the branch databases.

If you plan to implement the above. I think the following information
would be helpful. Mysql maintains a binlog file ( as specified in
my.cnf configuration file) which has a log of all mysql db changes
made. So having a application to reading it and updating the db would
be far easier than having to write a application to record all db
changes and relaying to the master db.

--ashwin
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] MySQL database sync

[prasannatsmkumar]

On Sat, Oct 29, 2011 at 8:59 PM, ashwin kesavan ashwin@gmail.comwrote:

 On 29 October 2011 11:06, Arun Venkataswamy arun...@gmail.com wrote:
  Thanks all for pitching in to help!
  I am not in a position to change the database software from MySQL.
 
  I guess the only way to do it is programmatically record all the changes
 at
  the master side (all SQL update/insert/delete commands) on a per branch
  basis and send that information for playback at the branch databases.

 If you plan to implement the above. I think the following information
 would be helpful. Mysql maintains a binlog file ( as specified in
 my.cnf configuration file) which has a log of all mysql db changes
 made. So having a application to reading it and updating the db would
 be far easier than having to write a application to record all db
 changes and relaying to the master db.

 --ashwin
 ___
 ILUGC Mailing List:
 http://www.ae.iitm.ac.in/mailman/listinfo/ilugc


You can use inotify to detect changes. Whenever there is a change in a file
an event will be generated by the system. Your application can register for
that event and do necessary things, in other time the application can sit
idle (wait for the event without using any system resources).
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

[Ilugc] MySQL database sync

[Arun Venkataswamy]

Hi,

This is my scenario:
* A master database in the internet with data from multiple office branches.
* Each branch office has sporadic access to the internet. Hence sections of
the database need to be stored offline in the branch.
* Every evening, the head office should be a able to view/modify/delete data
from the branches (only operate on the master database) (changes from branch
uploaded in evening).
* Next day morning the branch office should be able to see updated database.
(download the modified contents overnight or early morning)

Both the master database and the branch run on Linux.

To my knowledge, I understand that MySQL replication is a one-way street.
One master many slave system and slaves only sync themselves to the master
and the master does not sync itself to the slaves. Am i wrong?

Any simple solution for this?

Regards,
Arun
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] MySQL database sync

[Joe Steeve]

On Friday 28 October 2011 12:18 PM, Arun Venkataswamy wrote:
 * A master database in the internet with data from multiple office branches.
 * Each branch office has sporadic access to the internet. Hence sections of
 the database need to be stored offline in the branch.
 * Every evening, the head office should be a able to view/modify/delete data
 from the branches (only operate on the master database) (changes from branch
 uploaded in evening).
 * Next day morning the branch office should be able to see updated database.
 (download the modified contents overnight or early morning)

If you are not bound to MySQL and RDBMS, do have a look at CouchDB.

-- 
.o. I'm a Free man. I use Free Software.
..o
ooo http://www.joesteeve.org/



signature.asc
Description: OpenPGP digital signature
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] MySQL database sync

[Arun Venkataswamy]

On Fri, Oct 28, 2011 at 12:23 PM, Joe Steeve joe_ste...@gmx.net wrote:


 If you are not bound to MySQL and RDBMS, do have a look at CouchDB.


Pretty much bound to MySQL for now.
Even considering another RDBMS is not possible!

Regards,
Arun
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] MySQL database sync

[santhosh kumar]

Is there anything stopping you from running a cron job that updates the
master, with the records from the slave at the given time of the day when
you have connectivity?

On Fri, Oct 28, 2011 at 12:27 PM, Arun Venkataswamy arun...@gmail.comwrote:

 On Fri, Oct 28, 2011 at 12:23 PM, Joe Steeve joe_ste...@gmx.net wrote:

 
  If you are not bound to MySQL and RDBMS, do have a look at CouchDB.
 
 
 Pretty much bound to MySQL for now.
 Even considering another RDBMS is not possible!

 Regards,
 Arun
 ___
 ILUGC Mailing List:
 http://www.ae.iitm.ac.in/mailman/listinfo/ilugc




-- 
Regards

Santhosh
*github: *https://github.com/santhotech
*twitter | Facebook :* @santhotech
*Linkedin: *http://www.linkedin.com/pub/santhosh-kumar/27/bb/5a1
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] MySQL database sync

[Guruprasad]

Hi,

On Friday 28 October 2011 01:00:35 PM IST, santhosh kumar wrote:
 Is there anything stopping you from running a cron job that updates the
 master, with the records from the slave at the given time of the day when
 you have connectivity?

Is there anything that is stopping you from reading the mailing list 
guidelines (http://www.ilugc.in/content/mailinglist-guidelines/) and 
avoid top posting? Interleaved, trimmed posting style is the 
recommended one. So *do not top post*.

Thanks  Regards,
Guruprasad
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] MySQL database sync

[Arun Venkataswamy]

On Fri, Oct 28, 2011 at 1:00 PM, santhosh kumar santh...@gmail.com wrote:

 Is there anything stopping you from running a cron job that updates the
 master, with the records from the slave at the given time of the day when
 you have connectivity?


This solves the update on the master side. With binlogs option in MySQL this
is very simple. It beautifully stores all the operations done and we can
replay it on the master. The problem is mainly on the reverse side as I dont
want to send mega bytes of unwanted updates on the non related branches to
all brances.


Regards,
Arun
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] MySQL database sync

[Rajagopal Swaminathan]

Greetings,

On Fri, Oct 28, 2011 at 1:13 PM, Arun Venkataswamy arun...@gmail.com wrote:
 On Fri, Oct 28, 2011 at 1:00 PM, santhosh kumar santh...@gmail.com wrote:

 This solves the update on the master side. With binlogs option in MySQL this
 is very simple. It beautifully stores all the operations done and we can
 replay it on the master. The problem is mainly on the reverse side as I dont
 want to send mega bytes of unwanted updates on the non related branches to
 all brances.


Now we are talking about distributed database with two phase commits etc.

Oracle of course has this feature. At a Price of course.

I am not sure MySQL does it. Checkout PostgreSQL.

-- 
Regards,

Rajagopal
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] MySQL database sync

[Joe Steeve]

On Friday 28 October 2011 01:13 PM, Arun Venkataswamy wrote:
 This solves the update on the master side. With binlogs option in MySQL this
 is very simple. It beautifully stores all the operations done and we can
 replay it on the master. The problem is mainly on the reverse side as I dont
 want to send mega bytes of unwanted updates on the non related branches to
 all brances.

IMHO, this is not something that should be solved by the database
software. This is something that the application should solve.

The application should be architected in such a way that, it syncs up
with the head-quarters when requested.

-- 
.o. I'm a Free man. I use Free Software.
..o
ooo http://www.joesteeve.org/




signature.asc
Description: OpenPGP digital signature
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] MySQL database sync

[Arun Venkataswamy]

Thanks all for pitching in to help!
I am not in a position to change the database software from MySQL.

I guess the only way to do it is programmatically record all the changes at
the master side (all SQL update/insert/delete commands) on a per branch
basis and send that information for playback at the branch databases.

Regards,
Arun
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

[Ilugc] MySQL insert Unicode characters

[JAGANADH G]

Hi,

I was trying to insert some Unicode text into MySQL DB. The text is
extracted from Twitter where the tweets belongs to Anay language in the
world.
But when I inserted the data all the non english text were converted to
???.
I tries setuung collocation as utf8_common_cli , SET CHARACTER SET utf8; etc
.. still I am failing to repserve the original data.

Any clue how to do it efffectively
-- 
**
JAGANADH G
http://jaganadhg.freeflux.net/blog
*ILUGCBE*
http://ilugcbe.techstud.org
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

[Ilugc] Mysql database backup

[Vijay Kumar]

Dear all,

I want to take backup mysql database through cron, I have executed this
command

/usr/bin/mysqldump -u root -p(x) king | gzip  /backups/king_`date
+%y_%m_%d`.gz

It shows this error -

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using
password: YES) when trying to connect

But I have executed the above command with out any password it done
successfully, the command like this -

/usr/bin/mysqldump -u root -p king | gzip  /backups/king_`date
+%y_%m_%d`.gz

It asks the password and it executed successfully.


 Any one help me. Thanks in advance.


-- 

With regards,

G. Vijayakumar,
+91 8973417398
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] Mysql database backup

[krish]

On Thu, Jul 7, 2011 at 6:51 PM, Vijay Kumar vijayakumarn...@gmail.comwrote:

 Dear all,

 I want to take backup mysql database through cron, I have executed this
 command

 /usr/bin/mysqldump -u root -p(x) king | gzip  /backups/king_`date
 +%y_%m_%d`.gz

 It shows this error -

 mysqldump: Got error: 1045: Access denied for user 'root'@'localhost'
 (using
 password: YES) when trying to connect



Try

/usr/bin/mysqldump -u ${MYSQL_USER} -p${MYSQL_PASS} --result-file
${DUMP_DIR}/${db}.sql --databases ${db}

Dont forget to substitute variable names in the above command ^

-- 
krish
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

[Ilugc] MYSqL query doubt

[JAGANADH G]

Hi All

I have database having 1 Lakh URLS and its content .
I was trying to execute the following query and after 4 hours I got an error
too
I am pasting the query and error below. Is there anything wrong with the
query

select count(*) from `WCM-LN`.`reviews_index` where url in(Select link from
urls)
Error Code: 2013. Lost connection to MySQL server during query

-- 
**
JAGANADH G
http://jaganadhg.freeflux.net/blog
*ILUGCBE*
http://ilugcbe.techstud.org
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] MYSqL query doubt

[Shrinivasan T]

 select count(*) from `WCM-LN`.`reviews_index` where url in(Select link from
 urls)
 Error Code: 2013. Lost connection to MySQL server during query


Check the error log for mysql.

As the query takes a long time to run, check for the options for
longer timeout value in mysql.

-- 
Regards,
T.Shrinivasan


My Life with GNU/Linux : http://goinggnu.wordpress.com
Free/Open Source Jobs : http://fossjobs.in

Get CollabNet Subversion Edge :     http://www.collab.net/svnedge
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] MYSqL query doubt

[PV Sundarram]

On Wed, Jun 15, 2011 at 6:13 PM, JAGANADH G jagana...@gmail.com wrote:

 Hi All

 I have database having 1 Lakh URLS and its content .
 I was trying to execute the following query and after 4 hours I got an
 error
 too
 I am pasting the query and error below. Is there anything wrong with the
 query

 select count(*) from `WCM-LN`.`reviews_index` where url in(Select link from
 urls)
 Error Code: 2013. Lost connection to MySQL server during query


MySql's subquery performance is not that great, esp. for large tables.

You could try running the query by joining the two tables.

Also an index on the fields you are joining will also help.

--
@pvsundarram http://twitter.com/pvsundarram
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] MYSqL query doubt

[Rajesh kumar]

 I have database having 1 Lakh URLS and its content .
 I was trying to execute the following query and after 4 hours I got an
 error
 too
 I am pasting the query and error below. Is there anything wrong with the
 query

 select count(*) from `WCM-LN`.`reviews_index` where url in(Select link from
 urls)
 Error Code: 2013. Lost connection to MySQL server during query


Does this query works for lesser no of Records?

Please avoid using subquery, it will take lot of your CPU and MEM hence
leads to connection loss. Use joins instead, or try temporary table( My
Recommendation better than join).

Index the table for better performance.

--
regards

Rajesh Kumar R.K
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

[Ilugc] Mysql query issue

[JAGANADH G]

Hi All

I have a MySQL table and it contains around 20,000,00 records.
id(promary key and autogenerate),review(text),hash(int)) is the table
structure

There is a column called hash,which has the hash value (generated
programatcally)
I am pretty sure that there is duplicate records in my table.Thats y i
generated a hash.
Now i would like to dedupe the table using hash.
This is the query i used for the said purpose

Positive : table name
id : auto generated id
hash : hash value
review : reviews

delete Positive
from Positive,
(
select MIN(id) minIdent, hash s
from Positive m
group by hash
having count(1)  1
) as derived
where Positive.hash= derived.s
and id  minIdent

The above dedupe query is working.I checked it in a tabel which contains
10,000 records.All the duplicate hash values are removed.
But my problem is while trying the same query in large table (20,000,00),it
takes too long.
On a test run the query runs 24 hours and not completed


Is there anything which is wrong. Because I am not that much expert in DB

-- 
**
JAGANADH G
http://jaganadhg.freeflux.net/blog
*ILUGCBE*
http://ilugcbe.techstud.org
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] Mysql query issue

[Kenneth Gonsalves]

On Fri, 2011-04-01 at 16:04 +0530, JAGANADH G wrote:
 The above dedupe query is working.I checked it in a tabel which
 contains
 10,000 records.All the duplicate hash values are removed.
 But my problem is while trying the same query in large table
 (20,000,00),it
 takes too long.
 On a test run the query runs 24 hours and not completed 

this is a one-off query to clean the database. There is no point trying
to optimise it (IMHO). I had to do the same with a pg database, with a
few thousand records and it took an hour or two (old machine).
-- 
regards
KG
http://lawgon.livejournal.com
Coimbatore LUG rox
http://ilugcbe.techstud.org/

___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] Mysql query issue

[Raja Subramanian]

On Fri, Apr 1, 2011 at 4:04 PM, JAGANADH G jagana...@gmail.com wrote:
 I have a MySQL table and it contains around 20,000,00 records.

Your comas are misleading, how many records are you having?
20 million or 20 lakh records?


 id(promary key and autogenerate),review(text),hash(int)) is the table
 structure
 There is a column called hash,which has the hash value (generated
 programatcally)
 I am pretty sure that there is duplicate records in my table.Thats y i
 generated a hash.
 Now i would like to dedupe the table using hash.

To retain only the first duplicate record and delete all later ones, try this:

ALTER IGNORE TABLE your_table_name ADD PRIMARY KEY (hash);

This will create a primary key using your hash field, retain only the
first unique hash and delete everything else.

If this is too slow, the alternate method is to copy unique records
into a new table and delete the old table.


To view list of duplicates, run this query:

SELECT hash, COUNT(hash) AS duplicate_count FROM \
your_table_name GROUP BY hash HAVING (COUNT(hash)  1);

Ensure you have created an index for the hash field before running
above query, this will help avoiding table scans and give you fast
performance.


Needless to say, backup your tables before bulk updating.

- Raja
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] mysql db to db query

[Chandrashekar Babu]

On 12/02/11 3:42 PM, Raju s wrote:
 This also works.
 create the database in M2
 login to M1

   mysqldump -a *dbname *-u *user *-p*password* | ssh
 *user*@*xxx.xxx.xxx.xxx*mysql -u
 *user* -p*password* *dbname*

You don't need ssh for this (unless of course both
hosts are located in an insecure network. You can
rather do this:

On M1 at MySQL prompt:
 GRANT SELECT ON employee_details
   FROM 'someuser'@'M2'
   IDENTIFIED BY 'somepassword';

On M2 at the Shell prompt:
  mysqldump -h M1 -u someuser -psomepassword \
   --databases employee_details | mysql \
   -u someuser -psomepassword

But as Raja mentioned, if there's a connection
failure, it might lead to data corruption even
in this case.

Better option than the above methods is to configure
M1 as a master and M2 as a slave and setup
replication amongst them.

Cheers,
Chandrashekar.

-- 
http://www.chandrashekar.info/
http://www.slashprog.com/
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] mysql db to db query

[Raju s]

On Thu, Feb 10, 2011 at 10:42 PM, Chandrashekar Babu l...@chandrashekar.info
 wrote:

 On 10/02/11 4:33 PM, JAGANADH G wrote:
  Is it possible to quary and fetch data in one mysql db and store it in a
  second on .
 
  I have machine M1 and M2 with mysql configured . There is a DB in M1
 namely
  employee_details . Now I have to fectch all the recods in M1
  employee_details from and have to store M2 mysql database via mysql
 query.

 You might want to try using mysqldump as mentioned by Sathia.


This also works.
create the database in M2
login to M1

 mysqldump -a *dbname *-u *user *-p*password* | ssh
*user*@*xxx.xxx.xxx.xxx*mysql -u
*user* -p*password* *dbname*

Raju
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] mysql db to db query

[Raja Subramanian]

On Feb 12, 2011 3:42 PM, Raju s raju...@gmail.com wrote:

  mysqldump -a *dbname *-u *user *-p*password* | ssh
 *user*@*xxx.xxx.xxx.xxx*mysql -u
 *user* -p*password* *dbname*


If the SSH session drops in-between, you will be left with a mess on your
hands!

While bulk updating a production DB, it's good practice to do things in
small and verifyable steps.

Each step should also be minimized to ensure that it will not fail, and if
it does fail it should be quick and easy to revert.

Best option is to dump to a file, verify records, scp/rsync, and import into
the other DB. Depending on the number of records, indexes may need to be
temporarily disabled so the data is imported faster.

- Raja
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] mysql db to db query

[Karanbir Singh]

On 02/10/2011 03:50 PM, steve wrote:
 Google for SQL join.
 [...snipped rambling about SQL...]
 Could you please elaborate on just exactly how does one use SQL join to ...

I was wondering that as well...

 fectch all the recods in M1 ... and ...store [in] M2 ... where M1 and M2 are
 clearly specified as 2 different machines with different instances of mysql ?

to be totally pedantic, one could use mysql-proxy to achieve something 
like this. But some assembly required, batteries not included, might 
need adult supervision :)

- KB
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] mysql db to db query

[Sathia S]

Hi

Is it possible to quary and fetch data in one mysql db and store it in a
 second on .

 ya its possible


 I have machine M1 and M2 with mysql configured . There is a DB in M1 namely
 employee_details . Now I have to fectch all the recods in M1
 employee_details from and have to store M2 mysql database via mysql query.
 Is it possible or is it my ignorance about db concepts .


see this link -

http://dev.mysql.com/doc/refman/5.0/en/copying-databases.html


Regards

sathia
http://www.sathia27.wordpress.com
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] mysql db to db query

[Girish Venkatachalam]

On Thu, Feb 10, 2011 at 4:33 PM, JAGANADH G jagana...@gmail.com wrote:
 Hi

 Is it possible to quary and fetch data in one mysql db and store it in a
 second on .

 I have machine M1 and M2 with mysql configured . There is a DB in M1 namely
 employee_details . Now I have to fectch all the recods in M1
 employee_details from and have to store M2 mysql database via mysql query.
 Is it possible or is it my ignorance about db concepts .


Google for SQL join.

SQL is a worthwhile programming language to learn. There are some interesting
 threads on programming languages in LUG now but SQL does not fit into
any of those moulds.

It is a DB querying language that is very powerful and easy too.

-Girish
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] mysql db to db query

[steve]

On 02/10/2011 08:59 PM, Girish Venkatachalam wrote:
 On Thu, Feb 10, 2011 at 4:33 PM, JAGANADH Gjagana...@gmail.com  wrote:
  Hi

  Is it possible to quary and fetch data in one mysql db and store it in a
  second on .

  I have machine M1 and M2 with mysql configured . There is a DB in M1 namely
  employee_details . Now I have to fectch all the recods in M1
  employee_details from and have to store M2 mysql database via mysql query.
  Is it possible or is it my ignorance about db concepts .


 Google for SQL join.
 [...snipped rambling about SQL...]

Could you please elaborate on just exactly how does one use SQL join to ... 
fectch all the recods in M1 ... and ...store [in] M2 ... where M1 and M2 are 
clearly specified as 2 different machines with different instances of mysql ?

I'd really be interested in this !

cheers,
- steve

-- 
random spiel: http://lonetwin.net/
what i'm stumbling into: http://lonetwin.stumbleupon.com/
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] mysql db to db query

[Chandrashekar Babu]

On 10/02/11 4:33 PM, JAGANADH G wrote:
 Is it possible to quary and fetch data in one mysql db and store it in a
 second on .

 I have machine M1 and M2 with mysql configured . There is a DB in M1 namely
 employee_details . Now I have to fectch all the recods in M1
 employee_details from and have to store M2 mysql database via mysql query.

You might want to try using mysqldump as mentioned by Sathia.

If you want to replicate just one table from another machine
(M1), you can first try creating a federated table on the
target machine (M2) which has the same table structure as the
table in M1.

On target machine (M2) you might want to try something like
below:
--
CREATE TABLE `fed_emp_details`(
   `id`SERIAL PRIMARY KEY,
   `name`  VARCHAR(32) NOT NULL,
   `dept`  VARCHAR(32) NOT NULL
) ENGINE=FEDERATED
   CONNECTION='mysql://someuser@M1:3306/mydb/employee_details';
--
You might want to modify the table structure and connection
details to suit your requirement.

Now running queries on the federated table on M2 will actually
show/affect the contents from table that is stored on M1.

Now, you can use a query similar to the one below on the
target machine (M2) to actually replicate the table:
--
CREATE TABLE `employee_details` ENGINE=MyISAM AS
SELECT * FROM `fed_emp_details`;
--
This method could be used only if you do not have the luxury
of running mysqldump on your shell prompt. Of course, this
technique is non-optimal, and might not work if your source
table is not using MyISAM storage engine. Some deployments
of MySQL server might also have FEDERATED engine disabled.

Nevertheless, it is worth noting that there is a possibility.

To know more about federated table usage, kindly lookup
the URL: http://dev.mysql.com/doc/refman/5.0/en/federated-use.html

Cheers,
Chandrashekar.

-- 
http://www.chandrashekar.info/
http://www.slashprog.com/
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] mysql db to db query

[Chandrashekar Babu]

On 10/02/11 4:33 PM, JAGANADH G wrote:
 Is it possible to quary and fetch data in one mysql db and store it in a
 second one .

If you can write a script in PHP/Perl/Python/Ruby or any other
language of your choice, you can open two database connections
- one to the source host and one to the destination host:

1. Execute the query 'SHOW CREATE TABLE `employee_details`'
on the source host to get the table structure.

2. Execute the resultset column (which should be a valid
CREATE TABLE statement) as a query on destination host.

3. Now fetch rows from the table on the source host and
insert the result-set as rows in the destination host.

Hope this helps.

Cheers,
Chandrashekar.

-- 
http://www.chandrashekar.info/
http://www.slashprog.com/
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

[Ilugc] Mysql

[vinoth k]

Hello Friends,

 This My stupid to do... But i want to learn some Mysql
query . here i posted one simple query... If It`s useful learn it




Learn Some php *MYSQL Database

To select particular column in table

use following command


SELECT COLUMN NAME  FROM  TABLE NAME


EXAMPLE


SELECT FIRSTNAME FROM EMPLOYEE
*
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] Mysql

[kish]

On Tue, Sep 7, 2010 at 6:58 PM, vinoth k vinothk...@gmail.com wrote:
 Hello Friends,

                     This My stupid to do... But i want to learn some Mysql
 query . here i posted one simple query... If It`s useful learn it


Dude,
What are you trying to do?

If you are trying to educate people on the list you could simply point to a
better written documentation like

http://dev.mysql.com/doc/refman/5.0/en/select.html

or write a simple to understand step by step explanation instead of
dumping incoherent
sentences.

Your efforts will be much admired(I hope).


--Kish
---
Esse Quam Videri
krisk.wordpress.com
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] Mysql

[Varrun Ramani]

On 7 September 2010 18:58, vinoth k vinothk...@gmail.com wrote:
 Hello Friends,
                     This My stupid to do... But i want to learn some Mysql
 query . here i posted one simple query... If It`s useful learn it
 

You just made my day :-)

-- 
Varrun Ramani
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

[Ilugc] mysql optimization

[Deepan Chakravarthy]

Hi All,

I need some help in creating the best possible table schema for the
following condition.

There is a competition where people are allowed to signup in groups.
Each group has a groupid, each user has a uid, each group has  group
specific data like group_question_id etc. A single user can be part of
multiple groups. Each user can answer the group question with
group_answer_uid. The number of group_answer_uid (s) for user is equal
to number of groupid is he is part of.

Data

1)groupid
2)uid
3)group_question_id (specific to each group)
4)group_answer_uid (specific to each group for each user)

Following queries have to be made.

1)list all groups the user is part of
2)list all users in a groupid
3)list all group_answer_uids for a given group


What is the best schema possible for this?


Regards
Deepan

+91 9945702482
http://www.hashcube.com
http://twitter.com/codeshepherd
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] mysql optimization

[Venkatraman S]

On Thu, Jul 15, 2010 at 9:30 PM, Deepan Chakravarthy codesheph...@gmail.com
 wrote:


 I need some help in creating the best possible table schema for the
 following condition.


My Try:
 #Users - username,userid
 #Groups - groupname,groupid
 #Usergroups - userid,groupid
 #GroupQs  - groupid, groupQid,groupQQuestion
 #UserAnss   - userid, groupQid, userAnswer

Where Users,Groups,Usergroups,GroupQs ,UserAnss are the tables and the comma
separated list in front of the table name is the columns.

As for the queries, if you are using django-orm(or any other ORM) then it
should be pretty easy.

-V-
http://twitter.com/venkasub
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] mysql optimization

[Deepan Chakravarthy]

 | Each group has a groupid, each user has a uid, each group has  group
 | specific data like group_question_id etc.
 \--

 Does a group have many questions?

A group can have only one question.


 ---
 | Each user can answer the group question with
 | group_answer_uid. The number of group_answer_uid (s) for user is equal
 | to number of groupid is he is part of.
 \--

 Can you rephrase the above? Do you mean that if a user P belongs to
 groups A, B, then P can answer one question each in groups A and B?


You are correct. If user P belongs to group A,B, then P can answer
questions in both group A and B. (remember there is only one question
per group)
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] MYSQL General logs

[sri vats]

HI,

Thanks for your help.Its working.



On 12 April 2010 11:58, Chandrashekar Babu l...@chandrashekar.info wrote:

 Hi,

  I have installed mysql 5.0.83 in my ubuntu 9.10.I want to setup general
 log
  file but i don't know how to do it.

 Add the following line:
   log=queries.log
 in the [mysqld] section of my.cnf file and restart mysqld. the logs should
 be
 stored under the mysql data directory by default.
 For more information, lookup the following URL:
 http://dev.mysql.com/doc/refman/5.0/en/query-log.html

 Cheers,
 Chandrashekar.

 --
 Chandrashekar Babu.,
 http://www.chandrashekar.info/
 +91-9884386075, +91-9543019934,
 +91-44-32216664
 ___
 ILUGC Mailing List:
 http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

[Ilugc] MYSQL General logs

[sri vats]

Hi,

I have installed mysql 5.0.83 in my ubuntu 9.10.I want to setup general log
file but i don't know how to do it.Also I've  searched in Google and din't
get proper result.


-
Srivats.K.A
http://nothingtopost.wordpress.com
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] MYSQL log file

[Mehul Ved]

On Sat, Mar 27, 2010 at 11:16 AM, sri vats blackcap...@gmail.com wrote:
 HI,

 I installed xampp 1.7.3a in ubuntu karmic.The log files of mysql,apache and
 php will be saved in which directory?

If lampp is installed in /opt/lampp then check /opt/lampp/logs
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

[Ilugc] MYSQL log file

[sri vats]

HI,

I installed xampp 1.7.3a in ubuntu karmic.The log files of mysql,apache and
php will be saved in which directory?

Srivats.K.A
http://nothingtopost.wordpress.com
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] mysql : not working in Terminal

[Salvadesswaran P.S.]

On Fri, Mar 5, 2010 at 1:27 PM, selvamani sampath
selva_infob...@yahoo.co.in wrote:
 I installed mysql-admin, mysql-server, and mysql-client5.1. But I got the 
 error message while trying mysql in terminal. The error is :

 selvam...@selvamani-laptop:~$ mysql
 ERROR 1045 (28000): Access denied for user 'selvamani'@'localhost' (using 
 password: NO)
 selvam...@selvamani-laptop:~$

 Could any one help me to resolve this problem.

By default MySQL creates only the root account with no password. Use
mysql -u root. Before that, create a root password using mysqladmin
-u root password password_goes_here

Then use mysql -u root -p  to access the service. It is always
better to set a strong root password, even if it is not a production
system.

-- 


Salvadesswaran Srinivasan

http://www.twitter.com/salva_eswar
http://queasyquagmire.wordpress.com
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] mysql : not working in Terminal

[aishwarya .]

On Fri, Mar 5, 2010 at 1:27 PM, selvamani sampath 
selva_infob...@yahoo.co.in wrote:

 I installed mysql-admin, mysql-server, and mysql-client5.1. But I got the
 error message while trying mysql in terminal. The error is :

 selvam...@selvamani-laptop:~$ mysql
 ERROR 1045 (28000): Access denied for user 'selvamani'@'localhost' (using
 password: NO)
 selvam...@selvamani-laptop:~$

 Could any one help me to resolve this problem.



Try this
$mysql -u root -p
and then enter your password.

-- 
Thanks,
Aishwarya.M.B
http://aishwarya-stillsearchingwhoiam.blogspot.com/
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

[Ilugc] mysql query optimization

[Deepan Chakravarthy]

I am trying to figure out the most optimal mysql query.

Query 1:  SELECT  cid, dl FROMchal WHERE   cid IN ( SELECT  cid
FROMc_users WHERE   uid = 636587 );
Query 2:  SELECT  chal.cid AS cid, chal.dl  AS dl FROMchal,
c_users WHERE   uid = 808 ANDchal.cid = c_users.cid;

cid is primary key in chal cid and uid are indexed in c_users, cid is
not unique;
Which of the above query is better?

Explain says the following
Query 1 uses two types of index namely ALL and index_subquery
Query 2 users two types of index namely ALL and ref
I wonder why both queries say ALL as type of index though cid is
primary key in table chal.

I have posted the same at
http://stackoverflow.com/questions/2356714/mysql-query-optimisation

Regards
Deepan

+91 9945702482
http://www.hashcube.com
http://twitter.com/codeshepherd
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] mysql query optimization

[Raman.P]

--- On Mon, 1/3/10, Deepan Chakravarthy codesheph...@gmail.com wrote:

 I am trying to figure out the most
 optimal mysql query.
 
 Query 1:  SELECT  cid, dl FROM    chal
 WHERE   cid IN ( SELECT  cid
 FROM    c_users WHERE   uid =
 636587 );
 Query 2:  SELECT  chal.cid AS cid, chal.dl 
 AS dl FROM    chal,
 c_users WHERE   uid = 808 AND   
 chal.cid = c_users.cid;
 
 cid is primary key in chal cid and uid are indexed in
 c_users, cid is
 not unique;
 Which of the above query is better?
 
 Explain says the following
 Query 1 uses two types of index namely ALL and
 index_subquery
 Query 2 users two types of index namely ALL and ref
 I wonder why both queries say ALL as type of index though
 cid is
 primary key in table chal.
Based on my experience, observations are
a.query analyser decides based on statistics of the data distribution.
b.An IN+subquery means in-determinate number as opposed to WHERE. So it may be 
prudent to do a table  scan than index lookup. That may explain why it uses ALL.
c.Join on two large tables even on indexed field is not efficient. If such 
queries can be brought to IN+subquery I feel they will perform better.

If chal table is large and c_users small then query 1 should be efficient. If 
both are large there may not be any difference.

Raman.P
blog:http://ramanchennai.wordpress.com/




  The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. 
http://in.yahoo.com/
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc

Re: [Ilugc] mysql query optimization

[Deepan Chakravarthy]

On Tue, Mar 2, 2010 at 10:25 AM, Raman.P raam...@yahoo.co.in wrote:

 --- On Mon, 1/3/10, Deepan Chakravarthy codesheph...@gmail.com wrote:

 I am trying to figure out the most
 optimal mysql query.

 Query 1:  SELECT  cid, dl FROM    chal
 WHERE   cid IN ( SELECT  cid
 FROM    c_users WHERE   uid =
 636587 );
 Query 2:  SELECT  chal.cid AS cid, chal.dl
 AS dl FROM    chal,
 c_users WHERE   uid = 808 AND
 chal.cid = c_users.cid;

 cid is primary key in chal cid and uid are indexed in
 c_users, cid is
 not unique;
 Which of the above query is better?

 Explain says the following
 Query 1 uses two types of index namely ALL and
 index_subquery
 Query 2 users two types of index namely ALL and ref
 I wonder why both queries say ALL as type of index though
 cid is
 primary key in table chal.
 Based on my experience, observations are
 a.query analyser decides based on statistics of the data distribution.
 b.An IN+subquery means in-determinate number as opposed to WHERE. So it may 
 be prudent to do a table  scan than index lookup. That may explain why it 
 uses ALL.
 c.Join on two large tables even on indexed field is not efficient. If such 
 queries can be brought to IN+subquery I feel they will perform better.

 If chal table is large and c_users small then query 1 should be efficient. If 
 both are large there may not be any difference.

 Raman.P
 blog:http://ramanchennai.wordpress.com/


Hi Raman,

Thanks for explaining. Isn't a join supposed to be always better than
IN+subquery?
___
ILUGC Mailing List:
http://www.ae.iitm.ac.in/mailman/listinfo/ilugc