MySql5.7出现ERROR 1054 (42S22): Unknown column ‘password‘ in ‘field list‘的问题

MySQL5.7.37 修改canal密码时候，使用update user set password=password('canal') where user='canal';出现了ERROR 1054 (42S22): Unknown column ‘password’ in 'field list’的问题

系统：Ubuntu20.04

mysql> update user set password=password('canal') where user='canal';
ERROR 1054 (42S22): Unknown column 'password' in 'field list'

查看用户表字段发现没有password字段，改为了authentication_string字段

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from user
    -> ;
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| Host      | User          | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv 
| Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string                     | password_expired | password_last_changed | password_lifetime | account_locked |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
| %         | root          | Y           | Y           | Y           | Y           | Y           | Y         | Y           | Y             | Y            | Y         | Y          | Y
| Y          | Y          | Y            | Y          | Y                     | Y                | Y            | Y               | Y                | Y                | Y              | Y
         | Y                  | Y                | Y          | Y            | Y                      |          |            |             |              |             0 |           0 |
   0 |                    0 | mysql_native_password | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1 | N                | 2022-02-17 11:16:50   |              NULL | N              |
| localhost | mysql.session | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N
| N          | N          | N            | Y          | N                     | N                | N            | N               | N                | N                | N              | N
         | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |
   0 |                    0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N                | 2022-02-17 11:07:42   |              NULL | Y              |
| localhost | mysql.sys     | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N
| N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N
         | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |
   0 |                    0 | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | N                | 2022-02-17 11:07:42   |              NULL | Y              |
| %         | canal         | Y           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N
| N          | N          | N            | N          | N                     | N                | N            | Y               | Y                | N                | N              | N
         | N                  | N                | N          | N            | N                      |          |            |             |              |             0 |           0 |
   0 |                    0 | mysql_native_password | *E3619321C1A937C46A0D8BD1DAC39F93B27D4458 | N                | 2022-02-18 02:04:29   |              NULL | N              |
+-----------+---------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+-----------------------+-------------------------------------------+------------------+-----------------------+-------------------+----------------+
4 rows in set (0.01 sec)

把更新语句改为：update user set authentication_string=password('canal') where user='canal';就可以了。

mysql> update user set authentication_string=password('canal') where user='canal';
Query OK, 0 rows affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 0  Warnings: 1

最后别忘了flush privileges;

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

整体步骤如下：

登录MySQL

sudo mysql -u root -p

mysql> use mysql;
mysql> update user set authentication_string=password('canal') where user='canal';
#5.7以下版本使用 mysql> update user set password=password('canal') where user='canal';
mysql> flush privileges;

其他修改方法

例子：修改用户canal 密码：123

方法1： 用SET PASSWORD命令
登录MySQL

sudo mysql -u root -p

mysql> set password for canal@localhost = password('123'); 

方法2：用mysqladmin

mysqladmin -ucanal -p123456 password 123 

如果忘记密码

修改MySQL安装目录下的my.ini配置文件，使登录时跳过权限检查；

 #到mysql根目录找到mysql配置文件 
 vi my.ini
 #在my.ini，[mysqld]下添加一行，使其登录时跳过权限检查
 # MySql5.7.37 目录 
 skip_grant_tables

重启mysql 执行上面步骤更新密码。