1.选择你所需要的MySQL版本,从官网下载MySQL安装包,可下载到本地,或直接在服务器上下载:
https://downloads.mysql.com/archives/community/
或
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.18-linux-glibc2.5-i686.tar.gz
2.目录信息
版本 | mysql-5.7.18 |
---|---|
安装路径 | /data/mysql |
端口 | 13307(可定义) |
数据目录 | /data/mysql/data/13307 |
3.安装步骤
##添加mysql用户组
shell> groupadd -g 499 mysql
##添加mysql用户
shell> useradd -g mysql mysql
##解压安装包
shell> tar -zxvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz -C /data/
##重命名解压包
shell> mv /data/mysql-5.7.18-linux-glibc2.5-x86_64 /data/mysql
##创建mysql data目录
shell> mkdir -p /data/mysql/data/13307
shell> mkdir -p /data/mysql/base/mysql_tmp
shell> mkdir -p /data/mysql/binlogw/13307
##编辑my.cnf.13307文件,可根据自身需要配置参数
shell> vim /data/mysql/my.cnf.13307
[mysqld]
basedir = /data/mysql/base
datadir = /data/mysql/data/13307/
explicit_defaults_for_timestamp = 0
innodb_buffer_pool_instances = 16
innodb_buffer_pool_size = 128G
innodb_data_file_path = ibdata1:1G:autoextend
#innodb_flush_neighbors = 0
innodb_io_capacity = 2000
innodb_io_capacity_max = 20000
innodb_log_files_in_group = 2
innodb_log_file_size = 2048M
#innodb_lru_scan_depth = 4096
log-bin = /data/mysql/binlogw/13307/mysql-bin
lower_case_table_names = 1
port = 13307
server_id = 571394631
socket = /data/mysql/data/13307//mysql_13307.sock
tmpdir = /data/mysql/base/mysql_tmp
##max parm
max_user_connections = 4000
min_examined_row_limit = 0
max_connections = 5000
max_allowed_packet = 64M
##timeout
wait_timeout = 3600
interactive_timeout = 3600
slave_net_timeout = 30
skip-name-resolve = 1
default_storage_engine = InnoDB
character_set_server = utf8mb4
skip_external_locking = 1
back_log = 1500
default_time_zone = '+8:00'
symbolic_links = 0
secure_file_priv = ''
sql_mode = ''
# INNODB #
innodb_stats_on_metadata = 0
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 64M
innodb_file_per_table = 1
innodb_max_dirty_pages_pct = 60
innodb_flush_log_at_trx_commit = 1
innodb_adaptive_flushing = 1
innodb_thread_concurrency = 0
innodb_stats_persistent = 1
innodb_purge_threads = 4
innodb_use_native_aio = 1
innodb_autoinc_lock_mode = 2
innodb_change_buffering = inserts
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_print_all_deadlocks = 1
innodb_spin_wait_delay = 6
innodb_strict_mode = 1
innodb_online_alter_log_max_size = 1G
innodb_stats_persistent_sample_pages = 64
innodb_large_prefix = 1
innodb_sync_spin_loops = 100
innodb_sort_buffer_size = 64M
# REPLICATION #
binlog_row_image = FULL
binlog_checksum = CRC32
master_verify_checksum = on
slave_sql_verify_checksum = on
binlog_format = row
relay_log = mysql-relay-bin
log_slave_updates = 1
skip_slave_start = 1
expire_logs_days = 14
sync_binlog = 1
relay_log_info_repository = TABLE
relay_log_recovery = 1
master_info_repository = TABLE
binlog_gtid_simple_recovery = 1
# CACHES AND LIMITS #
key_buffer_size = 32M
tmp_table_size = 256M
max_heap_table_size = 256M
table_open_cache = 4096
table_open_cache_instances = 32
query_cache_type = 0
query_cache_size = 0
thread_cache_size = 1024
open_files_limit = 65535
join_buffer_size = 32M
read_buffer_size = 16M
read_rnd_buffer_size = 32M
sort_buffer_size = 8M
# LOGGING #
# DO NOT CHANGE
log_error = mysql-error.log
slow_query_log = 1
slow_query_log_file = mysql-slow.log
long_query_time = 2
log_slow_admin_statements = 1
log_slow_slave_statements = 1
log_bin_trust_function_creators = 1
binlog_cache_size = 1M
binlog_rows_query_log_events=1
##slave
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 16
slave_preserve_commit_order = 1
slave_pending_jobs_size_max = 128M
slave_transaction_retries = 128
slave_rows_search_algorithms='INDEX_SCAN,HASH_SCAN'
# SEMISYNC #
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
# GTID #
gtid_mode = ON
enforce_gtid_consistency = 1
# mysql5.7 #
# new innodb settings #
innodb_page_cleaners = 16
innodb_buffer_pool_dump_pct = 40
log_timestamps = system
show_compatibility_56 = on
##performance_schema
optimizer_switch='index_merge_intersection=off,use_index_extensions=off'
performance_schema_instrument = 'wait/lock/metadata/sql/mdl=ON'
##修改目录用户组属性
shell> chown -R mysql:mysql /data/mysql
##编辑环境变量
shell> vim /etc/profile
##在文件后添加以下内容,若已有PATH变量,直接添加/data/mysql/bin到PATH即可,以:分割
export MYSQL_HOME=/data/mysql
export PATH=${MYSQL_HOME}/bin:$PATH
##让PATH变量生效
shell> source /etc/profile
##初始化数据库,--initialize-insecure选项初始化后root账号默认为空
shell> cd /data/mysql
shell> ./bin/mysqld --defaults-file=/data/mysql/my.cnf.13307 --initialize-insecure --user=mysql
##启动数据库
shell> cd /data/mysql
shell> ./bin/mysqld_safe --defaults-file=/data/mysql/my.cnf.13307 --user=mysql &
##启动成功后连接数据库
shell> mysql -S /data/mysql/data/13307/mysql_13307.sock -uroot
##创建快捷登陆方式登陆
ln -s /data/mysql/data/13307/mysql_13307.sock /tmp/mysql.sock
##创建库及用户,并给用户授权
grant all privileges on *.* to 'root'@'%' identified by '123456';
create database scm default character set utf8 default collate utf8_general_ci;
grant all on scm.* to 'scm'@'%' identified by '123456';
create database amon default character set utf8 default collate utf8_general_ci;
grant all on amon.* to 'amon'@'%' identified by '123456';
create database rman default character set utf8 default collate utf8_general_ci;
grant all on rman.* to 'rman'@'%' identified by '123456';
create database hue default character set utf8 default collate utf8_general_ci;
grant all on hue.* to 'hue'@'%' identified by '123456';
create database metastore default character set utf8 default collate utf8_general_ci;
grant all on metastore.* to 'hive'@'%' identified by '123456';
create database sentry default character set utf8 default collate utf8_general_ci;
grant all on sentry.* to 'sentry'@'%' identified by '123456';
create database nav default character set utf8 default collate utf8_general_ci;
grant all on nav.* to 'nav'@'%' identified by '123456';
create database oozie default character set utf8 default collate utf8_general_ci;
grant all on oozie.* to 'oozie'@'%' identified by '123456';
flush privileges;
初始化cm数据库:/opt/cloudera/cm/schema/scm_prepare_database.sh mysql scm scm -h127.0.0.1 -P3306 -uroot -proot -v
##导出数据库为dbname的表结构
mysqldump -uuser -pdbpasswd -d dbname -P3306 >db.sql;
##导入表结构
use dbname
source db.sql