出现出现Unknown column ‘password’ in ‘field list’
采用如下语句来更新,因为新版本mysql采用authentication_string替代了password字段
update mysql.user set authentication_string=password('root') where user='root' ;
flush privileges;
quit;
误删root解决方案
1.my.ini底下添加skip-grant-tables
[mysqld]
port = 3306
skip-grant-tables
2.重启进入mysql
use mysql;
3. insert into user set user='root',ssl_cipher='',x509_issuer='',x509_subject='';
4.update user set Host='localhost',select_priv='y', insert_priv='y',update_priv='y',Alter_priv='y',delete_priv='y',create_priv='y',drop_priv='y',reload_priv='y',shutdown_priv='y',Process_priv='y',file_priv='y',grant_priv='y',References_priv='y',index_priv='y',create_user_priv='y',show_db_priv='y',super_priv='y',create_tmp_table_priv='y',Lock_tables_priv='y',execute_priv='y',repl_slave_priv='y',repl_client_priv='y',create_view_priv='y',show_view_priv='y',create_routine_priv='y',alter_routine_priv='y',create_user_priv='y' where user='root';
5.重启连接ok
查询用户
查询host
mysql> select user,host from user;
删除用户
Delete FROM mysql.user Where User='newUser';
修改密码
update mysql.user set password=password('新密码') where user='newUser';
创建用户和密码、授权数据库
创建新用户
create user 'newUser'@'%' identified by '123';
添加权限到指定表
grant all privileges on php.* to 'newUser'@'%' identified by '123' with grant option;
mysql本机以外主机连接数据库
mysql> use mysql;
如果没有"%"这个host值,就执行下面这两句:
mysql> update user set host='%' where user='root';
mysql> flush privileges;//刷新
授权任意主机访问
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '' WITH GRANT OPTION;
mysql> flush privileges;
授权指定主机访问
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.16' IDENTIFIED BY '' WITH GRANT OPTION;
mysql> flush privileges;
(外部连接端口为3306)
长字符串
用longtext类型并且数据库大小进行设置