mysql

来源：互联网发布：python count 编辑：程序博客网时间：2024/05/21 15:03

###################mysql#################################

1.基本的环境配置

yum install mariadb-server -y ###数据库软件的安装

systemctl start mariadb ##启动数据库服务

[root@mariadb ~]# netstat -antlpe |grep mysql ##查看网络端口

tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 27 122138 4434/mysqld

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

skip-networking=1 ###关闭网络接口所来连接的端口

[root@mariadb ~]# systemctl restart mariadb.service ##重启服务

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

2,对mysql进行初始化

[root@mariadb ~]# mysql_secure_installation

/usr/bin/mysql_secure_installation: line 379: find_mysql_client: command not found

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

... Success!

Disallow root login remotely? [Y/n] y

... Success!

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

...........................................

Reload privilege tables now? [Y/n] y

... Success!

.................................

Thanks for using MariaDB!

##############数据库的基本sql语句操作#############################

1，登录

mysql -uroot -p

[root@mariadb ~]# mysql -uroot -p ##-u，登录用户 -p，此用户密码

Enter password:

2,查询

show databases; ##显示数据库

use mysql; ##进入mysql库

show tables; ##显示当前库中表的名称

select * from user; ##查询user表中的所有内容（*可以用此表中的任何字段来代替）

desc user; ##查询user表的结构（显示所有字段的名称）

[root@mariadb ~]# mysql -uroot -p ##-u，登录用户 -p，此用户密码

Enter password:

MariaDB [(none)]> show databases; ##显示数据库

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

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

3 rows in set (0.00 sec)

MariaDB [(none)]> use mysql; ##进入mysql库

MariaDB [mysql]> show tables; ##显示当前库中表的名称

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

| Tables_in_mysql |

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

| columns_priv |

| db |

| event |

............................

| time_zone_transition_type |

| user |

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

24 rows in set (0.00 sec)

MariaDB [mysql]> select * from user; ##查询user表中的所有内容（*可以用此表中的任何字段来代替）

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

3 rows in set (0.00 sec)

MariaDB [mysql]> desc user;

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

| User | char(16) | NO | PRI | | |

3，数据库及表的建立

create database westos; ##创建westos库

create tables linux( ##创建linux表，并且linux表含有两个字段，username，password

username varchar(15) not null,

password varchar(15) not null);

insert into linux values ('user1','123'); ##向linux表中插入数据，username字段的数据为user1

*****如下所示：

[root@mariadb mysql]# mysql -uroot -p

Enter password:

MariaDB [(none)]> create database westos; ##创建westos库

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| westos |

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

4 rows in set (0.01 sec)

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

Database changed

MariaDB [westos]> show tables; 显示westos库中的所有表

Empty set (0.00 sec)

MariaDB [westos]> create table linux( ##创建linux表，并且linux表含有两个字段，username，password

-> username varchar(15) not null,

-> password varchar(50) not null);

Query OK, 0 rows affected (0.37 sec)

MariaDB [westos]> show tables;

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

| Tables_in_westos |

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

| linux |

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

1 row in set (0.00 sec)

MariaDB [westos]> desc linux; ##linux表的结构

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

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

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

2 rows in set (0.00 sec)

MariaDB [westos]> insert into linux values ('user1','123'); ##向linux表中插入数据，username字段的数据为user1

Query OK, 1 row affected (0.35 sec)

MariaDB [westos]> select * from linux; ##查询linux表中的所有内容（*可以用此表中的任何字段来代替）

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

| username | password |

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

| user1 | 123 |

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

1 row in set (0.00 sec)

4,更新数据库信息

update linux set password=password('123') where username='user1' ##更新user1的密码

update linux set password=password('123') where (username='user1' or username='user2'); ##更新user1和user2的密码

delete from linux where username=user1; ##删除user1的信息

alter table linux add class varchar(8); ##添加class字段到linux表中的最后一列

alter table linux add date varchar(5) after password; ##添加date字段到password之后

alter table linux drop class; ##删除class字段

[root@mariadb ~]# mysql -uroot -p

Enter password:

MariaDB [westos]> select * from linux;

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

| username | password |

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

| user1 | 123 |

| user2 | 123 |

| user3 | 123 |

| user4 | *23AE809DDACAF9 |

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

MariaDB [westos]> update linux set password=password('123') where username='user1'; ##更新user1的密码

Query OK, 1 row affected, 1 warning (0.03 sec)

Rows matched: 1 Changed: 1 Warnings: 1

MariaDB [westos]> select * from linux;

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

| username | password |

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

| user1 | *23AE809DDACAF9 |

| user2 | 123 |

| user3 | 123 |

| user4 | *23AE809DDACAF9 |

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

4 rows in set (0.00 sec)

MariaDB [westos]> alter table linux add class varchar(20) not null; ##添加class字段到linux表中的最后一列

Query OK, 4 rows affected (0.36 sec)

Records: 4 Duplicates: 0 Warnings: 0

MariaDB [westos]> select * from linux;

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

| username | password | class |

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

| user1 | *23AE809DDACAF9 | |

| user2 | 123 | |

| user3 | 123 | |

| user4 | *23AE809DDACAF9 | |

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

MariaDB [westos]> alter table linux add date varchar(20) not null after password; ##添加date字段到password之后

Query OK, 4 rows affected (0.31 sec)

Records: 4 Duplicates: 0 Warnings: 0

MariaDB [westos]> select * from linux;

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

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

| user2 | 123 | | |

| user3 | 123 | | |

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

5,备份数据库及表和删除数据库

mysqldump -uroot -pdwly --all-database ##备份所有表中的所有数据

mysqldump -uroot -pdwly --all-database --no-data ##备份所有表，但不备份数据

mysqldump -uroot -pdwly westos > /mnt/westos.sql ##备份westos库并把数据保存到westos.sql中

mysqldump -uroot -pdwly westos.linux > /mnt/linux.sql ##备份westos库中的linux表

mysql -uroot -pdwly -e "create database westos;" ##建立westos库

mysql -uroot -pdwly westos < /mnt/westos.sql ##把数据导入westos库

mysql -uroot -pdwly westos < /mnt/linux.sql ##把数据导入linux表

mysql -uroot -pdwly -e "show databases;" ##显示数据库

mysql -uroot -pdwly -e "drop database westos;" ##删除数据库westos

delete from linux where username='user1'; ##删除user1的数据从linux表中

drop table linux; ##删除linux表

drop database westos; ##删除westos库

****如下示例：

1）备份

[root@mariadb ~]# mysqldump -uroot -pdwly westos > /mnt/westos.spl ##备份westos库并把数据保存到westos.spl中

[root@mariadb ~]# mysqldump -uroot -pdwly westos linux > /mnt/linux.sql ##备份westos库中的linux表

2）删除

[root@mariadb ~]# mysql -uroot -p

Enter password:

MariaDB [(none)]> drop database westos; ##删除westos库

Query OK, 1 row affected (0.04 sec)

MariaDB [(none)]> show databases; ##可看到westos库已经删除

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

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

3 rows in set (0.00 sec)

MariaDB [(none)]> create database westos; ##建立westos库

Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show tables from westos; ##westos库中表是空的

Empty set (0.00 sec)

MariaDB [(none)]> quit

Bye

3）导入westos.linux表中

[root@mariadb ~]# mysql -uroot -pdwly westos < /mnt/linux.sql ##把数据导入linux表

[root@mariadb ~]# mysql -uroot -pdwly -e "show tables from westos;" ##导入成功

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

| Tables_in_westos |

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

| linux |

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

4）导入westos库中

[root@mariadb ~]# mysql -uroot -pdwly -e "drop database westos;" ##删除westos库

[root@mariadb ~]# mysql -uroot -pdwly -e "create database westos;" ##建立westos库

[root@mariadb ~]# mysql -uroot -pdwly westos < /mnt/westos.spl ##把数据导入westos库

[root@mariadb ~]# mysql -uroot -pdwly -e "show databases;" ##导入成功

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| westos |

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

6，用户授权

create user mysqli@'%' identified by 'mysqli'; ##建立用户mysqli，此用户可以通过网络登录

create user mysqli@localhost identified by 'mysqli'; ##建立用户mysqli，此用户只能通过本机登录

vim /etc/my.cnf ##开启与网络连接的端口

10 skip-networking=0

systemctl restart mariadb

mysql -umysqli -pmysqli -h 172.25.254.135 ##通过ip登录，用户授权

grant insert,update,delete,select on westos.* to mysqli@localhost; ##用户授权

grant select on westos.* to mysqli@'%';

show grants for mysqli@'%'; ##查看用户授权权力

show grants for mysqli@localhost;

revoke delete on westos.* from mysqli@localhost; ##去除用户授权权利

drop user mysqli@'%'; ##删除用户

*****如下示例：

[root@mariadb ~]# mysql -uroot -p

Enter password:

MariaDB [(none)]> create user mysqli@localhost identified by 'mysqli'; ##建立用户mysqli，此用户只能通过本机登录

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> create user mysqli@'%' identified by 'mysqli'; ##建立用户mysqli，此用户可以通过网络登录

Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> select User,Host from mysql.user;

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

| User | Host |

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

| mysqli | % |

| root | 127.0.0.1 |

| root | ::1 |

| mysqli | localhost |

| root | localhost |

MariaDB [(none)]> quit

Bye

[root@mariadb ~]# mysql -uroot -pdwly -h localhost ##用户授权，在本机登录

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

.........................................

MariaDB [(none)]> quit

Bye

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

10 skip-networking=0

systemctl restart mariadb

[root@mariadb ~]# mysql -umysqli -pmysqli -h 172.25.254.135 ##通过网络登录

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

[root@mariadb ~]# mysql -uroot -p

Enter password:

MariaDB [(none)]> grant insert,update,delete,select on westos.* to mysqli@localhost; ##用户授权

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant select on westos.* to mysqli@'%'; ##用户授权

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants for mysqli@'%'; ##查看用户授权权力

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

| Grants for mysqli@% |

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

| GRANT USAGE ON *.* TO 'mysqli'@'%' IDENTIFIED BY PASSWORD '*586E56A50E9A52581F04204122FC9897CDB17BC4' |

| GRANT SELECT ON `westos`.* TO 'mysqli'@'%' |

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

2 rows in set (0.00 sec)

MariaDB [(none)]> drop user mysqli@'%'; ##删除用户

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> revoke delete on westos.* from mysqli@localhost; ##去除用户授权权利

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> quit

Bye

[root@mariadb ~]# mysql -umysqli -pmysqli -h localhost ##授权测试，成功

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

MariaDB [westos]> select * from linux;

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

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

| user2 | 123 | | |

| user3 | 123 | | |

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

MariaDB [westos]> quit

Bye

7,密码修改

mysqladmin -uroot -pdwly password westos ##修改超级用户密码

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

systemctl stop mariadb.service ##关闭数据库服务

mysqld_safe --skip-grant-tables & ##开启mysql登录接口并忽略授权表

mysql ##不用密码，直接登录

update mysql.user set Password=password('123') where user='root'; ##更改超级用户密码

ps aux|grep mysql ##过滤并强制关闭掉的所有mysql进程

kill -9 mysqlpid

systemctl start mariadb ##开启数据库服务

测试登录:

[root@mariadb ~]# mysql -uroot -p123

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

###########################数据库的网页管理##########################(截图118)

1，安装

yum install httpd php php-mysql -y

systemctl start httpd

systemctl enable httpd

systemctl stop firewalld

systemctl disable firewalld

需要下载

phpMyAdmin-3.4.0-all-languages.tar.bz2

tar jxf phpMyAdmin-3.4.0-all-languages.tar.bz2 -C /var/www/html/

mv phpMyAdmin-3.4.0-all-languages/ mysqladmin

cd mysqladmin

cp -p config.sample.inc.php config.inc.php

vim config.inc.php

$cfg['blowfish_secret'] = 'mysql';

systemctl restart httpd

如图一

测试：

访问

http：//172.25.254.135/mysqladmin

如图二

阅读全文

0 0