在Linux系统中的MySQL数据库管理

首先安装数据库管理软件mariadb
[[email protected] ~]# yum install mariadb-server.x86_64 -y

一、启动或关闭mysql服务

1. /ect/init.d/mysql start开启服务 (前面为mysql的安装路径)

[root@localhost mysql]# which is mysql
/usr/bin/which: no is in (/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin)
/usr/bin/mysql

[[email protected] mysql]# /usr/bin/mysql -uroot -p888 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 50
Server version: 5.5.35-MariaDB MariaDB Server
Copyright (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)]> 

2.使用mysql命令启动

[root@localhost mysql]# mysql -uroot -p888

---

关闭服务

[root@localhost ~]# /usr/bin/mysql shutdown

设置网络是否可以访问

查看mysql网络接口

[root@foundation33 shell]# netstat -antlup | grep mysql
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      9031/mysqld   

[root@foundation33 ~]# vim /etc/my.cnf
  7 skip-networking=1
###添加此行可以关闭接口

[root@foundation33 ~]# restart mariadb
[root@foundation33 ~]# netstat -antlup | grep mysql
###接口关闭

二、数据库安全保护

#设置root用户访问的密码

[[email protected] ~]# 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] n
 ... skipping.
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] n  #移除测试数据库并且接入
 ... skipping.
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] n      #更新数据库权限表
 ... skipping.
Cleaning up...
All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.
Thanks for using MariaDB!

三、设置本地用户访问数据库

[[email protected] ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
skip-networking=1        #选择忽略网络连接

[[email protected] ~]# systemctl restart mariadb

四、mysql密码管理：

1.mysqladmin -u用户名 -p旧密码 password 新密码

更改原密码vincent为redhat

[[email protected] ~]# mysqladmin -uroot -pvincent password 'redhat'
[[email protected] ~]# mysql -uroot -predhat
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 18
Server version: 5.5.35-MariaDB MariaDB Server
Copyright (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)]> 

2.进入mysql命令行

SET PASSWORD FOR root=PASSWORD(“root”);

MariaDB [(none)]> update mysql.user set Password=password('redhat') where User='root'; 
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

3.忘记root用户密码

编辑配置文件添加忽略权限表，直接进入mysql，重启服务后生效。

[[email protected] ~]# vim /etc/my.cnf 
[mysqld]
...
skip-grant-tables
[[email protected] ~]# systemctl restart mariadb.service 
[[email protected] ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.35-MariaDB MariaDB Server
Copyright (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)]> flush privileges; 
####flush privileges的意思是强制刷新内存授权表，否则用的还是缓冲中的口令。 

---

查看本地密码

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

五、数据库管理

刚开始时才两个数据库：mysql和test。MySQL库很重要它里面有MYSQL的系统信息，我们改密码和新增用户，实际上就是用这个库进行操作。

1.数据库的管理

新建数据库

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

查看数据库

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

查看数据库中内容

MariaDB [new_db]> show tables;
+------------------+
| Tables_in_new_db |
+------------------+
| new_table        |
+------------------+
1 row in set (0.00 sec)

使用数据库

MariaDB [(none)]> use new_db;
Database changed

删除数据库

MariaDB [(none)]> drop database new_db;
Query OK, 0 rows affected (0.35 sec)

2.数据表的管理

创建数据表

MariaDB [new_db]> create table new_table(username varchar(6) not null);
Query OK, 0 rows affected (0.41 sec)

数据表插入和删除行

MariaDB [new_db]> insert into renamed_table values ('value1','value2');
Query OK, 1 row affected (0.10 sec)

MariaDB [new_db]> select * from renamed_table;
+----------+---------+
| username | new_raw |
+----------+---------+
| row1     | NULL    |
| value1   | value2  |
+----------+---------+
2 rows in set (0.00 sec)

删除数据表行

MariaDB [new_db]> delete from renamed_table where username='value1';
Query OK, 1 row affected (0.37 sec)

MariaDB [new_db]> select * from renamed_table;
+----------+---------+
| username | new_raw |
+----------+---------+
| row1     | NULL    |
+----------+---------+
1 row in set (0.00 sec)

更新数据表列

MariaDB [new_db]> update nuts set row2="glorymouse";
ERROR 1146 (42S02): Table 'new_db.nuts' doesn't exist
MariaDB [new_db]> update renamed_table set row2="glorymouse";
Query OK, 1 row affected, 1 warning (0.12 sec)
Rows matched: 1  Changed: 1  Warnings: 1
#row2列数据长度溢出
MariaDB [new_db]> select * from renamed_table;
+----------+---------+------+
| username | new_raw | row2 |
+----------+---------+------+
| row1     | NULL    | gl   |
+----------+---------+------+
1 row in set (0.00 sec)

数据表插入和删除列

MariaDB [new_db]> alter table renamed_table add new_raw varchar(20);                        #新增一列new_raw
Query OK, 1 row affected (0.15 sec)                
Records: 1  Duplicates: 0  Warnings: 0

MariaDB [new_db]> alter table renamed_table add row2 varchar(2) after new_raw;           #在new_raw后添加一列
Query OK, 1 row affected (0.16 sec)                
Records: 1  Duplicates: 0  Warnings: 0

显示数据表

MariaDB [new_db]> select * from renamed_table;  #查看数据表
+----------+---------+------+
| username | new_raw | row2 |
+----------+---------+------+
| row1     | NULL    | NULL |
+----------+---------+------+
1 row in set (0.00 sec)

删除数据表
drop table 表名;

MariaDB [new_db]> drop table new_table;
Query OK, 0 rows affected (0.10 sec)

delete from 表名；（也可以删除具体列）

MariaDB [new_db]> delete from renamed_table
renamed_table           renamed_table.row2      
renamed_table.new_raw   renamed_table.username  

显示数据表结构
describe 表名;/desc 表名;

MariaDB [new_db]> desc new_table;
+----------+------------+------+-----+---------+-------+
| Field    | Type       | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+-------+
| username | varchar(6) | NO   |     | NULL    |       |
+----------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

更改表名
rename table 旧表名 to 新表名;

MariaDB [new_db]> rename table new_table to renamed_table;
Query OK, 0 rows affected (0.12 sec)

MariaDB [new_db]> show tables;
+------------------+
| Tables_in_new_db |
+------------------+
| renamed_table    |
+------------------+
1 row in set (0.00 sec)

---

3.用户的管理

创建本地用户

MariaDB [new_db]> create user jzx@localhost identified by 'jzx';

创建外地用户

MariaDB [new_db]> create user root@172.25.254.79 identified by 'redhat';
Query OK, 0 rows affected (0.00 sec)

给用户赋权

MariaDB [new_db]> grant select,insert on new_db.* to jzx@localhost;
Query OK, 0 rows affected (0.00 sec)

查看用户权力

MariaDB [new_db]> show grants for [email protected];
+------------------------------------------------------------------------------------------------------------+
| Grants for [email protected]                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jzx'@'localhost' IDENTIFIED BY PASSWORD '*10DEC9752F67D4D01C0B0AFEC5B23C5EAAFB7B05' |
| GRANT SELECT, INSERT ON `new_db`.* TO 'jzx'@'localhost'                                                    |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

移除用户权限

MariaDB [new_db]> revoke insert on new_db.* from jzx@localhost;
Query OK, 0 rows affected (0.00 sec)

六、数据库备份与恢复

备份并保存到文件中

mysqldump -uroot -pwestos westos > /mnt/westos.sql ##备份数据库westos
mysqldump -uroot -pwestos westos –no-data 只备份westos库的框架，不备份数据
mysqldump -uroot -pwestos –all-database –no-data 备份所有库框架，不备份数据
mysqldump -uroot -pwestos –all-database > /mnt/westos.sql 备份所有库到/mnt/westos.sql

[root@localhost ~]# mysqldump -uroot -predhat --all-database > /mnt/redhat.sql

恢复
首先创建一个空数据库
再将备份的数据库导入

[root@localhost ~]# mysql -uroot -predhat -e "create database redhat";
[root@localhost ~]# mysql -uroot -predhat redhat < /mnt/redhat.sql 

七、数据库可视化管理

将数据库管理软件解压到apache共享目录下

[root@localhost home]# tar -xzvf phpMyAdmin-3.0.0-rc1-all-languages.tar.gz -C /Custom/html/

重命名解压目录

[root@localhost html]# mv phpMyAdmin-3.0.0-rc1-all-languages/ mysqladmin

更改配置

[root@localhost mysqladmin]# cp -p config.sample.inc.php config.inc.php 
[root@localhost mysqladmin]# vim config.inc.php 

$cfg['blowfish_secret'] = 'ba17c1ec07d65003'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */

在解压目录下的说明文档有mysqladmin应当填写的信息

重启httpd服务

[root@localhost mysqladmin]# systemctl restart httpd

测试：