福州网站建设招商,网站设计师培训学校,网站后台申请邮箱,网页版微信能传文件吗mysql_install - 适用于生产环境单实例快速部署
MySQL8.0 自动安装脚本
mysql8_install.sh#xff08;执行前修改一下脚本里的配置参数#xff0c;改成你自己的#xff09;#xff08;博客末尾#xff09;
my_test.cnf#xff08;博客末尾#xff09;#xff08;这个…mysql_install - 适用于生产环境单实例快速部署
MySQL8.0 自动安装脚本
mysql8_install.sh执行前修改一下脚本里的配置参数改成你自己的博客末尾
my_test.cnf博客末尾这个是模板文件基本上不用改mysql8_install.sh脚本执行的时候会自动替换里面的portserver_idinnodb_buffer_pool_size等
mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz
shell wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz
三个文件放在同一个目录下例如/root/soft/ Centos8 注意事项
shell yum install jemalloc -y
内存管理器jemalloc库文件名字已经变更需要建立一个软连接
shell ln -s /usr/lib64/libjemalloc.so.2 /usr/lib64/libjemalloc.so 1安装并启动mysql进程主和从库都执行
shell /bin/bash mysql8_install.sh
注my.cnf配置文件默认在/etc/目录下文件名是以你的数据库名命名例my_test.cnfmysql.sock在/tmp目录下。
数据存放在/data/mysql/目录下。2配置主从复制从库执行
shell /bin/bash mysql8_install.sh repl
3配置组复制先在Primary节点上执行再到Secondary节点上执行
注先把3个节点MySQL实例启动后再开始搭建mgr同时修改脚本里的ip地址和端口和hosts对应的主机名和地址
shell /bin/bash mysql8_install.sh mgr 注配置成功后会在data数据目录下生成mysqld-auto.cnf配置文件。 脚本 mysql8_install.sh
#!/bin/bash
# 适用于生产环境单实例快速部署操作系统CentOS 7。echo 正在安装MySQL软件.......useradd mysql
useradd nagios
useradd zabbixsleep 2######配置参数######
mysql8_versionmysql-8.0.32-linux-glibc2.12-x86_64.tar.xz
mysql8_version_dirmysql-8.0.32-linux-glibc2.12-x86_64################################主从复制配置################################
######同步复制用户######
repl_userrepl
repl_passwdsysrepl
#############################root密码######
root_passwd123456
#############################DBA管理用户######
dba_useradmin
dba_passwdhechunyang
#############################8.0克隆用户######
clone_userclone_user
clone_passwd123456
#######################################################mgr组复制配置######################################修改hosts文件######
cat EOF /etc/hosts192.168.71.11 mgr1
192.168.71.12 mgr2
192.168.71.13 mgr3EOF
#######################mysql_port3306 # 数据库实例对外请求端口
primary_ip192.168.71.11 # 每个mgr节点IP
secondary1_ip192.168.71.12 # 每个mgr节点IP
secondary2_ip192.168.71.13 # 每个mgr节点IPprimary_port33061 # mgr primary节点通讯端口
secondary1_port33062 # mgr secondary1节点通讯端口
secondary2_port33063 # mgr secondary2节点通讯端口local_ip192.168.71.11 # 每个mgr节点IP不相同
local_port33061 # 每个mgr节点通讯端口port不相同################################以下代码不用修改################################
if [ $1 repl ]
then
while true
doread -t 30 -p 输入你的主库IP: master_ipread -t 30 -p 输入你的主库端口号: master_portif [[ -z $master_ip || -z $master_port ]]thencontinueelseecho echo 主库IP是 $master_ipecho 主库端口号是 $master_portbreak fi
done/usr/local/mysql/bin/mysql -h127.0.0.1 -u$dba_user -p$dba_passwd -P$master_port --connect-expired-password -e CHANGE MASTER TO MASTER_HOST$master_ip,MASTER_USER$repl_user,MASTER_PASSWORD$repl_passwd,MASTER_PORT$master_port,MASTER_AUTO_POSITION 1,MASTER_CONNECT_RETRY10; START SLAVE;echo MySQL主从复制同步已经初始化完毕。exit 0
fi################################################
if [ $1 mgr ]
thenwhile true
doread -t 30 -p 是Primary吗是请输入yes否输入no: is_primaryif [[ -z $is_primary ]]thencontinueelseif [ $is_primary yes ] || [ $is_primary no ]thenbreak elseecho 你输入一个错误的字符$is_primary请重新输入...continuefifi
doneif [ $is_primary yes ]
then/usr/local/mysql/bin/mysql -h127.0.0.1 -u$dba_user -p$dba_passwd -P$mysql_port --connect-expired-password -e INSTALL PLUGIN group_replication SONAME group_replication.so; set persist group_replication_group_name 3b12b5bd-f0c6-11e9-9778-000c2900afc6;set persist group_replication_local_address ${local_ip}:${local_port}; set persist group_replication_group_seeds ${primary_ip}:${primary_port},${secondary1_ip}:${secondary1_port},${secondary2_ip}:${secondary2_port};SET GLOBAL group_replication_bootstrap_groupON; CHANGE MASTER TO MASTER_USER$repl_user,MASTER_PASSWORD$repl_passwd FOR CHANNEL group_replication_recovery;START GROUP_REPLICATION;select sleep(5);select * from performance_schema.replication_group_members;SET GLOBAL group_replication_bootstrap_groupOFF;else/usr/local/mysql/bin/mysql -h127.0.0.1 -u$dba_user -p$dba_passwd -P$mysql_port --connect-expired-password -e INSTALL PLUGIN group_replication SONAME group_replication.so; set persist group_replication_group_name 3b12b5bd-f0c6-11e9-9778-000c2900afc6;set persist group_replication_local_address ${local_ip}:${local_port}; set persist group_replication_group_seeds ${primary_ip}:${primary_port},${secondary1_ip}:${secondary1_port},${secondary2_ip}:${secondary2_port}; SET GLOBAL group_replication_bootstrap_groupOFF; CHANGE MASTER TO MASTER_USER$repl_user,MASTER_PASSWORD$repl_passwd FOR CHANNEL group_replication_recovery;START GROUP_REPLICATION;select sleep(5);select * from performance_schema.replication_group_members;fiecho MySQL Mgr组复制已经初始化完毕。
exit 0fi################################################ps aux | grep mysql | grep -v grep | grep -v bash
if [ $? -eq 0 ]
thenecho MySQL进程已经启动无需二次安装。exit 0
fiif [ ! -d /usr/local/${mysql8_version_dir} ]
thenyum install xz numactl* jemalloc* libaio* net-tools nload iftop sysstat wget vim ntpdate lrzsz -ytar -Jxvf ${mysql8_version} -C /usr/local/ln -s /usr/local/${mysql8_version_dir} /usr/local/mysqlchown -R mysql.mysql /usr/local/mysql/chown -R mysql.mysql /usr/local/mysql
elseln -s /usr/local/${mysql8_version_dir} /usr/local/mysqlchown -R mysql.mysql /usr/local/mysql/chown -R mysql.mysql /usr/local/mysql
fi while true
doread -t 30 -p 输入你的数据库名: dbnameread -t 30 -p 输入你的数据库端口号: dbportread -t 30 -p 输入MySQL serverId: serverIdread -t 30 -p 输入innodb_buffer_pool_size大小单位G: innodb_bp_sizeif [[ -z $dbname || -z $dbport || -z $serverId || -z $innodb_bp_size ]]thencontinueelseecho 数据库名字是 $dbnameecho 数据库端口是 $dbportecho MySQL serverId $serverIdecho BP大小是 $innodb_bp_size GBbreak fi
donesed s/test/$dbname/g;s/3306/$dbport/;s/413306/$serverId/;/innodb_buffer_pool_size/s/1/$innodb_bp_size/ my_test.cnf /etc/my_$dbname.cnfDATA_DIR/data/mysql/$dbname
[ ! -d $DATA_DIR ] mkdir -p $DATA_DIR/{data,binlog,relaylog,tmp,slowlog,log}; touch $DATA_DIR/log/error.log; chown -R mysql.mysql /data/mysql/if [ ls -A $DATA_DIR/data/ | wc -w -eq 0 ]
thencd /usr/local/mysqlecho echo 初始化MySQL数据目录......echo bin/mysqld --defaults-file/etc/my_$dbname.cnf --initialize --lower-case-table-names1 --usermysql --basedir/usr/local/mysql --datadir/data/mysql/$dbname/datasleep 2bin/mysqld_safe --defaults-file/etc/my_$dbname.cnf --usermysql
fiwhile true
donetstat -ntlp | grep $dbportif [ $? -eq 1 ]thenecho MySQL启动中稍等......sleep 5continueelseif [ ! -e /tmp/mysql_${dbname}.sock ];thenecho MySQL启动中稍等......sleep 5continuefibreakfi
doneps aux | grep mysql | grep -v grep | grep -v bash
if [ $? -eq 0 ]
thenecho MySQL安装完毕。
elseecho MySQL安装失败。exit 1
fi###更改root账号随机密码
random_passwdgrep temporary password $DATA_DIR/log/error.log | awk -F rootlocalhost: {print $2}/usr/local/mysql/bin/mysql -S /tmp/mysql_$dbname.sock -p$random_passwd --connect-expired-password -e alter user rootlocalhost identified by $root_passwd;
if [ $? -eq 0 ]
thenecho root账号随机密码更改完毕。
elseecho 密码更改失败再次重试更改。sleep 5/usr/local/mysql/bin/mysql -S /tmp/mysql_$dbname.sock -p$random_passwd --connect-expired-password -e alter user rootlocalhostidentified by $root_passwd;if [ $? -eq 0 ]thenecho root账号随机密码更改完毕。elseecho 密码更改失败exit 1fi
fi###创建同步账号和管理员账号
/usr/local/mysql/bin/mysql -S /tmp/mysql_$dbname.sock -p$root_passwd -e set sql_log_bin0;create user $repl_user% IDENTIFIED BY $repl_passwd; GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO $repl_user%; create user $dba_user% IDENTIFIED BY $dba_passwd; GRANT ALL on *.* to $dba_user% WITH GRANT OPTION;sed -i -r s/(PATH)/\1\/usr\/local\/mysql\/bin:/ /root/.bash_profile
source /root/.bash_profileecho MySQL账号初始化完毕。###安装clone插件
/usr/local/mysql/bin/mysql -S /tmp/mysql_$dbname.sock -p$root_passwd -e set sql_log_bin0;INSTALL PLUGIN CLONE SONAME mysql_clone.so; CREATE USER $clone_user% IDENTIFIED BY $clone_passwd;GRANT BACKUP_ADMIN,CLONE_ADMIN ON *.* TO $clone_user%;echo
echo clone克隆插件安装完毕。### 最后一步清空binlog文件恢复起始文件mysql-bin.000001和Position位置号153
/usr/local/mysql/bin/mysql -S /tmp/mysql_$dbname.sock -p$root_passwd -e reset master;my_test.cnf
[client]
port 3306
socket /tmp/mysql_test.sock
#default-character-set utf8
prompt(\\u\\h) [\\d]\\_# The MySQL server
#########Basic##################
[mysqld]
##bind_address 127.0.0.1
server-id 413306
port 3306
user mysql
basedir /usr/local/mysql
datadir /data/mysql/test/data
tmpdir /data/mysql/test/tmp
secure_file_priv /data/mysql/test/tmp
socket /tmp/mysql_test.sock
skip-external-locking
skip-name-resolve
skip-ssl
##init_connect SET global sql_mode
sql_mode
default_authentication_plugin mysql_native_password
activate_all_roles_on_login 1### Percona ###
#extra_port 13306
#extra_max_connections 100###skip-networking
default-storage-engine INNODB
character-set-server utf8
wait_timeout 3600
connect_timeout 20
interactive_timeout 3600
back_log 500
#event_scheduler ON
open_files_limit 65535
#thread_handling pool-of-threadslower_case_table_names 1###### binlog ######
log-bin /data/mysql/test/binlog/mysql-bin
binlog_format ROW
binlog_row_image FULL
binlog_row_metadata FULL
binlog_checksum NONE
binlog_transaction_dependency_tracking WRITESET
sync_binlog 1
gtid_mode on
enforce_gtid_consistency on
log_slave_updates 1
master_info_repository TABLE
relay_log_info_repository TABLE
max_binlog_size 128M
binlog_cache_size 1M
expire-logs-days 7#########replication#############
relay-log /data/mysql/test/relaylog/relay-log
slave-net-timeout 10
#rpl_semi_sync_master_enabled 1
#rpl_semi_sync_master_wait_no_slave 1
#rpl_semi_sync_master_timeout 1000
#rpl_semi_sync_slave_enabled 1
#skip-slave-start
log_slave_updates 1
relay_log_recovery 1
#slave_skip_errors 1062
read_only 0###### Mgr config ######
loose-transaction_write_set_extraction XXHASH64
loose-group_replication_start_on_boot ON ###是否随mysql启动Group Replication
loose-group_replication_bootstrap_group OFF ###是否是Group Replication的引导节点初次搭建集群的时候需要有一个节点设置为ON来启动Group Replication###参数设置为ON是为了标示以后加入集群的服务器都已这台服务器为基准。以后加入的就不需要进行设置loose-group_replication_group_name aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa
#loose-group_replication_local_address 192.168.148.41:33061
#loose-group_replication_group_seeds 192.168.148.41:33061,192.168.148.42:33072,192.168.148.39:33083
loose-group_replication_single_primary_mode ON
loose-group_replication_exit_state_action OFFLINE_MODE
loose-group_replication_transaction_size_limit 150000000 ###默认143M事务大小最大值2147483647大约2G当此系统变量设置为0时该组接受的事务大小没有限制。
loose-group_replication_enforce_update_everywhere_checks OFF ###在单主模式下设置为OFF多主模式下设置为ON。###offline_mode ON###MHA ############
relay_log_purge 1
######################parallel replication####
slave_parallel_type LOGICAL_CLOCK
slave_parallel_workers 24
slave_preserve_commit_order ON
###########################################slow log#############
slow_query_log 1
slow_query_log_file /data/mysql/test/log/mysql-slow.log
#log-slow-verbosity query_plan,explain
long_query_time 5#########error log#############
log-error /data/mysql/test/log/error.log
log_timestamps system#######per_thread_buffers############
max_connections4100
max_user_connections4000
max_connect_errors100000000
max_allowed_packet 256M
table_open_cache 6144
table_definition_cache 4096
sort_buffer_size 128K
read_buffer_size 128K
read_rnd_buffer_size 128k
join_buffer_size 128K
tmp_table_size 64M
max_heap_table_size 64M
bulk_insert_buffer_size 32M
thread_cache_size 64
#thread_concurrency 32
thread_stack 256K##MyISAM##
key_buffer_size 64M
myisam_sort_buffer_size 8M
concurrent_insert2
low_priority_updates1
myisam_recover_optionsBACKUP,FORCE######### InnoDB #############
sql_generate_invisible_primary_key 1
innodb_adaptive_hash_index 0
innodb_autoinc_lock_mode 2
innodb_numa_interleave 1
innodb_sort_buffer_size 32M
innodb_rollback_on_timeout 1
innodb_data_file_path ibdata1:10M:autoextend
innodb_buffer_pool_dump_at_shutdown 1
innodb_buffer_pool_load_at_startup 1
innodb_buffer_pool_size 1G
innodb_buffer_pool_instances 16
innodb_lru_scan_depth 2048
innodb_log_file_size 512M
innodb_log_buffer_size 64M
innodb_log_files_in_group 3
innodb_flush_log_at_trx_commit 2
innodb_lock_wait_timeout 10
innodb_sync_spin_loops 40
innodb_max_dirty_pages_pct 95
innodb_ddl_threads 16
innodb_ddl_buffer_size 1G
innodb_online_alter_log_max_size 1G
innodb_parallel_read_threads 32
innodb_thread_concurrency 0
innodb_thread_sleep_delay 500
innodb_concurrency_tickets 1000
log_bin_trust_function_creators 1
innodb_flush_method O_DIRECT
#innodb_flush_method O_DIRECT_NO_FSYNC
innodb_file_per_table 1
innodb_read_io_threads 16
innodb_write_io_threads 16
innodb_use_native_aio 1
innodb_io_capacity 800
innodb_purge_threads 16
innodb_purge_batch_size 32
innodb_old_blocks_time 1000
innodb_change_buffering all
transaction_isolation READ-COMMITTED
innodb_stats_on_metadata 0
performance-schema 1
performance-schema-instrument wait/lock/metadata/sql/mdlON####### NEW ########
##innodb_kill_idle_transaction 5###### SSD #########
#innodb_flush_neighbors 0
#innodb_log_block_size 4096
####################[mysqldump]
quick
max_allowed_packet 128M[mysql]
no-auto-rehash
#prompt(\\u\\h) [\\d]\\_
prompt(\\u\\h) [\\d]\\_[myisamchk]
key_buffer_size 64M
sort_buffer_size 256k
read_buffer 2M
write_buffer 2M[mysqlhotcopy]
interactive-timeout[mysqld_safe]
open-files-limit 65535
#flush_caches 1
#numa_interleave 1
malloc-lib /usr/lib64/libjemalloc.so
