mysql配置

mysql配置

安装mysql

• 先关闭Centos8中MySQL默认的AppStream仓库:

[root@host ~]# dnf remove @mysql
[root@host ~]# dnf module reset mysql && dnf module disable mysql

*因为CentOS 8上面没有mysql的仓库,这里用CentOS 7的形势手动创建一个mysql的仓库文件

[root@host ~]# vi /etc/yum.repos.d/mysql-community.repo
写入以下内容
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
gpgcheck=0

[mysql-connectors-community]
name=MySQL Connectors Community
baseurl=http://repo.mysql.com/yum/mysql-connectors-community/el/7/$basearch/
enabled=1
gpgcheck=0

[mysql-tools-community]
name=MySQL Tools Community
baseurl=http://repo.mysql.com/yum/mysql-tools-community/el/7/$basearch/
enabled=1
gpgcheck=0

• 开始安装

[root@host ~]# dnf --enablerepo=mysql57-community install mysql-community-server

配置

启动数据库
[root@host ~]# systemctl start mysqld.service 
[root@host ~]# ss -antl       查看端口3306是否存在
State      Recv-Q     Send-Q          Local Address:Port           Peer Address:Port     Process     
LISTEN     0          128                   0.0.0.0:111                 0.0.0.0:*                    
LISTEN     0          32              192.168.122.1:53                  0.0.0.0:*                    
LISTEN     0          128                   0.0.0.0:22                  0.0.0.0:*                    
LISTEN     0          5                   127.0.0.1:631                 0.0.0.0:*                    
LISTEN     0          80                          *:3306                      *:*                    
LISTEN     0          128                      [::]:111                    [::]:*                    
LISTEN     0          128                      [::]:22                     [::]:*                    
LISTEN     0          5                       [::1]:631                    [::]:*   

去日志中找到初始密码
[root@host ~]# grep 'A temporary password' /var/log/mysqld.log |tail -1
2021-04-28T12:11:07.562857Z 1 [Note] A temporary password is generated for root@localhost: rgm8TDh>2qRu

[root@host ~]# mysql -uroot -p
Enter password:                      这里输入密码

修改密码
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'xialuo123!';  这里注意密码格式
Query OK, 0 rows affected (0.00 sec)

• 安全配置

[root@host ~]# mysql_secure_installation

Securing the MySQL server deployment.

Enter password for user root: 
The 'validate_password' plugin is installed on the server.
The subsequent steps will run with the existing configuration
of the plugin.
Using existing password for root.

Estimated strength of the password: 50 
Change the password for root ? ((Press y|Y for Yes, any other key for No) : no       意解:修改root密码

 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL 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? (Press y|Y for Yes, any other key for No) : 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? (Press y|Y for Yes, any other key for No) : n     意解:禁止root远程登录
Success.

By default, MySQL 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? (Press y|Y for Yes, any other key for No) : 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? (Press y|Y for Yes, any other key for No) : y         意解:现在重新加载特权表
Success.

All done! 

• 创建数据库

mysql> CREATE DATABASE IF NOT EXISTS xialuo;      创建自己的数据库
Query OK, 1 row affected (0.00 sec)

mysql> SHOW DATABASES;           查看当前有哪些数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| xialuo             |
+--------------------+
5 rows in set (0.00 sec)

• 创建表

mysql> use xialuo;      进入xialuo数据库
Database changed
mysql> CREATE TABLE xialuoy (id int NOT NULL,name VARCHAR(100) NOT NULL,age tinyint);   创建xialuoy表(id整数非空,姓名100字节非空,年龄0-255直接的整数)
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLES;    查看当前有哪些表
+------------------+
| Tables_in_xialuo |
+------------------+
| xialuoy          |
+------------------+
1 row in set (0.01 sec)

• 删除表

mysql> DROP TABLE xialuoy;      删除xialuoy表
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW TABLES;       查看当前有哪些表
Empty set (0.00 sec)

• 删除数据库

mysql> DROP DATABASE IF EXISTS xialuo;     删除数据库xialuo
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW DATABASES;         查看当前有哪些数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql基础使用

mysql插入

mysql> use xialuoy;    进入数据库

mysql> create table student(id int not null ,name varchar(100) not null,age tinyint);   创建表
 
mysql> desc student;      查看表的结构
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    |                |
| name  | varchar(100) | NO   |     | NULL    |                |
| age   | tinyint(4)   | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> insert into student (id,name,age) value (1,'job',20);       一次插入一条记录
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;   
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | job  |   20 |
+----+------+------+
1 row in set (0.00 sec)

mysql> insert into student (id,name,age) values (2,'tom',20),(3,'roo',18),(4,'tou',15),(5,'bb',13);    一次插入多条记录
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from student;      
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | job  |   20 |
|  2 | tom  |   20 |
|  3 | roo  |   18 |
|  4 | tou  |   15 |
|  5 | bb   |   13 |
+----+------+------+
5 rows in set (0.00 sec)

mysql查找

mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | job  |   20 |
|  2 | tom  |   20 |
|  3 | roo  |   18 |
|  4 | tou  |   15 |
|  5 | bb   |   13 |
+----+------+------+
5 rows in set (0.00 sec)

mysql> select name from student;
+------+
| name |
+------+
| job  |
| tom  |
| roo  |
| tou  |
| bb   |
+------+
5 rows in set (0.00 sec)

mysql> select * from student order by age ;    升序
+----+------+------+
| id | name | age  |
+----+------+------+
|  5 | bb   |   13 |
|  4 | tou  |   15 |
|  3 | roo  |   18 |
|  1 | job  |   20 |
|  2 | tom  |   20 |
+----+------+------+
5 rows in set (0.00 sec)

mysql> select * from student order by age desc;    降序
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | job  |   20 |
|  2 | tom  |   20 |
|  3 | roo  |   18 |
|  4 | tou  |   15 |
|  5 | bb   |   13 |
+----+------+------+
5 rows in set (0.01 sec)

mysql> select * from student order by age limit 2;    查找最小的两个
+----+------+------+
| id | name | age  |
+----+------+------+
|  5 | bb   |   13 |
|  4 | tou  |   15 |
+----+------+------+
2 rows in set (0.00 sec)

mysql> select * from student order by age limit 1,2;     除了第一个,查找后最小的两个
+----+------+------+
| id | name | age  |
+----+------+------+
|  4 | tou  |   15 |
|  3 | roo  |   18 |
+----+------+------+
2 rows in set (0.01 sec)

mysql> select * from student where age >=15;     查找年龄大于等于15的 
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | job  |   20 |
|  2 | tom  |   20 |
|  3 | roo  |   18 |
|  4 | tou  |   15 |
+----+------+------+
4 rows in set (0.00 sec)

mysql> select * from student where age between 15 and 20;  查找大于15小于20的
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | job  |   20 |
|  2 | tom  |   20 |
|  3 | roo  |   18 |
|  4 | tou  |   15 |
+----+------+------+
4 rows in set (0.00 sec)

mysql改写

mysql> update student set age = 10 where name = 'tom';     将名叫tom的年龄改为10
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | job  |   20 |
|  2 | tom  |   10 |
|  3 | roo  |   18 |
|  4 | tou  |   15 |
|  5 | bb   |   13 |
+----+------+------+
5 rows in set (0.00 sec)
 
mysql> update student set name = 'bb'  where name = 'mar';     将名为'bb'的改为'mar'
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | job  |   20 |
|  2 | tom  |   10 |
|  3 | roo  |   18 |
|  4 | tou  |   15 |
|  5 | bb   |   13 |
+----+------+------+
5 rows in set (0.00 sec)

mysql删除

truncate与delete的区别：

语句类型	特点
delete	DELETE删除表内容时仅删除内容，但会保留表结构 DELETE语句每次删除一行，并在事务日志中为所删除的每行记录一项 可以通过回滚事务日志恢复数据 非常占用空间
truncate	删除表中所有数据，且无法恢复 表结构、约束和索引等保持不变，新添加的行计数值重置为初始值 执行速度比DELETE快，且使用的系统和事务日志资源少 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放 对于有外键约束引用的表，不能使用TRUNCATE TABLE删除数据 不能用于加入了索引视图的表

mysql> delete from student where id = 3 ;   删除id为3的记录
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | job  |   20 |
|  2 | tom  |   10 |
|  4 | tou  |   15 |
|  5 | bb   |   13 |
+----+------+------+
4 rows in set (0.00 sec)
mysql> delete from student ;
Query OK, 4 rows affected (0.00 sec)

mysql> select * from student ;
Empty set (0.00 sec)

mysql> desc student ;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   |     | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | tinyint(4)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)




mysql> truncate student ;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from student ;
Empty set (0.00 sec)

mysql> desc student ;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   |     | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | tinyint(4)   | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

授权登录
WITH GRANT OPTION：被授权的用户可将自己的权限副本转赠给其他用户(不建议使用)

授权roo用户在数据库本机上登录访问所有数据库
mysql> GRANT ALL ON *.* TO 'roo'@'localhost' IDENTIFIED BY 'xiaLUO123!';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> GRANT ALL ON *.* TO 'roo'@'127.0.0.1' IDENTIFIED BY 'xiaLUO123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

授权teng用户在192.168.149.133上远程登录访问xialuo1数据库
mysql>  GRANT ALL ON *.* TO 'roo'@'192.168.149.133' IDENTIFIED BY 'xiaLUO123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

授权roo用户在所有位置上远程登录访问xialuo1数据库
mysql> GRANT ALL ON *.* TO 'roo'@'%' IDENTIFIED BY 'xiaLUO123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

查看授权

mysql>  SHOW GRANTS;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

查看指定用户roo的授权信息
mysql>  SHOW GRANTS FOR roo ;
+------------------------------------------+
| Grants for roo@%                         |
+------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'roo'@'%' |
+------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR 'roo'@'localhost';
+--------------------------------------------------+
| Grants for roo@localhost                         |
+--------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'roo'@'localhost' |
+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR 'roo'@'127.0.0.1';
+--------------------------------------------------+
| Grants for roo@127.0.0.1                         |
+--------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'roo'@'127.0.0.1' |
+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW GRANTS FOR 'roo'@'192.168.149.133';
+----------------------------------------------------------------+
| Grants for roo@192.168.149.133                                 |
+----------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'roo'@'192.168.149.133'         |
| GRANT ALL PRIVILEGES ON `xialuo1`.* TO 'roo'@'192.168.149.133' |
+----------------------------------------------------------------+
2 rows in set (0.00 sec)