Wsl上mysql8.2单机部署

1
2
3
4
5

[root@thinkpadx1-jy mysql]# rpm -q glibc libaio
glibc-2.28-225.0.3.el8.x86_64
libaio-0.3.112-1.el8.x86_64
## 安装依赖包
[root@thinkpadx1-jy mysql]#yum install  glibc libaio

1
2
3

mkdir -p /data/mysql
cd /data/mysql/
mkdir -p tmp data etc log/relaylog log/redolog log/binlog

  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
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180

[mysql]
#character-set-server = utf8mb4
default-character-set=utf8mb4
port = 3306
socket = /data/mysql/data/mysqld.sock
prompt=(\\u@\\h \\R:\\m:\\s [\\d]>\\_

[mysqld]
super_read_only = 0
port = 3306
user = mysql
server_id = 1596275734
basedir = /data/mysql/base
datadir = /data/mysql/data
log_bin = /data/mysql/log/binlog/mysql-bin
tmpdir = /data/mysql/tmp
relay_log = /data/mysql/log/relaylog/mysql-relay
innodb_log_group_home_dir = /data/mysql/log/redolog
log_error = /data/mysql/data/mysql-error.log
socket = /data/mysql/data/mysqld.sock
pid_file = /data/mysql/data/mysqld.pid
slow_query_log_file = /data/mysql/data/mysql-slow.log


# BINLOG
binlog_error_action = ABORT_SERVER
binlog_format = row
binlog_checksum = 1
binlog_rows_query_log_events = 1
log_slave_updates = 1
master_info_repository = TABLE
max_binlog_size = 1G
max_binlog_cache_size = 2G
relay_log_info_repository = TABLE
relay_log_recovery = 1
relay-log-purge = 1
sync_binlog = 1

# GTID #
gtid_mode = ON
enforce_gtid_consistency = 1

# ENGINE
default_storage_engine = InnoDB
innodb_buffer_pool_size = 4000M
innodb_data_file_path = ibdata1:1G:autoextend
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit=1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 10000
innodb_io_capacity_max = 50000
innodb_log_buffer_size = 64M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 60
innodb_print_all_deadlocks=1
innodb_stats_on_metadata = 0
innodb_strict_mode = 1
#innodb_undo_tablespaces=10 高版本废弃参数
innodb_max_undo_log_size=4G
innodb_undo_log_truncate=1
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_purge_threads = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_dump_pct=25
innodb_sort_buffer_size = 8M
innodb_online_alter_log_max_size = 4G
innodb_rollback_on_timeout = 1
innodb_lock_wait_timeout = 10
innodb_checksum_algorithm = crc32
innodb_lru_scan_depth = 4000
innodb_open_files = 65535
innodb_page_cleaners = 4
innodb_flush_sync = 0
innodb_flush_neighbors = 0
innodb_autoinc_lock_mode = 1
#innodb_print_ddl_logs = 1
transaction_isolation = READ-COMMITTED

# OTHER
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 4G
# myisam_repair_threads = 1
lock_wait_timeout = 3600
explicit_defaults_for_timestamp = 1
innodb_thread_concurrency = 0
log_error_verbosity = 3


# CACHE
key_buffer_size = 32M
tmp_table_size = 32M
max_heap_table_size = 32M
table_open_cache = 1024
max_connections = 2000
max_connect_errors = 1000000
thread_cache_size = 1024
open_files_limit = 65535
binlog_cache_size = 1M
join_buffer_size = 8M
sort_buffer_size = 8M
thread_stack = 512K
table_definition_cache = 1024
table_open_cache_instances = 64

# SLOW LOG
slow_query_log = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
long_query_time  = 1

# LOG QUERY
log_queries_not_using_indexes =1
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 100

# SEMISYNC #
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
#rpl_semi_sync_master_enabled = 1
#rpl_semi_sync_slave_enabled = 0
#rpl_semi_sync_master_wait_for_slave_count = 1
#rpl_semi_sync_master_wait_no_slave = 0
#rpl_semi_sync_master_timeout = 300000 # 5 minutes

# CLIENT_DEPRECATE_EOF
session_track_schema = 1
session_track_state_change = 1
session_track_system_variables = '*'

# MISC
log_timestamps=SYSTEM
lower_case_table_names = 1
max_allowed_packet = 64M
read_only = 0
skip_external_locking = 1
skip_name_resolve = 1
skip_slave_start = 1
disabled_storage_engines = ARCHIVE,BLACKHOLE,EXAMPLE,FEDERATED,MEMORY,MERGE,NDB
log-output = TABLE,FILE
character_set_server = utf8mb4
secure_file_priv = ""
#ternal-locking = FALSE
performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
#expire_logs_days = 14 高版本废弃参数
binlog_expire_logs_seconds = 604800  #设置7天
interactive_timeout = 600
wait_timeout = 600

# MTS
slave-parallel-type=LOGICAL_CLOCK
slave_parallel_workers=16
slave_preserve_commit_order=1

#performance_schema
performance_schema = 1
#performance_schema_instrument = '%memory%=on'
performance_schema_instrument = '%lock%=on'

#innodb monitor
innodb_monitor_enable="module_innodb"
innodb_monitor_enable="module_server"
innodb_monitor_enable="module_dml"
innodb_monitor_enable="module_ddl"
innodb_monitor_enable="module_trx"
innodb_monitor_enable="module_os"
innodb_monitor_enable="module_purge"
innodb_monitor_enable="module_log"
innodb_monitor_enable="module_lock"
innodb_monitor_enable="module_buffer"
innodb_monitor_enable="module_index"
innodb_monitor_enable="module_ibuf_system"
innodb_monitor_enable="module_buffer_page"
innodb_monitor_enable="module_adaptive_hash"

[mysqldump]
quick
max_allowed_packet = 32M

1
2
3
4
5
6

tar -Jxf /tmp/mysql-8.2.0-linux-glibc2.28-x86_64.tar.xz -C /data/mysql/
cd /data/mysql/
mv mysql-8.2.0-linux-glibc2.28-x86_64 /data/mysql/base
useradd -u 3306 mysql; 
chown -R mysql:mysql /data/mysql;
/data/mysql/base/bin/mysqld --defaults-file=/data/mysql/etc/my.cnf --initialize --datadir=/data/mysql/data --basedir=/data/mysql/base --user=mysql

1
2

# mysql 用户
/data/mysql/base/bin/mysqld_safe --defaults-file=/data/mysql/etc/my.cnf &

1
2
3
4
5
6

# root用户
vi /etc/profile
export MYSQL_HOME=/data/mysql/base
export PATH=$PATH:$MYSQL_HOME/bin
export MYSQL_UNIX_PORT=/data/mysql/data/mysqld.sock
export MYSQL_PS1="(\u@\h \R:\m:\s) [\d]>"

1
2
3
4
5
6
7

grep password /data/mysql/data/mysql-error.log

Cq36*+7aKHhE51^_0T'

[root@localhost ~]$ mysql -uroot -p
mysql>alter user 'root'@'localhost' identified by 'Root~123';
mysql>flush privileges;

1

mysqld: error while loading shared libraries: libnuma.so.1: cannot open shared object file: No such file or directory

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

ldd mysqld
        linux-vdso.so.1 (0x00007ffd5e1c9000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f682ffa6000)
        libcrypto.so.3 => /data/mysql/base/bin/../lib/private/libcrypto.so.3 (0x00007f682f96f000)
        libssl.so.3 => /data/mysql/base/bin/../lib/private/libssl.so.3 (0x00007f682f6c7000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f682f4bf000)
        libprotobuf-lite.so.3.19.4 => /data/mysql/base/bin/../lib/private/libprotobuf-lite.so.3.19.4 (0x00007f683034b000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007f682f2bc000)
        libnuma.so.1 => not found
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f682f0b8000)
        libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f682ed23000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f682e9a1000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f682e788000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f682e3c3000)
        /lib64/ld-linux-x86-64.so.2 (0x00007f68301c6000)