MySQL installation process problem summary and processing method

When you first install and run MySQL, you may encounter some errors that make the MySQL server unable to start. The purpose of this document is to help diagnose and correct these errors.

First, when installing MySQL, please shut down the firewall and antivirus software, because the firewall and some antivirus software will cause MySQL installation, configuration failure, so as not to start the MySQL service.

The first information when troubleshooting a server problem is the error log. The MySQL server uses error logs to record information that the server cannot start. The error log is located in the data directory specified by the My.ini file. The default data directory is located in C:\Program files\mysql\mysql Server 5.1\dat.

Another source of error-related information is the message displayed on the console when the MySQL service starts. After installing mysqld as a service, check the MySQL service startup-related error message from the command line via the net start MySQL command.

The following is the most common error message that you will encounter when you first install and start the MySQL service:

1. System error 1067 has occurred.

Fatal Error:can ' t open privilege tables:table ' mysql.host ' doesn ' t exist

These messages appear when the MySQL server cannot find the MySQL permissions database or the critical files. This problem is typically encountered when the MySQL base or data directory is not installed in the default location (C:\mysql and C:\Program files\mysql\mysql Server 5.1\data), but is installed to a different location.

One scenario in which the problem occurs is to upgrade or install MySQL to a new location, but the configuration file is not updated to correspond to the new installation location. It may also be a new and old profile conflict, and when you upgrade MySQL, be sure to delete or rename the old configuration file.

If you do not install MySQL into the C:\Program files\mysql\mysql Server 5.1 directory, but instead install it in a different directory, you need to let the MySQL server know the installation directory by using a configuration (my.ini) file. The My.ini file should be located in the WINDOWS directory, typically C:\WINNT or C:\WINDOWS. You can enter the following command at the command prompt to determine the exact location from the WINDIR environment variable Value:

C:\> Echo%WINDIR%

You can create and modify an options file by using a text editor, such as Notepad. For example, if MySQL is installed under E:\mysql and the Data directory is in D:\MySQLdata, you can create an option file and set [mysqld] to specify the values of the Basedir and DataDir parameters:

[Mysqld]

# set Basedir to your installation path

Basedir=e:/mysql

# set DataDir to the location of your data directory

Datadir=d:/mysqldata

Note that you should use a (forward) slash instead of a backslash to specify the Windows pathname in the options file. If you use a backslash, you must use a double slash:

[Mysqld]

# set Basedir to your installation path

Basedir=c:\\program Files\\mysql\\mysql Server 5.1

# set DataDir to the location of your data directory

Datadir=d:\\mysqldata

If you change the DataDir value in the MySQL configuration file, you must move the contents of the existing MySQL data directory before restarting the MySQL server.

2. InstallationMySQLmay be generated whencannot create Windows service for mysql.error:0

Error, the cause of the error is mostly caused by reinstalling MySQL or upgrading to MYDQL using the MySQL Configuration Wizard.

You can use a service name other than MySQL, such as MYSQL11, etc. but this is not the best solution, we can use the Windows SC program to remove the MySQL service.

C:>SC Delete MySQL

[SC] DeleteService SUCCESS

Then re-use the MySQL Configuration Wizard, there will be no such error.

It's possible that this step is over. But it can't be started.

In general, after uninstalling the previous MySQL service, remember to restart the server.

And then install it. Generally, there will be no such mistake. L

As long as you delete the corresponding service in the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services ...

3. InWindows, aRootaccount used to connect from the local computerMySQLserver, and the other allows connections from any host.

You can specify a password for the root account in several ways. Three ways are described below:

• Use the Set Password statement

• Using the Mysqladmin command-line client program

• Use the UPDATE statement

To specify a password using Set password, connect to the server with root and execute two set password statements. Be sure to use the password () function to encrypt the password.

Statements in Windows:

shell> Mysql-u Root

mysql> SET PASSWORD for ' root ' @ ' localhost ' = PASSWORD (' newpwd ');

mysql> SET PASSWORD for ' root ' @ '% ' = PASSWORD (' newpwd ');

Statements in Unix:

shell> Mysql-u Root

mysql> SET PASSWORD for ' root ' @ ' localhost ' = PASSWORD (' newpwd ');

mysql> SET PASSWORD for ' root ' @ ' host_name ' = PASSWORD (' newpwd ');

Replace the HOST_NAME in the second set password statement with the server host name. This is the hostname that specifies the password for the anonymous account.

To use Mysqladmin to specify a password for the root account, execute the following command:

shell> mysqladmin-u root password "newpwd"

shell> mysqladmin-u root-h host_name password "newpwd"

The above commands apply to Windows and UNIX. Replace the HOST_NAME in the second command with the server host name. You don't need to enclose the password in double quotation marks, but if the password contains spaces or other characters dedicated to command interpretation, you need to enclose it in double quotation marks.

You can also modify the user table directly using update. The following UPDATE statement can specify a password for two root accounts at the same time:

shell> Mysql-u Root

mysql> UPDATE mysql.user SET Password = Password (' newpwd ') WHERE user = ' root ';

mysql> FLUSH privileges;

ERROR 1045 (28000): Access denied for user ' root ' @ ' localhost '  

Step one: Open MySQL Command line Client in MySQL to enter the password [that is the installation]

Step two: Enter after mysql>: UPDATE mysql.user SET Password = old_password (' password ') where Host = ' localhost ' and user = ' username ';

Prompt after Enter: Query OK, 0 rows affected (0.16 sec)

Rows matched:0 changed:0 Warnings:

The third step: after mysql> input: FLUSH privileges;

Prompt after Enter: Query OK, 0 rows affected (0.19 sec)

4. ERROR 1130: Cannot connect remotelyMySQLserver, but can be connected locally:

This error occurs when you link a MySQL server remotely: Error 1130: ....

There are two rows in the user table, where localhost is allowed to log on to the MySQL server, the host is allowed to connect the remote client to the server if the host is "%" means that any remote can log on to the MySQL server, If host is a specific IP, only the corresponding IP PC will be allowed to log on to the server;

Change the host entry for the user table in the MySQL database localhost rename%

Mysql-u Root–proot

Mysql>use MySQL;

Mysql>update User Set host = '% ' where user = ' root ';

Mysql>flush privileges;

Mysql>select ' host ', ' user ' from user where user= ' root ';

We can connect now.

After the installation of MySQL, when connecting to the database, the error 1130 (HY000): Host ' 192.168.0.1 ' is not allowed to connect to the this MySQL server prompt information, cannot connect to the database remotely. Consider probably because the system database MySQL in the user table host is localhost, so, I try to change this value to their own server IP, sure enough, but with Mysql-u root-p command is not connected to the database, need to use mysql-h Server Ip-u root-p Because of the default connection to the MySQL database user table in the value of host, and the default host for this command is localhost, it is not even up,

How to do it:

After MySQL is connected with localhost,
Use MySQL;
Update user set host= ' 123.145.214.188 ';
\q;

Quit MySQL, and then restart MySQL on the go.

Other Solutions

2. Change the table method. It may be that your account is not allowed to log on remotely, only on localhost. This time as long as the computer on the localhost, log in to MySQL, the "MySQL" Database in the "User" table "host", from "localhost" renamed "%", in line with the previously explained method of change;
Mysql-u Root–proot
Mysql>use MySQL;
Mysql>update User Set host = '% ' where user = ' root ';
Mysql>select host, user from user;
3. Authorization law. For example, if you want to myuser use MyPassword to connect to a MySQL server from any host.
GRANT all privileges on * * to ' myuser ' @ ' percent ' identified by ' MyPassword ' with GRANT OPTION;
If you want to allow users to connect to the MySQL server myuser from a host with IP 192.168.1.3 and use MyPassword as the password
GRANT all privileges on * * to ' myuser ' @ ' 192.168.0.1 ' identified by ' MyPassword ' with GRANT OPTION;

Note: If a 1130 error occurs, "localhost" will not log on to the MySQL server

, you can only uninstall MySQL, and remove the corresponding configuration file reinstall, or it will not be resolved;

5. Cannot StartMySQLService, error displayed as unable to startMySQLService:

The first workaround is to delete the My.ini configuration file and reconfigure MySQL, but there will still be problems with the MySQL service that cannot be restarted during configuration;

The last solution is to uninstall the MySQL program and remove all the MySQL folders under the installation path, and then reinstall and configure it, no problem.

MySQL installation process problem summary and processing method