Debian 12.7安装MySQL

一、MySQL简介

MySQL是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，属于 Oracle 旗下产品。MySQL是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的RDBMS (Relational Database Management System，关系数据库管理系统)应用软件之一。

二、Debian 12.7安装MySQL

1、安装MySQL（apt）

参考：https://dev.mysql.com/doc/mysql-apt-repo-quick-guide/en/

1）安装依赖

root@debian:~# apt -y update

root@debian:~# apt -y install gnupg

2）添加PGP公钥

root@debian:~# vim /tmp/signature-file

-----BEGIN PGP PUBLIC KEY BLOCK-----
Version: SKS 1.1.6
Comment: Hostname: pgp.mit.edu

mQINBGU2rNoBEACSi5t0nL6/Hj3d0PwsbdnbY+SqLUIZ3uWZQm6tsNhvTnahvPPZBGdl99iW
YTt2KmXp0KeN2s9pmLKkGAbacQP1RqzMFnoHawSMf0qTUVjAvhnI4+qzMDjTNSBq9fa3nHmO
YxownnrRkpiQUM/yD7/JmVENgwWb6akZeGYrXch9jd4XV3t8OD6TGzTedTki0TDNr6YZYhC7
jUm9fK9Zs299pzOXSxRRNGd+3H9gbXizrBu4L/3lUrNf//rM7OvV9Ho7u9YYyAQ3L3+OABK9
FKHNhrpi8Q0cbhvWkD4oCKJ+YZ54XrOG0YTg/YUAs5/3//FATI1sWdtLjJ5pSb0onV3LIbar
RTN8lC4Le/5kd3lcot9J8b3EMXL5p9OGW7wBfmNVRSUI74Vmwt+v9gyp0Hd0keRCUn8lo/1V
0YD9i92KsE+/IqoYTjnya/5kX41jB8vr1ebkHFuJ404+G6ETd0owwxq64jLIcsp/GBZHGU0R
KKAo9DRLH7rpQ7PVlnw8TDNlOtWt5EJlBXFcPL+NgWbqkADAyA/XSNeWlqonvPlYfmasnAHA
pMd9NhPQhC7hJTjCiAwG8UyWpV8Dj07DHFQ5xBbkTnKH2OrJtguPqSNYtTASbsWz09S8ujoT
DXFT17NbFM2dMIiq0a4VQB3SzH13H2io9Cbg/TzJrJGmwgoXgwARAQABtDZNeVNRTCBSZWxl
YXNlIEVuZ2luZWVyaW5nIDxteXNxbC1idWlsZEBvc3Mub3JhY2xlLmNvbT6JAlQEEwEIAD4W
IQS8pDQXw7SF3RKOxtS3s7eIqNN4XAUCZTas2gIbAwUJA8JnAAULCQgHAgYVCgkICwIEFgID
AQIeAQIXgAAKCRC3s7eIqNN4XLzoD/9PlpWtfHlI8eQTHwGsGIwFA+fgipyDElapHw3MO+K9
VOEYRZCZSuBXHJe9kjGEVCGUDrfImvgTuNuqYmVUV+wyhP+w46W/cWVkqZKAW0hNp0TTvu3e
Dwap7gdk80VF24Y2Wo0bbiGkpPiPmB59oybGKaJ756JlKXIL4hTtK3/hjIPFnb64Ewe4YLZy
oJu0fQOyA8gXuBoalHhUQTbRpXI0XI3tpZiQemNbfBfJqXo6LP3/LgChAuOfHIQ8alvnhCwx
hNUSYGIRqx+BEbJw1X99Az8XvGcZ36VOQAZztkW7mEfH9NDPz7MXwoEvduc61xwlMvEsUIaS
fn6SGLFzWPClA98UMSJgF6sKb+JNoNbzKaZ8V5w13msLb/pq7hab72HH99XJbyKNliYj3+KA
3q0YLf+Hgt4Y4EhIJ8x2+g690Np7zJF4KXNFbi1BGloLGm78akY1rQlzpndKSpZq5KWw8FY/
1PEXORezg/BPD3Etp0AVKff4YdrDlOkNB7zoHRfFHAvEuuqti8aMBrbRnRSG0xunMUOEhbYS
/wOOTl0g3bF9NpAkfU1Fun57N96Us2T9gKo9AiOY5DxMe+IrBg4zaydEOovgqNi2wbU0MOBQ
b23Puhj7ZCIXcpILvcx9ygjkONr75w+XQrFDNeux4Znzay3ibXtAPqEykPMZHsZ2sbkCDQRl
NqzaARAAsdvBo8WRqZ5WVVk6lReD8b6Zx83eJUkV254YX9zn5t8KDRjYOySwS75mJIaZLsv0
YQjJk+5rt10tejyCrJIFo9CMvCmjUKtVbgmhfS5+fUDRrYCEZBBSa0Dvn68EBLiHugr+SPXF
6o1hXEUqdMCpB6oVp6X45JVQroCKIH5vsCtw2jU8S2/IjjV0V+E/zitGCiZaoZ1f6NG7ozyF
ep1CSAReZu/sssk0pCLlfCebRd9Rz3QjSrQhWYuJa+eJmiF4oahnpUGktxMD632I9aG+IMfj
tNJNtX32MbO+Se+cCtVc3cxSa/pR+89a3cb9IBA5tFF2Qoekhqo/1mmLi93Xn6uDUhl5tVxT
nB217dBT27tw+p0hjd9hXZRQbrIZUTyh3+8EMfmAjNSIeR+th86xRd9XFRr9EOqrydnALOUr
9cT7TfXWGEkFvn6ljQX7f4RvjJOTbc4jJgVFyu8K+VU6u1NnFJgDiNGsWvnYxAf7gDDbUSXE
uC2anhWvxPvpLGmsspngge4yl+3nv+UqZ9sm6LCebR/7UZ67tYz3p6xzAOVgYsYcxoIUuEZX
jHQtsYfTZZhrjUWBJ09jrMvlKUHLnS437SLbgoXVYZmcqwAWpVNOLZf+fFm4IE5aGBG5Dho2
CZ6ujngW9Zkn98T1d4N0MEwwXa2V6T1ijzcqD7GApZUAEQEAAYkCPAQYAQgAJhYhBLykNBfD
tIXdEo7G1Lezt4io03hcBQJlNqzaAhsMBQkDwmcAAAoJELezt4io03hcXqMP/01aPT3A3Sg7
oTQoHdCxj04ELkzrezNWGM+YwbSKrR2LoXR8zf2tBFzc2/Tl98V0+68f/eCvkvqCuOtq4392
Ps23j9W3r5XG+GDOwDsx0gl0E+Qkw07pwdJctA6efsmnRkjF2YVO0N9MiJA1tc8NbNXpEEHJ
Z7F8Ri5cpQrGUz/AY0eae2b7QefyP4rpUELpMZPjc8Px39Fe1DzRbT+5E19TZbrpbwlSYs1i
CzS5YGFmpCRyZcLKXo3zS6N22+82cnRBSPPipiO6WaQawcVMlQO1SX0giB+3/DryfN9VuIYd
1EWCGQa3O0MVu6o5KVHwPgl9R1P6xPZhurkDpAd0b1s4fFxin+MdxwmG7RslZA9CXRPpzo7/
fCMW8sYOH15DP+YfUckoEreBt+zezBxbIX2CGGWEV9v3UBXadRtwxYQ6sN9bqW4jm1b41vNA
17b6CVH6sVgtU3eN+5Y9an1e5jLD6kFYx+OIeqIIId/TEqwS61csY9aav4j4KLOZFCGNU0FV
ji7NQewSpepTcJwfJDOzmtiDP4vol1ApJGLRwZZZ9PB6wsOgDOoP6sr0YrDI/NNX2RyXXbgl
nQ1yJZVSH3/3eo6knG2qTthUKHCRDNKdy9Qqc1x4WWWtSRjh+zX8AvJK2q1rVLH2/3ilxe9w
cAZUlaj3id3TxquAlud4lWDz
=h5nH
-----END PGP PUBLIC KEY BLOCK-----

root@debian:~# apt-key add /tmp/signature-file

Warning: apt-key is deprecated. Manage keyring files in trusted.gpg.d instead (see apt-key(8)).

OK

root@debian:~# apt-key adv --keyserver pgp.mit.edu --recv-keys A8D3785C

Warning: apt-key is deprecated. Manage keyring files in trusted.gpg.d instead (see apt-key(8)).

Executing: /tmp/apt-key-gpghome.nzN5tTRGue/gpg.1.sh --keyserver pgp.mit.edu --recv-keys A8D3785C

gpg: key B7B3B788A8D3785C: "MySQL Release Engineering <mysql-build@oss.oracle.com>" not changed

gpg: Total number processed: 1

gpg:                     unchanged: 1

3）配置MySQL源

root@debian:~# echo "deb http://repo.mysql.com/apt/debian bookworm mysql-8.0 mysql-tools" >/etc/apt/sources.list.d/mysql-community.list

4）安装MySQL

root@debian:~# apt -y update

root@debian:~# apt -y install mysql-server

5）配置root密码

root@debian:~# mysql -u root 2>/dev/null -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'G7#Wv8eDQbwdEBCL';"

root@debian:~# 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 14

Server version: 8.0.40 MySQL Community Server - GPL

 

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   |

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

1 row in set (0.00 sec)

6）常用基本操作

1、启动MySQL

root@debian:~# systemctl start mysql

2、停止MySQL

root@debian:~# systemctl stop mysql

3、重启MySQL

root@debian:~# systemctl restart mysql

4、查询MySQL运行状态

root@debian:~# systemctl status mysql

5、查询MySQL进程

root@debian:~# ps -ef |grep mysql

6、查询MySQL监听端口

root@debian:~# netstat -lntup |grep mysql

7、卸载MySQL

root@debian:~# apt -y --purge autoremove mysql-server

2、安装MariaDB（apt）

1）安装Mariadb

root@debian:~# apt -y update

root@debian:~# apt -y install mariadb-server

2）登录Mariadb

root@debian:~# mysql

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

Your MariaDB connection id is 31

Server version: 10.11.6-MariaDB-0+deb12u1 Debian 12

 

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

 

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

 

MariaDB [(none)]> select version();

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

| version()                    |

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

| 10.11.6-MariaDB-0+deb12u1 |

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

1 row in set (0.000 sec)

3）配置Mariadb

root@debian:~# sed -i 's/#user/user/' /etc/mysql/mariadb.conf.d/50-server.cnf

root@debian:~# sed -i 's/#datadir/datadir/' /etc/mysql/mariadb.conf.d/50-server.cnf

root@debian:~# sed -i 's/#tmpdir/tmpdir/' /etc/mysql/mariadb.conf.d/50-server.cnf

root@debian:~# sed -i '/bind-address/s/127.0.0.1/0.0.0.0/g' /etc/mysql/mariadb.conf.d/50-server.cnf

root@debian:~# sed -i -e '/max_connections/s/#//' -e '/max_connections/s/100/1000/' /etc/mysql/mariadb.conf.d/50-server.cnf

root@debian:~# sed -i -e '/general_log/s/#//g' -e '/general_log/s/mysql.log/general.log/' /etc/mysql/mariadb.conf.d/50-server.cnf

root@debian:~# sed -i -e '/log_error/s/#//g' -e '/log_error/s/error.log/mariadb.log/' /etc/mysql/mariadb.conf.d/50-server.cnf

root@debian:~# sed -i '/server-id/s/#//' /etc/mysql/mariadb.conf.d/50-server.cnf

root@debian:~# sed -i '/log_bin/s/#//' /etc/mysql/mariadb.conf.d/50-server.cnf

root@debian:~# sed -i -e '/max_binlog_size/s/#/' -e '/max_binlog_size/s/100M/512M/' /etc/mysql/mariadb.conf.d/50-server.cnf

root@debian:~# sed -i '/log_slow_query/s/#//g' /etc/mysql/mariadb.conf.d/50-server.cnf

root@debian:~# sed -i '/skip-external-locking/ilower_case_table_names = 1' /etc/mysql/mariadb.conf.d/50-server.cnf

4）重启Mariadb

root@debian:~# systemctl restart mariadb

root@debian:~# systemctl status mariadb

5）配置root密码

root@debian:~# mysql -u root 2>/dev/null -e "ALTER USER 'root'@'localhost' IDENTIFIED BY 'G7#Wv8eDQbwdEBCL';"

root@debian:~# mysql -uroot -pG7#Wv8eDQbwdEBCL

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

Your MariaDB connection id is 34

Server version: 10.11.6-MariaDB-0+deb12u1-log Debian 12

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]>

6）常用基本操作

1、启动MariaDB

root@debian:~# systemctl start mariadb

2、停止MariaDB

root@debian:~# systemctl stop mariadb

3、重启MariaDB

root@debian:~# systemctl restart mariadb

4、查询MariaDB运行状态

root@debian:~# systemctl status mariadb

5、查询MariaDB进程

root@debian:~# ps -ef |grep mariadb

6、查询MariaDB监听端口

root@debian:~# netstat -lntup |grep mariadb

7、卸载MySQL

root@debian:~# apt -y --purge autoremove mariadb-server

3、安装MySQL（二进制）

1）安装依赖

root@debian:~# apt -y update

root@debian:~# apt -y install wget libncurses5 libnuma-dev

2）下载二进制软件包

root@debian:~# wget https://mirrors.huaweicloud.com/mysql/Downloads/MySQL-8.0/mysql-8.0.29-linux-glibc2.12-x86_64.tar.xz

3）解压

root@debian:~# tar xf mysql-8.0.29-linux-glibc2.12-x86_64.tar.xz

4）重命名

root@debian:~# mv mysql-8.0.29-linux-glibc2.12-x86_64 /usr/local/mysql

5）创建用户和数据库日志目录

root@debian:~# useradd -s /sbin/nologin mysql

root@debian:~# mkdir -p /var/log/mysql

root@debian:~# chown -R mysql:mysql /var/log/mysql

6）配置my.cnf

root@debian:~# 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@debian:~# mkdir -p /data/mysql

root@debian:~# chown -R mysql:mysql /data/mysql

root@debian:~# /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql

root@debian:~# ll /data/mysql/

8）创建软链接

root@debian:~# ln -sf /usr/local/mysql/bin/mysql* /usr/bin

9）配置开机启动及启动MySQL

root@debian:~# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

root@debian:~# systemctl daemon-reload

root@debian:~# systemctl start mysql

root@debian:~# systemctl status mysql

10）设置root密码

root@debian:~# mysql -u root 2>/dev/null -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'G7#Wv8eDQbwdEBCL';"

11）登录MySQL

root@debian:~# 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)