MySQL问题记录

1、Can't connect to MySQL server on localhost (10061)

问题描述：在命令行输入 mysql -u root -p 登录mysql，返回”Can't connect to MySQL server on localhost (10061)”错误

 

问题原因：mysql没有启动。

 

解决方法：1、将mysql加入到Windows的服务中。切换到mysql安装目录下的bin文件夹，命令行运行"mysqld --install"

1 C:\Program Files\MySQL\MySQL Server 5.7\bin> mysqld --install
2 Service successfully installed.

　　此时若使用“net start mysql”成功启动msyql，则无须执行下面步骤

　　　　　2、初始化mysql数据库，输入“mysqld --initialize --user=root --console”。下面红色文字为初始化后的root 密码

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqld --initialize --user=root --console
2016-10-29T01:35:44.309833Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see doc
umentation for more details).
2016-10-29T01:35:46.659968Z 0 [Warning] InnoDB: New log files created, LSN=45790

2016-10-29T01:35:47.154996Z 0 [Warning] InnoDB: Creating foreign key constraintsystem tables.
2016-10-29T01:35:47.362008Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating
a new UUID: 043563f4-9d78-11e6-a8c8-606dc79c383c.
2016-10-29T01:35:47.388009Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2016-10-29T01:35:47.401010Z 1 [Note] A temporary password is generated for root@localhost: u).ig.Gd.7hW

　　　　  3、此时使用“net start mysql”成功启动msyql

　　　　4、使用生成的密码登录mysql，通过“set password=password('123456')”修改密码。此处将root密码设置为123456

mysql> set password=password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec

2、mysql Access denied for user root@localhost

问题描述：
  
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

 

问题原因：

设置密码为空时出现此问题

解决方法：

修改mysql的root密码

方法1： 用SET PASSWORD命令

?

1 2	MySQL -u root 　　mysql> SET PASSWORD FOR 'root' @ 'localhost' = PASSWORD ( 'newpass' );

方法2：用mysqladmin

?

1	mysqladmin -u root password "newpass"

　　如果root已经设置过密码，采用如下方法

?

1	mysqladmin -u root password oldpass "newpass"

方法3： 用UPDATE直接编辑user表

?

1 2 3 4

mysql -u root 　mysql> use mysql; 　mysql> UPDATE user SET Password = PASSWORD ( 'newpass' ) WHERE user = 'root' ; 　mysql> FLUSH PRIVILEGES ;

在丢失root密码的时候，可以这样

?

1 2 3 4

mysqld_safe --skip-grant-tables& 　　mysql -u root mysql 　　mysql> UPDATE user SET password = PASSWORD ( "new password" ) WHERE user = 'root' ; 　　mysql> FLUSH PRIVILEGES ;