The database is almost a right-hand person for the service-side developer. Almost every server-side developer has to deal with the database. and MySQL with its open source free, small and easy to use, powerful function of the Chinese database to occupy half of the share. Since reaching vagrant last year, Vagrant has attracted the D melon with its convenience and ease of use. In order to be as consistent as possible with the production environment and to reduce unnecessary problems, vagrant+ubuntu+mysql is almost perfect.
MySQL allows settings for remote access
1. Note Bind-address = 127.0.0.1.
Comment out bind-address = 127.0.0.1 (that is, add # at the beginning of the line), as follows:
In addition to looking at this sentence, you can also modify the IP address in the following to allow the connection of the IP address. However, if you are only developing a database, for convenience, it is recommended that you comment it out directly.
As you can see from the comments above, the old version of MySQL (from some data showing 5.0 and its previous versions) uses skip-networking. So, kindly remind me to use the old version of the small partner please note.
2. Delete anonymous users
Log in to the database:
3. Increase the number of users who allow remote access or allow remote access to existing users.
Then, after deleting an anonymous user, grant root all permissions to access any database on any host (%). The SQL statement is as follows:
4. Exit Database
After the MySQL shell finishes executing SQL, you need to exit to bash to execute system commands and exit. Because this is too common and too simple. The following prompts you to "exit" and do not repeat this command again.
5. Restart the database
After completing all of the above steps, you will need to restart the database for the modified configuration to take effect. Perform the following command to restart the database:
So far, you should be able to access the database remotely.
Of course, "impregnable passes Mandao really like Iron", the world is not imagined so the pattern Tucson broken, may also encounter some problems, affect the normal use. Below, d Melon put some of their own problems to deal with, to facilitate the reference of friends to solve. (to step back, without these problems to prop up the façade, D melon should be called "ultimate solution"?! haha
Frequently Asked Questions
Andy said well, "out of the mix are to return." For our small partners in the kicking industry, "all out of the pile of problems". Feng Gong also said good, "as Beijing people, pocket not 2000 dollars are embarrassed to greet others." D melon think, for the small partners to play, did not encounter problems, are embarrassed to say that they are kicking. Therefore, for some common problems, or the common operation may cause problems, it is necessary to tidy up, convenient to consult at any time use.
Error 1045 (28000) causes of errors and solutions
If you do not follow the above procedure, delete anonymous users, or you are based on the information found on the Internet to set up (most of them do not "delete anonymous user" This step), can cause this problem. As for the cause of the problem, D. To be clear about this reason, but also from the MySQL database user authentication mechanism.
In MySQL, user-related information exists in database MySQL. Let's look at the structure of the table, and the command and return values are as follows:
Or look at the table's build statement. The order is as follows:
Because the return value is doped some unnecessary useless information, for the convenience of everyone to view, D, the return value did a simple processing, only the most important part of the table to extract the statement. As follows:
From any of the above two methods, you can see that in MySQL, the key combination is used (host, user) as the primary key, which is to identify the only user. In other words, even for the same user name, such as root, different users, depending on the host, can have different passwords and permissions. This is, we can look at the data in the table. The SQL and return values are as follows:
You can see that there are these users in the database. So, if you have the ' root ' @ ' localhost ' login, how do you match it?
According to the official MySQL document MySQL:: MySQL 5.6 Reference Manual:: 6.2.4 Access Control, Stage 1:connection the verification is described as follows:
Whenever the server reads the user table into memory, it sorts the rows.
When a client attempts to connect, the server looks through the "rows in sorted" order.
The server uses the "matches" the client host name and user name.
The server uses sorting rules this order rows with the Most-specific Host values A. Literal host names and IP addresses are the most specific. (The specificity of a literal IP address isn't affected by whether it has a netmask, so 192.168.1.13 and 192.168.1.0/255. 255.255.0 are considered equally specific.) The pattern '% ' means, ' any host ', and is least specific. The empty string "also means" any host "but sorts after '% '. Rows with the same Host value are ordered with the Most-specific user values (a blank user value means ' any User ' D is least specific).
Please note this sentence: "The server uses sorting rules that order rows with the Most-specific Host values one. Literal host names and IP addresses are the most specific. "
Then, when you connect locally to the database, anonymous users will overwrite other users such as ' [Any_username] ' @ '% '. The exact matching process is as follows:
At ' root ' @ ' localhost ' can match ' root ' @ '% ', ' can also match root, then ' root ' @ ' localhost ' can also match ' @ ' localhost '. Depending on the document above, the host has a higher matching priority than the user name, and localhost has a higher matching priority than%. So, ' @ ' localhost ' is higher than the ' root ' @ '% ', ' root ' @ ' localhost ' matches ' @ ' localhost '. According to our query results above, the ' @ ' localhost ' password is null and does not match the password 123456 provided at logon. So, the login failed.
Speaking of which, presumably everyone has thought of the solution: very simple, directly delete the anonymous users can be.
However, the current problem is not to log on to the database locally, let alone delete the data.
What to do? D Guadalcanal thought of three solutions, as follows:
①, the more ridiculous solution, on another computer or virtual machine, remote connection to this database
This is one of the more ridiculous solutions that I think of. Now that we have allowed remote connections, we can remotely connect to this database on another computer or another virtual machine, and then delete anonymous users. The steps are as follows:
1. Connect to the database on the remote computer. The order is as follows:
2. Then, switch to the MySQL database. The order is as follows:
3. Delete anonymous users. SQL is as follows:
4. Exit, and then you can log on directly to the database locally.
②, opportunistic solution, local use bypass permission table Certified Login Database
Just now explained in the explanation above, although logged in using the root account, but was processed by the database as an anonymous user. and the login account and password and anonymous account information does not match, leading to the authorization failed. That simply "do not take the normal road", skip permission to authenticate this step, direct login is OK. The practice is similar to the "forgotten password" processing similar, so you can refer to the following forgotten database password. Note that here and the "Forget database Password" processing is not the same here, there is no need to log in here, log in after the need to delete the anonymous account instead of resetting the password. For the deletion of anonymous account method, please refer to the above description, here is no longer repeat.
③, the most bizarre solution, the local disguised as a remote host to log in to the database
If you don't know the IP address, MySQL defaults to being logged on from the local host. Then, we will specify the host, disguise itself as a remote host login. D Guadalcanal believes that this solution is based on the MySQL authentication mechanism.
First, use Ifconfig to detect the IP of the local host (note, not the loopback address at the beginning of 127).
Then, log on using the following command:
And then the job is to delete anonymous users. The above has been introduced, here no longer repeat.
For this method, D melon needs to be specifically explained. This method was speculated by D, and was actually tested. So, so, welcome to the bricks and the eggs! Ha ha
Forget the MySQL user password solution
Because the need for a long time not how to use the database, the password has forgotten, so encountered this problem. This was not intended to be written this time, but the plan was not up to the change because of the error 1045 (28000). Later, it was found that the use of this method can also solve the problem. In order to check the data for later convenience, simply put this topic, as one of the "Frequently asked Questions," also recorded.
1. Stop MySQL database. The order is as follows:
2. Start the database. The order is as follows:
Description: Parameter--skip-grant-tables means to start the MySQL service when skipping permission table authentication. Once started, the root of the connection to MySQL does not require a password.
Also, some computers do not have the Mysqld_safe command installed, you can use the following command to start the database:
In addition, you can modify the MySQL configuration file/etc/mysql/my.cnf, in the [mysqld] part of the add a sentence: Skip-grant-tables. However, this is the way to delete this sentence again after resetting the password. D Muggle personal feeling more trouble. It is not recommended to use this method only for understanding.
3. Log in to the database. The order is as follows:
4. Switch to MySQL database
5. Modify root account password
6. Exit and restart the database
You should now be able to use the new password to properly access the database.
Error 2002 (HY000) causes of errors and solutions
During logon to the database, you may experience the following error:
In the beginning, D Mysqld.sock thought it was the database did not correctly create the file, to/var/run/mysqld/under the view, there is no such document.
What's going on? How come there is no such document? Did the database not start up. Use the following command to view:
Shit, there is nothing to output. It seems that the database did not start up. Start the database with the following command:
If you need to start a skip authentication method, use one of the following commands:
If the hint is not mysqld_safe, use the following command:
Then you can log on normally.
On this issue, D Guadalcanal also want to say two more: the root cause of this error is not found Mysqld.sock file, there are two reasons for this problem: one did not create the file at all, that is, the database was not started, which is why the D-brother is here; another reason may be that the directory you created is not in the default directory, or when you connect to the database The specified file directory does not match the actual resulting directory. D in the online search data, also printed this speculation. Some of the users of the problem is the D Melon said that the second cause. So there is another way to solve this problem online. Because D Melon did not encounter, and the situation is more, D melon here will no longer introduce too much. Please contact the friend of this problem to find out their own data.
Error 2003 (HY000) The causes of errors and solutions.
Is talking, appeared this problem, D Melon elder brother also very egg ache. I don't know what's going on here! However, fortunately there are error codes (HY000), with this there is the key to open the door to the solution. 2003 Solutions are readily available.
With Google Baidu, and then on the StackOverflow to find the reason: MySQL database configuration file/etc/mysql/my.cnf in the bind-address = 127.0.0.1 did not comment out, Causes MySQL to receive only local access.
Knowing why, the solution comes along, commenting out the line and restarting the server. Refer to the first part of this article for a specific procedure.
Error 1130 causes of errors and solutions
D, in the process of searching the data, also encountered a mistake mentioned by others. The log is as follows:
The answer is "the IP address of the host used is not within the IP that is allowed to log in".
Brother D has also tested this error. Just started the test, the error did not recur, and thought I tested a problem. Later, the database was restarted, and again the test found success. There is indeed this error. In addition, according to D Muggle personal testing experience, a well-meaning reminder, the Mysql.user table to modify, remember to perform flush privileges or restart the database.
A brief introduction to Mysqld_safe
In the process of solving these problems, D Guadalcanal looked at the process of MySQL. The command and return values are as follows:
Well?! There is wood to find a just description of the familiar dongdong: Mysqld_safe. How does this stuff start up? At first, D-melon thought this thing should not start, and could even cause the Telnet database to be unsuccessful. (because there was indeed a problem.) Also, this command is used to skip the boot of the authentication checksum above. So, D-Melon thinks it should not be started. After the struggle with the enemy (you know, this is definitely some of the problems mentioned here), it turns out that the problem is not caused by this stuff. Fortunately, more thinking a little, otherwise d melon integrity not protect Ah! Ha ha
Because I just thought it was a problem. So, just add this to the list of issues. Through the understanding of D melon, found that this is not a problem, but for D melon, this really solves the D melon a doubt, also for the moment is a problem. In order to facilitate the same problem with the small partners, D melon think it is necessary to mysqld_safe to elaborate.
On the introduction of Mysqld_safe, the most authoritative information is the official document, because the D melon used is MySQL version 5.6. So, here is a reference to MySQL:: MySQL 5.6 Reference Manual:: 4.3.2 mysqld_safe-mysql Server Startup script to explain. The following content is translated from the official documentation description.
The official document, about the Mysqld_safe section of the title, you can well explain the positioning of the Mysqld_safe: MySQL server startup script. The text also says that under Unix, Mysqld_safe is the recommended way to start a MySQL server. Mysqld_safe can add some security features, such as restarting the database server when a server fails, and logging the run information to the error log file.
For more information, please consult your own friends MySQL:: mysql 5.6 Reference Manual:: 4.3.2 mysqld_safe-mysql Server Startup Script. However, there is no reference to the parameter--skip-grant-tables we just used, which is described in MySQL:: MySQL 5.6 Reference Manual:: 5.1.3 Server Command options.
Explanation of Flush privileges
All right, D. Guadalcanal admits that this is a problem that is not a problem. But, for D, this is still a doubt, or, D, I would like to know the exact meaning of this sentence.
In the literal sense, flush privileges can be translated to refresh permissions. This is also really a function of flush privileges: Reloads the permissions result from the authorization table in the database mSQL.
MySQL new user or change the password required flush privileges Refresh MySQL system permissions related tables, otherwise there will be denied access; Another way is to restart the MySQL database for the new settings to take effect.
In addition, flush privileges can also free up memory functionality. MySQL caches the results of the Grant,create user,create server and install plugin statements. These memory will not be released until the Revoke,drop user,drop server and uninstall plugin are executed. So, if the database executes many of the preceding statements, it increases the memory overhead. Performing flush privileges Releases this portion of memory.
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
