Mariadb :数据库管理系统,属于Mysql的一个分支,完全兼容mysql。
配置实验环境:将实验虚拟机重置
[root@localhost ~]# hostnamectl set-hostname mariadb.example.com ####修改主机名
-----------------------------mariadb的安装与使用------------------------
[root@mariadb ~]# yum clean all ####清除缓存的软件包
Loaded plugins: langpacks
Cleaning repos: rhel_dvd
Cleaning up everything
[root@mariadb ~]# yum install mariadb-server -y ####下载数据库软件
Loaded plugins: langpacks
rhel_dvd | 4.1 kB 00:00
(1/2): rhel_dvd/group_gz | 134 kB 00:00
(2/2): rhel_dvd/primary_db | 3.4 MB 00:00
Complete!
[root@mariadb ~]# systemctl start mariadb #####开启服务
[root@mariadb ~]# 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@mariadb ~]# netstat -antlpe | grep mysql ####查看mysql端口情况
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 27 76495 3536/mysqld
[root@mariadb ~]# vim /etc/my.cnf #####编写配置文件,关闭网络接口
[root@mariadb ~]# netstat -antlpe | grep mysql #####查看mysql端口,已经看不到开放的接口
[root@mariadb ~]# 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@mariadb ~]# 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 #####设置root用户密码
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@mariadb ~]# mysql #####安全初始化之后,匿名登录失败
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
--------------数据库的基本sql语句操作------------
1.数据库的登录
[root@mariadb ~]# mysql -uroot -p ####-u表示指定登录用户,root用户登录
Enter password: #####输入root用户密码
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
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.
2.数据库查询
MariaDB [(none)]> show databases; #####显示数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]> use mysql #####进入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]> desc user; ######查询user表的结构(显示所有字段的名称)
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| 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 | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| 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 | |
| plugin | char(64) | NO | | | |
| authentication_string | text | NO | | NULL | |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.01 sec)
MariaDB [mysql]> select HOST from user; #####查询user表中的HOST字段内容
+-----------+
| HOST |
+-----------+
| 127.0.0.1 |
| ::1 |
| localhost |
+-----------+
MariaDB [mysql]> select * from user; #####查询user表中所有内容(*可以用此表中的任何字段来代替)
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| 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 | | |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
3 rows in set (0.00 sec)
3.数据库及表的建立
MariaDB [(none)]> create database westos; ####创建westos数据库
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases; #####显示数据库,创建westos库成功
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| westos |
+--------------------+
MariaDB [(none)]> use westos ####进入westos库
Database changed
MariaDB [westos]> create table linux( #####创建linux表,表含有两个字段,username,password
-> username varchar(15) not null, -> password varchar(15) not null);Query OK, 0 rows affected (0.08 sec)
MariaDB [westos]> insert into linux values ('user1','123'); ####向linux表中插入数据,username的字段的数据为user1,password字段的数据为123
Query OK, 1 row affected (0.03 sec)
MariaDB [westos]> insert into linux values ('user2',password('123'));######向linux表中插入数据,username的字段的数据为user2,插入password字段是用passwd加密
Query OK, 1 row affected, 1 warning (0.08 sec)
MariaDB [westos]> select * from linux; #####查询linux表中内容
+----------+-----------------+
| username | password |
+----------+-----------------+
| user1 | 123 |
| user2 | *23AE809DDACAF9 |
+----------+-----------------+
4.更新数据库信息
MariaDB [westos]> update linux set password=password('123') where username='user1'; #####更新user1的密码
Query OK, 1 row affected, 1 warning (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 1
MariaDB [westos]> update linux set password=password('123') where ( username='user1' or username='user2' );
####更新user1和user2的密码
MariaDB [westos]> select * from linux; #####查询linux表中内容
+----------+-----------------+
| username | password |
+----------+-----------------+
| user1 | *23AE809DDACAF9 |
| user2 | *23AE809DDACAF9 |
+----------+-----------------+
MariaDB [westos]> delete from linux where username='user1'; #####删除user1信息
Query OK, 1 row affected (0.36 sec)
MariaDB [westos]> alter table linux add date varchar(20) not null; ####添加date字段到linux表中且不为空
Query OK, 1 row affected (0.08 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [westos]> select * from linux;
+----------+-----------------+------+
| username | password | date |
+----------+-----------------+------+
| user2 | *23AE809DDACAF9 | |
+----------+-----------------+------+
1 row in set (0.00 sec)
MariaDB [westos]> alter table linux add class varchar(20) not null after password; ####添加class字段到linux表中且不为空,添加到password字段后
Query OK, 1 row affected (0.41 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [westos]> select * from linux;
+----------+-----------------+-------+------+
| username | password | class | date |
+----------+-----------------+-------+------+
| user2 | *23AE809DDACAF9 | | |
5.删除数据库
delete from linux where username='user1'; ####删除user1的数据从inux表中
drop table linux; ####删除linux表
[root@mariadb mysql]mysqldump -uroot -pwestos --all-database ###备份所有表中的所有数据
[root@mariadb mysql]mysqldump -uroot -pwestos --all-database --no-data ###备份所有表,但不备份数据
[root@mariadb mysql]mysqldump -uroot -pwestos westos ####备份westos库
[root@mariadb mysql]mysqldump -uroot -pwestos westos > /mnt/westos.sql ####备份westos库并把数据保存到westos.sql中
[root@mariadb mysql]mysqldump -uroot -pwestos westos linux > /mnt/linux.sql ####备份westos库中的linux表
[root@mariadb mysql]mysqldump -uroot -pwestos westos test > /mnt/test.sql #####备份westos库中的test表
[root@mariadb mysql]mysqldump -uroot -pwestos -e "create database westos;" ####建立westos库
7.用户授权
[root@mariadb mnt]# mysql -uroot -pwestos #####root用户登录
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 56
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 from mysql.user; #####查看User
+------+
| User |
+------+
| root |
| root |
| root |
+------+
3 rows in set (0.00 sec)
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 'lee'; #####建立用户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)]> create user lee@'%' identified by 'lee'; ###建立用户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)]> quit
[root@mariadb mnt]# mysql -ulee -plee -h 172.25.254.142 ####本地ip地址登录失败
ERROR 2003 (HY000): Can't connect to MySQL server on '172.25.254.142' (111)
[root@mariadb mnt]# vim /etc/my.cnf ###编辑配置文件
skip-networking=0 ####不进行网络检查
[root@mariadb mnt]# systemctl restart mariadb ####重新启动数据库服务
[root@mariadb mnt]# mysql -ulee -plee -h 172.25.254.142 #####再次登录成功
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 databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> quit
[root@mariadb mnt]# mysql -uroot -pwestos #####root用户登录,进行用户授权
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)]> show grants for lee@'%'; ####查看用户授权
+----------------------------------------------------------------------------------------------------+
| Grants for lee@% |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lee'@'%' IDENTIFIED BY PASSWORD '*9BB439A3A652A9DAD3718215F77A7AA06108A267' |
| 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 '*9BB439A3A652A9DAD3718215F77A7AA06108A267' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `westos`.* TO 'lee'@'localhost' |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]> revoke delete on westos.* from lee@localhost; ####去除用户delete权力
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show grants for lee@localhost; ####查看用户授权
+------------------------------------------------------------------------------------------------------------+
| Grants for lee@localhost |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lee'@'localhost' IDENTIFIED BY PASSWORD '*9BB439A3A652A9DAD3718215F77A7AA06108A267' |
| GRANT SELECT, INSERT, UPDATE ON `westos`.* TO 'lee'@'localhost' |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]> drop user lee@'%' #####删除用户
MariaDB [(none)]> quit
8.密码修改
[root@mariadb mnt]# systemctl stop mariadb ####关闭mysql
[root@mariadb mnt]# mysqld_safe --skip-grant-tables & ####开启mysql登录接口并忽略授权表
[1] 2189
[root@mariadb mnt]# 170513 01:49:03 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
170513 01:49:03 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
[root@mariadb mnt]# 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)]> 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,Password from mysql.user; ####查看表内容
+------+-----------+-------------------------------------------+
| User | Host | Password |
+------+-----------+-------------------------------------------+
| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | 127.0.0.1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | ::1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| lee | % | *9BB439A3A652A9DAD3718215F77A7AA06108A267 |
| lee | localhost | *9BB439A3A652A9DAD3718215F77A7AA06108A267 |
+------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> quit
Bye
[root@mariadb mnt]# fg
mysqld_safe --skip-grant-tables
^Z
[1]+ Stopped mysqld_safe --skip-grant-tables
[root@mariadb mnt]# killall -9 mysqld_safe
[1]+ Killed mysqld_safe --skip-grant-tables
[root@mariadb mnt]# ps aux | grep mysql #####过滤mysql的所有进程并结束这些进程
mysql 2351 0.0 9.0 859068 89732 pts/1 Sl 01:49 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 2413 0.0 0.0 112640 936 pts/1 R+ 01:53 0:00 grep --color=auto mysql
[root@mariadb mnt]# kill -9 2351 ####结束mysql有关进程
[root@mariadb mnt]# ps aux | grep mysql
root 2433 0.0 0.0 112640 932 pts/1 R+ 01:54 0:00 grep --color=auto mysql
[root@mariadb mnt]# systemctl start mariadb ######重新开启mysql
[root@mariadb mnt]# mysql -uroot -p123 #####登录测试,密码修改成功
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
------------------------数据库的网页管理工具------------------------
1.软件安装
[root@mariadb mnt]# yum install httpd php php-mysql -y ####下载httpd服务,php,php-mysql辅助连接到数据库
loaded plugins: langpacks
Resolving Dependencies
--> Running transaction check
---> 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
Complete!
[root@mariadb mnt]# systemctl start httpd ####开启httpd服务
[root@mariadb mnt]# systemctl enable httpd ####开机自启
ln -s '/usr/lib/systemd/system/httpd.service' '/etc/systemd/system/multi-user.target.wants/httpd.service'
[root@mariadb mnt]# systemctl stop firewalld ####关闭防火墙
[root@mariadb mnt]# systemctl disable firewalld #####设置防火墙开机不启动
rm '/etc/systemd/system/basic.target.wants/firewalld.service'
rm '/etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service'
[root@mariadb mnt]# yum install lftp #####下载lftp服务
Loaded plugins: langpacks
Resolving Dependencies
--> Running transaction check
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@mariadb mnt]# ls
linux.sql phpMyAdmin-3.4.0-all-languages.tar.bz2 westos.sql
[root@mariadb mnt]# tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2 -C /var/www/html/ ####解压php-mysql安装包到/var/www/html下
[root@mariadb mnt]# ls
linux.sql phpMyAdmin-3.4.0-all-languages.tar.bz2 westos.sql
[root@mariadb mnt]# cd /var/www/html/
[root@mariadb html]# ls
phpMyAdmin-3.4.0-all-languages
[root@mariadb html]# mv phpMyAdmin-3.4.0-all-languages/ mysqladmin ####文件重命名
[root@mariadb html]# ll
total 4
drwxr-xr-x. 10 root root 4096 May 11 2011 mysqladmin
[root@mariadb html]# cd mysqladmin
[root@mariadb mysqladmin]# cp -p config.sample.inc.php config.inc.php ####复制文件模板
[root@mariadb mysqladmin]# vim config.inc.php #####编辑配置文件
17 $cfg['blowfish_secret']= 'mysql'; /* YOU MUST FILL IN TIS FOR COOKIE AUTH! */
[root@mariadb mysqladmin]# systemctl restart httpd #####重启服务测试
测试:
配置实验环境:将实验虚拟机重置
[root@localhost ~]# vim /etc/sysconfig/network-scripts/ifcfg-eth0 ####配置网络
[root@localhost ~]# vim /etc/yum.repos.d/rhel_dvd.repo ###修改yum源指向
[root@localhost ~]# hostnamectl set-hostname mariadb.example.com ####修改主机名
-----------------------------mariadb的安装与使用------------------------
[root@mariadb ~]# yum clean all ####清除缓存的软件包
Loaded plugins: langpacks
Cleaning repos: rhel_dvd
Cleaning up everything
[root@mariadb ~]# yum install mariadb-server -y ####下载数据库软件
Loaded plugins: langpacks
rhel_dvd | 4.1 kB 00:00
(1/2): rhel_dvd/group_gz | 134 kB 00:00
(2/2): rhel_dvd/primary_db | 3.4 MB 00:00
Complete!
[root@mariadb ~]# systemctl start mariadb #####开启服务
[root@mariadb ~]# 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@mariadb ~]# netstat -antlpe | grep mysql ####查看mysql端口情况
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 27 76495 3536/mysqld
[root@mariadb ~]# vim /etc/my.cnf #####编写配置文件,关闭网络接口
skip-networking=1
[root@mariadb ~]# netstat -antlpe | grep mysql #####查看mysql端口,已经看不到开放的接口
[root@mariadb ~]# 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@mariadb ~]# 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 #####设置root用户密码
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@mariadb ~]# mysql #####安全初始化之后,匿名登录失败
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
--------------数据库的基本sql语句操作------------
1.数据库的登录
[root@mariadb ~]# mysql -uroot -p ####-u表示指定登录用户,root用户登录
Enter password: #####输入root用户密码
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
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.
2.数据库查询
MariaDB [(none)]> show databases; #####显示数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
MariaDB [(none)]> use mysql #####进入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]> desc user; ######查询user表的结构(显示所有字段的名称)
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| 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 | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| 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 | |
| plugin | char(64) | NO | | | |
| authentication_string | text | NO | | NULL | |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.01 sec)
MariaDB [mysql]> select HOST from user; #####查询user表中的HOST字段内容
+-----------+
| HOST |
+-----------+
| 127.0.0.1 |
| ::1 |
| localhost |
+-----------+
MariaDB [mysql]> select * from user; #####查询user表中所有内容(*可以用此表中的任何字段来代替)
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| 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 | | |
+-----------+------+-------------------------------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
3 rows in set (0.00 sec)
3.数据库及表的建立
MariaDB [(none)]> create database westos; ####创建westos数据库
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> show databases; #####显示数据库,创建westos库成功
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| westos |
+--------------------+
MariaDB [(none)]> use westos ####进入westos库
Database changed
MariaDB [westos]> create table linux( #####创建linux表,表含有两个字段,username,password
-> username varchar(15) not null, -> password varchar(15) not null);Query OK, 0 rows affected (0.08 sec)
MariaDB [westos]> insert into linux values ('user1','123'); ####向linux表中插入数据,username的字段的数据为user1,password字段的数据为123
Query OK, 1 row affected (0.03 sec)
MariaDB [westos]> insert into linux values ('user2',password('123'));######向linux表中插入数据,username的字段的数据为user2,插入password字段是用passwd加密
Query OK, 1 row affected, 1 warning (0.08 sec)
MariaDB [westos]> select * from linux; #####查询linux表中内容
+----------+-----------------+
| username | password |
+----------+-----------------+
| user1 | 123 |
| user2 | *23AE809DDACAF9 |
+----------+-----------------+
2 rows in set (0.00 sec)
4.更新数据库信息
MariaDB [westos]> update linux set password=password('123') where username='user1'; #####更新user1的密码
Query OK, 1 row affected, 1 warning (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 1
MariaDB [westos]> update linux set password=password('123') where ( username='user1' or username='user2' );
####更新user1和user2的密码
MariaDB [westos]> select * from linux; #####查询linux表中内容
+----------+-----------------+
| username | password |
+----------+-----------------+
| user1 | *23AE809DDACAF9 |
| user2 | *23AE809DDACAF9 |
+----------+-----------------+
MariaDB [westos]> delete from linux where username='user1'; #####删除user1信息
Query OK, 1 row affected (0.36 sec)
MariaDB [westos]> alter table linux add date varchar(20) not null; ####添加date字段到linux表中且不为空
Query OK, 1 row affected (0.08 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [westos]> select * from linux;
+----------+-----------------+------+
| username | password | date |
+----------+-----------------+------+
| user2 | *23AE809DDACAF9 | |
+----------+-----------------+------+
1 row in set (0.00 sec)
MariaDB [westos]> alter table linux add class varchar(20) not null after password; ####添加class字段到linux表中且不为空,添加到password字段后
Query OK, 1 row affected (0.41 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [westos]> select * from linux;
+----------+-----------------+-------+------+
| username | password | class | date |
+----------+-----------------+-------+------+
| user2 | *23AE809DDACAF9 | | |
+----------+-----------------+-------+------+
5.删除数据库
delete from linux where username='user1'; ####删除user1的数据从inux表中
drop table linux; ####删除linux表
drop database westos; #####删除westos库
[root@mariadb mysql]mysqldump -uroot -pwestos --all-database ###备份所有表中的所有数据
[root@mariadb mysql]mysqldump -uroot -pwestos --all-database --no-data ###备份所有表,但不备份数据
[root@mariadb mysql]mysqldump -uroot -pwestos westos ####备份westos库
[root@mariadb mysql]mysqldump -uroot -pwestos westos > /mnt/westos.sql ####备份westos库并把数据保存到westos.sql中
[root@mariadb mysql]mysqldump -uroot -pwestos westos linux > /mnt/linux.sql ####备份westos库中的linux表
[root@mariadb mysql]mysqldump -uroot -pwestos westos test > /mnt/test.sql #####备份westos库中的test表
[root@mariadb mysql]mysqldump -uroot -pwestos -e "create database westos;" ####建立westos库
[root@mariadb mysql]mysqldump -uroot -pwestos westos < /mnt/westos.sql ####把数据导入westos库
先要建立数据库及其表信息:
root用户登录
进行数据库信息的备份:
7.用户授权
[root@mariadb mnt]# mysql -uroot -pwestos #####root用户登录
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 56
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 from mysql.user; #####查看User
+------+
| User |
+------+
| root |
| root |
| root |
+------+
3 rows in set (0.00 sec)
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 'lee'; #####建立用户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)]> create user lee@'%' identified by 'lee'; ###建立用户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)]> quit
[root@mariadb mnt]# mysql -ulee -plee -h 172.25.254.142 ####本地ip地址登录失败
ERROR 2003 (HY000): Can't connect to MySQL server on '172.25.254.142' (111)
[root@mariadb mnt]# vim /etc/my.cnf ###编辑配置文件
skip-networking=0 ####不进行网络检查
[root@mariadb mnt]# systemctl restart mariadb ####重新启动数据库服务
[root@mariadb mnt]# mysql -ulee -plee -h 172.25.254.142 #####再次登录成功
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 databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> quit
[root@mariadb mnt]# mysql -uroot -pwestos #####root用户登录,进行用户授权
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)]> show grants for lee@'%'; ####查看用户授权
+----------------------------------------------------------------------------------------------------+
| Grants for lee@% |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lee'@'%' IDENTIFIED BY PASSWORD '*9BB439A3A652A9DAD3718215F77A7AA06108A267' |
| 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 '*9BB439A3A652A9DAD3718215F77A7AA06108A267' |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `westos`.* TO 'lee'@'localhost' |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]> revoke delete on westos.* from lee@localhost; ####去除用户delete权力
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show grants for lee@localhost; ####查看用户授权
+------------------------------------------------------------------------------------------------------------+
| Grants for lee@localhost |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lee'@'localhost' IDENTIFIED BY PASSWORD '*9BB439A3A652A9DAD3718215F77A7AA06108A267' |
| GRANT SELECT, INSERT, UPDATE ON `westos`.* TO 'lee'@'localhost' |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [(none)]> drop user lee@'%' #####删除用户
MariaDB [(none)]> quit
8.密码修改
mysqladmin -uroot -pwestos password lee #####修改超级用户密码为lee
[root@mariadb mnt]# systemctl stop mariadb ####关闭mysql
[root@mariadb mnt]# mysqld_safe --skip-grant-tables & ####开启mysql登录接口并忽略授权表
[1] 2189
[root@mariadb mnt]# 170513 01:49:03 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
170513 01:49:03 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
[root@mariadb mnt]# 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)]> 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,Password from mysql.user; ####查看表内容
+------+-----------+-------------------------------------------+
| User | Host | Password |
+------+-----------+-------------------------------------------+
| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | 127.0.0.1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| root | ::1 | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| lee | % | *9BB439A3A652A9DAD3718215F77A7AA06108A267 |
| lee | localhost | *9BB439A3A652A9DAD3718215F77A7AA06108A267 |
+------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> quit
Bye
[root@mariadb mnt]# fg
mysqld_safe --skip-grant-tables
^Z
[1]+ Stopped mysqld_safe --skip-grant-tables
[root@mariadb mnt]# killall -9 mysqld_safe
[1]+ Killed mysqld_safe --skip-grant-tables
[root@mariadb mnt]# ps aux | grep mysql #####过滤mysql的所有进程并结束这些进程
mysql 2351 0.0 9.0 859068 89732 pts/1 Sl 01:49 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 2413 0.0 0.0 112640 936 pts/1 R+ 01:53 0:00 grep --color=auto mysql
[root@mariadb mnt]# kill -9 2351 ####结束mysql有关进程
[root@mariadb mnt]# ps aux | grep mysql
root 2433 0.0 0.0 112640 932 pts/1 R+ 01:54 0:00 grep --color=auto mysql
[root@mariadb mnt]# systemctl start mariadb ######重新开启mysql
[root@mariadb mnt]# mysql -uroot -p123 #####登录测试,密码修改成功
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
------------------------数据库的网页管理工具------------------------
1.软件安装
[root@mariadb mnt]# yum install httpd php php-mysql -y ####下载httpd服务,php,php-mysql辅助连接到数据库
loaded plugins: langpacks
Resolving Dependencies
--> Running transaction check
---> 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
Complete!
[root@mariadb mnt]# systemctl start httpd ####开启httpd服务
[root@mariadb mnt]# systemctl enable httpd ####开机自启
ln -s '/usr/lib/systemd/system/httpd.service' '/etc/systemd/system/multi-user.target.wants/httpd.service'
[root@mariadb mnt]# systemctl stop firewalld ####关闭防火墙
[root@mariadb mnt]# systemctl disable firewalld #####设置防火墙开机不启动
rm '/etc/systemd/system/basic.target.wants/firewalld.service'
rm '/etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service'
[root@mariadb mnt]# yum install lftp #####下载lftp服务
Loaded plugins: langpacks
Resolving Dependencies
--> Running transaction check
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@mariadb mnt]# ls
linux.sql phpMyAdmin-3.4.0-all-languages.tar.bz2 westos.sql
[root@mariadb mnt]# tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2 -C /var/www/html/ ####解压php-mysql安装包到/var/www/html下
[root@mariadb mnt]# ls
linux.sql phpMyAdmin-3.4.0-all-languages.tar.bz2 westos.sql
[root@mariadb mnt]# cd /var/www/html/
[root@mariadb html]# ls
phpMyAdmin-3.4.0-all-languages
[root@mariadb html]# mv phpMyAdmin-3.4.0-all-languages/ mysqladmin ####文件重命名
[root@mariadb html]# ll
total 4
drwxr-xr-x. 10 root root 4096 May 11 2011 mysqladmin
[root@mariadb html]# cd mysqladmin
[root@mariadb mysqladmin]# cp -p config.sample.inc.php config.inc.php ####复制文件模板
[root@mariadb mysqladmin]# vim config.inc.php #####编辑配置文件
17 $cfg['blowfish_secret']= 'mysql'; /* YOU MUST FILL IN TIS FOR COOKIE AUTH! */
[root@mariadb mysqladmin]# systemctl restart httpd #####重启服务测试
测试:
访问http://172.25.254.159/mysqladmin