My mysql server is configured to not allow remote connection.
Should I seriously care about mysql user and password in this case?
It is very good that the mysql server is not required to support remote connections because it greatly reduces the attackable surface.
But you should consider defence in depth as a strategy to slow down an attacker or even reduce the impact of an attack.
Setting up a good password for the database users is no significant amount of work, so you should do that. That same is true for permissions that are likely not needed such as executing shell commands or creating stored procedures.
Fine tuning the permissions of the database users might be more difficult. So it might be more effective to spend effort elsewhere first. For example reviewing a web application for SQL injection vulnerabilities.