0

During Installation of my MYSQL I must have chosen not to let remote connection in my mysql. I assume this because when I select all users by running this select User,Host from mysql.user; gives me:

+-------+-----------+
| User  | Host      |
+-------+-----------+
|       |           |
| root  | 127.0.0.1 |
| root  | ::1       |
|       |           |
| root  | localhost |
+-------+-----------+

I want to give root to host my IP so I follow grant-remote-access-of-mysql-database-from-any-ip-address

But I cant find :

#bind-address           = 127.0.0.1
#skip-networking

Below is copy paste of my my.cnf and the command I run to edit the my.cnf

I am using centOS 7

[root@www ~]# vi /etc/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

How to correct this mistake?

UPDATE:

The reason Why I want to do this is to get rid of this error:

Connection failed: SQLSTATE[HY000] [1045] Access denied for user 'root'@'www.XXXXXXXX.com' (using password: YES)

UPDATE:

Tried but getting error :

mysql> GRANT ALL PRIVILEGES
    -> ON database.*
    -> TO 'user'@'%'
    -> IDENTIFIED BY 'qweasdzxc';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database.*
TO 'user'@'%'
IDENTIFIED BY 'qweasdzxc'' at line 2
Martin
  • 365
  • 4
  • 7
  • 22
  • Possible duplicate of [grant remote access of MySQL database from any IP address](https://stackoverflow.com/questions/8348506/grant-remote-access-of-mysql-database-from-any-ip-address) – Avin Varghese Oct 09 '17 at 01:07
  • @AvinVarghese it was the same link in my OP.. I am following the second answer.. I cant uncomment the line because I cant see the line – Martin Oct 09 '17 at 01:12
  • I suppose user is a reserved keyword, you may not user 'user' as a username. Ideally, only GRANT the privileges that are enough for operation of your business code, and limited to the hostname that your MySQL clients will connect from. – Fabien Haddadi Oct 09 '17 at 02:08
  • **`database`** is a reserved word. is this an example or the real name? – Jacques Amar Oct 09 '17 at 05:16
  • @JacquesAmar those data are example only not the real values – Martin Oct 09 '17 at 05:33

0 Answers0