freebsd安装mysql 8.4

环境:
Os:freebsd 15

1.搜索可用版本
pkg search mysql8

2.安装 8.4 LTS（推荐）
pkg install -y mysql84-server-8.4.9 mysql84-client-8.4.9

3.开机自启 + 启动
sysrc mysql_enable=yes
service mysql-server start
service mysql-server status

查看端口是否启动
sockstat -l | grep 3306

发现没有初始密码
root@hxl:/var/db/mysql # mysql -h localhost -uroot

设置密码
alter user 'root'@'localhost' identified by 'mysql';
flush privileges;
exit;

这个时候就需要密码登录了
root@hxl:/var/db/mysql # mysql -h localhost -uroot -pmysql

 

4.使用自定义配置文件

pkg install vim
vim /usr/local/etc/mysql/my.cnf

[mysqld]
port=13306
server-id=1
datadir=/var/db/mysql
max_connections = 1000
character_set_server=utf8mb4
collation-server=utf8mb4_general_ci
init_connect='SET collation_connection = utf8mb4_general_ci'
init_connect='SET NAMES utf8mb4'
interactive_timeout=86400
wait_timeout=86400

skip-external-locking
key_buffer_size= 128M
max_allowed_packet=32M
read_buffer_size=2M
sort_buffer_size=1M
join_buffer_size= 128M
innodb_file_per_table= 1
innodb_open_files= 5000
innodb_buffer_pool_size= 4GB
innodb_write_io_threads= 16
innodb_read_io_threads= 16
innodb_thread_concurrency = 0
innodb_purge_threads= 1
innodb_flush_log_at_trx_commit= 2
innodb_log_buffer_size=16M
innodb_log_file_size=512M
innodb_log_files_in_group= 5
innodb_max_dirty_pages_pct= 90
innodb_lock_wait_timeout= 120
bulk_insert_buffer_size= 64M
log_bin_trust_function_creators=1
event_scheduler=1
max_binlog_size=100M
binlog_format=row
long_query_time=1
log_queries_not_using_indexes=on
binlog_cache_size=4MB
skip-name-resolve
skip-slave-start
replicate-ignore-db=information_schema,performance_schema,sys
slave_net_timeout=60
early-plugin-load=""
explicit_defaults_for_timestamp=true
log_slave_updates=1
gtid_mode=ON
enforce_gtid_consistency = ON
lower_case_table_names=0
sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'


[client]
port = 13306
default-character-set = utf8mb4

[mysqldump]
quick
max_allowed_packet = 32M
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

 

3.修改权限
chmod 644 /usr/local/etc/mysql/my.cnf
chown mysql:mysql /usr/local/etc/mysql/my.cnf

 

4.重新启动
service mysql-server stop
service mysql-server start

 

5.重新初始化数据库,修改不区分大小写参数

因为系统默认安装的时候是区分大小写的,我们这里修改为不区分大小写
lower_case_table_names=1

 

停掉服务
service mysql-server stop

 

清空原来的数据
mv /var/db/mysql /var/db/mysql_bak
mkdir /var/db/mysql
chown -R mysql:mysql /var/db/mysql
chmod 755 /var/db/mysql

 

确保 my.cnf 中写死
lower_case_table_names = 1

 

重新初始化并启动
# 初始化
/usr/local/libexec/mysqld --initialize-insecure --user=mysql
# 启动
service mysql-server start

 

重新登录
mysql -h localhost -uroot

重新修改密码
alter user 'root'@'localhost' identified by 'mysql';
flush privileges;
exit;

 

mysql -h localhost -uroot -pmysql -P13306
mysql> show variables like '%lower_case_table_names%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_table_names | 1     |
+------------------------+-------+
1 row in set (0.01 sec)