mysql5.7 存储二进制_MySQL5.7 基于二进制包的安装

傷城~ 2023-01-12 11:49 126阅读 0赞

1.MySQL5.7安装注意事项

1.在MySQL5.7中mysql\_install\_db已经不再推荐使用，建议改成mysqld-initialize 完成实力初始化。(mysql\_install\_db在/mysql/bin目录下，同时取消了/mysql/scripts目录)

2.在初始化是如果加上--initial-insecure,则会创建空密码的root@localhost 账号，否则会创建带密码的root@localhost账号，密码直接写在log-error日志文件中

3.MySQL5.7的安装过程与5.6的主要区别在于数据初始化过程。

5.7.12二进制安装包下载地址：

链接：https://pan.baidu.com/s/1PAeHOyRRSMzRqCUIasE0xQ

密码：12r6

2.MySQL5.7安装

2.1 安装相关依赖包

大部分情况下生产环境在系统初始化的过程中已经安装大部分依赖包，防止偶然时间。

yum -y groupinstall "Development tools"

yum -y install ncurses-devel openssl-devel bison gcc gcc-c++ make

2.2 解压MySQL二进制tar包

cd到mysql二进制的目录，然后使用tar命令解压

tar xf mysql-5.7.12-linux-glibc2.5-x86\_64.tar.gz

2.3 复制相关目录

复制解压后的mysql到mysqlbase目录

cp -rf mysql-5.7.12-linux-glibc2.5-x86\_64 /usr/local/mysql

2.4 创建MySQL用户

在linux中添加mysql用户

useradd mysql

2.5 创建MySQL数据目录

创建数据目录

mkdir -p /home/mysql3306/mysql3306

创建日志目录

mkdir -p /home/mysql3306/logs

2.6 修改目录权限

chown -R mysql.mysql -R /home/mysql3306

chown-R mysql.mysql -R /usr/local/mysql/

2.7 创建配置文件

删掉系统自带my.cnf,创建自定义配置文件

mv /etc/my.cnf /etc/my.cnf.bak

vim/etc/my3306.cnf

my.cnf配置模板

\#mysql5.7.9\[client\]

port= 3306socket= /tmp/mysql3306.sock

default\_character\_set=utf8mb4

\[mysqld\]

\#\*\*\* generic configuration options \*\*\*\#

lower\_case\_table\_names= 1port= 3306basedir= /usr/local/mysql

datadir= /home/mysql3306/mysql3306

socket= /tmp/mysql3306.sock

pid\_file= /home/mysql3306/mysql5711.pid

tmpdir= /tmp/slave\_load\_tmpdir= /tmp/\#\*\*\* dns resolve \*\*\*\#

skip\_name\_resolve= 1external\_locking

explicit\_defaults\_for\_timestamp= truecharacter\_set\_client\_handshake=FALSE

character\_set\_server=utf8mb4

collation\_server=utf8mb4\_unicode\_ci

\#\*\*\* connection \*\*\*\#

max\_connections= 512max\_connect\_errors= 100000back\_log= 102interactive\_timeout= 600wait\_timeout= 600table\_definition\_cache= 5000table\_open\_cache= 2048\#query\_cache\_size=64M

\#query\_cache\_limit=2M

thread\_cache\_size= 51ft\_min\_word\_len= 4\#performance\_schema=OFF

\#\#If large data transfer problems,enlarge max\_allowed\_packet

thread\_stack=192K

\#\*\*\* global memory parameter \*\*\*\#

max\_allowed\_packet=16M

tmp\_table\_size=256M

max\_tmp\_tables= 128max\_heap\_table\_size=96M

\#close query cache

\#query\_cache\_type= 0\#\*\*\* session memory parameter \*\*\*\#

sort\_buffer\_size=4M

read\_buffer\_size=4M \#顺序IO

join\_buffer\_size=4M

read\_rnd\_buffer\_size=8M \#随机IO

bulk\_insert\_buffer\_size=64M \#

\#\*\*\* engine \*\*\*\#

default\_storage\_engine=InnoDB

\#ignore\_builtin\_innodb

\#plugin\_load=innodb=ha\_innodb\_plugin.so;innodb\_trx=ha\_innodb\_plugin.so;innodb\_locks=ha\_innodb\_plugin.so;innodb\_lock\_waits=ha\_innodb\_plugin.so;innodb\_cmp=ha\_innodb\_plugin.so;innodb\_cmp\_reset=ha\_innodb\_plugin.so;innodb\_cmpmem=ha\_innodb\_plugin.so;innodb\_cmpmem\_reset=ha\_innodb\_plugin.so

\#\*\*\* log \*\*\*\#

log\_timestamps=system

log\_bin= /home/mysql3306/logs/mysql-bin

expire\_logs\_days= 10binlog\_format=mixed

\#binlog\_gtid\_simple\_recovery= 1\#binlog\_do\_db

\#binlog\_ignore\_db

log\_slave\_updates=1relay\_log= /home/mysql3306/logs/mysql5711\_relay\_bin

relay\_log\_index= /home/mysql3306/logs/mysql5711\_relay\_bin.index

relay\_log\_info\_file= /home/mysql3306/logs/mysql5711\_relay\_bin.info

relay\_log\_recovery= 0log\_error= /home/mysql3306/logs/mysql5711.err

slow\_query\_log= 1slow\_query\_log\_file= /home/mysql3306/logs/mysql5711\_slow.log

long\_query\_time= 1log\_output=FILE

log\_queries\_not\_using\_indexes= 1log\_slow\_admin\_statements= 1log\_slow\_slave\_statements= 1log\_throttle\_queries\_not\_using\_indexes= 10min\_examined\_row\_limit= 100\#general\_log=off

general\_log\_file= /home/mysql3306/logs/mysql5711\_general.log

\#log\_queries\_not\_using\_indexes= /mysqllog/mysql5711/mysql5711\_not\_useing\_index.log

max\_binlog\_cache\_size=8M

max\_binlog\_size=1024M

binlog\_cache\_size=4M

sync\_binlog= 1server\_id= 15133306\#read\_only= 0\#slave\_skip\_errors=all

\#\*\*\* MyISAM Specific options \*\*\*\#

\#key\_buffer\_size=128M

\#bulk\_insert\_buffer\_size=64M

\#myisam\_sort\_buffer\_size=128M

\#myisam\_max\_sort\_file\_size=10G

\#myisam\_repair\_threads= 1\#myisam\_recover\_options=BACKUP,FORCE

\#\*\*\* INNODB Specific options \*\*\*\#

innodb\_data\_home\_dir= /home/mysql3306/mysql3306/innodb\_data\_file\_path=ibdata1:1024M:autoextend

innodb\_log\_group\_home\_dir= /home/mysql3306/mysql3306/innodb\_buffer\_pool\_instances= 1innodb\_buffer\_pool\_size=1024MB

innodb\_buffer\_pool\_dump\_pct= 40innodb\_page\_cleaners= 4innodb\_log\_file\_size=256M

innodb\_log\_files\_in\_group= 2innodb\_log\_buffer\_size= 32M \#default16M

innodb\_flush\_log\_at\_trx\_commit= 1innodb\_lock\_wait\_timeout= 30innodb\_undo\_directory= /home/mysql3306/mysql3306/innodb\_undo\_logs= 128 \#must >=35.default 128innodb\_undo\_tablespaces= 3 \#must >=2innodb\_undo\_log\_truncate= 1innodb\_max\_undo\_log\_size=256M

innodb\_purge\_rseg\_truncate\_frequency= 128innodb\_print\_all\_deadlocks= 1innodb\_sort\_buffer\_size=64M

\#disabled\_storage\_engines=MyISAM

innodb\_buffer\_pool\_load\_at\_startup= 1innodb\_buffer\_pool\_dump\_at\_shutdown= 1innodb\_file\_format\_max=Antelope

innodb\_file\_format=Antelope

sql\_mode="STRICT\_TRANS\_TABLES,NO\_ENGINE\_SUBSTITUTION"innodb\_open\_files= 4000innodb\_file\_per\_table= 1innodb\_status\_file= 1innodb\_lock\_wait\_timeout= 30innodb\_read\_io\_threads= 8innodb\_write\_io\_threads= 8innodb\_io\_capacity= 200innodb\_thread\_concurrency= 32innodb\_commit\_concurrency= 0\#innodb\_flush\_neighbors= 0 \#forssd

\#innodb\_flush\_neighbors= 1 \#forother

innodb\_purge\_batch\_size= 1000innodb\_flush\_log\_at\_trx\_commit= 0innodb\_flush\_method=O\_DIRECT

innodb\_purge\_threads= 4\#innodb\_file\_format=barracuda

innodb\_strict\_mode= 1innodb\_support\_xa= 1skip\_innodb\_adaptive\_hash\_index

innodb\_force\_recovery=0\#innodb\_fast\_shutdown

innodb\_change\_buffering=all

\#innodb\_use\_native\_aio= 1innodb\_max\_dirty\_pages\_pct= 75\#\#replication

\#replicate\_do\_db

\#replicate\_ignore\_db=mysql

\#replicate\_ignore\_db=test

\#replicate\_do\_table

\#replicate\_ignore\_table

\#replicate\_wild\_do\_table

\#replicate\_wild\_ignore\_table

\#gtid\_mode=on

\#enforce\_gtid\_consistency= truemaster\_info\_repository=TABLE

relay\_log\_info\_repository=TABLE

sync\_master\_info= 1slave\_parallel\_workers= 2binlog\_checksum= CRC32 \#defaultcrc32

master\_verify\_checksum= 1 \#defaultoff

slave\_sql\_verify\_checksum= 1\#binlog\_rows\_query\_log\_events= 1\#

\#\#semi sync replication settings

\#plugin\_dir=/usr/local/mysql57/lib/plugin

\#plugin\_load= "rpl\_semi\_sync\_master=semisync\_master.so;rpl\_semi\_sync\_slave=semisync\_slave.so"\#loose\_rpl\_semi\_sync\_master\_enabled= 1\#loose\_rpl\_semi\_sync\_slave\_enabled= 1\#loose\_rpl\_semi\_sync\_master\_timeout= 10000 \# 10second

\#\#transaction

transaction\_isolation= REPEATABLE-READ

\#transaction\_write\_set\_extraction= MURMUR32 \#open --single-tra no use

\[mysqldump\]

quick

max\_allowed\_packet=16M

\[mysql\]

no\_auto\_rehash

default\_character\_set=utf8mb4

\[myisamchk\]

\#key\_buffer\_size=20M

\#sort\_buffer\_size=4M

\#read\_buffer=4M

\#write\_buffer=2M

\[mysqlhotcopy\]

interactive\_timeout

\[mysqld\_safe\]

open\_files\_limit= 8192

2.8 初始化MySQL

MySQL5.7的初始化过程

/usr/local/mysql/bin/mysqld --defaults-file=/etc/my3306.cnf --initialize-insecure --explicit\_defaults\_for\_timestamp --basedir=/usr/local/mysql --datadir=/home/mysql3306/mysql3306/ --user=mysql

2.9 启动MySQL

/usr/local/mysql/bin/mysqld\_safe --defaults-file=/etc/my3306.cnf --datadir=/home/mysql3306/mysql3306/ --user=mysql &

查看进程和监听端口

ps-ef|grep mysql

netstat-nltp|grep 3306

2.10 进入MySQL

启动后密码为空

/usr/local/mysql/bin/mysql -S /tmp/mysql3306.sock

2.11 安装完成后，简单初始化权限

因为安装完成后，没有详细的权限设置，所以我们需要手动的配置一下简单的权限

delete from mysql.user where user!='root' or host!='localhost';

update mysql.user set authentication\_string=password('root') where user='root';