一、MySQL简介
MySQL是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的RDBMS (Relational Database Management System，关系数据库管理系统)应用软件之一。
二、Ubuntu 22.04安装MySQL
1、安装MySQL（apt）
1）安装MySQL
root@ubuntu:~# apt -y update
root@ubuntu:~# apt -y install mysql-server
2）登录MySQL
root@ubuntu:~# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.40-0ubuntu0.22.04.1 (Ubuntu)
Copyright (c) 2000, 2024, 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> select version();
+————————-+
| version() |
+————————-+
| 8.0.40-0ubuntu0.22.04.1 |
+————————-+
1 row in set (0.00 sec)
3）配置MySQL
root@ubuntu:~# sed -i ‘/pid-file/s/# //’ /etc/mysql/mysql.conf.d/mysqld.cnf
root@ubuntu:~# sed -i ‘/socket/s/# //’ /etc/mysql/mysql.conf.d/mysqld.cnf
root@ubuntu:~# sed -i ‘/3306/s/# //’ /etc/mysql/mysql.conf.d/mysqld.cnf
root@ubuntu:~# sed -i ‘/bind-address/s/127.0.0.1/0.0.0.0/g’ /etc/mysql/mysql.conf.d/mysqld.cnf
root@ubuntu:~# sed -i ‘/datadir/s/# //’ /etc/mysql/mysql.conf.d/mysqld.cnf
root@ubuntu:~# sed -i -e ‘/max_connections/s/# //’ -e ‘/max_connections/s/151/1000/’ /etc/mysql/mysql.conf.d/mysqld.cnf
root@ubuntu:~# sed -i ‘/general_log/s/# //g’ /etc/mysql/mysql.conf.d/mysqld.cnf
root@ubuntu:~# sed -i ‘/slow_query_log/s/# //g’ /etc/mysql/mysql.conf.d/mysqld.cnf
root@ubuntu:~# sed -i ‘/server-id/s/# //’ /etc/mysql/mysql.conf.d/mysqld.cnf
root@ubuntu:~# sed -i ‘/log_bin/s/# //’ /etc/mysql/mysql.conf.d/mysqld.cnf
root@ubuntu:~# sed -i ‘/binlog_expire_logs_seconds/s/# //’ /etc/mysql/mysql.conf.d/mysqld.cnf
root@ubuntu:~# sed -i ‘/max_binlog_size/s/100M/512M/’ /etc/mysql/mysql.conf.d/mysqld.cnf
4）重启MySQL
root@ubuntu:~# systemctl restart mysql
root@ubuntu:~# systemctl status mysql
5）配置root密码
root@ubuntu:~# mysql -u root 2>/dev/null -e “ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘G7#Wv8eDQbwdEBCL’;”
root@ubuntu:~# mysql -uroot -pG7#Wv8eDQbwdEBCL
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 9
Server version: 8.0.40-0ubuntu0.22.04.1 (Ubuntu)
Copyright (c) 2000, 2024, 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>
6）常用基本操作
1、启动MySQL
root@ubuntu:~# systemctl start mysql
2、停止MySQL
root@ubuntu:~# systemctl stop mysql
3、重启MySQL
root@ubuntu:~# systemctl restart mysql
4、查询MySQL运行状态
root@ubuntu:~# systemctl status mysql
5、查询MySQL进程
root@ubuntu:~# ps -ef |grep mysql
6、查询MySQL监听端口
root@ubuntu:~# netstat -lntup |grep mysql
7、卸载MySQL
root@ubuntu:~# apt -y –purge autoremove mysql-server
2、安装MySQL（二进制）
1）安装依赖
root@ubuntu:~# apt -y update
root@ubuntu:~# apt -y install wget libncurses5 libnuma-dev
2）下载二进制软件包
root@ubuntu:~# wget https://mirrors.huaweicloud.com/mysql/Downloads/MySQL-8.0/mysql-8.0.29-linux-glibc2.12-x86_64.tar.xz
3）解压
root@ubuntu:~# tar xf mysql-8.0.29-linux-glibc2.12-x86_64.tar.xz
4）重命名
root@ubuntu:~# mv mysql-8.0.29-linux-glibc2.12-x86_64 /usr/local/mysql
5）创建用户和数据库日志目录
root@ubuntu:~# useradd -s /sbin/nologin mysql
root@ubuntu:~# mkdir -p /var/log/mysql
root@ubuntu:~# chown -R mysql:mysql /var/log/mysql
6）配置my.cnf
root@ubuntu:~# vim /etc/my.cnf
# 服务器端配置
[mysqld]
# 数据存储目录
datadir = /data/mysql
# socket通信文件
socket = /tmp/mysql.sock
# 使用Mysql用户启动
user = mysql
# Mysql服务运行的端口号
port = 3306
# 定义error错误文件
log-error = /var/log/mysql/mysql.log
# 开启bin-log日志
log-bin = mysql-bin
# Mysql服务ID号
server-id = 1
# 开启慢查询日志
slow_query_log = 1
# 慢查询日志记录时间为1秒
slow_launch_time = 1
# 定义慢查询日志文件
slow_query_log_file = /var/log/mysql/slow.log
# binlog日志过期清理时间
expire_logs_days = 5
# 定义binlog的模式为Mixed Level模式，默认为Statement Level
binlog_format = MIXED
# 设置binlog日志文件最大大小,达到512M，自动生成新的Binlog文件
max_binlog_size = 512M
# binlog缓存大小
binlog_cache_size = 256m
# 最大binlog缓存大小
max_binlog_cache_size = 512m
# 非事务语句缓存大小
binlog_stmt_cache_size = 256m
# PID文件路径
pid-file = mysqld.pid
# 最大连接数
max_connections = 5000
# 开启记录所有SQL语句记录
general_log = 1
# 记录所有SQL语句文件
general_log_file = /var/log/mysql/general.log
# 开启从库同步数据写入log-bin日志
log_slave_updates = 1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# 设置字符集为utf8
character-set-server = utf8
# 缓存区大小
innodb_buffer_pool_size = 1G
# 缓冲区实例个数
innodb_buffer_pool_instances = 2
sync_binlog = 0
[client]
default-character-set = utf8
port = 3306
socket = /tmp/mysql.sock
[mysql]
default-character-set = utf8
7）初始化数据库
root@ubuntu:~# mkdir -p /data/mysql
root@ubuntu:~# chown -R mysql:mysql /data/mysql
root@ubuntu:~# /usr/local/mysql/bin/mysqld –initialize-insecure –user=mysql –basedir=/usr/local/mysql –datadir=/data/mysql
root@ubuntu:~# ll /data/mysql/
8）创建软链接
root@ubuntu:~# ln -sf /usr/local/mysql/bin/mysql* /usr/bin
9）配置开机启动及启动MySQL
root@ubuntu:~# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
root@ubuntu:~# systemctl daemon-reload
root@ubuntu:~# systemctl start mysql
root@ubuntu:~# systemctl status mysql
10）设置root密码
root@ubuntu:~# mysql -u root 2>/dev/null -e “ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘G7#Wv8eDQbwdEBCL’;”
11）登录MySQL
root@ubuntu:~# mysql -uroot -pG7#Wv8eDQbwdEBCL
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 11
Server version: 8.0.29 MySQL Community Server – GPL
Copyright (c) 2000, 2022, 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> select version();
+———–+
| version() |
+———–+
| 8.0.29 |
+———–+
1 row in set (0.00 sec)
原创文章，作者：admin，如若转载，请注明出处：https://hostingchat.cn/425.html
