Shulou(Shulou.com)05/31 Report--

This article shows you how to build MYSQL8 and CENTOS7.6, the content is concise and easy to understand, can definitely brighten your eyes, through the detailed introduction of this article, I hope you can get something.

I. Environmental preparation

A total of 3 IP addresses: 2 physical machines IP,1 VIP

MASTER:10.18.96.15

SLAVE: 10.18.96.16

VIP: 10.18.96.17

Operating system: CENTOS7.6

Database: MYSQL8.0.20

My hardware resource is 8 cores and 16G.

Download address of MYSQL8:

Https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-boost-8.0.20.tar.gz

Depending on the download address:

Https://mirrors.ustc.edu.cn/gnu/gmp/gmp-6.1.2.tar.xz

Https://mirrors.ustc.edu.cn/gnu/mpfr/mpfr-4.0.2.tar.gz

Https://mirrors.ustc.edu.cn/gnu/mpc/mpc-1.1.0.tar.gz

Https://mirrors.ustc.edu.cn/gnu/gcc/gcc-9.2.0/gcc-9.2.0.tar.gz

Https://github.com/Kitware/CMake/releases/download/v3.15.2/cmake-3.15.2.tar.gz

(CMAKE can not be downloaded if CMAKE3 is installed using the YUM source.)

Second, dependent installation

Before installing MYSQL8, you must be prepared first, otherwise all kinds of errors will be reported and cannot be installed.

1. GCC compilation and installation

Yum-y install epel-release

Yum-y group install "Development Tools"

Mysql dependence

Yum-y install ncurses-devel openssl openssl-devel bison

Yum install cmake3

2. Install gcc source code compilation dependency

# # compile gmp- > mpfr- > mpc first

Cd / usr/local/src/

Compilation: gmp-6.1.2

Tar-xvf / opt/gmp-6.1.2.tar.xz

Cd gmp-6.1.2

. / configure-- prefix=/usr/local/gmp-6.1.2

Make-j $(nproc)

Make install

Cd.. /

Compilation: mpfr-4.0.2

Tar-xvf / opt/mpfr-4.0.2.tar.gz

Cd mpfr-4.0.2

. / configure-prefix=/usr/local/mpfr-4.0.2-with-gmp=/usr/local/gmp-6.1.2

Make-j $(nproc)

Make install

Cd.. /

Compilation: mpc-1.1.0

Tar-xvf / opt/mpc-1.1.0.tar.gz

Cd mpc-1.1.0

. / configure-- prefix=/usr/local/mpc-1.1.0-- with-mpfr=/usr/local/mpfr-4.0.2-- with-gmp=/usr/local/gmp-6.1.2

Make-j $(nproc)

Make install

# add mpfr lib to ld.so.conf or gcc compiling error

Echo / usr/local/mpfr-4.0.2/lib > > / etc/ld.so.conf

Ldconfig

Compile and install gcc

Cd / usr/local/src/

Tar-xvf / opt/gcc-9.2.0.tar.gz

Cd gcc-9.2.0

. / configure-- prefix=/usr/local/gcc-9.2.0\

-enable-threads=posix\

-disable-checking\

-disable-multilib\

-enable-languages=c,c++\

-- with-gmp=/usr/local/gmp-6.1.2\

-- with-mpfr=/usr/local/mpfr-4.0.2\

-- with-mpc=/usr/local/mpc-1.1.0\

-- with-tune=generic\

-- with-arch_32=x86-64

Make-j $(nproc)

Make install-j $(nproc)

(as a reminder, it took me more than an hour to finish running when I compiled GCC with make.)

# # backing up old gcc executable files

Mv / usr/bin/gcc / usr/bin/gcc.old

Mv / usr/bin/g++ / usr/bin/g++.old

Mv / usr/bin/c++ / usr/bin/c++.old

Mv / usr/bin/cpp / usr/bin/cpp.old

Mv / usr/bin/gcov / usr/bin/gcov.old

# # create the latest gcc execution file softchain

Ln-sf / usr/local/gcc-9.2.0/bin/* / usr/bin/

# # delete the .py file in the lib64 directory or ldconfig will report an error

Cp / usr/local/gcc-9.2.0/lib64/libstdc++.so.6.0.27-gdb.py / opt/

Rm-rf / usr/local/gcc-9.2.0/lib64/libstdc++.so.6.0.27-gdb.py

Echo / usr/local/gcc-9.2.0/lib64 > > / etc/ld.so.conf

Ldconfig

# # copying libstdc++.so.6.0.27 / lib64/

Cp / usr/local/gcc-9.2.0/lib64/libstdc++.so.6.0.27 / lib64/

# create a soft chain libstdc++.so.6

Cd / lib64

Ln-sf libstdc++.so.6.0.27 libstdc++.so.6

# # check whether the latest version is available

Strings / usr/lib64/libstdc++.so.6 | grep GLIBCXX

Compile and install cmake cd / usr/local/src/

Tar-xvf / opt/cmake-3.15.2.tar.gz

Cd / opt/cmake-3.15.2

. / configure-- prefix=/usr/local/cmake-3.15.2

Gmake-j $(nproc)

Gmake install-j $(nproc)

# create cmake soft chain

Ln-sf / usr/local/cmake-3.15.2/bin/cmake / bin/cmake3

III. MYSQL8 installation

Cd / opt/

Tar xvf mysql-boost-8.0.20.tar.gz

Cd / opt/mysql-8.0.20/

1 、 cmake

Cmake3\

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql/\

-DEFAULT_CHARSET=utf8mb4\

-DDEFAULT_COLLATION=utf8mb4_general_ci\

-DEXTRA_CHARSETS=all\

-DENABLED_LOCAL_INFILE=ON\

-DWITH_INNODB_MEMCACHED=ON\

-DWITH_INNOBASE_STORAGE_ENGINE=1\

-DWITH_FEDERATED_STORAGE_ENGINE=1\

-DWITH_BLACKHOLE_STORAGE_ENGINE=1\

-DWITH_ARCHIVE_STORAGE_ENGINE=1\

-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1\

-DWITH_PERFSCHEMA_STORAGE_ENGINE=1\

-DCOMPILATION_COMMENT= "nancy edition"\

-DDOWNLOAD_BOOST=1\

-DWITH_BOOST=/data/mysql/tmp\

-DMYSQL_UNIX_ADDR=/data/mysql/run/mysql.sock\

-DMYSQL_TCP_PORT=3306\

-DSYSCONFDIR=/data/mysql\

-DWITH_READLINE=1\

-DFORCE_INSOURCE_BUILD=1\

-DWITH_SSL=system\

-DWITH_ZLIB=system\

-DCMAKE_CXX_COMPILER=/usr/local/gcc-9.2.0/bin/g++\

-DCMAKE_C_COMPILER=/usr/local/gcc-9.2.0/bin/gcc

Note: if your machine is not connected to the Internet, you need to download a package boost_1_70_0.tar.gz and put it in the / data/mysql/tmp directory

Download address:

Https://dl.bintray.com/boostorg/release/1.70.0/source/boost_1_70_0.tar.gz

2. Compile:

Gmake-j $(nproc)

3. Install:

Gmake install-j $(nproc)

4. Create MYSQL users and related directories and authorizations

Groupadd mysql

Useradd mysql-g mysql-M-s / sbin/nologin

Mkdir / data/mysql/data/

Chown mysql.mysql / data/mysql/data/

Mkdir / data/mysql/run

Chown mysql.mysql / data/mysql/run

Mkdir / data/mysql/binlog/

Chown mysql.mysql / data/mysql/binlog/

Chown-R mysql.mysql / data/mysql/

5. Configuration file (main library)

Vi / etc/my.cnf

[mysqld]

Port = 3306

User = mysql

Socket = / data/mysql/run/mysql.sock

Pid_file = / data/mysql/mysqld.pid

Basedir = / usr/local/mysql

Datadir = / data/mysql/data

Tmpdir = / data/mysql/tmp

Open_files_limit = 65535

Server_id = 1

# 0 indicates case sensitivity by default, 1 indicates case insensitivity and is stored in lowercase. Developers are required to be case sensitive.

Lower_case_table_names = 0

Character_set_server = utf8mb4

# maximum number of connections allowed

Max_connections = 6000

# the number of failed connections allowed. This is to prevent someone from trying to attack the database system from the host.

Max_connect_errors = 10

# the default is 8 hours. Interactive waiting time and non-interactive waiting time

# 30000s is recommended. The values of the two parameters must be the same and revised at the same time.

Interactive_timeout = 500,

Wait_timeout = 500,

# how many requests can be stored in the stack in a short period of time

Back_log=1024

Default_storage_engine = InnoDB

# log_slave_updates = 1

# query_cache_size=1M

# * Logs related settings *

Log_bin = / data/mysql/binlog/mysql-bin

Log-bin-index=/data/mysql/binlog/mysql-bin.index

# binlog log expiration time setting (in seconds), 604800 = 7 days

Binlog_expire_logs_seconds=604800

Log_bin_trust_function_creators=1

# at least 400, which has a great impact on memory footprint

# Service mode: set from 1400 to 400m, memory reduced from 324m to 227m

# table_definition_cache=400

# optimize parameters, refresh disk and log by transaction [0: fastest mode, 1: safe mode, 2: safer than 0 but slower than 0]

Innodb_flush_log_at_trx_commit=2

# commit a transaction once and swipe it once, which can be n

Sync_binlog=1

# use "mysql_native_password" plug-in authentication by default

Default_authentication_plugin=mysql_native_password

# when dirty pages account for the proportion of innodb_buffer_pool_size, trigger brushing dirty pages to disk

# 25% 50%

Innodb_max_dirty_pages_pct=30

# maximum IO performance metrics for background processes

# default 20000, if SSD, adjust to 5000mm 20000

Innodb_io_capacity=200

# default is 10m. Prevent the database from being affected by high concurrency

Innodb_data_file_path=ibdata1:1024M:autoextend

# default 2, unit s. Slow query time. 0.3-0.5 is recommended.

Long_query_time=0.3

# 8.0 default row. Record format to make data safe and reliable

Binlog_format=row

# the default is 8 hours. Interactive waiting time and non-interactive waiting time

# 30000s is recommended. The values of the two parameters must be the same and modified at the same time.

Interactive_timeout=500

Wait_timeout=500

# Log size

Innodb_log_file_size=256M

# Log cache size

Innodb_log_buffer_size=12M

# confirm whether to use the compressed storage feature here

Innodb_file_per_table=1

# parameters that determine the degree of compression. If you set a larger setting, there is more compression and more CPU resources are consumed.

# conversely, if you set a smaller value, then the CPU usage is less. The default value is 6, which can be set to 0-zero.

Innodb_compression_level=6

# specify the maximum proportion of free space that can be used as free space on each compressed page

# this parameter is only applied when innodb_compression_failure_threshold_pct is set to non-zero, and the compression failure rate passes the breakpoint.

# the default value is 50, which can be set from 0 to 75

Innodb_compression_pad_pct_max=50

[mysqld_safe]

Log-error=/data/mysql/data/csp-serverdb-1.err

[mysql]

# set mysql client default character set

Default-character-set=utf8mb4

[client]

# set the default port for mysql clients to connect to the server

Port=3306

Default-character-set=utf8mb4

6. The configuration file (slave library) is actually the same as the master library, so change it with server_id.

7. Copy startup files

Cp / opt/mysql-8.0.20/support-files/mysql.server / etc/init.d/mysqld

Chmod 700 / etc/init.d/mysqld

8. Add mysql to the environment variable

Echo export PATH=$PATH:/usr/local/mysql/bin > > / etc/profiles

Source / etc/profile

Echo / usr/local/mysql/lib > > / etc/ld.so.conf

Ldconfig

Echo PATH=$PATH:/usr/local/mysql/bin / .bash_profile

Source / .bash_profile

9. Initialize

/ usr/local/mysql/bin/mysqld-defaults-file=/etc/my.cnf-initialize-user=mysql

After initialization, the root user login temporary random password will be printed on the screen.

10. Start

Service mysqld start

Or

/ etc/init.d/mysqld start

Or

/ usr/local/mysql/bin/mysqld_safe-defaults-file=/etc/my.cnf-user=mysql &

Do not remember the initialization password, you can look it up in the log

/ data/mysql/data/serverdb-1.err

11. Change the password:

Alter user 'root'@'localhost' identified by''

Flush privileges

(use mysql-uroot-hlocalhost to log in locally without secret for writing keepalived scripts)

Create user 'root'@'%' identified by' 123456 'with GRANT OPTION

Grant all privileges on *. * to 'root'@'%'; (create a similar pattern for other ordinary users. You can also add or decrease permissions in navicat)

ALTER user 'root'@'%' IDENTIFIED BY' 123456; (this is a modification)

Flush privileges

IV. MYSQL master-slave configuration

1. Main library

Clear the firewall.

Iptables-F

Setenforce 0

Create user' repl_user'@'%' identified by '123456'

Flush privileges

Grant REPLICATION SLAVE on *. * to 'repl_user'@'%'

Flush privileges

Master_log_file and master_log_pos are obtained by using the command on the main library:

Show master status\ G

2. From the library

Clear the firewall.

Iptables-F

Setenforce 0

Execute on the slave library:

Change master to

Master_host='10.18.96.15'

Master_port=3306

Master_user='repl_user'

Master_password='123456'

Master_log_file='mysql-bin.000001'

Master_log_pos=156

Start slave

Show slave status\ G

5. Keepalived building

1. Install keepalived

Cd / opt/

Tar zxvf / opt/keepalived-1.4.2.tar.gz

Cd / opt/keepalived-1.4.2

. / configure

If you encounter the following error

!!! OpenSSL is not properly installed on your system. !!!

!!! Can not include OpenSSL headers files.

Solution:

Yum-y install openssl-devel

2. Compile:

Make

Make install

3. Copy the configuration file

Mkdir / etc/keepalived

Cp / usr/local/etc/keepalived/keepalived.conf / etc/keepalived/

Cp / usr/local/sbin/keepalived / usr/sbin/

4. Configuration file

Vi / etc/keepalived/keepalived.conf

Global_defs {

Router_id MySQL-HA2

}

Vrrp_script check_run {

Script "/ etc/keepalived/mysql_check.sh"

Interval 60

}

Vrrp_sync_group VG2 {

Group {

VI_1

}

}

Vrrp_instance VI_2 {

State BACKUP

Interface eth0

Virtual_router_id 52

Priority 100

Advert_int 1

Nopreempt

Authentication {

Auth_type PASS

Auth_pass 1234

}

Track_script {

Check_run

}

Notify_master / etc/keepalived/master.sh

Notify_stop / etc/keepalived/stop.sh

Virtual_ipaddress {

10.18.96.17

}

}

From the same as the library, which is priority 90, change this to 90.

5. Automatically switch scripts with the same configuration of 2 nodes

(1)

Vi / etc/keepalived/mysql_check.sh

#! / bin/bash

. ~ / .bash_profile

Count=1

While true

Do

Mysql-uroot-hlocalhost-e "show status;" > / dev/null 2 > & 1

I don't know.

Ps aux | grep mysqld | grep-v grep > / dev/null 2 > & 1

Jackpot?

If [$I = 0] & & [$j = 0]

Then

Exit 0

Else

If [$I = 1] & & [$j = 0]

Then

Exit 0

Else

If [$count-gt 5]

Then

Break

Fi

Let count++

Continue

Fi

Fi

Done

/ usr/bin/systemctl stop keepalived

(2)

Vi / etc/keepalived/master.sh

#! / bin/bash

. / home/mysql/.bashrc

Master_Log_File=$ (mysql-uroot-hlocalhost-e "show slave status\ G" | grep-w Master_Log_File | awk-F ":"'{print $2}')

Relay_Master_Log_File=$ (mysql-uroot-hlocalhost-e "show slave status\ G" | grep-w Relay_Master_Log_File | awk-F ":"'{print $2}')

Read_Master_Log_Pos=$ (mysql-uroot-hlocalhost-e "show slave status\ G" | grep-w Read_Master_Log_Pos | awk-F ":"'{print $2}')

Exec_Master_Log_Pos=$ (mysql-uroot-hlocalhost-e "show slave status\ G" | grep-w Exec_Master_Log_Pos | awk-F ":"'{print $2}')

I, 1

While true

Do

If [$Master_Log_File = $Relay_Master_Log_File] & & [$Read_Master_Log_Pos-eq $Exec_Master_Log_Pos]

Then

Echo "ok"

Break

Else

Sleep 1

If [$I-gt 60]

Then

Break

Fi

Continue

Let iTunes +

Fi

Done

Mysql-uroot-hlocalhost-e "stop slave;"

Mysql-uroot-hlocalhost-e "reset slave all;"

Mysql-uroot-hlocalhost-e "reset master;"

Mysql-uroot-hlocalhost-e "show master status;" > / tmp/master_status_$ (date "+% y%m%d-%H%M"). Txt

(3)

Vi / etc/keepalived/stop.sh

#! / bin/bash

. ~ / .bash_profile

Masking File1percent $(mysql-uroot-hlocalhost-e "show master status\ G" | awk-filtering:'/ File/ {print $2}')

Masking Position1percent $(mysql-uroot-hlocalhost-e "show master status\ G" | awk-filtering:'/ Position/ {print $2}')

Sleep 1

Masking File2percent $(mysql-uroot-hlocalhost-e "show master status\ G" | awk-filtering:'/ File/ {print $2}')

Masking Position2percent $(mysql-uroot-hlocalhost-e "show master status\ G" | awk-filtering:'/ Position/ {print $2}')

I, 1

While true

Do

If [$M_File1 = $M_File2] & & [$M_Position1-eq $M_Position2]

Then

Echo "ok"

Break

Else

Sleep 1

If [$I-gt 60]

Then

Break

Fi

Continue

Let iTunes +

Fi

Done

Then confirm that the MYSQL master and slave have been configured and started.

Start keepalived

Systemctl start keepalived

The following is the stop command.

Systemctl stop keepalived

In this way, the deployment is complete, other tests (automatic active / standby switching, VIP floating), just do it yourself.

Note that if there are multiple sets of keepalived in the production library, just change the grouping such as virtual_router_id,group,instance.

If it is a production library, pay attention to the root user authorization

Create user 'root'@'%' identified by' 123456 'with GRANT OPTION

Grant all privileges on *. * to 'root'@'%'

Flush privileges

Another problem is that after I installed MYSQL, I found that all 16 gigabytes of memory had been eaten, so I refreshed the cache manually.

Echo 1 > / proc/sys/vm/drop_caches

Then observe for a period of time, the free memory is no longer eaten.

The above content is how to build MYSQL8 and CENTOS7.6. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.