1 Install MySQL

This section describes the installation of MySQL on Windows(64-bit) and connection from R, but versions are available for Mac, Linux, and several other operating systems.

1.1 Step 1: download MySQL

Download MySQL from https://dev.mysql.com/downloads/mysql/. Then click on “Download”.

Choose the zip Archive according to your Windows architecture, 32 bit or 64 bit. In this tutorial, I use the 64-bit version. Then, on the next page, if you don??t want to login/register just click the link No thanks, just start my download

1.2 Step 2: extract the files

Extract the downloaded zip file. For example, it can be extracted into a directory of “C:\mysql-8.0.16-winx64”. The extracted files look like the following figure:

1.3 Step 3: create a configuration file

MySQL provides several configuration methods, but, in general, we can create a my.ini file in the “C:\mysql-8.0.16-winx64” folder. The content of my my.ini file looks like the following:

     [mysql]
     default-character-set=utf8

     [mysqld]
     port = 3306
     basedir=C:\mysql-8.0.16-winx64
     max_connections=20
     character-set-server=utf8
     default-storage-engine=INNODB

Remember to change these folder locations if you have installed MySQL elsewhere.

1.4 Step 4: Customize the PATH for MySQL Tools

Setup environment variables by adding path C:\ mysql-8.0.16-winx64:
- On the Windows desktop, right-click the My Computer icon, and select Properties.
- Next select the Advanced tab from the System Properties menu that appears, and click the Environment Variables button.
- Under System Variables, select Path, and then click the Edit button. The Edit System Variable dialogue should appear.
- Add path C:\ mysql-8.0.16-winx64.

1.5 Step 5: Installation for the first time

Before runing MySQL in the first time, it should be installed.
installation
- Get into directory C:\mysql-8.0.16-winx64 and type mysqld -install in the command line
```
  cd C:\mysql-8.0.16-winx64
  mysqld -install
```
- Such as the following figure

1.6 Step 6: Start MySQL

Type net start mysql in the command line

 cd C:\mysql-8.0.16-winx64
 net start mysql

Such as the following figure

Then, type mysql -u root -p. The following figure is shown.

NOTE: When you type mysql -u root -p in the command , you will see ‘Enter password’. Press enter in the first time.

1.7 Step 7: Change the root password

The MySQL root user is an all-powerful account that can create and destroy databases. If you are on a shared network, it is advisable to change the default (blank) password. From the mysql> , type:

 use mysql:
 ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Your New Password'; 
 FLUSH PRIVILEGES;

1.8 How to check databesis

Type show databases; on the command line.

2 Install the ODBC drivers

2.1 Download Connector/ODBC

2.2 Installing the Windows Connector/ODBC Driver Using an Installer

The MSI installer package offers a very simple method for installing the Connector/ODBC drivers. Follow these steps to complete the installation:
1. Double-click on the standalone installer that you extracted, or the MSI file you downloaded.
2. The MySQL Connector/ODBC Setup Wizard starts. Click on the Next button to begin the installation process.
3. After accepting the licensing agreement, choose the installation type. The Typical installation provides the standard files needed to connect to a MySQL database using ODBC. The Complete option installs all the available files, including debug and utility components. Oracle recommends choosing one of these two options to complete the installation. If you choose one of these methods, click Next, then proceed to step 5.
  You can also choose a Custom installation, where you select the individual components to install. If you choose this method, click on Next, then proceed to step 4.
4. If you have chosen a custom installation, use the popups to select which components to install, then click on Next to install the necessary files.
5. If you get an error message “Error 1918” during the installation, it means that you do not have the required Microsoft Visual C++ 2013 Redistributable Package installed. See the discussion here for details. Install the package before you click Retry and continue.
6. Once the files are copied to their final locations and the drivers registered with the Windows ODBC manager, the installation is complete. Click on Finish to exit the installer.

3 How to create MySQL ODBC Connection on Windows

On this tutorial, you will see how to create an ODBC Connection to MySQL Server. Windows 10 is used.
First, make sure you had the following in hands.
- MySQL Server IP address or hostname.
- MySQL Server user and password.
Steps to create MySQL ODBC Connection.
- Open Control Panel and go to Administrative Tools. Click ODBC Data Sources (32 bit or 64 bit) depends on your system.
- On the User DSN tab, click Add and then select MySQL ODBC Driver. Make sure MySQL ODBC Driver is listed on the data source driver list. If not listed, check your MySQL ODBC Connector installation.
- Click on Finish and you will be asked to enter the MySQL ODBC configuration. For example
- Click on Test, if all correct, you should now have a working MySQL ODBC Connection. You can use this connection on other software such as R, Microsoft Excel and many others.

Installation of MySQL and connection from R

Xu Liu