Wednesday, March 30, 2011

MySQL root user

MySQL root user is meant for database administrators for administration task. Many times, I found it's being used as replication user and normal user for making connections to mysql database.

One of my clients did the same thing and later it was fixed.


110329 12:01:13 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.003726' at position 850699172, relay log './poxxxx-relay-bin.000821' position: 11845356
110329 12:01:13 [Note] Slave I/O thread: connected to master 'root@xxx.xx.xx.xxx:3306',  replication started in log 'mysql-bin.003726' at position 850699172
It can be problematic in many scenario and mysql server restart can be required. It leads to below errors and don't allow connections to mysql database. 
Too many connections
Posted by at

2 comments:

  1. Shlomi NoachMarch 31, 2011 at 10:11 PM

    Some may not be aware, but there's nothing special about he 'root' user. Unlike unix 'root', in MySQL there doesn;t have to be such an account. It's just a convention.

    In fact, many delete the 'root' user and create an 'admin' user, or something similar, in order to tighten their security.

    ReplyDelete
  2. Daniël van EedenApril 1, 2011 at 1:26 AM

    There is 1 reserved connection for anyone having he SUPER privilege. And your replication account shouldn't need the SUPER privilege.
    http://dev.mysql.com/doc/refman/5.5/en/too-many-connections.html

    In Bug #24826 there is a patch to increase the number of reserved connections. This was done to make it possible to let the slave user use reserved connections.

    You really should use the mysql_secure_installation utility when setting up a new server. This remove the privilege for root to login remotely.

    ReplyDelete

Subscribe to: Post Comments (Atom)