mysql-5.7.21 Binary Installation | Jemalloc Memory Optimization | Backup Recovery | Change Password

Brief introduction

######数据库目录/usr/local/mysql##################数据目录/data/mysql##################慢日志目录/data/slowlog##################端口号默认3306其余参数按需自行修改############

Installation scripts

#!/bin/bashcd/usr/local/src/if [!-f mysql-5.7.21-linux-glibc2.12-x86_64.tar.gz]; Thenwget Https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.21-linux-glibc2.12-x86_64.tar.gzyum install-y  autoconf automake imake libxml2-devel expat-devel cmake gcc gcc-c++ libaio libaio-devel BZR Bison libtool ncurses-develfi# Check if User is Rootif [$ (id-u)! = "0"]; Then echo "Error:you must is root to run this script and use the root to install" exit 1FICLEARCD/USR/LOCAL/SR CIF [!-F jemalloc-5.0.1.tar.bz2]; Thenyum Install Bzip2-ywget Https://github.com/jemalloc/jemalloc/releases/download/5.0.1/jemalloc-5.0.1.tar.bz2ta R-XJVF JEMALLOC-5.0.1.TAR.BZ2CD Jemalloc-5.0.1/./configure-prefix=/usr/local/jemalloc--libdir=/usr/local/li            Bmake && make Installecho/usr/local/lib >>/etc/ld.so.confldconfigficp/usr/local/lib/libjemalloc.so /usr/libecho "=========================================================================" echo "A tool to Auto-compile & Install MySQL 5.7.21 on Redhat/centos Linux "echo" ================================================= ======================== "cur_dir=$ (pwd) #set mysql root password echo" =========================== "mysqlrootpwd=" 123 456 "ECHO-E" Please input the root password of MySQL: "Read-p" (Default password:123456): "Mysqlrootpwd If [" $mysqlrootpwd "=" "];    Then mysqlrootpwd= "123456" fi echo "===========================" echo "MySQL root password: $mysqlrootpwd" echo "===========================" #which MySQL Version do your want to Install?echo "===========================" ISI nstallmysql57= "n" echo "Install MySQL 5.7.21,please input y" read-p "(Please input y, n):" Isinstallmysql57 CAs E "$isinstallmysql" in y| y| yes| yes|yes|yes|yes|    Yes|yes) echo "You'll install MySQL 5.7.21" isinstallmysql57= "Y";; *) echo "INPUT error,you would exit install MySQL 5.7.21" isinstallmysql57= "n" Exit Esac Get_char ()    {savedstty= ' stty-g ' Stty-echo stty cbreak #dd if=/dev/tty bs=1 count=1 2>/dev/null Stty-raw Stty echo Stty $SAVEDSTTY} echo "" echo "Press any key to Start...or press CTRL + C to cancel" char= ' Get_cha R ' # Initialize the installation related Content.function Initinstall () {cat/etc/issue uname-a memtotal= ' free- m | grep Mem | awk ' {print $} ' echo-e "\ Nand Memory is: ${memtotal} MB" #Set timezone #rm-rf/etc/localtime #ln-S/usr/  Share/zoneinfo/asia/shanghai/etc/localtime #Delete old MySQL program rpm-qa|grep MySQL rpm-e MySQL #Disable SeLinux if [-s/etc/selinux/config]; Then Sed-i ' s/selinux=enforcing/selinux=disabled/g '/etc/selinux/config fi setenforce 0} #Installation of depend On and Optimization options.function installdependsandopt () {CD $cur _dircat >>/etc/security/limits.conf<< eof* soft Nproc 65535* hard nproc 65535* soft nofile 65535* hard nofile 65535EOFecho "fs.file-Max=65535net.ipv4.tcp_fin_timeout = 30net.ipv4.tcp_tw_reuse = 1net.ipv4.tcp_tw_recycle = 1net.ipv4.tcp_max_tw_ Buckets = 4096net.ipv4.tcp_max_syn_backlog = 4096 ">>/etc/sysctl.confsysctl-p} #Install mysqlfunction InstallMySQL57 () {echo "============================install MySQL 5.7.21==================================" CD $cur _ Dir#backup old My.cnf#rm-f/etc/my.cnfif [-s/etc/my.cnf]; Then mv/etc/my.cnf/etc/my.cnf. ' Date +%y%m%d%h%m%s '. Bakfiecho "============================mysql 5.7.21 installing ... ========================= "#mysql directory Configurationcd/usr/local/srctar xvf mysql-5.7.21-linux-glibc2.12-x86 _64.TAR.GZMV mysql-5.7.21-linux-glibc2.12-x86_64/usr/local/mysqlgroupadd mysql-g 512useradd-u 512-g mysql-s/sbin/n ologin-d/home/mysql mysqlmkdir-p/data/mysqlmkdir-p/data/slowlogchown-r mysql:mysql/data/mysqlchown-r mysql:mysql /usr/local/mysqlchown-r mysql:mysql/data/slowlog#edit/etc/my.cnfserverid= ' ifconfig eth0 | grep "inet" | awk ' {print $}' | Awk-f. ' {print $3$4} ' cat >>/etc/my.cnf<<eof[client]port=3306socket=/tmp/ mysql.sockdefault-character-set=utf8[mysql]no-auto-rehashdefault-character-set=utf8[mysqld]port= 3306bind-address = 0.0.0.0character-set-server=utf8socket=/tmp/mysql.sockpid-file=/data/mysql/mysqld.pidbasedir= /usr/local/mysqldatadir=/data/mysqlexplicit_defaults_for_timestamp=truelower_case_table_names=1back_log=103max _connections=3000max_connect_errors=100000table_open_cache=512external-locking=falsemax_allowed_packet=32msort _buffer_size=16mjoin_buffer_size=2mthread_cache_size=51query_cache_size=32m#query_cache_limit=4mtransaction_ Isolation=repeatable-readtmp_table_size=96mmax_heap_table_size=96m###***slowqueryparameterslong_query_time= 1slow_query_log = 1slow_query_log_file=/data/slowlog/slow.log###***binlogparameterslog-bin=mysql-binbinlog_cache _size=4mmax_binlog_cache_size=4096mmax_binlog_size=1024mbinlog_format=mixedexpire_logs_days=7###*** Relay-logparameters#relay-log=/data/3307/relay-bin#relay-log-info-file=/data/3307/relay-log.info#master-info-repository=table#relay-log-info-repository=table# Relay-log-recovery=1#***myisamparameterskey_buffer_size=16mread_buffer_size=1mread_rnd_buffer_size=16mbulk_ Insert_buffer_size=1m#skip-name-resolve###***master-slavereplicationparametersserver-id= $SERVERID # SLAVE-SKIP-ERRORS=ALL#***INNODBSTORAGEENGINEPARAMETERSINNODB_BUFFER_POOL_SIZE=2G # OS memory is best 70%-80%. Innodb_data_file_path=ibdata1:10m:autoextend#innodb_file_io_threads=8innodb_thread_concurrency=16innodb_flush_ Log_at_trx_commit=1innodb_log_buffer_size=16minnodb_log_file_size=512minnodb_log_files_in_group=2innodb_max_ Dirty_pages_pct=75innodb_buffer_pool_dump_pct=50innodb_lock_wait_timeout=50innodb_file_per_table=off[mysqldump ]quickmax_allowed_packet=32m[myisamchk]key_buffer=16msort_buffer_size=16mread_buffer=8mwrite_buffer=8m[mysqld_ safe]malloc-lib=/usr/lib/libjemalloc.soopen-files-limit=8192log-error=/data/mysql/error.logpid-file=/data/ Mysql/mysqld.pideof/usr/local/mysql/bIn/mysqld--defaults-file=/etc/my.cnf--user=mysql--datadir=/data/mysql--basedir=/usr/local/mysql--initializ E-insecurecp/usr/local/mysql/support-files/mysql.server/etc/init.d/mysqldchmod 700/etc/init.d/mysqldchkconfig-- Add Mysqldchkconfig--level 2345 mysqld oncat >>/etc/ld.so.conf.d/mysql-x86_64.conf<<eof/usr/local/mysql /libeofldconfigif [-D "/proc/vz"];thenulimit-s unlimitedfi/etc/init.d/mysqld startcat >>/etc/profile << Eofexport path= $PATH:/usr/local/mysql/binexport ld_library_path= $LD _library_path:/usr/local/mysql/libeof/usr/ local/mysql/bin/mysqladmin-u root password $mysqlrootpwdcat >/tmp/mysql_sec_script<<eofuse mysql; #delete From Mysql.user where user!= ' root ' or host!= ' localhost ', #grant all privileges on * * to ' sys_admin ' @ '% ' identified by ' Mans Ager '; flush privileges; Eof/usr/local/mysql/bin/mysql-u root-p$mysqlrootpwd-h localhost </tmp/mysql_sec_scriptrm-f/tmp/mysql_sec_ Script#/etc/init.d/mysqld Restartecho "= = = =========================mysql 5.7.21 Install completed========================= "}function Checkinstall () {echo" = = = = = = ================================= Check Install =================================== "clearismysql=" "echo" Checking  ... "if [-s/usr/local/mysql/bin/mysql] && [-s/usr/local/mysql/bin/mysqld_safe] && [-S/ETC/MY.CNF]; Then echo "Mysql:ok" ismysql= "OK" Else echo "Error:/usr/local/mysql not found!!! MySQL install failed. " Fiif ["$ismysql" = "OK"]; Thenecho "Install MySQL 5.7.21 completed! Enjoy it. " echo "=========================================================================" Netstat-lntupelseecho "Sorry, Failed to install mysql! " echo "Can tail/root/mysql-install.log from your server." FI} #The installation Loginitinstall 2>&1 | Tee/root/mysql-install.loginstalldependsandopt 2>&1 | Tee-a/root/mysql-install.loginstallmysql57 >/dev/nullcheckinstall 2>&1 | Tee-a/root/mysql-install.log

Backup

mysqldump  -uroot -p123456 -B  ultrax        --single-transaction  --master-data=2  > ultrax.sql-A  全备份-B  备份库（表结构和数据）--single-transaction    预防锁   数据一致性   确保本次会话(dump)时，不会看到其他会话已经提交了的数据。--master-data=2     注释记录复制信息                       =1      记录change  master to 语句

View SQL

egrep -v "#|\*|--|^$"    ultrax.sql

Recovery

mysql  -uroot -p123456        < /root/ultrax.sql

Change Password

[mysqld]skip-grant-tables./mysqladmin -uroot -p passwordupdate user set password=password(‘root‘) where user=‘root‘;update user set authentication_string=PASSWORD(‘123456‘)  where user=‘root‘;

mysql-5.7.21 Binary Installation | Jemalloc Memory Optimization | Backup Recovery | Change Password