Updating your MySQL root user password
https://dev.mysql.com/doc/refman/5.7/en/set-password.html
SET PASSWORD
can be used with or without a FOR
clause that explicitly names a user account:
-
With a
FOR
clause, the statement sets the password for the named account, which must exist:user
SET PASSWORD FOR 'jeffrey'@'localhost' = 'auth_string';
-
With no
FOR
clause, the statement sets the password for the current user:user
SET PASSWORD = 'auth_string';
Any client who connects to the server using a nonanonymous account can change the password for that account. To see which account the server authenticated you as, invoke the
CURRENT_USER()
function:SELECT CURRENT_USER();
Setting the password for a named account (with a FOR
clause) requires the UPDATE
privilege for the mysql
database. Setting the password for yourself (for a nonanonymous account with no FOR
clause) requires no special privileges. When the read_only
system variable is enabled, SET PASSWORD
requires the SUPER
privilege in addition to any other required privileges.
If a FOR
clause is given, the account name uses the format described in Section 6.2.3, “Specifying Account Names”. For example:user
SET PASSWORD FOR 'bob'@'%.example.org' = 'auth_string';
The host name part of the account name, if omitted, defaults to '%'
.
The password can be specified in these ways:
-
Use a string without
PASSWORD()
SET PASSWORD FOR 'jeffrey'@'localhost' = 'mypass';
SET PASSWORD
interprets the string as a cleartext string, passes it to the authentication plugin associated with the account, and stores the result returned by the plugin in themysql.user
account row. (The plugin is given the opportunity to hash the value into the encryption format it expects. The plugin may use the value as specified, in which case no hashing occurs.) -
Use the
PASSWORD()
function (deprecated as of MySQL 5.7.6)SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('mypass');
The
PASSWORD()
argument is the cleartext (unencrypted) password.PASSWORD()
hashes the password and returns the encrypted password string for storage in themysql.user
account row.The
PASSWORD()
function hashes the password using the hashing method determined by the value of theold_passwords
system variable value. Be sure thatold_passwords
has the value corresponding to the hashing method expected by the authentication plugin associated with the account. For example, if the account uses themysql_native_password
plugin, theold_passwords
value must be 0:SET old_passwords = 0; SET PASSWORD FOR 'jeffrey'@'localhost' = PASSWORD('mypass');
If the
old_passwords
value differs from that required by the authentication plugin, the hashed password value returned byPASSWORD()
will not by usable by the plugin and correct authentication of client connections will not occur.
The following table shows the permitted values of old_passwords
, the password hashing method for each value, and which authentication plugins use passwords hashed with each method.
Value | Password Hashing Method | Associated Authentication Plugin |
---|---|---|
0 | MySQL 4.1 native hashing | mysql_native_password |
2 | SHA-256 hashing | sha256_password |
Pre MySQL 5.7
SQL statement to change the root password for MySQL to MYNEWPASSWORD
UPDATE mysql.user SET Password=PASSWORD('MYNEWPASSWORD') WHERE User='root'; FLUSH PRIVILEGES;
Case sensitive table names for MySQL
Add this to the bottom of your my.ini file (located in your mysql folder/directory) to make your table names case sensitive.
lower_case_table_names=2
You can check if the value is set by entering this SQL statement
select @@lower_case_table_names;
Fix error help: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)
ALTER table `testing` convert to character set utf8 collate utf8_general_ci;
Adding Users
After connecting to the server as root
, you can add new accounts. The following example uses CREATE USER
and GRANT
statements to set up four accounts:
mysql>CREATE USER 'finley'@'localhost' IDENTIFIED BY 'some_pass';
mysql>GRANT ALL PRIVILEGES ON *.* TO 'finley'@'localhost'
->WITH GRANT OPTION;
mysql>CREATE USER 'finley'@'%' IDENTIFIED BY 'some_pass';
mysql>GRANT ALL PRIVILEGES ON *.* TO 'finley'@'%'
->WITH GRANT OPTION;
mysql>CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin_pass';
mysql>GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
mysql>CREATE USER 'dummy'@'localhost';
The accounts created by those statements have the following properties:
-
Two accounts have a user name of
finley
and a password ofsome_pass
. Both are superuser accounts with full privileges to do anything. The'finley'@'localhost'
account can be used only when connecting from the local host. The'finley'@'%'
account uses the'%'
wildcard for the host part, so it can be used to connect from any host.The
'finley'@'localhost'
account is necessary if there is an anonymous-user account forlocalhost
. Without the'finley'@'localhost'
account, that anonymous-user account takes precedence whenfinley
connects from the local host andfinley
is treated as an anonymous user. The reason for this is that the anonymous-user account has a more specificHost
column value than the'finley'@'%'
account and thus comes earlier in theuser
table sort order. (user
table sorting is discussed in Section 7.2.4, “Access Control, Stage 1: Connection Verification”.) -
The
'admin'@'localhost'
account has a password ofadmin_pass
. This account can be used only byadmin
to connect from the local host. It is granted theRELOAD
andPROCESS
administrative privileges. These privileges enable theadmin
user to execute the mysqladmin reload,mysqladmin refresh, and mysqladmin flush-xxx
commands, as well as mysqladmin processlist . No privileges are granted for accessing any databases. You could add such privileges usingGRANT
statements. -
The
'dummy'@'localhost'
account has no password (which is insecure and not recommended). This account can be used only to connect from the local host. No privileges are granted. It is assumed that you will grant specific privileges to the account usingGRANT
statements.
To see the privileges for an account, use SHOW GRANTS
:
mysql> SHOW GRANTS FOR 'admin'@'localhost';
+-----------------------------------------------------+
| Grants for admin@localhost |
+-----------------------------------------------------+
| GRANT RELOAD, PROCESS ON *.* TO 'admin'@'localhost' |
+-----------------------------------------------------+
To see nonprivilege properties for an account, use SHOW CREATE USER
:
mysql> SHOW CREATE USER 'admin'@'localhost'G
*************************** 1. row ***************************
CREATE USER for admin@localhost: CREATE USER 'admin'@'localhost'
IDENTIFIED WITH 'mysql_native_password'
AS '*67ACDEBDAB923990001F0FFB017EB8ED41861105'
REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK
Revoke syntax
REVOKEpriv_type
[(column_list
)] [,priv_type
[(column_list
)]] ... ON [object_type
]priv_level
FROMuser
[,user
] ... REVOKE ALL PRIVILEGES, GRANT OPTION FROMuser
[,user
] ... REVOKE PROXY ONuser
FROMuser
[,user
] ...
The REVOKE
statement enables system administrators to revoke privileges from MySQL accounts.
When the read_only
system variable is enabled, REVOKE
requires the SUPER
privilege in addition to any other required privileges described in the following discussion.
Each account name uses the format described in Section 7.2.3, “Specifying Account Names”. For example:
REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';
If you specify only the user name part of the account name, a host name part of '%'
is used.
For details on the levels at which privileges exist, the permissible priv_type
, priv_level
, and object_type
values, and the syntax for specifying users and passwords, see Section 14.7.1.4, “GRANT Syntax”
To use the first REVOKE
syntax, you must have the GRANT OPTION
privilege, and you must have the privileges that you are revoking.
To revoke all privileges, use the second syntax, which drops all global, database, table, column, and routine privileges for the named user or users:
REVOKE ALL PRIVILEGES, GRANT OPTION FROMuser
[,user
] ...
To use this REVOKE
syntax, you must have the global CREATE USER
privilege or the UPDATE
privilege for the mysql
database.
REVOKE
removes privileges, but does not drop mysql.user
table entries. To remove a user account entirely, use DROP USER
(see Section 14.7.1.3, “DROP USER Syntax”) or DELETE
.
If the grant tables hold privilege rows that contain mixed-case database or table names and the lower_case_table_names
system variable is set to a nonzero value, REVOKE
cannot be used to revoke these privileges. It will be necessary to manipulate the grant tables directly. (GRANT
will not create such rows when lower_case_table_names
is set, but such rows might have been created prior to setting the variable.)
When successfully executed from the mysql program, REVOKE
responds with Query OK, 0 rows affected
. To determine what privileges result from the operation, use SHOW GRANTS
. See Section 14.7.5.21, “SHOW GRANTS Syntax”.
MySQL Mac OS X Start Stop Restart
Here are the three basic commands to start, stop, and restart MySQL in OS X, including Yosemite. Be sure to enter the command onto a single line, sudo obviously requires an administrator password to be entered.
Start MySQL
sudo /usr/local/mysql/support-files/mysql.server start
Stop MySQL
sudo /usr/local/mysql/support-files/mysql.server stop
Restart MySQL
sudo /usr/local/mysql/support-files/mysql.server restart
Source: mysql.com