MariaDB安装及启动：
yum groupinstall mariadb
启动mariadb
systemctl restart mariadb
systemctl enable mariadb
netstat -anplut | grep mysql #mysql端口3306
firewall-cmd --permanent --add-service=mysql
firewall-cmd --reload mysqld_db_t
#SElinux上下文
ll /usr/share/mysql/ #有4个规模的配置文件模板
vim /etc/my.cnf #配置文件
ll /var/lib/mysql #每个数据库会在此文件夹下建立一个单独目录
创建测试scott数据库的sql脚本
#vi /scott.sql
create database scott;
use scott
create table dept(
-- 部门编号
deptno int unsigned auto_increment primary key,
-- 部门名称
dname varchar(15) ,
-- 部门所在位置
loc varchar(50)
)engine = InnoDB;
create table emp(
-- 雇员编号
empno int unsigned auto_increment primary key,
-- 雇员姓名
ename varchar(15) ,
-- 雇员职位
job varchar(10) ,
-- 雇员对应的领导的编号
mgr int unsigned ,
-- 雇员的雇佣日期
hiredate date ,
-- 雇员的基本工资
sal decimal(7,2) ,
-- 奖金
comm decimal(7,2) ,
-- 所在部门
deptno int unsigned ,
foreign key(deptno) references dept(deptno)
)engine = innodb;
create table salgrade(
-- 工资等级
grade int unsigned ,
-- 此等级的最低工资
losal int unsigned ,
-- 此等级的最高工资
hisal int unsigned
)engine=innodb;
create table bonus(
-- 雇员姓名
ename varchar(10),
-- 雇员职位
job varchar(9),
-- 雇员工资
sal decimal(7,2),
-- 雇员资金
comm decimal(7,2)
)engine=innodb;
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-4-2',2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10);
INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'87-7-13',3000,NULL,20);
INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,30);
INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'87-7-13',1100,NULL,20);
INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'1981-12-3',950,NULL,30);
INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'1981-12-3',3000,NULL,20);
INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'1982-1-23',1300,NULL,10);
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);
无密码登录到mariadb
mysql #没设置root密码之前可以直接登录
select database(); #查看当前进入的数据库
select user(); #查看当前登录用户
show databases; #查看数据库
use test #进入test数据库
show tables; #查看此数据库的表，test没表，可以看其他数据库的
desc emp; #查看表结果
show variables like 'innodb%'; #查看环境变量
设置root密码
mysqladmin -u root password '123456'
mysql -u root -p #输入密码即可进入
调用系统资源
system ls -l /root #system+系统指令，可以直接显示系统指令执行结果
source /scott.sql #执行sql脚本
status #查看mariadb属性
MariaDB [(none)]> status -------------- mysql Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 10 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 5.5.56-MariaDB MariaDB Server Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 18 hours 11 min 3 sec
Server characterset: latin1 #服务器的字符集
Db characterset: latin1 #数据库的字符集
Client characterset: utf8 #客户端的字符集
Conn. characterset: utf8 #连接的字符集
客户端的字符集要和数据库的字符集一致，不一致有可能乱码,一般企业都使用UTF-8，建议修改mariadb字符集。
修改字符集：
vim /etc/my.cnf
character-set-server=utf8 #增加到[mysqld]字段
[mysqld] character-set-server=utf8 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock .......
systemctl restart mariadb
MariaDB [(none)]> status #重启后变更为新字符集
Server characterset: utf8 Db
characterset: utf8
Client characterset: utf8 Conn.
characterset: utf8
查看数据库的字符集
MariaDB [(none)]> show create database scott;
[root@rhel2 ~]# systemctl restart mariadb [root@rhel2 ~]# mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 2 Server version: 5.5.56-MariaDB MariaDB Server Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. MariaDB [(none)]> status -------------- mysql Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1 Connection id: 2 Current database: Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server: MariaDB Server version: 5.5.56-MariaDB MariaDB Server Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /var/lib/mysql/mysql.sock Uptime: 13 sec Threads: 1 Questions: 4 Slow queries: 0 Opens: 0 Flush tables: 2 Open tables: 26 Queries per second avg: 0.307 -------------- MariaDB [(none)]> show create database scott; +----------+------------------------------------------------------------------+ | Database | Create Database | +----------+------------------------------------------------------------------+ | scott | CREATE DATABASE `scott` /*!40100 DEFAULT CHARACTER SET latin1 */ | +----------+------------------------------------------------------------------+ 1 row in set (0.00 sec) MariaDB [(none)]>
scott数据库是在更改字符集之前建立，所以字符集是latin
drop database scott; #删除数据库
source /scott.sql #使用脚本重建数据库
show create database scott; #在更改字符集之后建立，所以是字符集是utf8
show create table emp;
更改字符集
alter database scott charset utf8;
alter table emp charset utf8;
========================================
导出表数据到外部文件：
MariaDB [scott]> select * from emp into outfile '/var/lib/mysql/scott/scott.emp.txt' fields terminated by ',' lines terminated by '
';
导入外部文件到表：
MariaDB [scott]> load data infile '/var/lib/mysql/scott/scott.emp.txt' into table lxjtest fields terminated by ',' lines terminated by '
';
备份数据库及表：
#mysqldump -u root -p scott > /scott.dump ---备份scott整个库
#mysqldump -u root -p scott emp > /scott_emp.dump --备份scott库中的emp表
# mysqldump -u root -p scott dept salgrade > /scott.dept_salgrade.dump --备份多个表
还原：
# mysql -u root -p scott < /scott
# mysql -u root -p scott < /scott_emp.dump
===========================
mariadb 的用户登录包括用户名和登录位置两部分。
早期版本%就可以代表所有的连接，后期版本localhost表示本地登录，%表示tcp/ip的远程登录的所有ip。
%也可以写具体主机或网段，比如192.168.100.2、192.168.100.% 或者192.168.100.0/255.255.255.0，其他格式不识别。
如果登录主机符合多个连接条件，默认连接第一个身份，设置权限的时候需要注意
select user(); #查看当前登录用户
select host,user,password from user; #查看数据库所有登录范围、用户和密码，4个root不是同一个用户，可以单独设置密码
create user rusky@'localhost' identified by '123456'; #建立可以本地登录的用户rusky并设置密码123456
create user rusky@'%' identified by '123456'; #建立可以远程登录的用户rusky并设置密码123456
create user rusky2@'localhost' identified by '123456'; #建立可以本地登录的用户rusky2并设置密码123456
create user rusky2@'192.168.100.2' identified by '123456'; #建立可以从192.168.100.2主机登录的用户rusky2并设置密码123456
grant all on *.* to rusky@'localhost'; #授权本地登录的用户rusky对所有数据库的所有表有所有权限
grant select on scott. to rusky2@'localhost'; #授权本地登录的用户rusky2可以读取scott数据库的所有表 --帮助提示，输入help grant，有示例。
grant all on *.* to ruskyrusky2@'%' identified by '123456'; #可以建立用户、授权、设置密码一起做
flush privileges; #刷新授权表
show privileges; #查看所有权限
show grants for rusky@'localhost'; #查看用户权限
show grants for rusky2@'localhost';
revoke select on scott. from rusky2@'192.168.100.2'; #收回权限
修改密码
在linux系统中修改
#mysqladmin -u root -p password '123456' #需输入root原始密码
在数据库中修改
MariaDB [mysql]>set password=password('redhat'); #对当前用户设置密码，立刻生效
MariaDB [mysql]>set password for rusky@'localhost'=password('123456'); #对任意用户设置密码
MariaDB [(none)]>use mysql
MariaDB [mysql]>update user set password=password('redhat') where user='root' and host='localhost';
#修改密码后需要flush privileges;刷新权限表，或重启服务
mariadb忘记root密码
方法一 systemctl stop mairadb
vi /etc/my.cnf skip-grant-tables #增加到[mysqld]字段，进入单用户模式
#systemctl start mariadb 输入mysql命令无需密码直接进入数据库
MariaDB [(none)]>use mysql
MariaDB [mysql]>update user set password=password('redhat') where user='root' and host='localhost';
停止mariadb服务，删除配置文件的skip-grant-tables选项，重启mariadb服务，使用新密码进入
方法二 systemctl stop mariadb
mysqld_safe --skip-grant-tables
先停止mariadb，输入参数进入单用户模式光标将停住，新建窗口输入mysql可直接进入数据库并修改密码，完成后退出终端，重启mariadb服务，使用新密码登录
http://www.cnblogs.com/rusking/p/4602051.html
----
#### 提高mariadb的安全性
mysql_secure_installation Enter current password for root (enter for none): #输入当前root的密码，密码为空直接回车即可 Change the root password? [Y/n] #是否修改root的密码 Remove anonymous users? [Y/n] #是否删除匿名用户 Disallow root login remotely? [Y/n] #是否不允许root远程登录 Remove test database and access to it? [Y/n] #是否删除测试数据库test Reload privilege tables now? [Y/n] #是否重新加载授权信息 systemctl restart mariadb
不通过firewalld，mariddb本身就可以禁止网络访问数据库 vim /etc/my.cnf skip-networking=1 #添加在[mysqld]字段
有时需重启系统才生效
systemctl restart mariadb netstat -anplut | grep mysql # 查询不到任何监听端口