Many newbies trip over connection issues, for three main reasons ...
(i) there are many moving parts,
(ii) MySQL documentation descriptions of these parts are often incomplete or confusing,
(iii) for many combinations of MySQL version and OS, it's hard to find step-by-step walkthroughs that work.
On the machine where MySQL is installed ...
1. Verify that your MySQL server is running. Run services.msc; MySQL should appear in the list of services under its name (MySQL, MySQL80, wampservermysqld80 &c;) with Status=Started.
If it isn't there, there's an installation issue to address; see https://dev.mysql.com/doc/refman/8.0/en/installing.html and/or Chapter 3 in Get It Done with MySQL.
If it's there but not started, right click on it to start it. If it fails, find the error description in the tail of <machinename>.err in the MySQL data folder where <machinename> is the name of the machine (if you don't know where the MySQL data folder is, look that up in my.ini, which is usually in a folder like c:\program files\mysql\MySQL Server 5.6), correct the error, and again try to start the service.
2. Verify that there is a MySQL user defined for desired logins and actions on your MySQL server. For now, we'll solve the connection problem for a 'root' user who can login from anywhere. You can attend to other users later. In a "DOS" commandline window, open the mysql command-line client using your MySQL password:
mysql -uroot -uPWDThis can go wrong in two ways:
(i) You may not have added a path to the MySQL bin folder where the client program lives. In that case, DOS will be unable to execute the above command. Fix that right now: right-click on the desktop [My] Computer icon, and navigate to Advanced System Settings | Environment variables. To the PATH environment variable add the full path to the MySQL bin folder. It will be something like "C:\Program Files\MySQL\MySQL Server 5.6\bin". In order for this path to be available to all programs, you may have to reboot the machine.
(ii) Possibly you defined no password for the MySQL 'root' user. In that case, the mysql program you invoked will deny access with a message about the password. Fix this now. In the DOS commandline window execute ...
mysql -urootWhen the mysql client program comes up, execute ...
select user,host,password from mysql.user;It will show a blank password value for 'root'. Makes things simple for now by letting 'root' log in from anywhere:
update mysql.user set host='%' where user='root';Then set a password for 'root' with ...
SET PASSWORD FOR user='root'@'%' = PASSWORD( 'pswd' ); FLUSH PRIVILEGES;Exit, and test the new password with
mysql -uroot -ppswd
Once that works, 'root' can login onto this machine from anywhere with that password. Test that from another machine on the LAN where another instance of MySQL is installed. There, open a "DOS" commandline window and execute
mysql -hmachine_name -uroot -ppswdwhere
machine_name is the name of the machine with the target MySQL server and pswd is the root user's password.
3. Connecting to your MySQL server from another LAN machine using the MySQL client program. It's often not a good idea to run an executable on boxA from boxB, especially when the boxA program depends on configuration settings which reference boxA paths--as is the case with the MySQL server. Also, it's not usually a good idea to share system folders, for example %windir% and %programfiles% on Windows.
So to use the MySQL client program on boxB to talk to a MySQL server on boxA, install MySQL on boxB for the sole purpose of using that mysql client executable to talk to the MySQL installation on boxA.
If boxA configuration or router settings may be interfering with boxA listening for incoming calls on port 3306, make sure Telnet is installed (it's free on most OSs) and run this from a command window in boxB:
telnet boxA 3306Then from that machine you can access MySQL on boxA with ...
mysql -hboxA -uUSR -pPWDwhere USR and PWD are appropriate username and password values for the instance of MySQL on boxA.
4. Connecting to your MySQL server from a LAN machine using MySQL WorkBench and other MySQL-enabled software. To connect to a MySQL Server from another machine on the LAN, you need only install MySQL WorkBench on that LAN machine, fire it up, click on New Server Instance under Server Administration, and enter the host user and password values from steps 1 and 2 above.
Other programs that connect to MySQL servers have similar user interface modules.
5. Connecting to your MySQL server from a LAN machine using MySQL APIs. If you will be connecting to your MySQL server via software you develop yourself using a MySQL API (Perl, PHP, ODBC, .NET, C/C++, Java &c;), follow the respective MySQL guideline at https://dev.mysql.com/doc/index-connectors.html or the respective chapter in Get It Done with MySQL.
Last updated 24 Sep 2024
