#####################################
############ unit3.MYSQL ############
#####################################
##########安装软件###########
yum install mariadb -y
###########创建|删除用户#############
1.创建本地用户
MariaDB [(none)]> CREATE USER redhat@laocalhost identified by 'redhat';
2.创建非本地用户（可通过数据库的网络接口登陆，但该接口必须开启才可以生效）
MariaDB [(none)]> CREATE USER redhat@'%' identified by 'redhat';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SELECT Host,User,Password FROM mysql.user;
+-----------+--------+-------------------------------------------+
| Host | User | Password |
+-----------+--------+-------------------------------------------+
| localhost | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| 127.0.0.1 | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| ::1 | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| % | redhat | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| localhost | westos | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 |
+-----------+--------+-------------------------------------------+
5 rows in set (0.00 sec)
3.删除用户
MariaDB [(none)]> DROP USER redhat@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SELECT Host,User,Password FROM mysql.user;
+-----------+--------+-------------------------------------------+
| Host | User | Password |
+-----------+--------+-------------------------------------------+
| localhost | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| 127.0.0.1 | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| ::1 | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| localhost | westos | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 |
+-----------+--------+-------------------------------------------+
4 rows in set (0.01 sec)
###########用户授权#############
注意：用户授权只能在root上做
MariaDB [(none)]> SELECT HOST,USER.PASSWD FROM mysql.user
-> ;
ERROR 1054 (42S22): Unknown column 'USER.PASSWD' in 'field list'
MariaDB [(none)]> SELECT Host,User,Password FROM mysql.user
-> ;
+-----------+------+-------------------------------------------+
| Host | User | Password |
+-----------+------+-------------------------------------------+
| localhost | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| 127.0.0.1 | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| ::1 | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
+-----------+------+-------------------------------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]> CREATE USER westos@localhost identified by 'westos';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SELECT Host,User,Password FROM mysql.user;
+-----------+--------+-------------------------------------------+
| Host | User | Password |
+-----------+--------+-------------------------------------------+
| localhost | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| 127.0.0.1 | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| ::1 | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| localhost | westos | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 |
+-----------+--------+-------------------------------------------+
4 rows in set (0.00 sec)
##先查看各个用户的部分权限列表
MariaDB [(none)]> SELECT User,Select_priv,Insert_priv,Create_priv FROM mysql.user;
+--------+-------------+-------------+-------------+
| User | Select_priv | Insert_priv | Create_priv |
+--------+-------------+-------------+-------------+
| root | Y | Y | Y |
| root | Y | Y | Y |
| root | Y | Y | Y |
| westos | N | N | N |
+--------+-------------+-------------+-------------+
4 rows in set (0.00 sec)
##添加权限
MariaDB [(none)]> GRANT SELECT,INSERT,CREATE on *.* to westos@localhost;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SELECT User,Select_priv,Insert_priv,Create_priv FROM mysql.user;
+--------+-------------+-------------+-------------+
| User | Select_priv | Insert_priv | Create_priv |
+--------+-------------+-------------+-------------+
| root | Y | Y | Y |
| root | Y | Y | Y |
| root | Y | Y | Y |
| westos | Y | Y | Y |
+--------+-------------+-------------+-------------+
4 rows in set (0.00 sec)
补充：如果权限没有生效，可以通过以下方法：
MariaDB [(none)]> FLUSH PRIVILEGES;##重载授权表
Query OK, 0 rows affected (0.00 sec)
##撤销权限
MariaDB [(none)]> REVOKE SELECT,INSERT,CREATE on *.* from westos@localhost;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SELECT User,Select_priv,Insert_priv,Create_priv FROM mysql.user;
+--------+-------------+-------------+-------------+
| User | Select_priv | Insert_priv | Create_priv |
+--------+-------------+-------------+-------------+
| root | Y | Y | Y |
| root | Y | Y | Y |
| root | Y | Y | Y |
| westos | N | N | N |
+--------+-------------+-------------+-------------+
4 rows in set (0.00 sec)
#############忘记root密码|修改密码#############
1.忘记root密码
[root@dns-server ~]# systemctl stop mariadb##关闭mariadb
[root@dns-server ~]# mysqld_safe --skip-grant-tables &##开启mysql的单用户模式
[1] 2469
161126 20:52:29 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
161126 20:52:29 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
[root@dns-server ~]# mysql -u root
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)]> SELECT User,Password FROM mysql.user；
+--------+-------------------------------------------+
| User | Password |
+--------+-------------------------------------------+
| root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| westos | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 |
+--------+-------------------------------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> UPDATE mysql.user set Password='westos' WHERE User='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
MariaDB [(none)]> SELECT User,Password FROM mysql.user;
+--------+-------------------------------------------+
| User | Password |
+--------+-------------------------------------------+
| root | westos |
| root | westos |
| root | westos |
| westos | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 |
+--------+-------------------------------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> UPDATE mysql.user set Password=password('westos') WHERE User='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
MariaDB [(none)]> SELECT User,Password FROM mysql.user;
+--------+-------------------------------------------+
| User | Password |
+--------+-------------------------------------------+
| root | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 |
| root | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 |
| root | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 |
| westos | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 |
+--------+-------------------------------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> Ctrl-C -- exit!
Aborted
[root@dns-server ~]# fg
mysqld_safe --skip-grant-tables
^Z##ctrl+z
[1]+ Stopped mysqld_safe --skip-grant-tables
[root@dns-server ~]# killall -9 mysqld_safe##关闭单用户模式
[1]+ Killed mysqld_safe --skip-grant-tables
[root@dns-server ~]# ps aux | grep mysql
mysql 2624 0.0 5.2 859052 98764 pts/0 Sl 20:52 0:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysq --skip-grant-tables --log-error=/var/log/mariadb/mariadb.log --pid-file=/var/run/mariadb/mariadb.pid --socket=/var/lib/mysql/mysql.sock
root 2709 0.0 0.0 112640 980 pts/0 R+ 20:59 0:00 grep --color=auto mysql
[root@dns-server ~]# kill -9 2624##关闭mysql相关进程
[root@dns-server ~]# systemctl start mariadb##开启mariadb
[root@dns-server ~]# 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)]> quit
Bye
2.修改密码（知道密码）
[root@dns-server ~]# mysqladmin -uroot -pwestos password redhat
[root@dns-server ~]# mysql -uroot -pwestos
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@dns-server ~]# mysql -uroot -predhat
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
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
#############web方式管理MYSQL##################
1.安装lftp
[root@dns-server ~]# yum install lftp -y
2.去ftp服务器上下载phpMyAdmin
[root@dns-server ~]# lftp 172.25.254.250
lftp 172.25.254.250:~> cd /pub/docs/software/
lftp 172.25.254.250:/pub/docs/software> get phpMyAdmin-3.4.0-all-languages.tar.bz2
4548030 bytes transferred
lftp 172.25.254.250:/pub/docs/software> exit
[root@dns-server ~]# ls
anaconda-ks.cfg Downloads Pictures Videos
Desktop Music Public
Documents phpMyAdmin-3.4.0-all-languages.tar.bz2 Templates
3.安装httpd
[root@dns-server ~]# yum install httpd -y
[root@dns-server ~]# mv phpMyAdmin-3.4.0-all-languages.tar.bz2 /var/www/html/
[root@dns-server ~]# cd /var/www/html/
[root@dns-server html]# ls
phpMyAdmin-3.4.0-all-languages.tar.bz2
[root@dns-server html]# tar -jxf phpMyAdmin-3.4.0-all-languages.tar.bz2
[root@dns-server html]# ls
phpMyAdmin-3.4.0-all-languages phpMyAdmin-3.4.0-all-languages.tar.bz2
[root@dns-server html]# rm -fr phpMyAdmin-3.4.0-all-languages.tar.bz2
[root@dns-server html]# ls
phpMyAdmin-3.4.0-all-languages
[root@dns-server html]# firewall-cmd --permanent --add-service=http
success
[root@dns-server html]# firewall-cmd --reload
success
[root@dns-server html]# mv phpMyAdmin-3.4.0-all-languages phpadmin
[root@dns-server html]# ls
phpadmin
4.安装php相关软件
[root@dns-server html]# 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@dns-server html]# yum install php -y
[root@dns-server html]# yum install php-mysql.x86_64 -y
5.修改php相关配置
[root@dns-server html]# cd phpadmin/
[root@dns-server phpadmin]# ls
browse_foreigners.php main.php server_status.php
bs_disp_as_mime_type.php navigation.php server_synchronize.php
bs_play_media.php phpdoctor.ini server_variables.php
ChangeLog phpinfo.php setup
changelog.php phpmyadmin.css.php show_config_errors.php
chk_rel.php pmd sql.php
config.sample.inc.php pmd_common.php tbl_addfield.php
contrib pmd_display_field.php tbl_alter.php
CREDITS pmd_general.php tbl_change.php
db_create.php pmd_help.php tbl_chart.php
db_datadict.php pmd_pdf.php tbl_create.php
db_export.php pmd_relation_new.php tbl_export.php
db_import.php pmd_relation_upd.php tbl_get_field.php
db_operations.php pmd_save_pos.php tbl_import.php
db_printview.php prefs_forms.php tbl_indexes.php
db_qbe.php prefs_manage.php tbl_move_copy.php
db_search.php print.css tbl_operations.php
db_sql.php querywindow.php tbl_printview.php
db_structure.php README tbl_relation.php
db_tracking.php README.VENDOR tbl_replace.php
docs.css RELEASE-DATE-3.4.0 tbl_row_action.php
Documentation.html robots.txt tbl_select.php
Documentation.txt schema_edit.php tbl_sql.php
enum_editor.php schema_export.php tbl_structure.php
export.php scripts tbl_tracking.php
favicon.ico server_binlog.php themes
import.php server_collations.php themes.php
import_status.php server_databases.php TODO
index.php server_engines.php transformation_overview.php
INSTALL server_export.php transformation_wrapper.php
js server_import.php url.php
libraries server_privileges.php user_password.php
LICENSE server_processlist.php view_create.php
license.php server_replication.php view_operations.php
locale server_sql.php webapp.php
[root@dns-server phpadmin]# less README ##可以查看该文件
[root@dns-server phpadmin]# less Documentation.txt##去查看该文件，会指导需要修改的文件
[root@dns-server phpadmin]# cp -p config.sample.inc.php config.inc.php##注意：要加-p
[root@dns-server phpadmin]# vim config.inc.php
17 $cfg['blowfish_secret'] = 'linux'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! ##必须加一个cookie值，内容不限
[root@dns-server phpadmin]# systemctl restart httpd.service ##重启后生效
测试：
真机172.25.254.19：
打开firefox
http://172.25.254.219/phpadmin
就可以登陆了
