Linux学习篇第三章之~mariadb

Mariadb数据库

 安装Mariadb数据库

 配置和管理数据库
 配置数据库用户和访问权限

 备份和恢复数据库

1.Mariadb安装

> yum install mariadb-server -y      ####下载数据库软件

> systemctl start mariadb          #####开启服务

> mysql                      ####进入到数据库中

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.35-MariaDB MariaDB Server
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> quit
Bye
> netstat -antlpe | grep mysql     ####查看mysql端口情况
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN     27         76495      3536/mysqld         
> vim /etc/my.cnf    #####编写配置文件，屏蔽网络接口

[root@mariadb ~]# systemctl restart mariadb   
[root@mariadb ~]# netstat -antlpe | grep mysql   #####查看mysql服务
[root@mariadb ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.35-MariaDB MariaDB Server
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> quit
Bye

> mysql_secure_installation     ###安全初始化
Set root password? [Y/n] Y     #####设置root用户密码
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!
Remove anonymous users? [Y/n] Y      #####删除匿名用户
 ... Success!
Disallow root login remotely? [Y/n] Y  #####不允许远程用户登录
 ... Success!
Remove test database and access to it? [Y/n] Y  ######删除测试数据库

[root@mariadb ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@mariadb ~]# mysql -uroot -p    ####root用户登录
MariaDB [(none)]> show databases;    #####显示数据库

MariaDB [(none)]> use mysql     #####进入mysql库
Database changed
MariaDB [mysql]> show tables;     ####显示当前库中表的名称

MariaDB [mysql]> desc user;    ######查询user表的结构（显示所有字段的名称）

MariaDB [mysql]> select * from user;   #####查询user表中所有内容（*可以用此表中的任何字段来代替）

2.数据库的建立

MariaDB [(none)]> create  database westos;   ####创建westos库
MariaDB [(none)]> show databases;         #####显示数据库

MariaDB [(none)]> use westos     ####进入westos库
MariaDB [westos]> create table linux(   #####创建linux表，表含有两个字段，username，password
    -> username varchar(15) not null,   

    -> password varchar(15) not null);
MariaDB [westos]> insert into linux values ('user1','123');  ####向linux表中插入数据,username的字段的                                             数据为user1，password字段的数据为123
MariaDB [westos]> insert into linux values ('user2',password('123'));

MariaDB [westos]> select * from linux;   #####查询linux表中内容


MariaDB [westos]> update linux set password=password('123') where username='user1';  #####更新                                                             user1的密码
MariaDB [westos]> select * from linux;   #####查询linux表中内容
MariaDB [westos]> delete from linux where username='user1';  #####删除user1信息
MariaDB [westos]> select * from linux;


MariaDB [westos]> alter table linux add class varchar(20) not null after password; ####添加class字段到                                           linux表中且不为空，添加到password字段


3.用户授权
[root@mariadb mnt]# mysql  -uroot -pwestos  #####root用户登录
MariaDB [(none)]> select User from mysql.user;  #####查看User

MariaDB [(none)]> create user zhao@localhost identified by 'zhao';  #####建立本地用户zhao                                       

                    
MariaDB [(none)]> create user zhao@'%' identified by 'zhao';   

[root@mariadb mnt]# mysql  -uzhao -pzhao -h 172.25.254.142  
ERROR 2003 (HY000): Can't connect to MySQL server on '172.25.254.142' (111)
[root@mariadb mnt]# vim /etc/my.cnf
skip-networking=0
[root@mariadb mnt]# systemctl restart mariadb
[root@mariadb mnt]# mysql  -uzhao -pzhao -h 172.25.254.143   ##测试
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.35-MariaDB MariaDB Server
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> quit
[root@mariadb mnt]# mysql  -uroot -pwestos
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.35-MariaDB MariaDB Server
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> grant insert,update,delete,select on westos.* to lee@localhost;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> grant select on westos.* to zhao@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show grants for zhao@'%';
+----------------------------------------------------------------------------------------------------+
| Grants for zhao@%                                                                                   |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhao'@'%' IDENTIFIED BY PASSWORD '*9BB439A3A652A9DAD3718215F77A7AA06108A267' |
| GRANT SELECT ON `westos`.* TO 'zhao'@'%'                                                            |
+----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)


MariaDB [(none)]> show grants for zhao@localhost;
+------------------------------------------------------------------------------------------------------------+
| Grants for zhao@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zhao'@'localhost' IDENTIFIED BY PASSWORD '*9BB439A3A652A9DAD3718215F77A7AA06108A267' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `westos`.* TO 'zhao'@'localhost'                                    |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

4.数据库的网页管理工具
> yum install httpd php php-mysql -y   ##安装服务
> systemctl start httpd
> systemctl enable httpd
>tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2 -C /var/www/html/  ##解压文件到指定目录
> cd /var/www/html/
> ls
phpMyAdmin-3.4.0-all-languages
> mv phpMyAdmin-3.4.0-all-languages/ mysqladmin   ##重命名
> cp -p config.sample.inc.php config.inc.php    ##复制模板
> vim config.inc.php
>systemctl restart httpd


5.密码修改
当超级用户忘记密码
[root@mariadb mnt]# systemctl stop mariadb     ####关闭mysql
[root@mariadb mnt]# mysqld_safe --skip-grant-tables & ####开启mysql登录接口并忽略授权表
[1] 2189
[root@mariadb mnt]# 170513 01:49:03 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
170513 01:49:03 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

[root@mariadb mnt]# mysql       ####直接不用密码可以登录
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.35-MariaDB MariaDB Server
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> update mysql.user set Password=password('123') where User='root';      ######更新                                                          超级用户密码信息

[root@mariadb mnt]# fg
mysqld_safe --skip-grant-tables
^Z
[1]+  Stopped                 mysqld_safe --skip-grant-tables
[root@mariadb mnt]# killall -9 mysqld_safe
[1]+  Killed                  mysqld_safe --skip-grant-tables
[root@mariadb mnt]# ps aux | grep mysql   #####过滤mysql的所有进程并结束这些进程
mysql     2351  0.0  9.0 859068 89732 pts/1    Sl   01:49   0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --skip-grant-tables --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
root      2413  0.0  0.0 112640   936 pts/1    R+   01:53   0:00 grep --color=auto mysql
[root@mariadb mnt]# kill -9 2351
[root@mariadb mnt]# ps aux | grep mysql
root      2433  0.0  0.0 112640   932 pts/1    R+   01:54   0:00 grep --color=auto mysql
[root@mariadb mnt]# systemctl start mariadb ######重新开启mysql
[root@mariadb mnt]# mysql -uroot -p123      #####登录测试

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.35-MariaDB MariaDB Server
Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement
MariaDB [(none)]> quit
Bye