MySQL Troubleshooting FAQ for Metawerx Customers - v1.1

This document describes the use of MySQL remotely through SSH, using the PLINK client. This information is intended for use by hosting customers of Metawerx Developer Hosting, however, it also serves as a useful guide for getting started with MySQL, either through a secure, encrypted remote connection, or locally for new developers.
CONTENTS

INTRODUCTION
Remote connections to MySQL are made by first setting up an SSH connection using SSH (Linux) or PuTTY/PLink (Windows). Please read the PLINK section of the following FAQ before attempting to connect to MySQL. SSH (RSync and PuTTY) Info/Tips Section 2 "Starting a connection with PLINK" If you are unable to connect, then the troubleshooting section below contains some common problems and solutions. After going through the Troubleshooting section, if you are still having problems connecting, please contact support for assistance. For faster turn-around, please include a screen shot of the problem you are having if possible, including the MySQL command window and the PLINK connection window.
REMOTE CONNECTION TROUBLESHOOTING FAQ
1. I am prompted for a password each time I try to connect. 2. I get the following error, what is wrong? ERROR 1045: Access denied for user: 'mydatabase@localhost' (Using password: YES) 3. I get the following error, what is wrong? Enter password: *** ERROR 1045: Access denied for user: 'mydatabase@localhost' (Using password: YES) 4. I get the following error, what is wrong? ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) 5. I get the following error, what is wrong? Enter password: ********** ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) 6. Other problems. 1. I am prompted for a password each time I try to connect. A. MySQL is attempting to connect to the wrong database, and also prompting for a password. This will happen if you use a space after the "-p" parameter in the MySQL command. -p should be specified without any space after it. For example, this will cause a problem: mysql -hlocalhost -u user -p pass The correct command is as follows: mysql -hlocalhost -uuser -ppass 2. I get the following error, what is wrong? ERROR 1045: Access denied for user: 'mydatabase@localhost' (Using password: YES) A. If you are also being prompted for a password, see the next FAQ question. (Make sure you are not using a space between "-p" and your password). If not, then either the password is incorrect or the database has been set up incorrectly. Please contact support and attach a screenshot of this problem. Another problem that can happen here, is if you are already running MySQL on your own PC. In this case, you *must* use port 3307 (or another port) instead of 3306, because your own MySQL instance (on your PC) will already be listening on port 3306, and it will be giving you the access denied error, probably because you are using the metawerx username/password, but your the MySQL instance running on your PC doesn't recognise this username/password. 3. I get the following error, what is wrong? Enter password: *** ERROR 1045: Access denied for user: 'mydatabase@localhost' (Using password: YES) A. MySQL is attempting to connect to the wrong database, and also prompting for a password. This will happen if you use a space after the "-p" parameter in the MySQL command. -p should be specified without any space after it. For example, this will cause a problem: mysql -hlocalhost -u user -p pass The correct command is as follows: mysql -hlocalhost -uuser -ppass 4. I get the following error, what is wrong? ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) A. No connection can be made to the database. This means the PLINK connection may not be correctly set up. If you are running MySQL on your machine: Check your PLINK command includes the following: "-L 3307:mysql.metawerx.net:3306" Check your MySQL command includes the following: "-hlocalhost -P3307" Check your PLINK window is active (it should be showing a prompt) If you are not running MySQL on your machine, and really want to use port 3306: Check your PLINK command includes the following: "-L 3306:mysql.metawerx.net:3306" Check your MySQL command includes the following: "-hlocalhost" Check your PLINK window is active (it should be showing a prompt) 5. I get the following error, what is wrong? Enter password: ********** ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) A. There are two problems here. You will be prompted for a password, and the MySQL command will attempt to connect to the wrong database if you use a space after the "-p" parameter in the MySQL command. -p should be specified without any space after it. For example, this will cause a problem: mysql -hlocalhost -u user -p pass The correct command is as follows: mysql -hlocalhost -uuser -ppass Secondly, no connection can be made to the database. This means the PLINK connection may not be correctly set up. If you are running MySQL on your machine: Check your PLINK command includes the following: "-L 3307:mysql.metawerx.net:3306" Check your MySQL command includes the following: "-hlocalhost -P3307" Check your PLINK window is active (it should be showing a prompt) If you are not running MySQL on your machine, and really want to use port 3306: Check your PLINK command includes the following: "-L 3306:mysql.metawerx.net:3306" Check your MySQL command includes the following: "-hlocalhost" Check your PLINK window is active (it should be showing a prompt) 6. Other problems - Please ensure you are using the latest version of MySQL on your local machine, including the latest version of the client. For most Linux users familiar with MySQL, this is usually the problem. - To test if your SSH/PLINK connection is working, and tunnelling through to our server, see if you can telnet to localhost, port 3307. If you can, you should get some garbage showing on the screen, because the server thinks you a MySQL client. This means you can connect correctly. Next, check your client is pointing to localhost, and port 3307 correctly.
CREATING A SIMPLE DATABASE WITH MYSQL
This tutorial explains how to create a simple database, with two rows and how to query it remotely using the MySQL command. The table will then be removed. 1. Connect to MySQL on the server In this example, we will assume your username is "testa", your password is "mypass", your database is "mydatabase" and you have created an SSH tunnel from port 3307 on your machine, to our MySQL server on port 3306 using the following tunnel: -L 3307:mysql.metawerx.net:3306 CD to your MySQL\bin folder and run the following command: mysql --port=3307 -hlocalhost -utesta -pmypass You should be presented with a prompt as follows: mysql> 2. In MySQL, switch to your database You can connect to your database using two methods. The first is to specify the database on the command line as follows: mysql --port=3307 -hlocalhost -utesta -pmypass mydatabase The second way is to connect first, then issue a USE command as follows: mysql> USE mydatabase The system should respond: Database changed mysql> 3. Now that you are in the right database, list all your tables using show "show tables" command. mysql> show tables; Remember to use a semicolon at the end of the command, or you will be prompted for additional lines, like in the following example: mysql> show tables -> ; The system will respond with a list of your tables, or "Empty set" if you have not created any tables yet. 4. Create a table using the following syntax (just copy and paste this example if you like) CREATE TABLE `contact` ( `id` int(11) NOT NULL default '0', `name` varchar(200) NOT NULL default '', PRIMARY KEY (`id`) ) TYPE=MyISAM; This creates a table with an ID column and a NAME column. You can use the "show tables" command to show the table to ensure it has been created. 5. Insert two rows using the following commands: INSERT INTO contact (id, name) VALUES (1, 'Test1'); INSERT INTO contact (id, name) VALUES (2, 'Test2'); Your database now contains two rows. 6. Run a SELECT statement to select and display all rows from the table. SELECT * FROM contact; This will show: +----+-------+ | id | name | +----+-------+ | 1 | Test1 | | 2 | Test2 | +----+-------+ 2 rows in set (0.00 sec) 7. We will now delete the table, because we only created it for this tutorial. DROP TABLE contact; 8. You may now exit the MySQL system by using the QUIT command. QUIT
COPYRIGHT NOTICE
All material is (c)Copyright Neale Rudd, Metawerx Pty Ltd, 1997-2014. If you found this information helpful, additional guides can be found at http://www.metawerx.net/admin under the 'Resources' link.
END OF DOCUMENT