ubuntu24.04离线安装deb格式的mysql-community-8.4.4

1下载解压​ 参考 https://blog.csdn.net/2202_76101487/article/details/145967039 下载页面选择不同的版本 https://dev.mysql.com/downloads/mysql/ ubuntu24 下载 wget https://cdn.mysql.com//Downloads/MySQL-8.4/mysql-server_8.4.7-1ubuntu24.04_amd64.deb-bundle.tar 解压 tar -xvf mysql-server_8.4.7-1ubuntu24.04_amd64.deb-bundle.tar ubuntu22 下载8.0.35版的的mysql: wget https://cdn.mysql.com/archives/mysql-8.0/mysql-server_8.0.35-1ubuntu22.04_amd64.deb-bundle.tar 解压 tar -xvf mysql-server_8.0.35-1ubuntu22.04_amd64.deb-bundle.tar ​2卸载旧的mysql程序查询安装的mysql: dpkg -l | grep mysql 停止mysql进程 service mysql stop 删除安装目录 /var/lib/mysql /etc/mysql /var/log/mysql 卸载Mysql: dpkg --purge mysql-community-client-core dpkg --purge mysql-community-client-plugins 或者 apt remove --purge mysql*3用dpkg -i命令批量安装安装依赖 apt -y install mecab-ipadic mecab-utils mecab-ipadic-utf8 libnuma1 libmecab2 psmisc needrestart 批量安装 dpkg -i mysql-common_8.4.7-1ubuntu24.04_amd64.deb mysql-client_8.4.7-1ubuntu24.04_amd64.deb mysql-community-client_8.4.7-1ubuntu24.04_amd64.deb mysql-community-client-core_8.4.7-1ubuntu24.04_amd64.deb mysql-community-client-plugins_8.4.7-1ubuntu24.04_amd64.deb mysql-community-server-core_8.4.7-1ubuntu24.04_amd64.deb mysql-community-server_8.4.7-1ubuntu24.04_amd64.deb 如果是ubuntu22,则只能安装mysql-8.0.35 安装命令dpkg -i mysql-common_8.0.35-1ubuntu22.04_amd64.deb mysql-community-client_8.0.35-1ubuntu22.04_amd64.deb mysql-client_8.0.35-1ubuntu22.04_amd64.deb mysql-community-client-core_8.0.35-1ubuntu22.04_amd64.deb mysql-community-client-core_8.0.35-1ubuntu22.04_amd64.deb mysql-community-client-plugins_8.0.35-1ubuntu22.04_amd64.deb mysql-community-server-core_8.0.35-1ubuntu22.04_amd64.deb mysql-community-server_8.0.35-1ubuntu22.04_amd64.deb 安装完成后查看mysql状态 systemctl status mysql 或者 service mysql status选择下载的包4配置账户4.1 常用sql连接到数据库在ubuntu中执行命令然后输入密码 mysql -uroot -p 添加root%用户 CREATE USER root% IDENTIFIED BY mytestaaa; # 赋权限给root%用户: grant all privileges on *.* to root% with grant option; grant all privileges on *.* to adminuser2% with grant option; 刷新配置 flush privileges; # 修改账号密码 ALTER USER rootlocalhost IDENTIFIED BY testAbc123 ; 查看当前Mysql的端口号 show global variables like port;4.2 切换mysql数据路径# 修改配置 cd /etc/mysql/mysql.conf.d/ vim mysqld.cnf # 修改内容 [client] socket /data3/mysqldata/app/mysqld.sock [mysqld] pid-file /data3/mysqldata/app/mysqld.pid socket /data3/mysqldata/app/mysqld.sock datadir /data3/mysqldata/data log-error /data3/mysqldata/logs/error.log slow_query_log_file/mnt/data2/datum/mysqldata/slowquery/sql_slow.log4.3 执行初始化#停止mysql : systemctl stop mysql # 修改文件夹owner: chown -R mysql:mysql /data3/mysqldata/ # 切换到mysql用户 sudo -u mysql -s # 执行初始化 mysqld --initialize --usermysql --datadir/data3/mysqldata/data # 执行完后要启动mysql: systemctl start mysql4.4 配置mysql8的ssl# 执行下面的命令。如果需要输入可以一直按回车 mkdir -p /home/mysql8/keys/ cd /home/mysql8/keys/ openssl genrsa 2048 ca-key.pem openssl req -new -x509 -nodes -days 36500 -key ca-key.pem -out ca-cert.pem openssl req -newkey rsa:2048 -days 36500 -nodes -keyout server-key.pem -out server-req.pem openssl rsa -in server-key.pem -out server-key.pem openssl x509 -req -in server-req.pem -days 36500 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem # 配置/etc/mysql/mysql.conf.d/mysqld.cnf文件 [mysqld] ssl-ca/home/mysql8/keys/ca-cert.pem ssl-cert/home/mysql8/keys/server-cert.pem ssl-key/home/mysql8/keys/server-key.pem # 都配置好了以后,重置Mysql: systemctl restart mysql5优化性能修改配置文件修改mysql配置 vim /etc/mysql/mysql.conf.d/mysqld.cnf在最下面添加下面的内容# 文件名 mysqld.cnf # 文件是被包含进来的 # !includedir /etc/mysql/conf.d/ # !includedir /etc/mysql/mysql.conf.d/ [mysqld] character-set-server utf8mb4 collation-server utf8mb4_unicode_ci # 下面是优化配置 # 参考 https://developer.huawei.com/home/forum/hwc/thread-02109153476715536012-1-1.html port3316 bind-address0.0.0.0 log_binmysql-bin binlog_formatROW # 下面是性能优化 max_connections300 innodb_buffer_pool_instances8 innodb_buffer_pool_size1G # 设置为 CPU 核心数的两倍 innodb_read_io_threads8 # 设置为与 CPU 核心数相匹配 innodb_write_io_threads8 # 批量插入的缓存大小设置为32MB bulk_insert_buffer_size33554432 # 是否开启慢日志记录。1打开0关闭。慢日志查询的默认路径 /var/lib/mysql/YC10-14U-slow.log。 # 查询值 show variables like slow_query%; slow_query_log1 # 慢sql日志文件及路径 slow_query_log_file/home/logs/mysql/slowquery/sql_slow.log ##6执行sql配置mysql参数# 检查当前外链检查参数 SELECT global foreign_key_checks; # 设置外链检查参数 SET foreign_key_checks0; # 检查当前主键重复参数 SELECT global unique_checks; # 设置当前主键重复参数 SET unique_checks0; # 查看批量插入的缓存大小设置为32MB SHOW VARIABLES LIKE bulk_insert_buffer_size; # 批量插入的缓存大小设置为32MB SET bulk_insert_buffer_size33554432 ; 参考笔记 # 是否检查外键。1打开0关闭。 查询值 SELECT foreign_key_checks;只能执行sql来配置 SET foreign_key_checks0; # foreign_key_checks0 # 禁用唯一性检查 。在golang里面通过id设置来解决数据重复。 查询值 SELECT UNIQUE_CHECKS ; 只能执行sql来配置 SET unique_checks0; #unique_checks0 ## 配置好以后核查 # 查询缓存命中率show status like Innodb_buffer_pool_read%;配置完成后重启Mysql, 在ubuntu中执行命令 systemctl restart mysql7参考链接https://blog.csdn.net/qq_39239864/article/details/144562977 https://gitcode.csdn.net/65eec91f1a836825ed79d747.html https://www.bilibili.com/opus/771742178064990243 https://developer.huawei.com/home/forum/hwc/thread-02109153476715536012-1-1.html8常用命令 查看最大连接数默认151 show variables like max_connections ; 查看索引缓冲池最大内存不带单位的话就代表字节可以用M,G为单位例1512默认为128M show variables like innodb_buffer_pool_size; # 显示缓冲池实例数默认为 show variables like innodb_buffer_pool_instances; # 查看外键检查开关。为打开为关闭默认为 SELECT foreign_key_checks; # 查看主键是否重复的开关。为打开为关闭默认为 SELECT unique_checks; # 查看缓存相关的配置 show variables like innodb_buffer_pool%; 查询缓存命中率 show status like Innodb_buffer_pool_read%; # 查询数据库占用的硬盘容量 SELECT table_schema AS Database, SUM(data_length index_length) / 1024 / 1024 AS Size (MB) FROM information_schema.TABLES GROUP BY table_schema ; # mysql备份数据库的所有数据(需要数据的密码) mysqldump -u root -p my_database backup.sql # mysql备份数据库时指定数据条件二进制导出为16进制格式(示例为id大于5导出1000条) mysqldump -u root -p --hex-blob --whereid 5 limit 1000 my_database my_table backup4.sql # 导出数据不导出建表语句。更安全 mysqldump -u root -p --no-create-info --hex-blob --whereid 5 limit 1000 my_database my_table backup5.sql # 远程连接Mysql(其中-P代表端口号-h代表IP地址): mysql -h 192.168.0.31 -P 3308 -u root -p # 查看版本 SELECT VERSION(); # 如果要把Mysql端口从内网映射出去 iptables -t nat -A PREROUTING -p tcp --dport 13306 -j DNAT --to-destination 10.0.0.115:3306 # 查看Mysql运行状态 mysqladmin -u root -p status 查看日志 cd /var/log/mysql tailf error.log优化实战排查缓存命中查看show status like Innodb_buffer_pool_read%; 的结果 Innodb_buffer_pool_read_requests逻辑读取请求的数量。 Innodb_buffer_pool_readsInnoDB无法从缓冲池满足的逻辑读取数必须直接从磁盘读取。 percent innodb_buffer_pool_read_requests / (innodb_buffer_pool_reads innodb_buffer_pool_read_requests) * 100% 上述的 percent99%则表示当前的buffer pool满足当前的需求。否则需要考虑增加 innodb_buffer_pool_size的值。缓存数据页占比show status like Innodb_buffer_pool_pages%; innodb_buffer_pool_pages_dataInnoDB缓冲池中包含数据的页数。这个数字包括脏页和干净页。使用压缩表时报告的Innodb_buffer_pool_pages_数据值可能大于 percent Innodb_buffer_pool_pages_data / Innodb_buffer_pool_pages_total * 100% 上述的 percent95% 则表示当前的innodb_buffer_pool_size满足当前的需求。否则可以考虑增加 innodb_buffer_pool_size的值。如何判断MySQL使用内存会不会过高可能还有有一些担心所有参数设置完毕后MySQL的占用会过高导致内存溢出那么我们可以算一下他会不会太高。 通过下面的SQL语句 SELECT ((key_buffer_sizeinnodb_buffer_pool_sizeinnodb_log_buffer_size)/1024/1024)((read_rnd_buffer_sizeread_buffer_sizemyisam_sort_buffer_sizesort_buffer_sizejoin_buffer_size)/1024/1024*max_connections); 最终单位为MB 若该值不超过系统可用内存说明还好理论