1、設定SELinux
在/etc/sysconfig/selinux檔案，修改SELINUX=disabled。
2、設定防火牆，或者關閉防火牆，安裝完成後再開啟。
wsrep(Write-Set Replication)
#mkdir -p /app/mysql/3306
#mkdir -p /app/mysql/binlog
1、安裝RPM包
# rpm -e mariadb-libs-1:5.5.52-1.el7.x86_64 --nodeps
# rpm -e mariadb-libs-5.5.44-2.el7.centos.x86_64 --nodeps
# rpm -ivh mysql-wsrep-common-5.7-5.7.21-25.14.el7.x86_64.rpm
warning: mysql-wsrep-common-5.7-5.7.21-25.14.el7.x86_64.rpm: Header V4 RSA/SHA512 Signature, key ID bc19ddba: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-wsrep-common-5.7-5.7.21-25.################################# [100%]
[root@mysql01 soft]# rpm -ivh mysql-wsrep-libs-5.7-5.7.21-25.14.el7.x86_64.rpm
warning: mysql-wsrep-libs-5.7-5.7.21-25.14.el7.x86_64.rpm: Header V4 RSA/SHA512 Signature, key ID bc19ddba: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-wsrep-libs-5.7-5.7.21-25.14################################# [100%]
[root@mysql01 soft]# rpm -ivh mysql-wsrep-client-5.7-5.7.21-25.14.el7.x86_64.rpm
warning: mysql-wsrep-client-5.7-5.7.21-25.14.el7.x86_64.rpm: Header V4 RSA/SHA512 Signature, key ID bc19ddba: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-wsrep-client-5.7-5.7.21-25.################################# [100%]
# rpm -ivh mysql-wsrep-server-5.7-5.7.21-25.14.el7.x86_64.rpm
warning: mysql-wsrep-server-5.7-5.7.21-25.14.el7.x86_64.rpm: Header V4 RSA/SHA512 Signature, key ID bc19ddba: NOKEY
error: Failed dependencies:
        lsof is needed by mysql-wsrep-server-5.7-5.7.21-25.14.el7.x86_64
        socat is needed by mysql-wsrep-server-5.7-5.7.21-25.14.el7.x86_64
# yum install lsof -y
# yum install socat -y
# rpm -ivh mysql-wsrep-server-5.7-5.7.21-25.14.el7.x86_64.rpm
warning: mysql-wsrep-server-5.7-5.7.21-25.14.el7.x86_64.rpm: Header V4 RSA/SHA512 Signature, key ID bc19ddba: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-wsrep-server-5.7-5.7.21-25.################################# [100%]
# rpm -ivh mysql-wsrep-libs-compat-5.7-5.7.21-25.14.el7.x86_64.rpm
warning: mysql-wsrep-libs-compat-5.7-5.7.21-25.14.el7.x86_64.rpm: Header V4 RSA/SHA512 Signature, key ID bc19ddba: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-wsrep-libs-compat-5.7-5.7.2################################# [100%]
# yum install rsync -y

# rpm -ivh jemalloc-3.6.0-8.el7.centos.x86_64.rpm
Preparing... ################################# [100%]
Updating / installing...
1:jemalloc-3.6.0-8.el7.centos ################################# [100%]
# ls -l /usr/lib64/libjemalloc.so.1
-rwxr-xr-x 1 root root 232808 Sep 14 2014 /usr/lib64/libjemalloc.so.1

2、建立並配置引數檔案
---建立並配置my.cnf檔案
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M

[mysqld]

# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin

# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
explicit_defaults_for_timestamp=true
log_timestamps=SYSTEM
#character_set_server = utf8
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
init_connect='set names utf8mb4'
skip_character_set_client_handshake = true
server-id=133
basedir=/usr
user=mysql
#skip-name-resolve
lower_case_table_names=1
max_connections=200
max_connect_errors=1000
event_scheduler=on
datadir=/app/mysql/3306
default-storage-engine = InnoDB
socket=/app/mysql/3306/mysql.sock
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=6G
thread_cache_size=64
max_allowed_packet=128M
#pid_file=/app/mysql/mysqld.pid
port=3306
log-error=/app/mysql/mysql.err
#add for bin-log
log-bin=ON
log-bin=/app/mysql/binlog/mysql-bin
log-bin-index=/app/mysql/binlog/mysql-bin.index
expire-logs-days=32
max-binlog-size=512M
####binlog_format=mixed
binlog-format=ROW
log-slave-updates=on
slow_query_log=on
#sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT'

[client]
socket=/app/mysql/3306/mysql.sock
#####default-character-set=utf8
default_character_set = utf8mb4

[mysql]
#default-character-set=utf8
default_character_set = utf8mb4
socket=/app/mysql/3306/mysql.sock

[mysqldump]
max_allowed_packet = 512M

[mysqld_safe]
malloc-lib=/usr/lib64/libjemalloc.so.1

# id mysql
uid=27(mysql) gid=27(mysql) groups=27(mysql)
# chmod -R 775 /app/mysql
# chown -R mysql.mysql /app/mysql

mysqld --initialize --user=mysql

cat /app/mysql/mysql.err
ftNlS1clpl>7

# mysql_ssl_rsa_setup
cd /usr/lib/systemd/system
vi mysqld.service

# cat mysqld.service
# Copyright (c) 2015, 2016, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# systemd service file for MySQL forking server
# Modified for wsrep (Galera): Recovery
#

[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql

Type=forking

#PIDFile=/var/run/mysqld/mysqld.pid
PIDFile=/app/mysql/mysqld.pid

# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0

# Execute pre and post scripts as root
PermissionsStartOnly=true

# Needed to create system tables and to check for cluster crash recovery
#ExecStartPre=/usr/bin/mysqld_pre_systemd --pre

# Start main service
#ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/app/mysql/mysqld.pid $MYSQLD_OPTS $MYSQLD_RECOVER_START
ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/app/mysql/mysqld.pid $MYSQLD_OPTS

# Needed to reset cluster crash recovery
#ExecStartPost=/usr/bin/mysqld_pre_systemd --post

# Use this to switch malloc implementation
EnvironmentFile=-/etc/sysconfig/mysql

# Sets open_files_limit
LimitNOFILE = 65535

Restart=on-failure

# Dirty hack to prevent fast restart in case of configuration problem.
# Longer-term fix will be to ensure exit status 1 for "RestartPreventExitStatus=1" below.
RestartSec=1

RestartPreventExitStatus=1

PrivateTmp=false

systemctl daemon-reload
systemctl enable mysqld.service
systemctl is-enabled mysqld

systemctl start mysqld.service

mysql -uroot -p

set password=password("mysql");

systemctl stop mysqld.service
systemctl start mysqld.service

---slave節點新增並行複製並重啟
##並行複製
slave_parallel_workers = 8
slave-parallel-type=LOGICAL_CLOCK
relay_log_recovery=ON
slave_net_timeout = 30
master_info_repository=TABLE
relay_log_info_repository=TABLE

-------以上步驟搭建master-slave節點---------------------------------------
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.21-log |
+------------+
1 row in set (0.00 sec)

symbolic-links=0

mysql> grant replication slave,reload,super,file on *.* to tyslave@'192.168.56.%' identified by 'tySlave2018';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

---克隆建立master庫的備份
mysql> flush tables with read lock;
)]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 65142 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysqldump -uroot -p --all-databases > master_to_slave.sql

mysql> unlock tables;

---slave節點操作
##並行複製

---將備份檔案傳到slave節點並匯入
)]> source /app/soft/master_to_slave.sql
)]> show slave status\G
Empty set (0.00 sec)

)]> change master to master_host='192.168.56.133',master_user='tyslave',master_password='tySlave2018',master_log_file='mysql-bin.000001', master_log_pos=65142;
Query OK, 0 rows affected, 2 warnings (0.11 sec)

)]> start slave;
Query OK, 0 rows affected (0.01 sec)

)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.133
                  Master_User: tyslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 83602
               Relay_Log_File: cnbjpgs12-relay-bin.000002
                Relay_Log_Pos: 18780
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 83602
              Relay_Log_Space: 18991
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
Replicate_Ignore_Server_Ids:
             Master_Server_Id: 91
                  Master_UUID: 4588034b-4863-11e8-9be6-fa163e652bf8
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

mysql> show slave status\G
Empty set (0.00 sec)

--------------------------------------
在slave節點change master to複製操作可以嵌入到mysqldump備份中自動執行上面的步驟，省略掉在Slave中的使用change master to複製操作。
用下面的方法對Master進行備份

mysql> flush tables with read lock;
# mysqldump -uroot -p --all-databases --master-data=1 > master_to_slave.sql
mysql> unlock tables;

--在Slave端對恢復資料庫
)]> ource /app/soft/master_to_slave.sql
--最後啟動slave
mysql> start slave;
Query OK, 0 rows affected (0.01sec)

注：master-data=1選項使mysqldump寫change master to語句，且引數為二進位制日誌檔案及其位置；可以在備份檔案master_to_slave.sql中找到相關語句
# more backup_source.sql
-- Position to startreplication or point-in-time recovery from
--
CHANGE MASTER TOMASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS=65142;

change master to master_host='10.70.161.91',master_user='tyslave',master_password='Ty#Slave27',master_log_file='mysql-bin.000001', master_log_pos=65142;

-----這裡我們注意到建立slave對master業務會有短暫離線(flush tables with read lock;)，影響業務的正常執行；如果資料庫已有slave，則搭建新的slave可從舊的slave節點完成，避免對master業務的影響。
具體實現步驟：
1、在slave節點停止複製：
stop slave;
flush tables with read lock;
2、獲取舊slave複製的binlog和Pos
show slave status\G
Relay_Master_Log_File: master-bin.000004
Exec_Master_Log_Pos: 2958
3、備份slave
mysqldump -uroot -p--all-databases > slave_to_slave.sql
4、將備份匯入新的slave節點

5、在新的slave節點配置同步binlog和pos並啟動複製
)]> change master to master_host='192.168.56.133',master_user='tyslave',master_password='Slave2018',master_log_file='mysql-bin.000004', master_log_pos=2958;
start slave;
show slave status\G
6、測試同步

------------------Master-Slave-End---------------------------------------------
------------------Master-Master------------------------------------------------
---slave節點操作：
)]> stop slave;
Query OK, 0 rows affected (0.00 sec)

create user identified by 'tySlave2018';
grant replication slave,reload,super,file on *.* to ;

---master節點操作：
)]> change master to master_host='192.168.56.134',master_user='tyslave',master_password='Slave2018',master_log_file='mysql-bin.000002', master_log_pos=2217;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

)]> start slave;
Query OK, 0 rows affected (0.01 sec)

)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.134
                  Master_User: tyslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 3614
               Relay_Log_File: mysql03-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3614
              Relay_Log_Space: 529
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
Replicate_Ignore_Server_Ids:
             Master_Server_Id: 133
                  Master_UUID: 2146514c-751b-11e8-b0ed-d4ae529d9607
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

--slave節點操作：
)]> stop slave;
Query OK, 0 rows affected (0.00 sec)

)]> show slave status\G

------------------Master-Master-End-By-TangYun[Tony.Tang]20180628---------------------------------------------

MySQL5.7 Master-Master主主搭建for Centos7

相關文章