Web Carpenter Development and Design

MYSQL change root user password and case sensitive table names Fix illegal mix of collations

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 user clause, the statement sets the password for the named account, which must exist:

    SET PASSWORD FOR 'jeffrey'@'localhost' = 'auth_string';

  • With no FOR user clause, the statement sets the password for the current 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 user clause is given, the account name uses the format described in Section 6.2.3, “Specifying Account Names”. For example:

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 the mysql.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 the mysql.user account row.

    The PASSWORD() function hashes the password using the hashing method determined by the value of the old_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 the mysql_native_password plugin, the old_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 by PASSWORD() 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 of some_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 for localhost. Without the'finley'@'localhost' account, that anonymous-user account takes precedence when finley connects from the local host and finley is treated as an anonymous user. The reason for this is that the anonymous-user account has a more specific Host column value than the'finley'@'%' account and thus comes earlier in the user 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 of admin_pass. This account can be used only by admin to connect from the local host. It is granted the RELOAD and PROCESS administrative privileges. These privileges enable the admin 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 using GRANT 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 using GRANT 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

REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION
    FROM user [, user] ...

REVOKE PROXY ON user
    FROM user [, 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 FROM user [, 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

 

Visit sunny St. George, Utah, USA

Send a Message

We would love to hear from you.