#####################################

############ 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

就可以登陆了

西部开源学习笔记BOOK3《unit 3.MYSQL》