windows2012安装mysql5.7(配置文件目录带双斜杠)
环境:
OS:windows 2012
DB:mysql 5.7.29
1.下载编译好的mysql
mysql-5.7.29-winx64.zip
2.解压压缩包到D盘
解压后命名文件夹名称为
D:\mysql57
3.创建相关目录
D:\mysql57\data
D:\mysql57\conf
D:\mysql57\secure_file
D:\mysql57\mysqllog\binlog
D:\mysql57\mysqllog\logfile
D:\mysql57\mysqllog\relaylog
3.编辑参数文件
D:\mysql57\conf\my.ini
注意文件目录是双\
[mysqld]
port=13306
server-id=1
basedir=D:\\mysql57
datadir=D:\\mysql57\\data
max_connections = 10000
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
query_cache_size=32M
read_buffer_size=2M
sort_buffer_size=128M
join_buffer_size= 128M
innodb_file_per_table= 1
innodb_open_files= 5000
innodb_buffer_pool_size= 8G
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
myisam_sort_buffer_size=64M
myisam_max_sort_file_size= 10G
myisam_repair_threads= 1
log_bin_trust_function_creators=1
event_scheduler=1
max_binlog_size=100M
binlog_format=row
log-bin=D:\\mysql57\\mysqllog\\binlog\\binlog.bin
slow_query_log=on
slow_query_log_file=D:\\mysql57\\mysqllog\\logfile\\slow-query.log
long_query_time=1
log_queries_not_using_indexes=on
log-error=D:\\mysql57\\mysqllog\\logfile\\mysql-err.log
binlog_cache_size=4MB
skip-host-cache
skip-name-resolve
expire_logs_days=15
skip-slave-start
relay-log-index=D:\\mysql57\\mysqllog\\relaylog\\slave-relay-bin.index
relay-log=D:\\mysql57\\mysqllog\\relaylog\\relaylog-binlog
replicate-ignore-db=information_schema,performance_schema,sys
slave_net_timeout=60
language="D:\\mysql57\\share\\english"
early-plugin-load=""
explicit_defaults_for_timestamp=true
log_slave_updates=1
gtid_mode=ON
enforce_gtid_consistency = ON
lower_case_table_names=1
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
secure_file_priv=D:\\mysql57\\secure_file
##skip-grant-tables
[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
内存根据机器实际情况配置
4.执行修复程序
DirectXRepair-v3.8.zip,解压之后安装先后顺序执行如下文件
DirectX Repair.exe（需要先修复.NET Framework 3.5才能执行）
DirectXRepair-v3.8.zip下载地址:
链接：https://pan.baidu.com/s/1wD2CUzAYXifww2sjvlb4bg
提取码：m19l
若运行修复程序报如下错误:
无法安装以下功能:.NET Framework 3.5
解决办法:
https://www.yingsoo.com/news/servers/72231.html
选择WEB服务器(IIS)-->选择 NET3.5
5.初始化数据库
C:\Users\huangxueliang>cd \
C:\>d:
D:\>cd mysql57
D:\mysql57>cd bin
mysqld --initialize --user=mysql --basedir=D:\mysql57 --datadir=D:\mysql57\data --lc_messages_dir=D:\mysql57\share --lc_messages=en_US
找到如下文件:D:\mysql57\data\WIN-ADServer.err,里面包含有初始化密码
2023-05-06T06:56:59.375538Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2023-05-06T06:57:09.765737Z 0 [Warning] InnoDB: New log files created, LSN=45790
2023-05-06T06:57:10.274477Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2023-05-06T06:57:10.366453Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 38eaefd4-ebdb-11ed-9eca-fa163e272d57.
2023-05-06T06:57:10.441001Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2023-05-06T06:57:12.242231Z 0 [Warning] CA certificate ca.pem is self signed.
2023-05-06T06:57:12.457814Z 1 [Note] A temporary password is generated for root@localhost: Prb<9Cl0QpSE
6.设置环境变量
MYSQL_HOME=E:\mysql57
PATH=%MYSQL_HOME%\bin
7.安装mysql服务
C:\Users\huangxueliang>cd \
C:\>d:
D:\>cd mysql57
D:\mysql57>cd bin
D:\mysql57\bin>mysqld.exe install MySQL57 --defaults-file="E:\mysql57\conf\my.ini"
Service successfully installed.
这个时候在win 服务找到MySQL57,尝试启动，若无法启动又找不到相关日志的话可以在命令行下启动，看是否有什么错误信息
mysqld.exe --defaults-file="E:\mysql57\conf\my.ini"
8.在参数文件中添加跳过授权表
skip-grant-tables
否则无法登录
D:\mysql57\bin>mysql -h localhost -uroot -p -P13306
Enter password: ************
ERROR 1130 (HY000): Host '::1' is not allowed to connect to this MySQL server
因为windows使用ipv6,但是mysql.user表里面没有相应的项.
9.登录mysql
mysql -h localhost -uroot -p -P13306
修改权限表
update mysql.user set host='%' where user='root';
10.注释掉跳过权限表然后重启
##skip-grant-tables
重新登录修改密码(root)
mysql -h localhost -uroot -p -P13306
这个时候需要输入初始化生成的密码，登录进去后再修改密码
set password=password('mysql');
若是觉得上面开启了%让所有ip可以访问的话,可以创建一个只能本地的ipv6的项，让本机访问
grant all on *.* to 'root'@'::1' identified by 'mysql';
11.定时备份数据库
dumpbak.bat文件内容
echo off
cls
echo 正在初始化环境变量……
echo.
set backup_date=%date:~0,4%%date:~5,2%%date:~8,2%
set db_name=db_hncdc
echo %backup_date%
mysqldump -h localhost -uroot -p"mysql" -P13306 %db_name% --default-character-set=utf8 --set-gtid-purged=OFF --skip-tz-utc --skip-disable-keys --skip-add-locks --single-transaction --master-data=2 >G:\mysqlbak\%db_name%_%backup_date%bak.sql
set dbbak_dir=G:\mysqlbak\
echo 删除15天前的备份文件……
echo.
forfiles /P %dbbak_dir% /D -15 -M *bak.sql /C "cmd /c del @file"
