mysql、MariaDB(yum)

Mysql安装yum

1.安装及修改初始密码

# cd /etc/yum.repos.d/
# yum install -y epel-release
​
# yum -y install wget
# wget https://repo.mysql.com/mysql57-community-release-el7-11.noarch.rpm
# ls /etc/yum.repos.d/
# yum -y install mysql57-community-release-el7-11.noarch.rpm    #安装mysql源
# yum -y install mysql-community-server     mysql
​
# systemctl restart mysqld
# grep 'passwd' /var/log/mysqld.log     //查看初始密码
xxxxxxxxxxx
# mysql -uroot -p'xxxxxxx'
> alter user 'root'@'localhost' identified by   'ABC123.com';   #修改密码
> \q

  

1.2mysql创建库并授权

//创建库并授权
# systemctl restart mysqld
# mysql -uroot -p'ABC123.com'
> create database zhangsandb character set utf8 collate utf8_bin;   #建库及字符集
> show databases;
zhangsandb
...
> grant select,insert,update,delete,create,drop,alter,index on zhangsan.* to 'zhangsan'@'localhost' identified by 'Qwe123.com';     #授权
> flush privileges;
> show grants for 'zhangsan'@'localhost';       查看用户权限
# systemctl enable mysqld

  

2.mariadb的使用yum

# cd /etc/yum.repos.d/
# vim mariadb.repo
[mariadb]
name = MariaDB
baseurl = https://mirrors.ustc.edu.cn/mariadb/yum/10.4/centos7-amd64/
gpgkey=https://mirrors.ustc.edu.cn/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1
# yum clean all
# yum makecache
# yum -y install mariadb-server mariadb-client

  

MariaDB数据库初始化

  • 设置root管理员在数据库中的密码(默认为空)

  • 设置root管理员专有密码

  • 删除匿名,并使用root远程登录

  • 删除默认测试数据库

  • 刷新权限列表,让初始化的设定立即生效

# systemctl restart mariadb
# mysql_secure_installation
:
[Y/n]y
password:xxxxxx
password:xxxxxx
[Y/n]y
[Y/n]y
[Y/n]n
[Y/n]y
# mysql -uroot -p"xxxxxx"
> show databases;
> 
​
//字符编码或数据库内设置
# vim /etc/my.cnf
[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-sert-client-handshake
​
# mysql -uroot -p"xxxxxx"
> set password = PASSWORD('abc123');
> create database test;
> use test;
> create table mytest(id int,name char(32));
> show tables;
> desc mytest;

  

2.1MariaDB增删改查

> insert into mytest(id,name) values(1,"zero"),(2,"one");   添加两条数据
> select id,name from mytest;
> show database;
test
> use test;
> show tables;
mytest
> desc mytest;
> select * from mytest;
​
> delete from mytest where id=2;        #删除一行数据
> select * from mytest;
​
> update mytest set name="ten" where id=1;      # 改
> select * from mytest;
​
> create user zero@'%' identified by 'zero';    # 创建用户和密码
​
//授予用户最大的权限,所有权限
> grangt all privileges on *.* to username@'%'  identified by 'password';
​
//授予zero用户,只有创建test数据库的权限
> grant create on test.* to zero@'%' identified by 'zero';
# mysql -uzero -pzero   //查询zero用户的数据库只有
> show database;
​
//授予one创建的权限,对于所有的库表生效
> grant create on *.* to one@'%' identified by 'one';
# mysql -uone -pone
> show database;
​
//删除one用户
> drop user one;
> flush privileges;         #刷新权限

  

2.2数据库备份与恢复

# cd /tmp/
# mysqldump -u root -p --all-datebases > /tmp/db.dump
# mysqldump -u root -p test >/tmp/test.sql
​
# mysql -u root -p
> show database;
> drop database test;
> drop database zhao;
> \q
​
# mysql -uroot -p < /tmp/db.dump

  

 

posted @ 2021-03-09 00:09  破碎的屋檐  阅读(55)  评论(0编辑  收藏  举报