ITPub博客

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

原创 MySQL 作者:tangyunoracle 时间:2018-06-28 15:03:35 0 删除 编辑

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

---在master节点创建复制用户
mysql> select user,host from mysql.user;
+---------------+--------------+
| user          | host         |
+---------------+--------------+
| mysql.session | localhost    |
| mysql.sys     | localhost    |
| root          | localhost    |
+---------------+--------------+
4 rows in set (0.00 sec)

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

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

mysql> select user,host from mysql.user;
+---------------+--------------+
| user          | host         |
+---------------+--------------+
| tyslave       | 192.168.56.% |
| mysql.session | localhost    |
| mysql.sys     | localhost    |
| root          | localhost    |
+---------------+------------

---克隆创建master库的备份
mysql> flush tables with read lock;
root@[(none)]> 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节点并导入
root@[(none)]> source /app/soft/master_to_slave.sql
root@[(none)]> show slave status\G
Empty set (0.00 sec)

root@[(none)]> 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)

root@[(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)

root@[(none)]> 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)

root@[(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| tyuni              |
| uni                |
+--------------------+
6 rows in set (0.00 sec)
---至此,master-slave已经搭建完成,剩下的就是测试同步了。
-----完全清除slave-------------------
mysql> stop slave;
mysql> reset slave all;
Query OK, 0 rows affected (0.02 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端对恢复数据库
root@[(none)]> 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并启动复制
root@[(none)]> 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节点操作:
root@[(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)

root@[(none)]> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     2217 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

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

---master节点操作:
root@[(none)]> 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)

root@[(none)]> start slave;
Query OK, 0 rows affected (0.01 sec)

root@[(none)]> 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节点操作:
root@[(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)

root@[(none)]> show slave status\G

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24930246/viewspace-2156973/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 没有了~
请登录后发表评论 登录
全部评论
有空写写一写,没空看一看。。。 微信号:tangyun0925
  • 博文量
    112
  • 访问量
    1706622