link

sudo vi /etc/mysql/my.cnf

[mysqld]
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

sudo service mysql restart

!!!After all for change password and privileges:

mysql_secure_installation

Symptoms

• MySQL has been upgraded and now fails to start.
• The following error message appears in /var/log/mysqld.log when trying to start MySQL:

  [ERROR] Fatal error: mysql.user table is damaged. Please run mysql_upgrade.

• Plesk is not accessible with the following error message in a browser:

  ERROR: Zend_Db_Adapter_Exception: SQLSTATE[HY000] [2002] No such file or directory
  Additionally, an exception has occurred while trying to report this error: Zend_Exception
  No entry is registered for key 'translate' (Abstract.php:144)

Cause

MySQL 5.1 has been upgraded to 5.5 without running the «mysql_upgrade» command.

Resolution

1. Connect to the Plesk server via SSH.
2. Add the «skip-grant-tables» record to log in to MySQL:2.1. Open MySQL configuration file /etc/my.cnf in a text editor. In this example we use the vi editor:

   # vi /etc/my.cnf

   2.2. Add «skip-grant-tables» on a new line under the [mysqld] section:

   [mysqld]
   skip-grant-tables
   <…>

   2.3. Save the changes and close the file.

3. Start MySQL:

   # service mysqld start

   OR

   # service mysql start

4. Complete the MySQL upgrade:

   # mysql_upgrade

5. Remove the line with «skip-grant-tables» from the /etc/my.cnf file.
6. Restart MySQL:

   # service mysqld restart

   OR

   # service mysql start

For over ways https://ruhighload.com/mysql+1045+access+denied+for+user

link

Your root account, and this statement applies to any account, may only have been added with localhost access (which is recommended).

You can check this with:

SELECT host FROM mysql.userWHEREUser='root';

If you only see results with localhost and 127.0.0.1, you cannot connect from an external source. If you see other IP addresses, but not the one you’re connecting from — that’s also an indication.

You will need to add the IP address of each system that you want to grant access to, and then grant privileges:

CREATEUSER'root'@'ip_address' IDENTIFIED BY'some_pass';GRANTALL PRIVILEGES ON*.*TO'root'@'ip_address';

If you see %, well then, there’s another problem altogether as that is «any remote source». If however you do want any/all systems to connect via root, use the % wildcard to grant access:

CREATEUSER'root'@'%' IDENTIFIED BY'some_pass';GRANTALL PRIVILEGES ON*.*TO'root'@'%';

Finally, reload the permissions, and you should be able to have remote access:

FLUSH PRIVILEGES;

Following two steps worked perfectly fine for me:

1. Comment out the bind address from the file /etc/mysql/my.cnf:

   #bind-address = 127.0.0.1

2. Run following query in phpMyAdmin:

   GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'; FLUSH PRIVILEGES;

https://dev.mysql.com/doc/refman/5.7/en/update.html

UPDATE some_table SET field1='Value 1' WHERE primary_key = 7

UPDATE `Table A`,`Table B`
SET `Table A`.`text`=concat_ws('',`Table A`.`text`,`Table B`.`B-num`," from ",`Table B`.`date`,'/')

UPDATE `table` SET `keywords` = concat_ws(', ',`keywords`,'main')

http://theory.phphtml.net/sql/concat_ws.html

link

I always forget the MySQL create database with UTF8 character set syntax, so here it is:

CREATE DATABASE `mydb` CHARACTER SET utf8 COLLATE utf8_general_ci;
GRANTALLON`mydb`.*TO`username`@localhost IDENTIFIED BY 'password';

Alternatively, you can use ‘CREATE SCHEMA’ instead of ‘CREATE DATABASE’:

CREATESCHEMA`mydb` CHARACTER SET utf8 COLLATE utf8_general_ci;
GRANTALLON`mydb`.*TO`username`@localhost IDENTIFIED BY 'password';

CREATEUSER'username'@'localhost' IDENTIFIED BY'password';
GRANTALL PRIVILEGES ON*.*TO'username'@'localhost'WITHGRANTOPTION;
CREATEUSER'username'@'%' IDENTIFIED BY'password';
GRANTALL PRIVILEGES ON*.*TO'username'@'%'WITHGRANTOPTION;
FLUSH PRIVILEGES;

CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER SCHEMA database DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
ALTER TABLE table_name CHARACTER SET utf8 COLLATE utf8_unicode_ci;

ALTER TABLE mytable CONVERTTO CHARACTER SET utf8;

show table status;