mariadb

MariaDB数据库的安装与配置

一、安装

yum install mariadb-server -y ##安装
systemctl start mariadb ##开启服务

二、基本配置

一）安全初始化

1、默认情况下，数据库端口是打开的，其他主机可以通过打开的端口来进行访问，所以先将端口关闭

[root@station mysql]# netstat -antlpe | grep mysqltcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      27         39838      3913/mysqld[root@station mysql]# vim /etc/my.cnfskip-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 1Server version: 5.5.44-MariaDB MariaDB ServerCopyright (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的密码为0618Query OK, 3 rows affected (0.00 sec)Rows matched: 3  Changed: 3  Warnings: 0MariaDB [(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-tablesmysql     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.sockroot      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 2Server version: 5.5.44-MariaDB MariaDB ServerCopyright (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 -pEnter password:Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 2Server version: 5.5.44-MariaDB MariaDB ServerCopyright (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 changedMariaDB [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: 0MariaDB [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: 0MariaDB [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字段后面，默认只能添加在某个字段之后，而不能用beforeQuery OK, 1 row affected (0.16 sec)                Records: 1  Duplicates: 0  Warnings: 0MariaDB [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: 0MariaDB [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;  ##更改表的名字为redhatQuery 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;   ##删除表redhatQuery OK, 0 rows affected (0.03 sec)MariaDB [lcl]> DROP DATABASE lcl;   ##删除库lclQuery 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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedMariaDB [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 -p0618Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 8Server version: 5.5.44-MariaDB MariaDB ServerCopyright (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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedMariaDB [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数据库图形化管理

1. 下载phpadmin软件包
2. yum install php php-mysql -y
3. systemctl restart httpd
4. tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2 -C /var/www/html/ ##记得解压到htpp的默认目录中，这样可以通过浏览器查看
5. mv phpMyAdmin-3.4.0-all-languages/ phpadmin ##因为默认名称太长，不利于浏览器查看，所以换了个名字
6. cd phpadmin ##里面有README文件，可以通过查看安装方法安装，具体方法如下：
7. cp config.sample.inc.php config.inc.php
8. vim Documentation.txt
   139 $cfg[‘blowfish_secret’] = ‘ba17c1ec07d65003’; ##将这一行引号中的内容复制到config.inc.php中
9. vim config.inc.php
   17 $cfg[‘blowfish_secret’] = ‘ba17c1ec07d65003’;
10. systemctl restart httpd

测试