MariaDB数据库的安装与配置
一、安装
yum install mariadb-server -y
##安装
systemctl start mariadb
##开启服务
二、基本配置
一)安全初始化
1、默认情况下,数据库端口是打开的,其他主机可以通过打开的端口来进行访问,所以先将端口关闭
[root@station mysql]# netstat -antlpe | grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 27 39838 3913/mysqld
[root@station mysql]# vim /etc/my.cnf
skip-networking=1 ##添加这一行,关闭网络端口
[root@station mysql]# systemctl restart mariadb
[root@station mysql]# netstat -antlpe | grep mysql ##此时不会有任何返回值,端口已经关闭
2、数据库的设定初始状态是不安全的,所以需要设定密码
mysQl_secure_installation
##这条命令为数据库设置密码
二)密码设置
1、更改密码
mysqladmin -uroot -plcl970618 password lcl
#把原来的密码修改为lcl
2、当超级用户密码忘记,不能登陆数据库时
[root@station ~]# systemctl stop mariadb ##先关掉数据库
[root@station ~]# mysqld_safe --skip-grant-tables & ##跳过数据库密码验证阶段
[1] 4815
[root@station ~]# 171123 05:43:05 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
171123 05:43:05 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
[root@station ~]# mysql ##此时进入数据库不需要密码
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.44-MariaDB MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation 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('0618') WHERE User='root'; ##更新User为root的密码为0618
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
MariaDB [(none)]> quit;
Bye
[root@station ~]# ps aux|grep mysql ##列出数据库的所有进程
root 4815 0.0 0.1 113252 1620 pts/0 S 05:43 0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
mysql 4970 0.0 8.2 843504 84256 pts/0 Sl 05:43 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 5012 0.0 0.0 112644 956 pts/0 R+ 05:44 0:00 grep --color=auto mysql
[root@station ~]# kill -9 4815 ##关闭数据库的所有进程
[root@station ~]# kill -9 4970
[1]+ Killed mysqld_safe --skip-grant-tables
[root@station ~]# systemctl start mariadb ##开启数据库
[root@station ~]# mysql -uroot -p ##此时进入数据库需要的密码是刚才更新的密码
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.44-MariaDB MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> ##成功进入
三)数据库的管理
mysql交互模式下的数据库、表都在/var/lib/mysql/目录里面,其中里面的目录是库,库目录里面的文件是表的名称。
1、建立、查询
[root@station ~]# mysql -uroot -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.44-MariaDB MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation 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 lcl; ##创建名为lcl的库
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> USE lcl;
Database changed
MariaDB [lcl]> CREATE TABLE user ( ##创建名为user的表
-> username varchar(50) not null, ##创建字段username,字符长最大为50,不能为空
-> password varchar(50) not null ##创建password字段
-> );
Query OK, 0 rows affected (0.07 sec)
MariaDB [lcl]> DESC user; ##列出表结构
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(50) | NO | | NULL | |
| password | varchar(50) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
MariaDB [lcl]> INSERT INTO user VALUES ('alice','123'); ##插入数据
Query OK, 1 row affected (0.03 sec)
MariaDB [lcl]> SELECT * FROM user; ##列出user表中的所有数据
+----------+----------+
| username | password |
+----------+----------+
| alice | 123 |
+----------+----------+
1 row in set (0.00 sec)
MariaDB [lcl]> SELECT username FROM user ##列出user表中的username字段的数据
-> ;
+----------+
| username |
+----------+
| alice |
+----------+
1 row in set (0.00 sec)
2、更改
MariaDB [lcl]> UPDATE user SET password=password('alice') WHERE username='alice'; ##将alice的密码改为加密后的‘alice’,等号后面的password表示加密
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [lcl]> SELECT * FROM user;
+----------+-------------------------------------------+
| username | password |
+----------+-------------------------------------------+
| alice | *4F5CCA657BD61D1C1127E5C4EA3B0EE4A9841B85 |
+----------+-------------------------------------------+
1 row in set (0.00 sec)
MariaDB [lcl]> ALTER TABLE user ADD class varchar(20); ##添加class字段,最大字符为20,若没有指定位置,则默认会添加在表的字段末尾
Query OK, 1 row affected (0.41 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [lcl]> SELECT * FROM user;
+----------+-------------------------------------------+-------+
| username | password | class |
+----------+-------------------------------------------+-------+
| alice | *4F5CCA657BD61D1C1127E5C4EA3B0EE4A9841B85 | NULL |
+----------+-------------------------------------------+-------+
1 row in set (0.00 sec)
MariaDB [lcl]> ALTER TABLE user ADD age varchar(10) AFTER username; ##指定将age字段添加在username字段后面,默认只能添加在某个字段之后,而不能用before
Query OK, 1 row affected (0.16 sec)
Records: 1 Duplicates: 0 Warnings: 0
MariaDB [lcl]> SELECT * FROM user;
+----------+------+-------------------------------------------+-------+
| username | age | password | class |
+----------+------+-------------------------------------------+-------+
| alice | NULL | *4F5CCA657BD61D1C1127E5C4EA3B0EE4A9841B85 | NULL |
+----------+------+-------------------------------------------+-------+
1 row in set (0.00 sec)
MariaDB [lcl]> UPDATE user SET password='123' WHERE username='alice';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MariaDB [lcl]> SELECT * FROM user; ##密码没有经过加密
+----------+------+----------+-------+
| username | age | password | class |
+----------+------+----------+-------+
| alice | NULL | 123 | NULL |
+----------+------+----------+-------+
1 row in set (0.00 sec)
MariaDB [lcl]> ALTER TABLE user RENAME redhat; ##更改表的名字为redhat
Query OK, 0 rows affected (0.04 sec)
MariaDB [lcl]> SELECT * FROM redhat;
+----------+------+----------+-------+
| username | age | password | class |
+----------+------+----------+-------+
| alice | NULL | 123 | NULL |
+----------+------+----------+-------+
1 row in set (0.00 sec)
3、删除
MariaDB [lcl]> DELETE FROM redhat WHERE username='alice'; ##删除alice数据
Query OK, 1 row affected (0.02 sec)
MariaDB [lcl]> SELECT * FROM redhat;
Empty set (0.00 sec)
MariaDB [lcl]> DROP TABLE redhat; ##删除表redhat
Query OK, 0 rows affected (0.03 sec)
MariaDB [lcl]> DROP DATABASE lcl; ##删除库lcl
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> SHOW DATABASES; ##可以看出已经删除
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
4、用户的授权
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]> CREATE user lcl@localhost identified by 'redhat'; ##创建lcl用户,并且设置密码为redhat,@localhost表示只能在本地登陆
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> GRANT SELECT,INSERT on lcl.* to lcl@localhost; ##授予lcl用户SELECT,INSERT权限
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> SHOW GRANTS for lcl@localhost; ##列出lcl用户的权限,和设置的相符合
+------------------------------------------------------------------------------------------------------------+
| Grants for lcl@localhost |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lcl'@'localhost' IDENTIFIED BY PASSWORD '*84BB5DF4823DA319BBF86C99624479A198E6EEE9' |
| GRANT SELECT, INSERT ON `lcl`.* TO 'lcl'@'localhost' |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [mysql]> REVOKE INSERT on lcl.* from lcl@localhost; ##去掉lcl用户的insert权限
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> SHOW GRANTS for lcl@localhost; ##lcl用户此时只剩下SELECT权限
+------------------------------------------------------------------------------------------------------------+
| Grants for lcl@localhost |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lcl'@'localhost' IDENTIFIED BY PASSWORD '*84BB5DF4823DA319BBF86C99624479A198E6EEE9' |
| GRANT SELECT ON `lcl`.* TO 'lcl'@'localhost' |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
5、数据库的备份与恢复
备份
[root@station ~]# mysqldump -uroot -p0618 lcl > /mnt/redhat.sql
#将lcl库备份到/mnt/redhat.sql文件中
mysqldump -uroot -plcl redhat –no-data ##将redhat库的框架进行备份,不备份数据
mysqldump -uroot -plcl –all-database ##将所有数据库的内容和框架进行备份
mysqldump -uroot -plcl –all-database –no-data ##将所有数据库的框架进行备份,不备份数据
恢复
方法一:
[root@station ~]# mysql -uroot -p0618 -e "CREATE DATABASE redhat;" ##进入数据库并创建redhat库
[root@station ~]# mysql -uroot -p0618 redhat < /mnt/redhat.sql ##从/mnt/redhat.sql文件中将文件内容导入redhat库中
[root@station ~]# mysql -uroot -p0618
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.44-MariaDB MariaDB Server
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lcl |
| mysql |
| performance_schema |
| redhat |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> USE redhat;
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 [redhat]> SHOW TABLES;
+------------------+
| Tables_in_redhat |
+------------------+
| redhat |
+------------------+
1 row in set (0.00 sec)
MariaDB [redhat]> SELECT * FROM redhat;
+----------+----------+
| username | password |
+----------+----------+
| alice | 123 |
+----------+----------+
1 row in set (0.00 sec)
此时恢复成功
方法二:
MariaDB [redhat]> DROP TABLE redhat; ##先将表删除
Query OK, 0 rows affected (0.04 sec)
MariaDB [redhat]> DROP DATABASE redhat; ##删除库
Query OK, 0 rows affected (0.00 sec)
[root@station ~]# vim /mnt/redhat.sql ##修改备份文件
21 CREATE DATABASE redhat; ##创建库
22 USE redhat; ##使用库
23 DROP TABLE IF EXISTS `redhat`; ##这行本身就有,是想说明在哪里添加
[root@station ~]# mysql -uroot -p0618 < /mnt/redhat.sql
##此时进入数据库查看就会发现恢复成功
四)phpmyadmin数据库图形化管理
- 下载phpadmin软件包
- yum install php php-mysql -y
- systemctl restart httpd
- tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2 -C /var/www/html/ ##记得解压到htpp的默认目录中,这样可以通过浏览器查看
- mv phpMyAdmin-3.4.0-all-languages/ phpadmin ##因为默认名称太长,不利于浏览器查看,所以换了个名字
- cd phpadmin ##里面有README文件,可以通过查看安装方法安装,具体方法如下:
- cp config.sample.inc.php config.inc.php
- vim Documentation.txt
139 $cfg[‘blowfish_secret’] = ‘ba17c1ec07d65003’; ##将这一行引号中的内容复制到config.inc.php中 - vim config.inc.php
17 $cfg[‘blowfish_secret’] = ‘ba17c1ec07d65003’; - systemctl restart httpd
测试