mysql8.0.15二进制安装

　　今天有幸尝试安装了社区版本的mysql8.0.15,记录下来，供以后方便使用。特此感谢知数堂的叶老师，提供了配置文件的模板。

# 第一部分：系统配置

1# 1、安装系统依赖包
2
3yum -y install make gcc-c++ cmake bison-devel ncurses-devel  readline-devel  libaio-devel perl libaio wget lrzsz vim libnuma* bzip2 xz

# 2、关闭selinux

1sed -i 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config
2setenforce  0

# 3、修改系统限制参数

1cat >> /etc/security/limits.conf << EOF
2#
3###custom
4#
5*           soft   nofile       20480
6*           hard   nofile       65535
7*           soft   nproc        20480
8*           hard   nproc        65535
9EOF

# 4、修改内核参数

1cat >>/etc/sysctl.conf <<"EOF"
2vm.swappiness=0
3#增加tcp支持的队列数
4net.ipv4.tcp_max_syn_backlog = 65535
5#减少断开连接时 ，资源回收
6net.ipv4.tcp_max_tw_buckets = 8000
7net.ipv4.tcp_tw_reuse = 1
8net.ipv4.tcp_tw_recycle = 1
9net.ipv4.tcp_fin_timeout = 10
10#改变本地的端口范围
11net.ipv4.ip_local_port_range = 1024 65535
12#允许更多的连接进入队列
13net.ipv4.tcp_max_syn_backlog = 4096  
14#对于只在本地使用的数据库服务器
15net.ipv4.tcp_fin_timeout = 30
16#端口监听队列
17net.core.somaxconn=65535
18#接受数据的速率
19net.core.netdev_max_backlog=65535
20net.core.wmem_default=87380
21net.core.wmem_max=16777216
22net.core.rmem_default=87380
23net.core.rmem_max=16777216
24EOF
25
26sysctl -p

# 第二部分：mysql的安装配置

# 1、下载安装包
# 社区版 8.0.15

1cd /opt/
2wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.15-linux-glibc2.12-x86_64.tar.xz

# 2、解压安装包

tar -xJf mysql-8.0.15-linux-glibc2.12-x86_64.tar.xz

# 3、进入目录，做软连接，方便以后升级

1cd /usr/local/
2ln -s /opt/mysql-8.0.15-linux-glibc2.12-x86_64 mysql

# 4、创建用户

1groupadd mysql
2useradd -g mysql mysql -d /home/mysql -s /sbin/nologin

# 5、创建相应的目录

mkdir -p /data/mysql/mysql_3306/{data,logs,tmp}

# 6、创建配置文件

1# 排除干扰因素
2
3if [ -f /etc/my.cnf ]; then
4    mv /etc/my.cnf /etc/my.cnf.`date +%Y%m%d%H%m`.bak
5fi
6
7cat >/data/mysql/mysql_3306/my_3306.cnf <<"EOF"
8[client]
9port    = 3306
10socket    = /data/mysql/mysql_3306/tmp/mysql_3306.sock
11
12[mysql]
13prompt="\u@\h \R:\m:\s [\d]> "
14no-auto-rehash
15
16[mysqld]
17user    = mysql
18port    = 3306
19admin_address = 127.0.0.1
20basedir    = /usr/local/mysql
21datadir    = /data/mysql/mysql_3306/data
22socket    = /data/mysql/mysql_3306/tmp/mysql_3306.sock
23pid-file = mysql_3306.pid
24character-set-server = utf8mb4
25skip_name_resolve = 1
26
27
28#replicate-wild-ignore-table=mysql.%
29replicate-wild-ignore-table=test.%
30replicate-wild-ignore-table=information_schema.%
31
32# Two-Master configure
33#server-1 
34#auto-increment-offset = 1
35#auto-increment-increment = 2 
36
37#server-2                          
38#auto-increment-offset = 2
39#auto-increment-increment = 2
40
41
42# semi sync replication settings #
43#plugin_dir = /usr/local/mysql/lib/mysql/plugin
44#plugin_load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
45plugin_dir = /usr/local/mysql/lib/plugin #官方版本的路径
46plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" #官方版本的路径
47
48slave_parallel_workers = 4
49slave_parallel_type = LOGICAL_CLOCK
50
51open_files_limit    = 65535
52back_log = 1024
53max_connections = 1024
54max_connect_errors = 1000000
55table_open_cache = 1024
56table_definition_cache = 1024
57table_open_cache_instances = 64
58thread_stack = 512K
59external-locking = FALSE
60max_allowed_packet = 32M
61sort_buffer_size = 4M
62join_buffer_size = 4M
63thread_cache_size = 1536
64interactive_timeout = 600
65wait_timeout = 600
66tmp_table_size = 32M
67max_heap_table_size = 32M
68slow_query_log = 1
69log_timestamps = SYSTEM
70slow_query_log_file = /data/mysql/mysql_3306/logs/slow.log
71log-error = /data/mysql/mysql_3306/logs/error.log
72long_query_time = 0.1
73log_queries_not_using_indexes =1
74log_throttle_queries_not_using_indexes = 60
75min_examined_row_limit = 100
76log_slow_admin_statements = 1
77log_slow_slave_statements = 1
78server-id = 3306
79log-bin = /data/mysql/mysql_3306/logs/mysql-bin
80sync_binlog = 1
81binlog_cache_size = 4M
82max_binlog_cache_size = 2G
83max_binlog_size = 1G
84binlog_expire_logs_seconds=2592000 
85master_info_repository = TABLE
86relay_log_info_repository = TABLE
87gtid_mode = on
88enforce_gtid_consistency = 1
89log_slave_updates
90slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
91binlog_format = row
92binlog_row_image=FULL
93binlog_checksum = 1
94relay_log_recovery = 1
95relay-log-purge = 1
96key_buffer_size = 32M
97read_buffer_size = 8M
98read_rnd_buffer_size = 4M
99bulk_insert_buffer_size = 64M
100myisam_sort_buffer_size = 128M
101myisam_max_sort_file_size = 10G
102myisam_repair_threads = 1
103lock_wait_timeout = 3600
104explicit_defaults_for_timestamp = 1
105innodb_thread_concurrency = 0
106innodb_sync_spin_loops = 100
107innodb_spin_wait_delay = 30
108
109#transaction_isolation = REPEATABLE-READ
110transaction_isolation = READ-COMMITTED
111#innodb_additional_mem_pool_size = 16M
112innodb_buffer_pool_size = 2867M
113innodb_buffer_pool_instances = 4
114innodb_buffer_pool_load_at_startup = 1
115innodb_buffer_pool_dump_at_shutdown = 1
116innodb_data_file_path = ibdata1:1G:autoextend
117innodb_flush_log_at_trx_commit = 1
118innodb_log_buffer_size = 32M
119innodb_log_file_size = 2G
120innodb_log_files_in_group = 3
121innodb_max_undo_log_size = 4G
122innodb_undo_directory = /data/mysql/mysql_3306/undolog
123innodb_undo_tablespaces = 95
124
125# 根据您的服务器IOPS能力适当调整
126# 一般配普通SSD盘的话，可以调整到 10000 - 20000
127# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
128innodb_io_capacity = 4000
129innodb_io_capacity_max = 8000
130innodb_flush_sync = 0
131innodb_flush_neighbors = 0
132innodb_write_io_threads = 8
133innodb_read_io_threads = 8
134innodb_purge_threads = 4
135innodb_page_cleaners = 4
136innodb_open_files = 65535
137innodb_max_dirty_pages_pct = 50
138innodb_flush_method = O_DIRECT
139innodb_lru_scan_depth = 4000
140innodb_checksum_algorithm = crc32
141innodb_lock_wait_timeout = 10
142innodb_rollback_on_timeout = 1
143innodb_print_all_deadlocks = 1
144innodb_file_per_table = 1
145innodb_online_alter_log_max_size = 4G
146innodb_stats_on_metadata = 0
147
148# some var for MySQL 8
149log_error_verbosity = 3
150innodb_print_ddl_logs = 1
151binlog_expire_logs_seconds = 2592000
152#innodb_dedicated_server = 0
153
154innodb_status_file = 1
155# 注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快
156innodb_status_output = 0
157innodb_status_output_locks = 0
158
159#performance_schema
160performance_schema = 1
161performance_schema_instrument = '%memory%=on'
162performance_schema_instrument = '%lock%=on'
163
164#innodb monitor
165innodb_monitor_enable="module_innodb"
166innodb_monitor_enable="module_server"
167innodb_monitor_enable="module_dml"
168innodb_monitor_enable="module_ddl"
169innodb_monitor_enable="module_trx"
170innodb_monitor_enable="module_os"
171innodb_monitor_enable="module_purge"
172innodb_monitor_enable="module_log"
173innodb_monitor_enable="module_lock"
174innodb_monitor_enable="module_buffer"
175innodb_monitor_enable="module_index"
176innodb_monitor_enable="module_ibuf_system"
177innodb_monitor_enable="module_buffer_page"
178innodb_monitor_enable="module_adaptive_hash"
179validate_password_policy=LOW
180
181[mysqldump]
182quick
183max_allowed_packet = 32M
184
185[mysqld_safe]
186#malloc-lib=/usr/local/mysql/lib/jmalloc.so 
187nice=-19
188open-files-limit=65535
189
190EOF

# 7、修改目录权限

chown -R mysql.mysql /data/mysql/mysql_3306

# 8、初始化数据库

1# /usr/local/mysql/bin/mysqld --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql --initialize-insecure
2# 官方推荐使用--initialize，会在错误日志中生成难以输入的临时密码，我这里使用的免密码的方式。
3/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql_3306/my_3306.cnf --initialize-insecure  --user=mysql &

# 9、查看日志

# tail -f /data/mysql/mysql_3306/logs/error.log

# 10、启动数据库

/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3306/my_3306.cnf &

# 11、首次登录方式

/usr/local/mysql/bin/mysql --socket=/data/mysql/mysql_3306/tmp/mysql_3306.sock

# 12、初次登陆强制修改密码方法

1# ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'UbP*tzonifjZdP$jsvYu' PASSWORD EXPIRE NEVER ;
2# flush privileges;
3# ALTER USER 'root'@'localhost' IDENTIFIED  BY 'AnvcTMagdLarwNV3CKaC' PASSWORD EXPIRE NEVER ; 
4# ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'AnvcTMagdLarwNV3CKaC' PASSWORD EXPIRE NEVER ;
5# 创建普通权限
6# create user 'test_w'@'%' identified by 'azpfT%aptxL^$XrBI&kk' PASSWORD EXPIRE NEVER ;
7# grant insert,delete,update,select on db144.* to 'test_w'@'%' ;
8
9
10ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'AnvcTMagdLarwNV3CKaC' PASSWORD EXPIRE NEVER ;
11create user 'root'@'127.0.0.1' identified WITH mysql_native_password by 'AnvcTMagdLarwNV3CKaC' PASSWORD EXPIRE NEVER ;
12grant all privileges on *.* to 'root'@'127.0.0.1' with grant option;
13
14create user 'admin_m'@'127.0.0.1' identified WITH mysql_native_password by 'rA75MQy*R*y@KO4z%LZe' PASSWORD EXPIRE NEVER ; 
15grant all privileges on *.* to 'admin_m'@'127.0.0.1' with grant option;
16
17create user 'admin_m'@'%' identified WITH mysql_native_password by 'rA75MQy*R*y@KO4z%LZe' PASSWORD EXPIRE NEVER ; 
18grant all privileges on *.* to 'admin_m'@'%' with grant option;
19
20create user 'test_w'@'%' identified with mysql_native_password by 'azpfT%aptxL^$XrBI&kk' PASSWORD EXPIRE NEVER ;
21grant insert,delete,update,select on db144.* to 'test_w'@'%' ;

# 13、创建软连接

1ln -s /usr/local/mysql/bin/* /usr/bin/
2ln -s /usr/local/mysql/lib/* /usr/lib64/

# 14、快捷登陆

1cat  >>/root/.bashrc <<"EOF"
2
3#
4alias mysql.3306.start="/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql_3306/my_3306.cnf &"
5alias mysql.3306.stop="/usr/local/mysql/bin/mysqladmin -h127.0.0.1 -P 3306 -uroot -p'AnvcTMagdLarwNV3CKaC' shutdown &"
6alias mysql.3306.login="/usr/local/mysql/bin/mysql -h127.0.0.1 -P 3306 -uroot -p'AnvcTMagdLarwNV3CKaC'"
7EOF
8
9source  /root/.bash_profile
10
11cat >>/etc/ld.so.conf <<"EOF"
12/usr/local/mysql/lib
13EOF
14
15ldconfig [root@db144 ~]# mysql.3306.login mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 14Server version: 8.0.15 MySQL Community Server - GPLCopyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> exitBye[root@db144 ~]# 

完毕！