MariaDB Windows 安装

1.复制安装文件到服务器 ，并解压

 

 2.执行初始化：bin下面的文件，执行下：

 

3.  data/my.ini 下会自动创建配置文件：

4  创建logs 文件夹，来放二进制文件

5.编辑my.ini文件内容

[client]
port=3307

[mysql]

default-character-set=utf8

[mysqld]
port=3307

#程序安装目录
basedir=E:/mariadb-10.4.7

#DB存储路径 
datadir=E:/mariadb-10.4.7/data/
character-set-server=utf8

#配置二进制日志,把下面的路径该成自己部署的路径
log-bin = E:/mariadb-10.4.7/logs/mysql-bin.log
expire-logs-days = 14
max-binlog-size = 50000M
server-id = 1

# 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 = 2M
# read_rnd_buffer_size = 2M 

innodb_buffer_pool_size=5GB

#下面配置链接数和缓存
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

thread_cache_size=200
max_connections=2000
#interactive_timeout决定的是交互连接的时间长短，而wait_timeout决定的是非交互连接的时间长短 参数默认值：28800秒（8小时）-特殊数据库需要考虑相关的应用场景 （）
wait_timeout = 300 
interactive_timeout = 500
# use cache
query_cache_size=2048M 
query_cache_type=1 

备注：interactive_timeout针对交互式连接，wait_timeout针对非交互式连接。所谓的交互式连接，即在mysql_real_connect()函数中使用了CLIENT_INTERACTIVE选项。说得直白一点，通过mysql客户端连接数据库是交互式连接，通过jdbc连接数据库是非交互式连接。 

推荐参数配置（8核32G）

[mysqld]
lower_case_table_names=1
skip_name_resolve=ON
port = 3310

back_log = 50
max_connections = 2000
wait_timeout = 256
max_connect_errors = 10

table_open_cache = 4096
max_allowed_packet = 1024M
binlog_cache_size = 512M
max_heap_table_size = 512M

read_buffer_size = 8M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 8M

thread_cache_size = 8
thread_concurrency = 8
thread_stack = 240K

query_cache_size = 128M
query_cache_limit = 2M
ft_min_word_len = 4
transaction_isolation = REPEATABLE-READ
tmp_table_size = 512M

log-bin=mysql-bin
binlog_format=mixed
slow_query_log=1
log_queries_not_using_indexes=1
long_query_time = 2
slow_query_log_file = /log/mysql-slow.log
server-id = 1

# INNODB options
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8
innodb_data_file_path = ibdata1:10M:autoextend

innodb_write_io_threads = 8
innodb_read_io_threads = 8

innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size = 1GB
innodb_change_buffering = all
innodb_change_buffer_max_size = 25

innodb_log_file_size = 512M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 256

6.命令 创建服务的命令 （绿色路径替换）

sc create "MariaDb" binpath= "\"E:/mariadb-10.4.7/bin/mysqld\" \"--defaults-file=E:/mariadb-10.4.7/data/my.ini\" MariaDb" DisplayName= "MariaDb" start= "auto"

 

7.启动服务

8 .cmd 到bin下面执行   mysql -u root 

格式： mysql -h主机地址 -u用户名 －p用户密码 -P端口

 

 执行： mysql -u root

9. 修改外网权限可以访问

　　GRANT ALL PRIVILEGES ON *.* TO 'root'@'11.1.1.112' IDENTIFIED BY '123456' WITH GRANT OPTION;

　　GRANT ALL PRIVILEGES ON *.* TO 'root'@'win-e3k0v0q25b7' IDENTIFIED BY '123456' WITH GRANT OPTION;
　　GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
　　FLUSH   PRIVILEGES;

计算机名的如果外网连接要添加才行

10. 修改用户密码：

　　格式：mysql> set password for 用户名@localhost = password('新密码'); 
　　例子：mysql> set password for root@localhost = password('123456');

　　　　　mysql> set password for root@127.0.0.1 = password('123456');

11. 备注：

　　登陆不进去可以：

　　#在my.ini，[mysqld]下添加一行，使其登录时跳过权限检查
　　skip-grant-tables   

 

12 卸载服务：

sc delete Mariadb

 13. 查询缓存配置
query_cache_type

query_cache_size