mariadb数据库

服务端

安装并启动mariadb

yum groupinstall mariadb mariadb-client -y 
systemctl enable --now mariadb
ps axf
pgrep mysql
ps -ao pid,pcpu,nice,comm $(pgrep mysql)
renice -n -10 $(pgrep mysql)
#进程调优

测试mariadb是否正常工作

mysql
show databases;
exit;

配置mariadb 方法1 手动配置

mysql_secure_installation
#安装向导
#Enter current password for root (enter for none): #这里回车
#Set root password? [Y/n] y
#密码设置为 redhat
#Remove anonymous users? [Y/n] y
#Disallow root login remotely? [Y/n] y
#Remove test database and access to it? [Y/n] y
#Reload privilege tables now? [Y/n] y
#Thanks for using MariaDB!

连接测试

mysql -uroot -predhat
#mysql -uroot -p 
#这种方法密码不会被记录

创建数据库和表

创建数据库
show databases;
create database inventory;
#创建一个数据库
show databases;
创建三个表（product，category，manufacturer）
use inventory;
create table product
(
id int(11) not null auto_increment,
name varchar(100) not null,
price double not null,
stock int(11) not null,
id_category int(11) not null,
id_manufacturer int(11),
primary key(id)
)charset=utf8;

create table category
(
   id int(11) not null auto_increment,
   name varchar(100) not null,
   primary key(id)
)charset=utf8;

create table manufacturer
(
   id int(11) not null auto_increment,
   name varchar(100) not null,
   seller varchar(100) not null,
   phone_number varchar(100) not null,
   primary key(id)
)charset=utf8;
show tables;
#查看表

插入数据

分别在三个表中插入数据（product，category，manufacturer）

insert into product(name,price,stock,id_category,id_manufacturer) values("ThinkServer TS140",539.88,20,2,4);
insert into product(name,price,stock,id_category,id_manufacturer) values("ThinkServer TS440",1736.00,10,2,4);
insert into product(name,price,stock,id_category,id_manufacturer) values("RT-AC68U",219.99,10,1,3);
insert into product(name,price,stock,id_category,id_manufacturer) values("X110 64GB",73.84,100,3,1);

insert into category(name) values("Networking");
insert into category(name) values("Servers");
insert into category(name) values("Ssd");

insert into manufacturer(name,seller,phone_number) values("SanDisk","John Miller","13688084569");
insert into manufacturer(name,seller,phone_number) values("Kingston","Mike Taylor","13688084598");
insert into manufacturer(name,seller,phone_number) values("Asus","Wilson Jckson","13699084598");
insert into manufacturer(name,seller,phone_number) values("Lenovo","Allen Scott","13699062598");

查询数据

select * from product;
select * from category;
select * from manufacturer;

创建一个用户

use mysql;
select HOST,User,Password from user;
create user 'liang23'@'%' identified by 'redhat';
#创建一个远程登陆的用户
select HOST,User,Password from user;
#验证是否创建成功
grant all privileges on *.* to 'liang23'@'%' identified by 'redhat' with grant option; 
#如果带with grant option，那么lgb这个账户也可以给其它账户授权
show grants for liang23;
exit;

客户机测试

yum groupinstall mariadb-client -y
mysql -uliang23 -predhat -h 10.1.1.3
show databases;
use inventory;
show tables;
select * from product;
select * from category;
select * from manufacturer;

数据库的备份

show variables like "%data%"
show variables like "datadir";
cd /var/lib/mysql/
ls -al

mysqldump -uroot -predhat inventory > /tmp/inventory.sql
mysqldump -uroot -predhat --all-databases > /tmp/all-databases.sql
mysqladmin -uroot -predhat variables | grep datadir

cd /tmp
ls | grep sql
#查看备份的sql文件

数据库的恢复

mysql -proot -predhat
drop  database inventory;
create database inventory;
exit
mysql -uroot -predhat inventory < /tmp/inventory.sql 
mysql -proot -predhat
show databases;
use inventory;
show tables;
select * from product;
select * from category;
select * from manufacturer;