[JAVA] MySQL migration memo (1)

Due to the influence of replacement of my home PC, I decided to transfer MySQL (mainly used for self-study etc.) stored in the PC between machines. Since it's a big deal, I'll leave a note of what I've done.

0. Premise

・ Simple migration from Windows10 + MySQL5.6 to Windows10 + MySQL5.6. (It's not surprising that I should have upgraded the version, but that's another opportunity.) -Make it available for Java programming. -Partially change the folder structure of the data referenced by MySQL.

1. Installation

1.1 Preparation

1.1.1 MySQL 5.6 installer download

https://dev.mysql.com/downloads/windows/installer/5.6.html Download the installer from.

The following two types of installers are prepared, but it seems that they can do the same thing. · Mysql-installer-web-community-5.6.45.0.msi · Mysql-installer-community-5.6.45.0.msi

A little research shows that the difference between the two is -Only the minimum required items are packaged, and go to download the necessary items when executing the installation (for web-community). ・ All in One (community)

• I used the web-community to proceed with the installation.

1.1.2 Microsoft Visual C ++ 2010 Redistributable Package (x64) Installation

I needed it when I introduced the Server function, so https://www.microsoft.com/ja-jp/download/details.aspx?id=14632 Download and install the installer from.

1.2 MySQL installation

Double-click the downloaded installer to start it. Below, install according to the wizard. 1: Select the Server feature and follow the wizard to install MySQL 5.6. 2: Select MySQL Connectors → Connector / J and follow the wizard to install the latest version of Connector / J 5.1.

1.3 PATH setting

In PATH "C:\Program Files\MySQL\MySQL Server 5.6\bin" To add.

2. Change startup settings

It is registered as a Windows service (MySQL56) at the time of installation, but some changes have been made to this content.

2.1 Service configuration check

Right click on the Windows icon → Select "Computer Management (G)" → Select "Services and Applications" in the left pane that appears → Select "Service" → Double-click "MySQL56"

→ Check the "Executable file path" slightly above the center of the file on the "General" tab.

The path of the referenced ini file is --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" Was set. There is no problem as it is, but I changed it to another place because of my personal taste and work efficiency.

2.2 Service configuration change

What I want to do: For MySQL that starts as a Windows service, change the path of the ini file that is referenced at startup.

At least the way

1. Edit the service configuration with the sc command
2. Edit the registry There seemed to be two ways, but since I have never used the sc command, I decided to do it in "2. Edit Registry".

2.2.1 Procedure

After stopping mysql

Right click on the Windows icon → Run by specifying the file name → Enter "regedit" and press the return key → Select in the following order 　HKEY_LOCAL_MACHINE－SYSTEM－CurrentControlSet－Services－MySQL56 → Select "Image Path" and double-click

→ Edit "Value data" and change the ini file name specified in defaults-file to the file you use. → Press the "OK" button

→ Click the "x" button at the top right of the registry editor to exit the registry editor → Restart Windows

2.2.2 Reference

If you do it with the sc command, sc config MySQL56 BINARY_PATH_NAME=... I wonder if I was able to change the settings with.

C:\windows\system32>sc qc MySQL56
[SC] QueryServiceConfig SUCCESS

SERVICE_NAME: MySQL56
        TYPE               : 10  WIN32_OWN_PROCESS
        START_TYPE         : 2   AUTO_START
        ERROR_CONTROL      : 1   NORMAL
        BINARY_PATH_NAME   : "C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" MySQL56
        LOAD_ORDER_GROUP   :
        TAG                : 0
        DISPLAY_NAME       : MySQL56
        DEPENDENCIES       :
        SERVICE_START_NAME : NT AUTHORITY\NetworkService

3. Environment settings for Java

3.1 Install the latest version of Connector / J 5.x series

Install the latest version of Connector / J 5.x series using the installer. In C: \ Program Files (x86) \ MySQL \ Connector J 5.1
mysql-connector-java-5.1.48.jar mysql-connector-java-5.1.48-bin.jar Etc. are created.

3.2 CLASSPATH setting

In CLASSPATH "C:\Program Files (x86)\MySQL\Connector J 5.1\mysql-connector-java-5.1.48-bin.jar" To add.

X. Bonus

If MySQL startup error fails

-When trying to start from the GUI of the Windows service

-When trying to start with the net start command from the DOS prompt

C:\windows\system32>net start mysql56

Start the MySQL56 service...
The MySQL56 service could not be started.

A system error has occurred.

System error 1067 has occurred.

The process was forcibly terminated in the middle.

Either way, I don't know what the error is.

In such a case, if you try to start it with the mysqld command instead of using the Windows service, you can see the error content.

C:\>mysqld
2019-10-12 13:35:37 0 [Note] mysqld (mysqld 5.6.45) starting as process 10532 ...
2019-10-12 13:35:37 10532 [Warning] Can't create test file D:\mysql\dat\DESKTOP-OHGJD7Q.lower-test
2019-10-12 13:35:37 10532 [Warning] Can't create test file D:\mysql\dat\DESKTOP-OHGJD7Q.lower-test
mysqld: Can't change dir to 'D:\mysql\dat\' (Errcode: 2 - No such file or directory)
2019-10-12 13:35:37 10532 [ERROR] Aborting

2019-10-12 13:35:37 10532 [Note] Binlog end
2019-10-12 13:35:37 10532 [Note] mysqld: Shutdown complete

The migration work is still in progress, but I think I was able to write a good article, so I will publish it here.

The continuation MySQL migration memo (2) Will be posted on.