最近清理mysql数据库，因误删除了一些数据库。想新添加上去，首先创建用户就报错：
insert into user (Host,User,Password) values ("localhost","user",PASSWORD("1234"));
#1364 - Field 'ssl_cipher' doesn't have a default value
意思必须给ssl_cipher一个默认值。ssl_cipher是什么字段暂不知道。其实user表除了ssl_cipher还有x509_issuer、x509_subject两个二进制字段必须指定默认值。
insert into user (Host,User,Password,ssl_cipher,x509_issuer,x509_subject) values ("localhost","user",PASSWORD("1234"),"","","");
这样就创建成功了。当然我们也可以换一种方法实现。以下就是在MySQL Command Line Client以命令完成的
Enter password: ****** #输入密码
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 73156
Server version: 6.0.2-alpha-community-nt-debug MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create user shbsw;#创建一个新用户shbsw
Query OK, 1 row affected (0.06 sec)
mysql> use mysql;#进入mysql数据库表
Database changed
mysql> update user set Host='localhost',Password = PASSWORD('12345') where User='shbsw'; #修改用户名密码与登陆域
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
mysql> flush privileges;#使改动生效
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on shbsw.* to 'shbsw'@'localhost';#分配所有权限
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for shbsw@localhost; #显示用户权限
+--------------------------------------------------------------------------------------------------------------+
| Grants for shbsw@localhost
|
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'shbsw'@'localhost' IDENTIFIED BY PASSWORD '*271BEAB6F6F
C0D3CEC4A728711DA11CFEEEE09F8' |
| GRANT ALL PRIVILEGES ON `shbsw`.* TO 'shbsw'@'localhost'
|
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
============================
随便说一下，当我们修改了数据库root密码后，使用phpMyAdmin管理mysql时就会提示mysql 使用配置文件中定义的控制用户连接失败。
修复方法是打开：phpMyAdmin/config.inc/php文件
/* User used to manipulate with storage */
$cfg['Servers'][$i]['controluser'] = 'root';
$cfg['Servers'][$i]['controlpass'] = '改成新密码';
然后重新登陆即可！
关键词: mysql,数据库 编辑时间: 2014-04-22 17:32:29