mysql脚本安装以及常用操作

我还能再学点

已于 2023-06-06 11:49:10 修改

阅读量380

点赞数

文章标签：服务器 linux 运维

于 2023-06-05 16:00:58 首次发布

本文链接：https://blog.csdn.net/youcan_doit/article/details/131047271

版权

准备工作

1.确保网卡切换为dhcp，且可以ping通外网

[root@localhost ~] ping www.baidu.com
PING www.baidu.com (124.237.176.4) 56(84) bytes of data.
64 bytes from 124.237.176.4 (124.237.176.4): icmp_seq=1 ttl=128 time=21.3 ms
64 bytes from 124.237.176.4 (124.237.176.4): icmp_seq=2 ttl=128 time=15.8 ms

2.防火墙关闭（systemctl firewalld、setenforce、iptables）

脚本安装

[root@localhost ~] vim mysql_install.sh   #创建文本
[root@localhost ~] chmod 755 mysql_install.sh   #赋予权限 
#!/bin/bash
wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm ;  #获取资源
rpm -ivh mysql57-community-release-el7-11.noarch.rpm ;     #安装Mysql5.7rpm文件
yum install -y mysql-community-server --nogpgcheck ;      #安装Mysql服务器
systemctl start mysqld.service ;                         #启动mysql服务
systemctl status mysqld.service ;                       #检测Mysql服务状态
grep 'temporary password' /var/log/mysqld.log ;        #获取默认root密码
mysql -u root -p                                     #用root用户登录编写完成
[root@localhost ~] sh mysql_install.sh 

6月 05 14:18:37 bogon systemd[1]: Starting MySQL Server...
6月 05 14:18:42 bogon systemd[1]: Started MySQL Server.
2023-06-05T06:18:39.590381Z 1 [Note] A temporary password is generated for root@localhost: ?ia3oE/T!rfj          # ：冒号后面就是一下行要输出的密码
Enter password:   #输入密码

默认密码不好记，修改一下

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.42

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

#显示登录成功，欢迎使用
mysql> ALTER USER 'root'@'localhost'IDENTIFIED BY'123Skl.com';   #修改密码‘’里是你设置的密码
Query OK, 0 rows affected (0.02 sec)    #OK表示成功

MySQL 操作示例

创建一个新的数据库

mysql> CREATE DATABASE mydb;   #尽量用大写，小写有时候系统认不出来
Query OK, 1 row affected (0.02 sec)
mysql> show databases;    #查看数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mydb               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.04 sec)

进入到mydb数据库里，创建一个新的数据表

mysql> use mydb;     #进入
Database changed
mysql>  CREATE TABLE customer (
    ->        id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ->        name VARCHAR(30) NOT NULL,
    ->        email VARCHAR(50),
    ->        created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ->    );                                 #创建新的数据表
Query OK, 0 rows affected (0.01 sec)

在“customer‘表中插入一些新数据

mysql>  INSERT INTO customer (name, email) VALUES ('John Doe', 'john.doe@example.com'),('Jane Smith','jane.smith@example.com');
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

查看’customer‘所有数据

mysql> SELECT*FROM customer;
+----+--------------+--------------------------+---------------------+
| id | name         | email                    | created_date        |
+----+--------------+--------------------------+---------------------+
|  1 | John Doe     | john.doe@example.com     | 2023-06-05 15:14:00 |
|  2 | Jane Smith   | jane.smith@example.com   | 2023-06-05 15:14:00 |
|  3 | chen jiafeng | chen,jiafeng@example.com | 2023-06-05 15:17:15 |
|  4 | lu lu        | lu.lu@example.com        | 2023-06-05 15:17:15 |
+----+--------------+--------------------------+---------------------+
4 rows in set (0.00 sec)

更新 `customer` 表中 ID 为 1 的数据

UPDATE customer SET name='John Smith', email='john.smith@example.com' WHERE id=1;

删除 `customer` 表中 ID 为 2 的数据

mysql> DELETE FROM customer WHERE id=2;
Query OK, 1 row affected (0.00 sec)

从 `customer` 表中选择 `name` 和 `email` 字段，并将结果按 `name` 字段升序排序

 mysql> SELECT name, email FROM customer ORDER BY name ASC;
+--------------+--------------------------+
| name         | email                    |
+--------------+--------------------------+
| chen jiafeng | chen,jiafeng@example.com |
| Jane Smith   | jane.smith@example.com   |
| John Smith   | john.smith@example.com   |
| lu lu        | lu.lu@example.com        |
+--------------+--------------------------+
4 rows in set (0.00 sec)

从 `customer` 表中选择 `name` 和 `email` 字段，并只显示名字中含有字母 `a` 的记录

mysql> SELECT name, email FROM customer WHERE name LIKE '%a%'; 
+--------------+--------------------------+
| name         | email                    |
+--------------+--------------------------+
| chen jiafeng | chen,jiafeng@example.com |
+--------------+--------------------------+
1 row in set (0.00 sec)

MySQL 5.7远程登录

1.登录 MySQL 数据库

[root@bogon etc] mysql -u root -p
Enter password:

2.创建一个新的远程登陆用户”skl1“

mysql> CREATE USER 'skl1'@'%' IDENTIFIED BY 'Skl123.com';
Query OK, 0 rows affected (0.02 sec)

3.授予”skl1“用户完全的权限，这将授予访问所有数据库和所有表的权限。如果你不希望用户访问某个数据库或表，请将 `*.*` 替换为相应的数据库和表名称。

mysql> GRANT ALL PRIVILEGES ON *.* TO 'skl1'@'%';
Query OK, 0 rows affected (0.00 sec)

4.更新权限，并退出

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql> EXIT;
Bye
[root@bogon etc]

5.切换另一台主机来登录mysql

[root@localhost ~] mysql -h192.168.1.122 -uskl1 -pSkl123.com  #输入要远程连接的IP和用户
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.42-log MySQL Community Server (GPL)

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> 
             #成功