MySQL binary installation

MySQL Binary installation documentation

2018/3/16 18:35:49
[Email protected]
Https://dev.mysql.com/doc/refman/5.7/en/binary-installation.html

[TOC]

0. Installation Brief steps

• installation package 10.162.73.61:/data/software
• TAR-XVF mysql-5.6.36-linux-glibc2.5-x86_64.tar.gz
• MV mysql-5.6.36-linux-glibc2.5-x86_64//data/mysql
• Upload my.cnf, create a directory in MY.CNF
• CD $basedir, initializing
  • ./scripts/mysql_install_db--defaults-file=/etc/my36.cnf--user=mysql #5.6
  • ./bin/mysqld--defaults-file=/etc/my38.cnf--initialize-insecure--user=mysql # 5.7
• Modify Permissions
  • Shell> chown Mysql:mysql $basedir
  • Shell> chown Mysql:mysql $datadir
  • shell> chmod mysql-files
• Start the database
  • Numactl--interleave=all/data/mysql3308/bin/mysqld_safe--defaults-file=/etc/my_frontweb_snap.cnf--ledir=/data/ Mysql3308/bin/& #5.6
  • Numactl--interleave=all/data/mysql3308/bin/mysqld_safe--defualts-file=/etc/my.cnf & #5.7
• Security Retrofit
  • ./mysql_secure_installation-s/data/mysql3307/data/mysql.sock
• Set the PATH variable
  • Vim/etc/profile
  • . /etc/profile

1. Configuring the installation Environment 1.1 disk planning

It is best to separate the system disk from MySQL:

• The system disk is 500G RAID1
• Standalone Mount SSD disk format mount as Mysqlbasedir and DataDir

1.2 Configuring the network environment

vim /etc/sysconfig/network-scripts/ifcfg-eth0vim /etc/sysconfig/networkvim /etc/hosts                //加入127.0.0.1和主机名的映射

1.3 Executing initialization scripts

vim mysql_install_init.shbash ./mysql_insall_init.sh

Script Content

Modify_para () {limit_cn= ' cat/etc/security/limits.conf |grep-i ' modify by Initsys ' |wc|awk ' {print '} ' if [$limit _cn-eq 0] Then Echo-e "\n###### Modify by Initsys ######" >>/etc/security/limits.conf echo "* Soft Npro C 1024000 ">>/etc/security/limits.conf echo" * Hard Nproc 1024000 ">>/etc/security/limits.c onf echo "* Soft nofile 1024000" >>/etc/security/limits.conf echo "* Hard Nofile 1024000 ">>/etc/security/limits.conf echo-e" \nmysql soft nproc 1024000 ">>/etc/security/limits.co NF echo "MySQL hard nproc 1024000" >>/etc/security/limits.conf echo "MySQL soft nofile 1 024000 ">>/etc/security/limits.conf echo" MySQL hard nofile 1024000 ">>/etc/security/limits.co nffisysctl_cn= ' cat/etc/sysctl.conf |grep ' modify by Initsys ' |wc|awk ' {print $} ' if [$sysctl _cn-eq 0]then echo-e ' \n###### Modify by InitsyS ###### ">>/etc/sysctl.conf echo fs.aio-max-nr = 1048576 >>/etc/sysctl.conf echo Fs.file-max = 681574 4 >>/etc/sysctl.conf echo vm.swappiness = ten >>/etc/sysctl.conf echo Net.core.rmem_max = 4194304 >& Gt /etc/sysctl.conf echo net.core.wmem_default = 262144 >>/etc/sysctl.conf echo net.core.wmem_max = 1048586 > >/etc/sysctl.conf echo kernel.msgmnb = 65536 >>/etc/sysctl.conf echo kernel.msgmax = 655360 >>/etc /sysctl.conf echo Kernel.sem = 32000 142 >>/etc/sysctl.conf echo Kernel.shmmni = 4096 >>/etc/s ysctl.conf echo kernel.shmall = 1073741824 >>/etc/sysctl.conf echo kernel.shmmax = 4398046511104 >>/et c/sysctl.conf echo net.ipv4.ip_local_port_range = 65500 >>/etc/sysctl.conf echo net.core.rmem_default = 262144 >>/etc/sysctl.conf echo net.ipv4.tcp_max_syn_backlog = 8192 >>/etc/sysctl.conf echo net.core.so Maxconn = 8192 >&Gt /etc/sysctl.conf echo net.ipv4.tcp_keepalive_time = >>/etc/sysctl.conf echo net.ipv4.tcp_keepalive_probes = 3 >>/etc/sysctl.conf echo NET.IPV4.TCP_KEEPALIVE_INTVL = >>/etc/sysctl.conf echo net.ipv4.tcp_fin _timeout = >>/etc/sysctl.conffi}modify_grub () {#edi_flag = ' cat/etc/redhat-release |grep ' release 5 ' |wc|awk ' { Print $ ' #if [$edi _flag-eq 1] #then mv/boot/grub/grub.conf/boot/grub/grub.conf_bak sed "s/.*default.*/default =1/"/boot/grub/grub.conf_bak >/boot/grub/grub.conf cat/boot/grub/grub.conf#fi}modify_user () {Useradd Mysqlusermod-g root-d/data/mysql/mysql}mofify_iptables () {/sbin/service iptables stop/sbin/chkconfig iptables off} Modify_paramodify_grubmodify_usermofify_iptables

1.4 Download Binary installation package

https://dev.mysql.com/downloads/mysql/
https://downloads.mysql.com/archives/community/#归档历史版本

2. Install MySQL2.1 install MySQL in the specified directory

1. Decompression

tar -xvf mysql-5.6.36-linux-glibc2.5-x86_64.tar.gzmv mysql-5.6.36-linux-glibc2.5-x86_64/  /data/mysql

2. New User

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

2.2 Edit MY.CNF

1. Attention

• MY.CNF's permission is 644.
• To manually create some of the directories defined in the MY.CNF, otherwise the initialization will be wrong
• In a multi-instance environment, the MY.CNF name can be configured according to the scenario, such as the configuration file for the/data/mysql3307 directory is/etc/my3307.cnf

2, a simple my.cnf

#适用于所有客户端程序读取 [client]port = 3306socket =/data/mysql/data/mysql.sock[mysql]no_auto_reh            Ash # When the table is long, automatically avoid collecting a lot of meta-data information, not automatically complete prompt = "\\r:\\m:\\s\\d>"                    #H: M:s:dbpager = "more" Default-character-set = utf8mb4 [mysqld]# #base Settingport = 3306socket =/data/mysql/data/mysql.sock #建议设置socket的位置在数据目录server-id =  171109report_host = 10.17.1.109 # show slave status in host Ipbasedir =/data/mysql                        DataDir =/data/mysql/data Tmpdir =/data/mysql/tmpdir user = MySQL                            # to avoid user--user=root adding options to my.cnf files Log_error = error.loginnodb_file_per_table = 1 # stand-alone tablespace lower_case_table_names = 1 # case-insensitive, library name, table name, table alias, variable name are handled in lowercase character_set    _server = Utf8mb4                                  Collation_server = utf8mb4_bin # #connectionskip-sslskip_name_resolve              = 1 # does not resolve client connection host name, authorization table only with IP address or localhostmax_connections = 1500              # Maximum number of connections Max_allowed_packet = 64M # packet maximum size innodb_buffer_pool_size = 10G # buffer pool size, 70%mem, if set too large beyond memory, easy oominnodb_buffer_pool_instances = 2 # Divide buffer pools into areas, less pool civil strife with # #redolo Gfile, Ibdatainnodb_flush_log_at_trx_commit = 1 # default 1,0 commit when the log is not written, write log and brush disk per second, 1 write the log and brush the disk, 2 commit the log, brush disk per second innodb_l  Og_files_in_group = 3 # Redo log file Number innodb_log_file_size = 2G # Redo Log file Size # # as Mastersync_binlog = 1 Log-bin =/data/mysql/arch/mysql- Bin # Set Binlog path and file prefix name binlog_rows_query_log_events = 1 # native Sqllog_bin_trust_function_c recorded in Binlog          Reators = 1      # By default, the function is written to Binlog, avoiding the creation of function by default Binlog_format = row # row mode replication Max_binlo G_size = 1G # binlog File size # # as slave relay_log =/data/mysql/rel Ay_log/relay-bin # Set relay log path and file prefix name relay_log_recovery = 1 # Turn on new trunk log, pull back from SQL execution location, clean old R                     Elay logsync_relay_log = 1 # relay Log brush disc mode, 0 dependent os,n,n event write relay log after brush disc Skip_slave_start = 1 # Avoid automatic start of slave thread from library

2.3 Initialization

• MySQL 5.6 and 5.7 Initialize different
• Multiple DataDir can be initialized with Basedir

Initialization of the 2.3.1 MySQL5.6

cd $basedir./scripts/mysql_install_db --defaults-file=/etc/my36.cnf --user=mysql      

Initialization of the 2.3.2 MySQL5.7

cd $basedir./bin/mysqld  --defaults-file=/etc/my38.cnf --initialize-insecure --user=mysql    # version 5.7

2.4 Start and close 2.4.1 Startup Suggestions

1. Note Database permissions
2. Using Mysqld_safe to start MySQL, the daemon mysqld_safe be able to pull up the mysqld service that suddenly hangs out;
3. Mysqld_safe--defaults-file, which means only using this configuration file, is very important under multiple instances and is not easy to read as a default configuration file
4. 5.6 When booting, the--LEDIR option specifies the MYSQLD program directory, which is very important

2.4.2 Command

# 修改权限chown mysql:mysql $basedirchown mysql:mysql $datadirchmod 750 mysql-files# 5.6启动numactl --interleave=all  /data/mysql3308/bin/mysqld_safe --defaults-file=/etc/my_frontweb_snap.cnf --ledir=/data/mysql3308/bin/ &# 5.7启动numactl --interleave=all /data/mysql20/bin/mysqld_safe --defaults-file=/etc/my20.cnf  &[1] 2435#2018-03-16T10:05:19.864783Z mysqld_safe Logging to ‘/data/mysql20/data/error.log‘.# 查看启动日志tailf  -100 /data/mysql20/data/error.log#关闭数据库mysqladmin -u -p -h -P shutdown

2.4.3 possible alarms and errors during startup

• Warning can be ignored
• Error typically causes database exceptions

# 告警1could not be looked up with /data/mysql//bin/resolveip.WARNING: The host ‘jq-db-3-22‘ could not be looked up with /data/mysql//bin/resolveip.# 解决1/etc/hosts 加入对hostname的解析# 告警2SSL context is not usable without certificate and private key[Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key#解决2在5.7中，通过ssl方式连接mysql被作为默认和推荐,如果不使用，skip掉vim /etc/my.cnf [mysqld]skip-ssl# 报错3找这个文件的时候报错：[ERROR] InnoDB: Cannot open ‘/data/mysql/data/ib_buffer_pool‘ for reading: No such file or directory#解决3    ib_buffer_pool 是--innodb-buffer-pool-filename=file 这个选项是默认值。    这个文件中放的是由于【 innodb_buffer_pool_dump_at_shutdown or innodb_buffer_pool_dump_now.】保存下来的tablespace IDs and page IDs 的一个列表。    所以，出现这个错误的时候，可以指定innodb-buffer-pool-filename=file，或者是关闭innodb_buffer_pool_dump的几个选项。

2.5 Login 2.5.1 Local use socket login

Note that the socket can only be logged on locally, and after the Mysqld service is started, there must be an [email protected] user with the highest privileges
Recommendation: During security, do not create a MySQL socket file in the/TMP public directory

# 登陆mysql -pmima --socket=/data/mysql_3307/data/mysql.sock  # 查看连接方式17:42:25 (none)> \sConnection:            Localhost via UNIX socket  `

2.5.2 TCP/IP Login

• Attention
  • Need to use [email protected]' IP ' users, such as [email protected] ' 127.0.0.1 '
• Suggestions
  • It is not recommended to create a root user [email protected]'% ' arbitrary address

【登陆】mysql -pmima -h127.0.0.1 -P3308【查看连接方式】17:43:42 (none)> \sConnection:            127.0.0.1 via TCP/IP

2.5.3 Login Error Handling

1. Access denied

# 报错mysql -p123123mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user ‘root‘@‘localhost‘ (using password: YES)# 检查用户名和密码（是[email protected]，还是[email protected])登陆# mysql登录时会自动检查my.cnf 中 [mysql] 和 [client]的配置，因为# cat /etc/my.cnf |more[client]port                    = 3308socket                  = /data/mysql20/data/mysql.sock所以，mysql使用socket登陆，会报错登陆[email protected]这个用户错误

2, Character set ' UTF8MB4 ' is not a compiled Character set

# mysql -proot mysql: Character set ‘utf8mb4‘ is not a compiled character set and is not specified in the ‘/usr/share/mysql/charsets/Index.xml‘ file解决：# yum list installed |grep mysqlmha4mysql-node.noarch              0.57-0.el6                        @/mha4mysql-node-0.57-0.el6.noarchmysql.x86_64                        5.1.73-8.el6_8                    @base    #因为安装了这个mysql-devel时，安装了mysql5.1的客户端mysql-devel.x86_64                  5.1.73-8.el6_8                    @base    mysql-libs.x86_64                  5.1.73-8.el6_8                    @base    zabbix-web-mysql.noarch            3.4.7-1.el6                        @zabbix  

2.6 Password hack

To start the database with the option skip-grant-tables:

• --skip-grant-tables option, this option enables the server to start without using the permission system at all, so that everyone accesses the database without a password
• After logging in, modify the user's password by set or alter
• Execute the mysqladmin flush-privileges or mysqladmin reload command in the shell, or after logging in to MySQL, flush privilege let MySQL Serer Reload Authorization Table

# 启动numactl --interleave=all  /data/mysql3308/bin/mysqld_safe --defualts-file=/etc/my.cnf  --skip-grant-tables & [1] 2435# 登陆mysql -urootWelcome to the MySQL monitor.  Commands end with ; or \g.#加载授权表，否则ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement18:06:28(none)>flush privileges;#修改密码set password for [email protected] = password(‘root‘);

2.7 MySQL Process detection

1. Process detection

# 检测命令ps -ef |grep mysqldps aux |grep mysqld# 进程信息root    36133 21704  0 17:35 pts/1    00:00:00 /bin/sh /data/mysql3308/bin/mysqld_safe --defaults-file=/etc/my_frontweb_snap.cnf --ledir=/data/mysql3308/bin/mysql    36978 36133  1 17:35 pts/1    00:00:06 /data/mysql3308/bin//mysqld --defaults-file=/etc/my_frontweb_snap.cnf --basedir=/data/mysql3308 --datadir=/data/mysql3308/data --plugin-dir=/data/mysql3308/lib/plugin --user=mysql --log-error=/data/mysql3308/data/error.log --open-files-limit=655350 --pid-file=/data/mysql3308/data/hadoop-jq-9-31.pid --socket=/data/mysql3308/data/mysql.sock --port=3308# 关于进程号可以从进程号中看出，root用户启动了mysqld_safe进程，mysql的父进程是mysqld_safe进程，也就是说，mysqld_safe拉起了mysql的mysqld程序

2. Port detection

# netstat检测netstat -an |grep 3306tcp        0      0 10.17.9.31:22261            10.17.1.110:3306            ESTABLISHED tcp        0      0 :::3306                    :::*                        LISTEN  # lsof检测，需要安包rpm -qf `which lsof`lsof-4.82-4.el6.x86_64lsof -i:3306 COMMAND  PID  USER  FD  TYPE    DEVICE SIZE/OFF NODE NAMEmysqld  3229 mysql  16u  IPv6    15009      0t0  TCP *:mysql (LISTEN)

3. Client detection

# mysql登录检测能使用mysql登录指定mysqld服务，服务肯定是在运行的# mysqladmin ping检测mysqladmin -pmima  ping      mysqld is alive

3, installation and rehabilitation Operation 3.1 mysql_secure_installation security modification

1. Introduction

• This is the same as MySQL, is a client program, the program under $basedir/bin/
• /data/mysql/bin/mysql_secure_installation
• The role is to improve installation security, the recommended version of less than 5.7 of the use of certain

2. How to use

./mysql_secure_installation --help# ./mysql_secure_installation -S /data/mysql3307/data/mysql.sockSecuring the MySQL server deployment.Enter password for user root: mysql_secure_installationEnter current password for root (enter for none):      # 密码为空，直接回车Change the root password? [Y/n] Y                      #修改密码Remove anonymous users? [Y/n] y                        #移除匿名用户.              ，5.7默认没有匿名用户Disallow root login remotely? [Y/n] y                  #禁止root用户远程登陆[email protected]’%’，5.7没有[email protected]‘%‘用户Remove test database and access to it? [Y/n] y          #移除test数据库              ，5.7无test数据库Reload privilege tables now? [Y/n] y                    #刷新权限表

3.2 PATH Variable settings

1. Advantages and Disadvantages

• Advantages
  • After setting the path variable, many programs for MySQL can be used directly
• Disadvantages
  • In the case of multiple instances, it is easy to create problems with production, such as:
  • Already have 5.6mysql service running, PATH variable set $5.6basedir/bin, want to initialize or start 5.7 service, must use 5.7 absolute path command, otherwise start a 5.6mysqld service

2. How to set up

# 编辑文件vim /etc/profile#最后追加，以‘:‘分隔PATH=$PATH:/data/mysql3308/bin# 生效. /etc/profile

3.3 Power-on self-booting

Turn on auto-start mysqld service is not used in a production environment. The server generally does not restart, the fault is mostly the hardware side, using HA to provide continuous service

3.3.1 Creating a system service service

Use $basedir/support-files/mysql.server script to create/etc/init.d/mysqld Quick Start

#1、创建服务cp $basedir/support-files/mysql.server /etc/init.d/mysqld        修改脚本chkconfig mysql on#2、修改修改/etc/init.d/mysqld 脚本内容#266行  numactl --interleave=all /ssd_data/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf  &-------------------------------------------------#3、将mysqld服务开机启动chkcofig mysqld on     #开机启动chkconfig --list |grep mysqld    #查看启动项

3.3.2 Start command Join Rc.local

Put the Mysqld_safe start command in/etc/rc.local

cat /etc/rc.local#追加内容：numactl --interleave=all /ssd_data/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf  &

3.4 Alias Settings

Create alias alias command and write to system configuration file

vim  /etc/profile# 追加一下内容alias mysql3306=‘mysql -uroot -p123123 -h127.0.0.1 -P3306‘alias mysql3306shutdown=‘mysql -uroot -p123123 -h127.0.0.1 -P3306 shutdown‘alias mysqlstartup=‘/etc/init.d/mysql start‘alias vimmycnf=‘vim /export/servers/mysql/etc/my.cnf‘

MySQL binary installation