Mysql8 master/slave installation configuration in centos7

Download version of centos7

Mirroring version requires a minor version of centos7; Download address suggested from Ali cloud mirror stationDownload address Currently, official images are classified into the following types:

  • Centos-7-x86_64-dvd-2009.iso standard installation version, a BAI like download this can be.
  • Centos-7-x86_64-netinstall-2009. iso Network installation image DU.
  • Centos-7-x86_64-everything-2009. iso complements the software of the full version of the installation disk to integrate all software
  • Centos-7-x86_64-minimal -2009. Iso Compact version, including core components.

Here I use centos7 operating system with centos-7-x86_64-DVD-2009. iso

Mysql 8.0.20 download

Mysql8.0.20; You are advised to download it from the official mirror: mysql8.0.20.

Mysql8.0.20 binary installation

The general installation steps are as follows:

  1. Operating system configuration and other related Settings
  2. Installing dependency packages
  3. Create a user
  4. Modify configuration files, create data directories and log directories, and authorize them
  5. Run the install command to start the database
  6. Configure environment variables, services, and so on (as needed)

Operating system configuration and Settings

View CPU, memory, SSL version, hard disk size, turn off firewall, and Selinux

Check the number of physical cpus

[root@localhost ~]# cat /proc/cpuinfo| grep "physical id"| sort| uniq| wc -l
1
Copy the code

Check the number of logical cpus

[root@localhost ~]# cat /proc/cpuinfo| grep "processor"| wc -l
1
Copy the code

Viewing CPU Information (Model)

[root@localhost ~]# cat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c1 Intel(R) Core(TM) I5-8265 U CPU @ 1.60GHzCopy the code

View memory:

[root@localhost ~]# cat /proc/meminfo
MemTotal:        1863076 kB
MemFree:           81492 kB
MemAvailable:      48092 kB
......
Copy the code

Viewing the SSL Version

[root@localhost ~]# openssl versionOpenSSL 1.0.2K - FIPS 26 Jan 2017Copy the code

Check the disk size. Try to make the/root directory occupy 80% of all disk directories. The home directory is 50-100G

[root@localhost ~]# df -lhFilesystem Size Used Avail Use% Mounted on devtmpfs 898M 0 898M 0% /dev TMPFS 910M 0 910M 0% /dev/shm TMPFS 910M 9.6m 901M 2% /run TMPFS 910M 0 910M 0% /sys/fs/cgroup /dev/mapper/centos-root 17G 5.4g 12G 32% / / /dev/sda1 1014M 150M 865M 15% /boot tmpfs 182M 0 182M 0% /run/user/0Copy the code

Disabling the Firewall

systemctl stop firewalld.service
systemctl disable firewalld.service
Copy the code

Change selinux =enforcing to selinux =disabled

[root@localhost ~]# vi /etc/selinux/config

# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of three values:
# targeted - Targeted processes are protected,
# minimum - Modification of targeted policy. Only selected processes are protected.
# mls - Multi Level Security protection.
SELINUXTYPE=targeted
Copy the code
It takes effect without restarting the machine
setenforce 0
Copy the code

Modify file limits

[root@localhost ~]# vi /etc/security/limits.conf
hard nofile 65535
soft nofile 65535
ulimit -n 65535
Copy the code
[root@localhost ~]# vi /etc/sysctl.conf

# sysctl settings are defined through files in
# /usr/lib/sysctl.d/, /run/sysctl.d/, and /etc/sysctl.d/.
#
# Vendors settings live in /usr/lib/sysctl.d/.
# To override a whole file, create a new file with the same in
# /etc/sysctl.d/ and put new settings there. To override
# only specific settings, add a file with a lexically later
# name in /etc/sysctl.d/ and put new settings there.
#
# For more information, see sysctl.conf(5) and sysctl.d(5).

fs.aio-max-nr = 1048576
fs.file-max = 6553600
net.ipv6.conf.all.disable_ipv6 = 1
net.ipv6.conf.default.disable_ipv6 = 1
net.ipv4.icmp_echo_ignore_broadcasts = 1
net.ipv4.icmp_ignore_bogus_error_responses = 1
net.ipv4.conf.all.accept_source_route = 0
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 68719476736
kernel.shmall = 4294967296
net.ipv4.tcp_max_tw_buckets = 6000
net.ipv4.tcp_sack = 1
net.ipv4.tcp_window_scaling = 1
net.ipv4.tcp_rmem = 4096 87380 4194304
net.ipv4.tcp_wmem = 4096 16384 4194304
net.core.wmem_default = 8388608
net.core.rmem_default = 8388608
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.core.netdev_max_backlog = 262144
net.ipv4.tcp_max_orphans = 3276800
net.ipv4.tcp_max_syn_backlog = 262144
net.ipv4.tcp_timestamps = 0
net.ipv4.tcp_synack_retries = 1
net.ipv4.tcp_syn_retries = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_mem = 94500000 915000000 927000000
net.ipv4.tcp_fin_timeout = 1
net.ipv4.tcp_keepalive_time = 30
net.ipv4.ip_local_port_range = 1024 65000


# take effectSysctl -pCopy the code

Install the mysql component package

yum install -y epel-release wget
yum install -y glibc gcc gcc-c++ openssl-devel autoconf automake cmake bison make ncurses-devel numactl numactl-devel
yum install -y libtool-ltdl-devel** zlib* libxml* fiex**
yum install -y libaio libaio-devel libmcrypt libmcrypt-devel mcrypt mhash**
Copy the code

Create users and user folders

groupadd mysql
useradd -r -g mysql mysql
Copy the code

Upload the downloaded installation package to the execution directory of the server, or use wGET to download the software package

https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.20-linux-glibc2.12-x86_64.tar.xz * *] (HTTP: / / https://cdn.mysql.com/archives/my SQL/mysql 8.0-8.0.20 - Linux - glibc2.12 - x86_64. Tar. XzCopy the code
# decompressionThe tar XVF mysql - 8.0.20 - Linux - glibc2.12 - x86_64. Tar. XzMove to /user/localMysql - 8.0.20 - Linux - mv glibc2.12 - x86_64 / usr /local/mysql
Add environment variables
cat >>/etc/profile << EOF
export PATH=$PATH:/usr/local/mysql/bin
EOF
# Environment variables in effect
source /etc/profile
Create a folder to install mysql
mkdir -p /data/mysql/tmp
mkdir -p /data/mysql/data
mkdir -p /data/mysql/etc
mkdir -p /data/mysql/logs
Grant mysql permission to the mysql folder
chown -R mysql:mysql /data/mysql
Copy the code

Modifying a Configuration File

[mysqld]
########basic settings########
server-id = 112             You are advised to use the same IP address as the server IP address. For example, write 66 here
port = 3306
user = mysql
character_set_server=utf8mb4
skip_name_resolve = 1
max_connections = 800
max_connect_errors = 1000
datadir = /data/mysql/data      # Modify according to the actual situation, it is recommended to store separately from the program
transaction_isolation = READ-COMMITTED
explicit_defaults_for_timestamp = 1
join_buffer_size = 512M
tmp_table_size = 512M
tmpdir = /data/mysql/tmp
pid-file = /data/mysql/tmp/mysqld.pi
socket = /tmp/mysql.sock
max_allowed_packet = 1024M
sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO"
interactive_timeout = 1800
wait_timeout = 1800
read_buffer_size = 16M
read_rnd_buffer_size = 32M
sort_buffer_size = 32M
lower_case_table_names=1      # case insensitive, Linux exclusive Windows server directly commented out
default_time_zone='+8:00'
########log settings########
log_error =/data/mysql/logs/mysqld.log            The recommended and datadir locations are on different physical disks
slow_query_log = 1
slow_query_log_file = /data/mysql/logs/slow.log   The recommended and datadir locations are on different physical disks
# log_queries_NOT_USING_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1
# log_throttLE_QUERies_NOT_USING_INDEXES = 10 # Set the number of unused index statements recorded to the log per minute. After this number, only the number of statements and the total time spent are recorded
binlog_expire_logs_seconds=10800                 # if the disk where log_bin resides has sufficient space; The recommended value is 25200
long_query_time = 2
min_examined_row_limit = 100


########replication settings########
master_info_repository = TABLE
relay_log_info_repository = TABLE
log_bin = /data/mysql/logs/mysql_binlog   The recommended and datadir locations are on different physical disks
sync_binlog = 1
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
binlog_format = row
relay_log = /data/mysql/logs/relay.log    The recommended and datadir locations are on different physical disks
relay_log_recovery = 1
binlog_gtid_simple_recovery = 1
slave_skip_errors = ddl_exist_errors

########innodb settings########
innodb_page_size = 16384  Compatible with older systems
innodb_buffer_pool_size = 14G    Change to 80% of the available memory of the server
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_lru_scan_depth = 2000
innodb_lock_wait_timeout = 5
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_method = O_DIRECT
innodb_log_group_home_dir = /data/mysql/logs/   The recommended and datadir locations are on different physical disks
innodb_flush_neighbors = 1
innodb_log_file_size = 512M               # Modify according to the actual situation
innodb_log_buffer_size = 16777216
innodb_purge_threads = 4
innodb_thread_concurrency = 64
innodb_print_all_deadlocks = 1
innodb_strict_mode = 1
innodb_sort_buffer_size = 67108864
innodb_read_io_threads=24                For example, if you have 32 cores, set it to 19
innodb_write_io_threads=24               For example, if you have 32 cores, set it to 19

########semi sync replication settings########
plugin_dir=/usr/local/mysql/lib/plugin      # Modify according to the actual situation
plugin_load = "rpl_semi_sync_master=semisync_master.so; rpl_semi_sync_slave=semisync_slave.so"
[Mysqld - 8.0 -]
innodb_buffer_pool_dump_pct = 40
innodb_page_cleaners = 4
innodb_undo_log_truncate = 1
innodb_purge_rseg_truncate_frequency = 128
binlog_gtid_simple_recovery=1
log_timestamps=system
transaction_write_set_extraction=MURMUR32
default_authentication_plugin = 'mysql_native_password'
log_bin_trust_function_creators=1
Copy the code

Install and start the database

Specify the configuration file for this instance to install

You are advised to run commands in full path

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

See the log

vi /data/mysql/logs/mysqld.log
Copy the code

You can view the temporary password and check whether errors are generated :A temporary password is generated for root@localhost: password

[root @ localhost ~] # vi/data/mysql/logs/mysqld. Log: the 2021-11-22 T11 36:07. 179120 + 08:00 0 [System] [MY - 013169] (Server) / usr/local/mysql/bin/mysqld (mysqld 8.0.20) initializing of server in progress as the process of 2503 2021-11-22T11:36:07.216846+08:00 1 [System] [my-013576] [InnoDB] Initialization has started. 2021-11-22T11:36:11.364494+08:00 1 [System] [my-013577] [InnoDB] InnoDB initialization has ended. 2021-11-22T11:36:11.697981+08:00 0 [Warning] [My-013501] [Server] Ignoring -- Plugin-load [_add] List as the Server is Running with --initialize(-insecure). 2021-11-22T11:36:12.605450+08:00 6 [Note] [my-010454] [Server] A temporary password is generated for root@localhost: VZico+qE8l0MCopy the code

Start the database

  • Method 1: Start the database in service mode you are advised to start the database in this mode.
cp /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld
service mysqld start
Copy the code
  • Start the database and run it in the background (enter multiple times when executing the following command, note only for small white)
/usr/local/mysql/bin/mysqld_safe  --defaults-file=/etc/my.cnf &
Copy the code

Viewing database Processes

[root@localhost ~]# ps -ef | grep mysql
root       1901      1  0 15:59 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql/data --pid-file=/data/mysql/tmp/mysqld.pi
mysql      2800   1901  1 15:59 pts/0    00:00:56 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/logs/mysqld.log --pid-file=/data/mysql/tmp/mysqld.pi --socket=/tmp/mysql.sock --port=3306
root       5569   1816  0 16:54 pts/0    00:00:00 grep --color=auto mysql
Copy the code

Change the default password of mysql

Log in to the database to change the root password. You must change the random root password after the installation. Otherwise, you cannot perform other operations

[root@localhost ~]# / usr/local/mysql/bin/mysql -u root -p 'initial generated password' - S/TMP/mysql. The sock
mysql> ALTER USER 'root'@The '%' IDENTIFIED BY 'Password to change';
Copy the code

Mysql8 primary/secondary configuration

  1. 1 virtual replication with mysql installed (note to change IP address)
  2. Modify the master and slave my.cnf files
It is recommended that the IP address of the server be the same as that of the server
server-id = 112       
Copy the code
  1. Auto. CNF (default mysql data directory)
mv /data/mysql/data/auto.cnf /data/mysql/data/auto.cnf_bak
Copy the code
  1. Restarting the Master Service
service mysqld restart
Copy the code
  1. Create a login user repL for the slave node
[root@localhost ~]# mysql -u root -pMysql > CREATE USER'repl'@The '%' IDENTIFIED WITH mysql_native_password BY 'Ron_master_1'; Query OK, 0 rows affected (0.01sec) mysql> GRANT REPLICATION SLAVE ON *.* TO'repl'@The '%';
Query OK, 0 rows affected (0.00 sec)
Copy the code

Note here that % in ‘repl’@’%’ does not write IP

  1. Check the status of the master node
[root@localhost ~]# mysql -u root -p mysql> show master status; +---------------------+----------+--------------+------------------+---------------------------------------------------- ---------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +---------------------+----------+--------------+------------------+---------------------------------------------------- ---------------------------------+ | mysql_binlog.000010 | 1304 | | | 56b7da5a-4b45-11ec-ae40-000c2964986e:1-14, fef0443e-4b5d-11ec-bf78-000c2964986e:1-5 | +---------------------+----------+--------------+------------------+---------------------------------------------------- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 row in the set (0.00 SEC)Copy the code

Note that File and Position are used here.

  1. Log in to the slave service and configure the information about the master node
[root@localhost ~]# mysql -u root -pMysql > CHANGE MASTER TO -> MASTER_HOST='192.168.182.110',
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='Ron_master_1',
    -> MASTER_LOG_FILE='mysql_binlog',
    -> MASTER_LOG_POS=856;
Copy the code

Note:

CHANGE MASTER TO # MASTER MASTER_HOST='192.168.182.110 ', # MASTER_PASSWORD='Ron_master_1', # binlog log File corresponds to File MASTER_LOG_FILE='mysql_binlog', Position MASTER_LOG_POS=856;Copy the code
  1. Check the status of the slave node
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168182.110.
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_binlog000010.
          Read_Master_Log_Pos: 196
               Relay_Log_File: relay000002.
                Relay_Log_Pos: 327
        Relay_Master_Log_File: mysql_binlog000010.
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table:
Copy the code

Slave_IO_State: Waiting for master to send event Slave_IO_Running: Yes Slave_SQL_Running: Yes saw that the configuration was successful, so you can test it using a connection tool like Nacicat.

record

  1. Mysql Displays the current user list
SELECT User, Host FROM mysql.user;
Copy the code

Q&A

  1. Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

This error message. That is, the master and slave schemas use the same UUID. First check whether the server_id system variable is the same:

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 110   |
+---------------+-------+
1 row in set (0.00 sec)
Copy the code
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 111   |
+---------------+-------+
1 row in set (0.00 sec)
Copy the code

CNF (find / -name auto. CNF) {find / -name auto.

[root@localhost logs]# vim /data/mysql/data/auto.cnf

[auto]
server-uuid=56b7da5a-4b45-11ec-ae40-000c2964986e

Copy the code
[root@localhost logs]# vim /data/mysql/data/auto.cnf

[auto]
server-uuid=56b7da5a-4b45-11ec-ae40-000c2964986e

Copy the code

You can see that the server-UUID on the two meters is the same. The reason is that the VM has been cloned and only the server_ID cannot be changed.

Solution: Rename the master auto. CNF file and restart mysql

[root@localhost ~]# mv /data/mysql/data/auto.cnf /data/mysql/data/auto.cnf.bak
[root@localhost ~]# service mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL.... SUCCESS! 
[root@localhost ~]# vim /data/mysql/data/auto.cnf 

[auto]
server-uuid=fef0443e-4b5d-11ec-bf78-000c2964986e
Copy the code

After a restart, you can see that the server-UUID has been regenerated.

  1. Got FATAL error 1236 from master when reading data from binary log: ‘Could not find first log…

This is because of the binlog position index problem.

1. Open the primary server and log in to mysql. 2. The main server will create a new binlog file. 3. Run the show master slave \G command on the master service. # record a and Position 4. 5.stop slave; 6.change master to master_log_file='File',master_log_pos=Position; # file and pos are displayed on the master server. 7.start slave; 8.show slave status \G; Check the slave table statusCopy the code