0. 概述
最近鼓捣出了一个mysql安装脚本，将该脚本，mysql的my.cnf文件，mysql的安装包这三个文件放在同一个目录下面，执行sh mysql-auto-install.sh就可以完成mysql的一键安装，是不是很方便呢。
1. 准备mysql的安装包
mysql的安装包下载地址：https://dev.mysql.com/downloads/mysql/
注意需要下载的是linux GA版本 64bit，图中我用红色圈出来的部分
2. 准备mysql-auto-install.sh
#!/bin/sh # author: yang bao # time: 2019-04-08 # note: this script is used to install mysql on a new machine. # 1. at first, you should prepare mysql install binary package like 'mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz' # and mysql-auto-install.sh and my.cnf under same directory such as /root. # 2. then exec 'sh mysql-auto-install.sh' and see the output. # prepare mysql related file echo "$(date +"%Y%m%d %H:%M:%S") start to check mysql related file...." # check if there is mysql install binary package or not cnt1=`find . -maxdepth 1 -name 'mysql-*-linux-glibc2.12-x86_64.tar.gz' -type f | wc -l` if [ $cnt1 -lt 1 ]; then echo "It seems there isn't mysql install binary package in current directory!" exit 1 elif [ $cnt1 -gt 1 ]; then echo "It seems there are too many mysql install binary packages in current directory,\ please just keep one, rename or move the others!" exit 1 fi # check if there is my.cnf in current directory or not cnt2=`find . -maxdepth 1 -name 'my.cnf' -type f | wc -l` if [ $cnt2 -lt 1 ]; then echo "It seems there isn't my.cnf in current directory!" exit 1 fi # check if there is my.cnf in /etc or not cnt3=`find /etc -maxdepth 1 -name 'my.cnf' -type f | wc -l` if [ $cnt3 -eq 1 ]; then echo "It seems there is my.cnf in /etc already, please delete it first!" exit 1 fi # check if there is /opt/mydata in system or not cnt4=`find / -maxdepth 1 -name 'opt' -type d | wc -l` if [ $cnt4 -eq 1 ]; then cnt5=`find /opt -maxdepth 1 -name 'mydata' -type d | wc -l` if [ $cnt5 -eq 1 ]; then echo "It seems there is /opt/mydata already, please delete it first!" exit 1 fi fi echo "$(date +"%Y%m%d %H:%M:%S") mysql related file is ok...." # check mysql user id mysql &> /dev/null if [ $? -eq 0 ]; then echo "mysql user is alreay exist, please delete it first!" exit 1 fi # prepare install mysql echo "$(date +"%Y%m%d %H:%M:%S") start prepare install mysql...." # clear old version rpm -qa | grep -i mysql | xargs rpm -ev --nodeps &> /dev/null # install required package yum install -y libaio &> /dev/null # if the package is not install correctly, terminate the script. cnt6=`rpm -qa | grep libaio | wc -l` if [ $cnt6 -lt 1 ]; then echo "libaio package is not install, please check!" exit 1 fi # adjust some parameter in /etc/security/limits.conf echo "mysql soft nproc 16384" >> /etc/security/limits.conf echo "mysql hard nproc 16384" >> /etc/security/limits.conf echo "mysql soft nofile 65536" >> /etc/security/limits.conf echo "mysql hard nofile 65536" >> /etc/security/limits.conf echo "mysql soft stack 1024000" >> /etc/security/limits.conf echo "mysql hard stack 1024000" >> /etc/security/limits.conf # adjust some parameter in /etc/sysctl.conf echo "vm.swappiness = 10" >> /etc/sysctl.conf sysctl -p &> /etc/null # turn off firewall /etc/init.d/iptables stop &> /etc/null chkconfig iptables off sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config # move my.cnf to /etc mv my.cnf /etc/ # add user mysql groupadd -g 600 mysql useradd -u 600 -g mysql mysql echo "mysql" | passwd --stdin mysql &> /etc/null # prepare directory mkdir -p /opt/mydata/data mkdir -p /opt/mydata/log/binlog mkdir -p /opt/mydata/log/redo mkdir -p /opt/mydata/log/undo mkdir -p /opt/mydata/log/relaybin mkdir -p /opt/mydata/tmp chown -R mysql:mysql /opt/mydata # add path to profile echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /root/.bash_profile echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /home/mysql/.bash_profile # unpackage mysql rm -rf /usr/local/mysql-*-linux-glibc2.12-x86_64 mysql tar -zxvf mysql-*-linux-glibc2.12-x86_64.tar.gz -C /usr/local/ &> /etc/null cd /usr/local/ ln -s mysql-*-linux-glibc2.12-x86_64 mysql echo "$(date +"%Y%m%d %H:%M:%S") prepare install mysql is ok...." # start install mysql echo "$(date +"%Y%m%d %H:%M:%S") start install mysql...." cd mysql ./bin/mysqld --initialize --user=mysql --explicit_defaults_for_timestamp if [ $? -ne 0 ]; then echo "mysql initialize failed, please check the error log!" exit 1 fi ./bin/mysql_ssl_rsa_setup &> /dev/null chmod +r /opt/mydata/data/server-key.pem nohup bin/mysqld_safe --user=mysql & if [ $? -ne 0 ]; then echo "mysql start failed, please check the error log!" exit 1 fi cp support-files/mysql.server /etc/init.d/mysql # wait mysql startup cnt7=`ps -ef | grep mysqld | grep -v grep | wc -l` while [ $cnt7 -lt 2 ] do sleep 3 cnt7=`ps -ef | grep mysqld | grep -v grep | wc -l` done # wait 10s for mysql startup completly and then change root password sleep 10 pass=`grep "temporary password" /opt/mydata/log/error.log |awk -F " " '{print $11}'` /usr/local/mysql/bin/mysqladmin -uroot -p$pass password 'root' echo "system user mysql initial password is 'mysql', mysql db user root initial password is 'root'" echo "$(date +"%Y%m%d %H:%M:%S") install mysql complete...." exit 0
3. 准备my.cnf文件
由于我这个是测试环境，所以内存值调的比较小，生产上面可以将下面两个参数进行调整
innodb_buffer_pool_size = 物理内存 * 60%
innodb_buffer_pool_instances = innodb_buffer_pool_size/128m，个人觉得不需要设太大，16即可
[client] port = 3306 socket = /opt/mydata/data/mysql.sock [mysql] prompt="(\\u@\\h)[\\d]> " [mysqld] # basic settings # port = 3306 basedir = /usr/local/mysql datadir = /opt/mydata/data tmpdir = /opt/mydata/tmp pid-file = /opt/mydata/data/mysql.pid socket = /opt/mydata/data/mysql.sock user = mysql character_set_server = utf8mb4 transaction_isolation = READ-COMMITTED explicit_defaults_for_timestamp = 1 max_allowed_packet = 1024M event_scheduler = 1 lower_case_table_names = 1 max_heap_table_size = 256M thread_cache_size = 512 secure_file_priv = '' # connection # skip_name_resolve = 1 max_connections = 1000 max_user_connections = 1000 max_connect_errors = 1000000 # session memory setting # read_buffer_size = 8M read_rnd_buffer_size = 4M sort_buffer_size = 4M tmp_table_size = 128M join_buffer_size = 8M # log settings # log_error = /opt/mydata/log/error.log slow_query_log = 1 long_query_time = 10 slow_query_log_file = /opt/mydata/log/slowquery.log log_queries_not_using_indexes = 1 log_throttle_queries_not_using_indexes = 10 min_examined_row_limit = 100 log_slow_admin_statements = 1 expire_logs_days = 30 binlog_rows_query_log_events = 1 binlog_row_image = minimal binlog_cache_size = 4M max_binlog_cache_size = 4G max_binlog_size = 2G log_bin_trust_function_creators = 1 log_timestamps = SYSTEM # innodb settings # innodb_data_file_path = ibdata1:1024M:autoextend innodb_buffer_pool_size = 300M innodb_buffer_pool_instances = 2 innodb_lock_wait_timeout = 10 innodb_io_capacity = 4000 innodb_io_capacity_max = 8000 innodb_flush_method = O_DIRECT innodb_undo_directory = /opt/mydata/log/undo innodb_undo_tablespaces = 3 innodb_flush_neighbors = 0 innodb_undo_log_truncate = 1 innodb_max_undo_log_size = 2G innodb_log_group_home_dir = /opt/mydata/log/redo innodb_log_file_size = 1G innodb_log_files_in_group = 4 innodb_log_buffer_size = 8M innodb_thread_concurrency = 16 innodb_print_all_deadlocks = 1 innodb_sort_buffer_size = 4M innodb_write_io_threads = 4 innodb_read_io_threads = 8 innodb_rollback_on_timeout = 1 innodb_file_per_table = 1 innodb_stats_persistent_sample_pages = 64 innodb_autoinc_lock_mode = 2 # MyISAM # key_buffer_size = 64M bulk_insert_buffer_size = 16M myisam_sort_buffer_size = 64M myisam_max_sort_file_size = 6G myisam_recover_options = DEFAULT # Master # server-id = 128 log-bin = /opt/mydata/log/binlog/mysql-bin binlog_format = ROW # Slave # relay-log = /opt/mydata/log/relaybin/slave-relay-bin replicate_wild_ignore_table = mysql.% log_slave_updates = 1 relay_log_purge = 1 relay_log_space_limit = 30G relay_log_recovery = 1 relay_log_info_repository = TABLE [mysqld_safe] user = mysql open_files_limit = 8192 [mysqldump] default_character_set = utf8mb4
4. 执行脚本，并查看输出
[root@mysqltest ~]# pwd /root [root@mysqltest ~]# ll total 629768 -rw-r--r--. 1 root root 2693 Apr 8 16:19 my.cnf -rw-r--r--. 1 root root 644862820 Mar 12 10:47 mysql-5.7.25-linux-glibc2.12-x86_64.tar.gz -rw-r--r--. 1 root root 4859 Apr 8 16:21 mysql-auto-install.sh [root@mysqltest ~]# sh mysql-auto-install.sh 20190408 16:22:32 start to check mysql related file.... 20190408 16:22:32 mysql related file is ok.... 20190408 16:22:32 start prepare install mysql.... 20190408 16:24:22 prepare install mysql is ok.... 20190408 16:24:22 start install mysql.... nohup: appending output to `nohup.out' mysqladmin: [Warning] Using a password on the command line interface can be insecure. Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety. system user mysql initial password is 'mysql', mysql db user root initial password is 'root' 20190408 16:25:06 install mysql complete....
5. 新开一个窗口登陆mysql
[root@mysqltest ~]# mysql -uroot -proot mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.7.25-log MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. (root@localhost)[(none)]>
登陆成功，表示mysql安装以及启动，密码修改都已经成功。
6. 总结
在写这个脚本的时候，开始都很顺利，到后面执行mysqladmin修改root密码的时候老报错
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/opt/mydata/data/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/opt/mydata/data/mysql.sock' exists!
询问了博客园大神ivictor[https://www.cnblogs.com/ivictor/]后，是因为我的mysql还没有起来，所以会报以上错误。因此我在脚本里面添加了sleep，等一段时间再修改密码。十分感谢ivictor的帮助
