数据库
来源：互联网 发布：朗诵录音软件 编辑：程序博客网 时间：2024/06/15 19:25
安装数据库
[root@test ~]# history
46 vim /etc/yum.repos.d/rhel_dvd.repo ##配置yum源
47 yum install mariadb-server -y ##安装数据库
48 systemctl restart mariadb ##重启
49 mysql ##进入数据库（无密码可直接进入）
50 netstat -antlpe | grep mysql ##看数据库是否可以通过网络访问
51 vim /etc/my.cnf ##数据库的配置文件 添加 skip-networking=1
53 systemctl restart mariadb.service ##重启
54 netstat -antlpe | grep mysql ##再次看数据库是否可以通过网络访问
55 mysql ##进入数据库
57 mysql_secure_installation ##选择开启或关闭数据库的一些功能（设置密码等）
58 mysql ##进入数据库，此时出错
60 mysql -uroot -predhat ##这种方式不安全，会窃取到密码
61 mysql -uroot -p ##这种方式安全
62 history
--------------------------------------------------------------------------------------
[kiosk@foundation5 Desktop]$ ssh root@172.25.254.105
root@172.25.254.105's password:
Last login: Fri May 12 21:42:35 2017
[root@test ~]#
[root@test ~]# vim /etc/yum.repos.d/rhel_dvd.repo
[root@test ~]# yum install mariadb-server -y
Loaded plugins: langpacks
......
Complete!
[root@test ~]# systemctl restart mariadb
[root@test ~]# 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
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 database
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'database' at line 1
MariaDB [(none)]> show database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'database' at line 1
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 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]> quit
Bye
[root@test ~]# netstat -antlpe | grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 27 59026 2679/mysqld
[root@test ~]# vim /etc/my.cnf
[root@test ~]# netstat -antlpe | grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 27 59026 2679/mysqld
[root@test ~]# systemctl restart mariadb.service
[root@test ~]# netstat -antlpe | grep mysql
[root@test ~]# 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
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
[root@test ~]# my_secure_installation
bash: my_secure_installation: command not found...
[root@test ~]# 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
... Success!
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
... Success!
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...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y
... Success!
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@test ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@test ~]# mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@test ~]# mysql -uroot -predhat
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
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
[root@test ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 14
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
[root@test ~]#
-----------------------------------------------------------------------------------
2.查询
MariaDB [(none)]> show databases; ##显示数据库
MariaDB [(none)]> use mysql; ##进入mysql库
MariaDB [mysql]> show tables; ##显示当前库中表的名称
MariaDB [mysql]> select * from user; ##查询表中所有内容（*可用此表中的任何字段代替）
MariaDB [mysql]> select Host,User,Password from user;##查询表中指定内容
MariaDB [mysql]> desc user; ##显示user表中的所有字段
3.数据库中表的建立
表在/var/lib/mysql下可以查到，可以在这里删除
MariaDB [mysql]> create database westos; ##创建westos库
MariaDB [westos]> create table linux( username varchar(15) not null, password varchar(50) not null);##创建linux表，并且linux表含有两个不能为空的字段username，password，字符长度最大分别为 15，50 个。
也可以这样写，方便检错：
MariaDB [westos]> create table linux(
-> username varchar(15) not null,
-> password varchar(50) not null);
MariaDB [westos]> insert into linux values ('user1','123');##向linux表中添加数据。
MariaDB [westos]> insert into linux values ('user2',password('123') );##加密密码
MariaDB [westos]> select * from linux;
+----------+-------------------------------------------+
| username | password |
+----------+-------------------------------------------+
| user1 | 123 |
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+----------+-------------------------------------------+
-------------------------------------------------------------------------------------
[root@test ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@test ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 18
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)]> 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 databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.01 sec)
MariaDB [mysql]> creat database westos;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'creat database westos' at line 1
MariaDB [mysql]> create database westos;
Query OK, 1 row affected (0.00 sec)
MariaDB [mysql]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| westos |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [mysql]> quit
Bye
[root@test ~]# cd /var/lib/mysql
[root@test mysql]# ls
aria_log.00000001 ibdata1 ib_logfile1 mysql.sock westos
aria_log_control ib_logfile0 mysql performance_schema
[root@test mysql]# rm -fr westos/
[root@test mysql]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@test mysql]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 20
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 |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]> create database westos;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| westos |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> use westos
Database changed
MariaDB [westos]> show tables;
Empty set (0.00 sec)
MariaDB [westos]> create table linux(
-> username varchar(15),not null,
-> password varchar(50) not null);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'not null,
password varchar(50) not null)' at line 2
MariaDB [westos]> create table linux( username varchar(15) not null, password varchar(50) not null);
Query OK, 0 rows affected (0.06 sec)
MariaDB [westos]> show tables;+------------------+
| Tables_in_westos |
+------------------+
| linux |
+------------------+
1 row in set (0.00 sec)
MariaDB [westos]> select * from linux
-> ;
Empty set (0.00 sec)
MariaDB [westos]> select * from linux;
Empty set (0.00 sec)
MariaDB [westos]> insert into linux values ('user1','123');
Query OK, 1 row affected (0.32 sec)
MariaDB [westos]> select * from linux;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
+----------+----------+
1 row in set (0.00 sec)
MariaDB [westos]> insert into linux values ('user2',password('123') );
Query OK, 1 row affected (0.30 sec)
MariaDB [westos]> select * from linux;
+----------+-------------------------------------------+
| username | password |
+----------+-------------------------------------------+
| user1 | 123 |
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+----------+-------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [westos]> quit
Bye
[root@test mysql]#
---------------------------------------------------------------------------------------
4.更新数据库信息
update linux set password=password('passwd2') where username='user1';##更新user1密码（进行加密）
update linux set password=password('456') where ( username='user1' or username='user2' );
MariaDB [westos]> delete from linux where username='user1';##删除user1信息
MariaDB [westos]> alter table linux add age varchar(4);##添加age字段到表的最后一列
MariaDB [westos]> alter table linux add sex varchar(4) not null;##同上
MariaDB [westos]> alter table linux drop sex; ##删除sex字段
MariaDB [westos]> alter table linux add sex varchar(4) not null after password;##添加sex字段到password字段之后
------------------------------------------------------------------------------------
[root@test mysql]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 21
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)]> 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 databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| westos |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [mysql]> 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]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| westos |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [westos]> select * from linux
-> ;
+----------+-------------------------------------------+
| username | password |
+----------+-------------------------------------------+
| user1 | 123 |
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+----------+-------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [westos]> insert into linux values ('user3','123');
Query OK, 1 row affected (0.02 sec)
MariaDB [westos]> select * from linux;
+----------+-------------------------------------------+
| username | password |
+----------+-------------------------------------------+
| user1 | 123 |
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| user3 | 123 |
+----------+-------------------------------------------+
3 rows in set (0.00 sec)
MariaDB [westos]> update linux set password=password('123') where passwd='123';
ERROR 1054 (42S22): Unknown column 'passwd' in 'where clause'
MariaDB [westos]> update linux set password=password('123') where password='123';
Query OK, 2 rows affected (0.33 sec)
Rows matched: 2 Changed: 2 Warnings: 0
MariaDB [westos]> select * from linux;
+----------+-------------------------------------------+
| username | password |
+----------+-------------------------------------------+
| user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| user3 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+----------+-------------------------------------------+
3 rows in set (0.00 sec)
MariaDB [westos]> delete from linux where username=user1;
ERROR 1054 (42S22): Unknown column 'user1' in 'where clause'
MariaDB [westos]> delete from linux where username='user1';
Query OK, 1 row affected (0.32 sec)
MariaDB [westos]> alter table linux add age varchar(4);
Query OK, 2 rows affected (0.37 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [westos]> select * from linux;
+----------+-------------------------------------------+------+
| username | password | age |
+----------+-------------------------------------------+------+
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | NULL |
| user3 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | NULL |
+----------+-------------------------------------------+------+
2 rows in set (0.00 sec)
MariaDB [westos]> alter table linux add sex varchar(4) not null;
Query OK, 2 rows affected (0.11 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [westos]> select * from linux;
+----------+-------------------------------------------+------+-----+
| username | password | age | sex |
+----------+-------------------------------------------+------+-----+
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | NULL | |
| user3 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | NULL | |
+----------+-------------------------------------------+------+-----+
2 rows in set (0.00 sec)
MariaDB [westos]> alter table linux drop sex;
Query OK, 2 rows affected (0.48 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [westos]> select * from linux;
+----------+-------------------------------------------+------+
| username | password | age |
+----------+-------------------------------------------+------+
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | NULL |
| user3 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | NULL |
+----------+-------------------------------------------+------+
2 rows in set (0.00 sec)
MariaDB [westos]> alter table linux add sex varchar(4) not null after password;
Query OK, 2 rows affected (0.36 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [westos]> select * from linux;
+----------+-------------------------------------------+-----+------+
| username | password | sex | age |
+----------+-------------------------------------------+-----+------+
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | NULL |
| user3 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | NULL |
+----------+-------------------------------------------+-----+------+
2 rows in set (0.00 sec)
MariaDB [westos]>
--------------------------------------------------------------------------------------
5.删除数据库
delete from linux where username='user1';
drop table linux;
drop database westos;
6.数据库的备份
mysqldump -u root -predhat --all-database ##备份所有表中的所有数据
mysqldump -u root -predhat --all-database --no-data##备份所有表，但不备份数据
mysqldump -u root -predhat westos ##备份westos库
mysqldump -u root -predhat westos > /mnt/westos.sql##备份westos库，并把数据保存到westos.sql中
mysqldump -u root -predhat westos linux > /mnt/linux.sql##备份westos库中的linux表
mysqldump -u root -predhat westos test > /mnt/test.sql##备份westos库中的test表
mysql -u root -predhat -e "create database westos;"##建立westos库
mysql -uroot -predhat westos < /mnt/westos.sql ##把数据导入westos库
mysql -uroot -predhat westos < /mnt/linux.sql ##把linux表数据导入westos库
7.用户授权
create user lee@localhost identified by 'lee'; ##建立用户lee，此用户只能通过本机登陆
create user lee@'%' identified by 'lee'; ##建立用户lee，此用户可以通过网络登陆
grant insert,update,delete,select on westos.test to lee@localhost;##用户授权
grant select on westos.* to lee@'%'; ##用户授权
show grants for lee@'%'; ##查看用户权力
show grants for lee@localhost; ##查看用户权力
revoke delete on westos.test from lee@localhost; ##去除用户授权的权力
drop user lee@'%'; ##删除用户
-------------------------------------------------------------------------
[root@test mysql]# mysql -uroot -predhat
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 38
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,Host from mysql.user;
+------+-----------+
| User | Host |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
+------+-----------+
3 rows in set (0.00 sec)
MariaDB [(none)]> create user lee@localhost identified by 'yy';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select User,Host from mysql.user;
+------+-----------+
| User | Host |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| lee | localhost |
| root | localhost |
+------+-----------+
4 rows in set (0.00 sec)
MariaDB [(none)]> create user lee@'%' identified by 'yy';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select User,Host from mysql.user;
+------+-----------+
| User | Host |
+------+-----------+
| lee | % |
| root | 127.0.0.1 |
| root | ::1 |
| lee | localhost |
| root | localhost |
+------+-----------+
5 rows in set (0.00 sec)
MariaDB [(none)]> quit
Bye
[root@test mysql]# mysql -ulee -pyy
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 39
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
[root@test mysql]# mysql -ulee -pyy -h localhost
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 40
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
[root@test mysql]# mysql -ulee -pyy -h 172.25.254.105
ERROR 2003 (HY000): Can't connect to MySQL server on '172.25.254.105' (111)
[root@test mysql]# vim /var/lib/mysql
[root@test mysql]# vim /etc/my.cnf
[root@test mysql]# cat /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=0
[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@test mysql]#
[root@test mysql]# mysql -ulee -pyy -h 172.25.254.105
ERROR 2003 (HY000): Can't connect to MySQL server on '172.25.254.105' (111)
[root@test mysql]# systemctl restart mariadb.service
[root@test mysql]# mysql -ulee -pyy -h 172.25.254.105
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)]> quit
Bye
[root@test mysql]# mysql -uroot -predhat
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)]> grant insert,update,delete,select on westos.* to lee@localhost
-> ;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> grant select on westos.* to lee@'%';Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select User,Host from mysql.user;
+------+-----------+
| User | Host |
+------+-----------+
| lee | % |
| root | 127.0.0.1 |
| root | ::1 |
| lee | localhost |
| root | localhost |
+------+-----------+
5 rows in set (0.00 sec)
MariaDB [(none)]> show grants from lee@'%'
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from lee@'%'' at line 1
MariaDB [(none)]> show grants for lee@'%';
+----------------------------------------------------------------------------------------------------+
| Grants for lee@% |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lee'@'%' IDENTIFIED BY PASSWORD '*F3F2E9AC1030645329A78A23EFDFA36DD39B668A' |
| GRANT SELECT ON `westos`.* TO 'lee'@'%' |
+----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]> show grants for lee@localhost;
+------------------------------------------------------------------------------------------------------------+
| Grants for lee@localhost |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lee'@'localhost' IDENTIFIED BY PASSWORD '*F3F2E9AC1030645329A78A23EFDFA36DD39B668A' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `westos`.* TO 'lee'@'localhost' |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]> quit
Bye
[root@test mysql]# mysql -ulee -pyy -h 172.25.254.105
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
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)]> 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]> select * from linux
-> ;
+----------+-------------------------------------------+-----+------+
| username | password | sex | age |
+----------+-------------------------------------------+-----+------+
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | NULL |
| user3 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | NULL |
+----------+-------------------------------------------+-----+------+
2 rows in set (0.00 sec)
MariaDB [westos]> creat table test;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'creat table test' at line 1
MariaDB [westos]> quit
Bye
[root@test mysql]# 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)]> drop user lee@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select User,Host from mysql.user;
+------+-----------+
| User | Host |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| lee | localhost |
| root | localhost |
+------+-----------+
4 rows in set (0.00 sec)
MariaDB [(none)]> quit
Bye
[root@test mysql]#
-------------------------------------------------------------------------
8.忘记密码
mysqladmin -uroot -predhat password lee ##修改超级用户密码
##当忘记超级用户密码
systemctl stop mariadb.service
mysqld_safe --skip-grant-tables & ##开启mysql登陆接口并忽略授权表
mysql ##直接登陆，不用密码
update mysql.user set Password=password('123') where User='root';##更新超级用户密码信息
fg ##察看是否有关于musql正在工作的
kill -9 mysql... ##杀死有关进程
ps aux | grep mysql ##查看mysql进程
kill -9 mysqlpid ##杀死进程
systemctl start mariadb ##重启数据库服务
mysql -uroot -p123 ##重新登陆测试
---------------------------------------------------------------------------------------
[root@test mysql]# mysqladmin -uroot -predhat password lee
[root@test mysql]# mysql -uroot =predhat
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@test mysql]# mysql -uroot =plee
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@test mysql]# mysql -uroot -predhat
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@test mysql]# mysql -uroot -plee
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
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
[root@test mysql]# mysqld_safe --skip-grant-tables &
[1] 5064
[root@test mysql]# 170513 01:50:24 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
170513 01:50:24 mysqld_safe A mysqld process already exists
[1]+ Exit 1 mysqld_safe --skip-grant-tables
[root@test mysql]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@test mysql]# systemctl stop mariadb.service
[root@test mysql]# mysqld_safe --skip-grants-tables &
[1] 5234
[root@test mysql]# 170513 01:52:00 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
170513 01:52:00 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
170513 01:52:03 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
[1]+ Done mysqld_safe --skip-grants-tables
[root@test mysql]# mysqld_safe --skip-grant-tables &
[1] 5419
[root@test mysql]# 170513 01:52:35 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
170513 01:52:35 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
[root@test mysql]#
[root@test mysql]# 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)]> quit
Bye
[root@test mysql]# ps aux | grep mysql
root 5419 0.0 0.1 113252 1560 pts/1 S 01:52 0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
mysql 5574 0.1 8.7 859068 87188 pts/1 Sl 01:52 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 5608 0.0 0.0 112640 936 pts/1 S+ 01:53 0:00 grep --color=auto mysql
[root@test mysql]# 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
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
MariaDB [(none)]> select User,Host from mysql.user
-> ;
+------+-----------+
| User | Host |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| lee | localhost |
| root | localhost |
+------+-----------+
4 rows in set (0.00 sec)
MariaDB [(none)]> select User,Host,Password from mysql.user
-> ;
+------+-----------+-------------------------------------------+
| User | Host | Password |
+------+-----------+-------------------------------------------+
| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | 127.0.0.1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | ::1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| lee | localhost | *F3F2E9AC1030645329A78A23EFDFA36DD39B668A |
+------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> quit
Bye
[root@test mysql]#
----------------------------------------------------------------------------------------
9.数据库的网页管理工具
先把上次的接口都关闭（kill掉）
yum install httpd php php-mysql.x86_64 -y ##安装这三个软件
systemctl start httpd
systemctl enable httpd
systemctl stop firewalld.service
systemctl disable firewalld.service
需要下载：
phpMyAdmin-3.4.0-all-languages.tar.bz2
tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2 -C /var/www/html
mv phpMyAdmin-3.4.0-all-languages mysqladmin
cd mysqladmin/
cp -p config.sample.inc.php config.inc.php
vim config.inc.php
17 $cfg['blowfish_secret'] = 'mysql'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
systemctl restart httpd
测试访问：http://172.25.254.144/mysqladmin (本机)
23 yum install httpd php php-mysql.x86_64 -y
24 systemctl start httpd
25 systemctl enable httpd
26 systemctl stop firewalld.service
27 systemctl disable firewalld.service
28 yum install lftp -y
29 lftp 172.25.254.250
30 ls
31 tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2 -C /var/www/html
32 cd /var/www/html/
33 ls
34 mv phpMyAdmin-3.4.0-all-languages mysqladmin
35 ls
36 cd mysqladmin/
37 cp -p config.sample.inc.php config.inc.php
38 vim config.inc.php
39 systemctl restart httpd
---------------------------------------------------------------------------------------------------
[root@test mysql]# yum install httpd php php-mysql.x86_64 -y
Loaded plugins: langpacks
Installed:
httpd.x86_64 0:2.4.6-17.el7 php.x86_64 0:5.4.16-21.el7 php-mysql.x86_64 0:5.4.16-21.el7
Dependency Installed:
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
libzip.x86_64 0:0.10.1-8.el7 mailcap.noarch 0:2.1.41-2.el7 php-cli.x86_64 0:5.4.16-21.el7
php-common.x86_64 0:5.4.16-21.el7 php-pdo.x86_64 0:5.4.16-21.el7
Complete!
[root@test mysql]# systemctl start httpd
[root@test mysql]# systemctl enable httpd
ln -s '/usr/lib/systemd/system/httpd.service' '/etc/systemd/system/multi-user.target.wants/httpd.service'
[root@test mysql]# systemctl stop firewalld.service
[root@test mysql]# systemctl disable firewalld.service
rm '/etc/systemd/system/basic.target.wants/firewalld.service'
rm '/etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service'
[root@test mysql]# yum install lftp -y
Loaded plugins: langpacks
Installed:
lftp.x86_64 0:4.4.8-3.el7
Complete!
[root@test mysql]# lftp 172.25.254.250
lftp 172.25.254.250:~> ls
drwxr-xr-x 2 0 0 4096 Mar 15 2016 CSA文档
drwxr-xr-x 3 0 0 41 Sep 21 2015 baidu
drwxr-xr-x 4 0 0 4096 Oct 02 2016 docs
drwxr-xr-x 18 0 0 4096 May 12 07:14 pub
lftp 172.25.254.250:/> cd pub
lftp 172.25.254.250:/pub> ls
-rw-r--r-- 1 0 0 12486177 Aug 25 2016 Discuz_X3.2_SC_UTF8.zip
drwxr-xr-x 7 0 0 73 Jun 24 2016 Enterprise
drwxr-xr-x 2 0 0 4096 May 12 03:25 RHCEPACKAGES
-rwxr-xr-x 1 1000 1000 103486240 Apr 28 2015 Student_2.7.13058.exe
drwxr-xr-x 10 0 0 105 Jun 12 2016 doc
drwxr-xr-x 13 1000 1000 4096 Apr 30 07:20 docs
drwxr-xr-x 3 1000 1000 4096 Dec 01 07:54 exam
-rwxr-xr-x 1 0 0 18928 Sep 17 2016 foundation-config-7.0-1.r26059.x86_64.rpm
-rwxr-xr-x 1 0 0 1053 Nov 03 2016 hostset
-rw-r--r-- 1 0 0 1079 Sep 22 2016 hostset.sh
drwxr-xr-x 2 0 0 4096 Apr 29 02:09 iso
drwxr-xr-x 2 0 0 53 Oct 31 2015 linuxmedia
drwxr-xr-x 3 0 0 18 Mar 01 2016 media
drwxr-xr-x 2 0 0 22 Apr 26 2016 python
-rw-r--r-- 1 1000 1000 116455 May 12 07:13 rhcsa考试说明.pdf
drwxr-xr-x 2 0 0 38 Nov 26 2015 rhel6
drwxr-xr-x 2 0 0 6 Sep 24 2015 rhel6.5
drwxr-xr-x 2 0 0 6 Nov 19 2015 rhel7.0
drwxr-xr-x 2 0 0 6 Jan 27 2016 rhel7.1
drwxr-xr-x 2 0 0 6 Jul 25 2016 rhel7.2
-rw-r--r-- 1 0 0 216 May 12 02:00 rht
drwxr-xr-x 2 0 0 4096 Nov 13 01:44 shellexample
drwxr-xr-x 4 0 0 4096 Apr 22 2016 software
-rw-r--r-- 1 0 0 397 Aug 25 2016 webapp.wsgi
-rwxr-xr-x 1 0 0 117 Sep 24 2015 x11vnc
-rw-r--r-- 1 0 0 85 Sep 13 2016 yum.repo
-rw-r--r-- 1 0 0 252 Nov 17 09:15 部署论坛
lftp 172.25.254.250:/pub> cd docs
lftp 172.25.254.250:/pub/docs> cd 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@test mysql]# ls
aria_log.00000001 ibdata1 ib_logfile1 mysql.sock phpMyAdmin-3.4.0-all-languages.tar.bz2
aria_log_control ib_logfile0 mysql performance_schema westos
[root@test mysql]# tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2 -C /var/www/html
[root@test mysql]# cd /var/www/html/
[root@test html]# ls
phpMyAdmin-3.4.0-all-languages
[root@test html]# mv phpMyAdmin-3.4.0-all-languages mysqladmin
[root@test html]# ls
mysqladmin
[root@test html]# cd mysqladmin/
[root@test mysqladmin]# cp -p config.sample.inc.php config.inc.php
[root@test mysqladmin]# vim config.inc.php
[root@test mysqladmin]# systemctl restart httpd
[root@test mysqladmin]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@test mysqladmin]# mysql -uroot -p123
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
[root@test mysqladmin]# systemctl restart mariadb.service
[root@test mysqladmin]# systemctl restart httpd
[root@test mysqladmin]# vim config.inc.php
[root@test mysqladmin]# vim config.inc.php
[root@test mysqladmin]# systemctl restart httpd
[root@test mysqladmin]# systemctl start httpd
[root@test mysqladmin]# systemctl enable httpd
[root@test mysqladmin]# systemctl stop firewalld
[root@test mysqladmin]# systemctl disable firewalld
[root@test mysqladmin]# systemctl restart httpd
[root@test mysqladmin]# vim config.inc.php
[root@test mysqladmin]# systemctl restart httpd
------------------------------------------------------------------------------------
数据库 完！！
[root@test ~]# history
46 vim /etc/yum.repos.d/rhel_dvd.repo ##配置yum源
47 yum install mariadb-server -y ##安装数据库
48 systemctl restart mariadb ##重启
49 mysql ##进入数据库（无密码可直接进入）
50 netstat -antlpe | grep mysql ##看数据库是否可以通过网络访问
51 vim /etc/my.cnf ##数据库的配置文件 添加 skip-networking=1
53 systemctl restart mariadb.service ##重启
54 netstat -antlpe | grep mysql ##再次看数据库是否可以通过网络访问
55 mysql ##进入数据库
57 mysql_secure_installation ##选择开启或关闭数据库的一些功能（设置密码等）
58 mysql ##进入数据库，此时出错
60 mysql -uroot -predhat ##这种方式不安全，会窃取到密码
61 mysql -uroot -p ##这种方式安全
62 history
--------------------------------------------------------------------------------------
[kiosk@foundation5 Desktop]$ ssh root@172.25.254.105
root@172.25.254.105's password:
Last login: Fri May 12 21:42:35 2017
[root@test ~]#
[root@test ~]# vim /etc/yum.repos.d/rhel_dvd.repo
[root@test ~]# yum install mariadb-server -y
Loaded plugins: langpacks
......
Complete!
[root@test ~]# systemctl restart mariadb
[root@test ~]# 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
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 database
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'database' at line 1
MariaDB [(none)]> show database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'database' at line 1
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 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]> quit
Bye
[root@test ~]# netstat -antlpe | grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 27 59026 2679/mysqld
[root@test ~]# vim /etc/my.cnf
[root@test ~]# netstat -antlpe | grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 27 59026 2679/mysqld
[root@test ~]# systemctl restart mariadb.service
[root@test ~]# netstat -antlpe | grep mysql
[root@test ~]# 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
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
[root@test ~]# my_secure_installation
bash: my_secure_installation: command not found...
[root@test ~]# 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
... Success!
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
... Success!
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...
... Success!
- Removing privileges on test database...
... Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] y
... Success!
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@test ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@test ~]# mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@test ~]# mysql -uroot -predhat
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
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
[root@test ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 14
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
[root@test ~]#
-----------------------------------------------------------------------------------
2.查询
MariaDB [(none)]> show databases; ##显示数据库
MariaDB [(none)]> use mysql; ##进入mysql库
MariaDB [mysql]> show tables; ##显示当前库中表的名称
MariaDB [mysql]> select * from user; ##查询表中所有内容（*可用此表中的任何字段代替）
MariaDB [mysql]> select Host,User,Password from user;##查询表中指定内容
MariaDB [mysql]> desc user; ##显示user表中的所有字段
3.数据库中表的建立
表在/var/lib/mysql下可以查到，可以在这里删除
MariaDB [mysql]> create database westos; ##创建westos库
MariaDB [westos]> create table linux( username varchar(15) not null, password varchar(50) not null);##创建linux表，并且linux表含有两个不能为空的字段username，password，字符长度最大分别为 15，50 个。
也可以这样写，方便检错：
MariaDB [westos]> create table linux(
-> username varchar(15) not null,
-> password varchar(50) not null);
MariaDB [westos]> insert into linux values ('user1','123');##向linux表中添加数据。
MariaDB [westos]> insert into linux values ('user2',password('123') );##加密密码
MariaDB [westos]> select * from linux;
+----------+-------------------------------------------+
| username | password |
+----------+-------------------------------------------+
| user1 | 123 |
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+----------+-------------------------------------------+
-------------------------------------------------------------------------------------
[root@test ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@test ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 18
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)]> 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 databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.01 sec)
MariaDB [mysql]> creat database westos;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'creat database westos' at line 1
MariaDB [mysql]> create database westos;
Query OK, 1 row affected (0.00 sec)
MariaDB [mysql]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| westos |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [mysql]> quit
Bye
[root@test ~]# cd /var/lib/mysql
[root@test mysql]# ls
aria_log.00000001 ibdata1 ib_logfile1 mysql.sock westos
aria_log_control ib_logfile0 mysql performance_schema
[root@test mysql]# rm -fr westos/
[root@test mysql]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@test mysql]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 20
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 |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]> create database westos;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| westos |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> use westos
Database changed
MariaDB [westos]> show tables;
Empty set (0.00 sec)
MariaDB [westos]> create table linux(
-> username varchar(15),not null,
-> password varchar(50) not null);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'not null,
password varchar(50) not null)' at line 2
MariaDB [westos]> create table linux( username varchar(15) not null, password varchar(50) not null);
Query OK, 0 rows affected (0.06 sec)
MariaDB [westos]> show tables;+------------------+
| Tables_in_westos |
+------------------+
| linux |
+------------------+
1 row in set (0.00 sec)
MariaDB [westos]> select * from linux
-> ;
Empty set (0.00 sec)
MariaDB [westos]> select * from linux;
Empty set (0.00 sec)
MariaDB [westos]> insert into linux values ('user1','123');
Query OK, 1 row affected (0.32 sec)
MariaDB [westos]> select * from linux;
+----------+----------+
| username | password |
+----------+----------+
| user1 | 123 |
+----------+----------+
1 row in set (0.00 sec)
MariaDB [westos]> insert into linux values ('user2',password('123') );
Query OK, 1 row affected (0.30 sec)
MariaDB [westos]> select * from linux;
+----------+-------------------------------------------+
| username | password |
+----------+-------------------------------------------+
| user1 | 123 |
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+----------+-------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [westos]> quit
Bye
[root@test mysql]#
---------------------------------------------------------------------------------------
4.更新数据库信息
update linux set password=password('passwd2') where username='user1';##更新user1密码（进行加密）
update linux set password=password('456') where ( username='user1' or username='user2' );
MariaDB [westos]> delete from linux where username='user1';##删除user1信息
MariaDB [westos]> alter table linux add age varchar(4);##添加age字段到表的最后一列
MariaDB [westos]> alter table linux add sex varchar(4) not null;##同上
MariaDB [westos]> alter table linux drop sex; ##删除sex字段
MariaDB [westos]> alter table linux add sex varchar(4) not null after password;##添加sex字段到password字段之后
------------------------------------------------------------------------------------
[root@test mysql]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 21
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)]> 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 databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| westos |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [mysql]> 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]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| westos |
+--------------------+
4 rows in set (0.00 sec)
MariaDB [westos]> select * from linux
-> ;
+----------+-------------------------------------------+
| username | password |
+----------+-------------------------------------------+
| user1 | 123 |
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+----------+-------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [westos]> insert into linux values ('user3','123');
Query OK, 1 row affected (0.02 sec)
MariaDB [westos]> select * from linux;
+----------+-------------------------------------------+
| username | password |
+----------+-------------------------------------------+
| user1 | 123 |
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| user3 | 123 |
+----------+-------------------------------------------+
3 rows in set (0.00 sec)
MariaDB [westos]> update linux set password=password('123') where passwd='123';
ERROR 1054 (42S22): Unknown column 'passwd' in 'where clause'
MariaDB [westos]> update linux set password=password('123') where password='123';
Query OK, 2 rows affected (0.33 sec)
Rows matched: 2 Changed: 2 Warnings: 0
MariaDB [westos]> select * from linux;
+----------+-------------------------------------------+
| username | password |
+----------+-------------------------------------------+
| user1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| user3 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+----------+-------------------------------------------+
3 rows in set (0.00 sec)
MariaDB [westos]> delete from linux where username=user1;
ERROR 1054 (42S22): Unknown column 'user1' in 'where clause'
MariaDB [westos]> delete from linux where username='user1';
Query OK, 1 row affected (0.32 sec)
MariaDB [westos]> alter table linux add age varchar(4);
Query OK, 2 rows affected (0.37 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [westos]> select * from linux;
+----------+-------------------------------------------+------+
| username | password | age |
+----------+-------------------------------------------+------+
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | NULL |
| user3 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | NULL |
+----------+-------------------------------------------+------+
2 rows in set (0.00 sec)
MariaDB [westos]> alter table linux add sex varchar(4) not null;
Query OK, 2 rows affected (0.11 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [westos]> select * from linux;
+----------+-------------------------------------------+------+-----+
| username | password | age | sex |
+----------+-------------------------------------------+------+-----+
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | NULL | |
| user3 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | NULL | |
+----------+-------------------------------------------+------+-----+
2 rows in set (0.00 sec)
MariaDB [westos]> alter table linux drop sex;
Query OK, 2 rows affected (0.48 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [westos]> select * from linux;
+----------+-------------------------------------------+------+
| username | password | age |
+----------+-------------------------------------------+------+
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | NULL |
| user3 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | NULL |
+----------+-------------------------------------------+------+
2 rows in set (0.00 sec)
MariaDB [westos]> alter table linux add sex varchar(4) not null after password;
Query OK, 2 rows affected (0.36 sec)
Records: 2 Duplicates: 0 Warnings: 0
MariaDB [westos]> select * from linux;
+----------+-------------------------------------------+-----+------+
| username | password | sex | age |
+----------+-------------------------------------------+-----+------+
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | NULL |
| user3 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | NULL |
+----------+-------------------------------------------+-----+------+
2 rows in set (0.00 sec)
MariaDB [westos]>
--------------------------------------------------------------------------------------
5.删除数据库
delete from linux where username='user1';
drop table linux;
drop database westos;
6.数据库的备份
mysqldump -u root -predhat --all-database ##备份所有表中的所有数据
mysqldump -u root -predhat --all-database --no-data##备份所有表，但不备份数据
mysqldump -u root -predhat westos ##备份westos库
mysqldump -u root -predhat westos > /mnt/westos.sql##备份westos库，并把数据保存到westos.sql中
mysqldump -u root -predhat westos linux > /mnt/linux.sql##备份westos库中的linux表
mysqldump -u root -predhat westos test > /mnt/test.sql##备份westos库中的test表
mysql -u root -predhat -e "create database westos;"##建立westos库
mysql -uroot -predhat westos < /mnt/westos.sql ##把数据导入westos库
mysql -uroot -predhat westos < /mnt/linux.sql ##把linux表数据导入westos库
7.用户授权
create user lee@localhost identified by 'lee'; ##建立用户lee，此用户只能通过本机登陆
create user lee@'%' identified by 'lee'; ##建立用户lee，此用户可以通过网络登陆
grant insert,update,delete,select on westos.test to lee@localhost;##用户授权
grant select on westos.* to lee@'%'; ##用户授权
show grants for lee@'%'; ##查看用户权力
show grants for lee@localhost; ##查看用户权力
revoke delete on westos.test from lee@localhost; ##去除用户授权的权力
drop user lee@'%'; ##删除用户
-------------------------------------------------------------------------
[root@test mysql]# mysql -uroot -predhat
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 38
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,Host from mysql.user;
+------+-----------+
| User | Host |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
+------+-----------+
3 rows in set (0.00 sec)
MariaDB [(none)]> create user lee@localhost identified by 'yy';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select User,Host from mysql.user;
+------+-----------+
| User | Host |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| lee | localhost |
| root | localhost |
+------+-----------+
4 rows in set (0.00 sec)
MariaDB [(none)]> create user lee@'%' identified by 'yy';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select User,Host from mysql.user;
+------+-----------+
| User | Host |
+------+-----------+
| lee | % |
| root | 127.0.0.1 |
| root | ::1 |
| lee | localhost |
| root | localhost |
+------+-----------+
5 rows in set (0.00 sec)
MariaDB [(none)]> quit
Bye
[root@test mysql]# mysql -ulee -pyy
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 39
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
[root@test mysql]# mysql -ulee -pyy -h localhost
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 40
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
[root@test mysql]# mysql -ulee -pyy -h 172.25.254.105
ERROR 2003 (HY000): Can't connect to MySQL server on '172.25.254.105' (111)
[root@test mysql]# vim /var/lib/mysql
[root@test mysql]# vim /etc/my.cnf
[root@test mysql]# cat /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=0
[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@test mysql]#
[root@test mysql]# mysql -ulee -pyy -h 172.25.254.105
ERROR 2003 (HY000): Can't connect to MySQL server on '172.25.254.105' (111)
[root@test mysql]# systemctl restart mariadb.service
[root@test mysql]# mysql -ulee -pyy -h 172.25.254.105
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)]> quit
Bye
[root@test mysql]# mysql -uroot -predhat
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)]> grant insert,update,delete,select on westos.* to lee@localhost
-> ;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> grant select on westos.* to lee@'%';Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select User,Host from mysql.user;
+------+-----------+
| User | Host |
+------+-----------+
| lee | % |
| root | 127.0.0.1 |
| root | ::1 |
| lee | localhost |
| root | localhost |
+------+-----------+
5 rows in set (0.00 sec)
MariaDB [(none)]> show grants from lee@'%'
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from lee@'%'' at line 1
MariaDB [(none)]> show grants for lee@'%';
+----------------------------------------------------------------------------------------------------+
| Grants for lee@% |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lee'@'%' IDENTIFIED BY PASSWORD '*F3F2E9AC1030645329A78A23EFDFA36DD39B668A' |
| GRANT SELECT ON `westos`.* TO 'lee'@'%' |
+----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]> show grants for lee@localhost;
+------------------------------------------------------------------------------------------------------------+
| Grants for lee@localhost |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lee'@'localhost' IDENTIFIED BY PASSWORD '*F3F2E9AC1030645329A78A23EFDFA36DD39B668A' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `westos`.* TO 'lee'@'localhost' |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]> quit
Bye
[root@test mysql]# mysql -ulee -pyy -h 172.25.254.105
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
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)]> 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]> select * from linux
-> ;
+----------+-------------------------------------------+-----+------+
| username | password | sex | age |
+----------+-------------------------------------------+-----+------+
| user2 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | NULL |
| user3 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | | NULL |
+----------+-------------------------------------------+-----+------+
2 rows in set (0.00 sec)
MariaDB [westos]> creat table test;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'creat table test' at line 1
MariaDB [westos]> quit
Bye
[root@test mysql]# 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)]> drop user lee@'%';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> select User,Host from mysql.user;
+------+-----------+
| User | Host |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| lee | localhost |
| root | localhost |
+------+-----------+
4 rows in set (0.00 sec)
MariaDB [(none)]> quit
Bye
[root@test mysql]#
-------------------------------------------------------------------------
8.忘记密码
mysqladmin -uroot -predhat password lee ##修改超级用户密码
##当忘记超级用户密码
systemctl stop mariadb.service
mysqld_safe --skip-grant-tables & ##开启mysql登陆接口并忽略授权表
mysql ##直接登陆，不用密码
update mysql.user set Password=password('123') where User='root';##更新超级用户密码信息
fg ##察看是否有关于musql正在工作的
kill -9 mysql... ##杀死有关进程
ps aux | grep mysql ##查看mysql进程
kill -9 mysqlpid ##杀死进程
systemctl start mariadb ##重启数据库服务
mysql -uroot -p123 ##重新登陆测试
---------------------------------------------------------------------------------------
[root@test mysql]# mysqladmin -uroot -predhat password lee
[root@test mysql]# mysql -uroot =predhat
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@test mysql]# mysql -uroot =plee
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@test mysql]# mysql -uroot -predhat
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@test mysql]# mysql -uroot -plee
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
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
[root@test mysql]# mysqld_safe --skip-grant-tables &
[1] 5064
[root@test mysql]# 170513 01:50:24 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
170513 01:50:24 mysqld_safe A mysqld process already exists
[1]+ Exit 1 mysqld_safe --skip-grant-tables
[root@test mysql]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@test mysql]# systemctl stop mariadb.service
[root@test mysql]# mysqld_safe --skip-grants-tables &
[1] 5234
[root@test mysql]# 170513 01:52:00 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
170513 01:52:00 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
170513 01:52:03 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended
[1]+ Done mysqld_safe --skip-grants-tables
[root@test mysql]# mysqld_safe --skip-grant-tables &
[1] 5419
[root@test mysql]# 170513 01:52:35 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
170513 01:52:35 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
[root@test mysql]#
[root@test mysql]# 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)]> quit
Bye
[root@test mysql]# ps aux | grep mysql
root 5419 0.0 0.1 113252 1560 pts/1 S 01:52 0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
mysql 5574 0.1 8.7 859068 87188 pts/1 Sl 01:52 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 5608 0.0 0.0 112640 936 pts/1 S+ 01:53 0:00 grep --color=auto mysql
[root@test mysql]# 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
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
MariaDB [(none)]> select User,Host from mysql.user
-> ;
+------+-----------+
| User | Host |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| lee | localhost |
| root | localhost |
+------+-----------+
4 rows in set (0.00 sec)
MariaDB [(none)]> select User,Host,Password from mysql.user
-> ;
+------+-----------+-------------------------------------------+
| User | Host | Password |
+------+-----------+-------------------------------------------+
| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | 127.0.0.1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | ::1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| lee | localhost | *F3F2E9AC1030645329A78A23EFDFA36DD39B668A |
+------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)
MariaDB [(none)]> quit
Bye
[root@test mysql]#
----------------------------------------------------------------------------------------
9.数据库的网页管理工具
先把上次的接口都关闭（kill掉）
yum install httpd php php-mysql.x86_64 -y ##安装这三个软件
systemctl start httpd
systemctl enable httpd
systemctl stop firewalld.service
systemctl disable firewalld.service
需要下载：
phpMyAdmin-3.4.0-all-languages.tar.bz2
tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2 -C /var/www/html
mv phpMyAdmin-3.4.0-all-languages mysqladmin
cd mysqladmin/
cp -p config.sample.inc.php config.inc.php
vim config.inc.php
17 $cfg['blowfish_secret'] = 'mysql'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
systemctl restart httpd
测试访问：http://172.25.254.144/mysqladmin (本机)
23 yum install httpd php php-mysql.x86_64 -y
24 systemctl start httpd
25 systemctl enable httpd
26 systemctl stop firewalld.service
27 systemctl disable firewalld.service
28 yum install lftp -y
29 lftp 172.25.254.250
30 ls
31 tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2 -C /var/www/html
32 cd /var/www/html/
33 ls
34 mv phpMyAdmin-3.4.0-all-languages mysqladmin
35 ls
36 cd mysqladmin/
37 cp -p config.sample.inc.php config.inc.php
38 vim config.inc.php
39 systemctl restart httpd
---------------------------------------------------------------------------------------------------
[root@test mysql]# yum install httpd php php-mysql.x86_64 -y
Loaded plugins: langpacks
Installed:
httpd.x86_64 0:2.4.6-17.el7 php.x86_64 0:5.4.16-21.el7 php-mysql.x86_64 0:5.4.16-21.el7
Dependency Installed:
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
libzip.x86_64 0:0.10.1-8.el7 mailcap.noarch 0:2.1.41-2.el7 php-cli.x86_64 0:5.4.16-21.el7
php-common.x86_64 0:5.4.16-21.el7 php-pdo.x86_64 0:5.4.16-21.el7
Complete!
[root@test mysql]# systemctl start httpd
[root@test mysql]# systemctl enable httpd
ln -s '/usr/lib/systemd/system/httpd.service' '/etc/systemd/system/multi-user.target.wants/httpd.service'
[root@test mysql]# systemctl stop firewalld.service
[root@test mysql]# systemctl disable firewalld.service
rm '/etc/systemd/system/basic.target.wants/firewalld.service'
rm '/etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service'
[root@test mysql]# yum install lftp -y
Loaded plugins: langpacks
Installed:
lftp.x86_64 0:4.4.8-3.el7
Complete!
[root@test mysql]# lftp 172.25.254.250
lftp 172.25.254.250:~> ls
drwxr-xr-x 2 0 0 4096 Mar 15 2016 CSA文档
drwxr-xr-x 3 0 0 41 Sep 21 2015 baidu
drwxr-xr-x 4 0 0 4096 Oct 02 2016 docs
drwxr-xr-x 18 0 0 4096 May 12 07:14 pub
lftp 172.25.254.250:/> cd pub
lftp 172.25.254.250:/pub> ls
-rw-r--r-- 1 0 0 12486177 Aug 25 2016 Discuz_X3.2_SC_UTF8.zip
drwxr-xr-x 7 0 0 73 Jun 24 2016 Enterprise
drwxr-xr-x 2 0 0 4096 May 12 03:25 RHCEPACKAGES
-rwxr-xr-x 1 1000 1000 103486240 Apr 28 2015 Student_2.7.13058.exe
drwxr-xr-x 10 0 0 105 Jun 12 2016 doc
drwxr-xr-x 13 1000 1000 4096 Apr 30 07:20 docs
drwxr-xr-x 3 1000 1000 4096 Dec 01 07:54 exam
-rwxr-xr-x 1 0 0 18928 Sep 17 2016 foundation-config-7.0-1.r26059.x86_64.rpm
-rwxr-xr-x 1 0 0 1053 Nov 03 2016 hostset
-rw-r--r-- 1 0 0 1079 Sep 22 2016 hostset.sh
drwxr-xr-x 2 0 0 4096 Apr 29 02:09 iso
drwxr-xr-x 2 0 0 53 Oct 31 2015 linuxmedia
drwxr-xr-x 3 0 0 18 Mar 01 2016 media
drwxr-xr-x 2 0 0 22 Apr 26 2016 python
-rw-r--r-- 1 1000 1000 116455 May 12 07:13 rhcsa考试说明.pdf
drwxr-xr-x 2 0 0 38 Nov 26 2015 rhel6
drwxr-xr-x 2 0 0 6 Sep 24 2015 rhel6.5
drwxr-xr-x 2 0 0 6 Nov 19 2015 rhel7.0
drwxr-xr-x 2 0 0 6 Jan 27 2016 rhel7.1
drwxr-xr-x 2 0 0 6 Jul 25 2016 rhel7.2
-rw-r--r-- 1 0 0 216 May 12 02:00 rht
drwxr-xr-x 2 0 0 4096 Nov 13 01:44 shellexample
drwxr-xr-x 4 0 0 4096 Apr 22 2016 software
-rw-r--r-- 1 0 0 397 Aug 25 2016 webapp.wsgi
-rwxr-xr-x 1 0 0 117 Sep 24 2015 x11vnc
-rw-r--r-- 1 0 0 85 Sep 13 2016 yum.repo
-rw-r--r-- 1 0 0 252 Nov 17 09:15 部署论坛
lftp 172.25.254.250:/pub> cd docs
lftp 172.25.254.250:/pub/docs> cd 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@test mysql]# ls
aria_log.00000001 ibdata1 ib_logfile1 mysql.sock phpMyAdmin-3.4.0-all-languages.tar.bz2
aria_log_control ib_logfile0 mysql performance_schema westos
[root@test mysql]# tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2 -C /var/www/html
[root@test mysql]# cd /var/www/html/
[root@test html]# ls
phpMyAdmin-3.4.0-all-languages
[root@test html]# mv phpMyAdmin-3.4.0-all-languages mysqladmin
[root@test html]# ls
mysqladmin
[root@test html]# cd mysqladmin/
[root@test mysqladmin]# cp -p config.sample.inc.php config.inc.php
[root@test mysqladmin]# vim config.inc.php
[root@test mysqladmin]# systemctl restart httpd
[root@test mysqladmin]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@test mysqladmin]# mysql -uroot -p123
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
[root@test mysqladmin]# systemctl restart mariadb.service
[root@test mysqladmin]# systemctl restart httpd
[root@test mysqladmin]# vim config.inc.php
[root@test mysqladmin]# vim config.inc.php
[root@test mysqladmin]# systemctl restart httpd
[root@test mysqladmin]# systemctl start httpd
[root@test mysqladmin]# systemctl enable httpd
[root@test mysqladmin]# systemctl stop firewalld
[root@test mysqladmin]# systemctl disable firewalld
[root@test mysqladmin]# systemctl restart httpd
[root@test mysqladmin]# vim config.inc.php
[root@test mysqladmin]# systemctl restart httpd
------------------------------------------------------------------------------------
数据库 完！！
阅读全文
0 0
