mysql5.7安装

一、环境说明

LINUX版本：CentOS Linux release 7.2.1511 (Core)
Mysql版本：5.7.32
安装方式：mysql5.7.32二进制包解压缩安装
安装包名：mysql-5.7.32-el7-x86_64.tar.gz

二、部署安装

2.1 添加mysql用户组和mysql用户

su - root
groupadd mysql
useradd -g mysql mysql

vi /etc/security/limits.conf
mysql		soft	nproc		2047
mysql		hard	nproc		16384
mysql		soft	nofile	1024
mysql		hard	nofile	65536

2.2 创建文件夹

mkdir -p /data/{mysql3306/{data,tmp,binlog,logs},backup,scripts}
chown -R mysql:mysql /data/mysql3306
chown -R mysql:mysql /data/backup
chown -R mysql:mysql /data/scripts
chmod -R 755 /data/mysql3306
chmod -R 755 /data/backup
chmod -R 755 /data/scripts

2.3 解压缩mysql安装程序

tar -zxvf mysql-5.7.32-el7-x86_64.tar.gz -C /usr/local
mv /usr/local/mysql-5.7.32-el7-x86_64/ /usr/local/mysql
su – mysql
echo 'export PATH=$PATH:/usr/local/mysql/bin' >> .bash_profile
source .bash_profile

2.4 创建参数文件

su - mysql
vi /data/mysql3306/my3306.cnf
[mysql]
no-auto-rehash
prompt=(\u@\h) [\d]>\_
 
[mysqld]
\# basic settings #
server_id = 14233306
user = mysql
report-host = 10.48.14.23
port = 3306
basedir = /usr/local/mysql
datadir = /data/mysql3306/data
socket = /data/mysql3306/mysql.sock
pid-file = /data/mysql3306/mysql.pid
log_error = /data/mysql3306/logs/error.log
tmpdir = /data/mysql3306/tmp
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER"
autocommit = 1
character_set_server=utf8
transaction_isolation = REPEATABLE-READ
explicit_defaults_for_timestamp = 1
max_allowed_packet = 256m
event_scheduler = 1
log_timestamps = system
lower_case_table_names = 1
 
\# connection #
interactive_timeout = 43200
wait_timeout = 43200
lock_wait_timeout = 43200
skip_name_resolve = 1
max_connections = 10000
max_connect_errors = 100000
 
\# table cache performance settings #
table_open_cache = 4096
table_definition_cache = 4096
table_open_cache_instances = 64
 
\# session memory settings #
read_buffer_size = 16M
read_rnd_buffer_size = 32M
sort_buffer_size = 32M
tmp_table_size = 64M
join_buffer_size = 128M
thread_cache_size = 64
 
\# log settings #
slow_query_log  = 1
slow_query_log_file = ../logs/slow_query.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_throttle_queries_not_using_indexes = 10
expire_logs_days = 0
long_query_time = 2
min_examined_row_limit = 100
binlog-rows-query-log-events = 1
log-bin-trust-function-creators = 1
max_binlog_size = 512M
 
\# InnoDB settings #
innodb_page_size = 16384
innodb_data_file_path = ibdata1:12m:autoextend
innodb_buffer_pool_size = 8g
innodb_buffer_pool_instances = 4
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 4096
innodb_lock_wait_timeout  = 50
innodb_io_capacity = 10000
innodb_io_capacity_max = 20000
innodb_flush_method = O_DIRECT
innodb_undo_logs = 128
innodb_undo_tablespaces = 0 
innodb_flush_neighbors = 0
innodb_log_buffer_size = 10m
innodb_log_file_size = 100m
innodb_log_files_in_group = 3
innodb_purge_threads = 4
innodb_large_prefix = 1
innodb_thread_concurrency = 16
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 16m
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_file_per_table = 1
innodb_stats_persistent_sample_pages = 16
innodb_autoinc_lock_mode = 2
innodb_online_alter_log_max_size = 100m
innodb_open_files = 4096
innodb_flush_log_at_trx_commit = 1
 
\# Myisam settings #
myisam_max_sort_file_size=512M
myisam_sort_buffer_size  =8M
 
\# replication settings #
skip_slave_start = 1
slave_parallel_workers = 4
slave_parallel_type = 'LOGICAL_CLOCK'
master_info_repository = table
relay_log_info_repository = table
sync_binlog = 1
gtid_mode = on
enforce_gtid_consistency = 1
log-slave-updates = 1
log_bin = /data/mysql3306/binlog/mysql-bin
log_bin_index = /data/mysql3306/binlog/mysql-bin.index
binlog_format = row
binlog_row_image = full
binlog_rows_query_log_events = 1
relay_log_index = /data/mysql3306/logs/relaylog.index
relay_log = /data/mysql3306/logs/relay.log
#relay_log_recovery = 1
slave_skip_errors = ddl_exist_errors,1595,1782,1590
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
 
\# binlog-do-db=
binlog-ignore-db=mysql
binlog-ignore-db=performance_schema
binlog-ignore-db=information_schema
binlog-ignore-db=test
binlog-ignore-db=sys
#replicate-ignore-table=db_name.table_name

\# semi sync replication settings #
#plugin_load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave.so'
#rpl_semi_sync_master_enabled = 1
#rpl_semi_sync_master_timeout = 10000
#rpl_semi_sync_slave_enabled= 1
 
\# password plugin #
#validate_password_policy = strong
#validate-password = force_plus_permanent
\# mysqlshell dump
thread_stack = 10485760
log_bin_trust_function_creators = 1

2.5 创建mysql实例

创建软件链接

[root@mysql8 ~]# ln -s /data/mysql3306/my3306.cnf /etc/my.conf

利用mysqld创建实例：

su - mysql
/usr/local/mysql/bin/mysqld --initialize --datadir=/data/mysql3306/data --basedir=/usr/local/mysql

注意：在日志中会有登录MYSQL的密码。

2.6 启动实例

 [root@mysql8 ~]# ln -s /data/mysql3306/mysql.sock /tmp/mysql.sock

编写启动脚本并启动实例：

su - mysql
echo '/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql3306/my3306.cnf --socket=/data/mysql3306/mysql.sock &' > /home/mysql/start3306.sh
chmod 777 /home/mysql/start3306.sh
sh start3306.sh

2.7 登录实例并修改root密码

利用创建实例时使用的密码登录实例：

mysql -uroot -p'tX,0mdcC(p.Q' 
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> alter user root@localhost identified by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

2.8 设置开机自动启动

su - root
[root@localhost ~]# cat >> /etc/rc.d/rc.local <<EOF
/home/mysql/start3306.sh
EOF