windows 下 mysql server 安装

1. 下载安装包

下载后解压到目标目录，例如 D:\mysql-8.0.46-winx64。

2. 创建配置文件

在 MySQL 解压目录下新建 my.ini 文件，内容如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
[mysqld]
port=3306
basedir=D:\\mysql-8.0.46-winx64
datadir=D:\\mysql-8.0.46-winx64\\data
max_connections=2000
max_connect_errors=1000
character-set-server=UTF8MB4
default-storage-engine=INNODB
default_authentication_plugin=mysql_native_password

max_allowed_packet = 1G
event_scheduler=ON
secure_file_priv=
innodb_buffer_pool_size=256M
transaction-isolation = READ-COMMITTED
transaction-read-only = OFF
skip-log-bin
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

[mysql]
default-character-set=UTF8MB4

[client]
port=3306
default-character-set=UTF8MB4

注意：basedir 和 datadir 需替换为实际路径，路径中的反斜杠需要双写（\\）。

3. 初始化数据库

1
mysqld --defaults-file="D:\mysql-8.0.46-winx64\my.ini" --initialize --console

初始化完成后，控制台会输出初始随机密码，请妥善记录。

4. 安装服务并启动

1
mysqld install MySQL

5. 检查端口

1
netstat -ano|findstr "3306"

6. 修改 root 密码

使用初始密码登录（假设初始密码为 jlk7k;Yn0iIf）：

1
mysql -uroot -p'jlk7k;Yn0iIf'

登录后修改密码：

1
2
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Admin@098';
FLUSH PRIVILEGES;

7. 切换认证插件（可选）

如需兼容旧版客户端，可将认证方式改为 mysql_native_password：

1
2
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Admin@098';
FLUSH PRIVILEGES;

8. 开启远程访问

创建允许任意主机连接的 root 用户：

1
2
3
CREATE USER 'root'@'%' IDENTIFIED BY 'Admin@098';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

sql_mode 修改

  1. 修改 mysql 全局配置文件 my.conf/my.ini/mysqld.cnf
    加入以下内容
1
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
  1. 重启 mysql
    配置文件修改

mysql 分区

先给原有数据分区

1
2
3
4
alter table tbName partition BY RANGE (fieldName) (
    PARTITION p201802 VALUES LESS THAN (TO_DAYS('2018-03-01')),
    PARTITION p201803 VALUES LESS THAN (TO_DAYS('2018-04-01'))
);

查询分区

1
2
3
4
5
6
7
8
select
  partition_name part,
  partition_expression expr,
  partition_description descr,
  table_rows
from information_schema.partitions  where
  table_schema = schema()
  and table_name='tbName';

自动增加分区函数(按月)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
DELIMITER $$
#该表所在数据库名称
USE `dbName`$$
DROP PROCEDURE IF EXISTS `create_partition_by_month`$$
CREATE PROCEDURE `create_partition_by_month`(IN_SCHEMANAME VARCHAR(64), IN_TABLENAME VARCHAR(64))
BEGIN
    DECLARE ROWS_CNT INT UNSIGNED;
    DECLARE PARTITIONNAME VARCHAR(16);
    DECLARE ENDTIME_DATETIME VARCHAR(30);
    SET PARTITIONNAME = DATE_FORMAT( NOW(), 'p%Y%m' );
    SET ENDTIME_DATETIME = DATE_FORMAT((NOW() + INTERVAL 1 MONTH), '%Y-%m-01');
    SELECT COUNT(*) INTO ROWS_CNT FROM information_schema.partitions
	WHERE table_schema = IN_SCHEMANAME AND table_name = IN_TABLENAME AND partition_name = PARTITIONNAME;
    IF ROWS_CNT = 0 THEN
        SET @SQL = CONCAT( 'ALTER TABLE `', IN_SCHEMANAME, '`.`', IN_TABLENAME, '`',
	    ' ADD PARTITION (PARTITION ', PARTITIONNAME, " VALUES LESS THAN (UNIX_TIMESTAMP('",
            ENDTIME_DATETIME ,"')) ENGINE = InnoDB);" );
        PREPARE STMT FROM @SQL;
        EXECUTE STMT;
        DEALLOCATE PREPARE STMT;
     ELSE
	SELECT CONCAT("partition `", PARTITIONNAME, "` for table `",IN_SCHEMANAME, ".", IN_TABLENAME, "` already exists") AS result;
     END IF;
END$$
DELIMITER ;

新增事务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DELIMITER $$
#该表所在的数据库名称
USE `datong_collect`$$
CREATE EVENT IF NOT EXISTS `gps_part_manage`
ON SCHEDULE EVERY 1 MONTH   #执行周期，还有天、月等等
STARTS '2018-04-01 00:00:00'
ON COMPLETION PRESERVE
ENABLE
COMMENT 'Creating partitions'
DO BEGIN
    #调用刚才创建的存储过程，第一个参数是数据库名称，第二个参数是表名称
    CALL create_partition_by_month('datong_collect','tb_gps_trail');
END$$
DELIMITER ;

删除分区：

1
alter table voice drop partition p201907;

获取时间

1
2
3
4
5
select curdate();  #获取当前日期
select last_day(curdate());  #获取当月最后一天。
select DATE_ADD(curdate(),interval -day(curdate())+1 day);  #获取本月第一天
select date_add(curdate()-day(curdate())+1,interval 1 month);# 获取下个月的第一天
select DATEDIFF(date_add(curdate()-day(curdate())+1,interval 1 month ),DATE_ADD(curdate(),interval -day(curdate())+1 day)) from dual;#获取当前月的天数