Linux之mariadb数据库管理

mariadb 简介

MariaDB 数据库管理系统是 MySQL 的一个分支，主要由开源社区在维护，采用GPL 授权许可 MariaDB 的目的是完全兼容 MySQL , 包括 API 和命令行，是MySQL 的代替品
MariaDB 由 MySQL 的创始人 Michael Widenius 主导开发，他早前曾以10亿元的价格，将自己创建的公司 MySQL AB 卖给了 SUN ,此后，随肴 SUN 被甲骨文收购，MySQL 的所有权也落入 Oracle 的手中 MariaDB 名称来自Michael Widenius 的女儿 Maria 的名字

mariadb 安装

[root@node1 ~]# yum install mariadb-server -y  #安装mariadb
[root@node1 ~]# systemctl start mariadb   #启动mariadb服务
[root@node1 ~]# netstat -antlpe | grep mysql  #查看mysql网络接口
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      27         55074      2146/mysqld

mysql初始化

初始化第一步——关闭对外开放接口

[root@node1 ~]# systemctl stop firewalld  #关闭防火墙
[root@node1 ~]# vim /etc/my.cnf
skip-networking=1    #文件添加内容，表示把数据库在网络上开启的接口跳过
[root@node1 ~]# systemctl restart mariadb  #重启mariadb
[root@node1 ~]# netstat -antlpe | grep mysql  #网络接口已关闭

初始化第二步——进行安全设定

[root@node1 ~]# mysql_secure_installation
/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not foundNOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDBSERVERS 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] ... 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] ... 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] - 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] ... 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!

测试：是否初始化？

mysql -uroot -p | -p密码 #登录mysql，为了安全起见不会直接在-p后面加密码

这里写图片描述

查询 mysql

SHOW DATABASES; #显示当前库的名字，相当于查看目录下的子目录的名字
USE database_name; #进入数据库，相当于进入目录的子目录里面
SHOW TABLES; #查看库里面有哪些表，相当于查看子目录里面有哪些文件
SELECT * FROM table_name; #查询所有内容从哪个库里面查
DESC table_name; #查询表结构
测试：

[root@node1 ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 5.5.35-MariaDB MariaDB ServerCopyright (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)]> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
MariaDB [mysql]> SHOW TABLES;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| servers                   |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
24 rows in set (0.00 sec)
MariaDB [mysql]> SELECT User,Host,Password FROM user;
+------+-----------+-------------------------------------------+
| User | Host      | Password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root | 127.0.0.1 | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root | ::1       | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
MariaDB [mysql]> SELECT User,Host,Password FROM user Where User='root' AND Host='localhost';
+------+-----------+-------------------------------------------+
| User | Host      | Password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+------+-----------+-------------------------------------------+
1 row in set (0.00 sec)

这里写图片描述

建立 mysql

CREATE DATABASE database_name; #创建数据库
CREATE TABLE table_name (name VARCHAR(20),sex CHAR(1)); #创建表格
INSERT INTO table_name VALUES (XXX,‘M’); #给表格写入name为XXX，sex为M的内容
测试：

[root@node1 ~]# 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 ServerCopyright (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)]> CREATE DATABASE haha;
Query OK, 1 row affected (0.00 sec)MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| haha               |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)MariaDB [(none)]> USE haha;
Database changed
MariaDB [haha]> SHOW TABLES;
Empty set (0.00 sec)MariaDB [haha]> CREATE TABLE xixi(-> username varchar(6) not null,-> password varchar(50) not null);
Query OK, 0 rows affected (0.14 sec)MariaDB [haha]> SHOW TABLES;+----------------+
| Tables_in_haha |
+----------------+
| xixi           |
+----------------+
1 row in set (0.00 sec)MariaDB [haha]> INSERT INTO xixi values ('ying','123');
Query OK, 1 row affected (0.36 sec)MariaDB [haha]> SELECT * FROM xixi;
+----------+----------+
| username | password |
+----------+----------+
| ying     | 123      |
+----------+----------+
1 row in set (0.00 sec)MariaDB [haha]> quit
Bye

更新 mysql

一般情况下库的名字不做修改
/var/lib/mysql/ 可以查看到数据库的名字，也可以修改名字使用 mv
1、ALTER TABLE newtable_name RENAME oldtable_name; #修改表的名字
2、ALTER TABLE table_name ADD style varchar(5); #添加表的内容
3、ALTER TABLE table_name ADD style varchar(5) AFTER username; #指定添加位置添加表的内容
4、ALTER TABLE table_name DROP style; #指定删除表的表列内容
7、 UPDATE table_name SET password=‘333’ WHERE username=‘yi’; #修改表格的内容
8、DELETE FROM table_name WHERE username=‘yi’; #指定删除表格的内容
9、DROP TABLE table_name; #删除表
10、DROP DATABASE database_name; #删除库
测试：

[root@node1 ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 5.5.35-MariaDB MariaDB ServerCopyright (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 haha;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
MariaDB [haha]> SHOW TABLES;
+----------------+
| Tables_in_haha |
+----------------+
| xixi           |
+----------------+
1 row in set (0.01 sec)
MariaDB [haha]> ALTER TABLE xixi RENAME lala;
Query OK, 0 rows affected (0.10 sec)MariaDB [haha]> SHOW TABLES;
+----------------+
| Tables_in_haha |
+----------------+
| lala           |
+----------------+
1 row in set (0.00 sec)
MariaDB [haha]> ALTER TABLE lala ADD style varchar(5) AFTER username;
Query OK, 1 row affected (0.38 sec)                
Records: 1  Duplicates: 0  Warnings: 0MariaDB [haha]> SELECT * FROM lala;
+----------+-------+----------+
| username | style | password |
+----------+-------+----------+
| ying     | NULL  | 123      |
+----------+-------+----------+
1 row in set (0.00 sec)
MariaDB [haha]> ALTER TABLE lala DROP style;
Query OK, 1 row affected (0.52 sec)                
Records: 1  Duplicates: 0  Warnings: 0MariaDB [haha]> SELECT * FROM lala;
+----------+----------+
| username | password |
+----------+----------+
| ying     | 123      |
+----------+----------+
1 row in set (0.00 sec)
MariaDB [haha]> UPDATE lala SET password='333' WHERE username='ying';
Query OK, 1 row affected (0.36 sec)
Rows matched: 1  Changed: 1  Warnings: 0MariaDB [haha]> SELECT * FROM lala;+----------+----------+
| username | password |
+----------+----------+
| ying     | 333      |
+----------+----------+
1 row in set (0.00 sec)
MariaDB [haha]> DELETE FROM lala WHERE username='ying';
Query OK, 1 row affected (0.17 sec)MariaDB [haha]> SELECT * FROM lala;
Empty set (0.00 sec)MariaDB [haha]> DROP TABLE lala;
Query OK, 0 rows affected (0.34 sec)MariaDB [haha]> SHOW TABLES;
Empty set (0.00 sec)MariaDB [haha]> DROP DATABASE haha;
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)MariaDB [(none)]> quit
Bye

网页模式建立 mysql

下载安装数据库软件登录后即可进行修改
安装数据库软件：

[root@node1 ~]# yum install php http -y  #下载php，http
[root@node1 ~]# cd /var/www/html/
[root@node1 html]# ls
phpMyAdmin-3.4.0-all-languages.tar.bz2
[root@node1 html]# tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2  #解压php数据库软件
[root@node1 html]# ls
phpMyAdmin-3.4.0-all-languages
phpMyAdmin-3.4.0-all-languages.tar.bz2
[root@node1 html]# rm -fr *.bz2  #删除压缩包
[root@node1 html]# ls
phpMyAdmin-3.4.0-all-languages
[root@node1 html]# mv phpMyAdmin-3.4.0-all-languages/ mysqladmin #修改数据库软件名称
[root@node1 html]# cd mysqladmin/
[root@node1 mysqladmin]# cp config.sample.inc.php config.inc.php  #复制新文件，根据快速安装步骤说明
[root@node1 mysqladmin]# yum install php-mysql.x86_64 -y  #安装php支持的mysql软件
[root@node1 ~]# firefox  #打开浏览器进入网页，进行登录

这里写图片描述
登录后创建数据库data：

进入数据库添加表time：

给表添加内容：

添加完成后可看到表里面的内容：

在终端查看网页添加的数据库：

[root@node1 ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 56
Server version: 5.5.35-MariaDB MariaDB ServerCopyright (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 |
| data               |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)MariaDB [(none)]> USE data;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
MariaDB [data]> SHOW TABLES;
+----------------+
| Tables_in_data |
+----------------+
| time           |
+----------------+
1 row in set (0.00 sec)MariaDB [data]> SELECT * FROM time;
+----------+--------+-------+
| username | passwd | time  |
+----------+--------+-------+
| aaa      | 123    | 11:00 |
| bbb      | 234    | 12:00 |
+----------+--------+-------+
2 rows in set (0.00 sec)MariaDB [data]> quit
Bye

用户和访问权限

建立用户：
CREATE USER user@localhost identified by ‘haha’;
CREATE USER user@’%’ identified by ‘haha’;
localhost表示本地用户，’%‘表示任何客户端可以登录，可以远程登录，identified by 后面加用户密码
用户授权：
GRANT INSERT,UPDATE,DELETE,SELECT on haha.* to user@localhost;
GRANT SELECT on mariadb.* haha@’%’;
查看用户授权：
SHOW GRANTS FOR user@localhost;
重载授权表：
FLUSH PRIVILEGES;
撤销用户授权：
REVOKE UPDATE on haha.* from user@localhost;
删除用户：
DROP USER user@localhost;
测试：

[root@node1 ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 62
Server version: 5.5.35-MariaDB MariaDB ServerCopyright (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)]> CREATE USER user1@localhost identified by 'user1';
Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> CREATE USER user2@localhost identified by 'user2';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> GRANT INSERT,UPDATE,SELECT on data.* to user1@localhost;  #给用户user1添加插入、更新、查看权限
Query OK, 0 rows affected (0.00 sec)
MariaDB [data]> SHOW GRANTS FOR user1@localhost;+--------------------------------------------------------------------------------------------------------------+
| Grants for user1@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*34D3B87A652E7F0D1D371C3DBF28E291705468C4' |
| GRANT SELECT, INSERT, UPDATE ON `data`.* TO 'user1'@'localhost'                                              |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

网页测试：
进入数据库：
这里写图片描述
更改表里面的内容：

显示如下则更新成功：

在终端查看是否更新：

MariaDB [data]> SELECT * FROM time;
+----------+--------+-------+
| username | passwd | time  |
+----------+--------+-------+
| aab      | 123    | 11:00 |
| bbb      | 234    | 12:00 |
+----------+--------+-------+
2 rows in set (0.00 sec)
MariaDB [data]> REVOKE UPDATE on data.* from user1@localhost;  #撤销用户更新权限
Query OK, 0 rows affected (0.00 sec)

在网页更新时会显示如下提示：
这里写图片描述

MariaDB [data]> DROP USER user1@localhost;  #删除用户
Query OK, 0 rows affected (0.00 sec)
MariaDB [data]> quit
Bye

忘记数据库密码怎么办？

1、systemctl stop mariadb
2、mysqld_safe --skip-grant-tables & #跳过授权表进入mysql
3、进入数据库更改密码
4、kill -9 数据库进程
5、systemctl start mariadb
实验：

[root@node1 ~]# systemctl stop mariadb  #关闭mariadb
[root@node1 ~]# mysqld_safe --skip-grant-tables &
[1] 6860
[root@node1 ~]# 180529 11:07:28 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
180529 11:07:28 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
[root@node1 ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.35-MariaDB MariaDB ServerCopyright (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 -ADatabase changed
MariaDB [mysql]> SELECT User,Password FROM user;  #查看mysql里面的表，可以查看到root用户加密后的密码
+-------+-------------------------------------------+
| User  | Password                                  |
+-------+-------------------------------------------+
| root  | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root  | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| root  | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| user2 | *12A20BE57AF67CBF230D55FD33FBAF5230CFDBC4 |
+-------+-------------------------------------------+
4 rows in set (0.00 sec)MariaDB [mysql]> update mysql.user set Password=Password('haha') Where User='root';  修改root用户的密码为haha，用使用加密类型为Password，不对密码加密时，密码时明文存在，步安全
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0MariaDB [mysql]> SELECT User,Password FROM user;  #查看密码已改变
+-------+-------------------------------------------+
| User  | Password                                  |
+-------+-------------------------------------------+
| root  | *85D0F19E5598AC04AC7B3FCF5383247D28FB59EF |
| root  | *85D0F19E5598AC04AC7B3FCF5383247D28FB59EF |
| root  | *85D0F19E5598AC04AC7B3FCF5383247D28FB59EF |
| user2 | *12A20BE57AF67CBF230D55FD33FBAF5230CFDBC4 |
+-------+-------------------------------------------+
4 rows in set (0.00 sec)
MariaDB [mysql]> quit
Bye
[root@node1 ~]# fg   #Ctrl+z把进程打入后台
mysqld_safe --skip-grant-tables
^Z
[1]+  Stopped                 mysqld_safe --skip-grant-tables
[root@node1 ~]# killall -9 mysqld_safe  #杀死进程
[1]+  Killed                  mysqld_safe --skip-grant-tables
[root@node1 ~]# ps aux | grep mysql  #查看mysql相关进程
mysql     7015  0.0  9.1 859064 88264 pts/1    Sl   11:07   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      7078  0.0  0.0 112640   936 pts/1    R+   11:11   0:00 grep --color=auto mysql
[root@node1 ~]# kill -9 7015  #杀死mysql相关进程
[root@node1 ~]# ps aux | grep mysql
root      7080  0.0  0.0 112640   932 pts/1    R+   11:12   0:00 grep --color=auto mysql
[root@node1 ~]# systemctl start mariadb  #打开mariadb
[root@node1 ~]# mysql -uroot -phaha  #这里为了实验效果明文写密码，下面显示用密码haha可以登录，即修改成功
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.35-MariaDB MariaDB ServerCopyright (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

备份与恢复

备份

mysqldump -uroot -proot --all-database #备份所有数据库
mysqldump -uroot -proot --all-database --no-data #只备份所有数据库的框架不备份数据
mysqldump -uroot -proot haha #备份数据库haha，备份位置在当前
mysqldump -uroot -proot haha > /mnt/haha.sql #备份数据库haha，并将其导入/mnt下

恢复方法一

vim /mnt/haha.sql #编辑备份过来的文件
文件内容：
这里写图片描述
mysql -uroot -predhat < /mnt/haha.sql #将文件导入

恢复方法二

mysql -uroot -predhat -e “CREATE DATABASE haha;” #创建数据库haha
mysql -uroot -predhat haha < /mnt/haha.sql #将表内容导入数据库
实验如下：
备份：

[root@node1 ~]# mysqldump -uroot -phaha data > /mnt/data.sql
[root@node1 ~]# mysql -uroot -phaha -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| data               |
| mysql              |
| performance_schema |
+--------------------+
[root@node1 ~]# mysql -uroot -phaha -e "drop database data;"  #删除数据库
[root@node1 ~]# mysql -uroot -phaha -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

恢复方法一：

[root@node1 ~]# vim /mnt/data.sql

文件内容：
这里写图片描述

[root@node1 ~]# mysql -uroot -phaha < /mnt/data.sql
[root@node1 ~]# mysql -uroot -phaha -e "show databases;"  #查看已经恢复
+--------------------+
| Database           |
+--------------------+
| information_schema |
| data               |
| mysql              |
| performance_schema |
+--------------------+

恢复方法二：

[root@node1 ~]# mysql -uroot -phaha -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
[root@node1 ~]# mysql -uroot -phaha -e "CREATE DATABASE data;"
[root@node1 ~]# mysql -uroot -phaha data < /mnt/data.sql
[root@node1 ~]# mysql -uroot -phaha -e "show databases;"  #查看已经恢复
+--------------------+
| Database           |
+--------------------+
| information_schema |
| data               |
| mysql              |
| performance_schema |
+--------------------+
[root@node1 ~]# mysql -uroot -phaha -e "select * from data.time;"
+----------+--------+-------+
| username | passwd | time  |
+----------+--------+-------+
| aab      | 123    | 11:00 |
| bbb      | 234    | 12:00 |
+----------+--------+-------+