mariadb

########mariadb##########

資料庫環境的配置

[root@localhost ~]# yum install mariadb-server -y ##資料庫軟體的下載下傳

Loaded plugins: langpacks

Resolving Dependencies

--> Running transaction check

---> Package mariadb-server.x86_64 1:5.5.35-3.el7 will be installed

--> Processing Dependency: mariadb(x86-64) = 1:5.5.35-3.el7 for package: 1:mariadb-server-5.5.35-3.el7.x86_64

--> Processing Dependency: perl(DBI) for package: 1:mariadb-server-5.5.35-3.el7.x86_64

--> Processing Dependency: perl(Data::Dumper) for package: 1:mariadb-server-5.5.35-3.el7.x86_64

--> Processing Dependency: perl-DBD-MySQL for package: 1:mariadb-server-5.5.35-3.el7.x86_64

--> Processing Dependency: perl-DBI for package: 1:mariadb-server-5.5.35-3.el7.x86_64

---> Package mariadb.x86_64 1:5.5.35-3.el7 will be installed

---> Package perl-DBD-MySQL.x86_64 0:4.023-5.el7 will be installed

---> Package perl-DBI.x86_64 0:1.627-4.el7 will be installed

--> Processing Dependency: perl(RPC::PlClient) >= 0.2000 for package: perl-DBI-1.627-4.el7.x86_64

--> Processing Dependency: perl(RPC::PlServer) >= 0.2001 for package: perl-DBI-1.627-4.el7.x86_64

---> Package perl-Data-Dumper.x86_64 0:2.145-3.el7 will be installed

---> Package perl-PlRPC.noarch 0:0.2020-14.el7 will be installed

--> Processing Dependency: perl(Net::Daemon) >= 0.13 for package: perl-PlRPC-0.2020-14.el7.noarch

--> Processing Dependency: perl(Compress::Zlib) for package: perl-PlRPC-0.2020-14.el7.noarch

--> Processing Dependency: perl(Net::Daemon::Log) for package: perl-PlRPC-0.2020-14.el7.noarch

--> Processing Dependency: perl(Net::Daemon::Test) for package: perl-PlRPC-0.2020-14.el7.noarch

---> Package perl-IO-Compress.noarch 0:2.061-2.el7 will be installed

--> Processing Dependency: perl(Compress::Raw::Bzip2) >= 2.061 for package: perl-IO-Compress-2.061-2.el7.noarch

--> Processing Dependency: perl(Compress::Raw::Zlib) >= 2.061 for package: perl-IO-Compress-2.061-2.el7.noarch

---> Package perl-Net-Daemon.noarch 0:0.48-5.el7 will be installed

---> Package perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7 will be installed

---> Package perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7 will be installed

--> Finished Dependency Resolution

Dependencies Resolved

=====================================================================

Package Arch Version Repository

Size

Installing:

mariadb-server x86_64 1:5.5.35-3.el7 rhel_dvd 11 M

Installing for dependencies:

mariadb x86_64 1:5.5.35-3.el7 rhel_dvd 8.9 M

perl-Compress-Raw-Bzip2 x86_64 2.061-3.el7 rhel_dvd 32 k

perl-Compress-Raw-Zlib x86_64 1:2.061-4.el7 rhel_dvd 57 k

perl-DBD-MySQL x86_64 4.023-5.el7 rhel_dvd 140 k

perl-DBI x86_64 1.627-4.el7 rhel_dvd 802 k

perl-Data-Dumper x86_64 2.145-3.el7 rhel_dvd 47 k

perl-IO-Compress noarch 2.061-2.el7 rhel_dvd 260 k

perl-Net-Daemon noarch 0.48-5.el7 rhel_dvd 51 k

perl-PlRPC noarch 0.2020-14.el7 rhel_dvd 36 k

Transaction Summary

Install 1 Package (+9 Dependent packages)

Total download size: 21 M

Installed size: 107 M

Downloading packages:

(1/10): mariadb-5.5.35-3.el7.x86_64.rpm | 8.9 MB 00:01

(2/10): perl-Compress-Raw-Bzip2-2.061-3.el7.x86 | 32 kB 00:00

(3/10): mariadb-server-5.5.35-3.el7.x86_64.rpm | 11 MB 00:01

(4/10): perl-Compress-Raw-Zlib-2.061-4.el7.x86_ | 57 kB 00:00

(5/10): perl-DBD-MySQL-4.023-5.el7.x86_64.rpm | 140 kB 00:00

(6/10): perl-Data-Dumper-2.145-3.el7.x86_64.rpm | 47 kB 00:00

(7/10): perl-IO-Compress-2.061-2.el7.noarch.rpm | 260 kB 00:00

(8/10): perl-DBI-1.627-4.el7.x86_64.rpm | 802 kB 00:00

(9/10): perl-Net-Daemon-0.48-5.el7.noarch.rpm | 51 kB 00:00

(10/10): perl-PlRPC-0.2020-14.el7.noarch.rpm | 36 kB 00:00

---------------------------------------------------------------------

Total 10 MB/s | 21 MB 00:02

Running transaction check

Running transaction test

Transaction test succeeded

Running transaction

Installing : perl-Data-Dumper-2.145-3.el7.x86_64 1/10

Installing : 1:mariadb-5.5.35-3.el7.x86_64 2/10

Installing : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64 3/10

Installing : perl-Net-Daemon-0.48-5.el7.noarch 4/10

Installing : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64 5/10

Installing : perl-IO-Compress-2.061-2.el7.noarch 6/10

Installing : perl-PlRPC-0.2020-14.el7.noarch 7/10

Installing : perl-DBI-1.627-4.el7.x86_64 8/10

Installing : perl-DBD-MySQL-4.023-5.el7.x86_64 9/10

Installing : 1:mariadb-server-5.5.35-3.el7.x86_64 10/10

Verifying : perl-Compress-Raw-Bzip2-2.061-3.el7.x86_64 1/10

Verifying : perl-Net-Daemon-0.48-5.el7.noarch 2/10

Verifying : perl-Data-Dumper-2.145-3.el7.x86_64 3/10

Verifying : perl-PlRPC-0.2020-14.el7.noarch 4/10

Verifying : 1:perl-Compress-Raw-Zlib-2.061-4.el7.x86_64 5/10

Verifying : 1:mariadb-server-5.5.35-3.el7.x86_64 6/10

Verifying : perl-DBI-1.627-4.el7.x86_64 7/10

Verifying : 1:mariadb-5.5.35-3.el7.x86_64 8/10

Verifying : perl-DBD-MySQL-4.023-5.el7.x86_64 9/10

Verifying : perl-IO-Compress-2.061-2.el7.noarch 10/10

Installed:

mariadb-server.x86_64 1:5.5.35-3.el7

Dependency Installed:

mariadb.x86_64 1:5.5.35-3.el7

perl-Compress-Raw-Bzip2.x86_64 0:2.061-3.el7

perl-Compress-Raw-Zlib.x86_64 1:2.061-4.el7

perl-DBD-MySQL.x86_64 0:4.023-5.el7

perl-DBI.x86_64 0:1.627-4.el7

perl-Data-Dumper.x86_64 0:2.145-3.el7

perl-IO-Compress.noarch 0:2.061-2.el7

perl-Net-Daemon.noarch 0:0.48-5.el7

perl-PlRPC.noarch 0:0.2020-14.el7

Complete!

[root@localhost ~]# vim /etc/sysconfig/network-scripts/ifcfg-eth0

[root@localhost ~]# systemctl start mariadb

[root@localhost ~]# netstat -antlpe | grep mysql

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 27 55211 4303/mysqld

[root@localhost ~]# vim /etc/my.cnf ##資料庫本身配置檔案

@@@@@

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

# Settings user and group are ignored when systemd is used.

# If you need to run mysqld under a different user or group,

# customize your systemd unit file for mariadb according to the

# instructions in http://fedoraproject.org/wiki/Systemd

skip-networking=1 ##關閉資料庫在網絡打開的端口

[mysqld_safe]

log-error=/var/log/mariadb/mariadb.log

pid-file=/var/run/mariadb/mariadb.pid

# include all files from the config directory

!includedir /etc/my.cnf.d

@@@@@@

[root@localhost ~]# systemctl restart mariadb

[root@localhost ~]# netstat -antlpe | grep mysql ##檢視打開的端口

[root@localhost ~]# 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

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> exit

Bye

[root@localhost ~]# mysql_secure_installation ##更改登陸設定

/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB

SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current

password for the root user. If you've just installed MariaDB, and

you haven't set the root password yet, the password will be blank,

so you should just press enter here.

Enter current password for root (enter for none):

OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB

root user without the proper authorisation.

Set root password? [Y/n] Y ##是否密碼登陸

New password:

Re-enter new password:

Password updated successfully!

Reloading privilege tables..

... Success!

By default, a MariaDB installation has an anonymous user, allowing anyone

to log into MariaDB without having to have a user account created for

them. This is intended only for testing, and to make the installation

go a bit smoother. You should remove them before moving into a

production environment.

Remove anonymous users? [Y/n] Y ##不允許匿名使用者登陸

Normally, root should only be allowed to connect from 'localhost'. This

ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] Y ##不允許root遠端登陸

By default, MariaDB comes with a database named 'test' that anyone can

access. This is also intended only for testing, and should be removed

before moving into a production environment.

Remove test database and access to it? [Y/n] Y ##是否删除測試資料庫

- Dropping test database...

- Removing privileges on test database...

Reloading the privilege tables will ensure that all changes made so far

will take effect immediately.

Reload privilege tables now? [Y/n] Y ##更新資料庫，是否重新加載

Cleaning up...

All done! If you've completed all of the above steps, your MariaDB

installation should now be secure.

Thanks for using MariaDB!

[root@localhost ~]# mysql ##再次進入資料庫

ERROR 1045 (28000): Access denied for user ##通路被拒 'root'@'localhost' (using password: NO)

[root@localhost ~]# mysql -uroot -pwestos ##使用賬戶密碼登陸（但此登陸方式密碼會被記錄。不安全）

Your MariaDB connection id is 12

[root@localhost ~]# mysql -uroot -p

Enter password: ##使用者登陸，密碼無回顯,更安全

Your MariaDB connection id is 13

[root@localhost ~]#

1.登陸 mysql -uroot -p

Enter password:

2.查詢

*show databases; ##顯示資料庫（同ls）

*use mysql; ##進入mysql資料庫（同cd）

*select * from user; ##查詢表中内容（同show）

* desc user; ##顯示表結構（顯示所有字段的名稱）

MariaDB [(none)]> show databases; ##顯示資料庫

+--------------------+

| Database |

| information_schema |

| mysql |

| performance_schema |

3 rows in set (0.00 sec)

MariaDB [(none)]> use mysql ##進入資料庫

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 [mysql]> show tables; ##顯示目前庫中表的名稱

+---------------------------+

| Tables_in_mysql |

| columns_priv |

| db |

| event |

| func |

| general_log |

| help_category |

| help_keyword |

| help_relation |

| help_topic |

| host |

| ndb_binlog_index |

| plugin |

| proc |

| procs_priv |

| proxies_priv |

| servers |

| slow_log |

| tables_priv |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

24 rows in set (0.00 sec)

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

+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+

| localhost | root | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |

| 127.0.0.1 | root | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |

| ::1 | root | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |

MariaDB [mysql]> select Host,User,Password from user;

+-----------+------+-------------------------------------------+

| Host | User | Password |

| localhost | root | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 |

| 127.0.0.1 | root | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 |

| ::1 | root | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 |

MariaDB [mysql]> desc user; ##顯示表結構（顯示所有字段的名稱）

+------------------------+-----------------------------------+------+-----+---------+-------+

| Host | char(60) | NO | PRI | | |

| User | char(16) | NO | PRI | | |

| Select_priv | enum('N','Y') | NO | | N | |

| Insert_priv | enum('N','Y') | NO | | N | |

| Update_priv | enum('N','Y') | NO | | N | |

| Delete_priv | enum('N','Y') | NO | | N | |

| Create_priv | enum('N','Y') | NO | | N | |

| Drop_priv | enum('N','Y') | NO | | N | |

| Reload_priv | enum('N','Y') | NO | | N | |

| Shutdown_priv | enum('N','Y') | NO | | N | |

| Process_priv | enum('N','Y') | NO | | N | |

| File_priv | enum('N','Y') | NO | | N | |

| Grant_priv | enum('N','Y') | NO | | N | |

| References_priv | enum('N','Y') | NO | | N | |

| Index_priv | enum('N','Y') | NO | | N | |

| Alter_priv | enum('N','Y') | NO | | N | |

| Show_db_priv | enum('N','Y') | NO | | N | |

| Super_priv | enum('N','Y') | NO | | N | |

| Create_tmp_table_priv | enum('N','Y') | NO | | N | |

| Lock_tables_priv | enum('N','Y') | NO | | N | |

| Execute_priv | enum('N','Y') | NO | | N | |

| Repl_slave_priv | enum('N','Y') | NO | | N | |

| Repl_client_priv | enum('N','Y') | NO | | N | |

| Create_view_priv | enum('N','Y') | NO | | N | |

| Show_view_priv | enum('N','Y') | NO | | N | |

| Create_routine_priv | enum('N','Y') | NO | | N | |

| Alter_routine_priv | enum('N','Y') | NO | | N | |

| Create_user_priv | enum('N','Y') | NO | | N | |

| Event_priv | enum('N','Y') | NO | | N | |

| Trigger_priv | enum('N','Y') | NO | | N | |

| Create_tablespace_priv | enum('N','Y') | NO | | N | |

| max_questions | int(11) unsigned | NO | | 0 | |

| max_updates | int(11) unsigned | NO | | 0 | |

| max_connections | int(11) unsigned | NO | | 0 | |

| max_user_connections | int(11) | NO | | 0 | |

42 rows in set (0.00 sec)

3.資料庫及表的建立

MariaDB [mysql]> show databases;

3 rows in set (0.01 sec)

MariaDB [mysql]> create database westos; ##建立westos庫

Query OK, 1 row affected (0.00 sec)

| westos |

4 rows in set (0.00 sec)

MariaDB [(mysql)]> use westos;

MariaDB [westos]> show tables;

Empty set (0.00 sec)

MariaDB [westos]> create table linux( ##建立linux表，并且linux表含有兩個字段，username，password

-> username varchar(15) not null, ##username字段字元長度最大15個，并且不能為空

-> password varchar(50) not null ); ##password字段字元長度最大50個，并且不能為空

Query OK, 0 rows affected (0.39 sec)

MariaDB [westos]> desc linux; ##檢視linux表結構

+----------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

MariaDB [westos]> insert into linux values('user1','123'); ##向linux表中插入資料，username字段的資料為user1

Query OK, 1 row affected (0.33 sec)

MariaDB [westos]> select * from linux ##查詢linux表中個所有内容

-> ;

+----------+----------+

| username | password |

| user1 | 123 |

1 row in set (0.00 sec)

MariaDB [westos]> insert into linux values('user2','234');

Query OK, 1 row affected (0.39 sec)

MariaDB [westos]> select * from linux;

| user2 | 234 |

MariaDB [westos]> insert into linux values('user3',password('234') ); ##插入的password字段的資料是用password加密過的

+----------+-------------------------------------------+

| username | password |

| user1 | 123 |

| user2 | 234 |

| user3 | *565B1B47FD7BC0488435D2B707071F5EF873197B |

4.更新資料庫資訊

MariaDB [westos]> update linux set password=password('123') where password='123'; ##更新原密碼為123的使用者密碼，将其變成加密狀态

Query OK, 1 row affected (0.34 sec)

Rows matched: 1 Changed: 1 Warnings: 0

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

| user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |

MariaDB [westos]> alter table linux add date varchar(20) not null; ##添加date字段到linux表的最後一列

Query OK, 3 rows affected (0.19 sec)

Records: 3 Duplicates: 0 Warnings: 0

+----------+-------------------------------------------+------+

| username | password | date |

| user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | |

| user2 | 234 | |

| user3 | *565B1B47FD7BC0488435D2B707071F5EF873197B | |

MariaDB [westos]> alter table linux add class varchar(20) not null; ##添加class字段到linux表的最後一列

Query OK, 3 rows affected (0.52 sec)

+----------+-------------------------------------------+------+-------+

| user2 | 234 | | |

MariaDB [westos]> alter table linux drop class ; ##删除class字段

Query OK, 3 rows affected (0.41 sec)

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

MariaDB [westos]> alter table linux add class varchar(20) not null after password ; ##添加class字段在password字段之後

Query OK, 3 rows affected (0.16 sec)

+----------+-------------------------------------------+-------+------+

| user2 | 234 | | |

MariaDB [westos]> delete from linux where username='user2'; ##删除使用者user2的資訊

5.資料庫的備份

[root@localhost ~]# mysqldump -uroot -pwestos --all-database --no-data ##備份所有表但不被備份資料

[root@localhost ~]# mysqldump -uroot -pwestos --all-database ##備份所有表中的所有資料

[root@localhost ~]# mysqldump -uroot -pwestos westos ##備份westos庫

-- MySQL dump 10.14 Distrib 5.5.35-MariaDB, for Linux (x86_64)

-- Host: localhost Database: westos

-- ------------------------------------------------------

-- Server version5.5.35-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

-- Table structure for table `linux`

DROP TABLE IF EXISTS `linux`;

/*!40101 SET @saved_cs_client = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `linux` (

`username` varchar(15) NOT NULL,

`password` varchar(50) NOT NULL,

`class` varchar(20) NOT NULL,

`date` varchar(20) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*!40101 SET character_set_client = @saved_cs_client */;

-- Dumping data for table `linux`

LOCK TABLES `linux` WRITE;

/*!40000 ALTER TABLE `linux` DISABLE KEYS */;

INSERT INTO `linux` VALUES ('user1','*23AE809DDACAF96AF0FD78ED04B6A265E05AA257','',''),('user3','*565B1B47FD7BC0488435D2B707071F5EF873197B','','');

/*!40000 ALTER TABLE `linux` ENABLE KEYS */;

UNLOCK TABLES;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2017-05-12 23:43:51

[root@localhost ~]# mysqldump -uroot -pwestos westos > /mnt/westos.sql ##備份westos庫并儲存到/mnt/westos.sql中

[root@localhost ~]# mysqldump -uroot -pwestos westos linux> /mnt/linux.sql ##備份westos庫中的linux表

[root@localhost ~]# mysql -uroot -pwestos -e "create database westos;" ##建立westos庫

[root@localhost ~]# mysql -uroot -pwestos westos < /mnt/westos.sql ##把資料導入到westos庫

6.删除資料庫

delete from linux where username='user1'; ##從linux表中删除user1

drop table linux; ##删除linux表

drop database westos; ##删除westos庫

7.使用者授權

MariaDB [westos]> select user,Host from mysql.user;

+------+-----------+

| user | Host |

| root | 127.0.0.1 |

| root | ::1 |

| root | localhost |

MariaDB [westos]> create user hello@localhost identified by 'hello'; ##建立使用者hello，此使用者隻能通過本機登陸

Query OK, 0 rows affected (0.00 sec)

+-------+-----------+

| user | Host |

| root | 127.0.0.1 |

| root | ::1 |

| hello | localhost |

| root | localhost |

MariaDB [westos]> create user hello@'%' identified by 'hello'; ##建立使用者hello，此使用者可以通過網絡登陸

| hello | % |

5 rows in set (0.00 sec)

MariaDB [westos]> quit

[root@server mnt]# mysql -uhello -p

Your MariaDB connection id is 5

MariaDB [(none)]> quit

[root@server mnt]# mysql -uhello -phello -h localhost

Your MariaDB connection id is 6

[root@server mnt]# mysql -uhello -phello -h 172.25.254.113

ERROR 2003 (HY000): Can't connect to MySQL server on '172.25.254.113' (111)

[root@server mnt]# vim /etc/my.cnf

skip-networking=0 ##打開資料庫網絡端口

[root@server mnt]# systemctl restart mariadb.service

MariaDB [(none)]> show databases;

[root@server mnt]# mysql -uroot -pwestos

Your MariaDB connection id is 3

MariaDB [(none)]> grant insert,update,delete,select on westos.* to hello@localhost; ##使用者授權

MariaDB [(none)]> grant select on westos.* to hello@'%';

Your MariaDB connection id is 4

MariaDB [(none)]> use westos;

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

+----------+-----------------+------+-------+

show grants for hello@'%'; ##檢視使用者授權

show grants for hello@localhost;

revoke delete on westos.* from hello@localhost; ##去除使用者授權權力

drop user hello@'%'; ##删除使用者

8.密碼修改

1）密碼記得，隻是修改

[root@localhost ~]# mysqladmin -u root -pwestos password xiao

更改密碼後測試：

[root@localhost ~]# mysql -u root -pwestos

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

[root@localhost ~]# mysql -u root -pxiao

2)超級使用者密碼忘記

[root@localhost ~]# systemctl stop mariadb.service ##關閉mysql

[root@localhost ~]# mysqld_safe --skip-grant-tables & ##開啟mysql登陸接口并忽略授權表

[1] 7702

[root@localhost ~]# 170513 01:56:56 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.

170513 01:56:56 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

[root@localhost ~]# mysql ##直接不用密碼登陸

Your MariaDB connection id is 1

MariaDB [(none)]> update mysql.user set Password=password('westos') where User='root'; ##更新超級使用者密碼資訊

Rows matched: 3 Changed: 1 Warnings: 0

MariaDB [(none)]> select User,Host,Password from mysql.user;

+------+-----------+-------------------------------------------+

| User | Host | Password |

| root | localhost | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 |

| root | 127.0.0.1 | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 |

| root | ::1 | *28C1E2BE21B45562A34B6CC34A19CFAFC2F88F96 |

| xiao | localhost | *17B4EAD97343D050B0713F77137AD9E9C86D2CDB |

[root@localhost ~]# fg

mysqld_safe --skip-grant-tables

^C^Z

[1]+ Stopped mysqld_safe --skip-grant-tables

[root@localhost ~]# killall -9 mysqld_safe

[1]+ Killed mysqld_safe --skip-grant-tables

[root@localhost ~]# ps aux | grep mysqld_safe

root 7950 0.0 0.0 112640 944 pts/1 R+ 02:01 0:00 grep --color=auto mysqld_safe

[root@localhost ~]# ps aux | grep mysql

###資料庫的網頁管理工具

1.安裝

yum install httpd php php-mysql -y

systemctl start httpd

systemctl enable httpd

systemctl stop firewalld

systemctl disable firewalld

[root@localhost ~]# yum install httpd -y

---> Package httpd.x86_64 0:2.4.6-17.el7 will be installed

--> Processing Dependency: httpd-tools = 2.4.6-17.el7 for package: httpd-2.4.6-17.el7.x86_64

--> Processing Dependency: /etc/mime.types for package: httpd-2.4.6-17.el7.x86_64

--> Processing Dependency: libapr-1.so.0()(64bit) for package: httpd-2.4.6-17.el7.x86_64

--> Processing Dependency: libaprutil-1.so.0()(64bit) for package: httpd-2.4.6-17.el7.x86_64

---> Package apr.x86_64 0:1.4.8-3.el7 will be installed

---> Package apr-util.x86_64 0:1.5.2-6.el7 will be installed

---> Package httpd-tools.x86_64 0:2.4.6-17.el7 will be installed

---> Package mailcap.noarch 0:2.1.41-2.el7 will be installed

==================================================================

Package Arch Version Repository Size

httpd x86_64 2.4.6-17.el7 rhel_dvd 1.2 M

apr x86_64 1.4.8-3.el7 rhel_dvd 103 k

apr-util x86_64 1.5.2-6.el7 rhel_dvd 92 k

httpd-tools x86_64 2.4.6-17.el7 rhel_dvd 77 k

mailcap noarch 2.1.41-2.el7 rhel_dvd 31 k

Install 1 Package (+4 Dependent packages)

Total download size: 1.5 M

Installed size: 4.3 M

(1/5): apr-1.4.8-3.el7.x86_64.rpm | 103 kB 00:00

(2/5): apr-util-1.5.2-6.el7.x86_64.rpm | 92 kB 00:00

(3/5): httpd-tools-2.4.6-17.el7.x86_64.rpm | 77 kB 00:00

(4/5): mailcap-2.1.41-2.el7.noarch.rpm | 31 kB 00:00

(5/5): httpd-2.4.6-17.el7.x86_64.rpm | 1.2 MB 00:00

------------------------------------------------------------------

Total 6.2 MB/s | 1.5 MB 00:00

Installing : apr-1.4.8-3.el7.x86_64 1/5

Installing : apr-util-1.5.2-6.el7.x86_64 2/5

Installing : httpd-tools-2.4.6-17.el7.x86_64 3/5

Installing : mailcap-2.1.41-2.el7.noarch 4/5

Installing : httpd-2.4.6-17.el7.x86_64 5/5

Verifying : mailcap-2.1.41-2.el7.noarch 1/5

Verifying : httpd-tools-2.4.6-17.el7.x86_64 2/5

Verifying : apr-1.4.8-3.el7.x86_64 3/5

Verifying : apr-util-1.5.2-6.el7.x86_64 4/5

Verifying : httpd-2.4.6-17.el7.x86_64 5/5

httpd.x86_64 0:2.4.6-17.el7

apr.x86_64 0:1.4.8-3.el7 apr-util.x86_64 0:1.5.2-6.el7

httpd-tools.x86_64 0:2.4.6-17.el7 mailcap.noarch 0:2.1.41-2.el7

[root@localhost ~]# yum install php -y

---> Package php.x86_64 0:5.4.16-21.el7 will be installed

--> Processing Dependency: php-common(x86-64) = 5.4.16-21.el7 for package: php-5.4.16-21.el7.x86_64

--> Processing Dependency: php-cli(x86-64) = 5.4.16-21.el7 for package: php-5.4.16-21.el7.x86_64

---> Package php-cli.x86_64 0:5.4.16-21.el7 will be installed

---> Package php-common.x86_64 0:5.4.16-21.el7 will be installed

--> Processing Dependency: libzip.so.2()(64bit) for package: php-common-5.4.16-21.el7.x86_64

---> Package libzip.x86_64 0:0.10.1-8.el7 will be installed

Package Arch Version Repository Size

php x86_64 5.4.16-21.el7 rhel_dvd 1.3 M

libzip x86_64 0.10.1-8.el7 rhel_dvd 49 k

php-cli x86_64 5.4.16-21.el7 rhel_dvd 2.7 M

php-common x86_64 5.4.16-21.el7 rhel_dvd 559 k

Install 1 Package (+3 Dependent packages)

Total download size: 4.7 M

Installed size: 17 M

(1/4): libzip-0.10.1-8.el7.x86_64.rpm | 49 kB 00:00

(2/4): php-5.4.16-21.el7.x86_64.rpm | 1.3 MB 00:00

(3/4): php-common-5.4.16-21.el7.x86_64.rpm | 559 kB 00:00

(4/4): php-cli-5.4.16-21.el7.x86_64.rpm | 2.7 MB 00:00

Total 8.7 MB/s | 4.7 MB 00:00

Installing : libzip-0.10.1-8.el7.x86_64 1/4

Installing : php-common-5.4.16-21.el7.x86_64 2/4

Installing : php-cli-5.4.16-21.el7.x86_64 3/4

Installing : php-5.4.16-21.el7.x86_64 4/4

Verifying : php-common-5.4.16-21.el7.x86_64 1/4

Verifying : php-cli-5.4.16-21.el7.x86_64 2/4

Verifying : libzip-0.10.1-8.el7.x86_64 3/4

Verifying : php-5.4.16-21.el7.x86_64 4/4

php.x86_64 0:5.4.16-21.el7

libzip.x86_64 0:0.10.1-8.el7

php-cli.x86_64 0:5.4.16-21.el7

php-common.x86_64 0:5.4.16-21.el7

[root@localhost ~]# yum install php-mysql.x86_64 -y

---> Package php-mysql.x86_64 0:5.4.16-21.el7 will be installed

--> Processing Dependency: php-pdo(x86-64) = 5.4.16-21.el7 for package: php-mysql-5.4.16-21.el7.x86_64

---> Package php-pdo.x86_64 0:5.4.16-21.el7 will be installed

Package Arch Version Repository Size

php-mysql x86_64 5.4.16-21.el7 rhel_dvd 96 k

php-pdo x86_64 5.4.16-21.el7 rhel_dvd 93 k

Install 1 Package (+1 Dependent package)

Total download size: 189 k

Installed size: 424 k

(1/2): php-mysql-5.4.16-21.el7.x86_64.rpm | 96 kB 00:00

(2/2): php-pdo-5.4.16-21.el7.x86_64.rpm | 93 kB 00:00

Total 233 kB/s | 189 kB 00:00

Installing : php-pdo-5.4.16-21.el7.x86_64 1/2

Installing : php-mysql-5.4.16-21.el7.x86_64 2/2

Verifying : php-mysql-5.4.16-21.el7.x86_64 1/2

Verifying : php-pdo-5.4.16-21.el7.x86_64 2/2

php-mysql.x86_64 0:5.4.16-21.el7

php-pdo.x86_64 0:5.4.16-21.el7

[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

[root@localhost ~]# systemctl disable firewalld

rm '/etc/systemd/system/basic.target.wants/firewalld.service'

rm '/etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service'

[root@localhost ~]# vim /var/www/html/index.html

2.下載下傳

[root@localhost ~]# lftp 172.25.254.250

bash: lftp: command not found...

[root@localhost ~]# yum install lftp -y

---> Package lftp.x86_64 0:4.4.8-3.el7 will be installed

Package Arch Version Repository Size

lftp x86_64 4.4.8-3.el7 rhel_dvd 749 k

Install 1 Package

Total download size: 749 k

Installed size: 2.4 M

lftp-4.4.8-3.el7.x86_64.rpm | 749 kB 00:00

Installing : lftp-4.4.8-3.el7.x86_64 1/1

Verifying : lftp-4.4.8-3.el7.x86_64 1/1

lftp.x86_64 0:4.4.8-3.el7

lftp 172.25.254.250:~> cd pub/

cd ok, cwd=/pub

lftp 172.25.254.250:/pub> cd docs/software/

lftp 172.25.254.250:/pub/docs/software> ls

-rwxr-xr-x 1 1000 1000 3086326 Dec 25 2013 phpMyAdmin-2.11.3-all-languages.tar.bz2

-rwxr-xr-x 1 1000 1000 4548030 Dec 25 2013 phpMyAdmin-3.4.0-all-languages.tar.bz2

-rw-rw-r-- 1 1000 1000 2713600 Jun 07 2015 taobao.tar

-rwxr-xr-x 1 1000 1000 52387876 Feb 01 2015 thunderbird-31.2.0-1.el7.x86_64.rpm

-rwxr-xr-x 1 1000 1000 36902724 Feb 01 2015 thunderbird-31.4.0.tar.bz2

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

[root@localhost ~]# ls

anaconda-ks.cfg

Desktop

Documents

Downloads

Kwestoskey.+157+24921.key

Kwestoskey.+157+24921.private

Music

phpMyAdmin-3.4.0-all-languages.tar.bz2

Pictures

Public

Templates

Videos

[root@localhost ~]# tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2 -C /var/www/html/ ##解壓

[root@localhost ~]# cd /var/www/html/

[root@localhost html]# ls

index.html phpMyAdmin-3.4.0-all-languages

[root@localhost html]# mv phpMyAdmin-3.4.0-all-languages/ mysqladmin ##更改檔案名為mysqladmin

[root@localhost html]# ll

total 8

-rw-r--r--. 1 root root 23 May 13 02:27 index.html

drwxr-xr-x. 10 root root 4096 May 11 2011 mysqladmin

[root@localhost html]# mysql -uroot -pwestos

[root@localhost html]# cd mysqladmin

[root@localhost mysqladmin]# cp -p config.sample.inc.php config.inc.php

[root@localhost mysqladmin]# vim config.inc.php

17$cfg['blowfish_secret'] = 'mysql'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */ ##添加mysql（此名任意什麼都可以）

[root@localhost mysqladmin]# systemctl restart httpd

[root@localhost mysqladmin]# mysql

3.測試：

mariadb

繼續閱讀

Testlink安裝部署之XAMPP

set define off關閉替代變量功能

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述