linux——mariadb

最新推荐文章于 2020-03-13 13:15:09 发布

chine666

最新推荐文章于 2020-03-13 13:15:09 发布

阅读量220

点赞数

分类专栏：服务

本文链接：https://blog.csdn.net/chine666/article/details/78604582

版权

服务专栏收录该内容

10 篇文章 0 订阅

订阅专栏

1.安装
yum install mariadb-server -y

systemctl start mariadb

2.安全初始化
默认情况下，数据库的网络接口是打开的，为了安全需要关闭此端口：
vim /etc/my.cnf
skip-networking=1

systemctl restart mariadb

netstat -antlpe | grep mysql ##查看端口

数据库起始状态设定信息时不安全的，需要做以下设定（设定密码等等）：

mysql_secure_installation

内容选择如下：

/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] Y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
... Success!

Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
... Success!

Cleaning up...

All done! If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

登陆：

mysql -uroot -p

3.数据库的管理
修改密码：
mysqladmin -uroot -p123 password westos ##更改密码123为westos

当超级用户密码忘记时：
systemctl stop mariadb
mysqld_safe --skip-grant-tables & ##跳过认证
mysql

MariaDB [(none)]> update mysql.user set Password=password('123') where user='root'; ##修改密码（password('123')意为不被看见）
MariaDB [(none)]> select Password from mysql.user; ##查看密码表格

ps aux | grep msysql ##查看进程，然后删除mysql进程
systemctl start mariadb

建立：
[root@localhost ~]# mysql -uroot -p
MariaDB [(none)]> SHOW DATABASES;   ##列出库
CREATE DATABASE westos;   ##建立westos库
USE westos;    ##进入库
MariaDB [westos]> CREATE TABLE linux(
    -> username varchar(50) not null,
    -> password varchar(50) not null
    -> );                ##建立表linux

DESC linux;      ##查看表结构
INSERT INTO linux VALUES ('lee','123');   ##插入数据到linux中
SELECT *FROM linux；         ##查询所有字段在linux表中
SELECT username,password FROM linux;    ##查询指定字段在linux表中

更改：
MariaDB [westos]> UPDATE linux SET password=password('lee') where username='lee';修改lee的密码，看不见
ALTER TABLE linux ADD class varchar(20);    ##添加字段class
ALTER TABLE linux DROP class;     ##删除字段class
ALTER TABLE linux ADD class varchar(20) AFTER username;     ##在username后添加字段class
ALTER TABLE linux RENAME redhat;     ##修改表名称为redhat

删除：
MariaDB [westos]> DELETE FROM redhat where username='lee'; ##删除数据
MariaDB [westos]> DROP TABLE redhat;    ##删除表
SHOW TABASES;       ##列出表
DROP DATABASE westos;     ##删除库
SHOW DATABASES;   ##列出库
flush privileges;    ##刷新

用户授权：
CREATE USER lee@'localhost' identified by 'westos';   ##创建用户
MariaDB [(none)]> select user from mysql.user;    ##查看用户表格
GRANT SELECT,INSERT on westos.* TO lee@localhost;    ##给lee用户select，insert权限
SHOW GRANTS FOR lee@localhost;    ##显示用户权限
REVOKE INSERT ON westos.* FROM lee@localhost;   ##删除insert权限

4.数据库的备份
备份：
mysqldump -uroot -p123 westos >/mnt/westos.sql    ##导入/mnt，名叫westos.sql
mysqldump -uroot -p123 westos --no-data     ##westos不带数据
mysqldump -uroot -p123 --all-database     ##所有库都备份
mysqldump -uroot -p123 --all-database --no-data    ##所有库不带数据备份

、

drop database westos; ##删除库

恢复方式1：
mysql -uroot -p123 -e "CREATE DATABASE westos;"
mysql -uroot -p123 westos < /mnt/westos.sql

恢复方式2：
vim /mnt/westos.sql
CREATE DATABASE westos;
USE westos;
mysql -uroot -p123 < /mnt/westos.sql

6.安装phpmyaadmin数据库图形管理
yum install php-mysql -y

下载phpMyAdmin-3.4.0-all-languages.tar.bz2 到/var/www/html中 ##在网上找安装包
[root@localhost html]#tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2
[root@localhost html]#mv phpMyAdmin-3.4.0-all-languages mysqladmin
[root@localhost html]#cd mysqladmin
[root@localhost mysqladmin]#cp config.sample.inc.php config.inc.php
[root@localhost mysqladmin]#ls

[root@localhost mysqladmin]#vim Documentation.txt ##查询第四个步骤即找ba17c1ec07d65003
[root@localhost mysqladmin]#vim config.inc.php
$cfg['blowfish_secret'] = 'ba17c1ec07d65003';

systemctl restart httpd
systemctl restart mariadb