linux-mysql-白红宇

#1.安装#################

[root@liu ~]# yum install mariadb-server-y ##安装

[root@liu ~]# systemctl start mariadb ##开启

#2.安全初始化###################

*）默认情况下，数据库的网络接口是打开的

为了安全，需要关闭此接口

[root@liu ~]# vim /etc/my.cnf

[root@liu ~]# netstat -antlpe | grep mysql

[root@liu ~]# systemctl restart mariadb

*)数据库起始状态设定信息是不安全的，需要做以下设定：

Enter current password for root (enter fornone): ##按回车

Set root password? [Y/n] y

New password: ##设定密码

Re-enter new password:

Password updated successfully!

Reloading privilege tables..

...Success!

Remove anonymous users? [Y/n] y

Disallow root login remotely? [Y/n] y

Remove test database and access to it? [Y/n] y

Reload privilege tables now? [Y/n] y

[root@liu ~]# mysql -uroot -p ##登录

[root@liu ~]# mysql -uroot -predhat ##也可这样登录（不建议）

#3.密码的管理###############

*)修改密码

[root@liu ~]# mysqladmin -uroot -predhatpassword lee ##改密码为lee

[root@liu ~]# mysql -uroot -plee ##登录测试是否更改成功

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 14

Server version: 5.5.44-MariaDB MariaDBServer

*)当超级用户密码忘记时

[root@liu ~]# systemctl stop mariadb ##关闭

[root@liu ~]# mysqld_safe--skip-grant-tables & ##跳过密码验证

[1] 3988

MariaDB [(none)]> update mysql.user setPassword=password('redhat') where User='root' ##更新用户密码

-> ;

[root@liu ~]# ps aux | grep mysql

kill -9 mysql的所有进程id ##杀掉进程

[root@liu ~]# systemctl start mariadb ##开启

##测试

#4.数据库的管理###############

*）建立

[root@liu ~]# mysql -uroot -predhat

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 2

MariaDB [(none)]> SHOW DATABASES; ##列出库

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

+--------------------+

MariaDB [(none)]> CREATE DATABASE westos; ##建立库

MariaDB [(none)]> USE westos; ##进入库

MariaDB [westos]> CREATE TABLE linux( ##建立表

-> username varchar(50) not null, （不可为空）

-> password varchar(50) not null

-> );

MariaDB [westos]> DESC linux; ##查看表结构

MariaDB [westos]> INSERT INTO linuxVALUES ('lee','123'); ##插入数据到linux表中

MariaDB [westos]> SELECT * FROM linux; ##查询所有字段在linux表中

+----------+----------+

| username | password |

+----------+----------+

| lee | 123 |

+----------+----------+

MariaDB [westos]> SELECT USERNAME FROMlinux; ##查询指定字段在linux表中

MariaDB [westos]> SELECT USERNAME,PASSWORD FROM linux; ##逗号隔开指定字段

MariaDB [westos]> INSERT INTO linuxVALUES ('tom','123');

Query OK, 1 row affected (0.01 sec)

*）更改

MariaDB [westos]> SELECTUSERNAME,PASSWORD FROM linux;

+----------+----------+

| USERNAME | PASSWORD |

+----------+----------+

| lee | 123 |

| tom | 123 |

+----------+----------+

MariaDB [westos]> UPDATE linux SETpassword=password('tom') where username='tom' ##更改tom密码（加密）

MariaDB [westos]> SELECTUSERNAME,PASSWORD FROM linux;

-+-------------------------------------------+

| USERNAME | PASSWORD |

+----------+-------------------------------------------+

| lee | 123 |

| tom | *71FF744436C7EA1B954F6276121DB5D2BF68FC07 |

+----------+-------------------------------------------+

MariaDB [westos]> ALTER TABLE linux ADDclass varchar(20); ##添加class 字段

Query OK, 2 rows affected (0.04 sec)

Records: 2 Duplicates: 0 Warnings: 0

MariaDB [westos]> SELECT * FROM linux;

+----------+-------------------------------------------+-------+

| username | password | class |

+----------+-------------------------------------------+-------+

| lee | 123 |NULL |

| tom | *71FF744436C7EA1B954F6276121DB5D2BF68FC07 | NULL |

+----------+-------------------------------------------+-------+

MariaDB [westos]> ALTER TABLE linux DROPCLASS; ##删除字段

MariaDB [westos]> ALTER TABLE linux ADDage varchar(20) AFTER username; ##在指定位置加入字段

MariaDB [westos]> SELECT * FROM linux;

+----------+------+-------------------------------------------+

| username | age | password |

+----------+------+-------------------------------------------+

| lee | NULL | 123 |

| tom | NULL | *71FF744436C7EA1B954F6276121DB5D2BF68FC07 |

+----------+------+-------------------------------------------+

MariaDB [westos]> ALTER TABLE linuxRENAME redhat; ##更改表名为redhat

*)删除

MariaDB [westos]> DELETE FROM linuxwhere username='lee'; ##删除表中lee内容

MariaDB [westos]> DELETE FROM linuxwhere username='tom'; ##删除表中tom内容

MariaDB [westos]> DROP TABLE linux; ##删除表linux

MariaDB [westos]> DROP DATABASEwestos; ##删除库

*）.用户授权

MariaDB [westos]> CREATE USERliu@'localhost' identified by 'westos'; ##建立授权用户身份

MariaDB [westos]> GRANT SELECT,INSERT onwestos.* TO lee@localhost; ##授权

MariaDB [westos]> SHOW GRANTS FORlee@localhost; ##查看授权

MariaDB [westos]> REVOKE INSERT ONwestos.* FROM lee@localhost; ##撤销授权

5.数据库的备份##############

mysqldump -uroot -pwestos westos >/mnt/westos.sql ##备份westos数据库

mysqldump -uroot -pwestos westos--no-datacd ##不备份数据

mysqldump -uroot -pwestos--all-database ##备份所有数据库

mysqldump -uroot -pwestos --all-database--no-data ##备份所有数据库框架

恢复方式1

mysql -uroot -pwestos -e "CREATE DATABASE westos;"

mysql -uroot -pwestos westos < /mnt/westos.sql

恢复方式2

vim /mnt/westos.sql

CREATE ADTABASE westos;

USE westos;

mysql -uroot -pwestos < /mnt/westos.sql

6.安装phpmyadmin##########

上传：

[root@liu ~]# yum install php php-mysql -y

[root@liu ~]# systemctl restart httpd

[root@liu html]# tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2

[root@liu html]# ls

admin index.php test virtual

cgi phpMyAdmin-3.4.0-all-languages test.html

index.html phpMyAdmin-3.4.0-all-languages.tar.bz2 tmprequest

[root@liu html]# cd mysqladmin

[root@liu mysqladmin]# cpconfig.sample.inc.php config.inc.php

[root@liu mysqladmin]# vim config.inc.php

17$cfg['blowfish_secret'] = 'ba17c1ec07d65003'; /* YOU MUST FILL IN THIS FORC OOKIE AUTH! */

效果：

[root@liu html]# unzip Discuz_X3.2_SC_UTF8.zip

[root@liu html]# ls

admin index.html readme tmprequest virtual

cgi index.php test upload

Discuz_X3.2_SC_UTF8.zip mysqladmin test.html utility

[root@liu html]# chmod 777 upload/ -R ##加权限

[root@liu html]# getenforce

Enforcing

[root@liu html]# setenforce 0 ##调为警告模式

本文转自 huanzi2017 51CTO博客，原文链接:http://blog.51cto.com/13362895/1983675