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