Fixing MySQL Service Could Not Start, 1067 Errors

If you’ve unzipped mysql out to a folder and you set it up to run as a service, like so

C:\mysql5_x64\bin\mysqld-nt.exe --install-manual

You might try to launch the service from the Control Panel -> Administrative Tools -> Services Window. But if you’re getting the error message:

‘Could not start the MySQL service on Local Computer

Error 1067: The process terminated unexpectedly.’

Running from the command line:

C:\mysql5_x64\bin\mysqld-nt.exe --console

Should work fine. So what’s the problem? Well, you may be missing your my.ini file. Create the file (you can copy one of the defaults that come with it such as my-small.ini), and make the following changes by adding the basedir & datadir lines, so it looks something like this:

# The MySQL server
[mysqld]
###################################
basedir=C:/mysql5_x64
datadir=C:\mysql5_x64/data
###################################
port        = 3306
socket        = /tmp/mysql.sock

If you try to launch the service again, you should feel alot happier.

Some of the readers have put in some helpful comments, which I’ll include here

Take a look at your error log by looking in:

“C:\Documents and Settings\All Users.WINDOWS\Application Data\MySQL\”

Then click on the “MySQL Server X.X\” folder and then “data\”

Find the file [MY-DOMAIN-NAME].err and open it using notepad

Another possible cause of the 1067 error might be faulty my.ini settings.

…

So the general tip from me would be: Try to think of any reconfigurations you’ve done recently. That might be what’s causing this. —ChromaWoods

We were getting a 1067 error because (we think) we’d been unable to successfully complete a reinstall after mixing up the original root password [on the mysql server].

Our solution in the end was to _completely_ uninstall MySQL and reinstall. The tricky part is that the uninstall requires three steps:

1. Uninstall via Add/Remove Programs from the Control Panel.

2. Delete the Base directory — likely C:/Program Files/MySQL

3. Delete the Data directory — likely C:/Documents and Settings/All Users/Application Data/MySQL

Finding the Data directory may be a bit tricky, since the Application Data folder is hidden by default. If you change your Folder Options so you can view the Application Data folder and still cannot see it, type the path (from step 3) into Run from the Start Menu to open the folder.

Once you’ve uninstalled MySQL and deleted these two folders, you should be ready to start fresh!

…

Administrating MySQL From the Command Line

The GUI tools provided by the folk who make MySQL are very useful;

http://dev.mysql.com/downloads/gui-tools/5.0.html

So you’ve had a look, created a user account using the GUI tools, but you’re stuck and you can’t login. We’ll start this post by adding a user to your mysql server. Then we shall grant the login privileges needed for loggin in to your server from your hostname/ip-address.

The way I add users is like this. Download the GUI ‘mysql administrator’ tool and the ‘mysql query browser’. Use the query browser to login and create a new schema if you need one. To do this right click in the schema list box on the right hand side, and click create schema. Use the administrator tool to add your new user, and give the user appropriate rights to the schemas that users needs access to.

Check if the user account works by logging in using the query browser. If you want to do this from the command prompt, assuming you’ve installed mysql in C:\mysql5, run a command prompt (black dos window)

C:\> cd C:\mysql5\bin
C:\mysql5\bin>mysql.exe -u USERNAME -p SCHEMA_NAME
Enter Password: ********
mysql>

If you can’t login, make sure the schema name is correct. The problem may also be that you don’t have the right user privileges.

Try this to allow login from the host you’re using. Login using the query browser or command line as an administrator, such as root. We want to allow our user to connect to the server using a password from localhost. You can use any other hostname/ip-address you want instead of localhost/127.0.0.1. This can be done by running the following query. To do this in the query browser GUI  type the query below into the top box in between the green refresh and execute buttons, then press execute.

grant usage on SCHEMA.* to USERNAME@LOCALHOST identified by ‘PASSWD‘;

If you want to do this from the command line, it will look like this:

mysql> grant usage on SCHEMA.* to USERNAME@LOCALHOST
>identified by 'PASSWD';
Query OK, 0 rows affected (0.00 sec)
mysql>

You should now be able to login. If your login attempt still fails, the mysql program will give you a detailed error message, so you can find out what’s wrong.