1、centos7 中安装 mysql 8.x
(1)下载安装包
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.33-1.el7.x86_64.rpm-bundle.tar
(2)解压
tar -xf mysql-8.0.33-1.el7.x86_64.rpm-bundle.tar
(3)卸载maridb
yum -y remov mariadb-libs.x86_64
(4)下载net-tools
yum -y install net-tools
(5)下载rpm -ivh mysql-community-server-8.0.33-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.33-1.el7.x86_64.rpm 按要求一个一个依赖包下载,最后达到server软件包成功下载即可
(6)修改配置文件
vim /etc/my.cnf //添加explicit_defaults_for_timestamp=true rm -rf /var/lib/mysql/*
(7)初始化配置mysql
mysqld --initialize
(8)启动mysql服务
systemctl start mysqld.service
(9)设置mysql开机自启
systemctl enable mysqld
(10)获得初始化密码
cat /var/log/mysqld.log | grep password 2024-08-02T02:04:03.141224Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: !iI-ht4hXM5,
(11)使用初始密码登陆
mysql -uroot -p !iI-ht4hXM5,
(12)设置密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Root@123456'; 在mysql内将密码修改为Root@123456
(13)修改远程权限
create user 'root'@'%' identified with mysql_native_password by 'Root@123456'; grant all privileges on *.* to 'root'@'%' with grant option; flush privileges;
2、mysql基础操作
(1)创建库
mysql> create datababse test charset utf8; Query OK, 1 row affected, 1 warning (0.01 sec)
(2)查看库
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.00 sec)
(3)删除库
mysql> drop database hh; Query OK, 0 rows affected (0.04 sec)
(4)选择使用数据库
mysql> use test; Database changed
(5)创建表
mysql> create table student( -> id int not null auto_increment, -> name varchar(20) not null, -> passwd varchar(20) not null, -> primary key (id) -> )comment = 'us'; Query OK, 0 rows affected (0.05 sec)
(6)查看表
mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | student | | user | +----------------+ 2 rows in set (0.01 sec)
(7)删除表
mysql> drop table student; Query OK, 0 rows affected (0.03 sec)
(8)查看表结构
mysql> describe user; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | username | varchar(45) | NO | UNI | NULL | | | password | varchar(45) | NO | | NULL | | +----------+-------------+------+-----+---------+----------------+ 3 rows in set (0.01 sec)
(9)查看创建表代码
mysql> show create table user; +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | user | CREATE TABLE `user` ( `id` int NOT NULL AUTO_INCREMENT, `username` varchar(45) NOT NULL, `password` varchar(45) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `username_UNIQUE` (`username`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3 COMMENT='us' | +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
(10)新增列
mysql> alter table user add column realname varchar(20) null after password; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
(11)修改列名
mysql> alter table user change column realname zsxm varchar(20) null default null; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0
(12)删除列
mysql> alter table user drop column zsxm; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0
(13)添加记录
mysql> insert into user(id,username,password) values(5,'小六','xiaoliu'),(6,'小七','xiaoqi'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0
(14)查看记录
mysql> select * from user; +----+----------+----------+ | id | username | password | +----+----------+----------+ | 1 | 张三 | 123 | | 2 | 赵六 | zhaoliu | | 3 | 王五 | wangwu | | 4 | 李四 | lisi | | 5 | 小六 | xiaoliu | | 6 | 小七 | xiaoqi | +----+----------+----------+ 6 rows in set (0.00 sec)
(15)删除记录
mysql> delete from user where username='张三';
(16)修改记录
mysql> update user set password=123 where username='小六'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0
3、mysql图示化操作软件使用
打开软件
点击加号-------------输入连接名字(随便)------------hostname(数据库主机的ip地址)----------------------ok
点击mysql02
输入密码
进入软件
如果出现以下页面:1、检查防火墙 2、查看是否为远程授权