环境优化
# 关闭防火墙 systemctl stop firewalld.service systemctl disable firewalld.service #查看selinux getenforce #关闭selinux setenforce 0 #永久关闭selinux sed -i 's#SELINUX=enforcing#SELINUX=disabled#g' /etc/selinux/config #清除iptables 规则 iptables -F # 关闭NetworkManager systemctl stop NetworkManager systemctl disable NetworkManager # 尽量不使用交换区,默认60 echo '0' > /proc/sys/vm/swappiness # 永久修改 尽量不使用交换区,默认60 cat >>/etc/sysctl.conf<<'EOF' vm.swappiness=0 vm.overcommit_memory = 1 EOF sysctl -p ntpdate -u ntp.aliyun.com # 时间同步 hwclock -w # 更新BIOS时间
安装常用命令及依赖
yum install -y net-tools lrzsz vim libaio-devel ntpdate epel-release \ tree libaio-devel lsof sysstat bash-completion bash-completion-extras jemalloc wget
卸载mariadb
yum remove mariadb* -y
创建MySQL软件及数据目录
mkdir /mysql/{tools,tar} -p mkdir /data7/data7/mydata/3306/{binlog,data,etc,lock,log,pid,socket,tmp,script} -p
下载二进制包
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz
md5验证包的完整性
[root@node9 tar]# md5sum mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz c17075cfc58d8cd25cea4be36d77eed2 mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz
解压MySQL包
cd /mysql/tar tar xf /mysql/tar/mysql-8.0.35-linux-glibc2.17-x86_64.tar.xz
移动tar包到mysql目录
mv /mysql/tar/mysql-8.0.35-linux-glibc2.17-x86_64 /mysql/mysql80
配置环境变量
cat >/etc/profile.d/mysql.sh<<'EOF' export PATH=/mysql/mysql80/bin:$PATH EOF #修改权限 chmod 700 /etc/profile.d/mysql.sh #生效环境变量 source /etc/profile.d/mysql.sh
创建所需用户
groupadd mysql useradd mysql -g mysql
添加配置文件
vim /data7/mydata/3306/etc/my.cnf [client] socket=/mydata/3306/socket/mysql.sock [mysql] socket=/mydata/3306/socket/mysql.sock prompt='\u@\d>\_' default-character-set=utf8mb4 no-auto-rehash #show-warnings #查看告警信息 [mysqld] # GENERAL # default_authentication_plugin = mysql_native_password mysqlx = OFF mysqlx_port = 33060 mysqlx_socket = /mydata/3306/socket/mysqlx.sock admin_address = 127.0.0.1 admin_port = 33062 lower_case_table_names = 1 character_set_server = utf8mb4 collation_server = utf8mb4_general_ci user = mysql default_storage_engine = InnoDB port = 3306 socket = /mydata/3306/socket/mysql.sock pid-file = /mydata/3306/pid/mysql.pid log_timestamps = SYSTEM default_time_zone = +08:00 secure_file_priv = '' local_infile = ON thread_stack = 512K # MyISAM # key_buffer_size = 128M myisam_sort_buffer_size = 8M read_rnd_buffer_size = 4M # SAFETY # back_log = 1024 #bind-address = 0.0.0.0 innodb_strict_mode = 1 max_allowed_packet = 32M max_connect_errors = 1000000 interactive_timeout = 900 wait_timeout = 900 skip_grant_tables = 0 skip_name_resolve = 1 host_cache_size = 0 sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES sysdate_is_now = 1 transaction_isolation = READ-COMMITTED explicit_defaults_for_timestamp = 1 # DATA STORAGE # basedir = /mysql/mysql80 datadir = /mydata/3306/data tmpdir = /mydata/3306/tmp # BINARY LOGGING # #max_binlog_files = 100 binlog_transaction_dependency_tracking = WRITESET binlog_cache_size = 1M binlog_format = ROW binlog_expire_logs_seconds = 15 max_binlog_size = 1G max_binlog_cache_size = 4G log_bin = /mydata/3306/binlog/mysql-bin log_bin_index = /mydata/3306/binlog/mysql-bin.index sync_binlog = 1 # CACHES AND LIMITS # max_connections = 2000 max_heap_table_size = 128M tmp_table_size = 128M open_files_limit = 65535 join_buffer_size = 1M read_buffer_size = 4M sort_buffer_size = 1M table_definition_cache = 4096 table_open_cache = 4000 thread_cache_size = 1000 # INNODB # innodb_stats_persistent_sample_pages = 500 lock_wait_timeout = 3600 bulk_insert_buffer_size = 64M innodb_max_undo_log_size = 4G innodb_autoextend_increment = 8 innodb_buffer_pool_instances = 10 # 等于 innodb_buffer_pool_size innodb_buffer_pool_size = 20G # 改成机器可用内存的 70% innodb_buffer_pool_load_at_startup = ON innodb_buffer_pool_dump_at_shutdown = ON innodb_data_file_path = ibdata1:1024M:autoextend innodb_fast_shutdown = 1 innodb_file_per_table = 1 innodb_force_recovery = 0 innodb_flush_log_at_trx_commit = 1 innodb_flush_method = O_DIRECT innodb_lock_wait_timeout = 50 innodb_log_buffer_size = 8M innodb_log_file_size = 1024M innodb_log_files_in_group = 4 innodb_open_files = 10000 innodb_purge_threads = 4 innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_sync_spin_loops = 30 innodb_thread_concurrency = 24 innodb_print_all_deadlocks = 1 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" # LOGGING # general_log = 0 general_log_file = /mydata/3306/log/mysql-general.log log_error = /mydata/3306/log/mysql-error.log log_throttle_queries_not_using_indexes = 60 min_examined_row_limit = 100 log_slow_admin_statements = 1 log_slow_slave_statements = 1 log_error_verbosity = 3 log_queries_not_using_indexes = 1 long_query_time = 1 slow_query_log = 1 slow_query_log_file = /mydata/3306/log/mysql-slow.log # GTID enforce_gtid_consistency = ON gtid_mode = ON binlog_gtid_simple_recovery = TRUE # SEMI-SYNC plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" rpl_semi_sync_master_enabled = ON rpl_semi_sync_slave_enabled = ON rpl_semi_sync_master_timeout = 3000 rpl_semi_sync_master_trace_level = 32 rpl_semi_sync_slave_trace_level = 32 rpl_semi_sync_master_wait_no_slave = ON # REPLICATION # log_replica_updates = 1 read_only = 0 relay_log = /mydata/3306/binlog/relay-bin relay_log_index = /mydata/3306/binlog/relay-bin.index server_id = 330675 replica_skip_errors = OFF skip_replica_start replica_net_timeout = 60 replica_compressed_protocol = OFF #5.6版本、5.7.21+、8.0.4+可以设置为ON ##parallel ## replica_parallel_type = LOGICAL_CLOCK replica_parallel_workers = 40 slave_rows_search_algorithms = 'INDEX_SCAN,HASH_SCAN' binlog_group_commit_sync_delay = 500 binlog_group_commit_sync_no_delay_count = 13 slave_preserve_commit_order = 1 binlog_transaction_dependency_tracking = writeset transaction_write_set_extraction = XXHASH64 slave_checkpoint_period = 2 binlog_checksum = CRC32 log_slave_updates = ON master_info_repository = TABLE relay_log_info_repository = TABLE relay_log_recovery = 1 #将配置文件链接到 etc 下 ln -s /data7/mydata/3306/etc/my.cnf /etc/my3306.cnf
创建error文件
touch /data7/mydata/3306/log/mysql-error.log
授权mysql数据目录
chown -R mysql:mysql /data7/mydata/ chmod 700 /data7/mydata/
初始化MySQL
mysqld --initialize-insecure --user=mysql --basedir=/mysql/mysql57 --datadir=/data7/mydata/3306/data --innodb_data_file_path=ibdata1:1024M:autoextend --lower_case_table_names=1
注意初始化时必须初始化字符集
配置system管理
vim /usr/lib/systemd/system/mysqld3306.service [Unit] Description=MySQL 3306 Server After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/mysql/mysql80/bin/mysqld --defaults-file=/etc/my3306.cnf LimitNOFILE = 65536 LimitNPROC = 65535 # 重启条件 Restart=on-failure # 退出码为1不重启，即正常shutdown RestartPreventExitStatus=1 # 重启条件满足后等多久自动重启(秒) RestartSec=10 # 五分钟内只能重启两次，第三次不重启了 StartLimitInterval=300 # 五分钟内只能重启两次，第三次不重启了 StartLimitBurst=2 # 服务启动的超时时间，单位秒 TimeoutStartSec=30 # 服务关闭的超时时间，单位秒 TimeoutStopSec=30
生效
systemctl daemon-reload
启动实例
systemctl start mysqld3306
重置启动次数
systemctl reset-failed mysqld3306.service
查看状态
systemctl status mysqld3306
设置便捷登录
vim /usr/local/bin/3306_mysql_login.sh mysql -A -S /data7/mydata/3306/socket/mysql.sock -p
授权
chmod 755 /usr/local/bin/3306_login.sh
修改密码
alter user root@localhost identified by '123456'; flush privileges;
##########################END###########################
