mysql二进制安装

mysql单实例安装

1.环境检查

vi /etc/security/limits.conf

添加
* soft nofile 65535
* soft nproc 65535
* hard nproc 65535
* soft nofile 65535
* hard nofile 65535

不使用swap交换区
查看swappiness值大小cat /proc/sys/vm/swappiness

修改swappiness值 echo "vm.swappiness=10">>/etc/sysctl.conf

sysctl -p

centos7 的I/O算法为deadline
echo “deadline”>>/sys block/sda/queue/scheduler
查看 cat /sys/block/sda/queue/scheduler
中括号[]选中deadline即为格式修改成功

关闭selinux和防火墙
vi /etc/sysconfig/selinux
修改selinux状态为disabled

我使用的是centos7，所以防火墙是firewalld而不是iptables
所以先关闭firewalld
停止firewalld
systemctl stop firewalld.service

禁止开启firewalld
systemctl disable firewall.service

2.创建用户和用户组

groupadd mysql
useradd -r -g mysql -s /bin/false mysql

3.创建所需要的目录并附权限

mkdir -p /data/mysql5.7/data
mkdir -p /data/mysql5.7/tmp
mkdir -p /data/mysql5.7/logs/bin-log
mkdir -p /data/mysql5.7/logs/relay-log
mkdir -p /data/mysql5.7/undolog
mkdir -p /data/mysql5.7/logs/general
touch -p /data/mysql5.7/logs/err/err.log

chown -R mysql:mysql /data
chmod 775 /data

4.下载mysql软件包

cd /usr/local

解压

tar  -xvf mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz

mv mysql-5.7.34-linux-glibc2.12-x86_64 mysql5.7

5.环境变量配置

vi ~/.bash_profile

export PATH=$PATH:/usr/local/mysql5.7/bin

 

source  ~/.bash_profile 生效

 

6.mysql配置文件

vi /etc/my.cnf

[client]
port = 3306
socket = /data/mysql5.7/tmp/mysql.sock

[mysql]
prompt = "\\u@mysql \\R:\\m:\\s [\\d]> "

[mysqld]
################################### basic settings ####################################
server-id = 123
user = mysql
port = 3306
basedir	= /usr/local/mysql5.7
datadir	= /data/mysql5.7/data
socket = /data/mysql5.7/tmp/mysql.sock
tmpdir = /data/mysql5.7/tmp
pid_file = /data/mysql5.7/tmp/mysql5.7.pid
language = /usr/local/mysql5.7/share/english
character-set-server = utf8mb4
skip_name_resolve = 1
open_files_limit = 65535
back_log = 1024
lock_wait_timeout = 3600
interactive_timeout = 600
wait_timeout = 600
max_allowed_packet = 128M
tmp_table_size = 96M
max_heap_table_size = 96M
explicit_defaults_for_timestamp = 1
transaction_isolation = READ-COMMITTED
query_cache_size = 0
query_cache_type = 0
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

#################################### MyISAM ###########################################
table_open_cache = 1024
table_definition_cache = 1024
table_open_cache_instances = 64
sort_buffer_size = 16M
join_buffer_size = 16M
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

################################## thread settings #####################################
thread_stack = 512K
thread_cache_size = 768
skip-external-locking = 0
max_connections = 2000
max_connect_errors = 100000

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

##################################### log settings #####################################
log_timestamps = SYSTEM
log-error = /data/mysql5.7/logs/err.log
slow_query_log = 1
long_query_time = 3
slow_query_log_file = /data/mysql5.7/logs/slow-log/slow.log
min_examined_row_limit = 100
log_queries_not_using_indexes =1
log_throttle_queries_not_using_indexes = 60
log_slow_admin_statements = 0
log_slow_slave_statements = 0
#general_log = 0
#general_log_file = /data/mysql5.7/logs/general/general.log

################################## group replication ####################################
#transaction_write_set_extraction = XXHASH64
#loose-group_replication_group_name = 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaaaaaa'
#loose-group_replication_start_on_boot = off
#loose-group_replication_bootstrap_group = off
#loose-group_replication_local_address = '172.16.10.101:33080'
#loose-group_replication_group_seeds ='172.16.10.101:33080,172.16.10.102:33080,172.16.10.103:33080'
#loose-group_replication_single_primary_mode = off
#loose-group_replication_enforce_update_everywhere_checks = on

#################################### replication ########################################
log_bin = ON
log-bin = /data/mysql5.7/logs/bin-log/mysql-bin
max_binlog_cache_size = 1G
max_binlog_size = 1G
expire_logs_days = 7
binlog_format = row
binlog_checksum = 1
binlog_rows_query_log_events = ON
sync_binlog = 1
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 16
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = ON
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates = 1
slave-rows-search-algorithms = 'INDEX_SCAN,HASH_SCAN'
slave_load_tmpdir = /data/mysql5.7/tmp
relay-log-purge = 1
relay_log = /data/mysql5.7/logs/relay-log/mysql-relay
#read_only = ON

################################## Innodb settings ########################################
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30

#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 4
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 4M
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_max_undo_log_size = 1G
innodb_undo_directory = /data/mysql5.7/undolog
innodb_undo_log_truncate = 1
innodb_undo_tablespaces = 5
innodb_undo_logs = 128

innodb_io_capacity = 10000
innodb_io_capacity_max = 20000
innodb_flush_sync = 0
innodb_flush_neighbors = 0
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 75
#innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
innodb_lock_wait_timeout = 60
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 1G
internal_tmp_disk_storage_engine = InnoDB
innodb_stats_on_metadata = 0
innodb_support_xa = 1

# MySQL5.7
innodb_checksums = 1
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda

# 注意:开启innodb_status_output & innodb_status_output_locks后,可能会导致log-error文件增长较快
#innodb_status_output = 0
#innodb_status_output_locks = 0

#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]
max_allowed_packet = 128M

7.初始化

mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql &

8.配置启动脚本

cp /usr/local/mysql5.7/support-files/mysql.server   /etc/init.d/mysqld

vi  /etc/init.d/mysqld

配置对应路径

basedir=/usr/local/mysql5.7
datadir=/data/mysql5.7/data

 

chmod +x /etc/init.d/mysqld

 

 

9.启动

service mysqld status

service mysqld start

service mysqld stop

 

10.配置开机自启动

赋予可执行权限

chmod +x /etc/init.d/mysqld

添加服务

chkconfig   --add mysqld

显示服务列表

chkconfig --list

如果看到mysql的服务，并且3,4,5都是on的话则成功

 

 

 

在RHEL7中，使用systemctl 启动mysql

1，创建启动文件 .service

vim /usr/lib/systemd/system/mysqld.service

[Unit]

Description=MySQL Server

After=network.target

After=syslog.target

[Service]

User=mysql

Group=mysql

Type=forking

PermissionsStartOnly=true

ExecStart= /etc/init.d/mysqld start

ExecStop= /etc/init.d/mysqld stop

ExecReload= /etc/init.d/mysqld restart

LimitNOFILE = 5000

[Install]

WantedBy=multi-user.target

2，重新加载一下服务的配置文件

systemctl daemon-reload

3，开启mysql服务

systemctl  start  mysqld.service

4，关闭mysql服务

systemctl  stop  mysqld.service