By default remote access to MySQL database server is disabled for security reasons. However, some time you need to provide remote access to database server from home or a web server. If you want to remotely access to the database server from the web server or home, follow this quick tutorial.
MySQL Remote Access
You need type the following commands which will allow remote connections.
Step # 1: Login Using SSH (if server is outside your data center)
First, login over ssh to remote MySQL database server:
ssh user@mysql.nixcraft.iStep # 2: Edit my.cnf File
Once connected you need to edit the MySQL server configuration file my.cnf using a text editor such as vi.
- If you are using Debian Linux file is located at /etc/mysql/my.cnf location
- If you are using Red Hat Linux/Fedora/Centos Linux file is located at /etc/my.cnf location
- If you are using FreeBSD you need to create a file /var/db/mysql/my.cnf
Edit /etc/my.cnf, run:
# vi /etc/my.cnf
Step # 3: Once file opened, locate line that read as follows
[mysqld]
Make sure line skip-networking is commented (or remove line) and add following line
bind-address=YOUR-SERVER-IP
For example, if your MySQL server IP is 65.55.55.2 then entire block should be look like as follows:
[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 = 65.55.55.2 # skip-networking .... .. ....
Where,
- bind-address : IP address to bind to.
- skip-networking : Don’t listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed. Since you need to allow remote connection this line should be removed from my.cnf or put it in comment state.
Step# 4 Save and Close the file
Restart the mysql server, enter:
# /etc/init.d/mysql restart
Step # 5 Grant access to remote IP address
Connect to mysql server:
$ mysql -u root -p mysql
Grant access to a new database
If you want to add a new database called foo for user bar and remote IP 202.54.10.20 then you need to type the following commands at mysql> prompt:mysql> CREATE DATABASE foo;
mysql> GRANT ALL ON foo.* TO bar@'202.54.10.20' IDENTIFIED BY 'PASSWORD';
How Do I Grant Access To An Existing Database?
Let us assume that you are always making connection from remote IP called 202.54.10.20 for database called webdb for user webadmin, To grant access to this IP address type the following command At mysql> prompt for existing database, enter:
mysql> update db set Host='202.54.10.20' where Db='webdb';
mysql> update user set Host='202.54.10.20' where user='webadmin';
Step # 5: Logout of MySQL
Type exit command to logout mysql:mysql> exit
Step # 6: Open port 3306
You need to open TCP port 3306 using iptables or BSD pf firewall.
A sample iptables rule to open Linux iptables firewall
/sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT
OR only allow remote connection from your web server located at 10.5.1.3:
/sbin/iptables -A INPUT -i eth0 -s 10.5.1.3 -p tcp --destination-port 3306 -j ACCEPT
OR only allow remote connection from your lan subnet 192.168.1.0/24:
/sbin/iptables -A INPUT -i eth0 -s 192.168.1.0/24 -p tcp --destination-port 3306 -j ACCEPT
Finally save all rules:
# service iptables save
A sample FreeBSD / OpenBSD pf rule ( /etc/pf.conf)
pass in on $ext_if proto tcp from any to any port 3306
OR allow only access from your web server located at 10.5.1.3:
pass in on $ext_if proto tcp from 10.5.1.3 to any port 3306 flags S/SA synproxy state
Step # 7: Test it
From your remote system or your desktop type the following command:
$ mysql -u webadmin –h 65.55.55.2 –p
Where,
- -u webadmin: webadmin is MySQL username
- -h IP or hostname: 65.55.55.2 is MySQL server IP address or hostname (FQDN)
- -p : Prompt for password
You can also use telnet to connect to port 3306 for testing purpose:
$ telnet 65.55.55.2 3306
Featured Articles:
- 20 Linux System Monitoring Tools Every SysAdmin Should Know
- 20 Linux Server Hardening Security Tips
- Linux: 20 Iptables Examples For New SysAdmins
- My 10 UNIX Command Line Mistakes
- 25 PHP Security Best Practices For Sys Admins
- The Novice Guide To Buying A Linux Laptop
- Top 5 Email Client For Linux, Mac OS X, and Windows Users
- Top 20 OpenSSH Server Best Security Practices
- Top 10 Open Source Web-Based Project Management Software
Facebook it - Tweet it - Print it -
We're here to help you make the most of sysadmin work. So, subscribe!
{ 118 comments… read them below or add one }
Could you tell us how to setup proper MySQL client program on remote machine first?
As far as I now, even if we ONLY install MySQL client program on remote machine, it will generate a my.cnf file. Whenever you issue mysql command on remote machine, this file will be consulted and thus, the client will attempt to connect to a non-exist local MySQL sever rather than your remote server.
To be frank you don’t need to setup my.cnf for client configuration. All you need to do is specify remote mysql host with –h option. For example to connect remote mysql server called dbserver.nixcraft.in you need to type command as follows:
$ mysql –u vivek –h dbserver.nixcraft.in -p
OR if you with to use MySQL server ip address (192.168.1.101):
$ mysql –u vivek –h 192.168.1.101 -p
Absolutely right! because I’d tried it how many times. And and I found it was right.
There is no need to setup in my.cnf, without configuration it works fine for remote address.
Sachid: can you please tell me how can i configure without updating my.cnf file ?
nixcraft said…
To be frank you don’t need to setup my.cnf for client configuration. All you need to do is specify remote mysql host with –h option. For example to connect remote mysql server called dbserver.nixcraft.in you need to type command as follows:
$ mysql –u vivek –h dbserver.nixcraft.in -p
OR if you with to use MySQL server ip address (192.168.1.101):
$ mysql –u vivek –h 192.168.1.101 -p
5/04/2006 11:29 AM
+—————————————————-+
This didn’t work at all. BTW, who is “vivek”? Is that your client machine?
Anyhow, the host server keeps telling me that my client computer is not allowed to connect. There must be more to it that I am missing.
+—————————————————–+
vivek is username and 192.168.1.101 is server IP. You need to setup correct permission using GRANT command (see above for an example).
I’m having a problem accessing the file:
~
“/etc/my.cnf” [New File]
[1]+ Stopped vi /etc/my.cnf
[mysql5@serv mysql5]$ vi /etc/my.cnf
please help.
I’m having a problem accessing the file:
~
“/etc/my.cnf” [New File]
[1]+ Stopped vi /etc/my.cnf
[mysql5@serv mysql5]$ vi /etc/my.cnf
please help.
/etc/my.cnf is standard location. But location is depend upon mysql version and Linux distro. Use find command to find out my.cnf. Type the following command as root user:
find / -iname my.cnf
Before I have this response from
> mysql -h hostname -u username -p
ERROR 2003 (HY000): Can’t connect to MySQL server on (113)
After following your solution. I got this response
mysql Ver 14.12 Distrib 5.0.22, for redhat-linux-gnu (i686) using readline 5.0
Copyright (C) 2002 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Usage: mysql [OPTIONS] [database]
-?, –help Display this help and exit.
-I, –help Synonym for -?
–auto-rehash Enable automatic rehashing. One doesn’t need to use
I check connectivity using ping host
and got response
icmp_seq=0 ttl=64 time=0.542 ms
meaning i have connectivity
Is there something i missed?
I tried telnet for connectivity
$telnet ipaddr
Trying ipaddr…
telnet: connect to address ipaddr: No route to host
telnet: Unable to connect to remote host: No route to host
do i have a problem with my ipaddr?
thanks, this helped me out.
Do not forget to adjust your iptables file (/etc/sysconfig/iptables usually) to allow connections on that port. Typically you will find that TCP connections are enabled on port 22 (ssh) and port 80 (http). Add an entry for port 3306
Using Fedora 6 as host and added port 3306 to
/etc/sysconfig/ip6tables and /etc/sysconfig/iptables
For the ip6table file the formatted line is;
-A RH-Firewall-1-INPUT -m tcp -p tcp –dport 3306 -j ACCEPT
-A RH-Firewall-1-INPUT -m tcp -p tcp –sport 3306 -j ACCEPT
CptBeluga,
Yup, you need to open port 3306 for communication.
Appreciate your post!
I was able to access mysql after changing these tables but when I restarted the computer the files were changed back. There must be another way to do this without manually changing them.
mysql> update user set Host=’202.54.10.20′ where user=’webadmin’;
I always receiving error when performing above command. Could u advise
mysql> update user set Host=’202.54.10.20′ where User=’webadmin’;
I also cannot find my iptables file on redhat enterprise
you know what, now am not be able to login as root on localhost. How can I revoke those update above
Yeah exact same thing happened to me. I try running the same command to give me back rights and it wont work.
***************************************************************
SyntaxError: invalid syntax
>>> update user set Host=’127.0.0.1′ where user=’root’;
File “”, line 1
update user set Host=’127.0.0.1′ where user=’root’;
^
Bless your soul for writing this. I was endlessly modifying permissions in mysql until I read your post and realized I had to change the bind-address.
– George
Hello,
Simply great,expect same again.
This is an awesome article. The only thing I wish it covered is how to name your server (so when you connect you could do it through `myserver.mydomain.com` and whether you can specify a range of allowed remote IPs. But still, this is a very good write up and one that I learned a lot from.
Look, what if a want that all my lan have acces to the db?
Hey i am having a problem with Bad Handshake!!
Can any body help me please!!
Nice addition. Simply great,expect same again.it will help me a lot.
How can i access mysql database running on windows pc from linux server, on both machine i am using mysql 5 and perl 5.8.8 , ip of windows machine ie. 192.168.0.50 and linux server has 192.168.0.10 on single LAN.
heloo admin ,
i want to find the ip address of the my sqlserver in fedoro 5 how to find it can u guide me .
I have 2 mysql servers #1 and #2 in a LAN. I want to remotely access either 1 of the 2 servers from box #3. How do I specify that server #2 is to respond and not server #1 to my request. thanks a lot.
Is there a way to make a mysql server accessible both locally AND remotely?
When I change bind-address to some ip address the ubuntu LAMP server will fail to start mysqld. When I change the bind-address to 127.0.0.1, I can’t access it remotely.
Nice tutorial up there !
Keep up the good work
Hi there,thankyou for this article..
I made all steps and when I try to login in the WEB application that i’m setting up, there is an error:
MySQL error, Connection Lost during query
Does this mean that conection was done, but something kicked me out ?
I think yes, and I will be very glad to know if you have a clue of what it is causing this.
Thank you
The MySQL manual has some information on this which you might find useful. It goes about it a slightly different way which some people might find easier.
MySQL remote Access Control
Hi,
what if I could not find the file my.cnf on my server?…..I am actually using a web hosting server.
Regards,
KK
I have problem to connect to remote server thought telnet I made and configure a user in Linux and in mysql also.
I want that the user get in directly to mysql trough Linux shell giving password not to use Linux shell, just telnet the ip address and enter password to go to mysql
For database use and when the user wants to exit the session must close. Would you please help.
Every time I try and restart Mysql I ge this error. Everything to be working write I can get into mysql but can’t log on to it remotely. Please help.
Thanks,
-Veggie
veggie@Server:~$ sudo /etc/init.d/mysql restart
* Stopping MySQL database server mysqld [ OK ]
* Starting MySQL database server mysqld [ OK ]
* Checking for corrupt, not cleanly closed and upgrade needing tables.
Dear All;
to access to remote computer you need to select (Enable root access from remote machines) during the instsllation of Mysql server.
after that you must change the localhost to the IP adress for the remote computer and you will get the conection.
EX: you use the A PC and need to conect to B PC in B PC you have the database and it’s IP 1.1.1.1 so you need to change the do the following in A pc to get the conection to B PC:
“jdbc:mysql://1.1.1.1:3306/yorDBName”
good luck for all
great article. I could setup the remote mysql admin by editing the my.cnf fie.
Thanks.
Thanks to commenters’ tips to open port 3306 – fixed my problem.
Alternative to directly editing the iptables file:
system-config-securitylevel
–>advanced
–>add the port at the bottom
Thanks
Thank you, just what I was looking for
Thanks Google to send me to this page. Thanks for the article. I have two questions. In CPanel I think there is a remote access option. Can u not use to set up remote access? Is there any way I can create desktop application to access database in host
Just to say: Great!
Solved my problem at all!
Thanks for writing it!
Yes this is help me out and thanks a lot
This is a very informative article. It helped me a lot.
I can not find the my.cnf anywhere in the system. I have installed RHEL5.1.
I ran this command: find / -iname my.cnf, but returns nothing.
Am I missing something? Do I need to actually create a file called my.cnf under etc?
Thanks for your inputs.
cfscg.
Do you have mysql installed?
yum list installed mysql
Yes. I have MySQL 5 installed. I am able to log into the test and mysql databases when I am on the machine (not remotely).
but I am trying to connected to this server remotely and obviously I need to work on the my.cnf
Below is what yum command returned. Thanks for your help.
cfscg.
May be file is deleted or replaced. Just do forceful installation again.
saved my day, thanks a lot…
Thanks, useful article!
And how to grant access to ANY ip address?
E.g. several users with different unpredictable IPs should access to database…
Just a follow up.
Thanks for your help.
I did re install the MYsql 5 and I had to copy the My-huge.cnf to /etc/my.cnf
i hav designed a website using JSP and mysql, there is an enquiry form in that website ,when one fills the form the details automaticaly go into databse as the logic goes..but my problem is on localhost(tomcat) when i fill the form and submit, a retrieved form is displayed which shows my entry..but when i hosted the website the data is not being retrieved …which means it is not geting connected to the server database which i created on putty.. can anyone please help me on this.i am new to jsp ..i just cannot get the logic.i mean on local host alls well but nt on the server..can anyone help step by step on this..please.
my website link is http://www.dcg.net.in
u can try and fill the form ..
it will give you a blank retrieved form..
below is my code containg database connectivity
String connectionURL = "jdbc:mysql://mail.dcg.net.in:3306/student"; Connection connection = null; PreparedStatement pstatement = null; Class.forName("com.mysql.jdbc.Driver").newInstance(); int updateQuery = 0; if(subject!=null && enquirydetails!=null && firstname!=null && lastname!=null && companyname!=null && companyaddress!=null && zipcode!=null && phoneno!=null && emailadd!=null) { if(subject!="" && enquirydetails!="" && firstname!="" && lastname!="" && companyname!="" && companyaddress!="" && zipcode!="" && phoneno!="" && emailadd!="") { try { /* Create a connection by using getConnection() method that takes parameters of string type connection url, user name and password to connect to database. */ connection = DriverManager.getConnection (connectionURL, "root", "XXXX"); // sql query to insert values in the secified table. String queryString = "INSERT INTO enquiry(subject,enquirydetails,firstname,lastname,companyname,companyaddress,zipcode,phoneno,emailadd) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"; /* createStatement() is used for create statement object that is used for sending sql statements to the specified database. */ pstatement = connection.prepareStatement(queryString); pstatement.setString(1, subject); pstatement.setString(2, enquirydetails); pstatement.setString(3, firstname); pstatement.setString(4, lastname); pstatement.setString(5, companyname); pstatement.setString(6, companyaddress); pstatement.setString(7, zipcode); pstatement.setString(8, phoneno); pstatement.setString(9, emailadd); updateQuery = pstatement.executeUpdate(); if (updateQuery != 0) { %>Hi, your article helped me a lot! Thanx! :)
Just in the section “How Do I Grant access to existing database?” you first must select MYSQL database, and then you can update or alter tables USER and DB. So:
mysql> use mysql
mysql> update db set Host=’202.54.10.20′ where Db=’webdb’;
mysql> update user set Host=’202.54.10.20′ where user=’webadmin’;
Hi, this is a pretty good article even though I had to make a few adjustments to setup the MySQL server on my Mac but the instructions are nonetheless still good.
nice tutorial……. keep it up….. thanks for ur help….
Thank you, thank you, thank you. I’m running Ubuntu 8.04 and this was perfect.
hello, how could i allow access only to one remote ip?
i noticed that i can login to all my mysql accounts remote..
Thank you for your good article.
But I can�t access MySQL-Server remotely.
The bind-address is added
The user �test� has grants and can be accessed remotely.
But I can access MySQL � Server from the second host, because the access is not allowed from the host.
Can you tell me please what I�m doing wrong?
Thank you
Thank you for your good article.
But I can’t access MySQL-Server remotely.
The bind-address is added
http://lh5.ggpht.com/_ZBIpOWf6mz4/St4Rw51LRYI/AAAAAAAAB4c/PTJZqWy8H8Y/s800/my.cnf.jpeg
The user ‘test’ has grants and can be accessed remotely.
http://lh6.ggpht.com/_ZBIpOWf6mz4/St4Rw8KUD4I/AAAAAAAAB4g/JzQwzOUVhTM/s800/admin.jpeg
But I can access MySQL-Server from the second host, because the access is not allowed from the host.
http://lh6.ggpht.com/_ZBIpOWf6mz4/St4Rw9bgW0I/AAAAAAAAB4k/uLknuLyo7zk/s800/telnet.jpeg
Can you tell me please what I’m doing wrong?
Thank you
my error was very simple. the host name of the second host was wrong :-)
P R
Hi all,
To get the above task done, there isn’t any need to change the configuration file . It can be done simply by typing the below command from mysql console:
GRANT ALL ON foo.* TO bar@’%’ IDENTIFIED BY ‘PASSWORD’;
By default, mysql allows only local connections, using ‘%’ sign this setting can be
changed to allow remote connections and local connection as well.
Muchísimas gracias por tu ayuda, alfín he podido configurar mi entorno de desarrollo.
Thanks a lot for your help, finally I could configure my development environment.
=)
Thank you. This saved me a lot of time and aggravation.
Nice… great article
;-)
I suggest that you remove the –bind-address from my.cnf :
============
http://dev.mysql.com/doc/refman/5.1/en/server-options.html#option_mysqld_bind-address
–bind-address
[...]
The IP address to bind to. Only one address can be selected. If this option is specified multiple times, the last address given is used.
If no address or 0.0.0.0 is specified, the server listens on all interfaces.
I have made the bind-address entry in my.cnf and granted access to host and db in mysql db as per the instructions above.
Both the mysql server and remote host r in same network, still i get an error like this
ERROR 2003 (HY000): Can’t connect to MySQL server on ’172.16.56.1′ (113)
Following step 5 did not work for me because my db was created in MySQL Workbench as a blank db. Does this instance need the tables from the ‘mysql’ instance copied over before I can perform this step. There is no table named db in my newly created instance; hypothetically “foo”.
try writing “flush privileges;” before loging out mysql in step 5 ;-)
Ditto what villalvilla said– gotta flush privileges!
I have to do a program in C# that access a mysql database but i have no idea how to can you tell me a website that can help with this????
hi,
please can you tell me how to configure jdbc connection in linux…. oracle version is 10.0.2.0.1.0
geat.. sory i can’t speak english with Good
i am a hamachi user ::
so how if i want use 2 IP to remote mysql
when first i use ip 192.168.0.1 in local homework
and Ip 5.59.52.1 in hamachi group
thank’s
Great Article.
When I add the bind-address to my.cnf
mysqld will not restart.
In the log I have the following:
100327 13:12:58 mysqld started
100327 13:12:58 InnoDB: Started; log sequence number 0 43654
100327 13:12:58 [ERROR] Can’t start server: Bind on TCP/IP port: Cannot assign requested address
100327 13:12:58 [ERROR] Do you already have another mysqld server running on port: 3306 ?
100327 13:12:58 [ERROR] Aborting
100327 13:12:58 InnoDB: Starting shutdown…
100327 13:12:59 InnoDB: Shutdown completed; log sequence number 0 43654
100327 13:12:59 [Note] /usr/libexec/mysqld: Shutdown complete
I do not have any IPtables or firewall that I can find. This is on Centos4
Thanks a ton. Just in case anyone needs help, granting my permissions worked fine on my existing database. (the instruction for making a new database) I skipped the next two instructions. Also, service iptables didn’t work for me, but my distro had some instructions for saving the new iptables rule to a new file in the /etc directory and putting a line in the /etc networking configuration to load that file at every boot; so search your distro for saving iptables if service iptables doesn’t work for you either.
hello i am new to using mysql can you tell me the command to allow remote access to a server on the mysql command line.
im agree with you thanks for u gays ,but the big problem is not the binding-ip ,the problem is how to access to this file if you are a client .
the hosting company only can access to this file.
Typically hosting companies will use some type of web client that will allow you to make these changes to a mysql database located on their system. I am not familiar with all of the systems out there, but in cpanel there should be a section of icons listed under the heading databases. Select “Remote MySQL” and enter the ip address or host name of the system you are allowing access. You will also need to do this if you are designing a website that will be database driven so that you can make connections to the database through a web page.
Great manual, thanks a lot!
Thanks for the article – sorted my problem easily!
what is the Grant command to give access to a ‘user’ on ‘db1′ and ‘db2′ ,all tables?
Paul,
Create user n then grant priviledges.
create user ‘user’@'localhost’ identified by ‘password’;
grant SELECTon db1.* to ‘user’@’10.1.1.13′ identified by ‘password’;
Here user is the username
password is the password for user to connect to mysql
db1.* refers to all tables of db1
10.1.1.13 refers to ip address from where the user connects
You can also give ALL priviledges or SELECT,INSERT,UPDATE,DELETE,CREATE,DROP priviledges
Thankx for ur reply.
I wanted ‘user’ to have access only on db1 and db2 databases only (all tables) out of several other databases, not to a particular db1 ..as in ur example.
Kindly help.
yes. This query will provide access to db1. Write a similar query for db2 as well. It will help.
U can write similar query for db2 also. It will provide user to access only db1 and db2 databases.
Is that ur query?
yes ,thnkx a lot .It worked for me . One thing i would like to share is that seperate create statement for user creation is not required in case of grant statement.grant command create user as well.
Anyways thnkx ..thnkx a lot..
It is a very helpful document, it did help me from the start and I’m facing the next step now; collaboration and backup from a remote machine&domain. They want to do a login from another domain and with port forwarding to copy a few DBs. Something like: $ ssh -L freeport:mysql-server-ip:3306 user@host.domain.com and on the remotehost.domain in a new terminal window $ mysql -h localhost –port=freeport -u mysqluser -p
comes back with : ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)
The user@host.domain.com can login successfully with: $mysql -h mysql-server-ip -u mysqluser -p only the client port forwarding need something.
Can anyone help with this ? Thanks in advance.
hi i m confused with your query for the solution you have provided for the existing database grant to the existing user that he can logged with his original password or should we have to update the password.
mysql> update db set Host=’202.54.10.20′ where Db=’webdb’;
mysql> update user set Host=’202.54.10.20′ where user=’webadmin’;
Thanks, by doing an update solved my problem Much appreciated.
Thanks, useful.
Fantastic writeup. I had multiple issues and this one solved everything…
Hi Sir, I have this in my .conf ( OS is debian and was installed from apt-get )
# Instead of skip-networking the default is now to listen only on
How can I grant access to my local database to a remote connection via Workbench 5.2?
Thanks a lot
For ubuntu 9.10 and later
Don’t
/etc/init.d/mysql restart
Use
sudo service mysql restart
Anyone else getting this error:
ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’
When you run step 5 on Ubuntu 10.04?
Neat. Easy to follow. Got my job done. Thanks
I was looking for any method to access remotely a db and update local database with every change occur. is it possible? thanks in advance
I wonder what’s the difference of just connecting via SSH, use a command line and access the mysql prompt from there.
Any benefits of this tutorial’s approach than what I do with mine?
Thanks, this has helped me figure out how to deal with remote connections.
Hi,
I am trying to allow external connections to an existing database. When I issue the command:
update db set Host=’202.54.10.20′ where Db=’webdb’;
(I changed the IP to my host and webdb to my database name), I always get:
“ERROR 1046 (3D000): No database selected”.
If I select my database first (by doing “use mydb”;), and issue the above command again, then I get:
“ERROR 1146 (42S02): Table ‘mydb.db’ doesn’t exist”
What am I missing?
Yes ! without configuring /etc/my.cnf aslo it can work . It’ll help to update remote host mysql db .
Best Regards,
Satya.M
Thank you for this tutorial!
I followed every single step and it worked!
and…. how could I grant all ip address range 192.168.1.20 – 192.168.1.30
to connect into that mysql server?
Don’t say to me repeat that command over 10 times. That’s not the answer, tough.
Hello admin… just U save my life I was found how to access to my mysql BD for the enterily day and finally I found this page… It’s so clear and really helpful so thanks
Hi there, I have a query that needs your support.
This is the ERROR I get while creating a NEW SERVER INSTANCE: in Windows.
1.………… error ………………………………………………………………………
Connecting to MySQL server localhost…
Cannot start SSH tunnel manager
Connecting to MySQL server localhost…
Cannot start SSH tunnel manager
Connecting to MySQL server localhost…
Cannot start SSH tunnel manager
……………………………………………………………………………………………
And also
2. ……………………………………………………………………………………………
In order to manage a MYSQL server it must be installed as a service.
The wizard could not find MYSQL service on the target machine, hence
the server instance cannot be created.
……………………………………………………………………………………………
Question
How do I make sure that MYSQL 5.1 is running before I use the MYSQL
Administrator to configure new Server Instance.
I will be grateful to be answered the above question in reference to
the errors the program is giving me in the Windows environment.
I know this article is not about SSH but I just wanted to mention that those that are confused with these condifuration changes or skittish about doing them can just use an SSH client (I use Putty) and use tunneling to then access MySQL data as ‘localhost’. Keeps everything locked down and I use the MySQL administrator and Query Browser without any issues on a windows 7 machine. I do have a local dev MySQL server running on my machine so I do have to stop that service when I tunnel in to the remote MySQL debian linux machines. If I forget the user/pass’s will trip me up so not a serious mistake waiting to happen :-)
everything seem to work fine but Step # 6: Open port 3306
/sbin/iptables -A INPUT -i eth0 -p tcp –destination-port 3306 -j ACCEPT ??
The variable skip-name-resolve will also keep remote access from working. Was pulling my hair out trying to get this to work, comment out that variable and viola.
i have configured multiple instance of mysql having new instance port 3307. But am not able to connect to this port from a mysql client. Where should i change the configuration?
Thanks for this excellently written article. I was going to write my own, but I think I’ll just link to yours.
Hello To all,
mysql is work proparly,but for remotly access i make file as you write but after this changes mysql is not start,i am using centos 5
/etc/ini.d/mysqld restart
so please help me.As Soon as possible.
How to allow multiple users in local network to share a single MySQL database
Hi, I have edited my.cnf according to your Step-3. But while restarting it says “MySQL Daemon failed to start.”. Just for the note, my server is running on amazon ec2. Any idea what went wrong?
you can comment bind-address:
#bind-address
to skip networking
Why do all this…if you have SSH Access, just setup a tunnel, unless you need people without SSH Access to access the database. But an SSH tunnel will be way more secure than opening it up to the world, and you don’t have to do any of this simply:
ssh -L 3307:localhost:3306 you@yourhost.com
Using 3307, incase you have a local MySQL server installed.
Very nice article, the series of comments which has went over here, made many things clears. Thanks for sharing