一、数据库的安装方式及其安装步骤
1、安装方式
rpm包安装，yum安装，二进制包安装，编译安装
2、安装步骤 (本文是使用二进制包安装)
1）下载mariadb相应版本的数据库，解压
tar xf mariadb-5.5.48-linux-86_64.tar.gz -C /usr/local/
2)创建软链接及其创建用户和存放数据目录和授权其目录文件
cd /usr/local
ln -sv mariasb-5.5.48 mysql
chown -R root.mysql mysql
groupadd -r mysql
useradd -r -g mysql mysql
mkdir /mydata/data -pv
chown -R mysql.mysql /mydata/data
3)给数据库提供配置文件及其启动脚本
cp /usr/local/mysql/support-files/my-large.cnf /etc/my.cnf
cp /usr/local/mysql/support-files/my.server /etc/init.d/mysqld
chkconfig --add /etc/init.d/mysqld
chkconfig mysqld on
4)给/etc/my.cnf加上如下项
datadir=/mydata/data
skip-name-resolve=ON
innodb-file-per-table=ON
5)初始化数据库及其启动数据库
/usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/mydata/data
service mysqld start
6)执行mysql_secure_installation命令设置密码及其删除匿名用户等操作
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
[root@centos6 ~]
# mysql_secure_installation
/usr/bin/mysql_secure_installation
: line 379: find_mysql_client:
command
not found
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MariaDB to secure it, we'll need the current
password
for
the root user. If you've just installed MariaDB, and
you haven't
set
the root password yet, the password will be blank,
so you should just press enter here.
Enter current password
for
root (enter
for
none):
OK, successfully used password, moving on...
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
You already have a root password
set
, so you can safely answer
'n'
.
Change the root password? [Y
/n
] n
... skipping.
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created
for
them. This is intended only
for
testing, and to
make
the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous
users
? [Y
/n
] y
... Success!
Normally, root should only be allowed to connect from
'localhost'
. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y
/n
] n
... skipping.
By default, MariaDB comes with a database named
'test'
that anyone can
access. This is also intended only
for
testing, and should be removed
before moving into a production environment.
Remove
test
database and access to it? [Y
/n
] n
... skipping.
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y
/n
] y
... Success!
Cleaning up...
All
done
! If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks
for
using MariaDB!
|
二、MariaDB基础
1、配置文件查找次序
/etc/my.cnf/-->/etc/mysql/my.cnf-->~/.my.cnf
2、mariadb的命令行交互式客服端工具
mysql[options] [database]
常用选项：
-u:username,用户名，默认为root
-h:host远程主机或地址，默认为localhost
-p:password,用户的密码
-D:database_name,设置指定连接库
-e:直接在命令行运行mysql数据库中的命令
实例：mysql -uroot -hlocalhost -p mysql -utestuser -h10.1.%.% -p mysql -uroot -p -e 'show databases';
图示：
3、mariadb数据库命令行客服端命令
常用帮助参数如下注释
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
[root@centos7 ~]
# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection
id
is 19
Server version: 5.5.44-MariaDB MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type
'help;'
or
'\h'
for
help. Type
'\c'
to
clear
the current input statement.
MariaDB [(none)]>
MariaDB [(none)]> \?
#获取帮助也可使用help命令
General information about MariaDB can be found at
http:
//mariadb
.org
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.
For server side help,
type
'help contents'
MariaDB [(none)]>
|
4、数据库常用数据类型
1）字符型
char，varchar,binary,varbinary,txt,blob...
2)数值型
int,float,double,tinyint...
3)枚举型
set,enum
4)时间日期型
date,time,datetime,timestamp,year
5)数据类型修饰符
unsigned,not null,default
三、服务端命令
1、数据定义语言(DDL)，主要用于管理数据库组件，例如索引，视图，用户，存储过程,主要命令有：create，alter，drop。
对库的操作：
创建：create
create {database|schema} [if not exists] db_name
修改：alter
alter {database|schema} [db_name]
删除：drop
drop {database|schema} [if exists] {db_name}
查看支持的字符集：show character set
查看支持的所有排序的规则：show collation;
图示：
对表的操作：
创建表：
create [temporary] table [if not exists] tbl_name
修改表：
alter [online|offline] [ignore] table tbl_name
字段：
添加：add [column] col_name column_definition
删除：drop [column] col_name
修改：change [column] old_col_name new_col_name column_definition
modify [column] col_name column_definition
键：
添加：ADD [CONSTRAINT [symbol]] PRIMARY KEY
add {primary key unique key foreign}(col1,col2...)
删除：
主键：drop primary key
外键：drop foreign key fk_name
索引：
添加：add {index|key} {index_name}
删除：drop {index|key} index_name
主要实例如下：
|
1
2
3
4
5
6
7
8
9
10
|
create database testdb;
use testdb;
create table
if
not exists students(
id
int unsigned NOT NULL primary key, name char(20) NOT NULL, age tinyint unsigned, gender enum(
'f'
,
'm'
));
alter table students add class varchar(20) not null after age;
alter table students change gender sex enum(
'f'
,
'm'
);
alter table students modify class char(30) after sex;
alter table students drop primary key ;
alter table students add primary key(
id
,name);
alter table students add index class (class);
alter table students drop index class;
|
图示：
创建库、表及其查看表结构
添加和修改字段名字及其类型
修改主键及其添加主键
查看建表信息及其表的状态信息
2、数据操纵语言(DML)，主要用于管理表中的数据，实现数据的增删改查。
插入数据：insert into
insert into tbl_name [cols....] values (val1,val2,...)
实例：insert into students (id,name) value (1,'alren');
insert into students values (2,'alren',29,'m','one');
查询数据： select
select [col1,col2,...] from tbl_name where clause;
实例：select * from students where id=1;
select name,age from students where age>11 and age<20;
select name,age from students where age between 11 and 20;
select name,age from students where name rlike 'en$';
select age,sex from students where age is not;
select age,sex from students where age is not null;
select id,name from students order by id desc;
select id,name,age from students order by id asc;
更新数据：update
update [low_priority] [ignore] table_reference
update students set age=age+10 where name like '%lren';
删除数据：delete from
delete from tbl_name where clause;
delete from students; #删除表中的所有数据，很危险，谨慎操纵。
delete from students order by age desc limit 20;
图示：
修改表为自增长
插入数据两种方式
查询表中数据
更新数据
删除数据
四、创建用户及其授权管理
1、创建用户账号
create user 'username'@'host' [indentified by 'password']
实例：create user 'testuser'@'10.1.10.%.%' identified by 'pass';
2、删除用户账号
DROP USER user [, user] ...
drop user 'username'@'host'
图示：
删除和授权用户
测试是否授权成功
远程登录测试成功
3、授权管理
grant privileges_type on [object_type] db_name.tabl_name to 'username'@'host' identified by 'password'
privileges_type:all,create,alter,drop,delete,update,insert,select
库表的对应有如下关系：db_name.tbl_name
*.*:所有库的中的所有表
db_name.*:指定库的所有表
db_name.tbl_name:指定库的指定表
db_name.routine_name:指定库上的存储函数或过程
图示：
创建test用户为其授权测试
授予test用户所有的权限
回收用户权限
查看当前用户及其指定用户授权信息
授权一用户只给查询和插入权限，则除此权限外其他权限均无
本文转自chengong1013 51CTO博客，原文链接：http://blog.51cto.com/purify/1860056，如需转载请自行联系原作者
