参考

MySQL-5.7

实践

vim mysql.sh

  1. #!/bin/bash
  2. v_fileName=$1
  3. #获取当前脚本路径
  4. v_curPath=$(cd `dirname $0`; pwd)
  5. v_log=${v_curPath}"/"$0".log"
  6. #声明消息提醒前缀方法
  7. f_msg(){
  8.     echo $(date "+%Y-%m-%d %H:%M:%S")$LINENO" "$1"==>"
  9. }
  11. #判断日志文件是否存在若不存在则创建
  12. if [ ! -f ${v_log} ]
  13.     then
  14.         touch ${v_log}
  15.         echo $(f_msg INFO)$LINENO" 创建日志文件" >> ${v_log}
  16.     else
  17.         echo "=======================================">>${v_log}
  18. fi
  20. #校验工具是否存在方法
  21. f_checkRpm(){
  22.     v_name1=$1
  23.     v_check=$(rpm -qa ${v_name1})
  24.     if [ ${#v_check} -eq 0 ]
  25.         then
  26.             echo $(f_msg ERROR)$LINENO " "${v_name1}"工具不存在,请安装"${v_name1}"工具">>${v_log}
  27.             return 0
  28.         else
  29.             echo $(f_msg INFO)$LINENO " "${v_name1}"工具的版本号为"${v_check}>>${v_log}
  30.             return 1
  31.     fi
  32. }
  33. v_yum=$(rpm -qa yum)
  34. if [ ${#v_yum} -eq 0 ]
  35.     then
  36.         echo $(f_msg ERROR)$LINENO" 未检测到yum，请安装yum"
  37.         echo $(f_msg ERROR)$LINENO" 未检测到yum，请安装yum" >> ${v_log}
  38.         exit 0
  39.     else
  40.         echo $(f_msg INFO)$LINENO" yum的版本为"${v_yum} >> ${v_log}
  41. fi
  43. v_tcl=$(rpm -qa tcl)
  44. if [ ${#v_tcl} -eq 0 ]
  45.     then
  46.         echo $(f_msg INFO)$LINENO" 未检测到tcl，请安装tcl"
  47.         echo "执行语句 yum -y install tcl "
  48.         echo $(f_msg INFO)$LINENO" 未检测到tcl，请安装tcl" >> ${v_log}
  49.         exit 0
  50.     else
  51.         echo $(f_msg INFO)$LINENO" tcl的版本为"${v_tcl} >> ${v_log}
  53. fi
  54. v_expect=$(rpm -qa expect)
  55. if [ ${#v_expect} -eq 0 ]
  56.     then
  57.         echo $(f_msg INFO)$LINENO" 未检测到expect，请安装expect"
  58.         echo "执行语句 yum -y install expect "
  59.         echo $(f_msg INFO)$LINENO" 未检测到expect，请安装expect" >> ${v_log}
  60.         exit 0
  61.     else
  62.         echo $(f_msg INFO)$LINENO" expect的版本为"${v_expect} >> ${v_log}
  63. fi
  64. #判断文件是否存在，若不存在则下载
  65. if [ ${#v_fileName} -eq 0 ]
  66.     then
  67.         f_checkRpm wget
  68.         v_rqmReturn=$?
  69.         #检测当前目录下是否有mysql文件
  70.         if [ ${v_rqmReturn} -eq 0 ]
  71.             then
  72.                 echo $(f_msg INFO)$LINENO" 未检测到wget，请安装wget" >> ${v_log}
  73.                 exit 0
  74.         fi
  76.         v_mysqlC=$(ls -ld mysql* |grep ^-|wc -l)
  77.         if [ ${v_mysqlC} -lt 1 ]
  78.             then
  79.                 #若不存在则下载
  80.                 wget http://dev.MySQL.com/get/Downloads/MySQL-5.7/mysql-5.7.11-Linux-glibc2.5-x86_64.tar.gz
  81.         fi
  82.     else
  83.         if [ ! -f ${v_fileName} ]
  84.             then
  85.                 wget http://dev.MySQL.com/get/Downloads/MySQL-5.7/${v_fileName}
  86.         fi
  87. fi
  88. #检验tar是否存在
  89. v_tar=$(rpm -qa tar)
  90. if [ ${#v_tar} -le 0 ]
  91.     then
  92.         echo $(f_msg ERROR)$LINENO" tar工具不存在，请安装tar工具">>${v_log}
  93.         exit 0
  94.     else
  95.         echo $(f_msg INFO)$LINENO" tar工具的版本号为"${v_tar}>>${v_log}
  96. fi
  97. #获取文件名
  98. for fileName in `ls -d  mysql*`
  99.     do
  100.         if [ -f fileName ]
  101.             then
  102.                 v_fileName=${fileName}
  103.         fi
  104. done
  105. echo $(f_msg INFO)$LINENO" 获取mysql压缩包文件名"${v_fileName}>>${v_log}
  106. #解压文件
  107. mkdir mysql
  108. tar -xvf ${v_fileName} -C mysql
  109. cd mysql
  110. v_mysqlChildC=$(ls -l|grep ^d|wc -l)
  111. v_tempFileName=""
  112. for fileName in `ls`
  113.     do
  114.         v_tempFileName=${fileName}
  115. done
  116. cd ${v_tempFileName}
  117. mv  * ../
  118. cd ..
  119. rm -rf ${v_tempFileName}
  120. #创建data目录
  121. mkdir -p /usr/local/mysql/data
  123. #创建mysql用户组及其用户
  124. if [ ! $(grep mysql /etc/group) ]
  125.     then
  126.         groupadd mysql
  127.         echo $(f_msg INFO)$LINENO" 创建mysql组成功">>${v_log}
  128.     else
  129.         echo $(f_msg INFO)$LINENO" mysql组已存在无需创建">>${v_log}
  130. fi
  131. #判断密码是否输入，若不输入则默认值为root
  132. v_pwd=${2}
  133. if [ ${#v_pwd} -eq 0 ]
  134.     then
  135.         v_pwd="mmsql"
  136. fi
  137. #创建用户
  138. if [ ! $(grep mysql /etc/passwd) ]
  139.     then
  140.         useradd -r -s /sbin/nologin -g mysql mysql -p v_pwd
  141.         echo $(f_msg INFO)$LINENO" 创建mysql用户成功">>${v_log}
  142.     else
  143.         echo $(f_msg INFO)$LINENO" mysql组已存在无需创建">>${v_log}
  144. fi
  146. #初始化mysql数据
  147. cd /usr/local/mysql/
  148. ./bin/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/
  149. echo $(f_msg INFO)$LINENO" 初始化mysql数据">>${v_log}
  150. #判断my.cnf 是否存在若存在则删除
  151. if [ $(ls -l /etc/my.cnf | wc -l) -ne 0 ]
  152.     then
  153.         rm -rf /etc/my.cnf
  154. fi
  155. if [ $(ls -l /etc/init.d/mysqld | wc -l) -ne 0 ]
  156.     then
  157.         rm -rf /etc/init.d/mysqld
  158. fi
  159. #拷贝文件
  160. cp -a ./support-files/my-default.cnf /etc/my.cnf
  161. cp -a ./support-files/mysql.server /etc/init.d/mysqld
  162. echo $(f_msg INFO)$LINENO" 拷贝mysql配置文件">>${v_log}
  163. #修改my.cnf内容
  164. sed -i "s/# basedir = ...../basedir = \/usr\/local\/mysql/g"  /etc/my.cnf
  165. sed -i "s/# datadir = ...../datadir = \/usr\/local\/mysql\/data/g"  /etc/my.cnf
  166. sed -i "s/# port = ...../port = 3306/g"  /etc/my.cnf
  167. sed -i "s/# socket = ...../socket = \/tmp\/mysql.sock/g"  /etc/my.cnf
  169. #echo "skip-grant-tables " >>/etc/my.cnf
  171. #判断character-set-server 是否存在，若存在则删除
  172. if [ $(grep character-set-server /etc/my.cnf) ]
  173.     then
  174.         sed -i "/character-set-server/d"  /etc/my.cnf
  175. fi
  176. echo "character-set-server = utf8" >> /etc/my.cnf
  177. if [ $(grep client /etc/my.cnf) ]
  178.     then
  179.         sed -i "/client/d" /etc/my.cnf
  180. fi
  181. #echo "[client]" >> /etc/my.cnf
  182. #echo "host = localhost" >> /etc/my.cnf
  183. #echo "user = root" >> /etc/my.cnf
  184. #echo "password ="123456 >> /etc/my.cnf
  185. #echo $(f_msg INFO)$LINENO" 修改my.cnf文件">>${v_log}
  186. #将mysql 添加到环境变量中
  187. echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
  188. #重新加载profile文件
  189. /usr/bin/expect<<EOF
  190.     set timeout -1
  191.     spawn  su - root   
  192.     expect "*"
  193.     send "source /etc/profile\n"
  194.     expect "*" 
  195.     send "exit\n"
  196.     expect eof
  197. EOF
  198. #source /etc/profile
  199. #echo $(f_msg INFO)$LINENO" 配置MySQL的环境变量，使mysql命令生效">>${v_log}
  200. #创建In
  201. ln -s /usr/local/mysql/  /usr/bin/
  202. #启动服务
  203. service mysqld start
  204. #获取mysql 初始密码
  205. v_mysqlinitpwd=$(cat /root/.mysql_secret | awk 'END {print}')
  206. #修改MySQL初始密码
  207. <<!
  208. /usr/local/mysql/bin/mysql -u root -p${v_mysqlinitpwd} -e "alter user root@localhost identified by '123456';flush privileges;"
  209. echo $(f_msg INFO)$LINENO" 修改root用户初始密码">>${v_log}
  210. #查询mysql用户是否有外网登录权限
  211. v_msyqlC=$(/usr/local/mysql/bin/mysql -N -s -u root -p${v_mysqlinitpwd} -D mysql -e "select count(1) from user where host ='%' and user='mysql';")
  212. if [ ${v_msyqlC} -le 0 ]
  213.     then
  214.         #创建用户mysql
  215.         /usr/local/mysql/bin/mysql -N -s -u root -p${v_mysqlinitpwd} -D mysql -e "CREATE USER 'mysql'@'host' IDENTIFIED BY '123456';flush privileges;"
  216.         #给用户mysql 授权
  217.         /usr/local/mysql/bin/mysql -N -s -u root -p${v_mysqlinitpwd} -D mysql -e "grant all privileges on *.* to mysql@'%' identified by '123456';flush privileges;"
  218. fi
  219. echo $(f_msg INFO)$LINENO" 添加mysql用户，并给mysql用户授权">>${v_log}
  220. !
  221. /usr/bin/expect<<EOF
  222.     set timeout -1
  223.     spawn  su - root   
  224.     expect "*" { send "/usr/local/mysql/bin/mysql -u root -p\n" }
  225.     expect "Enter password:" { send "${v_mysqlinitpwd}\n" }
  226.     expect "mysql>" { send "alter user root@localhost identified by '123456';\n" }
  227.     expect "mysql>" { send "flush privileges;\n" }
  228.     expect "mysql>" { send "use mysql;\n"}
  229.     expect "mysql>" { send "CREATE USER 'mysql'@'host' IDENTIFIED BY '123456';\n" }
  230.     expect "mysql>" { send "flush privileges;\n" }
  231.     expect "mysql>" { send "grant all privileges on *.* to mysql@'%' identified by '123456';\n" }
  232.     expect "mysql>" { send "flush privileges;\n" }
  233.     expect "mysql>" { send "exit\n" }
  234.     expect "*"
  235.     send "exit\n"
  236.     expect eof
  237. EOF
  238. echo $(f_msg INFO)$LINENO" 初始化数据库信息">>${v_log}
  239. #设置开机启动 mysql
  240. systemctl enable mysqld
  241. systemctl daemon-reload
  242. echo $(f_msg INFO)$LINENO" 设置mysql开机启动">>${v_log}
  243. echo "需要手动执行 source /etc/profile"
  244. echo "如外网无法调用请检查防火墙的端口是否打开"
  245. echo "执行完毕"

注意

  • 133 行创建用户时 不要用这里的密码请自行设置防止被攻击
  • 数据库默认密码为123456请提前更换后执行脚本或mysql启动后更改
  • 脚本启动是要设置 chmod -R 777 xx.sh 要不然不能执行sh文件
  • 脚本要放置在 usr/local/文件夹下 要不然会报 No such file or directory 的错
    • 我尝试修改过，没改成功。
  • mysql文件可以使用离线的

shell 问题总汇

https://renwole.com/archives/2215

  1. #!/bin/env bash
  2. # =================================================
  3. # Author: renwole.com                             #
  4. # Description: MySQL automatic installation tool  #
  5. # =================================================
  6. export PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/sbin:/usr/local/bin
  8. Install_MySQL() {
  9.     # CentOS 8 mysql: error while loading shared libraries: libncurses.so.5
  10.     yum install ncurses ncurses-compat-libs -y
  11.     # Install dependencies
  12.     setenforce 0
  13.     yum -y install epel-release
  14.     yum -y install wget tar jemalloc jemalloc-devel gcc gcc-c++
  16.     # MySQL configuration
  17.     mysql_version="8.0.21"
  18.     mysql_password="111"
  19.     mkdir -p /apps/server/mysql/data
  20.     mysql_install_dir="/apps/server/mysql"
  21.     mysql_data_dir="/apps/server/mysql/data"
  22.     download_mysql="https://mirrors.tuna.tsinghua.edu.cn/mysql/downloads"
  24.     # Download MySQL
  25.     cd /mnt
  26.     useradd -M -s /sbin/nologin mysql >/dev/null 2>&1
  27.     wget -c ${download_mysql}/MySQL-8.0/mysql-${mysql_version}-linux-glibc2.12-x86_64.tar.xz
  28.     echo "========== Start installing MySQL ============"
  29.     tar xJf mysql-${mysql_version}-linux-glibc2.12-x86_64.tar.xz
  30.     \mv mysql-${mysql_version}-linux-glibc2.12-x86_64/* ${mysql_install_dir}
  32.     if [[ -d "${mysql_install_dir}/support-files" ]]; then
  33.         rm -rf mysql-${mysql_version}-linux-glibc2.12-x86_64
  34.     else
  35.         rm -rf ${mysql_install_dir}
  36.         echo "Error: MySQL install failed, Please contact the author"
  37.         kill -9 $$
  38.     fi
  40.     # Initialize the database
  41.     ${mysql_install_dir}/bin/mysqld --initialize-insecure --user=mysql --basedir=${mysql_install_dir} --datadir=${mysql_data_dir}
  42.     chown -R root . ${mysql_install_dir}
  43.     chown -R mysql.mysql ${mysql_data_dir}
  44.     \cp -f ${mysql_install_dir}/support-files/mysql.server /etc/init.d/mysqld
  45.     sed -i "s@^basedir=.*@basedir=${mysql_install_dir}@" /etc/init.d/mysqld
  46.     sed -i "s@^datadir=.*@datadir=${mysql_data_dir}@" /etc/init.d/mysqld
  47.     sed -i "s@/usr/local/mysql@${mysql_install_dir}@g" ${mysql_install_dir}/bin/mysqld_safe
  48.     sed -i 's@executing mysqld_safe@executing mysqld_safe\nexport LD_PRELOAD=/usr/lib64/libjemalloc.so@' ${mysql_install_dir}/bin/mysqld_safe
  49.     echo 'PATH=$PATH:'${mysql_install_dir}'/bin' >/etc/profile.d/mariadb_renwole.com.sh
  51.     # my.conf configuration
  52.     cat > /etc/my.cnf << EOF
  53. [client]
  54. port = 3306
  55. socket = /tmp/mysql.sock
  56. default-character-set = utf8mb4
  58. [mysql]
  59. prompt="MySQL [\\d]> "
  60. no-auto-rehash
  62. [mysqld]
  63. port = 3306
  64. socket = /tmp/mysql.sock
  65. default_authentication_plugin = mysql_native_password
  67. basedir = ${mysql_install_dir}
  68. datadir = ${mysql_data_dir}
  69. pid-file = ${mysql_data_dir}/mysql.pid
  70. user = mysql
  71. bind-address = 0.0.0.0
  72. server-id = 1
  74. init-connect = 'SET NAMES utf8mb4'
  75. character-set-server = utf8mb4
  76. collation-server = utf8mb4_0900_ai_ci
  78. skip-name-resolve
  79. #skip-networking
  80. back_log = 300
  82. max_connections = 1000
  83. max_connect_errors = 6000
  84. open_files_limit = 65535
  85. table_open_cache = 128
  86. max_allowed_packet = 500M
  87. binlog_cache_size = 1M
  88. max_heap_table_size = 8M
  89. tmp_table_size = 16M
  91. read_buffer_size = 2M
  92. read_rnd_buffer_size = 8M
  93. sort_buffer_size = 8M
  94. join_buffer_size = 8M
  95. key_buffer_size = 4M
  97. thread_cache_size = 8
  99. ft_min_word_len = 4
  101. log_bin = mysql-bin
  102. binlog_format = mixed
  103. binlog_expire_logs_seconds = 604800
  105. log_error = ${mysql_data_dir}/mysql-error.log
  106. slow_query_log = 1
  107. long_query_time = 1
  108. slow_query_log_file = ${mysql_data_dir}/mysql-slow.log
  110. performance_schema = 0
  111. explicit_defaults_for_timestamp
  113. #lower_case_table_names = 1
  115. skip-external-locking
  117. default_storage_engine = InnoDB
  118. #default-storage-engine = MyISAM
  119. innodb_file_per_table = 1
  120. innodb_open_files = 500
  121. innodb_buffer_pool_size = 64M
  122. innodb_write_io_threads = 4
  123. innodb_read_io_threads = 4
  124. innodb_thread_concurrency = 0
  125. innodb_purge_threads = 1
  126. innodb_flush_log_at_trx_commit = 2
  127. innodb_log_buffer_size = 2M
  128. innodb_log_file_size = 32M
  129. innodb_log_files_in_group = 3
  130. innodb_max_dirty_pages_pct = 90
  131. innodb_lock_wait_timeout = 120
  133. bulk_insert_buffer_size = 8M
  134. myisam_sort_buffer_size = 8M
  135. myisam_max_sort_file_size = 10G
  136. myisam_repair_threads = 1
  138. interactive_timeout = 28800
  139. wait_timeout = 28800
  141. [mysqldump]
  142. quick
  143. max_allowed_packet = 500M
  145. [myisamchk]
  146. key_buffer_size = 8M
  147. sort_buffer_size = 8M
  148. read_buffer = 4M
  149. write_buffer = 4M
  150. EOF
  151.     # Set up MySQL
  152.     chmod 600 /etc/my.cnf
  153.     chmod +x /etc/init.d/mysqld
  154.     systemctl enable mysqld
  155.       systemctl start mysqld
  156.       ${mysql_install_dir}/bin/mysql -uroot -hlocalhost -e "create user root@'127.0.0.1' identified by \"${mysql_password}\";"
  157.       ${mysql_install_dir}/bin/mysql -uroot -hlocalhost -e "grant all privileges on *.* to root@'127.0.0.1' with grant option;"
  158.      ${mysql_install_dir}/bin/mysql -uroot -hlocalhost -e "grant all privileges on *.* to root@'localhost' with grant option;"
  159.       ${mysql_install_dir}/bin/mysql -uroot -hlocalhost -e "alter user root@'localhost' identified by \"${mysql_password}\";"
  160.       ${mysql_install_dir}/bin/mysql -uroot -p${mysql_password} -e "reset master;"
  161.       rm -rf /etc/ld.so.conf.d/{mysql,mariadb,percona,alisql}*.conf
  162.       echo "${mysql_install_dir}/lib" > /etc/ld.so.conf.d/mysql_renwole.com.conf
  163.       ldconfig
  164.       echo "========== MySQL installing Successfully ====="
  165.       echo
  166.     echo "=============================================="
  167.     echo "    Thank you for using Renwole.com script"
  168.     echo "=============================================="
  169.     echo "MySQL:"
  170.     echo "account: root"
  171.     echo "password: ${mysql_password}"
  172.     echo "database: ${mysql_data_dir}"
  173.     echo "=============================================="
  174. }
  176. Install_MySQL
  177. source /etc/profile