mysql基础

来源：互联网发布：js 上传文件的原理编辑：程序博客网时间：2024/06/06 01:12

mysql

yum install mysql mysql-server

mysqladmin -uroot -predhat password westos 修改本地mysql root密码
mysqladmin -uroot -predhat -h 192.168.0.188 password westos 修改远程192.168.0.188 mysql服务器 root密码

mysql_secure_installation 第一次安装mysql以后通过这条命令可以对mysql进行设置

mysql -uroot -predhat 从本机登录mysql数据库

show databases; 显示数据库
use mysql; 进入数据库
show tables; 显示数据库中的表
desc user; 查看user表的数据结构
flush privileges; 刷新数据库信息
select host.user,password from user; 查询user表中的host，user，password字段

mysql里面命令都要大写（英文字母）

create database westos; 创建westos数据库
use westos;
create table linux( 创建表，username，password字段（即列）
username varchar(50) not null, name 字符串长度50个字节，不能为空
password varchar(50) not null
);
select * from mysql.user; 查询mysql库下的user表中的所有
alter table linux add age varchar(4); 添加age字段到linux表中
ALTER TABLE linux DROP age 删除age字段
ALTER TABLE linux ADD age VARCHAR(5) AFTER name 在name字段后添加字段age

show tables;
desc linux;

insert into linux values (‘user1’,’passwd1’); 在linux表中插入值为username = user1，password = password1
update linux set password=password(‘passwd2’) where username=user1; 更新linux表中user1 的密码为password2
delete from linux where username=user1; 删除linux表中user1的所以内容

grant select on . to user1@localhost identified by ‘passwd1’; 授权user1 密码为passwd1 并且只能在本地
查询数据库的所以内容
grant all on mysql.* to user2@’%’ identified by ‘passwd2’; 授权user2 密码为passwd2 可以从远程任意
主机登录 mysql 并且可以对mysql数据库任意操作

备份
/var/lib/mysql
mysqldump -uroot -predhat mysql > mysql.bak 备份mysql库到mysql.bak

mysql -uroot -predhat westos < mysql.bak 恢复mysql.bak 到westos库

为将前面的内容给到后后名的文件
< 将后面的内荣恢复到<前面的文件里

mysql 密码恢复
/etc/init.d/mysqld stop

mysqld_safe –skip-grant-tables & 跳过grant-tables授权表不需要认证登录本地mysql 数据库

update mysql.user set password=password(‘westos’) where user=’root’; 更新mysql.user 表中条件为root用户的密码
为新密码 westos

/etc/init.d/mysql restart

phpmyadmin
yum install php php-mysql httpd mysql mysql-server

tar jxf phpmyadmin-*.tar.bz2 -C /var/www/html
mv phpmyadmin phpadmin
cp config.sample.inc.php config.inc.php
vim config.inc.php
add
$cfg[‘blowfish_secret’] = ‘test’;

/etc/init.d/httpd start
http://192.168.0.188/phpadmin

实验部分：

1.安装mariadb

[root@client yum.repos.d]# yum search mariadb
Loaded plugins: langpacks
rhel_dvd | 4.1 kB 00:00
(1/2): rhel_dvd/group_gz | 136 kB 00:00
(2/2): rhel_dvd/primary_db | 3.6 MB 00:00
============================= 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.x86_64 : The shared libraries required for MariaDB/MySQL clients
mariadb-libs.i686 : 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.

[root@client yum.repos.d]# yum install mariadb-server.x86_64 -y

root@client ~]# systemctl start mariadb

（真机连接虚拟机后无法启动服务，在虚拟机里面敲键盘，内容不管控）

[root@client ~]# mysql 可以直接进入mysql

MariaDB [(none)]> SHOW DATABASES; 显示数据库，命令要大写，并以“；”分号结尾，回车执行
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| test |
+——————–+
4 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

数据库的端口不显示。

[root@client ~]# netstat -antlpe | grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 27 56678 3781/mysqld
[root@client ~]# vim /etc/my.cnf
第10行写上： skip-networking=1
[root@client ~]# systemctl restart mariadb
[root@client ~]# netstat -antlpe | grep mysql
[root@client ~]#

[root@client ~]# systemctl restart mariadb
[root@client ~]# netstat -antlpe | grep mysql
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 27 56678 3781/mysqld
[root@client ~]# vim /etc/my.cnf
[root@client ~]#
[root@client ~]# systemctl restart mariadb
[root@client ~]# netstat -antlpe | grep mysql

第一次安装mysql后，对mysql进行设置：

[root@client ~]# 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
… 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!

（root用户密码设置为westos）

[root@client ~]# mysql -uroot -pwestos 以root用户登陆mysql 的命令格式。
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 5.5.44-MariaDB MariaDB Server

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

MariaDB [(none)]> CREATE DATABASE westos; 创建westos 数据库
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> USE westos
Database changed
MariaDB [westos]> CREATE TABLE linux( 建立linux的表格
-> username varchar(50) not null,
-> password varchar(50) not null);
Query OK, 0 rows affected (0.29 sec)

MariaDB [westos]> SHOW TABLES
-> ;
+——————+
| Tables_in_westos |
+——————+
| linux |
+——————+
1 row in set (0.00 sec)
MariaDB [westos]> INSERT INTO linux VALUES (‘user1’,’123’) 在linux表格中输入内容。
-> ;
Query OK, 1 row affected (0.05 sec)

MariaDB [westos]> DELETE from linux WHERE username=’user1’; 从linux中删除user1以及user1的相关信息
Query OK, 1 row affected (0.05 sec)

MariaDB [westos]> SELECT * FROM linux;
Empty set (0.00 sec)

MariaDB [westos]> DROP TABLE linux; 删除linux表格
Query OK, 0 rows affected (0.05 sec)

MariaDB [westos]> SHOW TABLES
-> ；

MariaDB [westos]> ALTER TABLE linux DROP age; 删除linux中的age这一行
Query OK, 1 row affected (0.52 sec)
Records: 1 Duplicates: 0 Warnings: 0

MariaDB [westos]> ALTER TABLE linux ADD age VARCHAR(5) AFTER username; 指定位置，加在username后面。
Query OK, 1 row affected (0.10 sec)
Records: 1 Duplicates: 0 Warnings: 0

MariaDB [westos]> INSERT INTO linux VALUES (‘user1’,’18’,’123’);
Query OK, 1 row affected (0.05 sec)
MariaDB [westos]> INSERT INTO linux VALUES (‘user2’,’28’,’123’);
Query OK, 1 row affected (0.06 sec)

MariaDB [westos]> SELECT * FROM linux;
+———-+——+———-+
| username | age | password |
+———-+——+———-+
| user1 | NULL | 123 |
| user1 | 18 | 123 |
| user2 | 28 | 123 |
+———-+——+———-+
3 rows in set (0.00 sec)

MariaDB [westos]> UPDATE linux set age=’38’ WHERE username=’user1’; 更新表格的内容。
Query OK, 2 rows affected (0.06 sec)
Rows matched: 2 Changed: 2 Warnings: 0

MariaDB [westos]> SELECT * FROM linux;
+———-+——+———-+
| username | age | password |
+———-+——+———-+
| user1 | 38 | 123 |
| user1 | 38 | 123 |
| user2 | 28 | 123 |
+———-+——+———-+
3 rows in set (0.00 sec)

创建新用户为，并指定密码。

MariaDB [westos]> CREATE USER westos@localhost identified by ‘redhat’;
Query OK, 0 rows affected (0.00 sec)

授权新用户，以及移除权力：

[root@client ~]# mysql -uroot -pwestos 用超级用户授权给普通用户

MariaDB [(none)]> GRANT SELECT ON westos.* TO westos@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> GRANT INSERT ON westos.* TO westos@localhost;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW GRANTS FOR westos@localhost
-> ;
+—————————————————————————————————————+
| Grants for westos@localhost |
+—————————————————————————————————————+
| GRANT USAGE ON . TO ‘westos’@’localhost’ IDENTIFIED BY PASSWORD ‘*84BB5DF4823DA319BBF86C99624479A198E6EEE9’ |
| GRANT SELECT, INSERT ON westos.* TO ‘westos’@’localhost’ |
+—————————————————————————————————————+
2 rows in set (0.01 sec)

MariaDB [(none)]> REVOKE INSERT ON westos.* FROM westos@localhost; 移除INSERT 权力
Query OK, 0 rows affected (0.00 sec)

0 0