the MySQL service on local computer started and then stopped

Question

the MySQL service on local computer started and then stopped. Some services stop automatically if they are not in use by other services or programs.

Can anyone resolve this issue? Thanks.

http://forums.mysql.com/read.php?11,638281,638401#msg-638401 — xlecoustillier, Feb 27 '16 at 14:11
Does this answer your question? [The MySQL57 service on Local Computer started and then stopped](https://stackoverflow.com/questions/37910680/the-mysql57-service-on-local-computer-started-and-then-stopped) — Nazim, Feb 11 '20 at 11:29
Please delete all the files in the data directory before you initialize. There could be error files in there that will stop you. — AturSams, Jan 30 '21 at 09:57
I ended up reinstalling the MySQL server that was running as this service — gawkface, Sep 29 '22 at 16:42

score 190 · Accepted Answer · edited Apr 02 '16 at 22:30

190

After installing it by mysqld --install. Try this mysqld --initialize. We got the same problem but it's now working.

edited Apr 02 '16 at 22:30

Tunaki

132,869
46
340
423

answered Apr 02 '16 at 22:27

Alexa

2,201
1
10
15

16

@Tunaki. We need to open command prompt as an Administrator, to execute mysqld --install and mysqld --initialize commands. – Pankaj Shinde Apr 26 '16 at 07:28
2

if simple is not working, than: bin\mysqld --initialize-insecure (and latare add root password) – danielpopa Oct 01 '17 at 12:53
3

After using mysqld --initialize search for root password in MySQL error log file, the line would look like this: A temporary password is generated for root@localhost: Ok+ju;UT8%ED – Alex Khimich May 10 '19 at 15:20
I had used this `MYSQL80` service before. Somehow it's not starting, I usually start it manualy from Services but now its not functional from there – Waseem Ahmad Naeem May 13 '19 at 17:44
2

opened `cmd` as administrator, executed the commands `mysqld --install` and `mysqld -- initialize`. Then opened `mysql` in services and started the service. It worked this way – Suresh Jan 31 '20 at 18:15
After trying all of the above, the service still wouldn't start. I had to also manually create an `Uploads` subdirectory under my `data` subdirectory. Then, it started! – tonypdmtr May 02 '20 at 13:28
This is a one-shot solution to clear the my.ini file, check other solutions for specific solutions within the file – Raul Lapeira Herrero May 12 '22 at 06:23
Can't believe wamp didnt even include executing ---initialize on first run. – chitgoks Mar 18 '23 at 06:35

jhersey29 · Answer 2 · 2019-04-04T15:46:36.340

79

If using version 8 and you edit the my.ini I found that Notepad is putting 3 hex characters at the beginning of the my.ini file. EF BB BF. Deleting the 3 characters from the beginning of the file in a hex editor fixes the problem.

In version 8 they are accidentally putting Unicode characters in the ini file. This is causing Notepad to save the file with Byte order mark characters.

The following line in the file is the culprit "The line # range from 1 to 2^32 − 1. “Unique” means that each ID must be different." has 3 Unicode characters. This is causing notepad to append the byte order mark to the text file.

edited Apr 04 '19 at 15:46

answered Apr 04 '19 at 14:52

jhersey29

959
6
6

1

Those three bytes called BOM https://en.wikipedia.org/wiki/Byte_order_mark. Stupid notepad for inserting that without asking, stupid MySQL for putting unicode chars in a windows config file, which is very likely to be edited by notepad. – Zar Shardan Mar 30 '20 at 23:25
My heart jumped out of my chest when the server went down. Thank you for helping! Stupid notepad! – user2219963 Mar 31 '23 at 20:45

score 42 · Answer 3 · answered Aug 15 '16 at 18:39

42

Also remember to give NETWORK SERVICE permission to the folder:

Right-click Data folder
Select Properties
Select Security tab
Click Advanced
Click Change Permissions...
Click Add...
Type NETWORK SERVICE
Click Check Names
Click OK
Select Full Control
Click OK – four times
Start MySQL service

answered Aug 15 '16 at 18:39

Warwick

925
1
10
22

5

2 hours almost and setting up permissions in windows did the trick, didn't thought this could be a problem as no such indication was there at all. thanks a lot buddy – Zaffar Saffee Oct 28 '17 at 20:43
I also had to make sure that MySQL service is "Network Service" account after this. – Nae May 19 '20 at 21:29
1

especially important when moving the datadir – CodeMonkey Oct 04 '21 at 09:54
Thankyou Thankyou Thankyou, I was tearing my hair out trying to work out why even giving the folder all permissions for EVERYONE still didn't allow me to restart the MySQL service but just adding permissions for NETWORK SERVICE did the trick. I followed other posts instructions exactly over and over without success until I noticed your comment and link to this post and applied your instructions and IT WORKS!, your my savior, thankyou so much :) – David Crawford Mar 12 '22 at 13:18

score 14 · Answer 4 · edited May 26 '17 at 05:52

14

In my case, I tried to open a DOS prompt and go to the MySQL bin\ directory and issue the below command:

mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server 5.0\my.ini" --standalone --console

And it shows me I was missing the "C:\Program Files\MySQL\MySQL Server 5.0\Uploads" folder; I built one and problem solved.

edited May 26 '17 at 05:52

Dr.jacky

3,341
6
53
91

answered May 26 '17 at 04:31

Han Xiong

171
1
6

After messing with lots of permission settings it turned out I had an incorrect drive letter in my ini/cnf file. This answer helped me. – PadraigD Jul 09 '19 at 16:12
**On using above query, Try1**, It showed me that - "Operating system error number 32 in a file operation. [ERROR] [MY-012615] The error means that another program is using InnoDB's files. This might be a backup or antivirus software or another instance of MySQL.". **On using above query, Try2**, it Started crash recovery and started successfully :) – Himalaya Garg Jan 12 '20 at 09:53
1

This worked well to give us detailed errors to allow us to solve our problem, except our "my.ini" file is in the "ProgramData" directory path instead of the "Program Files" directory path `mysqld --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.0\my.ini" --standalone --console` – Abel Wenning Jul 12 '21 at 23:26

score 11 · Answer 5 · answered Sep 20 '19 at 00:46

Using Community 8.0.17, upgraded from 8.0.16

There may be a more secure way of doing this, but since I'm just running a dev box:

navigate to the \data folder
make SURE the \data folder is EMPTY. If files exist the --initialize option will fail
give SYSTEM Full Control permissions on the \data folder
go back to your command prompt
run mysqld --initialize

you should see all of the required data file structures in the data folder now. If they are not created, the mysqld process has contention or improper security to write into the folder correctly.

Now start your named service in whatever fashion you want (SC/NET/service.msc, etc)

Assuming all of the files were created properly by --initialize the service will start.

Thank you! Emptying "data" folder and running mysqld --initialize, after that, did the trick for me. — prubini87, Jun 21 '21 at 08:57

score 7 · Answer 6 · answered Nov 01 '18 at 09:13

7

Open Service
Right click on MYSQL service
select Log on
check local system account
start service

answered Nov 01 '18 at 09:13

Maninderpal Singh

69
1
1

delete your duplicate answer below. – Farhana Naaz Ansari Nov 01 '18 at 09:34

score 5 · Answer 7 · answered Nov 28 '18 at 17:15

5

This may be because of changing lower_case_table_names after a server has been already initialized.

From the docs:

lower_case_table_names can only be configured when initializing the server. Changing the lower_case_table_names setting after the server is initialized is prohibited.

The solution to this problem is to set lower_case_table_names parameter at server installation as described in the following answer:

Can't set lower_case_table_names in MySQL 8.x on Windows 10

answered Nov 28 '18 at 17:15

vter

1,881
1
20
38

This is exactly my situation since I need the `lower_case_table_names` set to 2 to work with Prisma migration tools. Had to uninstall then reinstall everything. Fortunately I don't have a lot of data. – Bao Huynh Lam Mar 26 '23 at 03:28

score 4 · Answer 8 · answered Apr 06 '20 at 10:26

I see this error when I install MySQL 8.x, edit the max_allowed_packet in the my.ini file, and then restart mysql on a Windows 10 machine. My recommendation, to avoid this issue, is to edit the my.ini file in a hex editor (something like Frhed). DO NOT USE NOTEPAD. It does something to the hex layout of the .ini file.

PhuVu94 · Answer 9 · 2020-05-06T01:52:23.830

Follow the below instruction as below to save your day:

If you modify my.ini not correct, you will find the trouble "the MySQL service on local computer started and then stopped". So you need to check your my.ini in "C:\ProgramData\MySQL\MySQL Server 8.0\my.ini" again to make sure there is no strange command. If you don't know and want to restore to original, you can search on web with key word "my.ini configuration", download the latest and copy it to "C:\ProgramData\MySQL\MySQL Server 8.0\my.ini"

For whom delete the SQL service on Window or Linux, You can follow these step below:

(Window)

Delete Current Service

You need to stop MySQL80/MySQLXX in Service before carry out the action below Open commandLine of Window and run as administrator

sc delete mysql80 <= If you use mysql80, it will delete MySQL80 in Service

sc delete mysql <= If you use "mysql --initliaze", it will delete MySQL in Service

The command mysqld --initialize just create MySQL in Service of Window or Linux

So when you check it Path Executable, it misses my.ini so you can't use you MySQL Server although MySQL Service is working

For MySQL80

"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe" --install MySQL80 --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini"

For MySQLXX

"C:\Program Files\MySQL\MySQL Server X.X\bin\mysqld.exe" --install MySQLXX --defaults-file="C:\ProgramData\MySQL\MySQL Server X.X\my.ini"

=> It will create MySQLXX Service working with my.ini

Then MySQL works normally and you won't need to install your MySQL again.

I don't use Linux or Mac so I can't instruct - But you can find the answer above for reference

score 3 · Answer 10 · answered Jun 16 '20 at 15:06

This problem is so complicated and depend on MySQL Version.My problem was happened on MySQL 8.0.2. When I've config my.ini file on notepad and save it.I soleved following @jhersey29 solution https://stackoverflow.com/a/55519014/1764354 but little different approch.

my solution is restore my.ini file and edit config value via Option files on WorkBench Application. workBench Menu Example

score 2 · Answer 11 · answered Oct 16 '18 at 08:10

2

Search for services.msc and look up through your services that are running if there is a mysql service running already other than the one you want to run (it could be xampp or wamp) or another service (for example Skype) using the same port as mysql and stop the service so you can run your mysql service.

answered Oct 16 '18 at 08:10

Simbarashe Mupfururirwa

355
2
12

MySql on a godaddy VPS stopped loading. The MariaDb that comes with the server either turned itself back on or I never actually disabled it and MySQL managed to grab the port first for a year or so, until it didn't. Sometimes the error is exactly what it says it is. You are exactly right in that it could just be the port actually being in use. – user1544428 Jul 15 '22 at 18:15

Alex Khimich · Answer 12 · 2019-05-10T15:22:55.440

2

This error happened in my case when secure-file-priv was pointing to unexistent folder, make sure it exists and readable.

The line of code example in my.ini: secure-file-priv="D:/MySQL/uploads"

edited May 10 '19 at 15:22

answered May 10 '19 at 15:17

Alex Khimich

788
9
10

Yes, same here, this is the problem if you're moving the data directory of newer mysql and in the process moved uploads directory too. – J Rao Dec 14 '19 at 04:55

Srihari Karanth · Answer 13 · 2020-12-04T03:59:21.847

2

You also might have accidentally added some wrong text in my.ini file. Make sure no invalid character is added at the beginning of the file.

edited Dec 04 '20 at 03:59

answered Jun 20 '19 at 06:45

Srihari Karanth

2,067
2
24
34

1

Thank you, this got me on the right track... Apparently editing the my.ini file in Notepad will break the file. I made the same edits to the file in Visual Studio Code instead and it worked just fine. – justanotherguy Dec 03 '20 at 20:21
I edited the bind-address too with notepad and it starts to have a start-stop problem. Did you have the same problem with bind-address ? Just to know if Visual Stufio Code could also be the solution in my case. – user2305415 Jan 23 '22 at 19:47

score 2 · Answer 14 · answered Jul 25 '19 at 13:07

Nothing was working for me but then I checked here. I ran that command qc sc mysql57 and copied the value of BINARY_PATH_NAME from it. After that I checked this and changed the value of lower_case_table_names from 0 to 2 in my.ini file. Then in the command prompt, I ran this command - << BINARY_PATH_NAME >> --install-manual. After that, I started the MySQL57 service and it worked.

score 1 · Answer 15 · answered Apr 17 '18 at 07:32

1

Rename or delete the folder under,

C:\ProgramData\MySQL\MySQL Server 5.7

folder name change as per your MYSQL version number.

answered Apr 17 '18 at 07:32

john sooraj xavier

21
4

Aris · Answer 16 · 2018-11-24T08:18:31.167

I had this issue after my database was working fine for long time. It turned out it was some data corruption.

In the error log I had:

2017-02-07T10:11:42.270567Z 0 [ERROR] InnoDB: Ignoring the redo log due to missing MLOG_CHECKPOINT between the checkpoint 44002250712 and the end 44002250240.
2017-02-07T10:11:42.270606Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2017-02-07T10:11:42.577436Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2017-02-07T10:11:42.577470Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2017-02-07T10:11:42.577484Z 0 [ERROR] Failed to initialize plugins.
2017-02-07T10:11:42.577488Z 0 [ERROR] Aborting

Then I had to delete the 2 ib_logfile* files, and it restarted again.

`mysqld --initialize` doesn't work if there exist databases already. This solution worked for me. — Miron, May 21 '21 at 02:36

score 1 · Answer 17 · answered Mar 07 '21 at 21:21

1

my.ini file should save as (Encoding) ANSI not UTF-8 or others files.

answered Mar 07 '21 at 21:21

user5816728

61
1
3

score 1 · Answer 18 · answered Mar 29 '21 at 02:41

1

I have faced the same situation. I have changed the server-id=2 and when saved got this error. After a lot of research, I have found the solution. In windows after

changing my.ini mysql configuration file, save the file as ANSI rather than UTF-8

This has worked for me well.

answered Mar 29 '21 at 02:41

r Reddy

49
5

Thank you for this suggestion this worked for me. – sanki May 19 '21 at 19:07

score 1 · Answer 19 · answered Apr 17 '21 at 10:10

1

another instance of mysql like 5.6 or 8 run at the same time

and the 80 port was reserved

please > go to >> services>> stop all another instance >> disable them

in my case problem solved and service started as well

answered Apr 17 '21 at 10:10

saber tabatabaee yazdi

4,404
3
42
58

score 1 · Answer 20 · answered Sep 12 '21 at 05:32

Go to "C:\ProgramData\MySQL\MySQL Server 8.0\Data\[machine name].err" and check the last rows - it should tell you why it failed. In my case it was duplicated tablespace id between databases.

It looks like this:

2021-09-12T05:19:22.815152Z 0 [Warning] [MY-010915] [Server] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2021-09-12T05:19:22.818357Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.22) starting as process 30664
2021-09-12T05:19:22.866774Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-09-12T05:19:22.908983Z 1 [ERROR] [MY-012209] [InnoDB] Multiple files found for the same tablespace ID:
2021-09-12T05:19:22.911667Z 1 [ERROR] [MY-012202] [InnoDB] Tablespace ID: 124 = ['db1\accounts.ibd', 'db2\accounts.ibd'] 
2021-09-12T05:19:22.915514Z 1 [ERROR] [MY-012930] [InnoDB] Plugin initialization aborted with error Failed, retry may succeed.
2021-09-12T05:19:22.918627Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2021-09-12T05:19:22.927714Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2021-09-12T05:19:22.930912Z 0 [ERROR] [MY-010119] [Server] Aborting
2021-09-12T05:19:22.934432Z 0 [System] [MY-010910] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: Shutdown complete (mysqld 8.0.22)  MySQL Community Server - GPL.

This solved my problem. In my case I had a copy of a table that I did manually. When restarting the MySQL service there was duplicated tablespace ID. Thanks @Qua285. — f4d0, Jun 14 '23 at 18:36

score 1 · Answer 21 · answered Mar 26 '22 at 20:54

1

Changing xampp folder's permission in windows to full control for 'authenticates user' fixed my problem.

answered Mar 26 '22 at 20:54

user13960527

47
2

score 0 · Answer 22 · answered Jan 10 '18 at 15:38

In my case, mysqld was starting and stopping with no error message. I needed to open command prompt using "Run as Administrator", and then run mysqld.

I am only doing this for temporary development. I would not recommend running MySQL as an administrator in any case.

This was the final step after the above troubleshooting.

Deepanshu · Answer 23 · 2020-04-29T16:02:58.333

0

The same problem happened with me also, nothing worked... I first deleted the service (in my case MySQL80 and MySQL) by command:

sc delete MySQL80
sc delete MySql

and then reinstalled MySQL. Mine was MySQL 8.0. And then everything was back to normal.

edited Apr 29 '20 at 16:02

answered Aug 26 '19 at 11:57

Deepanshu

141
1
5

score 0 · Answer 24 · answered Feb 20 '20 at 08:45

0

Delete or rename the 2 following files from "C:\ProgramData\MySQL\MySQL Server 5.7\Data": ib_logfile0 ib_logfile1

answered Feb 20 '20 at 08:45

Valantis Stergiopoulos

399
3
11

score 0 · Answer 25 · answered May 06 '20 at 01:26

the MySQL service on local computer started and then stopped

This Error happen when you modified my.ini in C:\ProgramData\MySQL\MySQL Server 8.0\my.ini not correct.

You can search my.ini original configuration or checking yours to make sure everything is OK

score 0 · Answer 26 · answered Sep 11 '20 at 04:32

If you came across this while installing WAMP64 on Windows 10. Check if data folder in MySQL folder is missing. My WAMP 3.2.3 version installation failed to create such a folder. So wamp icon was orange (1 of 2 services running).

A quick fix was to run this command from MySQL location.

C:\wamp64\bin\mysql\mysql5.7.31\bin\
mysqld --initialize-insecure

score 0 · Answer 27 · answered Oct 11 '20 at 09:42

0

non of the above actually works so delete mysql,reinstall mysql,restore back if any...enjoy

answered Oct 11 '20 at 09:42

Pushkar Shrivastava

79
1
3

score 0 · Answer 28 · answered Dec 06 '20 at 17:49

I had the exact same message as displayed in the question. I have tried many suggestions with little luck. I was about to install WAMP (I'm on Windows) and was about to uninstall MySql 8.0 first, when I noticed a program included in the installation: 'MySQL Installer - Community' so I tried that. With this installer I set up a new connection, added password to root, added another user with password, set my choice of locations for all logs and ran the install. The result was that it now totally works.

score 0 · Answer 29 · answered Jan 28 '21 at 03:14

Such a problem can very often come due to configuration errors.

Vivek Sethi mentioned 20th December 2012 that it is possible to get logging in the console by running the following command:

C:\Windows\system32>"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld" --defaults-file="C:\Program Files\MySQL\MySQL Server 5.5\my.ini" --standalone --console

Right-clicking on my service in Windows 10, I recognized the --defaults-file= parameter, and with console logging I was able to see what was wrong in my configuration. In my case the path to datadir= and secure-file-priv= was wrong. I hope others can be empowered as well by being able to see logging details in the console. Good luck!

score 0 · Answer 30 · answered Sep 06 '21 at 05:52

0

make sure your data folder is fully empty before use command mysqld --initialize

answered Sep 06 '21 at 05:52

user3709120

47
4

score 0 · Answer 31 · answered May 12 '22 at 06:22

0

This happened in my install when I added the parameter

audit-log = ON

On the my.ini file, add to remove it for it to work

answered May 12 '22 at 06:22

Raul Lapeira Herrero

891
5
19

score 0 · Answer 32 · answered Dec 26 '22 at 07:11

0

I had to restart my pc :|

After restarting pc, start MySQL service manually.

(I don't know the real issue)

answered Dec 26 '22 at 07:11

Lojith Vinsuka

906
1
10
8

score 0 · Answer 33 · answered Apr 06 '23 at 10:33

0

In mycase how this worked.

Enter the following folder: C:\ProgramData\MySQL\MySQL Server 8.0\Data
Check for folder #innodb_redo
Empty the above folder
Restart the service and MySQL80 has started now.

I hope the above answer helps for someone.

answered Apr 06 '23 at 10:33

user503285

125
3
14

It did not work. Additionally, on your bullet point you mention "Restart", when this issue is related to an SQL server that is stopped, not running. You cannot restart something that is off. – f4d0 Jun 14 '23 at 18:33
Yeah I agree..its a start. Restart became common in Windows world. – user503285 Jun 19 '23 at 13:04

score 0 · Answer 34 · answered Apr 23 '23 at 12:46

cmd>mysqld --install
cmd> mysqld --initialize
Go to services-> mysql - start the service. In case there is still error, go to mysql-8.0.32-winx64\data directory.There will be an error file with .err extension.

In my case, it said, there are alreay files under /data directory. So, I leaned this up, and re-run
cmd> mysqld --initialize followed by services->mysql - start the service

score -1 · Answer 35 · answered Jun 15 '18 at 19:19

-1

mysqld --initialize

Run the above after the install command. Then try to start the service - that should work.

answered Jun 15 '18 at 19:19

PraSanVar

1

score -2 · Answer 36 · answered Jun 03 '19 at 23:43

-2

If you have changed data directory (the path to the database root in my.ini) to an external hard drive, make sure that the hard drive is connected.

answered Jun 03 '19 at 23:43

Ehsan

1
1

Hello Ehsan. The question didn't mention that they are using an external drive. So, it is unlikely this is the problem. – Diego Malone Jun 04 '19 at 00:11

the MySQL service on local computer started and then stopped

36 Answers36

Linked