[root@node01 ~]# cat >> /etc/hosts <<EOF 192.168.1.201 node01 EOF [root@node01 ~]# useradd mysql [root@node01 ~]# id mysql uid=500(mysql) gid=500(mysql) groups=500(mysql) [root@node01 ~]# cat >> /etc/security/limits.conf <<EOF mysql soft nproc 4096 mysql hard nproc 65535 mysql soft nofile 4096 mysql hard nofile 65535 EOF [root@node01 ~]# yum install -y libaio yum-utils [root@node01 ~]# rpm -ivh mysql80-community-release-el7-3.noarch.rpm [root@node01 ~]# yum-config-manager --disable mysql80-community [root@node01 ~]# yum-config-manager --enable mysql57-community [root@node01 ~]# yum repolist all | grep mysql [root@node01 ~]# yum install -y mysql-community-server [root@node01 ~]# yum install -y mysql-community-devel [root@node01 ~]# vim /usr/lib/systemd/system/mysqld.service # Sets open_files_limit LimitNOFILE = 65535 [root@node01 ~]# vim /etc/my.cnf [mysqld] user = mysql port = 3306 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error = mysqld_error.log pid-file=mysqld.pid log_timestamps = SYSTEM character-set-server = utf8mb4 open-files-limit = 65535 max_connections = 500 max_connect_errors = 10000 max_allowed_packet = 32M tmp_table_size = 512M max_heap_table_size = 512M slow_query_log = 1 long_query_time = 5 slow_query_log_file = mysqld_slow.log server_id = 201 log_bin = mysql-bin binlog_cache_size = 16M max_binlog_cache_size = 4G max_binlog_size = 128M expire_logs_days = 7 binlog_rows_query_log_events = on log_slave_updates = on master_info_repository = table relay_log_info_repository = table relay_log = relay-bin relay_log_recovery = 1 transaction-isolation = READ-COMMITTED innodb_buffer_pool_size = 8G innodb_buffer_pool_instances = 8 innodb_log_file_size = 256M innodb_log_files_in_group = 4 innodb_log_buffer_size = 16M innodb_undo_logs = 128 innodb_undo_tablespaces = 3 innodb_undo_log_truncate = 1 innodb_data_file_path = ibdata1:1024M:autoextend innodb_temp_data_file_path = ibtmp1:128M:autoextend innodb_print_all_deadlocks = 1 [mysql] default-character-set = utf8mb4 [root@node01 ~]# mysqld --initialize-insecure [root@node01 ~]# systemctl daemon-reload [root@node01 ~]# systemctl start mysqld.service [root@node01 ~]# systemctl status mysqld.service [root@node01 ~]# netstat -tunlp | grep mysqld tcp6 0 0 :::3306 :::* LISTEN 22255/mysqld [root@node01 ~]# mysql_secure_installation ##密码校验插件 VALIDATE PASSWORD PLUGIN can be used to test passwords and improve security. It checks the strength of password and allows the users to set only those passwords which are secure enough. Would you like to setup VALIDATE PASSWORD plugin? Press y|Y for Yes, any other key for No: n ##修改root密码 Please set the password for root here. New password: Re-enter new password: ##删除匿名用户 Remove anonymous users? (Press y|Y for Yes, any other key for No) : y Success. ##禁止root远程登录 Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y Success. ##删除测试库 Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y - Dropping test database... Success. - Removing privileges on test database... Success. ##刷新权限 Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y Success. All done! [root@node01 ~]# mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 231 Server version: 5.7.32-log MySQL Community Server (GPL) Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select version(); +------------+ | version() | +------------+ | 5.7.32-log | +------------+ 1 row in set (0.00 sec) mysql> create user 'root'@'%' identified by 'abcd.1234'; Query OK, 0 rows affected (0.01 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION; Query OK, 0 rows affected (0.00 sec) mysql> flush PRIVILEGES; Query OK, 0 rows affected (0.00 sec) mysql> show grants for 'root'@'%'; +-------------------------------------------------------------+ | Grants for root@% | +-------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION | +-------------------------------------------------------------+ 1 row in set (0.00 sec)
MySQL 5.7通过Tcmalloc管理内存 [root@node01 ~]# yum -y install gcc make gcc-c++ lsof libunwind libunwind-devel [root@node01 ~]# wget https://github.com/gperftools/gperftools/releases/download/gperftools-2.10/gperftools-2.10.tar.gz [root@node01 ~]# tar zxvf gperftools-2.10.tar.gz [root@node01 ~]# cd gperftools-2.10 [root@node01 gperftools-2.10]# ./configure [root@node01 gperftools-2.10]# make [root@node01 gperftools-2.10]# make install [root@node01 ~]# cat /usr/lib/systemd/system/mysqld.service # Use this to switch malloc implementation EnvironmentFile=-/etc/sysconfig/mysql [root@node01 ~]# echo 'LD_PRELOAD=/usr/local/lib/libtcmalloc.so' > /etc/sysconfig/mysql [root@node01 ~]# systemctl daemon-reload [root@node01 ~]# systemctl restart mysqld [root@node01 ~]# lsof -n | grep tcmalloc mysqld 17206 mysql mem REG 8,3 2644880 4501918 /usr/local/lib/libtcmalloc.so.4.5.10 mysqld 17206 17207 mysql mem REG 8,3 2644880 4501918 /usr/local/lib/libtcmalloc.so.4.5.10 mysqld 17206 17208 mysql mem REG 8,3 2644880 4501918 /usr/local/lib/libtcmalloc.so.4.5.10 mysqld 17206 17209 mysql mem REG 8,3 2644880 4501918 /usr/local/lib/libtcmalloc.so.4.5.10 mysqld 17206 17210 mysql mem REG 8,3 2644880 4501918 /usr/local/lib/libtcmalloc.so.4.5.10 mysqld 17206 17211 mysql mem REG 8,3 2644880 4501918 /usr/local/lib/libtcmalloc.so.4.5.10 mysqld 17206 17212 mysql mem REG 8,3 2644880 4501918 /usr/local/lib/libtcmalloc.so.4.5.10 mysqld 17206 17213 mysql mem REG 8,3 2644880 4501918 /usr/local/lib/libtcmalloc.so.4.5.10 mysqld 17206 17214 mysql mem REG 8,3 2644880 4501918 /usr/local/lib/libtcmalloc.so.4.5.10
MySQL 5.7通过Jemalloc管理内存 [root@node01 ~]# wget https://github.com/jemalloc/jemalloc/releases/download/5.2.1/jemalloc-5.2.1.tar.bz2 [root@node01 ~]# tar -jxvf jemalloc-5.2.1.tar.bz2 [root@node01 ~]# cd jemalloc-5.2.1 [root@node01 jemalloc-5.2.1]# ./configure [root@node01 jemalloc-5.2.1]# make [root@node01 jemalloc-5.2.1]# make install [root@node01 ~]# cat /usr/lib/systemd/system/mysqld.service # Use this to switch malloc implementation EnvironmentFile=-/etc/sysconfig/mysql [root@node01 ~]# echo 'LD_PRELOAD=/usr/local/lib/libjemalloc.so' > /etc/sysconfig/mysql [root@node01 ~]# systemctl daemon-reload [root@node01 ~]# systemctl restart mysqld [root@node01 ~]# lsof -n | grep jemalloc mysqld 6272 mysql mem REG 253,0 4397600 273167915 /usr/local/lib/libjemalloc.so.2 mysqld 6272 6273 mysql mem REG 253,0 4397600 273167915 /usr/local/lib/libjemalloc.so.2 mysqld 6272 6274 mysql mem REG 253,0 4397600 273167915 /usr/local/lib/libjemalloc.so.2