动手实战-MySQL数据库快速部署实践

2021-09-07 10:38:51

一创建体验资源

本次体验创建并使用了如下资源

ECS服务器

ECS公网地址：

101.132.193.208

ECS登录名：

root

登录密码：

Vg6Et8Wr5A

ECS实例ID：

i-uf66g3m67qx1v27adv3p

IP白名单：

121.43.229.190,120.55.43.55,114.55.110.50,47.98.219.184,47.96.60.0/24,118.31.243.0/24,121.43.224.157,47.99.67.70,112.224.21.220

地域：

华东 2 (上海)

二 MYSQL的安装配置及使用

1 更新yam源

[root@iZuf66g3m67qx1v27adv3pZ ~]# rpm -Uvh http://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm

Retrieving http://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm

warning: /var/tmp/rpm-tmp.gskoxE: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

Preparing... ################################# [100%]

Updating / installing...

1:mysql57-community-release-el7-9 ################################# [100%]

2 安装mysql

[root@iZuf66g3m67qx1v27adv3pZ ~]# yum -y install mysql-community-server --nogpgcheck

Downloading packages:

Delta RPMs disabled because /usr/bin/applydeltarpm not installed.

(1/7): libaio-0.3.109-13.el7.x86_64.rpm | 24 kB 00:00:00

(2/7): mysql-community-common-5.7.37-1.el7.x86_64.rpm | 311 kB 00:00:01

(3/7): mysql-community-libs-5.7.37-1.el7.x86_64.rpm | 2.4 MB 00:00:01

(4/7): mysql-community-libs-compat-5.7.37-1.el7.x86_64.rpm | 1.2 MB 00:00:00

(5/7): postfix-2.10.1-9.el7.x86_64.rpm | 2.4 MB 00:00:00

(6/7): mysql-community-client-5.7.37-1.el7.x86_64.rpm | 25 MB 00:00:06

(7/7): mysql-community-server-5.7.37-1.el7.x86_64.rpm | 174 MB 00:00:44

--------------------------------------------------------------------------------------------------------------------

9/9

Installed:

mysql-community-libs.x86_64 0:5.7.37-1.el7 mysql-community-libs-compat.x86_64 0:5.7.37-1.el7

mysql-community-server.x86_64 0:5.7.37-1.el7

Dependency Installed:

libaio.x86_64 0:0.3.109-13.el7 mysql-community-client.x86_64 0:5.7.37-1.el7

mysql-community-common.x86_64 0:5.7.37-1.el7

Dependency Updated:

postfix.x86_64 2:2.10.1-9.el7

Replaced:

mariadb-libs.x86_64 1:5.5.64-1.el7

Complete!

3 查看mysql版本

[root@iZuf66g3m67qx1v27adv3pZ ~]# mysql -V

mysql Ver 14.14 Distrib 5.7.37, for Linux (x86_64) using EditLine wrapper

4 启动mysql数据库，命令执行成功，没有任何报错。

[root@iZuf66g3m67qx1v27adv3pZ ~]# systemctl start mysqld

5 设置MySQL数据库开机自动启动，命令执行成功，没有任何报错

[root@iZuf66g3m67qx1v27adv3pZ ~]# systemctl enable mysqld

6 配置root密码

6.1 获取mysql数据库root用户初始密码

[root@iZuf66g3m67qx1v27adv3pZ ~]# grep 'temporary password' /var/log/mysqld.log

2022-02-16T02:25:55.057985Z 1 [Note] A temporary password is generated for root@localhost: U&Q2tNk85ot>

6.2 使用初始密码登陆到mysql

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

Enter password:

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

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

mysql> set global validate_password_policy=0; 更改密码安全策略为低，只校验密码长度（最小8位）

Query OK, 0 rows affected (0.00 sec

6.3 更改root用户密码

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '12345678';

Query OK, 0 rows affected (0.00 sec)

6.4 授予root用户远程管理权限

sql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '12345678';

Query OK, 0 rows affected, 1 warning (0.00 sec)

6.5 退出数据库

mysql> exit

Bye

7 使用更改后的root密码登陆数据库

[root@iZuf66g3m67qx1v27adv3pZ ~]# mysql -uroot -p12345678

8 创建test数据库

mysql> create database test;

Query OK, 1 row affected (0.00 sec)

9 显示mysql中现有数据库

mysql> show databases;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| sys |

| test |

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

5 rows in set (0.00 sec)

10 切换到MySQL库

mysql> use mysql;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

显示mysql库中的表

10 mysql> show tables;

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

| Tables_in_mysql |

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

| columns_priv |

| db |

| engine_cost |

| event |

| func |

| general_log |

| gtid_executed |

| help_category |

| help_keyword |

| help_relation |

| help_topic |

| innodb_index_stats |

| innodb_table_stats |

| ndb_binlog_index |

| plugin |

| proc |

| procs_priv |

| proxies_priv |

| server_cost |

| servers |

| slave_master_info |

| slave_relay_log_info |

| slave_worker_info |

| slow_log |

| tables_priv |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

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

31 rows in set (0.00 sec)

11 创建测试表及数据

11.1 创建emp表

mysql> create table emp(

-> empno int unsigned auto_increment primary key COMMENT '雇员编号',

-> ename varchar(15) COMMENT '雇员姓名',

-> job varchar(10) COMMENT '雇员职位',

-> mgr int unsigned COMMENT '雇员对应的领导的编号',

-> hiredate date COMMENT '雇员的雇佣日期',

-> sal decimal(7,2) COMMENT '雇员的基本工资',

-> comm decimal(7,2) COMMENT '奖金',

-> deptno int unsigned COMMENT '所在部门'

-> )ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='雇员表';

Query OK, 0 rows affected (0.02 sec）

11.2 向emp表中插入数据

mysql> INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30);

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'1981-4-2',2975,NULL,20);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp VALUES (7788,'SCOTT','ANALYST',7566,'87-7-13',3000,NULL,20);

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO emp VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp VALUES (7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,30);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp VALUES (7876,'ADAMS','CLERK',7788,'87-7-13',1100,NULL,20);

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO emp VALUES (7900,'JAMES','CLERK',7698,'1981-12-3',950,NULL,30);

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO emp VALUES (7902,'FORD','ANALYST',7566,'1981-12-3',3000,NULL,20);

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO emp VALUES (7934,'MILLER','CLERK',7782,'1982-1-23',1300,NULL,10);

Query OK, 1 row affected (0.01 sec)

11.3 退出mysql

mysql> exit

Bye

12 导出导入数据

12.1 使用mysqldump导出test数据，需输入root密码

[root@iZuf66g3m67qx1v27adv3pZ ~]# mysqldump -uroot -p test >test.sql

Enter password:

检查导出的sql文件

[root@iZuf66g3m67qx1v27adv3pZ ~]# cat test.sql

DROP TABLE IF EXISTS `emp`;

CREATE TABLE `emp` (

`empno` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '雇员编号',

`ename` varchar(15) DEFAULT NULL COMMENT '雇员姓名',

`job` varchar(10) DEFAULT NULL COMMENT '雇员职位',

`mgr` int(10) unsigned DEFAULT NULL COMMENT '雇员对应的领导的编号',

`hiredate` date DEFAULT NULL COMMENT '雇员的雇佣日期',

`sal` decimal(7,2) DEFAULT NULL COMMENT '雇员的基本工资',

`comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',

`deptno` int(10) unsigned DEFAULT NULL COMMENT '所在部门',

PRIMARY KEY (`empno`)

) ENGINE=InnoDB AUTO_INCREMENT=7935 DEFAULT CHARSET=utf8 COMMENT='雇员表';

LOCK TABLES `emp` WRITE;

INSERT INTO `emp` VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800.00,NULL,20),(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600.00,300.00,30),(7521,'WARD','SALESMAN',7698,'1981-02-22',1250.00,500.00,30),(7566,'JONES','MANAGER',7839,'1981-04-02',2975.00,NULL,20),(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250.00,1400.00,30),(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850.00,NULL,30),(7782,'CLARK','MANAGER',7839,'1981-06-09',2450.00,NULL,10),(7788,'SCOTT','ANALYST',7566,'1987-07-13',3000.00,NULL,20),(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000.00,NULL,10),(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500.00,0.00,30),(7876,'ADAMS','CLERK',7788,'1987-07-13',1100.00,NULL,20),(7900,'JAMES','CLERK',7698,'1981-12-03',950.00,NULL,30),(7902,'FORD','ANALYST',7566,'1981-12-03',3000.00,NULL,20),(7934,'MILLER','CLERK',7782,'1982-01-23',1300.00,NULL,10);

UNLOCK TABLES;

12.3 登陆数据库，drop掉测试表

mysql> use test;

mysql> show tables;

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

| Tables_in_test |

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

| emp |

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

1 row in set (0.00 sec)

mysql> drop table emp;

Query OK, 0 rows affected (0.01 sec)

mysql> show tables;

Empty set (0.00 sec)

sql> exit

Bye

12.4 导入数据

[root@iZuf66g3m67qx1v27adv3pZ ~]# mysql -uroot -p -Dtest<test.sql

Enter password:

12.5 检查导入的数据

[root@iZuf66g3m67qx1v27adv3pZ ~]# mysql -uroot -p12345678

mysql> use test;

mysql> show tables;

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

| Tables_in_test |

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

| emp |

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

1 row in set (0.00 sec)

mysql> select * from emp;

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

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

| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |

| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |

| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |

| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |

| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |

| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |

| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |

| 7788 | SCOTT | ANALYST | 7566 | 1987-07-13 | 3000.00 | NULL | 20 |

| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |

| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |

| 7876 | ADAMS | CLERK | 7788 | 1987-07-13 | 1100.00 | NULL | 20 |

| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |

| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |

| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |

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

14 rows in set (0.00 sec)

12.6 drop掉测试表后，用另一种方式导入

mysql> drop table emp;

Query OK, 0 rows affected (0.01 sec)

mysql> show tables;

Empty set (0.00 sec)

mysql> source test.sql

Query OK, 14 rows affected (0.00 sec)

Records: 14 Duplicates: 0 Warnings: 0