PlanetMysql.ru — информация о СУБД MySQL

Today on MySql Forums, there was a question in the newbie section about two users — I have a doubt on db host and db user relationship . What does this mean ?

name	host
tom	%
joe	127.0.0.1

New DBAs are often confused by the quirky methods of authentication that MySQL uses. Heck, extremely experienced MySQL DBAs can get confused.

From the manual, 6.2.4. Access Control, Stage 1: Connection Verification

When you attempt to connect to a MySQL server, the server accepts or rejects the connection based on your identity and whether you can verify your identity by supplying the correct password. If not, the server denies access to you completely. Otherwise, the server accepts the connection, and then enters Stage 2 and waits for requests.

Your identity is based on two pieces of information:

The client host from which you connect

Your MySQL user name

So the first user, Tom, is allowed to connect from ‘%’ and ‘%’ is a wildcard for any system. And the second user is only allowed to connect from a host with the address ’127.0.0.1′. Usually systems have a network loop-back (think short circuit) assigned to 127.0.0.1 and uses that for its own traffic internally¹.

Also from the same manual page (abbreviated) to provide a little more clarity.

The following table shows how various combinations of Host and User values in the user table apply to incoming connections.

Host Value	User Value	Permissible Connections
‘thomas.loc.gov’	‘fred’	fred, connecting from thomas.loc.gov
‘thomas.loc.gov’	”	Any user, connecting from thomas.loc.gov
‘%’	‘fred’	fred, connecting from any host
‘%’	”	Any user, connecting from any host
‘%.loc.gov’	‘fred’	fred, connecting from any host in the loc.gov domain

It helps to occasionally re-read the The MySQL Access Privilege System of the manual to help remember how users get into the system as well as the edge cases. It is all too easy to set up multiple users with the same user name value but different privileges depending on where they connect.

And thanks to all who take the time to answer questions on the forums!

1. Hugh simplification used here for brevity.

PlanetMySQL Voting: Vote UP / Vote DOWN

Today on MySql Forums, there was a question in the newbie section about two users — I have a doubt on db host and db user relationship . What does this mean ?

name	host
tom	%
joe	127.0.0.1

New DBAs are often confused by the quirky methods of authentication that MySQL uses. Heck, extremely experienced MySQL DBAs can get confused.

From the manual, 6.2.4. Access Control, Stage 1: Connection Verification

When you attempt to connect to a MySQL server, the server accepts or rejects the connection based on your identity and whether you can verify your identity by supplying the correct password. If not, the server denies access to you completely. Otherwise, the server accepts the connection, and then enters Stage 2 and waits for requests.

Your identity is based on two pieces of information:

The client host from which you connect

Your MySQL user name

So the first user, Tom, is allowed to connect from ‘%’ and ‘%’ is a wildcard for any system. And the second user is only allowed to connect from a host with the address ’127.0.0.1′. Usually systems have a network loop-back (think short circuit) assigned to 127.0.0.1 and uses that for its own traffic internally¹.

Also from the same manual page (abbreviated) to provide a little more clarity.

The following table shows how various combinations of Host and User values in the user table apply to incoming connections.

Host Value	User Value	Permissible Connections
‘thomas.loc.gov’	‘fred’	fred, connecting from thomas.loc.gov
‘thomas.loc.gov’	”	Any user, connecting from thomas.loc.gov
‘%’	‘fred’	fred, connecting from any host
‘%’	”	Any user, connecting from any host
‘%.loc.gov’	‘fred’	fred, connecting from any host in the loc.gov domain

It helps to occasionally re-read the The MySQL Access Privilege System of the manual to help remember how users get into the system as well as the edge cases. It is all too easy to set up multiple users with the same user name value but different privileges depending on where they connect.

And thanks to all who take the time to answer questions on the forums!

1. Hugh simplification used here for brevity.

PlanetMySQL Voting: Vote UP / Vote DOWN

How to measure a Storm’s Fury One Breakfast at a Time is a non MySQL-Centric story I need to share with my fellow DBAs. The US Federal Emergency Management Agency has a metric to judge the severity of a storm. They monitor the status of Waffle House Restaurants. Waffle House goes to extreme measures to open up storm damaged or impacted restaurants that need to be studied by anyone who had any disaster recovery duties. Their measures are so extensive that FEMA now tracks which Waffle Houses are open, closed or operating in a diminished capacity after a storm. After Katrina, seven restaurants were destroyed and one hundred more shut down. But they were quickly reopened and swamped with customers. And sales volumes can double or triple after a storm.

So, how fast could you get your instances online after a flood, fire, or other disaster.

PlanetMySQL Voting: Vote UP / Vote DOWN

“I needed a password eight characters long so I picked Snow White and the Seven Dwarfes.” — Nick Helm¹

MySQL Account Management is often nebulous mess to new DBAs. Frank² is a new MySQL with many years of Linux administration duties in his background and now he has been taking on more duties after his companies previous DBA departed. One of his co-workers was in the middle of a name change and could no longer access the need instance. Frank had run

mysql -u root mysql
mysql>UPDATE user SET Name='jjones' where Name='jsmith';

But Mrs. Jones was not able to access the database. Frank tried for a while to find the answer. To greatly simplify the MySQL authorization process, the sever checks the Host of a new connection, then the User and Password. Frank had checked all three. The name of the host Mrs. Jones used had not changed. Neither had the password. Only the account name.

I gave Frank my best Zen-like answer, “Flush privileges.”

“Is that it? ‘Flush privileges’ with a semi-colon at the end?” He gasped.

“Semi-colon or back-slash followed by a letter ‘G’ of either case.” He gave the simple two word command and suddenly Mrs. Jones was back into her tables³

1. Best Jokes from the 2011 Fringe
2. Name changed to protect the promised quantity of adult beverage for solving problem.
3. Some administrative tools such as Workbench would have avoided this problem by performing the flush for the DBA. But Frank is trying to learn the CLI.

PlanetMySQL Voting: Vote UP / Vote DOWN