Windows Tutorial for Installing Extracted MySQL 5.7. 18

1. Installation process

MySQL version: 5.7. 18

1. Configure my. ini file (simple configuration) and put it in the root directory of MySQL. The file path configuration here needs to be absolute path (double spelling is required to use backslash, and one slash is enough) (data folder does not need to be created by itself, but will be generated later)

[client]
default-character-set=utf8
[mysqld]
port=3306
character_set_server=utf8
basedir="D:\\mysql-5.7.18-winx64"
datadir="D:\\mysql-5.7.18-winx64\\data"
#  The character set used by the server defaults to 8 Bit coded latin1 Character set 
character-set-server=utf8
#  Default storage engine to be used when creating new tables 
default-storage-engine=INNODB
[WinMySQLAdmin]
D:\\mysql-5.7.18-winx64\\bin\\mysqld.exe

2 Configure environment variables and configure bin directory in Path

3 Initialize the database and generate the data folder and its 1 configuration files (the default password for the root account will be generated after initialization: in the xx. err file)

mysqld -initialize
# err Example file: 
[Note] A temporary password is generated for root@localhost: w1BI/g/y.wfx

4 Registration Services

mysqld -install

5 Start MySQL

net start mysql

6 Log in after startup and fill in the generated default password

mysql -uroot -p

7 Change the password of the account

set password for root@localhost=password('root');

8 Stop MySQL service

net stop mysql

9 If you want to delete MySQL service, you can have the following command to delete it

mysqld -remove

2. Post-installation problems

ONLY_FULL_GROUP_BY Issue

Errors are sometimes reported after use:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'col_user_6.a.START_TIME' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Reason: MySQL starts the mode of only_full_group_by by default, which can only obtain the field information affected by group by, and cannot coexist with other fields not affected by group by, or can only put the field of group by at the first place of select keyword, which is limited
Solution:

1) Direct sql solution: This solution has some limitations, that is, when the database is restarted, the only_full_group_by mode will still be started by default

SET @@global.sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

2) Permanently resolve: Under the my. ini file [mysqld], add the following conditions to filter out the only_full_group_by mode when MySQL starts

sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

3 Control scripts

Finally, write a control script, so that you can use command operations frequently when you use MySQL

cls 
@echo off
: Set the window font color 
color 0a 
: Set the window title 
TITLE MySQL Management program 

call :checkAdmin

goto menu
: Menu 
:menu
cls
echo. 
echo.=-=-=-=- Please select what you want to match MySQL Operation of -=-=-=-=-
echo.
echo.1:  Start MySQL
echo.
echo.2:  Shut down MySQL
echo. 
echo.3:  Restart MySQL
echo. 
echo.4:  Retreat   Out 
echo.
echo.=-=-=-=- Please enter the serial number of the project you want to select ↓ -=-=-=-
set /p id=
if "%id%"=="1" goto startup
if "%id%"=="2" goto shutdown
if "%id%"=="3" goto reboot
if "%id%"=="4" exit
pause

: Start 
:startup
echo.
call :checkMySQL 1
echo. Start MySQL......
net start "MySQL"
echo. Start MySQL Success! 
pause 
goto menu 

: Stop 
:shutdown
echo.
call :checkMySQL 2
echo. Shut down MySQL......
net stop "MySQL"
echo. Shut down MySQL Success! 
pause 
goto menu

: Restart 
:reboot
echo.
call :checkMySQL 2
echo. Shut down MySQL......
net stop "MySQL"
echo. Shut down MySQL Success! 
goto startup
goto menu

: Quit 
:goout
pause
goto menu

: Check MySQL Does the process exist 
:checkMySQL
set /a count=0
for /f "tokens=1 delims= " %%i in ('tasklist /nh ^| find /i "MySQL"') do (set /a count+=1)
if %count% neq 0 if "%1" equ "1" (
 echo  Warning: MySQL Started 
 goto goout
)
if %count% equ 0 if "%1" equ "2" (
 echo  Warning: MySQL Not started 
 goto goout
)

: Check whether you are running as an administrator 
:checkAdmin
echo test am i admin? > %SystemRoot%\System32\admin.hujunjie
if not exist %SystemRoot%\System32\admin.hujunjie (
 echo  Warning: Please run as an administrator! 
 pause
 exit
)
#  Here's xxxx You can set it yourself 
del %SystemRoot%\System32\admin.xxxx