1 #!/bin/bash 2 3 userdir=/usr/local 4 basedir=/data 5 port=3306 6 socket=/data/mysql/3306/run/mysql.sock 7 newpwd="123456" 8 tarfile="mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz" 9 #------------------------------------------------------------------ 10 11 tarfiledir="${tarfile%.tar.gz*}" 12 realpath=$(readlink -f "$0") 13 realdir=$(dirname "$realpath") 14 15 logfile=${realdir}/mysql_install.log 16 true > ${logfile} 17 18 #adduser 19 #mkdir -p ${userdir} 20 21 yum install libaio libaio-devel expect -y> /dev/null 2>&1 22 23 id mysql >/dev/null 2>&1 24 if [ $? -eq 1 ] 25 then 26 groupadd -r mysql 27 useradd -g mysql -s /bin/false -r mysql 28 echo "create user mysql by this script" >> ${logfile} 29 else 30 echo "user mysql has been created before run this script" >> ${logfile} 31 fi 32 33 #init dir 34 35 36 cd "${realdir}" 37 #wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz 38 if [ ! -d ${tarfiledir} ] 39 then 40 tar -xf ${tarfile} -C ${userdir}/src 41 fi 42 43 if [ ! -d "${basedir}" ] 44 then 45 46 mkdir -p ${basedir}/mysql/3306/{data,tmp,log,innodb,relay-log,binlog,run,etc,redo,undo,ssl} 47 fi 48 49 50 cd ${userdir} 51 52 ln -s ${userdir}/src/mysql-5.7.22-linux-glibc2.12-x86_64 mysql 53 cd ${userdir}/mysql 54 mkdir mysql-files 55 chmod 750 mysql-files 56 cd ${userdir} 57 chown mysql.mysql mysql/ -R 58 59 cd ${basedir} 60 chown -R mysql.mysql ${basedir} 61 62 #init mysql 63 cat > ${basedir}/mysql/3306/etc/my.cnf << EOF 64 [client] 65 default-character-set = utf8mb4 66 67 [mysql] 68 default-character-set = utf8mb4 69 prompt=(\\u@\\h) [\\d]>\\_ 70 71 [mysqld] 72 ########basic settings######## 73 server-id = 1001 74 port = 3306 75 user = mysql 76 #skip-networking 77 #bind_address=118.31.66.53 78 #autocommit = 0 79 character_set_server=utf8mb4 80 skip_name_resolve = 1 81 max_connections = 100000 82 max_connect_errors = 5000 83 datadir = /data/mysql/3306/data 84 default_storage_engine=INNODB 85 transaction_isolation = READ-COMMITTED 86 explicit_defaults_for_timestamp = 1 87 join_buffer_size = 134217728 88 tmp_table_size = 67108864 89 tmpdir = /data/mysql/3306/tmp 90 socket = /data/mysql/3306/run/mysql.sock 91 pid-file = /data/mysql/3306/run/mysql.pid 92 max_allowed_packet = 16777216 93 sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER" 94 interactive_timeout = 1800 95 wait_timeout = 1800 96 lock_wait_timeout = 1800 97 read_buffer_size = 16777216 98 read_rnd_buffer_size = 33554432 99 sort_buffer_size = 33554432 100 key_buffer_size = 2147483648 101 group_concat_max_len = 102400 102 103 ########log settings######## 104 log_bin = /data/mysql/3306/binlog/bin.log 105 log_error = /data/mysql/3306/log/error.log 106 slow_query_log = 1 107 slow_query_log_file = /data/mysql/3306/log/slow.log 108 secure_file_priv='/root/' 109 log_queries_not_using_indexes = 1 110 log_slow_admin_statements = 1 111 log_slow_slave_statements = 1 112 log_throttle_queries_not_using_indexes = 10 113 expire_logs_days = 90 114 long_query_time = 0.75 115 min_examined_row_limit = 100 116 117 118 ########replication settings######## 119 master_info_repository = TABLE 120 relay_log_info_repository = TABLE 121 sync_binlog = 1 122 gtid_mode = on 123 enforce_gtid_consistency = 1 124 log_slave_updates = on 125 binlog_format = row 126 relay_log = /data/mysql/3306/relay-log/relay.log 127 relay_log_recovery = 1 128 binlog_gtid_simple_recovery = 1 129 slave_skip_errors = ddl_exist_errors 130 #read_only = on 131 #super_read_only = on 132 133 ########innodb settings######## 134 innodb_data_home_dir = /data/mysql/3306/innodb 135 innodb_data_file_path = ibdata1:1000M;ibdata2:1000M:autoextend 136 innodb_page_size = 8192 137 innodb_buffer_pool_size = 750M 138 innodb_buffer_pool_instances = 8 139 innodb_buffer_pool_load_at_startup = 1 140 innodb_buffer_pool_dump_at_shutdown = 1 141 innodb_lru_scan_depth = 500 142 innodb_lock_wait_timeout = 100 143 innodb_io_capacity = 4000 144 innodb_io_capacity_max = 8000 145 innodb_flush_method = O_DIRECT 146 innodb_flush_log_at_trx_commit = 1 147 innodb_file_format = Barracuda 148 innodb_file_format_max = Barracuda 149 innodb_log_group_home_dir = /data/mysql/3306/redo/ 150 innodb_undo_directory = /data/mysql/3306/undo/ 151 innodb_undo_logs = 128 152 innodb_undo_tablespaces = 3 153 innodb_flush_neighbors = 1 154 innodb_log_file_size = 2G 155 innodb_log_buffer_size = 33554432 156 innodb_purge_threads = 8 157 innodb_large_prefix = 1 158 innodb_thread_concurrency = 64 159 innodb_print_all_deadlocks = 1 160 innodb_strict_mode = 1 161 innodb_sort_buffer_size = 67108864 162 metadata_locks_hash_instances = 64 163 innodb_open_files = 4096 164 table_open_cache = 4096 165 table_definition_cache = 4096 166 table_open_cache_instances = 128 167 thread_cache_size = 64 168 innodb_online_alter_log_max_size=1G 169 170 ########semi sync replication settings######## 171 plugin_dir=/usr/local/mysql/lib/plugin 172 plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" 173 loose_rpl_semi_sync_master_enabled = 1 174 loose_rpl_semi_sync_slave_enabled = 1 175 loose_rpl_semi_sync_master_timeout = 5000 176 177 EOF 178 179 ${userdir}/mysql/bin/mysqld --defaults-file=${basedir}/mysql/3306/etc/my.cnf --initialize --user=mysql && echo "init mysql successful" 180 181 ${userdir}/mysql/bin/mysql_ssl_rsa_setup --datadir=${basedir}/mysql/3306/ssl && echo "ssl setup successful" 182 183 ${userdir}/mysql/bin/mysqld_safe --defaults-file=${basedir}/mysql/3306/etc/my.cnf --user=mysql & 184 185 186 #update root's pwd 187 188 genpwd=$(cat ${basedir}/mysql/3306/log/error.log | grep "password is generated for root@localhost") || genpwd="" 189 190 oldpwd=${genpwd##*root@localhost: } 191 192 #${userdir}/mysql/bin/mysql -uroot -p${oldpwd} -S $socket -e "alter user user() identified by \"${newpwd}\"" 193 194 sleep 15 195 196 cd ${realdir} 197 cat > tmp_expect.sh << EOF 198 #!/usr/bin/expect 199 spawn ${userdir}/mysql/bin/mysql -uroot -p -S $socket 200 expect { 201 "password" { send "${oldpwd}\n";exp_continue;} 202 "mysql>" {send "alter user user() identified by \"${newpwd}\";\n"} 203 } 204 interact 205 EOF 206 chmod 755 ./tmp_expect.sh 207 expect ./tmp_expect.sh && rm -rf ./tmp_expect.sh