本文包含了日常的基本操作:用户基本管理,权限分类,密码策略及修改方法,初始化参数查询和设置,创建库、表、存储过程、自定义函数,在不知道root密码的情况下如何修改root密码等。
MySQL使用手册及各版本的变化
Expert Guides
MySQL用户管理
MySQL的用户和其他数据库的用户不太一样,她的用户由用户名和主机字符串构成。
ohsdba@localhost
ohsdba@10.0.2.10
ohsdba@192.168.0.%
10.0.2.% 匹配 10.0.2.[0-255]
192.168._.% 匹配 192.168.[0-9].[0-255]
%.oracle.com 匹配任何以oracle.com结尾的主机
查看所有用户
select user,host from mysql.user;
查看当前用户
select user();
select current_user();
创建用户
create user 'ohsdba'@'10.0.2.10' identified by 'password';
删除用户
drop user 'ohsdba'@'10.0.2.10';
重命名用户
rename user 'ohsdba'@'10.0.2.10' to 'dba'@'10.0.2.10';
设置用户密码
set password for 'ohsdba'@'10.0.2.10' = password('new password');
mysql> mysql> create user oracle identified by 'Oracle12'; #创建用户 Query OK, 0 rows affected (0.01 sec) mysql> drop user oracle@'%'; Query OK, 0 rows affected (0.00 sec) mysql> [root@mysql ~]# mysql -u root -p Enter password: mysql> show create user ohsdba\G *************************** 1. row *************************** CREATE USER for ohsdba@%: CREATE USER 'ohsdba'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*B370ECE36F8EEE6E03971D22FBFCDD655DFA659D' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK 1 row in set (0.00 sec) mysql> show create user root@localhost\G *************************** 1. row *************************** CREATE USER for root@localhost: CREATE USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*DA7C403AB735A5A4C142745CB668CAA26931AADD' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK 1 row in set (0.00 sec) mysql> mysql> SET PASSWORD FOR ohsdba = PASSWORD('RobinHan'); #设置ohsdba用户密码 Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> mysql> set password = password('Oracle12'); #设置root用户密码 Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> mysql> alter user ohsdba account lock; #锁住账户 Query OK, 0 rows affected (0.00 sec) mysql> alter user ohsdba account unlock; #账户解锁 Query OK, 0 rows affected (0.00 sec) mysql> alter user ohsdba identified by 'Oracle12'; #修改用户密码 Query OK, 0 rows affected (0.00 sec) mysql> "%" 表示对所有非本地主机授权,不包括localhost,对localhost授权: mysql> grant all privileges on ohs.* to ohsdba@localhost identified by 'Oracle24'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> mysql> drop user ohsdba@localhost; Query OK, 0 rows affected (0.00 sec) mysql> mysql> drop user ohsdba@'%'; Query OK, 0 rows affected (0.00 sec)
MySQL权限列表
all | grants all privileges (except grant option) |
alter | allows a user to use alter table |
alter routine | allows a user to alter or drop stored routines |
create | allows a user to use create table |
create routine | allows a user to create stored routines |
create temporary tables | allows a user to use create temporary table |
create user |
allows a user to use create userdrop user rename user revoke all privileges |
create view | allows a user to use create view |
delete | allows a user to use delete |
drop | allows a user to use drop |
event | allows a user to use create event and drop event |
execute | allows a user to run stored routines |
file | allows a user to execute both select into outfile and load data infile |
grant option | allows a user to grant other users privileges |
index | allows a user to use create index and drop index |
insert | allows a user to use create insert |
lock tables | allows a user to use lock tables |
process | allows a user to see all processes when executing show processlist |
references | this is not used |
reload | allows a user to execute flush |
replication client | allows a user to execute both show master status and show slave status |
replication slave | needed by the replication slave to read binary logs from the master |
select | allows a user to execute select |
show databases | when a user executes show databases command will return a list of all databases |
show view | allows a user to execute show create view |
shutdown | allows a user to execute mysqladmin shutdown |
super |
allows a user to execute change master killkill purge master logs set global |
trigger | allows a user to use create trigger and drop trigger |
update | allows a user to execute update |
usage | allows a user to connect |
为了安全,尽量避免以下权限(grant, process, shutdown, super, file)
revoke grant option on *.* from 'test'@'%'; revoke process on *.* from 'test'@'%'; revoke shutdown on *.* from 'test'@'%'; revoke super on *.* from 'test'@'%'; revoke file on *.* from 'test'@'%';
MySQL授权分类
全局级别:grant all on *.*,revoke all on *.* 数据库级别:grant all ondb_name.*,revoke all on db_name.* 表级别:GRANT all on db_name.tbl_name,revoke all on db_name.tbl_name
列级别:grant select (col1,col2), insert (col1,col2), update (col1,col2) on <database>.<table_name> to 'ohsdba'@'%';
存储过程/函数:grant create,alert,execute routine on database.* to 'ohsdba'@'%'; mysql@MySQL> show tables like '%priv%'; +--------------------------+ | Tables_in_mysql (%priv%) | +--------------------------+ | columns_priv | | procs_priv | | proxies_priv | | tables_priv | +--------------------------+ 4 rows in set (0.00 sec) mysql@MySQL>
mysql> show grants; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ 2 rows in set (0.00 sec) mysql>
mysql> show grants for 'ohsdba'@'%'; +---------------------------------------------+ | Grants for ohsdba@% | +---------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'ohsdba'@'%' | +---------------------------------------------+ 1 row in set (0.00 sec) mysql>MySQL帮助信息
mysql> help List of all MySQL commands: Note that all text commands must be first on line and end with ';' ? (\?) Synonym for `help'. clear (\c) Clear the current input statement. connect (\r) Reconnect to the server. Optional arguments are db and host. delimiter (\d) Set statement delimiter. edit (\e) Edit command with $EDITOR. ego (\G) Send command to mysql server, display result vertically. exit (\q) Exit mysql. Same as quit. go (\g) Send command to mysql server. help (\h) Display this help. nopager (\n) Disable pager, print to stdout. notee (\t) Don't write into outfile. pager (\P) Set PAGER [to_pager]. Print the query results via PAGER. print (\p) Print current command. prompt (\R) Change your mysql prompt. quit (\q) Quit mysql. rehash (\#) Rebuild completion hash. source (\.) Execute an SQL script file. Takes a file name as an argument. status (\s) Get status information from the server. system (\!) Execute a system shell command. tee (\T) Set outfile [to_outfile]. Append everything into given outfile. use (\u) Use another database. Takes database name as argument. charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets. warnings (\W) Show warnings after every statement. nowarning (\w) Don't show warnings after every statement. resetconnection(\x) Clean session context. For server side help, type 'help contents'mysql> help show;
通过show命令我们可以查看对象的创建语句,就像oracle中dbms_metadata.get_ddl。还可以查看全局和会话级别参数信息,就像Oracle数据库的初始化参数一样。
Name: 'SHOW' Description: SHOW has many forms that provide information about databases, tables, columns, or status information about the server. This section describes those following: SHOW {BINARY | MASTER} LOGS SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count] SHOW CHARACTER SET [like_or_where] SHOW COLLATION [like_or_where] SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where] SHOW CREATE DATABASE db_name SHOW CREATE EVENT event_name SHOW CREATE FUNCTION func_name SHOW CREATE PROCEDURE proc_name SHOW CREATE TABLE tbl_name SHOW CREATE TRIGGER trigger_name SHOW CREATE VIEW view_name SHOW DATABASES [like_or_where] SHOW ENGINE engine_name {STATUS | MUTEX} SHOW [STORAGE] ENGINES SHOW ERRORS [LIMIT [offset,] row_count] SHOW EVENTS SHOW FUNCTION CODE func_name SHOW FUNCTION STATUS [like_or_where] SHOW GRANTS FOR user SHOW INDEX FROM tbl_name [FROM db_name] SHOW MASTER STATUS SHOW OPEN TABLES [FROM db_name] [like_or_where] SHOW PLUGINS SHOW PROCEDURE CODE proc_name SHOW PROCEDURE STATUS [like_or_where] SHOW PRIVILEGES SHOW [FULL] PROCESSLIST SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n] SHOW PROFILES SHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count] SHOW SLAVE HOSTS SHOW SLAVE STATUS [NONBLOCKING] SHOW [GLOBAL | SESSION] STATUS [like_or_where] SHOW TABLE STATUS [FROM db_name] [like_or_where] SHOW [FULL] TABLES [FROM db_name] [like_or_where] SHOW TRIGGERS [FROM db_name] [like_or_where] SHOW [GLOBAL | SESSION] VARIABLES [like_or_where] SHOW WARNINGS [LIMIT [offset,] row_count] like_or_where: LIKE 'pattern' | WHERE expr If the syntax for a given SHOW statement includes a LIKE 'pattern' part, 'pattern' is a string that can contain the SQL "%" and "_" wildcard characters. The pattern is useful for restricting statement output to matching values. Several SHOW statements also accept a WHERE clause that provides more flexibility in specifying which rows to display. See http://dev.mysql.com/doc/refman/5.7/en/extended-show.html. URL: http://dev.mysql.com/doc/refman/5.7/en/show.html mysql>设置提示符
[root@mysql ~]# export MYSQL_PS1="`hostname`@MySQL> " mysql@MySQL> prompt ohs@MySQL> PROMPT set to 'ohs@MySQL> ' ohs@MySQL> 可以通过export环境变量和prompt命令实现MySQL注视
-- 单行注释 /* */ 多行注释如果设置MySQL初始化参数
设置一个GLOBAL变量的值 mysql> set global sort_buffer_size=value; mysql> set @@global.sort_buffer_size=value; 设置一个SESSION变量的值 mysql> set session sort_buffer_size=value; mysql> set @@session.sort_buffer_size=value; mysql> set sort_buffer_size=value; 查询一个GLOBAL变量的值 mysql> select @@global.sort_buffer_size; mysql> show global variables like 'sort_buffer_size'; 查询一个SESSION变量的值 mysql> select @@sort_buffer_size; mysql> select @@session.sort_buffer_size; mysql> show session variables like '';密码策略(当前版本5.7.16)
mysql> show variables like '%pass%'; +---------------------------------------+-------+ | Variable_name | Value | +---------------------------------------+-------+ | default_password_lifetime | 0 | | disconnect_on_expired_password | ON | | log_builtin_as_identified_by_password | OFF | | mysql_native_password_proxy_users | OFF | | old_passwords | 0 | | report_password | | | sha256_password_proxy_users | OFF | | validate_password_check_user_name | OFF | | validate_password_dictionary_file | | | validate_password_length | 8 | | validate_password_mixed_case_count | 1 | | validate_password_number_count | 1 | | validate_password_policy |MEDIUM | | validate_password_special_char_count | 1 | +---------------------------------------+-------+ 14 rows in set (0.00 sec) mysql> mysql> select curtime(); +-----------+ | CURTIME() | +-----------+ | 15:22:59 | +-----------+ 1 row in set (0.00 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2016-11-09 15:23:02 | +---------------------+ 1 row in set (0.00 sec) mysql>
mysql> select user(); +----------------+ | user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql> select current_user(); +----------------+ | current_user() | +----------------+ | root@localhost | +----------------+ 1 row in set (0.00 sec) mysql>在忘记密码的情况下如何重置root密码?
关闭mysql数据库,通过mysqld_safe启动 [root@mysql ~]# mysqld_safe --skip-grant-tables & [1] 8784 [root@mysql ~]# 2016-11-08T05:27:38.192954Z mysqld_safe Logging to '/var/log/mysqld.log'. 2016-11-08T05:27:38.216762Z mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql [root@mysql ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 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> UPDATE user SET Password = password ('ohsdba') WHERE User = 'root' ; ERROR 1054 (42S22): Unknown column 'Password' in 'field list'
#在5.7中password字段变成了authentication_string
mysql> update user set authentication_string=password('ohsdba') where user='root'; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> exit Bye [root@mysql ~]# /etc/init.d/mysqld stop 2016-11-08T05:30:04.476301Z mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended Stopping mysqld: [ OK ] [1]+ Done mysqld_safe --skip-grant-tables [root@mysql ~]# [root@mysql ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.7.16 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SET PASSWORD = PASSWORD('Welc0me12'); #用新密码登陆后,需要重新设置新密码,然后才能继续操作 ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql> SET PASSWORD = PASSWORD('Welc0me12@oracle.com'); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql>MySQL创建数据库和表
mysql> create database ohs; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | ohs | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> mysql> grant select,delete,update,create,drop on *.* to ohsdba@"%" identified by 'oracle'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql> mysql> show warnings\G #show warnings和show errors是一样的效果 *************************** 1. row *************************** Level: Error Code: 1819 Message: Your password does not satisfy the current policy requirements 1 row in set (0.00 sec) mysql> mysql> show variables like '%policy%'\G *************************** 1. row *************************** Variable_name: validate_password_policy Value: MEDIUM 1 row in set (0.01 sec) mysql> set GLOBAL validate_password_policy=low; Query OK, 0 rows affected (0.00 sec) mysql> mysql> grant select,delete,update,create,drop on *.* to ohsdba@"%" identified by 'Oracle12'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> mysql> use ohs Database changed mysql> create table t1(id int,name varchar(20)); Query OK, 0 rows affected (0.00 sec) mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql>执行SQL脚本
[root@mysql ~]# cat my.sql create database ora2mysql; use ora2mysql; create table ora(name varchar(20)); [root@mysql ~]# [root@mysql ~]# mysql -u root < my.sql #方法一 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | ohs | | ora2mysql | | performance_schema | | sys | +--------------------+ 6 rows in set (0.00 sec) mysql> use ora2mysql 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> show tables; +---------------------+ | Tables_in_ora2mysql | +---------------------+ | ora | +---------------------+ 1 row in set (0.00 sec) mysql> drop database ora2mysql; Query OK, 1 row affected (0.01 sec) mysql> source /root/my.sql #方法二 Query OK, 1 row affected (0.00 sec) Database changed Query OK, 0 rows affected (0.01 sec) mysql> drop database ora2mysql; Query OK, 1 row affected (0.01 sec) mysql> \. /root/my.sql #方法三 Query OK, 1 row affected (0.00 sec) Database changed Query OK, 0 rows affected (0.01 sec) mysql> show tables; +---------------------+ | Tables_in_ora2mysql | +---------------------+ | ora | +---------------------+ 1 row in set (0.00 sec) mysql>MySQL表
show tables; #查看当前数据库下的全部的表 mysql> create table user1 as select user,host,account_locked from user; Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> desc user1; +----------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+---------------+------+-----+---------+-------+ | user | char(32) | NO | | | | | host | char(60) | NO | | | | | account_locked | enum('N','Y') | NO | | N | | +----------------+---------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> show create table user1\G *************************** 1. row *************************** Table: user1 Create Table: CREATE TABLE `user1` ( `user` char(32) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', `host` char(60) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '', `account_locked` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> mysql> grant select on mysql.user1 to ohsdba; Query OK, 0 rows affected (0.01 sec) mysql> mysql> revoke select on mysql.user1 from ohsdba; Query OK, 0 rows affected (0.00 sec) mysql> revoke all on mysql.* from ohsdba;如何设置MySQL root用户免密码登录(修改密码策略,设置为空密码)
mysql> show variables like '%pass%'; +---------------------------------------+-------+ | Variable_name | Value | +---------------------------------------+-------+ | default_password_lifetime | 0 | | disconnect_on_expired_password | ON | | log_builtin_as_identified_by_password | OFF | | mysql_native_password_proxy_users | OFF | | old_passwords | 0 | | report_password | | | sha256_password_proxy_users | OFF | | validate_password_check_user_name | OFF | | validate_password_dictionary_file | | | validate_password_length | 8 | | validate_password_mixed_case_count | 1 | | validate_password_number_count | 1 | | validate_password_policy | LOW | | validate_password_special_char_count | 1 | +---------------------------------------+-------+ 14 rows in set (0.00 sec) mysql> set global validate_password_mixed_case_count=0; Query OK, 0 rows affected (0.00 sec) mysql> set global validate_password_number_count=0; Query OK, 0 rows affected (0.00 sec) mysql> set global validate_password_special_char_count=0; Query OK, 0 rows affected (0.00 sec) mysql> set global validate_password_length=0; Query OK, 0 rows affected (0.00 sec) mysql> show variables like '%pass%'; +---------------------------------------+-------+ | Variable_name | Value | +---------------------------------------+-------+ | default_password_lifetime | 0 | | disconnect_on_expired_password | ON | | log_builtin_as_identified_by_password | OFF | | mysql_native_password_proxy_users | OFF | | old_passwords | 0 | | report_password | | | sha256_password_proxy_users | OFF | | validate_password_check_user_name | OFF | | validate_password_dictionary_file | | | validate_password_length | 0 | | validate_password_mixed_case_count | 0 | | validate_password_number_count | 0 | | validate_password_policy | LOW | | validate_password_special_char_count | 0 | +---------------------------------------+-------+ 14 rows in set (0.01 sec) [root@mysql ~]# 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> update user set authentication_string=password('') where user='root' and host='localhost'; 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> exit ByeMySQL中符号`
这个符号是对数据库名、表明、字段的特殊处理。可有可无,使用关键字时,需要使用`,以防止用户自定义的名称和MySQL保留字冲突
mysql@MySQL> create table table(a int); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table(a int)' at line 1 mysql@MySQL> show errors\G *************************** 1. row *************************** Level: Error Code: 1064 Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table(a int)' at line 1 1 row in set (0.00 sec) mysql@MySQL> create table `table`(a int); Query OK, 0 rows affected (0.01 sec) mysql@MySQL> desc `table`; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | a | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql@MySQL>MySQL存储过程
mysql@MySQL> create table t(name varchar(20)); Query OK, 0 rows affected (0.01 sec) mysql@MySQL> insert into t values ('oracle'); Query OK, 1 row affected (0.00 sec) mysql@MySQL> insert into t values('mysql'); Query OK, 1 row affected (0.00 sec) 定义分隔符,因为procedure里面包含了; delimiter // --创建存储过程 CREATE PROCEDURE simpleproc (OUT param1 INT) BEGIN SELECT COUNT(*) INTO param1 FROM t; END// delimiter ; --执行存储过程 CALL simpleproc(@a); mysql@MySQL> delimiter // mysql@MySQL> CREATE PROCEDURE simpleproc (OUT param1 INT) -> BEGIN -> SELECT COUNT(*) INTO param1 FROM t; -> END// Query OK, 0 rows affected (0.00 sec) mysql@MySQL> delimiter ; mysql@MySQL> CALL simpleproc(@a); Query OK, 1 row affected (0.00 sec) mysql@MySQL> SELECT @a; +------+ | @a | +------+ | 2 | +------+ 1 row in set (0.00 sec) mysql@MySQL>MySQL函数
这个函数里面没有;,所以可以付设置分隔符 CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) DETERMINISTIC RETURN CONCAT('Hello, ',s,'!'); mysql@MySQL> CREATE FUNCTION hello (s CHAR(20)) -> RETURNS CHAR(50) DETERMINISTIC -> RETURN CONCAT('Hello, ',s,'!'); Query OK, 0 rows affected (0.00 sec) mysql@MySQL> select hello('welcoome ohsdba'); +--------------------------+ | hello('welcoome ohsdba') | +--------------------------+ | Hello, welcoome ohsdba! | +--------------------------+ 1 row in set (0.00 sec) mysql@MySQL>查看MySQL相关参数
mysqld --no-defaults --verbose --help http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html
查看my.cnf配置文件位置
[root@mysql ~]# mysqld --no-defaults --verbose --help|grep my.cnf /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default [root@mysql ~]#
通过insert语句创建用户
insert into mysql.user(Host,User,Password) values("localhost","Oracle",password("Oracle12")); 下面的测试版本为在5.7.16 mysql@MySQL> insert into mysql.user(Host,User,authentication_string,ssl_cipher,x509_issuer,x509_subject)values("localhost","Oracle",password("Oracle12"),' ',' ',' '); Query OK, 1 row affected, 1 warning (0.00 sec) mysql@MySQL> show warnings\G *************************** 1. row *************************** Level: Warning Code: 1681 Message: 'PASSWORD' is deprecated and will be removed in a future release. 1 row in set (0.00 sec) mysql@MySQL> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql@MySQL>
MySQL客户端工具
不错的MySQL客户端工具MySQL workbench,PhpMyAdmin,Navicat
Reference
http://dev.mysql.com/doc/refman/5.7/en/data-types.html
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html