Windows MySQL 8.x 初始化

梦里梦外; 2024-02-19 12:13 40阅读 0赞

mysql.ini

# For advice on how to change settings please see
    # https://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
    # https://dev.mysql.com/doc/refman/8.0/en/windows-create-option-file.html
    # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
    # *** default location during install, and will be replaced if you
    # *** upgrade to a newer version of MySQL.
    
    [client]
    #password=your_password
    port=3306
    socket="D:/ProgramFiles/MySQL/mysql-commercial-8.0.12-winx64/tmp/mysql.sock"
    
    ssl-ca="D:/ProgramFiles/MySQL/mysql-commercial-8.0.12-winx64/data/ca.pem"
    ssl-cert="D:/ProgramFiles/MySQL/mysql-commercial-8.0.12-winx64/data/client-cert.pem"
    ssl-key="D:/ProgramFiles/MySQL/mysql-commercial-8.0.12-winx64/data/client-key.pem"
    
    #utf8mb4
    #character-set-server=utf8mb4
    #collation-server=utf8mb4_unicode_ci
    #lower_case_table_names=1
    default-character-set=utf8mb4
    collation_connection=utf8mb4_unicode_ci
    
    [mysql]
    default-character-set=utf8mb4
    #这个配置段设置启动MySQL服务的条件；在这种情况下，no-auto-rehash确保这个服务启动得比较快。
    no-auto-rehash
    
    connect_timeout=2
    
    [mysqld]
    # The TCP/IP Port the MySQL Server will listen on
    port=3306
    # Set to the amount of RAM for the most important data cache in MySQL.
    # Start at 70% of total RAM for dedicated server, else 10%.
    # innodb_buffer_pool_size=128M
    max_connections=100
    table_open_cache=256
    
    tmp_table_size=32M
    thread_cache_size=8
    
    innodb_data_home_dir="D:/ProgramFiles/MySQL/mysql-commercial-8.0.12-winx64/data/"
    # source 导入大量数据时，可将这个数设置为 0：最不安全，效率最高（0、1、2）
    innodb_flush_log_at_trx_commit=1
    innodb_log_buffer_size=128M
    innodb_buffer_pool_size=128M
    innodb_buffer_pool_instances=32
    innodb_log_file_size=10M
    innodb_thread_concurrency=16
    innodb-autoextend-increment=1000
    # 修改InnoDB为独立表空间模式：每个数据库的每个表都会生成一个数据空间
    innodb_file_per_table=1
    
    join_buffer_size=128M
    sort_buffer_size=32M
    read_rnd_buffer_size=32M
    # MySQL 服务器关闭交互式连接前等待活动的秒数，默认8小时（28800秒）
    interactive_timeout=3000
    # MySQL 服务器关闭非交互连接之前等待活动的秒数，默认8小时（28800秒）
    wait_timeout=3000
    max_allowed_packet=32M
    explicit-defaults-for-timestamp=true
    
    innodb_lock_wait_timeout=50
    innodb_flush_method=normal
    
    # Remove leading # to turn on a very important data integrity option: logging
    # changes to the binary log between backups.
    # log_bin
    
    socket="D:/ProgramFiles/MySQL/mysql-commercial-8.0.12-winx64/tmp/mysql.sock"
    #Path to installation directory. All paths are usually resolved relative to this.
    basedir="D:/ProgramFiles/MySQL/mysql-commercial-8.0.12-winx64"
    #Path to the database root
    datadir="D:/ProgramFiles/MySQL/mysql-commercial-8.0.12-winx64/data/"
    tmpdir="D:/ProgramFiles/MySQL/mysql-commercial-8.0.12-winx64/tmp/"
    log-error="D:/ProgramFiles/MySQL/mysql-commercial-8.0.12-winx64/data/mysql_error.log"
    # 指定 LOAD_FILE、LOAD DATA、SELECT ... INTO OUTFILE 等报表文件的目录
    secure-file-priv="D:/ProgramFiles/MySQL/mysql-commercial-8.0.12-winx64/tmp/"
    open_files_limit=10240
    
    # server_id=.....
    #skip-locking
    
    # The default character set that will be used when a new schema or table is
    # created and no character set is defined
    character-set-client-handshake=FALSE
    character-set-server=utf8mb4
    collation-server=utf8mb4_unicode_ci
    init_connect='SET NAMES utf8mb4'
    
    # The default storage engine that will be used when create new tables when  MyIASM InnoDB
    default-storage-engine=InnoDB
    
    # Remove leading # to set options mainly useful for reporting servers.
    # The server defaults are faster for transactions and fast SELECTs.
    # Adjust sizes as needed, experiment to find the optimal values.
    #join_buffer_size=128M
    #sort_buffer_size=32M
    #read_rnd_buffer_size=32M
    
    slow_query_log=ON
    slow_query_log_file=mysql-slow.log
    long_query_time=2
    
    # 不需要密码验证，直接登录
    #skip-grant-tables
    
    # 启用 MySQL SSL 配置 2016-07-28 13:42
    ssl-ca="D:/ProgramFiles/MySQL/mysql-commercial-8.0.12-winx64/data/ca.pem"
    ssl-cert="D:/ProgramFiles/MySQL/mysql-commercial-8.0.12-winx64/data/server-cert.pem"
    ssl-key="D:/ProgramFiles/MySQL/mysql-commercial-8.0.12-winx64/data/server-key.pem"
    bind-address=*
    
    # Installing and Upgrading MySQL  (2017-02-25 21:03:34)
    # https://dev.mysql.com/doc/refman/5.7/en/installing.html
    
    # 指定时区，不使用 CST（SHOW VARIABLES LIKE '%time_zone%';）2017-10-25 16:44:26
    default-time-zone='+08:00'
    # 使日志时间与系统时间一致（SHOW GLOBAL VARIABLES LIKE 'log_timestamps';） 2018-08-14 17:51:03
    log_timestamps=SYSTEM
    
    [mysqld-8.0]
    #sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
    sql_mode=TRADITIONAl

mysql-initialize.sql

--------------- 安装 MySQL 8.0 服务之前，必须先初始化数据 ----------------------
    -- https://dev.mysql.com/doc/refman/8.0/en/data-directory-initialization-mysqld.html
    --										2018-04-24 16:34:17
    -- 以管理员身份运行 命令行
    
    D:\ProgramFiles\MySQL\mysql-commercial-8.0.12-winx64\bin
    
    -- 移除旧版本的 MySQL 服务
    mysqld --remove MySQL80
    -- 初始化数据库
    mysqld --initialize --user=mysql
    mkdir ..\tmp
    -- 初始化成功后，在 data/<计算机名称>.err 日志文件中有一个临时的root用户密码。
    如： A temporary password is generated for root@localhost: (z?tz+nf*4kC
    
    -- 启动数据库服务（临时）
    mysqld
    
    
    --------------- 修改 root 用户临时密码 -----------------------------------------
    -- 重新启动一个终端（cmd.exe）
    
    D:\ProgramFiles\MySQL\mysql-commercial-8.0.12-winx64\bin
    
    -- 使用上面的密码，连接到MySQL数据库
    mysql -u root -p
    Enter password: <临时密码>
    
    -- 修改 root 用户密码（MySQL8.0之后，使用新的加密规则：caching_sha2_password）
    ALTER USER 'root'@'localhost' IDENTIFIED BY 'the-password';
    --UPDATE user SET password=PASSWORD('the-password') WHERE user='root';
    --UPDATE user SET authentication_string=PASSWORD('the-password') WHERE user='root';
    
    -- 授权
    GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
    -- 刷新系统权限表
    FLUSH PRIVILEGES;
    
    												-- 李远明 2016-07-07 09:12
    
    --------------- 创建 dev 用户，常用 --------------------------------------------
    -- 创建用户
    -- 创建用户：dev，密码：the-password，允许远程登录
    CREATE USER 'dev'@'%' IDENTIFIED BY 'the-password';
    -- 为 dev 用户授权：@'%' 表示对所有非本地主机授权，不包括localhost
    GRANT select,insert,update,delete,create,drop,index,alter ON *.* TO 'dev'@'%' WITH GRANT OPTION;
    GRANT show databases,create temporary tables,create view,show view ON *.* TO 'dev'@'%' WITH GRANT OPTION;
    -- 对localhost授权 https://dev.mysql.com/doc/refman/8.0/en/grant.html
    CREATE USER 'dev'@'localhost' IDENTIFIED BY 'the-password';
    GRANT ALL PRIVILEGES ON *.* TO 'dev'@'localhost' WITH GRANT OPTION;
    FLUSH PRIVILEGES;
    												-- 李远明 2015-09-11 10:39
    
    -------------- 安装 MySQL 服务 -------------------------------------------------
    --以 管理员身份 启动一个终端
    D:\ProgramFiles\MySQL\mysql-commercial-8.0.12-winx64>startup.cmd
    												-- 李远明 2017-12-21 18:01:18
    
    
    -- 删除匿名用户
    use mysql;
    DELETE FROM user WHERE user='';
    FLUSH PRIVILEGES;
    
    -- 设置数据库字符集
    set names UTF8;
    												-- 李远明 2015-09-11 14:03
    charset UTF8;
    												-- 李远明 2015-09-11 17:28
    
    show variables like '%character%';
    show variables like '%collation%';
    SHOW CHARACTER SET;
    
    SET character_set_client='utf8';
    SET character_set_connection='utf8';
    SET character_set_results='utf8';
    
    
    ------------------- MySQL 5.7 新特性 ---------------------------
    --密码可以设置自动失效的时间
    --user表增加了 password_lifetime 这个字段，来设置密码自动失效的时间。
    --这个参数可以在配置文件设置，也可以动态修改。
    [mysqld]
    default_password_lifetime=180（180失效）
    
    [mysqld]
    default_password_lifetime=0（永不失效）
    
    --动态修改
    SET GLOBAL default_password_lifetime = 180;
    ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;（设置某个用户90天失效）
    ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;（用户密码用不失效）
    ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE DEFAULT;（使此用户按照global设置）
    
    
    -- 5.7 加密与压缩函数
    SET block_encryption_mode = 'aes-256-cbc';
    SET @key_str = SHA2('F3229A0B371ED2D9441B830D21A390C3',512);
    SET @init_vector = RANDOM_BYTES(16);
    SET @crypt_str = AES_ENCRYPT('the-password', @key_str, @init_vector);
    SELECT AES_DECRYPT(@crypt_str, @key_str, @init_vector);
    
    --------------------------------------------------
    --新建密码加密规则为mysql_native_password的用户
    CREATE USER 'python'@'%' IDENTIFIED WITH mysql_native_password BY '1234rewq';
    GRANT select,insert,update,delete,create,drop,index,alter ON *.* TO 'python'@'%' WITH GRANT OPTION;
    GRANT show databases,create temporary tables,create view,show view ON *.* TO 'python'@'%' WITH GRANT OPTION;
    GRANT references ON *.* TO 'python'@'%' WITH GRANT OPTION;
    FLUSH PRIVILEGES;

startup.cmd

@echo off
    
    if not exist data\%computername%.pid goto startsvr
    
    net stop MySQL80
    bin\mysqld.exe --remove MySQL80
    
    :startsvr
    echo Installe MySQL service...
    bin\mysqld.exe --install MySQL80 --defaults-file="%cd%\mysql.ini"
    sc config MySQL80 start= demand
    sc description MySQL80 "MySQL Server v8.0.12"
    
    net start MySQL80
    
    IF %ERRORLEVEL% NEQ 0 goto:EOF
    
    echo Starting MySQL terminal... User: dev
    :: cd ./bin
    bin\mysql.exe -u dev -p

shutdown.cmd

@echo off
    
    if not exist data\%computername%.pid goto stopsvr
    
    :stopsvr
    echo Remove MySQL service...
    net stop MySQL80
    bin\mysqld.exe --remove MySQL80