MySQL Common operations Summary

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

MySQL ERROR 1045 (28000): Access denied for user workaround

Problem Recurrence (The following discussion is for Windows environments only):

c:\appserv\mysql> mysql-u root-p Enter password:error 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using Password:yes)

Edit MySQL profile My.ini (do not know where to search), under the [mysqld] This entry add Skip-grant-tables save exit after restarting MySQL

1. Click "Start", "Run" (shortcut key Win+r).

2. Start: Enter net stop MySQL

3. Stop: Enter net start MySQL

At this time in the cmd input mysql-u root-p can be used without password login, appear password: When the direct return can enter, will not appear error 1045 (28000), but many operations will be limited, because we can not grant (no permission). Follow the process below (the red part is the input section, the pink One is the code shown after the execution does not have to be entered):

1. Go to MySQL database:

mysql> use MySQL; Database changed

2. Set a new password for the root user, and enter the blue part yourself: mysql> update user set Password=password ("New password") where user= "root"; Query OK, 1 rows affected (0.01 sec) rows matched:1 changed:1 warnings:0

3. Refresh the database mysql> flush privileges; Query OK, 0 rows Affected (0.00 sec)

4. Exit mysql:mysql> quit Bye

After the change, and then modify the My.ini this file, we just joined the "Skip-grant-tables" this line to delete, save the exit and then restart MySQL.

http://blog.csdn.net/hsg77/article/details/39159191

1067 error resolution and reflection on MySQL startup under Windows platform

First,

1, open My.ini file, find Default-storage-engine=innodb this line, change it to Default-storage-engine=myisam.

2. Remove Ib_logfile0 and Ib_logfile1 3 from the data directory in the MySQL installation directory. Locate the Infodb directory that you specified when configuring the MySQL server to remove ibdata1

According to the My.ini file:

#*** INNODB Specific Options * * * innodb_data_home_dir= "d:/". 4. Restarting the service for MySQL

According to my own practice, a third step will solve the problem.

Two

Err file Contents:

090417 9:02:55 InnoDB:Error:unable to create temporary file; errno:2 090417 9:02:55 [Error] Plugin ' Inno DB ' init function returned error. 090417 9:02:55 [ERROR] Plugin ' InnoDB ' registration as a STORAGE ENGINE failed. 090417 9:02:55 [ERROR] unknown/u nsupported table Type:innodb 090417 9:02:55 [ERROR] aborting

090417 9:02:55 [Warning] forcing shutdown of 1 plugins 090417 9:02:55 [Note] Mysql:shutdown complete

after a Google, find out why not start: MySQL will not automatically install the initial tmpdir (temporary file directory), so in the configuration file My.ini add the following:

[Mysqld]

#自己指定的临时文件目录

tmpdir= "D:/mysql/mysql Server 5.1/temp"

start MySQL again everything works, and some *.tmp temporary files are generated under the Temp folder.

Finally, there are some questions: if it is not initialized Tmpdir, why did I not initialize the first installation, but there is no such a problem?

Three

It was a good time to manually install MySQL5.0.16 Windows service, which was always prompted with an error of 1067 when installing the service for 5.0.83 last night. Online someone said put My.ini under the C:\WINDOWS, but I encountered the situation is still not solve the problem. My solution is to replace the--defaults-file in the parameter with--defaults-extra-file, as follows:

mysqld--install MySQL--defaults-file=e:/mysql-5.0.83-win32/my.ini

instead

mysqld--install MySQL--defaults-extra-file=e:/mysql-5.0.83-win32/my.ini

Execute mysqld--verbose--help can see the use of mysqld, where there is a description of these two parameters.

According to my own practice, the most likely cause of this problem is the data table storage engine, in short, the storage engine is the type of the table

) caused by. MySQL database supports a variety of data table storage engines, can be viewed with the command: Show engines, in the MySQL configuration file My.ini can also be seen (the default is MyISAM):

# The default storage engine that would be used when create new tables when Default-storage-engine=myisam.

the common storage engine and INNODB,INNODB have many advantages, InnoDB to MySQL table provides transaction, rollback, crash repair ability, Multiple versions of concurrency-controlled transaction security. At the same time, InnoDB is the first engine on MySQL to provide foreign key constraints, and the ability of the InnoDB storage engine to handle transactions is unmatched by other MySQL storage engines.

The difference between InnoDB and MyISAM:

Tables stored in the InnoDB storage engine, the. frm that stores the table structure is stored separately from the saved table data file (IBDATA1), and the file location where the table data is stored can be set by modifying the My.ini file itself:

#*** INNODB Specific Options * * *

Innodb_data_home_dir= "d:/"

The table stored in the MyISAM storage engine consists of 3 files:. frm (the structure of the stored table) file. MYD (myoudata, stored table data) file. MYI (abbreviation for Myindex, the index of the storage table), this three files are stored in the directory where the MySQL database is installed, such as F:\ProgramData\MySQL\MySQL Server 5.1\data\databasename.

In addition, through the resolution of this problem, I realize the importance of viewing the error log, when encountered problems, we should be to see the error log, rather than go to Google or Baidu, in fact, from the error log we can get the real cause of the problem, The remedy can be charm. The MySQL error log (. err) is located in the F:\ProgramData\MySQL\MySQL Server 5.1\data directory, and the file name is typically your computer name.

Http://blog.sina.com.cn/s/blog_600357e40100q5ub.html

MySQL Common operations Summary