MariaDB数据库的操作使用与MySQL基本相同。
https://wenku.baidu.com/view/737628694a35eefdc8d376eeaeaad1f3469311a2.html
http://www.360doc.com/content/12/0518/13/4919917_211877836.shtml
常用操作:
登陆
1. 连接MariaDB:mysql -uroot -p
2. 连接远程主机上的MariaDB:mysql -h110.110.110.110 -uroot -pabcd12
3. 退出MYSQL命令: exit
4. 修改密码:mysqladmin -uroot -pab12 password djg345
查询
- 创建数据库:create database 库名;
- 删除库:drop database 库名;
- 显示数据库列表: show databases;
- 进入数据库:use work;
- 查看数据库中的表单:show tables;
- 创建表:create table 表名 (字段设定列表);
- 删除表:drop table 表名;
- 查看某个表单的数据表结构:describe 表名;
- 表中记录清空:delete from 表名;
- 显示表中所有内容:select * from 表名;
修改数据库结构
- 增加字段 :alter table dbname add column <字段名><字段选项>
- 修改字段:alter table dbname change <旧字段名> <新字段名><选项>
- 删除字段:alter table dbname drop column <字段名>
开启远程连接:root是登陆数据库的用户,123456是登陆数据库的密码,*就是意味着任何来源任何主机反正就是权限很大的样子。
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'IDENTIFIED BY '123456' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.120.110'IDENTIFIED BY '123456' WITH GRANT OPTION;
flush privileges;
实例:
create database school; //建立库SCHOOL
use school; //打开库SCHOOL
create table teacher //建立表TEACHER
(
id int(3) auto_increment not null primary key, //id设置为主关键字,并自动设值,也就是添加的时候,你不必向ID字段写内容
name char(10) not null,
address varchar(50) default ‘深圳’, //设置默值为深圳
year date
); //建表结束
//以下为插入字段
insert into teacher values(”,’glchengang’,’深圳一中’,’1976-10-10’); //ID不用写内容
insert into teacher values(”,’jack’,’深圳一中’,’1975-12-23’);
一、配置
-
安装MariaDB数据库
-
[root@localhost ~]#yum install -y mariadb mariadb-server
启动mariadb服务:
-
[root@localhost ~]#systemctl start mariadb
设置开机自启动:
-
[root@localhost ~]#systemctl enable mariadb.service
防火墙添加mysql服务:
-
[root@localhost ~]#firewall-cmd –add-service=mysql –permanent
修改配置文件/etc//my.cnf文件,添加以下编解内容:
-
[root@localhost ~]#character-set-server=utf8
[root@localhost ~]#datadir=/var/lib/mysql #数据库文件存储位置:
二、管理数据库
-
查看数据库:
-
MariaDB [(none)]> show databases;
查看当前用户:
-
MariaDB [(none)]> select user();
查看数据库当前的所有属性信息:
-
MariaDB [(none)]> status
进入数据库:
-
MariaDB [(none)]> use school;
显示表:
-
MariaDB [school]> show tables;
创建表:
-
MariaDB [school]> create table teacher(id int,name varchar(10),gender varchar(5) )
查看表结构:
-
MariaDB [school]> desc teacher;
查询和插入表数据:
-
MariaDB [school]> select * from teacher;
MariaDB [school]> insert into teacher values(‘1’,’lisi’,’male’);
清空表内容:
-
MariaDB [school]> truncate table teacher;
添加列:
-
MariaDB [school]> alter table teacher add department varchar(20) [first|after column];
删除列:
-
MariaDB [school]> alter table teacher drop department;
外和内连接:
-
MariaDB [school]> select * from teacher join student using(id);
MariaDB [school]> select * from teacher inner join student where a.id=b.id;
三、配置数据库
-
用户管理:
-
MariaDB [(none)]> use mysql;
MariaDB [mysql]> desc users;
MariaDB [mysql]> select host,user,password from user;
给用户root设置密码的方法:
-
[root@linuxidc ~]# mysqladmin -uroot -p password ‘RedHat’ #或者
-
MariaDB [(none)]> set password=password(‘redhat’); #或者
-
MariaDB [(none)]> update mysql.user set password=password(‘redhat’) where user=’root’ and host=’localhost’;
MariaDB [(none)]> flush privileges;
忘记root密码重新设置密码,使用以下2种方式重新修改密码:
- 1、修改my.cnf文件,加入以下语句:
-
skip-grant-tables
直接进入数据库无需密码,然后执行以下修改密码的命令:
-
MariaDB [(none)]> update mysql.user set password=password(‘redhat’) where user=’root’ and host=’localhost’;
MariaDB [(none)]> flush privileges;
-
- 2、使用mysqld-safe命令修改密码: 先停止mysqld服务,再修改密码:
-
[root@linuxidc ~]# systemctl stop mariadb.service
[root@linuxidc ~]# mysqld_safe –skip-grant-tables
MariaDB [(none)]> update mysql.user set password=password(‘redhat’) where user=’root’ and host=’localhost’;
MariaDB [(none)]> flush privileges;
创建普通用户并设置密码:
-
MariaDB [(none)]> create user redhat@’localhost’;
MariaDB [(none)]> create user redhat@’%’;任意主机
MariaDB [(none)]> set password for redhat@’localhost’=password(‘redhat’);
查看用户的权限:
-
MariaDB [(none)]> show grants for redhat;
查看系统的所有权限:
-
MariaDB [(none)]> show privileges;
授权给用户:
-
MariaDB [(none)]> grant create,insert,drop,update on school.* to redhat@’%’ identified by ‘redhat’;
回收权限:
-
MariaDB [(none)]> revoke drop,update on school.* from redhat;
四、数据库的备份与恢复
冷备份:停机备份数据库文件;
-
热备份:(使用mysqldump命令备份)
-
[root@linuxidc ~]# mysqldump -u root -p school teacher student> /mysql_backup/teacher.sql
备份整个数据库表,后面则不需要指定任何表:
-
[root@linuxidc ~]# mysqldump -u root -p school> /mysql_backup/all_tables.sql
备份整个数据库:
-
[root@linuxidc ~]# mysqldump -u root -p -B school> /mysql_backup/all.sql
进入库恢复表或者恢复表:
-
MariaDB [school]> source /mysql_backup/teacher.sql;
[root@linuxidc ~]# mysql -u root -p’redhat’ < /mysql_backup/all.sql
将表数据保存到文件,修改备份目录的属主属组信息:
-
[root@linuxidc ~]# setfacl -m u:mysql:rwx /mysql_backup/
MariaDB [school]> select * from teacher into outfile ‘/mysql_backup/teacher_data’fields terminated by ‘,’;
根据外部文件恢复表数据:
-
MariaDB [school]> load data infile ‘/mysql_backup/teacher_data’ into table teacher fields terminated by ‘,’;
mysqldump不能做增量备份: