一,安装phpmyadmin
[root@localhost ~]# yum install httpd php -y <<<<需要在网页打开(http服务),网页是由php写(php软件)
[root@localhost ~]# systemctl start httpd <<<<开启阿帕奇服务
[root@localhost ~]# rpm -qa | grep mariadb <<<<查询数据库版本
mariadb-5.5.35-3.el7.x86_64
mariadb-libs-5.5.35-3.el7.x86_64
mariadb-server-5.5.35-3.el7.x86_64
[root@localhost ~]# rpm -qa | grep php <<<<查询php版本
php-cli-5.4.16-21.el7.x86_64
php-common-5.4.16-21.el7.x86_64
php-5.4.16-21.el7.x86_64
[root@localhost ~]# cd /var/www/html <<<<需要在网页上面打开,所以phpMyAdmin软件放在默认发布目录下
[root@localhost html]# scp root@172.25.254.156:'/home/kiosk/Downloads/phpMyAdmin-3.4.0-all-languages.tar.bz2' .
root@172.25.254.156's password:
phpMyAdmin-3.4.0-all-languages.tar.bz2 100% 4441KB 4.3MB/s 00:00
[root@localhost html]# ls
phpMyAdmin-3.4.0-all-languages.tar.bz2
[root@localhost html]# tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2 <<<<解压
[root@localhost html]# rm -rf *.bz2 <<<<删除解压包
[root@localhost html]# ls
phpMyAdmin-3.4.0-all-languages
[root@localhost html]# mv phpMyAdmin-3.4.0-all-languages/ mysqladmin <<<<改名
[root@localhost html]# cd mysqladmin/
[root@localhost mysqladmin]# cp config.sample.inc.php con
config.sample.inc.php contrib/
[root@localhost mysqladmin]# cp config.sample.inc.php config.inc.php <<<<进入目录复制快速安装模板
[root@localhost mysqladmin]# systemctl stop firewalld <<<<关闭防火墙
[root@localhost mysqladmin]# yum search php
Loaded plugins: langpacks
====================================== N/S matched: php =======================================
php.x86_64 : PHP scripting language for creating dynamic web sites
php-cli.x86_64 : Command-line interface for PHP
php-common.x86_64 : Common files for PHP
php-gd.x86_64 : A module for PHP applications for using the gd graphics library
php-ldap.x86_64 : A module for PHP applications that use LDAP
php-mysql.x86_64 : A module for PHP applications that use MySQL databases
php-odbc.x86_64 : A module for PHP applications that use ODBC databases
php-pdo.x86_64 : A database access abstraction module for PHP applications
php-pear.noarch : PHP Extension and Application Repository framework
php-pgsql.x86_64 : A PostgreSQL database module for PHP
php-process.x86_64 : Modules for PHP script using system process interfaces
php-recode.x86_64 : A module for PHP applications for using the recode library
php-soap.x86_64 : A module for PHP applications that use the SOAP protocol
php-xml.x86_64 : A module for PHP applications which use XML
php-xmlrpc.x86_64 : A module for PHP applications which use the XML-RPC protocol
php-pecl-memcache.x86_64 : Extension to work with the Memcached caching daemon
Name and summary matches only, use "search all" for everything.
[root@localhost mysqladmin]# yum install php-mysql.x86_64 -y <<<<<<安装php支持的mysql组件
这里可以用php -m 查看php支持的组件
[root@localhost mysqladmin]# systemctl restart httpd.service <<<<<<重启网络服务
2,浏览器打开
http://172.25.254.100/mysqladmin/
注意:利用网页管理数据库十分方便,一般不用命令方式管理数据库。
二,数据库中创建用户并授权,删除用户
(本地用户username@localhost 远程登陆用户username@'%')
远程登陆数据库 myql -h 远程主机ip -uusername -ppassword (注意安装启动数据库)
创建用户
MariaDB [(none)]> CREATE USER zm@localhost identified by 'westos'; <<<<<创建zm用户,密码是westos
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select user from mysql.user; <<<<<查看用户表,注意mysql.user的写法(mysql库的user数据表)
+------+
| user |
+------+
| root |
| root |
| root |
| zm |
+------+
4 rows in set (0.00 sec)
用户授权与查看
MariaDB [westos]> GRANT INSERT,UPDATE,DELETE,SELECT on westos.* to zm@localhost; <<<<<授予本地用户zm对wetsos库下面的
所有数据表写入数据,修改数据,删除数据表,查看数据表内容的权利
Query OK, 0 rows affected (0.00 sec)
MariaDB [westos]> show grants for zm@localhost; <<<<<查看本地用户zm的权利
+-----------------------------------------------------------------------------------------------------------+
| Grants for zm@localhost |
+-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zm'@'localhost' IDENTIFIED BY PASSWORD '*28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `westos`.* TO 'zm'@'localhost' |
+-----------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [westos]> REVOKE UPDATE on westos.* from zm@localhost <<<<<收回zm对westos库所有文件更新数据的权利
-> ;
Query OK, 0 rows affected (0.00 sec)
MariaDB [westos]> show grants for zm@localhost;
+-----------------------------------------------------------------------------------------------------------+
| Grants for zm@localhost |
+-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'zm'@'localhost' IDENTIFIED BY PASSWORD '*28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96' |
| GRANT SELECT, INSERT, DELETE ON `westos`.* TO 'zm'@'localhost' |
+-----------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [westos]> DROP USER zm@localhost; <<<<<<<<<删除本地用户zm
Query OK, 0 rows affected (0.00 sec)
MariaDB [westos]> SELECT User FROM mysql.user;
+------+
| User |
+------+
| root |
| root |
| root |
+------+
3 rows in set (0.00 sec)
三,破译数据库超级用户的密码
[root@localhost ~]# systemctl stop mariadb.service <<<<<<<<
[root@localhost ~]# mysqld_safe --skip-grant-tables & <<<<<<<<执行脚本
[1] 4808 跳过授权数据表,打入后台,就可以无密码进入
[root@localhost ~]# 180526 02:43:48 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
180526 02:43:48 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
<<<<<<<这里需要敲下回车
[root@localhost ~]#
[root@localhost ~]# mysql 无密码登陆 <<<<<
MariaDB [(none)]> update mysql.user set Password=password('123') where User='root'; <<<<<<<
注意要以password的方式加秘,这个过程其实就是修改mysql库的user表的User字段的root处的数据
可以查看这个字段root处修改前后的内容和不一样
MariaDB [mysql]> exit; <<<<<<<<<<
[root@localhost ~]# fg 调入前台 <<<<<<<<<<
mysqld_safe --skip-grant-tables
^Z <<<<<<<<<<打入后台
[1]+ Stopped mysqld_safe --skip-grant-tables
[root@localhost ~]# killall -9 mysqld_safe <<<<<<<<<<kill这个脚本
[1]+ Killed mysqld_safe --skip-grant-tables
[root@localhost ~]# ps aux | grep mysql <<<<<<<<<查mysql的所有进程
mysql 4963 0.1 9.3 859052 91012 pts/1 Sl 02:43 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 5054 0.0 0.0 112640 936 pts/1 R+ 02:51 0:00 grep --color=auto mysql
[root@localhost ~]# kill -9 4963 <<<<<<<<<<kill mysql的所有进程(这个包括stop数据库,所以下面要大卡)
[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# mysql -uroot -p
Enter password:
成功登陆!!!!!!!!!
四,命令行管理数据库
注意:一定要加上-e!!!
[root@localhost ~]# mysql -uroot -predhat -e "show databases" <<<<<<-e表示执行
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| westos |
+--------------------+
[root@localhost ~]# mysql -uroot -predhat
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
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 |
| mysql |
| performance_schema |
| westos |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> drop database westos; <<<<<<<<<删除westos库
Query OK, 1 row affected (0.05 sec)
MariaDB [(none)]> quit;
Bye
[root@localhost ~]# mysql -uroot -predhat -e "show databases" <<<<<<<显示库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
更改数据库密码
[root@localhost ~]# mysqladmin -uroot -predhat password 'linux'
五,备份数据库
1,备份
[root@localhost ~]# mysqldump -uroot -predhat westos > /mnt/westos.sql <<<<将数据备份到westos.sql里面
2,删除
[root@localhost ~]# mysql -uroot -predhat -e 'drop database westos' <<<删除
[root@localhost ~]# mysql -uroot -predhat -e 'show databases'
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3,恢复
方法一
1,直接导入
[root@localhost ~]# mysql -uroot -predhat westos < /mnt/westos.sql
ERROR 1049 (42000): Unknown database 'westos' <<<<<所以要先建立一个库
2,编辑备份.sql文件
[root@localhost ~]# vim /mnt/westos.sql
CREATE DATABASE westos; <<<<<<<建立一个库
USE westos; <<<<<<<<使用库
DROP TABLE IF EXISTS `linux`; <<<<<<<<这是原文件,直接就开始恢复,不存在建库的过程,所以要执行上面两行
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
3,导入
[root@localhost ~]# mysql -uroot -predhat < /mnt/westos.sql
方法二
1,手动建立库
[root@localhost ~]# mysql -uroot -predhat -e "CREATE DATABASE westos;"
2,直接导入
[root@localhost ~]# mysql -uroot -predhat westos < /mnt/westos.sql