RHCE7认证学习笔记36——MariaDB数据库配置与管理
时间:2016-05-29 04:28 来源:linux.it.net.cn 作者:IT
RHCE7认证学习笔记36——MariaDB数据库配置与管理
MariaDB数据库的操作使用与MySQL基本相同。
一、配置
安装MariaDB数据库
[root@it.net.cn ~]# yum install mariadb\* -y
启动mariadb服务:
[root@it.net.cn ~]# systemctl start mariadb
防火墙添加mysql服务:
[root@it.net.cn ~]# firewall-cmd --add-service=mysql --permanent
修改配置文件/etc//my.cnf文件,添加以下编解内容:
character-set-server=utf8
数据库文件存储位置:
datadir=/var/lib/mysql
二、管理数据库
查看数据库:
MariaDB [(none)]> show databases;
查看当前数据库:
MariaDB [(none)]> select database();
查看当前用户:
MariaDB [(none)]> select user();
查看数据库当前的所有属性信息:
MariaDB [(none)]> status
进入数据库:
MariaDB [(none)]> use school;
显示表:
1 MariaDB [school]> show tables;
创建表:
MariaDB [school]> create table teacher(id int,name varchar(10),gender varchar(5) )
查看表结构:
MariaDB [school]> desc teacher;
查询和插入表数据:
1 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设置密码的方法:
1、
[root@it.net.cn ~]# mysqladmin -uroot -p password 'RedHat'
2、
MariaDB [(none)]> set password=password('redhat');
3、
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@it.net.cn ~]# systemctl stop mariadb.service
[root@it.net.cn ~]# 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@it.net.cn ~]# mysqldump -u root -p school teacher student> /mysql_backup/teacher.sql
备份整个数据库表,后面则不需要指定任何表:
[root@it.net.cn ~]# mysqldump -u root -p school> /mysql_backup/all_tables.sql
备份整个数据库:
[root@it.net.cn ~]# mysqldump -u root -p -B school> /mysql_backup/all.sql
进入库恢复表或者恢复表:
MariaDB [school]> source /mysql_backup/teacher.sql;
[root@it.net.cn ~]# mysql -u root -p'redhat' < /mysql_backup/all.sql
将表数据保存到文件,修改备份目录的属主属组信息:
[root@it.net.cn ~]# 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不能做增量备份:
(责任编辑:IT)
RHCE7认证学习笔记36——MariaDB数据库配置与管理 MariaDB数据库的操作使用与MySQL基本相同。 一、配置 安装MariaDB数据库 [root@it.net.cn ~]# yum install mariadb\* -y 启动mariadb服务: [root@it.net.cn ~]# systemctl start mariadb 防火墙添加mysql服务: [root@it.net.cn ~]# firewall-cmd --add-service=mysql --permanent 修改配置文件/etc//my.cnf文件,添加以下编解内容: character-set-server=utf8 数据库文件存储位置: datadir=/var/lib/mysql 二、管理数据库 查看数据库: MariaDB [(none)]> show databases; 查看当前数据库: MariaDB [(none)]> select database(); 查看当前用户: MariaDB [(none)]> select user(); 查看数据库当前的所有属性信息: MariaDB [(none)]> status 进入数据库: MariaDB [(none)]> use school; 显示表: 1 MariaDB [school]> show tables; 创建表: MariaDB [school]> create table teacher(id int,name varchar(10),gender varchar(5) ) 查看表结构: MariaDB [school]> desc teacher; 查询和插入表数据: 1 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设置密码的方法: 1、 [root@it.net.cn ~]# mysqladmin -uroot -p password 'RedHat' 2、 MariaDB [(none)]> set password=password('redhat'); 3、 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@it.net.cn ~]# systemctl stop mariadb.service [root@it.net.cn ~]# 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@it.net.cn ~]# mysqldump -u root -p school teacher student> /mysql_backup/teacher.sql 备份整个数据库表,后面则不需要指定任何表: [root@it.net.cn ~]# mysqldump -u root -p school> /mysql_backup/all_tables.sql 备份整个数据库: [root@it.net.cn ~]# mysqldump -u root -p -B school> /mysql_backup/all.sql 进入库恢复表或者恢复表: MariaDB [school]> source /mysql_backup/teacher.sql; [root@it.net.cn ~]# mysql -u root -p'redhat' < /mysql_backup/all.sql 将表数据保存到文件,修改备份目录的属主属组信息: [root@it.net.cn ~]# 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不能做增量备份: (责任编辑:IT) |