MySQL Service, Workspace, Users and Tables
Here we are with another sub-post related to the main original post that is aimed to describe how to interact with MySQL from C++ using mysql-connector (MySQL C++ with mysql-connector).
Because of the length of such post, given all the satellite configurations and tasks that need to be performed in order to achieve the goal, I decided to split it, generating few articles that are topic on their own.
This is one of them.
So, although this post is focused on installing and configuring MySQL, with few usage example, it is mainly intended to be seen in the context of the bigger picture represented by the main post: MySQL C++ with mysql-connector
I already have installed MySQL on my machine but I will follow the configuration sequence again to guide you through this process.
This means that some of the screens during the installation and the final one could differ from what you will, simply because this will be an additional installation on my side. This is actually even more interesting because I believe helps even better in understanding how this system works.
That said, let’s see how it’s done.
MySQL – Download
Let’s download it from here:
Then select the web version that will download the installer when you will execute the installer itself or the fully offline version. I opted for the second option here:
In a similar way as we downloaded the connector, we need to select to login or just download the file:
Here is where you could probably see a different number for your selected port.
I already have mysql installed and configured on port 3306, therefore I will use the suggested port 3307. Let’s keep this in mind when we will connect to it from C++.
Select a strong password if you intend to use this for important stuff as well:
For the same reason explained above, the service name will have a name that could be different from the one you will see in your installation.
There is no problem there, we just need to keep it in mind when we will use it later on.
My one will be named “MySQL56”:
At the end of the installation it should start immediately MySQL Workbench:
Create a DB – Connect to our MySQL Instance
It’s time to create our sample database before writing any code to connect to it (this is related to the other topic, but it is valid as an example to show how to use MySQL and the Workbench in particular).
So, open MySQL Workbench from your Windows Start Menu (if you clodes it):
When the workbench starts you will see your available connections.
They are two in my case (for the reason explained above), so just click on your instance:
and insert the password once requested:
If everything is OK, then you should not see any error (of course) and be presented with a screen similar to this:
Create a DB – Create a sample DB
Click on the forth icon to create a new schema in the current server:
Let’s call it “cpptest” and click “Apply”:
It should be now listed amongst the other schemas:
Time to add a table to it!
Create a DB – Adding a table
Expand the cpptest entry and right click on “Tables”, selecting then “Create Table…”:
Edit it giving it a name (“contacts” for example) and three fields.
Note that to add the fields you just need to double click into an empty row of the column list table.
The first field should be automatically configured, named “idcontacts”, set as primary key and with Datatype “INT”.
Check the “AI” (Auto Increment) check-box, accept it and edit the other two:
- name, VARCHAR(45)
- surname, VARCHAR(45)
Then click “Apply”:
Create a DB – Adding records to the table
To add some sample records to the table, right click on it and choose the entry:
“Select Rows – Limit 1000”:
and click “Apply”:
confirm the script clicking Apply and then Finish:
Repeat the process entering a series of names that you want to insert.
Note that the idcontacts column is automatically filled up for us (remember the Auto Increment flag):
As usual Apply and Finish.
We are Happy with our collection:
Create a DB – Adding records to the table
Time to create a new user that will have privileges to use this DB.
Click on the “Users and Privileges” Management option and enter your root password if required:
Click then on the “Add Account” button at the bottom of the just opened panel:
Fill-up the fields choosing a username (I used “cppuser”) and a password (as strong as you prefer). Click then Apply once ready:
Select the user from the left panel, open the “Schema Privileges” tab and click on “Add Entry…”:
In the new window select the “Selected Schema:” option and select our database “cpptest”, then click “OK”:
Select then all the options in the “Object Rights” column (SELECT, INSERT, etc.) and click “Apply”:
Job done here!
We can switch to Visual Studio for the code part (at last!)
It is time to go back to the main article: MySQL C++ with mysql-connector