MariaDB数据库简单入门（含备份、恢复），mariadb数据库

和通数据库htsjk.Com2019-07-16 01:10 来源:未知阅读:12040 评论 354 热度5

标签：

MariaDB数据库简单入门（含备份、恢复），mariadb数据库

1、安装MariaDB数据库主程序和服务端

[root@fudanwuxi ~]# yum install mariadb mariadb-server.x86_64
[root@fudanwuxi ~]# systemctl restart mariadb.service 
[root@fudanwuxi ~]# systemctl enable mariadb.service        
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.

2、初始化

[root@fudanwuxi ~]# mysql_secure_installation   #安装安装，即进行初始化

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):   #输入当前数据库root用户的密码，初始没有密码，直接按回车
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  #禁止数据库root用户进行远程登陆
 ... 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!

3、登陆数据库（修改密码、创建用户、授权、取消授权）

修改root密码

[root@fudanwuxi ~]# mysql -u root -p  #-u用来指定以数据库用户root登陆，-p用来验证用户数据库的密码
Enter password:   #输入用户root的数据库密码
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, 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)]> set password = PASSWORD('fudanwuxi');  #修改密码为fudanwuxi
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> exit  #退出数据库
Bye
[root@fudanwuxi ~]# mysql -u root -p  #重新进行登陆测试
Enter password:   #输入初始化设定的密码时候拒绝登陆
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
[root@fudanwuxi ~]# mysql -u root -p  #重新测试登陆
Enter password:   #输入刚才修改的密码fudanwuxi，登陆成功
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

创建一个普通用户

[root@fudanwuxi ~]# mysql -u root -p  #以数据库root用户进行登陆
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create user ajie@localhost identified by 'ajie123';  #创建一个数据库用户ajie，密码为ajie123
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> exit  #退出root用户登陆
Bye
[root@fudanwuxi ~]# mysql -u ajie -p  #使用ajie进行登陆
Enter password:   #输入密码ajie123
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, 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 |
+--------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

数据库普通用户提权

[root@fudanwuxi ~]# mysql -u root -p  #使用数据库root用户登陆
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> grant select,update,delete,insert ON mysql.* to ajie@localhost;  #授予ajie用户查询、更新、删除、插入权限。mysql.*代表授予ajie的权限对数据库mysql下的所有表单有效
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> exit
Bye
[root@fudanwuxi ~]# mysql -u ajie -p  #退出root，使用ajie来登陆
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;  #此时查看所有数据库的时候，可以看到有mysql了
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 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;  #查看数据库mysql中的所有表单
+---------------------------+
| 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.01 sec)

MariaDB [mysql]>

取消普通用户授权

[root@fudanwuxi ~]# mysql -u root -p  #登陆root用户
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> revoke select,update,delete,insert on mysql.* from ajie@localhost;   #移除刚才授予ajie的权限
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants for ajie@localhost;  #查看用户ajie的权限，已经没有了之前授予的权限
+-------------------------------------------------------------------------------------------------------------+
| Grants for ajie@localhost                                                                                   |
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'ajie'@'localhost' IDENTIFIED BY PASSWORD '*A65FBC245EF7CC4346000B7CFA058E0D5A234219' |
+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> exit  #退出
Bye
[root@fudanwuxi ~]# mysql -u ajie -p  #使用ajie登陆
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;  #查看数据库，看不到之前授予权限时候能看到的mysql数据库了
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

4、创建数据库及表单（查找、修改、删除）

创建一个数据库wechat

[root@fudanwuxi ~]# mysql -u root -p  #以root登陆
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 22
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, 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 wechat;  #新增一个数据库，名称为wechat。删除使用drop。
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;  #再查看数据库，多了一个wechat
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| wechat             |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]>

在数据库wechat中创建表单myid

MariaDB [(none)]> use wechat;  #进入使用数据库wechat
Database changed
MariaDB [wechat]> create table myid(name char(15),address char(15),price int);  
#创建一个表单，名称为myid。
定义存储数据内容的结构，三个字段，
其中长度为15个字符的字符型字段name存放微信名；
长度为15个字符的字符型字段address存放使用地区；
整型字段price存放售卖价格。
Query OK, 0 rows affected (0.01 sec)

MariaDB [wechat]> show tables;  #再查看表单，多了一个刚刚新建的myid表单
+------------------+
| Tables_in_wechat |
+------------------+
| myid             |
+------------------+
1 row in set (0.00 sec)

MariaDB [wechat]> describe myid;  #初始化新建的表单myid
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| name    | char(15) | YES  |     | NULL    |       |
| address | char(15) | YES  |     | NULL    |       |
| price   | int(11)  | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

MariaDB [wechat]>

向表单myid中插入数据和查询数据

MariaDB [wechat]> insert INTO myid(name,address,price) VALUES('jschinamobile','jiangsu','999');  #向表单myid中插入一条信息，name,address,price它们的值分别对应jschinamobile,jiangsu,999
Query OK, 1 row affected (0.00 sec)

MariaDB [wechat]> select * from myid;  #查询myid表单中的所有数据
+---------------+---------+-------+
| name          | address | price |
+---------------+---------+-------+
| jschinamobile | jiangsu |   999 |
+---------------+---------+-------+
1 row in set (0.00 sec)

MariaDB [wechat]> select name,price from myid;   #仅查询myid表单中的name和price数据
+---------------+-------+
| name          | price |
+---------------+-------+
| jschinamobile |   999 |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [wechat]> update myid set price='666666' where name='jschinamobile';  #将name值为jschinamobile的price值修改为666666
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [wechat]> select * from myid;  #查看表单，price值变为了666666
+---------------+---------+--------+
| name          | address | price  |
+---------------+---------+--------+
| jschinamobile | jiangsu | 666666 |
+---------------+---------+--------+
1 row in set (0.00 sec)

MariaDB [wechat]> delete from myid;  #删除myid表单中的所有内容
Query OK, 1 row affected (0.00 sec)

MariaDB [wechat]> select * from myid;  #查询myid表单，提示空的
Empty set (0.00 sec)

MariaDB [wechat]>

指定查找

MariaDB [wechat]> insert INTO myid(name,address,price) VALUES('jschinamobile','jiangsu','999'); 
Query OK, 1 row affected (0.00 sec)

MariaDB [wechat]> insert INTO myid(name,address,price) VALUES('www.wuxier.cn','jiangsu','555');  
Query OK, 1 row affected (0.00 sec)

MariaDB [wechat]> insert INTO myid(name,address,price) VALUES('fudan','shanghai','1000');        
Query OK, 1 row affected (0.00 sec)

MariaDB [wechat]> insert INTO myid(name,address,price) VALUES('ajie','dongtai','666');           
Query OK, 1 row affected (0.00 sec)

MariaDB [wechat]> select * from myid;
+---------------+----------+-------+
| name          | address  | price |
+---------------+----------+-------+
| jschinamobile | jiangsu  |   999 |
| www.wuxier.cn | jiangsu  |   555 |
| fudan         | shanghai |  1000 |
| ajie          | dongtai  |   666 |
+---------------+----------+-------+
4 rows in set (0.00 sec)

MariaDB [wechat]> select * from myid where name='ajie';  #查找表单myid中name值为ajie的
+------+---------+-------+
| name | address | price |
+------+---------+-------+
| ajie | dongtai |   666 |
+------+---------+-------+
1 row in set (0.00 sec)

MariaDB [wechat]> select name,price from myid where name='ajie';   #查找值为ajie，只看name和price两个信息
+------+-------+
| name | price |
+------+-------+
| ajie |   666 |
+------+-------+
1 row in set (0.00 sec)

MariaDB [wechat]> select * from myid where price='555';   #查找表单myid中price值为555的      
+---------------+---------+-------+
| name          | address | price |
+---------------+---------+-------+
| www.wuxier.cn | jiangsu |   555 |
+---------------+---------+-------+
1 row in set (0.00 sec)

MariaDB [wechat]> select * from myid where price>'600';  #查找表单myid中price值大于600的
+---------------+----------+-------+
| name          | address  | price |
+---------------+----------+-------+
| jschinamobile | jiangsu  |   999 |
| fudan         | shanghai |  1000 |
| ajie          | dongtai  |   666 |
+---------------+----------+-------+
3 rows in set (0.00 sec)

5、数据库的备份

备份数据库wechat

[root@fudanwuxi ~]# mysqldump -u root -p wechat > /root/backup_wechat.dump  
#使用root去备份，-p代表它有密码，wechat为要备份的数据库，备份到/root/下名称为backup_wechat.dump
Enter password:   #输入数据库root用户的密码
[root@fudanwuxi ~]# ll /root/
total 12
-rw-------. 1 root root 1783 May 14 12:46 anaconda-ks.cfg
-rw-r--r--. 1 root root 1972 Aug  6 14:35 backup_wechat.dump  #查看/root/目录下，生成了backup_wechat.dump备份文件
drwxr-xr-x. 2 root root    6 Jul 28 15:21 Desktop
drwxr-xr-x. 2 root root    6 Jul 28 15:21 Documents
drwxr-xr-x. 2 root root    6 Jul 28 15:21 Downloads
-rw-r--r--. 1 root root 1831 May 14 12:53 initial-setup-ks.cfg
drwxr-xr-x. 2 root root    6 Jul 28 15:21 Music
drwxr-xr-x. 2 root root    6 Jul 28 15:21 Pictures
drwxr-xr-x. 2 root root    6 Jul 28 15:21 Public
drwxr-xr-x. 2 root root    6 Jul 28 15:21 Templates
drwxr-xr-x. 2 root root    6 Jul 28 15:21 Videos

删除数据库wechat中的表单myid的数据

[root@fudanwuxi ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 26
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, 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 |
| wechat             |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> use wechat
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 [wechat]> show tables;
+------------------+
| Tables_in_wechat |
+------------------+
| myid             |
+------------------+
1 row in set (0.00 sec)

MariaDB [wechat]> select * from myid;
+---------------+----------+-------+
| name          | address  | price |
+---------------+----------+-------+
| jschinamobile | jiangsu  |   999 |
| www.wuxier.cn | jiangsu  |   555 |
| fudan         | shanghai |  1000 |
| ajie          | dongtai  |   666 |
+---------------+----------+-------+
4 rows in set (0.00 sec)

MariaDB [wechat]> delete from myid;     #删除 
Query OK, 4 rows affected (0.01 sec)

MariaDB [wechat]> select * from myid;   #再查询表单myid中数据为空
Empty set (0.00 sec)

恢复数据库

[root@fudanwuxi ~]# mysql -u root -p wechat < /root/backup_wechat.dump   #输入重定向来导入数据wechat进行还原
Enter password: 
[root@fudanwuxi ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 29
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, 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 |
| wechat             |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [(none)]> use wechat;
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 [wechat]> show tables;
+------------------+
| Tables_in_wechat |
+------------------+
| myid             |
+------------------+
1 row in set (0.00 sec)

MariaDB [wechat]> select * from myid;   #查看表单myid中的信息，还原成功
+---------------+----------+-------+
| name          | address  | price |
+---------------+----------+-------+
| jschinamobile | jiangsu  |   999 |
| www.wuxier.cn | jiangsu  |   555 |
| fudan         | shanghai |  1000 |
| ajie          | dongtai  |   666 |
+---------------+----------+-------+
4 rows in set (0.02 sec)