Amygo
作者Amygo2019-06-17 18:32
数据库管理员, oracle

【小白福利—初级DBA入门必看】MySQL常用工具介绍(一)——安装篇

字数 21367阅读 720评论 0赞 3

之前Amy为大家更新了基于MySQL的分布式事务数据库的开发规范和MySQL数据类型测试点此查看系列文章,考虑到很多刚入门的初级DBA可能对MySQL工具的使用也不甚熟练,Amy决定接下来为大家更新一系列介绍MySQL的常用工具的文章,让大家对MySQL的使用更加得心应手。

今天先更新MySQL工具的几种安装方式,大家可以自由选择哦。

使用yum或rpm在linux安装、初始化、使用MySQL 5.7

1.1 安装部分

1.1.1 安装过程
yum安装方式
先通过wget从官网下载相应平台的MySQL的yum仓库,然后执行安装。
此步骤自动配置好MySQL的yum源,后续可以方便地安装MySQL官方相关软件。
wget: https://repo.mysql.com//mysql80-community-release-el7-2.noarch.rpm
yum -y localinstall mysql80-community-release-el7-2.noarch.rpm

由于该仓库默认启用的是mysql80库,想要安装mysql57还需将mysql80库禁用并将mysql57启用。这里使用如下方式修改仓库配置
yum-config-manager --disable mysql80-community
yum-config-manager --enable mysql57-community

安装MySQL,这会依次安装:

mysql-community-common #cs共同的错误信息和字符集包
mysql-community-libs #共享客户端库,会替换mariadb-libs
mysql-community-client #客户端包
mysql-community-server
mysql-community-libs-compat #会替换mariadb-libs
yum install mysql-community-server

rpm安装方式

通过官网下载等方式获取对应平台以下几个rpm包,并将其上传至所要安装的平台上,执行以下命令,则会依次安装好这几个包
mysql-community-common #cs共同的错误信息和字符集包
mysql-community-libs #共享客户端库,会替换mariadb-libs
mysql-community-client #客户端包
mysql-community-server
mysql-community-libs-compat #会替换mariadb-libs
yum -y install mysql-community-{server,client,common,libs}- mysql-5. --exclude=‘minimal’

1.1.2 安装说明

安装布局:即安装完毕后会创建的文件或目录。若安装完毕后不修改配置直接启动服务器,则数据目录等设置和下表相同。

安装会覆盖/etc/my.cnf的内容,里面启用了几个重要参数,和上述布局相同。若愿意,可以在启动服务器前,修改该配置文件,如将数据目录放置在其它地方:
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
安装完毕,MySQL还会给我们创建一个mysql用户和mysql基本组
[root@centip10 ~ 20:59:12]# cat /etc/passwd | grep mysql
mysql❌27:27:MySQL Server:/var/lib/mysql:/bin/false

1.2 yum、rpm初始化部分及启动

对于yum和rpm方式安装的MySQL,可以在安装后直接启动服务器(如centos6可以使用service mysqld start;centos7还可以使用systemctl start mysqld)。
若启动时检测到数据目录为空或不存在,则会初始化数据目录:
1)初始化服务器;
2)在数据目录中生成SSL证书和密钥文件;
3)安装并启用validate_password:要求密码小写、大写、数字、特殊字符都存在,长度不小于8
4)创建’root’@'localhost超级用户帐户。临时密码存储在错误日志文件中,查看命令为:
grep ‘temporary password’ /var/log/mysqld.log

注意:对于5.6,该过程调用mysql_install_db,初始密码为空。当然也可以手动先初始化数据目录,mysql_install_db --random-passwords,给root账户生成一个随机密码,密码保存在用户主目录下.mysql_secret。
1.3 使用mysql连接服务器并修改密码
使用mysql -p命令,并输入由1.2中找到的密码,当可以成功连接到服务器,此后必须先修改密码才能执行其他语句:
ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘MyNewPass4!’; #5.6要使用SET PASSWORD = PASSWORD(‘new_password’);
quit #退出mysql

**- 2 .使用通用二进制安装、源代码在linux安装、初始化、使用MySQL 5.7

2.1 安装部分**

通用二进制安装部分
以从官网下载等方式获取平台对应的通用二进制包,并上传至平台,在该包目录执行解压将其解压至/usr/local/目录,重命名为mysql
tar zxvf mysql-5.7.26-el7-x86_64.tar.gz -C /usr/local/
rm -r /usr/local/mysql
mv /usr/local/mysql-5.7.26-el7-x86_64 /usr/local/mysql

源安装部分

先安装依赖包
yum -y install cmake libaio libaio-devel #所有平台上的构建框架,mysql要求cmake检查libaio和libaio-devel的存在
yum -y install make #一个良好的make程序,官方建议使用GNU make 3.75及更高版本
yum -y install gcc gcc-c++ #一个有效的ANSI C ++编译器,如默认情况下cmake会检查受支持的编译器的最低版本:
vs2013、gcc4.4或clang3.3、ds12.5。-DFORCE_UNSUPPORTED_COMPILER=ON时可以使cmake跳过此检查。
Boost C++ libraries用来创建mysql,必须安装Boost 1.59.0。通过cmake . -DWITH_BOOST=/usr/local/boost_1_59_0通知其位置。
这里我们直接下载官方带有boost的源码包,故不需要安装boost
yum -y install ncurses ncurses-devel bison bison-devel git # 其它安装依赖包

获取并解压分发包:这里直接下载带boost的版本,否则还需自己下载boost并放在对应位置源码包的顶层目录
VERSION=5.7.26
tar zxvf mysql-boost-KaTeX parse error: Expected 'EOF', got '#' at position 16: VERSION.tar.gz #̲配置分发: cd mysql-VERSION
mkdir bld
cd bld
cmake… -DWITH_BOOST=…/boost -DBUILD_CONFIG=mysql_release -DWITH_DEBUG=OFF
cmake使用默认值,只需指定所在位置。-G可以指定开发环境,如windows上cmake … -G “Visual Studio 12 2013 Win64”
还可以添加更多选项,如
-DBUILD_CONFIG=mysql_release: Configure the source with the same build options used by Oracle to produce binary distributions for official MySQL releases.必须也安装libaio
-DCMAKE_INSTALL_PREFIX=dir_name: Configure the distribution for installation under a particular location.
-DCPACK_MONOLITHIC_INSTALL=1: Cause make package to generate a single installation file rather than multiple files.
-DWITH_DEBUG=1: Build the distribution with debugging support.
For a more extensive list of options, see Section 2.9.4, “MySQL Source-Configuration Options”.
要查看配置
shell> cmake … -L # overview
shell> cmake … -LH # overview with help text
shell> cmake … -LAH # all params with help text
shell> ccmake … # interactive display
编译错误要先删除bld目录下的该文件rm CMakeCache.txt;

建立分发并安装分发
make & make install
make VERBOSE=1可以打印过程中每个编译命令
make install DESTDIR="/opt/mysql" 安装在特定目录,默认/usr/local/mysql
make package生成一个或多个.tar.gz通用二进制包。若cmake -DCPACK_MONOLITHIC_INSTALL=1则操作产生单个文件,否则多个
成功执行到此处,相当于把通用二进制版本放到了/user/local/mysql里,里面也有bin等目录
2.2 一些必要步骤及初始化、启动过程
安装一些依赖包
rpm -qa | grep mariadb | xargs rpm -e --nodeps #最好是删除预装的mariadb,否则可能因为共享库的原因导致mysql服务器启动失败
yum install libaio #必须,若不安装,则数据目录初始后和后续服务器启动步骤将失败
yum -y install numactl.x86_64 #5.7.19始,通用linux构建包含对非统一内存NUMA的支持,依赖于libnuma
设定一些常用的参数
cat > /etc/my.cnf <<EOFcnf
[mysqld]

实例唯一

server-id =3306
port =3306
socket =/tmp/mysql.sock
datadir =/usr/local/mysql/data/
general-log-file =/usr/local/mysql/log/general.log
slow-query-log-file =/usr/local/mysql/log/slow.log
log-bin =/usr/local/mysql/repl/binlog
relay-log =/usr/local/mysql/repl/relaylog

通用属性

plugin-load ="validate_password=validate_password.so"
basedir =/usr/local/mysql/
read-only =1
user =mysql
lower_case_table_names=1
character-set-server =utf8mb4
default-storage-engine =INNODB
secure-file-priv='/usr/local/mysql/'
explicit_defaults_for_timestamp=ON
sql_mode=default
event-scheduler =1
log-bin-trust-function-creators =1
group-concat-max-len =102400
validate-password =ON
validate_password_length=8
validate_password_policy=0

log-output =FILE
general-log
slow-query-log
long_query_time =1

gtid-mode =ON
enforce-gtid-consistency =true

binlog-format =ROW
expire-logs-days =70
max-binlog-size =1024M
log-slave-updates =1

skip-slave-start
relay-log-recovery =ON
slave-parallel-type =LOGICAL_CLOCK
slave-parallel-workers =20
replicate-ignore-db =test

relay-log-info-repository =TABLE
master-info-repository =TABLE

EOFcnf

创建mysql用户组

groupadd mysql
useradd -r -g mysql -s /bin/false mysql

清空数据目录等,创建必要的目录

myfdir=/usr/local
rm -rf $myfdir/mysql/data $myfdir/mysql/log $myfdir/mysql/repl $myfdir/mysql/ib
mkdir -p $myfdir/mysql/log
mkdir -p $myfdir/mysql/repl
mkdir -p $myfdir/mysql/ib

赋予相关文件或目录mysql属主和适当的权限

chown -R mysql:mysql /etc/my.cnf
chmod -R 750 /etc/my.cnf
chown -R mysql:mysql $myfdir/mysql/
chmod -R 750 $myfdir/mysql/

执行初始化

mypw=$myfdir/mysql/bin/mysqld --initialize --user=mysql --basedir=$myfdir/mysql --datadir=$myfdir/mysql/data 2>&1 |grep "A temporary password is generated for"
mypw=${mypw#*: }
echo $mypw #如果初始化成功,应该能看到一个复杂的密码,这是mysql账户root@localhost的过期的初始密码

添加服务、创建一些软连接以便能更好的调用mysql软件

ln -sf $myfdir/mysql/support-files/mysql.server /etc/init.d/mysqld #使mysqld可以作为服务启动
chmod +x /etc/init.d/mysqld #使mysqld可以作为服务启动
ln -sf $myfdir/mysql/support-files/mysqld_multi.server /etc/init.d/mysqldm
chmod +x /etc/init.d/mysqldm
ln -sf $myfdir/mysql /mysql
ln -sf $myfdir/mysql/bin/ $myfdir/bin/
ln -sf $myfdir/mysql/lib/libmysqlclient.so
/usr/lib64/

启动mysqld服务器

service mysqld start #或者使用mysqld_safe --user=mysql &

2.3 使用mysql连接服务器并修改root账号的初始密码
mysql --password=$mypw --connect-expired-password -e “ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘ztdmimadl1MY’;”

3 linux上四种安装方式的比较
yum和rpm的本质都是安装几个rpm包,区别在于使用yum安装时需要访问公网访问官方下载地址下载安装所需的文件,其安装过程、mariadb库的自动替换、安装布局、服务自启等设定都是一样的。此外,rpm包可以选择使用yum命令或rpm -ivh命令安装,使用rpm安装需要自己解决潜在的依赖包问题,如libaio。
源代码和通用二进制的区别主要在于:使用源代码安装可以设定编译选项,并且编译过程耗时较长。实际上,通用二进制包不过是官方根据各平台优化配置后的编译好的二进制文件,我们使用源代码自定义编译的目标就是生成与通用二进制包相似的二进制文件。故此,编译好源码后,通用二进制安装和源代码编译好的二进制的安装过程几乎一致。
一般来说,我们可以使用官方提供的通用二进制包进行安装,其优势在于安装位置比较集中,不像rpm安装默认布局一样分散,更加便于管理,对系统文件系统的影响较小。
特殊情况可以考虑使用源码安装,源码安装有其优势:
1)可自定义mysql组件的安装位置
2)可加入标准二进制分布没有的功能,如最为常用的
-DWITH_LIBWRAP=1 for TCP wrappers support.
-DWITH_ZLIB={system|bundled} for features that depend on compression
-DWITH_DEBUG=1 for debugging support
For additional information, see Section 2.9.4, “MySQL Source-Configuration Options”.
3)删除某些功能,按需安装,最小化安装:如可以不使用某些字符集
4)修改某些源代码来自定义软件
5)源分发比二进制安装包含更多测试、例子
4 附:centos6或7自动化安装MySQL脚本
脚本文件:
使用方式:sh install_mysql_auto.sh {yum | rpm | bin}
注意事项:
1.使用脚本前,请将先前mysql的安装、进程、服务启动项、路径等清理干净。最好在一个没有安装过mysql的电脑使用
2.使用yum安装,需要外网,将使mysqld安装到最新的5.7版本;
使用rpm安装,需将较新5.7版本的mysql-community-common、mysql-community-libs、mysql-community-client、mysql-community-server、mysql-community-libs-compat包上传至工作目录;
使用bin安装,需将grep mysql-5.7-el64.tar.gz能识别的包(如mysql-5.7.26-el7-x86_64.tar.gz)上传至工作目录
3.脚本将删除/data/mysql/data /data/mysql/log /data/mysql/repl /data/mysql/ib原有内容,使用bin还会删除/usr/local/mysql
4.脚本将修改/etc/security/limits.conf、/etc/sysctl.conf等系统文件,关闭防火墙、selinux,以及其他一些系统相关操作
5.脚本将重写/etc/my.cnf。若有需要,可以先备份。此外,最好更名备份其他位置的配置文件,以免脚本初始化服务器等出错。

脚本内容:

!/bin/sh


Created By: xxxx


判断使用方式是否正确

case "$1" in
'yum' | 'rpm' | 'bin' )
;;
*)
echo "error:Usage: $0 {yum|rpm|bin}" >&2
exit 1
;;
esac

提醒安装脚本的注意事项和对系统的影响,4和5中的一些配置可以自己修改
cat << EOFreadme
1.使用脚本前,请将先前mysql的安装、进程、服务启动项、路径等清理干净。最好在一个没有安装过mysql的电脑使用
2.使用yum安装,需要外网,将使mysqld安装到最新的5.7版本;
使用rpm安装,需将较新5.7版本的mysql-community-common、mysql-community-libs、mysql-community-client、mysql-community-server、mysql-community-libs-compat包上传至工作目录;
使用bin安装,需将grep mysql-5.7-el64.tar.gz能识别的包(如mysql-5.7.26-el7-x86_64.tar.gz)上传至工作目录
若继续执行,则
3.脚本将删除/data/mysql/data /data/mysql/log /data/mysql/repl /data/mysql/ib原有内容,使用bin还会删除/usr/local/mysql
4.脚本将修改/etc/security/limits.conf、/etc/sysctl.conf等系统文件,关闭防火墙、selinux,以及其他一些系统相关操作
5.脚本将重写/etc/my.cnf。若有需要,可以先备份。此外,最好更名备份其他位置的配置文件,以免脚本初始化服务器等出错。

EOFreadme
read -n1 -p "Do you want to continue [Y/N]?" answer
case $answer in
Y | y )
echo -e "nfine, continue";;
*)
echo -e "nthe script will exit with 1"
exit 1;;
esac

yum方式安装mysql

install_mysql_yum() {
echo “installing mysql throught yum”
wget https://repo.mysql.com//mysql80-community-release-el7-2.noarch.rpm
yum -y localinstall mysql80-community-release-el7-2.noarch.rpm

由于该仓库默认启用的是mysql80库,想要安装mysql57还需将mysql80库禁用并将mysql57启用。这里使用如下方式修改仓库配置
yum-config-manager --disable mysql80-community
yum-config-manager --enable mysql57-community

安装MySQL,这会依次安装:
mysql-community-common #cs共同的错误信息和字符集包
mysql-community-libs #共享客户端库,会替换mariadb-libs
mysql-community-client #客户端包
mysql-community-server
mysql-community-libs-compat #会替换mariadb-libs
yum -y install mysql-community-server
rpm -qa |grep mysql-community-server-5.7
if [ "$?" != 0 ];then echo "error:something wrong during the installation for mysql's rpm";exit 1; fi
mysqld=/usr/sbin/mysqld
echo "mysql5.7 installed successfully"
}

rpm包方式安装mysql

install_mysql_rpm() {
echo “installing mysql throught rpm”
通过官网下载等方式获取对应平台以下几个rpm包,并将其上传至所要安装的平台上,执行以下命令,则会依次安装好这几个包
mysql-community-common #cs共同的错误信息和字符集包
mysql-community-libs #共享客户端库,会替换mariadb-libs
mysql-community-client #客户端包
mysql-community-server
mysql-community-libs-compat #会替换mariadb-libs
yum -y install mysql-community-{server,client,common,libs}-5.7el64 --exclude=‘minimal’
rpm -qa |grep mysql-community-server-5.7
if [ “$?” != 0 ];then echo “error:something wrong during the installation for mysql’s rpm”;exit 1; fi
mysqld=/usr/sbin/mysqld
echo “mysql5.7 installed successfully”
}

通用二进制方式安装mysql

install_mysql_bin() {
echo "installing mysql throught general binary"
myfdir=/usr/local
pkg=ls | grep mysql-5.7*-el*64.tar.gz
if [ -z $pkg ];then echo "error:you havn't got a correct binary package(like:mysql-5.7.*-el7-x86_64.tar.gz) in the work directory";exit 1; fi
rm -rf $myfdir/${pkg%.tar.gz} $myfdir/mysql
tar zxvf $pkg --directory=$myfdir
mv $myfdir/${pkg%.tar.gz} $myfdir/mysql
if [ "$?" != 0 ];then echo "error:something wrong during the decompression";exit 1; fi

rm -f pkg

mysqld=$myfdir/mysql/bin/mysqld
rpm -qa | grep mariadb | xargs rpm -e --nodeps
yum -y install libaio
yum -y install numactl.x86_64
groupadd mysql
useradd -r -g mysql -s /bin/false mysql

ln -sf $myfdir/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
ln -sf $myfdir/mysql/bin/ $myfdir/bin/
ln -sf $myfdir/mysql/lib/libmysqlclient.so
/usr/lib64/

chkconfig --add mysqld
chkconfig mysqld on
echo "mysql5.7 installed successfully"
}

根据选择,调用相应的安装方式

case "$1" in
'yum' )
install_mysql_yum
;;
'rpm' )
install_mysql_rpm
;;
'bin' )
install_mysql_bin
;;
*)
echo "error:Usage: $0 {yum|rpm|bin}" >&2
exit 1
;;

esac

修改用户资源限制

cat > /etc/security/limits.conf <<EOFlimits
mysql - proc 10240
mysql - nofile 65535
mysql - memlock 402653184
mysql - nice -20
mysql - rtprio 100
root - proc 65535
root - nofile 65535
EOFlimits
ulimit -u 10240
ulimit -n 262140

修改内核参数设置

cat > /etc/sysctl.conf <<EOFsysctl
kernel.sysrq =0
net.core.wmem_default=8388608
net.core.rmem_default=8388608
net.core.rmem_max=16777216
net.core.wmem_max=16777216
net.ipv4.tcp_timestamps=1
net.ipv4.tcp_tw_recycle=1
net.ipv4.tcp_tw_reuse=1
net.ipv4.tcp_keepalive_time=300
net.ipv4.tcp_keepalive_probes=2
net.ipv4.tcp_keepalive_intvl=10
net.ipv4.ip_local_port_range=10000 65535

vm.swappiness=1
fs.aio-max-nr=1048576
net.ipv6.conf.all.disable_ipv6=1
net.ipv6.conf.default.disable_ipv6=1
EOFsysctl
sysctl -p

关闭selinux

setenforce 0
sed -i ‘s/SELINUX=enforcing/SELINUX=disabled/’ /etc/selinux/config

调整ssh设置

sed -i ‘s/#UseDNS ./UseDNS no/g’ /etc/ssh/sshd_config
sed -i ‘s/#GSSAPIAuthentication ./GSSAPIAuthentication no/g’ /etc/ssh/sshd_config

将磁盘io调度方式设置为deadline

echo deadline>/sys/block/sda/queue/scheduler
echo deadline>/sys/block/sdb/queue/scheduler
echo deadline>/sys/block/sdc/queue/scheduler
echo deadline>/sys/block/sdd/queue/scheduler
if [ cat /etc/redhat-release|sed -r 's/.* ([0-9]+)\..*/\1/' = "7" ];then
grubby --update-kernel=ALL --args="elevator=deadline"
fi

关闭防火墙,防止自启

service iptables stop 2> /dev/null
service ip6tables stop 2> /dev/null
service firewalld stop 2> /dev/null
chkconfig iptables off 2> /dev/null
chkconfig ip6tables off 2> /dev/null
chkconfig firewalld off 2> /dev/null

配置mysql的选项文件

cat > /etc/my.cnf <<EOFmy
[client]
user =root
host =127.0.0.1
port =3306
loose-default-character-set =utf8mb4

[mysqld]
server-id =3306
port =3306
socket =/tmp/mysql.sock

datadir =/data/mysql/data/

general-log-file =/data/mysql/log/general.log
slow-query-log-file =/data/mysql/log/slow.log

log-bin =/data/mysql/repl/binlog
relay-log =/data/mysql/repl/relaylog

innodb-undo-directory =/data/mysql/log/
innodb-log-group-home-dir =/data/mysql/log/

plugin-load ="validate_password=validate_password.so"
plugin-load-add ="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

basedir =/usr/local/mysql/
read-only =1
user =mysql
lower_case_table_names=1
character-set-server =utf8mb4
default-storage-engine =INNODB
secure-file-priv='/data/mysql/'
explicit_defaults_for_timestamp=ON
sql_mode=default

event-scheduler =1
log-bin-trust-function-creators =1
group-concat-max-len =102400
validate-password =ON
validate_password_length=8
validate_password_policy=0

log-output =FILE
general-log
slow-query-log
long_query_time =1

gtid-mode =ON
enforce-gtid-consistency =true

binlog-format =ROW
expire-logs-days =70
max-binlog-size =1024M
log-slave-updates =1

skip-slave-start
relay-log-recovery =ON
slave-parallel-type =LOGICAL_CLOCK
slave-parallel-workers =20
replicate-ignore-db =test

relay-log-info-repository =TABLE
master-info-repository =TABLE

rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled =1
rpl_semi_sync_master_timeout=10000 #毫秒
rpl_semi_sync_master_wait_no_slave=1 #默认
rpl_semi_sync_master_wait_for_slave_count=1
rpl_semi_sync_master_wait_point=AFTER_SYNC #,AFTER_COMMIT

innodb-data-file-path =ibdata1:128M;ibdata2:64M:autoextend:max:2048M
innodb-temp-data-file_path =ibtmp1:128M;ibtmp2:64M:autoextend:max:2048M
innodb-autoextend-increment =64

auto_increment_offset =1
auto_increment_increment =2

skip-name-resolve=1
innodb-file-per-table =1
innodb-flush-log-at-trx-commit =1
sync-binlog =1
max-connections =200
max-allowed-packet =300M

innodb_buffer_pool_size =128M
innodb_buffer_pool_instances=1 #默认 当维持每个实例的大小大于1g才生效
innodb_log_buffer_size =16M
key_buffer_size =8M
table_definition_cache =1400
table_open_cache =2000
thread_cache_size =16
host_cache_size =128

query_cache_type=2
query_cache_size=1M
binlog_cache_size=32K # binlog_cache_use和binlog_cache_disk_use
binlog_stmt_cache_size=16K # binglog_stmt_cache_use和binlog_stmt_cache_disk_use
stored_program_cache=256
max_prepared_stmt_count=16382

read_buffer_size=128K #截断为4K的倍数:myisam顺序扫描;ORDER BY时caching the indexes in a temporary file;bulk insert into partitions;caching results of nested queries;determine the memory block size for MEMORY tables;
read_rnd_buffer_size=256K # reading rows from a MyISAM table in sorted order following a key-sorting operation;Multi-Range Read optimization;
bulk_insert_buffer_size=8M #多行插入如insert ...select,insert...values(),(),load data等,设置为0将禁用优化
join_buffer_size=256K # When BKA is used, the value of join_buffer_size defines how large the batch of keys is in each request to the storage engine. The minimum size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans.
sort_buffer_size=256K # Sort_merge_passes比较多时,增加该值。
innodb_sort_buffer_size=1M #静态全局,索引创建中分配3个
myisam_sort_buffer_size=8M # when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE.
tmp_table_size=16M #group by很重要;Created_tmp_disk_tables和Created_tmp_tables
max_heap_table_size=16M
preload_buffer_size=32K # The size of the buffer that is allocated when preloading indexes.
max_digest_length=1024 #默认,每个会话

innodb_old_blocks_pct=37 #默认
innodb_old_blocks_time=1000 #默认
innodb_change_buffering=all #默认
innodb_change_buffer_max_size=25 #默认。
innodb_adaptive_hash_index=1 #默认,当随机调整

innodb_thread_concurrency=0 #尝试将innodb并发线程数控制在给定范围内
innodb_commit_concurrency=0 #同时提交的线程数;动态不能再0和非0间改;
innodb_concurrency_tickets=5000
innodb_thread_sleep_delay=10000 #定义InnoDB线程在加入InnoDB队列之前休眠的时间(以微秒为单位)
innodb_adaptive_max_sleep_delay=150000

innodb_read_io_threads=4
innodb_write_io_threads=4
innodb_purge_threads=4
innodb_purge_batch_size=300 #每个线程每次清理的undo页

innodb_io_capacity=2000 #所有缓冲池实例的每秒i/o操作数的上限,默认200。 机械硬盘可能只能有200
innodb_io_capacity_max=4000 #当flush落后,可以增加capacity到该数值。默认两倍cp。

innodb_read_ahead_threshold=56 #默认 #状态Innodb_buffer_pool_read_ahead_evicted,Innodb_buffer_pool_read_ahead
innodb_random_read_ahead=1 #默认 #状态 Innodb_buffer_pool_read_ahead_rnd

innodb_flush_sync=1 #默认
innodb_adaptive_flushing_lwm=10
innodb_adaptive_flushing=1
innodb_max_dirty_pages_pct_lwm=0 #默认禁用预刷新
innodb_max_dirty_pages_pct=75

innodb_spin_wait_delay=6 #默认

innodb_buffer_pool_dump_at_shutdown=ON
innodb_buffer_pool_dump_pct=25 #默认25,可以设置高一点。在关闭时为每个缓冲池保存最近使用的页面的百分比,减少预热时间
innodb-buffer-pool-load-at-startup=ON
innodb_stats_persistent=1

EOFmy

为初始化创建相应的目录,防止初始化或启动mysql失败

rm -rf /data/mysql/data /data/mysql/log /data/mysql/repl /data/mysql/ib #实际上只要保证数据目录为空或不存在,其他目录存在即可
mkdir -p /data/mysql/data
mkdir -p /data/mysql/log
mkdir -p /data/mysql/repl
mkdir -p /data/mysql/ib

为相应目录、文件设定mysql用户和正确的读写权限

chown -R mysql:mysql /etc/my.cnf
chmod -R 750 /etc/my.cnf
chown -R mysql:mysql /data/mysql/
chmod -R 750 /data/mysql/

开始初始化数据目录

mypw=$mysqld --initialize --user=mysql 2>&1 |grep "A temporary password is generated for" #这里没有给出–datadir,故配置文件中必须给出
mypw=${mypw#*: }
if [ -z $mypw ]; then “error:something wrong when initialize mysql”;exit 1;fi
echo $mypw

启动mysql

service mysqld start
if [ “$?” != 0 ];then echo “error:something wrong during starting mysqld”;exit 1; fi
sleep 1
service mysqld status

修改root密码,创建一些常用账户并赋予权限

addsalt=ztd123 #注意设定加盐长度等使其符合密码验证插件的要求

mysql --protocol=tcp -P3306 -uroot --password=$mypw --connect-expired-password -e "
ALTER USER 'root'@'localhost' IDENTIFIED BY 'root$addsalt';
update mysql.user set mysql.user.Host='%' where mysql.user.User='root';

CREATE USER 'application'@'%' IDENTIFIED BY 'application$addsalt';
GRANT DELETE,UPDATE,INSERT,SELECT,EXECUTE ON *.* TO 'application'@'%';

CREATE USER 'readonly'@'%' IDENTIFIED BY 'readonly$addsalt';
GRANT SELECT ON *.* TO 'readonly'@'%';

CREATE USER 'replication'@'%' IDENTIFIED BY 'replication$addsalt';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replication'@'%';

CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor$addsalt';
GRANT SUPER,PROCESS,SELECT ON *.* TO 'monitor'@'%';

CREATE USER 'backup'@'%' IDENTIFIED BY 'backup$addsalt';
GRANT SELECT,RELOAD,PROCESS,REPLICATION CLIENT ON *.* TO 'monitor'@'%';

CREATE USER 'shutdown'@'localhost' IDENTIFIED BY 'shutdown$addsalt'; 
GRANT SHUTDOWN ON *.* TO 'shutdown'@'localhost';
flush privileges;
"

验证root密码是否修改成功,若能成功进入mysql交互界面,则已成功安装并配置好mysql。
mysql --protocol=tcp -P3306 -uroot -proot$addsalt

好了!以上就是关于如何安装的详细步骤,完结撒花~
如果看的还不过瘾可以浏览之前的文章——MySQL数据类型测试系列文章和分布式事务数据库MySQL数据库开发规范
see you~

如果觉得我的文章对您有用,请点赞。您的支持将鼓励我继续创作!

3

添加新评论0 条评论

Ctrl+Enter 发表

关于TWT  使用指南  社区专家合作  厂商入驻社区  企业招聘  投诉建议  版权与免责声明  联系我们
© 2019  talkwithtrend — talk with trend,talk with technologist 京ICP备09031017号-30