1.下载数据库服务软件
yum install mariadb-server -y ##下载服务软件
**MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可 MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。
systemctl start mariadb ##开启服务
mysql ##登陆测试（以匿名用户身份）
netstat -antlpe |grep mysql ##查询监听端口
vim /etc/my.cnf ##修改配置文件，加上一行
10 skip-networking=1
mysql_secure_installation ##打开mysql安全配置向导
Set root password? [Y/n] y ##确认设置密码
Remove anonymous users? [Y/n] y ##移除匿名用户
Disallow root login remotely? [Y/n] y ##不允许超级用户远程登陆
Remove test database and access to it? [Y/n] y##删除测试数据库
Reload privilege tables now? [Y/n] y ##立即刷新安全配置向导
systemctl restart mariadb ##重启服务
2. 数据库的登陆
[root@localhost ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) ##匿名用户登陆被拒绝 [root@localhost ~]# mysql -uroot -p
Enter password: ##输入密码即以root用户身份登陆（安全起见，密码无回显）
**密码也可直接加在-p之后，例如：mysql -uroot -ppasswd，但这种方法由于密码以明文方式输入不安全，所以一般不用这种方式登陆3.数据库管理基本命令
show databases; ##显示数据库
use mysql ##进入mysql数据库
show tables; ##显示当前库中表的名称
select * from user; ##查询user表中的所有内容（* 可以用此表中的任何字段来代替）
desc user; ##查询user表的结构（显示所有字段名称）
4.数据库及表的建立
MariaDB [mysql]> create database westos; ##创建westos库
Query OK, 1 row affected (0.00 sec) MariaDB [mysql]> use westos; ##进入westos库
Database changed
MariaDB [westos]> create table linux( username varchar(15) not null, password varchar(50) not null );
Query OK, 0 rows affected (0.01 sec) ##在westos库中创建linux表，表中有两个字段
**当一条命令比较长时，可分段编写（在命令没写完时按Enter键即跳到下一行），这样，在命令出错时系统会提示哪一行出错了，这样比较容易排错
MariaDB [westos]> desc linux;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(15) | NO | | NULL | |
| password | varchar(50) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
MariaDB [westos]> insert into linux values('user1','123'); ##在linux表中插入一条信息
Query OK, 1 row affected (0.00 sec)
MariaDB [westos]> insert into linux values('user2',password('123') ); ##插入第二条信息，user2的password字段使用password加密字符
Query OK, 1 row affected (0.01 sec)
MariaDB [westos]> select * from linux;
+----------+-------------------------------------------+
| username | password |
+----------+-------------------------------------------+
| user1 | 123 |
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+----------+-------------------------------------------+
2 rows in set (0.00 sec)5.更新数据库信息
MariaDB [westos]> alter table linux add age varchar(5); ##更新linux表的结构，添加age字段（默认添加到最后一列）
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [westos]> select * from linux;
+----------+-------------------------------------------+------+
| username | password | age |
+----------+-------------------------------------------+------+
| user1 | 123 | NULL |
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | NULL |
+----------+-------------------------------------------+------+
2 rows in set (0.00 sec)
MariaDB [westos]> alter table linux drop age ; ##更新linux表的结构，删除age字段
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [westos]> select * from linux;
+----------+-------------------------------------------+
| username | password |
+----------+-------------------------------------------+
| user1 | 123 |
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+----------+-------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [westos]> alter table linux add age varchar(5) after username;##添加age字段到username后面
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [westos]> select * from linux;
+----------+------+-------------------------------------------+
| username | age | password |
+----------+------+-------------------------------------------+
| user1 | NULL | 123 |
| user2 | NULL | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+----------+------+-------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [westos]> update linux set password=password('123') where username='user1' ;##更新数据库，将user1的密码改为加密字符
***update linux set password=password('123') where （username='user1' or username='user2' ) ;##更新数据库，将user1和user2的密码用password加密
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [westos]> select * from linux;
+----------+-------------------------------------------+
| username | password |
+----------+-------------------------------------------+
| user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+----------+-------------------------------------------+
2 rows in set (0.00 sec)6.数据库的备份和恢复
[root@localhost ~]# mysqldump -u root -pwestos westos > /mnt/westos.sql ##备份westos库
[root@localhost mnt]# mysql -uroot -p
Enter password:
MariaDB [(none)]> use westos
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [westos]> delete from linux where username='user1' ; ##删除linux表中的user1项中的数据
Query OK, 1 row affected (0.01 sec)
MariaDB [westos]> select * from linux;
+----------+-------------------------------------------+
| username | password |
+----------+-------------------------------------------+
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+----------+-------------------------------------------+
1 row in set (0.00 sec)
MariaDB [westos]> drop database westos; ##删除westos库
Query OK, 1 row affected (0.01 sec)
MariaDB [(none)]> use westos
ERROR 1049 (42000): Unknown database 'westos'
MariaDB [(none)]> create database westos; ##新建westos库（无数据，准备恢复）
Query OK, 1 row affected (0.00 sec)
[root@localhost mnt]# mysql -uroot -pwestos westos < /mnt/westos.sql ##用备份文件恢复westos库
[root@localhost mnt]# mysql -uroot -p
Enter password:
MariaDB [(none)]> show tables from westos;
+------------------+
| Tables_in_westos |
+------------------+
| linux |
+------------------+
1 row in set (0.00 sec)
[root@localhost mnt]# mysqldump -u root -pwestos westos linux> /mnt/linux.sql ##备份linux表内容
[root@localhost mnt]# mysql -uroot -p
Enter password:
MariaDB [(none)]> use westos
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [westos]> drop table linux;
Query OK, 0 rows affected (0.01 sec)
MariaDB [westos]> select * from linux;
ERROR 1146 (42S02): Table 'westos.linux' doesn't exist
[root@localhost mnt]# mysql -uroot -pwestos westos < /mnt/linux.sql
[root@localhost mnt]# mysql -uroot -p
Enter password:
MariaDB [(none)]> show tables from westos;
+------------------+
| Tables_in_westos |
+------------------+
| linux |
+------------------+
1 row in set (0.00 sec)
7.用户授权
MariaDB [(none)]> create user yan@localhost identified by '123'; ##创建用户yan，只允许他在本地登录，密码为123
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> create user yan@'%' identified by '123'; ##创建用户yan，允许他通过网络登录，密码为123
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> grant insert,update,delete,select on westos.linux to yan@localhost;
Query OK, 0 rows affected (0.01 sec) ##给yan@localhost用户更删增找的权限
MariaDB [(none)]> grant select on westos.* to yan@'%'; ##给yan@'%'用户找的权限
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show grants for yan@'%' ##显示用户权限
-> ;
+----------------------------------------------------------------------------------------------------+
| Grants for yan@% |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'yan'@'%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT SELECT ON `westos`.* TO 'yan'@'%' |
+----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]> show grants for yan@localhost;
+------------------------------------------------------------------------------------------------------------+
| Grants for yan@localhost |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'yan'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `westos`.`linux` TO 'yan'@'localhost' |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [westos]> revoke delete on westos.linux from yan@localhost; ##除去yan@localhost用户删除的权限
Query OK, 0 rows affected (0.00 sec)
[root@localhost mnt]# mysql -uyan -p123 ##登录测试
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 28
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 tables from westos;
+------------------+
| Tables_in_westos |
+------------------+
| linux |
| test |
+------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]> use westos
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [westos]> insert into linux values('yan','123'); ##插入信息正常
Query OK, 1 row affected (0.01 sec)
MariaDB [westos]> select * from linux; ##查看信息正常
+----------+-------------------------------------------+
| username | password |
+----------+-------------------------------------------+
| user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| yan | 123 |
+----------+-------------------------------------------+
4 rows in set (0.00 sec)
MariaDB [westos]> delete from linux where username='yan';
ERROR 1142 (42000): DELETE command denied to user 'yan'@'localhost' for table 'linux' ##删除信息时被拒绝
MariaDB [(none)]> drop user yan@'%'; ##删除yan@'%'用户
Query OK, 0 rows affected (0.00 sec)
8.修改密码
**旧密码记得**
[root@localhost mnt]# mysqladmin -uroot -pwestos password yan ##直接用命令修改密码
[root@localhost mnt]# mysql -uroot -pyan ##登陆测试（新密码登陆成功）
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 7
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.
**旧密码忘记**
[root@localhost mnt]# systemctl stop mariadb
[root@localhost mnt]# mysqld_safe --skip-grant-tables & ##开启mysql登录接口并忽略授权表
[1] 2397
[root@localhost mnt]# 170513 01:50:03 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
170513 01:50:03 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
[root@localhost 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';##更新超户密码信息
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
[root@localhost mnt]# ps aux |grep mysql ##查找mysql的进程并结束他们
root 2397 0.0 0.0 113248 1620 pts/1 S 01:50 0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
mysql 2552 0.1 4.8 859072 91324 pts/1 Sl 01:50 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 2587 0.0 0.0 112640 976 pts/1 S+ 01:51 0:00 grep --color=auto mysql
[root@localhost mnt]# kill -9 2397
[root@localhost mnt]# kill -9 2552
[1]+ Killed mysqld_safe --skip-grant-tables
[root@localhost mnt]# ps aux |grep mysql
root 2597 0.0 0.0 112640 980 pts/1 R+ 01:52 0:00 grep --color=auto mysql
[root@localhost mnt]# systemctl start mariadb ##开启mysql
[root@localhost 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)]>
9.数据库网页管理工具
[root@localhost ~]# yum install httpd php php-mysql -y ##下载相关语言服务
[root@localhost ~]# systemctl start httpd
[root@localhost ~]# systemctl enable httpd
ln -s '/usr/lib/systemd/system/httpd.service' '/etc/systemd/system/multi-user.target.wants/httpd.service'
[root@localhost ~]# systemctl stop firewalld.service
[root@localhost ~]# systemctl disable firewalld.service
rm '/etc/systemd/system/basic.target.wants/firewalld.service'
rm '/etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service'
[root@localhost ~]# yum install lftp -y
lftp 172.25.254.250:/pub/docs/software> get phpMyAdmin-3.4.0-all-languages.tar.bz2 ##下载语言翻译的压缩包
4548030 bytes transferred
[root@localhost ~]# tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2 -C /var/www/html ##将压缩宝解压到http默认发布目录
[root@localhost ~]# cd /var/www/html
[root@localhost html]# ls
phpMyAdmin-3.4.0-all-languages
[root@localhost html]# mv phpMyAdmin-3.4.0-all-languages mysqladmin ##重命名，换个短的名字[root@localhost html]# ls
mysqladmin
[root@localhost html]# cd mysqladmin
[root@localhost mysqladmin]# ls
[root@localhost mysqladmin]# cp -p config.sample.inc.php config.inc.php
[root@localhost mysqladmin]# vim config.inc.php
[root@localhost mysqladmin]# systemctl restart httpd测试：访问http://172.25.254.240/mysqladmin