#!/bin/bash MySQL_Package=Percona-Server-5.7.22-22-Linux.x86_64.ssl101.tar.gz Package_Source=Percona-Server-5.7.22-22-Linux.x86_64.ssl101 PORT=3306 PrepareENV() { #Remove MySQL Local yum -y remove mysql mysql-server rm -rf /etc/my.cnf userdel -r mysql groupdel mysql echo ' mysql soft nproc 65536 mysql hard nproc 65536 mysql soft nofile 65536 mysql hard nofile 65536 ' >> /etc/security/limits.conf groupadd mysql useradd -g mysql -s /sbin/nologin -d /usr/local/mysql/ -MN mysql yum -y install gcc gcc-c++ make yum -y install perl perl-devel yum -y install ncurses ncurses-devel openssl-devel bison } InstallMySQL() { tar zxvf ${MySQL_Package} mv ${Package_Source} /usr/local/mysql if [ $? -eq 0 ];then echo "Install MySQL successfully !" chown -R mysql:mysql /usr/local/mysql echo 'export PATH=/usr/local/mysql/bin:$PATH' >>/root/.bash_profile else echo "Install MySQL error ,exit !" exit fi } InitMySQL() { mkdir -p /app/mysqldata/{$PORT/{data,tmp,binlog,redolog,undolog},backup,scripts} # Init config file echo -e ' [client] user=dba_user password=msds007 [mysql] no-auto-rehash prompt = (u@h) [d]>\_ default-character-set = utf8mb4 [mysqld] ########basic settings######## server-id = 1013306 port = 3306 user = mysql bind_address = 192.168.1.101 autocommit = 1 character_set_server=utf8mb4 skip_name_resolve = 1 max_connections = 800 max_connect_errors = 1000 datadir = /app/mysqldata/3306/data socket = /app/mysqldata/3306/mysql.sock transaction_isolation = READ-COMMITTED explicit_defaults_for_timestamp = 1 join_buffer_size = 134217728 tmp_table_size = 67108864 tmpdir = /app/mysqldata/3306/tmp max_allowed_packet = 16777216 sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER" interactive_timeout = 1800 wait_timeout = 1800 read_buffer_size = 16777216 read_rnd_buffer_size = 33554432 sort_buffer_size = 33554432 ########log settings######## log_error = /app/mysqldata/3306/error.log slow_query_log = 1 slow_query_log_file = /app/mysqldata/3306/slow.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 = 7 long_query_time = 1 min_examined_row_limit = 100 ########replication settings######## master_info_repository = TABLE relay_log_info_repository = TABLE log_bin = /app/mysqldata/3306/binlog/mysql-bin.log sync_binlog = 1 gtid_mode = on enforce_gtid_consistency = 1 log_slave_updates binlog_format = row relay_log = /app/mysqldata/3306/binlog/relay-bin.log relay_log_recovery = 1 binlog_gtid_simple_recovery = 1 slave_skip_errors = ddl_exist_errors ########innodb settings######## innodb_page_size = 8192 innodb_buffer_pool_size = 1G #生产环境做调整 innodb_buffer_pool_instances = 8 innodb_buffer_pool_load_at_startup = 1 innodb_buffer_pool_dump_at_shutdown = 1 innodb_lru_scan_depth = 2000 innodb_lock_wait_timeout = 5 innodb_io_capacity = 4000 innodb_io_capacity_max = 8000 innodb_flush_method = O_DIRECT innodb_log_group_home_dir = /app/mysqldata/3306/redolog/ innodb_undo_directory = /app/mysqldata/3306/undolog/ innodb_undo_logs = 128 innodb_undo_tablespaces = 3 innodb_flush_neighbors = 1 innodb_log_file_size = 128M #生产环境做调整 innodb_log_buffer_size = 16777216 innodb_purge_threads = 4 innodb_large_prefix = 1 innodb_thread_concurrency = 0 innodb_print_all_deadlocks = 1 innodb_strict_mode = 1 innodb_sort_buffer_size = 67108864 ########semi sync replication settings######## plugin_dir=/usr/local/mysql/lib/plugin plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" loose_rpl_semi_sync_master_enabled = 1 loose_rpl_semi_sync_slave_enabled = 1 loose_rpl_semi_sync_master_timeout = 5000 [mysqld-5.7] innodb_buffer_pool_dump_pct = 40 innodb_page_cleaners = 4 innodb_undo_log_truncate = 1 innodb_max_undo_log_size = 2G innodb_purge_rseg_truncate_frequency = 128 binlog_gtid_simple_recovery=1 log_timestamps=system transaction_write_set_extraction=MURMUR32 show_compatibility_56=on #binlog_checksum=NONE # only for group replication #group replication setting #plugin_load=group_replication.so #loose_group_replication_group_name=e5720089-6b00-11e5-b248-fa163e30f9a2 #loose_group_replication_start_on_boot=1 #loose_group_replication_recovery_user=rpl_user #loose_group_replication_recovery_password=rpl_pass '> /app/mysqldata/$PORT/my.cnf #set ENV echo -e ' #set env MYSQL_USER=dba_user MYSQL_PASS=msds007 #check_parameter if [ $# -ne 1 ] then HOST_PORT=3306 else HOST_PORT=$1 fi ' >/app/mysqldata/scripts/mysql_env.ini chmod 600 /app/mysqldata/scripts/mysql_env.ini #################################################### #Make scirpt for use echo -e ' #!/bin/sh source /app/mysqldata/scripts/mysql_env.ini echo "Startup MySQL Service: localhost_"${HOST_PORT} /usr/local/mysql/bin/mysqld_safe --defaults-file=/app/mysqldata/${HOST_PORT}/my.cnf & ' >/app/mysqldata/scripts/mysql_db_startup.sh echo ' #!/bin/sh source /app/mysqldata/scripts/mysql_env.ini echo "Shutdown MySQL Service: localhost_"${HOST_PORT} /usr/local/mysql/bin/mysqladmin -u${MYSQL_USER} -p${MYSQL_PASS} -S /app/mysqldata/${HOST_PORT}/mysql.sock shutdown ' > /app/mysqldata/scripts/mysql_db_shutdown.sh echo -e ' #!/bin/sh source /app/mysqldata/scripts/mysql_env.ini echo "Login MySQL Service: localhost_"${HOST_PORT} /usr/local/mysql/bin/mysql -u${MYSQL_USER} -p${MYSQL_PASS} -S /app/mysqldata/${HOST_PORT}/mysql.sock $2 ' > /app/mysqldata/scripts/mysqlplus.sh chmod 755 /app/mysqldata/scripts/{mysql_db*,mysqlplus.sh} echo 'export PATH=/app/mysqldata/scripts:$PATH' >>/root/.bash_profile #################################################### chown -R mysql:mysql /app/mysqldata /usr/local/mysql/bin/mysqld --defaults-file=/app/mysqldata/$PORT/my.cnf --initialize --user=mysql /usr/local/mysql/bin/mysql_ssl_rsa_setup --defaults-file=/app/mysqldata/$PORT/my.cnf #################################################### echo "start mysqld at boot time" #ln -s /app/mysqldata/$PORT/my.cnf /etc/my.cnf #cp -rf /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql #chkconfig --add mysql #chkconfig mysql on #service mysql stop #service mysql start #ln -s /app/mysqldata/$PORT/mysql.sock /tmp/mysql.sock /app/mysqldata/scripts/mysql_db_startup.sh 3306 > /app/mysqldata/3306/mysql_db_startup.log 2>&1 chmod +x /etc/rc.d/rc.local echo '/app/mysqldata/scripts/mysql_db_startup.sh 3306 > /app/mysqldata/3306/mysql_db_startup.log 2>&1' >> /etc/rc.local sleep 5 echo "update root password" tmppasswd=`grep password /app/mysqldata/$PORT/error.log | awk '{print $NF}'` /usr/local/mysql/bin/mysql -uroot -p$tmppasswd -S /app/mysqldata/$PORT/mysql.sock --connect-expired-password -e "set password=password('msds007')" /usr/local/mysql/bin/mysql -uroot -pmsds007 -S /app/mysqldata/$PORT/mysql.sock -e "grant all on *.* to dba_user@'%' identified by 'msds007'" /usr/local/mysql/bin/mysql -uroot -pmsds007 -S /app/mysqldata/$PORT/mysql.sock -e "flush privileges" /usr/local/mysql/bin/mysql -uroot -pmsds007 -S /app/mysqldata/$PORT/mysql.sock -e "select user,host,authentication_string from mysql.user" } PrepareENV InstallMySQL InitMySQL