Ubuntu 24.04 二进制方式安装 MySQL 8

安装 MySQL

1. 安装 libmecab2

   apt install -y libaio-dev libaio1t64  libmecab2

2. 从 MySQL 官网下载 MySQL 的安装包

   wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-server_8.0.40-1ubuntu24.04_amd64.deb-bundle.tar -P /usr/local/src

3. 解压 MySQL 安装包

   # 解压 MySQL 软件包
   cd /usr/local/src/ && mkdir mysql && tar xf mysql-server_8.0.40-1ubuntu24.04_amd64.deb-bundle.tar -C mysql/
   
   # 解压后在 mysql 目录会存在以下文件
   # ll mysql/
   total 495124
   drwxr-xr-x 2 root root       4096 Dec 14 14:55 ./
   drwxr-xr-x 4 root root       4096 Dec 14 14:55 ../
   -rw-r--r-- 1 7155 31415   1479134 Sep 18 18:56 libmysqlclient21_8.0.40-1ubuntu24.04_amd64.deb
   -rw-r--r-- 1 7155 31415  28075874 Sep 18 18:56 libmysqlclient-dev_8.0.40-1ubuntu24.04_amd64.deb
   -rw-r--r-- 1 7155 31415     57774 Sep 18 18:56 mysql-client_8.0.40-1ubuntu24.04_amd64.deb
   -rw-r--r-- 1 7155 31415     59074 Sep 18 18:56 mysql-common_8.0.40-1ubuntu24.04_amd64.deb
   -rw-r--r-- 1 7155 31415   2073356 Sep 18 18:56 mysql-community-client_8.0.40-1ubuntu24.04_amd64.deb
   -rw-r--r-- 1 7155 31415   2131712 Sep 18 18:56 mysql-community-client-core_8.0.40-1ubuntu24.04_amd64.deb
   -rw-r--r-- 1 7155 31415   1376574 Sep 18 18:56 mysql-community-client-plugins_8.0.40-1ubuntu24.04_amd64.deb
   -rw-r--r-- 1 7155 31415     69384 Sep 18 18:56 mysql-community-server_8.0.40-1ubuntu24.04_amd64.deb
   -rw-r--r-- 1 7155 31415  27977008 Sep 18 18:56 mysql-community-server-core_8.0.40-1ubuntu24.04_amd64.deb
   -rw-r--r-- 1 7155 31415  34480632 Sep 18 18:56 mysql-community-server-debug_8.0.40-1ubuntu24.04_amd64.deb
   -rw-r--r-- 1 7155 31415 400231108 Sep 18 18:56 mysql-community-test_8.0.40-1ubuntu24.04_amd64.deb
   -rw-r--r-- 1 7155 31415   8825298 Sep 18 18:56 mysql-community-test-debug_8.0.40-1ubuntu24.04_amd64.deb
   -rw-r--r-- 1 7155 31415     57766 Sep 18 18:56 mysql-server_8.0.40-1ubuntu24.04_amd64.deb
   -rw-r--r-- 1 7155 31415     57784 Sep 18 18:56 mysql-testsuite_8.0.40-1ubuntu24.04_amd64.deb

4. 使用以下命令预配置 MySQL 服务器包，执行该命令后我们需要配置数据库 root 用户的密码和一些设置。

   sudo dpkg-preconfigure mysql-community-server_*.deb

5. 安装 MySQL

   sudo dpkg -i mysql-{common,community-client-plugins,community-client-core,community-client,client,community-server-core,community-server,server}_*.deb

配置 MySQL

1. 修改 MySQL 配置文件 /etc/mysql/my.cnf，新增 client 相关配置

   cat >> /etc/mysql/my.cnf << EOF
   
   [client]
   port = 3306
   socket = /var/run/mysqld/mysqld.sock
   default-character-set = utf8mb4
   EOF

2. 修改 MySQL 配置文件 /etc/mysql/conf.d/mysql.cnf，新增 prompt 等配置

   cat >> /etc/mysql/conf.d/mysql.cnf << EOF
   prompt="MySQL [\\d]> "
   no-auto-rehash
   EOF

3. 修改 MySQL 配置文件 /etc/mysql/mysql.conf.d/mysqld.cnf，重新生成内容

   # 备份 mysqld.cnf 文件
   cp /etc/mysql/mysql.conf.d/mysqld.cnf{,.bak}
   
   # 重新生成文件内容
   cat > /etc/mysql/mysql.conf.d/mysqld.cnf << EOF
   [mysqld]
   pid-file        = /var/run/mysqld/mysqld.pid
   socket          = /var/run/mysqld/mysqld.sock
   datadir         = /data/mysql
   log-error       = /var/log/mysql/error.log
   slow_query_log_file = /data/mysql/mysql-slow.log
   default_authentication_plugin = mysql_native_password
   
   user = mysql
   bind-address = 0.0.0.0
   server-id = 1
   init-connect = 'SET NAMES utf8mb4'
   character-set-server = utf8mb4
   
   skip-name-resolve
   back_log = 300
   max_connections = 2631
   max_connect_errors = 6000
   open_files_limit = 65535
   table_open_cache = 1024
   max_allowed_packet = 500M
   binlog_cache_size = 1M
   max_heap_table_size = 8M
   tmp_table_size = 128M
   
   read_buffer_size = 2M
   read_rnd_buffer_size = 8M
   sort_buffer_size = 8M
   join_buffer_size = 8M
   key_buffer_size = 256M
   
   thread_cache_size = 64
   ft_min_word_len = 4
   
   log_bin = mysql-bin
   binlog_format = mixed
   binlog_expire_logs_seconds = 604800
   
   slow_query_log = 1
   long_query_time = 1
   
   performance_schema = 0
   explicit_defaults_for_timestamp
   lower_case_table_names = 1
   skip-external-locking
   default_storage_engine = InnoDB
   innodb_file_per_table = 1
   innodb_open_files = 500
   innodb_buffer_pool_size = 1024M
   innodb_write_io_threads = 4
   innodb_read_io_threads = 4
   innodb_thread_concurrency = 0
   innodb_purge_threads = 1
   innodb_flush_log_at_trx_commit = 2
   innodb_log_buffer_size = 2M
   innodb_redo_log_capacity = 32M
   innodb_max_dirty_pages_pct = 90
   innodb_lock_wait_timeout = 120
   
   bulk_insert_buffer_size = 8M
   myisam_sort_buffer_size = 64M
   myisam_max_sort_file_size = 10G
   
   interactive_timeout = 28800
   wait_timeout = 28800
   
   [mysqldump]
   quick
   max_allowed_packet = 500M
   
   [myisamchk]
   key_buffer_size = 256M
   sort_buffer_size = 8M
   read_buffer = 4M
   write_buffer = 4M
   EOF

4. 优化内存相关配置

   # 获取系统内存大小
   Mem=`free -m | awk '/Mem:/{print $2}'`
   
   # 修改最大连接数
   sed -i "s@max_connections.*@max_connections = $((${Mem}/3))@" /etc/mysql/mysql.conf.d/mysqld.cnf 
   
   # 修改与内存相关的配置
   if [ ${Mem} -gt 1500 -a ${Mem} -le 2500 ]; then
       sed -i 's@^thread_cache_size.*@thread_cache_size = 16@' /etc/mysql/mysql.conf.d/mysqld.cnf
       sed -i 's@^myisam_sort_buffer_size.*@myisam_sort_buffer_size = 16M@' /etc/mysql/mysql.conf.d/mysqld.cnf
       sed -i 's@^key_buffer_size.*@key_buffer_size = 16M@' /etc/mysql/mysql.conf.d/mysqld.cnf
       sed -i 's@^innodb_buffer_pool_size.*@innodb_buffer_pool_size = 128M@' /etc/mysql/mysql.conf.d/mysqld.cnf
       sed -i 's@^tmp_table_size.*@tmp_table_size = 32M@' /etc/mysql/mysql.conf.d/mysqld.cnf
       sed -i 's@^table_open_cache.*@table_open_cache = 256@' /etc/mysql/mysql.conf.d/mysqld.cnf
   elif [ ${Mem} -gt 2500 -a ${Mem} -le 3500 ]; then
       sed -i 's@^thread_cache_size.*@thread_cache_size = 32@' /etc/mysql/mysql.conf.d/mysqld.cnf
       sed -i 's@^myisam_sort_buffer_size.*@myisam_sort_buffer_size = 32M@' /etc/mysql/mysql.conf.d/mysqld.cnf
       sed -i 's@^key_buffer_size.*@key_buffer_size = 64M@' /etc/mysql/mysql.conf.d/mysqld.cnf
       sed -i 's@^innodb_buffer_pool_size.*@innodb_buffer_pool_size = 512M@' /etc/mysql/mysql.conf.d/mysqld.cnf
       sed -i 's@^tmp_table_size.*@tmp_table_size = 64M@' /etc/mysql/mysql.conf.d/mysqld.cnf
       sed -i 's@^table_open_cache.*@table_open_cache = 512@' /etc/mysql/mysql.conf.d/mysqld.cnf
   elif [ ${Mem} -gt 3500 ]; then
       sed -i 's@^thread_cache_size.*@thread_cache_size = 64@' /etc/mysql/mysql.conf.d/mysqld.cnf
       sed -i 's@^myisam_sort_buffer_size.*@myisam_sort_buffer_size = 64M@' /etc/mysql/mysql.conf.d/mysqld.cnf
       sed -i 's@^key_buffer_size.*@key_buffer_size = 256M@' /etc/mysql/mysql.conf.d/mysqld.cnf
       sed -i 's@^innodb_buffer_pool_size.*@innodb_buffer_pool_size = 1024M@' /etc/mysql/mysql.conf.d/mysqld.cnf
       sed -i 's@^tmp_table_size.*@tmp_table_size = 128M@' /etc/mysql/mysql.conf.d/mysqld.cnf
       sed -i 's@^table_open_cache.*@table_open_cache = 1024@' /etc/mysql/mysql.conf.d/mysqld.cnf
   fi

5. 迁移 MySQL 数据目录

   cp -a /var/lib/mysql /data/mysql

6. 修改 AppArmor 配置，配置文件 /etc/apparmor.d/usr.sbin.mysqld

   # 在 Allow data dir access 段添加以下内容
   
   /data/mysql/ r,
   /data/mysql/** rwk,

   修改完后内容如下

   # Allow data dir access
   /var/lib/mysql/ r,
   /var/lib/mysql/** rwk,
   /data/mysql/ r,
   /data/mysql/** rwk,

   重启 AppArmor

   systemctl restart apparmor.service 

7. 由于安装 MySQL 时，表名默认是区分大小写的，要想配置数据库表名不区分大小写，需要重新初始化 MySQL

   # 停止 MySQL 服务
   systemctl stop mysql
   
   # 清空 MySQL 数据目录
   rm -rf /data/mysql/*
   
   # 重新初始化 MySQL
   sudo mysqld --initialize-insecure --lower_case_table_names=1 --user=mysql --basedir=/usr --datadir=/data/mysql

8. 进行 MySQL 安全配置

   mysql_secure_installation

备注

1. Ubuntu 24.04 可以安装 libstring-mkpasswd-perl 软件包来生成用户密码，使用方式为

   mkpasswd.pl -l 12