mariadb数据库，

mariadb数据库，

#########################
######Mariadb数据库#####
#########################
 

Mariadb数据库管理系统是Mysql的一个分支，完全兼容于Mysql数据库

。

一．Mariadb的安装和使用

 

yum install mariadb-server -y ##安装数据库服务

systemctl start mariadb      ##启动服务

mysql                        ##进入数据库

netstat -antlpe | grep mysql  ##查看与mysql有关的端口信息，从而校验mariadb的监听端口

vim /etc/my.cnf

10 skip-network=1           ##跳过网络功能开启，阻断所有来自网络的tcp/ip连接

systemctl restart mariadb

netstat -antlpe | grep mysql

[root@mariadb ~]#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): ##输入root当前密码，如果没有，直接enter

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 ##设定root密码

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 ##不允许root用户远程登录

 ... 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!

 

[root@mariadb ~]# mysql            ##设置密码后不能直接进入数据库

 

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

 

 

[root@mariadb ~]# mysql -uroot -pwestos  ####登陆数据库，-u指定用户，-p指定密码

Welcome to the MariaDB monitor.  Commands end with ; or \g.

 

二．数据库的基本sql语句操作

 

1.登陆

mysql -u root -pwestos   

 2.查询

show databases;               ##显示数据库

use mysql;                     ##进入mysql库

show tables;                    ##显示当前库中表的名称

select * from user;             ##查询user表中的所有内容（*可以用表中任何字段代替）

desc user;                      ##查询user表中的结构（显示所有字段的名称）

3.数据库及表的建立

creat database westos;         ##创建westos库

[root@mariadb ~]# cd /var/lib/mysql;

[root@mariadb mysql]# rm -fr westos/  ##删除westos库

MariaDB [(none)]> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| westos             |

+--------------------+

4 rows in set (0.00 sec)

MariaDB [(none)]> use westos;

Database changed

MariaDB [westos]> show tables;

Empty set (0.00 sec)

MariaDB [westos]> create table linux(   ##创建linux表，并且linux表含有两个字段username，password

    -> username varchar(15) not null,   ##username字段，字符长度为15，且不能为空

    -> password varchar(15) not null );

Query OK, 0 rows affected (0.05 sec)

 

MariaDB [westos]> desc linux;

+----------+-------------+------+-----+---------+-------+

| Field    | Type        | Null | Key | Default | Extra |

+----------+-------------+------+-----+---------+-------+

| username | varchar(15) | NO   |     | NULL    |       |

| password | varchar(15) | NO   |     | NULL    |       |

+----------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

 

MariaDB [westos]> insert into linux values ('user1','123');  ##向linux表中插入数据

Query OK, 1 row affected (0.03 sec)

 

MariaDB [westos]> select * from linux;

+----------+----------+

| username | password |

+----------+----------+

| user1    | 123      |

| user2    | 123      |

+----------+----------+

2 rows in set (0.00 sec)

MariaDB [westos]> insert into westos.linux values ('user3',password('123') ); 

Query OK, 1 row affected, 1 warning (0.03 sec)

 

MariaDB [westos]> select * from westos.linux;

+----------+-----------------+

| username | password        |

+----------+-----------------+

| user1    | 123             |

| user2    | 123             |

| user3    | *23AE809DDACAF9 |

+----------+-----------------+

3 rows in set (0.00 sec)

 

4.更新数据库信息

 MariaDB [westos]> update linux set password=password('123') where password='123';          ##更新密码是123的用户的密码

Query OK, 2 rows affected, 2 warnings (0.26 sec)

Rows matched: 2  Changed: 2  Warnings: 2

 

MariaDB [westos]> select * from linux;+----------+-----------------+

| username | password        |

+----------+-----------------+

| user1    | *23AE809DDACAF9 |

| user2    | *23AE809DDACAF9 |

| user3    | *23AE809DDACAF9 |

+----------+-----------------+

3 rows in set (0.00 sec)

delete from linux where username=user1;

MariaDB [westos]> alter table linux add class varchar(20) not null; ##添加class字段到linux表

Query OK, 3 rows affected (0.12 sec)               

Records: 3  Duplicates: 0  Warnings: 0

 

MariaDB [westos]> select * from linux;

+----------+-----------------+-------+

| username | password        | class |

+----------+-----------------+-------+

| user1    | *23AE809DDACAF9 |       |

| user2    | *23AE809DDACAF9 |       |

| user3    | *23AE809DDACAF9 |       |

+----------+-----------------+-------+

3 rows in set (0.00 sec)

 

MariaDB [westos]> alter table linux add date varchar(20) not null after password; ##添加date字段在password之后

Query OK, 3 rows affected (0.10 sec)               

Records: 3  Duplicates: 0  Warnings: 0

 

MariaDB [westos]> select * from linux;+----------+-----------------+------+-------+

| username | password        | date | class |

+----------+-----------------+------+-------+

| user1    | *23AE809DDACAF9 |      |       |

| user2    | *23AE809DDACAF9 |      |       |

| user3    | *23AE809DDACAF9 |      |       |

+----------+-----------------+------+-------+

3 rows in set (0.00 sec)

alter table linux drop date     ##删除date字段

 

5.删除数据库

delete from linux where username=’user1’;    ##删除user1的数据从linux表中

drop table linux;                                ##删除linux表

drop database westos;                         ##删除westos库

6.数据库的备份

mysqldump -u root -pwestos --all-database     ##备份所有表中的所有数据

mysqldump -u root -pwestos --all-database --no-data ##仅备份所有表不备份数据

mysqldump -u root -pwestos westos            ##备份westos库

mysqldump -u root -pwestos  > /mnt/westos/sql##备份westos库并把 数据保存到/mnt/westos.sql

mysqldump -uroot -pwestos -e “create database westos;” ##建立westos库

mysqldump -uroot -pwestos westos < /mnt/westos.sql ##把数据倒入westos库

mysqldump -uroot -pwestos -e “select * from westos.linux;”

 

7.用户授权

MariaDB [westos]> create user lee@localhost identified by 'lee';##只能通过本机登陆

MariaDB [westos]> create user lee@'%' identified by 'lee';##可以通过网络登录

Query OK, 0 rows affected (0.00 sec)

 

MariaDB [westos]> select User,Host from mysql.user;

+------+-----------+

| User | Host      |

+------+-----------+

| lee  | %         |

| root | 127.0.0.1 |

| root | ::1       |

| lee  | localhost |

| root | localhost |

+------+-----------+

[root@mariadb mysql]# mysql -ulee -plee ##登陆测试

[root@mariadb mysql]# mysql -ulee -plee -h localhost

[root@mariadb mysql]# mysql -ulee -p -h 172.25.254.100

Ip登陆需进入vim /etc/my.cnf 删除10 skip-network=1

grant insert,update,delete,select on westos.test to lee@localhost; ##用户授权（root

grant select on westos.* to lee@’%’;   

show grant for lee@’%;

show grant for lee@localhost;        ##查看用户授权

revoke delete on westos.test from lee@localhost; ##去除用户授权权利

drop user lee@’%’;                   ##删除用户

8.密码修改

mysqladmin -u root -pwestos passwd lee  ##修改超级用户密码

##当超级用户忘记秘密

mysql_safe --skip-grant-tables &           ##开启mysql登陆接口并忽略授权表

update mysql.user set Passwdword=password（‘123’) where User=’root’;##更新超级用户密码信息

ps aux | grep mysql    ##过滤mysql的所有进程并结束

kill -9 mysqlpid

sysemctl start mariadb ##重新开启mysql

mysql -uroot -p123     ##登陆测试

 

三．搭建图形化界面管理数据库

1.安装

yum install httpd php php-mysql -y

systemctl start httpd

systemctl enable htpd

systemctl stop firewalld

systemctl disable firewalld

下载phpMyAdmin-3.4.0-all-languages.tar.bz2

[root@mariadb html]# ls

phpMyAdmin-3.4.0-all-languages.tar.bz2

[root@mariadb html]# tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2 -C  /var/www/html  ##解压到/var/www/html

[root@mariadb html]# ls

phpMyAdmin-3.4.0-all-languages  phpMyAdmin-3.4.0-all-languages.tar.bz2

[root@mariadb html]# rm -fr phpMyAdmin-3.4.0-all-languages.tar.bz2

[root@mariadb html]# ls

phpMyAdmin-3.4.0-all-languages

[root@mariadb html]# mv phpMyAdmin-3.4.0-all-languages myadmin

[root@mariadb html]# cd myadmin/

[root@mariadb myadmin]# cp -p config.sample.inc.php config.inc.php

[root@mariadb myadmin]# vim config.inc.php  ##修改配置文件

17 $cfg['blowfish_secret'] = 'mysql'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH!     */

[root@mariadb myadmin]# systemctl restart httpd

2.测试

浏览器访问：http://172.25.254.100/myadmin