[MySQL] highlights of Common Errors and Common commands
[Background]When using the SQL Server database, you must have had experience solving various problems. In many cases, you may find problems and solutions in your blog. Currently, the MySQL database is used for development. Now, we find that we are experiencing more and more experiences in MySQL. Two days ago, I had a problem with my computer's MySQL database. I 've been tossing for several hours ~~
This blog will record the previous and current errors. You can find a better and faster solution in the future.
[Common Errors] (1) windows mysql prompts: 1045 access denied for user 'root' @ 'localhost' using password yes
Http://blog.csdn.net/so1127/article/details/6619844 because in solving this problem, need to use my. the INI file does not exist after installation. You can create it on the Internet, so I created this file myself. The content is as follows:
# For advice on how to change settings please see # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the # *** default location during install, and will be replaced if you # *** upgrade to a newer version of MySQL.[mysqld] # Remove leading # and set to the amount of RAM for the most important data # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%. # innodb_buffer_pool_size = 128M # Remove leading # to turn on a very important data integrity option: logging # changes to the binary log between backups. # log_bin # These are commonly set, remove the # and set as required. basedir = E:\MySQL\mysql-5.6.24-winx64 datadir = E:\MySQL\mysql-5.6.24-winx64\data # port = 8099 # server_id = sa # Remove leading # to set options mainly useful for reporting servers. # The server defaults are faster for transactions and fast SELECTs. # Adjust sizes as needed, experiment to find the optimal values. # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" skip-grant-tables default-character-set=utf8
This problem is solved. After the file is added, I tried to create a new user and a new problem emerged.
(2) ERROR 1364 (HY000): Field 'ssl _ cipher 'doesn' t have a default value
Reference blog: http://xiejianglei163.blog.163.com/blog/static/12472762014424048216/
I changed one sentence in the my. ini file according to the blog content:
After SQL-mode = "STRICT_TRANS_TABLES, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION" is changed to SQL-mode = "NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION", I have successfully created a user.
These two problems have been solved, and everything went well when I was studying online malls.
But two days ago, after restarting the computer, the database encountered the following problems.
(3) Start the MySQL service with error 1067:
Reference link: http://jingyan.baidu.com/article/d5c4b52bee6e69da560dc5ec.html this problem occurs, I took the way is method 2, delete the my. ini file, so MySQL service can be started normally.
However, after the my. ini file is deleted, the error 1045 always appears. In this way, the two problems seem to be endless and cannot be solved all the time. In desperation, I uninstalled mysql and re-installed it once. The process of uninstalling or installing MySQL is not mentioned here. Let's continue to look at several MySQL errors:
(4) 2003-Can't connect to MySQL server on 'localhost' (10061)
This error occurs because the MySQL service is not started. You can enter the service. msc window command to go to service management and start the MySQL service. Or you can use the mysql command to start the instance.
(5) Host "localhost" is not allowed to connect to mysql serverYou cannot log on to the MySQL database locally. Http://www.cnblogs.com/ycsfwhh/archive/2011/02/18/1957980.html first, stop mysql service: SC stop mysql
Then, mysqld -- skip-grant-tables
Open another terminal and continue. mysqlcheck -- check-upgrade -- all-databases -- auto-repair
Enter mysql again and then prompt that the connection is successful.
The local connection can be successful. When others remotely connect to their own database, the following may encounter a 1045 error.
(6) Access denied for user 'root' @ 'localhost' (using password: YES)
Solution:
Solution 1: Change the tableUse [databasename]; select user, host, password from user; update user set host = '%' where user = 'root ';
Solution 2: Authorization
Grant all privileges on *. * TO 'root' @ '%' identified by '000000' with grant option; flush privileges; (any host can connect TO the mysql server)
(7) Chinese garbled characters
During the ITOO project, the Chinese characters are garbled from directly inserting records into the database in the code. During the first version of the thinking exam, the time was tight and the problem was not completely solved. Some of the questions could be imported into Chinese, while some of them were garbled. In the subsequent development of this version, we finally found the root cause of the problem.
Reason for garbled characters:
Different encoding formats may lead to the same character, and the encoding in different character sets will be different. The characters of the same Code in different character sets are also different. When the encoding format (character set) of the string returned by your MySQL is associated with your client tool Program (mysql, php, query browser ,...) if the character set used is different, garbled characters are generated.
Information to be checked after garbled characters:
1. Character Set settings for fields in the database table.Show create table TableName or show full columns from tableName
2. Currently connected system parametersShow variables like 'Char %'
Tip:Make sure that the character set of this field in the table is Chinese compatible, including the following four types:
Big5 | Big5 Traditional Chinese
Gb2312 | GB2312 Simplified Chinese
Gbk | GBK Simplified Chinese
Utf8 | UTF-8 Unicode
Garbled
:
I checked the results of the above two pieces of information. Obviously, the character set of the connected database is latin1. Therefore, when importing data, it becomes garbled.
Solution:
Use Navicat to open the corresponding database and set the character set of its database attribute to utf8.
[Error Summary]
In many cases, 1045 errors occur. The following common causes are found during data query:
1. Incomplete records may exist in the mysql user table; 2. users in the mysql user table may not be granted permissions.
Because mysql databases are not like SQL server databases, they can be operated on the visual interface. In mysql, various operations need to be performed through various commands. below, summarize some common commands to improve the efficiency of later work.
Start and Stop a service
Net stop mysql
Net start mysql
Log on to mysql
Mysql-r root-p press enter to enter the password
Show database list
Show databases;
Select database and display data table
Use mysql;
Show tables;
Create and delete Databases
Create database dbName;
Drop database dbName;
Backup
Mysqldump-u [Database User Name]-p [name of the database to be backed up]> [storage path of the backup file] example: mysqldump-u root-p test> E: \ tt. SQL
Restore
Mysql-u root-p <[backup file storage path]
[Learning experience]
1. Database Backup is very important.Because I don't know when or why, my database won't be available. When it happens, I regret it too late.
2. There is nothing that can't be solved. The worst way is to try again.When I encountered a problem, I felt good for the first time, but encountered some problems that were not expected by myself. When I encountered a problem that was no longer applicable to the previous solution, I felt a little different. Finally, I made the database myself. As it turns out, don't give up. The worst way is to try again.
3. Learning is a long process of continuous addition and accumulation.In fact, the problem of solving Chinese garbled characters was added later, because mysql has been used all the time and is still in the process of continuous learning, so that you can solve the previous problems. Therefore, learning is a complementary stage.
