服务端
安装并启动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 -uroot -predhat
drop database inventory;
create database inventory;
exit
mysql -uroot -predhat inventory < /tmp/inventory.sql
mysql -uroot -predhat
show databases;
use inventory;
show tables;
select * from product;
select * from category;
select * from manufacturer;