01初识MySQL

学习《MySQL是怎样运行的》，感谢作者！

原文 #

下载与安装 #

环境Centos7

添加MySQL5.7仓库

sudo rpm -ivh https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

解决证书问题

rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

查看是否添加成功

sudo yum repolist all | grep mysql | grep 启用   
mysql-connectors-community/x86_64   MySQL Connectors Community      启用:    213
mysql-tools-community/x86_64        MySQL Tools Community           启用:     96
mysql57-community/x86_64            MySQL 5.7 Community Server      启用:    642

MySQL安装

sudo yum -y install mysql-community-server

运行与密码修改 #

Centos7中安装目录查看，在/usr/bin中，与Max有所不同

whereis mysql
mysql: /usr/bin/mysql /usr/lib64/mysql /usr/share/mysql /usr/share/man/man1/mysql.1.gz
ls /usr/bin |grep mysql
mysql
mysqladmin
mysqlbinlog
mysqlcheck
mysql_config_editor
mysqld_pre_systemd
mysqldump
mysqldumpslow
mysqlimport
mysql_install_db
mysql_plugin
mysqlpump
mysql_secure_installation
mysqlshow
mysqlslap
mysql_ssl_rsa_setup
mysql_tzinfo_to_sql
mysql_upgrade

添加mysqld目录到环境变量中（这里可省略，因为mysqld默认在/usr/bin中了

启动MySQL(和书上说的启动方式有点不一样，查资料得知，从5.7.6起，不再支持mysql_safe的启动方式)

# 启动MySQL
root@centos7101:~                                                                      
▶ systemctl start mysqld

# 查看MySQL状态
root@centos7101:~                                                                      
▶ systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since 一 2023-04-17 11:43:42 CST; 19s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 2182 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─2182 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

4月 17 11:43:37 centos7101 systemd[1]: Starting MySQL Server...
4月 17 11:43:42 centos7101 systemd[1]: Started MySQL Server.


# 设置为开机启动
root@centos7101:~                                                                      
▶ systemctl enable mysqld

查看MySQL默认密码

cat /var/log/mysqld.log |grep -i 'temporary password'
2023-04-17T03:43:38.995935Z 1 [Note] A temporary password is generated for root@localhost: ampddi9+fpyQ

连接

mysql -uroot -p123456
#或者
mysql -uroot -p
#或者
mysql -hlocalhost -uroot -p123456

为了方便起见，修改密码为123456

# 修改密码强度
set global validate_password_policy=LOW;
#修改密码长度
set global validate_password_length=6;
#修改密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
#刷新权限
flush privileges;

退出

quit
#或者
exit
#或者
\q

客户端与服务端连接过程 #

采用TCP作为服务端和客户端之间的网络通信协议

远程连接前提

#添加一个远程用户
CREATE USER 'root'@'%' IDENTIFIED BY '123456.';
grant all on *.* to 'root'@'%' identified by "123456." with grant option;
#修改用户密码
SET PASSWORD FOR 'root'@'host' = password('123456.');

端口号修改与远程连接

#修改MySQL启动的端口
vim /etc/my.cnf

[mysqld]
port=33062 #新增该行即可
#重启
systemctl restart mysqld
#查看状态
systemctl status mysqld
#查看服务是否启动
netstat -lntup |grep mysql
tcp6       0      0 :::33062                :::*                    LISTEN      4612/mysqld
#远程连接
mysql -hnode2 -uroot -P33062 -p

处理客户端请求
ly-20241212142154070

常用存储引擎：Innodb和MyISAM

查看当前服务器支持的存储引擎
ly-20241212142154242

只有InnoDB是支持事务的且支持分布式事务、部分回滚

存储引擎是负责对表中数据进行读取和写入的

-- 创建表时指定存储引擎
CREATE TABLE engine_demo_table(i int) ENGINE = MyISAM
-- 查看建表语句
mysql> SHOW CREATE TABLE engine_demo_table \G
*************************** 1. row ***************************
       Table: engine_demo_table
Create Table: CREATE TABLE `engine_demo_table` (
  `i` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
-- 修改建表时指定的存储引擎
ALTER TABLE engine_demo_table ENGINE=InnoDB
-- 修改编码
ALTER TABLE engine_demo_table CHARSET=UTF8