Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System


当前位置: 首页 » 技术文章 » MySQL

MySQL入门


本文包含了日常的基本操作:用户基本管理,权限分类,密码策略及修改方法,初始化参数查询和设置,创建库、表、存储过程、自定义函数,在不知道root密码的情况下如何修改root密码等。


MySQL使用手册及各版本的变化

MySQL Utilities Release Notes

Expert Guides

MySQL Test Framework 2.0

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 user
drop 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 kill
kill
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
Bye
MySQL中符号`
这个符号是对数据库名、表明、字段的特殊处理。可有可无,使用关键字时,需要使用`,以防止用户自定义的名称和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

https://dev.mysql.com/doc/refman/5.5/en/mysql-commands.html

http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html



关键词:mysql 

相关文章

OGG from MySQL to Oracle
MySQL数据库高可用实践
Install oracle products on docker
MySQL Cookbook for Oracle DBA
MySQL HA - Innodb Cluster
腾讯微信的PhxSQL数据库
MySQL Group Replication(MGR)
MySQL and Oracle Enterprise Manager
MySQL Tools for performance, backup, monitor
MySQL Multi-Master Single-Slave Replication
MySQL and GPL
MySQL monthly report from alibaba
Top