Ask Your Question
1

connect to MySQL from external

asked 2015-04-09 19:03:18 -0600

---newbie--- gravatar image

updated 2015-04-13 11:45:52 -0600

mether gravatar image

I'm new to Linux and have done a clean install of MySQL during install of Fedora21

I have a Window7 with MySQL Workbench.

What steps or configuration are needed on Fedora & MYSQL to allow connections from my Windows 7 PC to MYSQL on Fedora?

Many thanks!

edit retag flag offensive close merge delete

Comments

How are you running both machines? as virtual or two separate physical machines?

anishjp gravatar imageanishjp ( 2015-04-10 06:17:44 -0600 )edit

Both machines are virtualized on separate hosts.

---newbie--- gravatar image---newbie--- ( 2015-04-10 11:54:34 -0600 )edit

I assume you understand how to get the networks of both virtualized machines talking, given you can telnet to port 3306 on the Fedora box, you probably do.

Do you have mariadb or mysql installed?

While there is very little difference between them, there are a couple of differences that matter.

snowolfe gravatar imagesnowolfe ( 2015-04-11 01:47:20 -0600 )edit

3 Answers

Sort by » oldest newest most voted
1

answered 2015-04-13 09:52:02 -0600

---newbie--- gravatar image

Problem solved thanks all, I used parts of all your answers to open the firewall and create a sql user:

firewall-cmd --permanent --add-service=mysql firewall-cmd --reload

login to mysql:

mysql -u root -p (or try just: mysql)

enter password

Create a user and set permissions

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON . TO 'newuser'@'localhost' WITH GRANT OPTION; CREATE USER 'newuser'@'%' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON . TO 'newuser'@'%' WITH GRANT OPTION;

edit flag offensive delete link more

Comments

Excellent.

See my comment above. If you agree with my changes can you please select the check mark on my answer to indicate the solution is shown. This will help others needing to do something similar and give them confidence that it is a workable solution.

snowolfe gravatar imagesnowolfe ( 2015-04-16 02:24:37 -0600 )edit
2

answered 2015-04-10 03:45:53 -0600

snowolfe gravatar image

updated 2015-04-16 02:21:00 -0600

Adapt this process as necessary. Note: This process is almost exactly the same for mariadb.

The IP addresses shown are examples only. Some of this content has been replicated from here.

  1. Logon to your Fedora box and open a terminal (use a ssh client, like Putty, if you are doing this from the Windows 7 machine). Switch to root (you will be prompted for your fedora-username password):
    sudo -i
    
  2. Edit /etc/my.cnf using an editor like nano (recommended over vi if you are a Linux newbie):
    nano /etc/my.cnf
    
  3. Locate and edit the bind-address, must be your Fedora machine external IP address, not localhost. Comment out the skip-networking by prefixing it with an asterisk (#). For example:
    [mysqld]
    user            = mysql
    pid-file        = /var/run/mysqld/mysqld.pid
    socket          = /var/run/mysqld/mysqld.sock
    port            = 3306
    basedir         = /usr
    datadir         = /var/lib/mysql
    tmpdir          = /tmp
    language        = /usr/share/mysql/English
    bind-address    = 192.168.0.10                # example address shown
    # skip-networking
    ...
    
    Save your changes. Note: If you need to determine the IP address of your Fedora machine, use the appropriate address from the output of:
    ip addr show | grep inet
    
  4. Update the mysql server service using systemctl:
    systemctl enable mysql    # ensures the services starts at boot time
    systemctl restart mysql   # assumes mysql server is already running
    
    Note: use man systemctl to find more commands, but the common commands are 'reload', 'start', 'stop', 'status' and 'disable'.
  5. Login to mysql server and grant access to the remote (Windows) IP address:
    mysql -u root -p mysql
    
    To create a user, admin, able to login from anywhere and administer all databases, including granting rights to other users (superuser), enter:
    mysql> CREATE USER 'admin'@'%' IDENTIFIED BY 'password';
    mysql> GRANT ALL PRIVILEGES ON *.* TO 'bar'@'%' WITH GRANT OPTION;
    
    To grant access to a new database, foo, for user bar, and from a specific remote IP 192.168.0.20, enter:
    mysql> CREATE DATABASE foo;
    mysql> GRANT ALL PRIVILEGES ON 'foo'.* TO 'bar'@'192.168.0.20';
    
    To grant access to an existing database, webdb, user webadmin, and remote IPs 192.168.0.0/24, enter:
    mysql> GRANT ALL PRIVILEGES ON 'webdb'.* TO 'webadmin'@'192.168.0.%';
    
    Logout of mysql server:
    mysql> exit
    
    Note: Use 'localhost' for users that will only ever connect from the local machine.
  6. Enable access through the firewall:
    firewall-cmd --permanent --add-service=mysql
    firewall-cmd --reload
    
    The above updates the default 'zone' with the predefined mysql server port settings (firewalld ships with a large selection of common services predefined, including mysql.) Use --zone=NAME to update a different firewall zone. As an aside; the same service name can be used for mariadb.
  7. Test or begin using the mysql server from Windows.

Change as necessary for additional remote connection clients.

edit flag offensive delete link more

Comments

Many thanks for the details instructions, I've edited the my.cnf file, it was missing all the information under [mysqld]

when I tried "systemctl enable mysql " I got the error: Failed to execute operation: No such file or directory

I continued to the firewall settings and I can now telnet from the windows box to 3306 but get the

following error: host '10.xxx.xxx.xxx' is not allowed to connect to this mariaDB server

how can I whitelist all IP's or a subnet?

Thanks!

---newbie--- gravatar image---newbie--- ( 2015-04-10 10:05:31 -0600 )edit

Sorry - I began drafting a response and got called away (family - so demanding!), and then forgot - my bad.

As you have discovered % acts as a wildcard (including localhost) and if memory serves, you can also do something like 192.168.0.% to manage connections from a subnet.

Use user@'%' with caution - if the user account is compromised, then your database is dependent on network/external security to stop remote access.

I have updated the steps above to clarify these points.

snowolfe gravatar imagesnowolfe ( 2015-04-16 01:14:09 -0600 )edit
0

answered 2015-04-10 01:38:35 -0600

masteroman gravatar image

First you'll need to enable access from remote IPs in your MySQL configuration. After that you will probably need to open up port 3306 which is default port for MySQL. You can do that by following this guide: link

edit flag offensive delete link more

Comments

1

@masteroman the link you provide for configuring remote access also describes how to open the port in the Fedora firewall; while the information given in the link is mostly still valid, it is also out of date because F21 is now using systemd and firewalld. The IP tables configuration may be invalidated/lost if the user also manipulates the firewall later, using firewalld. Steps 2, 3, 5 & 6 (all pertaining to mysql) should be good, but the other steps should probably be done using systemctl and either firewall-cmd (or the GUI version). I will post an answer showing those steps next.

snowolfe gravatar imagesnowolfe ( 2015-04-10 02:04:49 -0600 )edit

@snowolfe you're right, I haven't noticed that that was out-of-date guide, and yes, since systemd came everybody should use firewall-cmd for managing firewall rules. I'll try to be more careful in the future.

masteroman gravatar imagemasteroman ( 2015-04-10 02:10:38 -0600 )edit

Question Tools

1 follower

Stats

Asked: 2015-04-09 19:03:18 -0600

Seen: 6,471 times

Last updated: Apr 16 '15