How to Connect to MySql

Connect to a MySql Server

Connecting to MySql require entering the MySql host, port, database user and password as explained in the Connection Dialog page.

If you fail connecting you may need to check one of the items below.

  1. 1Start MySql Service

    To make sure MySql is up and running

  2. 2MySql User Names

    MySql users are a combination of username and host name. Like this a user is bind to a certain host from where he can connect. Read this to understand how this works.

  3. 3Enable Remote Connections

    If MySql is running on a different computer as DbSchema, MySql should be configured to allow remote connections. This can be done during MySql installation or afterwords as described here

  4. 4Reset Password or Missing Privileges to Login

    Use this if you forgot the password or to fix the missing login privileges.

  5. 5Allow MySql through Windows Firewall

    Firewalls may block the connections. Read this for details.

  6. 6What to Check on the Common Error Messages

    A list of error messages and how you may fix them.

  7. 7Run MySql using Docker Container

    How to run MySql from docker container.

  8. 8Guided MySql Installation on Windows

    How to install MySql on Windows.

Enable MySql Service

MySql server should be up and running in order to be able to connect. Therefore the MySql service should be started.

Windows

To enable MySql Service follow this steps:
  1. Go to Start -> Control Panel -> System and Security -> Administrative Tools -> Component Services
    How to configure MySQL services
  2. Open Service Local
  3. Find your mysql service name setup during installation (For Example: MySql56)
  4. Right click on the service name and click start.
    How to configure the service name

Linux

To start the mysql service on linux just type in the console: service mysqld start

Understand MySql User Names

MySql users are a combination of username and host name which are allowed to connect ( can be '%' for any host). For example you can create an user sample in the database which may connect only from 'localhost':
CREATE USER 'sample'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'sample'@'localhost';
        FLUSH PRIVILEGES;
If you try to connect from another host you may receive the error 'Host ... is not allowed to connect'. In this case you better create the user with the right to connect from any host ('%'), or use the host which is shown in the error message.
CREATE USER 'sample'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'sample'@'%';
        FLUSH PRIVILEGES;
Connecting from DbSchema you should use the user 'sample'. By default MySql already has an user 'root'@'localhost', with a password you should have already set during installation. This user will work only if you run DbSchema on the same machine where MySql is running ( if you use docker containers it is a virtual separate machine ).

If this is not the case, you should create an user with a the a host which is matching your host. Also check the chapter which is explaining how to enable remote connections ( disable bind_address in my.ini or my.cnf ).

Reset Password or Missing Privileges to Login

If you forgot the database user password you can follow this steps and reset it. From the MySql prompt execute:
UPDATE mysql.user SET Password=PASSWORD('EnterPassword') WHERE User='myuser';
FLUSH PRIVILEGES;
This will reset the password for the user 'myuser'.
If you forgot completely the 'root' password and you cannot get in the MySql prompt, please follow this steps:

Windows
  1. Shut down MySql or MariaDb service: Go to Start -> run -> services.msc -> MySQL -> right click and select STOP
  2. Create a text file reset.txt. Im my case I save it under "C:\Temp\reset.txt". In this file paste the following lines:
    UPDATE mysql.user SET Password=PASSWORD('EnterPassword') WHERE User='root';
    FLUSH PRIVILEGES;
    Replace "EnterPassword" with your new password. Also make sure that the UPDATE and FLUSH commands are on separate lines!

  3. Open the command prompt and navigate to your MySQL or MariaDb bin directory inside the program folder ("C:\Program Files\MySQL\MySQL Server 5.0\bin" or "C:\Program Files\MariaDb\bin")
    enter the following command
    mysqld.exe --init-file=C:\Temp\reset.txt --console
    This will replace your root password with the one you have set in the text file. The command may hang as the daemon continues to run. You can stop it ( Ctrl-C ) and start MySql again from Windows Services.
  4. Restart MySQL Service: Start -> run -> services.msc -> MySQL > right click and select START

Linux
  1. Log in as root and stop mysql service(daemon) by entering the command: service mysqld stop
  2. Start mysql service(daemon) and skip the grant tables which store the passwords by entering the command: mysqld_safe --skip-grant-tables
  3. Now you should be able to connect to mysql without a password.Run the following commands:
    mysql --user=root mysql
    
    update user set Password=PASSWORD('new-password') where user='root';
    flush privileges;
    exit
  4. Log in as root and start the mysql daemon by entering the command: service mysqld start

Enable Remote Connections and Grant Privileges

Remote access can be enabled during MySql installation. If you did not activate it, you can follow this steps.
Remote connections are checking two settings:
  1. The setting bind_address has to be disabled in my.ini or my.cnf
  2. The user you are using to connect has been created in MySql using a matching host, as described in introduction.
Here are presented this step by step:
Windows
  1. Open the command prompt by following this steps: Start -> run -> cmd -> press enter.
  2. Navigate to your mysql installation folder (Default: C:\Program Files\MySQL\MySQL Server 5.1\bin)
  3. Type in: mysql -u root -p
  4. GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
    FLUSH PRIVILEGES;
    • Replace PASSWORD with the root user password.
    • The command is written to enable remote access for user 'root'. This can be any other user name.
  5. To exit type: quit
  6. In the MySql Server installation directory there is a file called 'my.ini'. Edit this file and comment out the line 'bind-address = 127.0.0.1' by adding an '#'. The file should look like
    #bind-address = 127.0.0.1

Linux
The steps are the same as on Windows, the
  1. Login to the MySql server and edit the file /etc/my.cnf
    Comment out the row
    #bind-address=...
    and the row
    #skip-networking
  2. Restart the server using '/etc/init.d/mysql restart'
  3. Login on the server using 'mysql -u root -p mysql' and execute the statements below replacing the password :
    GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
    FLUSH PRIVILEGES;

Enable Connections to Database through Windows Firewall

The common reason for connectivity problems is the error 'Communications link failure'. This is because during the MySql installation the checkbox to add an firewall exception was not enabled. To enable it later do as follow :

Open Windows Firewall by clicking the Start button Picture of the Start button, and then clicking Control Panel. In the search box, type firewall, and then click Windows Firewall.
In the left pane, click Advanced settings. Administrator permission required If you're prompted for an administrator password or confirmation, type the password or provide confirmation.
In the Windows Firewall with Advanced Security dialog box, in the left pane, click Inbound Rules, and then, in the right pane, click New Rule.
Follow the instructions in the New Inbound Rule wizard. Make sure to select Add Port.
For the Port name use MySQL.
For the specific local port use 3306
Make sure to check off Allow the Connection
For When does this rule apply?
Put a check in Domain, private, and public.
Make sure to follow the directions above for creating an Outbound rule...basically the same way except you click Outbound Rules on the left pane.

SSL Connections

If you wish to use ssl encryption, append this '?useSSL=true&verifyServerCertificate=false' to the URL Pattern in the JDBC Driver Manager.

Common Error Messages

Exception: Communications link failure.The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
  1. 1Check if the MySQL Service on Server is Started

  2. 2Check the MySQL server port and hostname
    Test the connection using the PING button from the Connection Dialog.
    The default MySql port is 3306. However, this port can be changed for each MySQL instance.
    Check the server files my.ini or my.cnf, in the mysqld section, to see the configured port, for example, port=3306.
    Find out the computer name right clicking My Computer and selecting Properties

  3. 3Disable Firewalls on Server and Client
    Firewalls on client or server may block the connections. Configure them to accept incoming connections for MySql port or disable them.

  4. 4Check the JDBC Driver Version
    DbSchema includes one JDBC driver for MySql. If the driver is not an actual version,
    search the internet for 'download mysql driver' or look on http://dev.mysql.com/downloads/connector/j/
    You will probably download a file mysql-connector-java-x.x.x.zip.
    Decompress it and look for a file mysql-connector-java-x.x.x.jar. Upload this file in the in DbSchema JDBC Driver Manager.

Exception: Test Connection Failed java.sql.SQLException: null, message from server: "Host 'YOUR_IP' is not allowed to connect to this MySQL server" Your ip is not allowed to connect to the mysql server.
  1. 1 If the bind_address values is set to 127.0.0.1 in the MySQL configuration file, the MySQL server will not accept connections from remote hosts.
    The bind_address line in the MySQL configuration file (my.ini or my.cnf) can either be removed or commented out to allow remote connections.

Run MySql as Docker Container

A short intro can be found here. Install docker then run from docker prompt:
docker pull mysql/mysql-server:8.0
docker run --name=mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql/mysql-server:8.0
To get a MySql prompt:
docker exec -it mysql mysql -uroot -p
Now you can create further users in the database:
CREATE USER 'john'@'%' IDENTIFIED BY 'john';
GRANT ALL privileges ON *.* TO 'john'@'%';
FLUSH PRIVILEGES;
To drop the container:
docker stop mysql
docker rm mysql
docker system prune

Guided MySql Installation on Windows

Download MySql from http://dev.mysql.com/downloads/

During the installation you have to enter the password for the database user root. Also you may want to enable access to MySql from remote computers

Setup Type

Configure

Configuration Type

At this step set a firewall exception for MySql to allow remote computers to connect to MySql. The port number which is set here will be used also by DbSchema.

Instance Configuration

Server Instance

At this step you enter the root password and enable access to MySql database from remote computers :

Root Password

After installation you can enable MySql to write all its executed SQL statements to a log file. For this edit the Program Files/MySql/data/my.cfg and add log=<file>

MySql Statements

  • Create Database
    mysql -U root -p
    use &lt;schema&gt;;
    show tables;
    create database test;
    drop database test;
  • Change Table Engine to Myisam
    ALTER TABLE schema.table ENGINE=Myisam;