当我们忘记mysql数据库密码时我们就无法正常进入数据库，也就无法修改密码，那么这时该怎么修改密码呢，这里教大家一个简单常用修改密码的方式。

工具/原料

 
  • mysql数据库
  • cmd命令行

方法/步骤

 
  1. 1

    打开mysql.exe和mysqld.exe所在的文件夹,复制路径地址

    mysql数据库忘记密码时如何修改
  2. 2

    打开cmd命令提示符，进入上一步mysql.exe所在的文件夹。

    mysql数据库忘记密码时如何修改
  3. 3

    输入命令  mysqld --skip-grant-tables  回车，此时就跳过了mysql的用户验证。注意输入此命令之后命令行就无法操作了，此时可以再打开一个新的命令行。注意：在输入此命令之前先在任务管理器中结束mysqld.exe进程，确保mysql服务器端已结束运行。

    mysql数据库忘记密码时如何修改
  4. 4

    然后直接输入mysql，不需要带任何登录参数直接回车就可以登陆上数据库。

    mysql数据库忘记密码时如何修改
  5. 5

    输入show databases;   可以看到所有数据库说明成功登陆。

    mysql数据库忘记密码时如何修改
  6. 6

    其中mysql库就是保存用户名的地方。输入 use mysql;   选择mysql数据库。

    mysql数据库忘记密码时如何修改
  7. 7

    show tables查看所有表，会发现有个user表，这里存放的就是用户名，密码，权限等等账户信息。

    mysql数据库忘记密码时如何修改
  8. 8

    输入select user,host,password from user;   来查看账户信息。

    mysql数据库忘记密码时如何修改
  9. 9

    更改root密码，输入update user set password=password('123456') where user='root' and host='localhost';

    mysql数据库忘记密码时如何修改
  10. 10

    再次查看账户信息，select user,host,password from user;   可以看到密码已被修改。

    mysql数据库忘记密码时如何修改
  11. 11

    退出命令行，重启mysql数据库，用新密码尝试登录。

    mysql数据库忘记密码时如何修改
  12. 12

    测试不带密码登录mysql,发现还是能够登陆上，但显示数据库时只能看到两个数据库了，说明重启之后跳过密码验证已经被取消了。

    mysql数据库忘记密码时如何修改
  13. 13

    我这地方重启数据库之后之所以不带密码任然能够登录是因为我的数据库里存在设无须口令的账户。

    mysql数据库忘记密码时如何修改
    END

注意事项

 
  • 注意其中需要重启数据库的几个地方
 
经验内容仅供参考，如果您需解决具体问题(尤其法律、医学等领域)，建议您详细咨询相关领域专业人士。
举报作者声明：本篇经验系本人依照真实经历原创，未经许可，谢绝转载。
 
 
 
 
###############2
 

阿里云服务器 ECS上，安装完MySQL数据库后，在本地能连接上，远程连接不上，一直报10060的错误，如下图：

MySQL在本地能连接上，远程连接不上的解决办法
 

工具/原料

 
  • 阿里云服务器 ECS
  • 安装MySQL

方法/步骤

 
  1.  

    查看MySQL进程是否正常，可以阿里云服务器 ECS 服务器上输入：ps -ef|grep mysqld

    mysqld_safe和mysqld都在，说明MySQL进程是正常，若没有这两进程，执行/etc/init.d/mysqld start，启动msyql。

    MySQL在本地能连接上，远程连接不上的解决办法
  2.  

    查看mysql监听IP和端口是否正常。

    使用：netstat -anpt

    监听得地址如果是:::3306或者是0.0.0.0:3306，表示监听所有IP地址，这监听状态是正常。若出现127.0.0.0:3306，说明监听的本地地址，需要在mysql配置文件中将bind-address选项设置为bind-address = 0.0.0.0，重启mysql。

    MySQL在本地能连接上，远程连接不上的解决办法
  3.  

    查看用于远程访问的mysql用户权限是否正确。

    在本地登入mysql服务器，

    use mysql;

    SELECT user, host from mysql.user; 查看用于远程访问的mysql用户host的权限，%表示允许所有机器访问。若host为127.0.0.1/localhost，那么这个用户就只能本机访问，则需要将host改为%，可以使用update user set host='%' where user='root';

    MySQL在本地能连接上，远程连接不上的解决办法
    MySQL在本地能连接上，远程连接不上的解决办法
  4.  

    若以上操作都正常，还是远程还是不能访问的话，可以使用tcpdump在服务器端抓一下3306端口，看是否有数据包，排查以下网络原因。

    在服务上输入抓包命令：tcpdump port 3306

    然后远程连接mysql数据库，看一下服务端是否有数据包。

    MySQL在本地能连接上，远程连接不上的解决办法
  5.  

    如果没有数据包，查一下阿里云服务器 ECS下的安全组件中的配置

    MySQL在本地能连接上，远程连接不上的解决办法
  6.  

    若安全组件中的配置中，入口未对数据库服务监听的3306端口放开，则需要将3306端口放开。如下图，并没有3306端口，则表示未放开该端口。

    MySQL在本地能连接上，远程连接不上的解决办法
    MySQL在本地能连接上，远程连接不上的解决办法
  7.  

    在阿里云服务器 ECS下的安全组件中的配置，放开3306端口。单击添加安全组规则，如下图:

    MySQL在本地能连接上，远程连接不上的解决办法
    MySQL在本地能连接上，远程连接不上的解决办法
  8.  

    以上操作基本上可以排查问题得所在原因了，要是还有问题，可以留言讨论；若对您有帮助，请在下方投个票。

     
 ##################
5.6 原厂 关于修改密码的说法

2.10.4 Securing the Initial MySQL Accounts

The MySQL installation process involves initializing the data directory, including the grant tables in the mysql system database that define MySQL accounts. For details, see Section 2.10.1, “Initializing the Data Directory”.

This section describes how to assign passwords to the initial accounts created during the MySQL installation procedure, if you have not already done so.

The mysql.user grant table defines the initial MySQL user accounts and their access privileges:

  • Some accounts have the user name root. These are superuser accounts that have all privileges and can do anything. If these root accounts have empty passwords, anyone can connect to the MySQL server as root without a password and be granted all privileges.

    • On Windows, root accounts are created that permit connections from the local host only. Connections can be made by specifying the host name localhost, the IP address 127.0.0.1, or the IPv6 address ::1. If the user selects the Enable root access from remote machines option during installation, the Windows installer creates another root account that permits connections from any host.

    • On Unix, each root account permits connections from the local host. Connections can be made by specifying the host name localhost, the IP address 127.0.0.1, the IPv6 address ::1, or the actual host name or IP address.

    An attempt to connect to the host 127.0.0.1 normally resolves to the localhost account. However, this fails if the server is run with skip_name_resolve enabled, so the 127.0.0.1 account is useful in that case. The ::1 account is used for IPv6 connections.

  • If accounts for anonymous users were created, these have an empty user name. The anonymous accounts have no password, so anyone can use them to connect to the MySQL server.

    • On Windows, there is one anonymous account that permits connections from the local host. Connections can be made by specifying a host name of localhost.

    • On Unix, each anonymous account permits connections from the local host. Connections can be made by specifying a host name of localhost for one of the accounts, or the actual host name or IP address for the other.

    • The 'root'@'localhost' account also has a row in the mysql.proxies_priv table that enables granting the PROXY privilege for ''@'', that is, for all users and all hosts. This enables root to set up proxy users, as well as to delegate to other accounts the authority to set up proxy users. See Section 6.2.12, “Proxy Users”.

To display which accounts exist in the mysql.user system table and check whether their passwords are empty, use the following statement:

mysql> SELECT User, Host, Password FROM mysql.user;
+------+--------------------+----------+
| User | Host               | Password |
+------+--------------------+----------+
| root | localhost          |          |
| root | myhost.example.com |          |
| root | 127.0.0.1          |          |
| root | ::1                |          |
|      | localhost          |          |
|      | myhost.example.com |          |
+------+--------------------+----------+

This output indicates that there are several root and anonymous-user accounts, none of which have passwords. The output might differ on your system, but the presence of accounts with empty passwords means that your MySQL installation is unprotected until you do something about it:

  • Assign a password to each MySQL root account that does not have one.

  • To prevent clients from connecting as anonymous users without a password, either assign a password to each anonymous account or remove the accounts.

In addition, the mysql.db table contains rows that permit all accounts to access the test database and other databases with names that start with test_. This is true even for accounts that otherwise have no special privileges such as the default anonymous accounts. This is convenient for testing but inadvisable on production servers. Administrators who want database access restricted only to accounts that have permissions granted explicitly for that purpose should remove these mysql.db table rows.

The following instructions describe how to set up passwords for the initial MySQL accounts, first for the root accounts, then for the anonymous accounts. The instructions also cover how to remove anonymous accounts, should you prefer not to permit anonymous access at all, and describe how to remove permissive access to test databases. Replace new_password in the examples with the password that you want to use. Replace host_name with the name of the server host. You can determine this name from the output of the preceding SELECT statement. For the output shown, host_name is myhost.example.com.

You need not remove anonymous entries in the mysql.proxies_priv table, which are used to support proxy users. See Section 6.2.12, “Proxy Users”.

Note

For additional information about setting passwords, see Section 6.2.9, “Assigning Account Passwords”. If you forget your root password after setting it, see Section B.4.3.2, “How to Reset the Root Password”.

To set up additional accounts, see Section 6.2.7, “Adding Accounts, Assigning Privileges, and Dropping Accounts”.

You might want to defer setting the passwords until later, to avoid the need to specify them while you perform additional setup or testing. However, be sure to set them before using your installation for production purposes.

Note

Alternative means for performing the process described in this section:

Assigning root Account Passwords

A root account password can be set several ways. The following discussion demonstrates three methods:

To assign passwords using SET PASSWORD, connect to the server as root and issue a SET PASSWORD statement for each root account listed in the mysql.user system table.

For Windows, do this:

shell> mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('new_password');
mysql> SET PASSWORD FOR 'root'@'::1' = PASSWORD('new_password');
mysql> SET PASSWORD FOR 'root'@'%' = PASSWORD('new_password');

The last statement is unnecessary if the mysql.user table has no root account with a host value of %.

For Unix, do this:

shell> mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('new_password');
mysql> SET PASSWORD FOR 'root'@'::1' = PASSWORD('new_password');
mysql> SET PASSWORD FOR 'root'@'host_name' = PASSWORD('new_password');

You can also use a single statement that assigns a password to all root accounts by using UPDATE to modify the mysql.user table directly. This method works on any platform:

shell> mysql -u root
mysql> UPDATE mysql.user SET Password = PASSWORD('new_password')
    ->     WHERE User = 'root';
mysql> FLUSH PRIVILEGES;

The FLUSH statement causes the server to reread the grant tables. Without it, the password change remains unnoticed by the server until you restart it.

To assign passwords to the root accounts using mysqladmin, execute the following commands:

shell> mysqladmin -u root password "new_password"
shell> mysqladmin -u root -h host_name password "new_password"

Those commands apply both to Windows and to Unix. The double quotation marks around the password are not always necessary, but you should use them if the password contains spaces or other characters that are special to your command interpreter.

The mysqladmin method of setting the root account passwords does not work for the 'root'@'127.0.0.1' or 'root'@'::1' account. Use the SET PASSWORD method shown earlier.

After the root passwords have been set, you must supply the appropriate password whenever you connect as root to the server. For example, to shut down the server with mysqladmin, use this command:

shell> mysqladmin -u root -p shutdown
Enter password: (enter root password here)

The mysql commands in the following instructions include a -p option based on the assumption that you have assigned the root account passwords using the preceding instructions and must specify that password when connecting to the server.

Assigning Anonymous Account Passwords

To assign passwords to the anonymous accounts, connect to the server as root, then use either SET PASSWORD or UPDATE.

To use SET PASSWORD on Windows, do this:

shell> mysql -u root -p
Enter password: (enter root password here)
mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('new_password');

To use SET PASSWORD on Unix, do this:

shell> mysql -u root -p
Enter password: (enter root password here)
mysql> SET PASSWORD FOR ''@'localhost' = PASSWORD('new_password');
mysql> SET PASSWORD FOR ''@'host_name' = PASSWORD('new_password');

To set the anonymous-user account passwords with a single UPDATE statement, do this (on any platform):

shell> mysql -u root -p
Enter password: (enter root password here)
mysql> UPDATE mysql.user SET Password = PASSWORD('new_password')
    ->     WHERE User = '';
mysql> FLUSH PRIVILEGES;

The FLUSH statement causes the server to reread the grant tables. Without it, the password change remains unnoticed by the server until you restart it.

Removing Anonymous Accounts

If you prefer to remove any anonymous accounts rather than assigning them passwords, do so as follows on Windows:

shell> mysql -u root -p
Enter password: (enter root password here)
mysql> DROP USER ''@'localhost';

On Unix, remove the anonymous accounts like this:

shell> mysql -u root -p
Enter password: (enter root password here)

 

 

### sample 10

MySql表、字段、库的字符集修改及查看方法

 

修改数据库字符集：

复制代码 代码如下:

ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...];  


把表默认的字符集和所有字符列（CHAR,VARCHAR,TEXT）改为新的字符集：

复制代码 代码如下:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET character_name [COLLATE ...] 


如：ALTER TABLE logtest CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; 

 

只是修改表的默认字符集：

复制代码 代码如下:

ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE...]; 


如：ALTER TABLE logtest DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 

 

修改字段的字符集：

复制代码 代码如下:

ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE ...]; 


如：ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci; 

 

查看数据库编码：

复制代码 代码如下:

SHOW CREATE DATABASE db_name;  

 


--database 配置 COLLATE
CREATE DATABASE `account` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |


查看表编码：

复制代码 代码如下:

SHOW CREATE TABLE tbl_name;  

 


--table 配置 COLLATE

CREATE TABLE `his_config_info` (
`id` bigint unsigned NOT NULL,
`tenant_id` varchar(128) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT '' COMMENT '租户字段',
PRIMARY KEY (`nid`) USING BTREE,
KEY `idx_gmt_create` (`gmt_create`) USING BTREE,
KEY `idx_gmt_modified` (`gmt_modified`) USING BTREE,
KEY `idx_did` (`data_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=196 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC COMMENT='多租户改造' |

 

 


查看字段编码：

复制代码 代码如下:

SHOW FULL COLUMNS FROM tbl_name;   
 
 

###sample 11   如何修改数据库的编码
mysql -uroot -p -S //mysql/data/mysql.sock

1、使用root连接的服务
2、切换asset数据库
use asset;

3、查看collation编码
show variables like '%coll%';

#可以看出collation_connection和collation_database、collation_server的编码不一致导致应用连接出现错误。需要改成和
connection一致

4、修改collation_database的编码值
#在mysql的shell输入

SHOW CREATE DATABASE asset;
SHOW CREATE DATABASE etl_data;
mysql> SHOW CREATE DATABASE asset;
+----------+----------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------------------------------------+
| asset | CREATE DATABASE `asset` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ |

 

use asset;
alter database asset COLLATE utf8mb4_general_ci;
flush privileges;

5、修改collation_server的编码值
#在配置文件my.cnf的[mysqld] 条目下面加上
collation-server=utf8mb4_general_ci

#然后重启mysqld服务就行