linux——mySQL数据库配置

一、数据库管理系统类型

1.DB2 IBM：关系型数据库管理系统，在不同操作系统服务；

2.Oracle 甲骨文：高效率、可靠性好的适应高吞吐量的数据库解决方案；

3.Mysql 甲骨文：业内广泛使用的web服务器解决方案，被称为LAMP；

4.SQL server 微软：使用方便可伸缩性好，相关软件集成程度高；

5.Sybase：典型的UNIX或WindowsNT环境下的大型关系型数据库系统；

6.Informix IBM：个集成解决方案，在线数据处理的旗舰数据服务系统；

7.Access 微软：结合JetData base Engine和图形化界面特点；

8.VFP 微软：在DOS上运行，与xBase系列相容；

二、mySQL数据库配置

1、安装数据库（mariadb）服务

[[e-mail] ~]# yum search mariadb        查找mariadb服务的安装包
Loaded plugins: langpacks
Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
============================= N/S matched: mariadb =============================
mariadb-bench.x86_64 : MariaDB benchmark scripts and data
mariadb-devel.i686 : Files for development of MariaDB/MySQL applications
mariadb-devel.x86_64 : Files for development of MariaDB/MySQL applications
mariadb-libs.i686 : The shared libraries required for MariaDB/MySQL clients
mariadb-libs.x86_64 : The shared libraries required for MariaDB/MySQL clients
mariadb-server.x86_64 : The MariaDB server and related files
mariadb.x86_64 : A community developed branch of MySQL
mariadb-test.x86_64 : The test suite distributed with MariaD

  Name and summary matches only, use "search all" for everything.
[[e-mail] ~]# yum install mariadb-server.x86_64 -y   安装服务

2、数据库设置

1、数据库的打开方式

[root@localhost ~]# systemctl start mariadb     打开数据库服务
[root@localhost ~]# systemctl enable mariadb    社主数据库服务开机自启动
ln -s '/usr/lib/systemd/system/mariadb.service' '/etc/systemd/system/multi-user.target.wants/mariadb.service'
[root@localhost ~]# netstat -antuple | grep mysql   查看数据库接口
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      27         55899      3028/mysqld         

2、数据库接口
为了数据库安全。一般情况，数据库接口是要关闭的
关闭接口在数据库的配置文件中

[root@localhost ~]# vim /etc/my.cnf    数据库配置文件

修改完配置文件后重启mariadb服务，再次查看接口

[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# systemctl restart mariadb.service 
[root@localhost ~]# netstat -antuple | grep mysql
[root@localhost ~]# 

3、数据库开启前配置

[[e-mail] ~]# 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       是否重置密码
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     是否移除test数据库
 - 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!

4、数据库登陆

[[e-mail] ~]# mysql -uroot -predhat     密码明文登陆
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 10
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)]> quit
Bye
[[e-mail] ~]# mysql -uroot -p    输入密码登陆
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
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)]> quit
Bye

三、数据库命令

mySQL中命令都以；结尾

1、显示数据库

MariaDB [(none)]> show databases；     
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

2、进入数据库

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

3、查看数据库中表格

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)

4、查找信息

在user表中，查找Hsot，User，Password信息

MariaDB [mysql]> select Host,User,password from user;
+-----------+------+-------------------------------------------+
| Host      | User | password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| 127.0.0.1 | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| ::1       | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
+-----------+------+-------------------------------------------+
3 rows in set (0.00 sec)

5、查询表的数据结构

MariaDB [mysql]> desc user;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field                  | Type                              | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+

6、新建数据库

新建westos数据库

MariaDB [mysql]> create database westos;
Query OK, 1 row affected (0.00 sec)

MariaDB [mysql]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| westos             |
+--------------------+
4 rows in set (0.00 sec)

7、新建表

MariaDB [westos]> use westos;    进入westos数据库
Database changed
MariaDB [westos]> show tables;    查看表格
Empty set (0.00 sec)

MariaDB [westos]> create table linux(    新建linux表格
    -> username varchar(50) not null,    建立username字段，字符串长度不能超过50
    -> password varchar(50) not null);  建立password字段，字符串长度不能超过50  
Query OK, 0 rows affected (0.43 sec)

MariaDB [westos]> show tables;  查看表格
+------------------+
| Tables_in_westos |
+------------------+
| linux            |
+------------------+
1 row in set (0.00 sec)

MariaDB [westos]> 

8、在表格中插入信息

在linux表格中插入hello和123，分别属于username和password字段

MariaDB [westos]> insert into linux values('hello','123');
Query OK, 1 row affected (0.07 sec)

MariaDB [westos]> select * from linux;   查看linux表格中所有信息
+----------+----------+
| username | password |
+----------+----------+
| hello    | 123      |
+----------+----------+
1 row in set (0.00 sec)

9、修改表格中的信息

修改hello的password为321

MariaDB [westos]> update linux set password='321' where username='hello';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [westos]> select * from linux;
+----------+----------+
| username | password |
+----------+----------+
| hello    | 321      |
+----------+----------+
1 row in set (0.00 sec)

10、表格中添加字段

在linux 表格中插入age字段，age字段字符串不超过4，插入在username字段后面

MariaDB [westos]> alter table linux add age varchar(4) after username;
Query OK, 1 row affected (0.23 sec)                
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [westos]> select * from linux;
+----------+------+----------+
| username | age  | password |
+----------+------+----------+
| hello    | NULL | 321      |
+----------+------+----------+
1 row in set (0.00 sec)
MariaDB [westos]> update linux set age='50' where username='hello';  修改hello的age为50
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [westos]> select * from linux;
+----------+------+----------+
| username | age  | password |
+----------+------+----------+
| hello    | 50   | 321      |
+----------+------+----------+
1 row in set (0.00 sec)

11、移除表格中的字段

MariaDB [westos]> alter table linux drop age;    移除age字段
Query OK, 1 row affected (0.14 sec)                
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [westos]> select * from linux;
+----------+----------+
| username | password |
+----------+----------+
| hello    | 321      |
+----------+----------+
1 row in set (0.00 sec)

12、修改表格名称

修改linux表格的名称为lee

MariaDB [westos]> alter table linux rename lee;
Query OK, 0 rows affected (0.05 sec)
MariaDB [westos]> show tables;
+------------------+
| Tables_in_westos |
+------------------+
| lee              |
+------------------+
1 row in set (0.00 sec)

MariaDB [westos]> select * from lee;
+----------+----------+
| username | password |
+----------+----------+
| hello    | 321      |
+----------+----------+
1 row in set (0.00 sec)

13、删除表格中的某一行

删除username为hello开头的一行

MariaDB [westos]> select * from lee;
+----------+----------+
| username | password |
+----------+----------+
| hello    | 321      |
+----------+----------+
1 row in set (0.00 sec)

MariaDB [westos]> delete from lee where username='hello';
Query OK, 1 row affected (0.32 sec)

MariaDB [westos]> select * from lee;
Empty set (0.00 sec)

14、删除表格

删除lee表格

MariaDB [westos]> drop table lee;
Query OK, 0 rows affected (0.04 sec)

MariaDB [westos]> show tables;
Empty set (0.01 sec)

15、删除数据库

删除westos数据库

drop database westos

MariaDB [westos]> show databases;  删除前
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| westos             |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [westos]> drop database westos;
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)

四、数据库用户管理

1、新建用户

新建本地用户hello @localhost为本地用户 @%为远程用户

MariaDB [westos]> create user hello@localhost identified by 'redhat';
Query OK, 0 rows affected (0.00 sec)

在mysql数据库中的user表中可以查到用户信息

MariaDB [mysql]> select Host,User,Password from user;
+-----------+-------+-------------------------------------------+
| Host      | User  | Password                                  |
+-----------+-------+-------------------------------------------+
| localhost | root  | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| 127.0.0.1 | root  | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| ::1       | root  | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| localhost | hello | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
+-----------+-------+-------------------------------------------+
4 rows in set (0.00 sec)

2、用户授权

grant

MariaDB [(none)]> grant INSERT,SELECT on westos.* to [e-mail]; 用户授权（[e-mail]用户可以在westos数据库中执行查看和插入权限）
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants for [e-mail];   查看用户权限
+--------------------------------------------------------------------------------------------------------------+
| Grants for [e-mail]                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'hello'@'localhost' IDENTIFIED BY PASSWORD '*84BB5DF4823DA319BBF86C99624479A198E6EEE9' |
| GRANT SELECT, INSERT ON `westos`.* TO 'hello'@'localhost'                                                    |
+--------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

测试
用[e-mail]用户登陆

[[e-mail] ~]# mysql -uhello -predhat
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 20
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 |
| westos             |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> use westos;
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 [westos]> show tables;
+------------------+
| Tables_in_westos |
+------------------+
| linux            |
+------------------+
1 row in set (0.00 sec)
MariaDB [westos]> insert into westos.linux values('xiaoming','123');  可以插入
Query OK, 1 row affected (0.34 sec)

MariaDB [westos]> select * from linux;  可以查看
+----------+----------+ 
| username | password |
+----------+----------+
| xiaoming | 123      |
+----------+----------+
1 row in set (0.00 sec)

MariaDB [westos]> delete from mydata where username='xiaoming';  不能删除
ERROR 1142 (42000): DELETE command denied to user 'hello'@'localhost' for table 'mydata'

3、权限回收

root用户收回hello用户权限
revoke

[[e-mail] ~]# mysql -uroot -predhat
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 21
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)]> revoke INSERT on westos.* from [e-mail];  回收插入权限
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants for [e-mail];
+--------------------------------------------------------------------------------------------------------------+
| Grants for [e-mail]                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'hello'@'localhost' IDENTIFIED BY PASSWORD '*84BB5DF4823DA319BBF86C99624479A198E6EEE9' |
| GRANT SELECT ON `westos`.* TO 'hello'@'localhost'                                                            |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> flush privileges;   重载授权表
Query OK, 0 rows affected (0.00 sec)

测试

[[e-mail] ~]# mysql -uhello -predhat
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 22
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 |
| westos             |
+--------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> use westos;
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 [westos]> select * from linux;   可以查看
+----------+----------+
| username | password |
+----------+----------+
| xiaoming | 123      |
+----------+----------+
1 row in set (0.00 sec)

MariaDB [westos]> insert into westos.linux values('xiaohong','123');   不可插入
ERROR 1142 (42000): INSERT command denied to user 'hello'@'localhost' for table 'linux'

4、用户删除

drop

MariaDB [(none)]> drop user [e-mail];
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select Host,User,Password from mysql.user;
+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| 127.0.0.1 | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
| ::1       | root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 |
+-----------+------+-------------------------------------------+
3 rows in set (0.00 sec)

五、数据库备份

1、数据备份

 mysqldump -uroot -predhat --all-database > /mnt/data.all  备份所有
 mysqldump -uroot -predhat --all-database --no-data > /mnt/data.err  只备份数据结构
 mysqldump -uroot -predhat westos > /mnt/westos.sql  指定备份

2、数据恢复

[root@localhost ~]# mysql -uroot -predhat < /mnt/data.all   恢复所有

当数据库丢失时，恢复数据库会报错，我们手动删除westos表格，通过/mnt/westos.sql恢复时提示报错

MariaDB [mysql]> drop database westos;
Query OK, 1 row affected (0.32 sec)

MariaDB [mysql]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

MariaDB [mysql]> quit
Bye
[[e-mail] ~]# mysql -uroot -predhat < /mnt/westos.sql 
ERROR 1046 (3D000) at line 22: No database selected

如果数据库丢失，恢复方式有两种，
第一种修改备份文件

登陆数据库后查看

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| westos             |
+--------------------+
4 rows in set (0.00 sec)

第二种是在数据库建立westos，然后导入备份文件

[[e-mail] ~]# mysql -uroot -predhat -e "create database westos;"
[[e-mail] ~]# mysql -uroot -predhat < /mnt/westos.sql 
ERROR 1007 (HY000) at line 21: Can't create database 'westos'; database exists
[[e-mail] ~]# mysql -uroot -predhat 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 40
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 |
| westos             |
+--------------------+
4 rows in set (0.00 sec)

六、root用户密码忘记后修改

1、若忘记密码 列表

关闭数据库服务，跳过认证系统.进入数据库执行修改密码操作

[[e-mail] ~]# systemctl stop mariadb.service 
[[e-mail] ~]# mysqld_safe --skip-grant-tables &
[1] 1508
[[e-mail] ~]# 180507 14:07:25 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
180507 14:07:25 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
^C
[[e-mail] ~]# 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='redhat' where User='root' ;    明文修改密码
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

MariaDB [(none)]> select User,Password,Host from mysql.user;
+------+----------+-----------+
| User | Password | Host      |
+------+----------+-----------+
| root | redhat   | localhost |
| root | redhat   | 127.0.0.1 |
| root | redhat   | ::1       |
+------+----------+-----------+
3 rows in set (0.00 sec)

MariaDB [(none)]> update mysql.user set Password=password('redhat') where User='root' ;    加密字符修改密码方式
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

MariaDB [(none)]> select User,Password,Host from mysql.user;
+------+-------------------------------------------+-----------+
| User | Password                                  | Host      |
+------+-------------------------------------------+-----------+
| root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | localhost |
| root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | 127.0.0.1 |
| root | *84BB5DF4823DA319BBF86C99624479A198E6EEE9 | ::1       |
+------+-------------------------------------------+-----------+

修改完毕推出数据库，关闭和数据库有关的所有进程后，重启服务，用新密码登陆数据库就可以了。

[root@localhost ~]# ps aux | grep mysql
root      1508  0.0  0.1 113248  1556 pts/1    S    14:07   0:00 /bin/sh /usr/bin/mysqld_safe --skip-grant-tables
mysql     1675  0.0  8.7 843948 84412 pts/1    Sl   14: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      1787  0.0  0.0 112640   936 pts/1    R+   14:13   0:00 grep --color=auto mysql
[root@localhost ~]# kill -9 1508
[root@localhost ~]# kill -9 1675
[1]+  Killed                  mysqld_safe --skip-grant-tables
[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# mysql -uroot -predhat
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.