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

Here is the 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_typepriv_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

Get In Touch

We would love to hear from you.

Please enter your email address.
Please enter a valid email address.
Please enter the verification number.
You have entered a wrong verification number.
Please enter a message.
Please check your message for any errors.
Your message has been sent successfully.
Sorry, error occured this time sending your message.
* required