怎么实现mysql 5.7主主同步

本篇内容主要讲解“怎么实现mysql 5.7主主同步”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“怎么实现mysql 5.7主主同步”吧!

一.环境:
OS:CentOS 6.5 X64
DB Version:Percona Mysql 5.7.15-9-log
路径:/app/mysql57
数据文件路径:/data/mysql57/data
DB1:192.168.213.4
DB2:192.168.213.5

二.安装Mysql
1.安装依赖包
yum -y install gcc gcc-c++ ncurses ncurses-devel cmake readline-devel 

2.创建所需目录
mkdir -pv /app/mysql57
mkdir -pv /data/mysql56/data/

3.创建用户及赋权
useradd -M -s /sbin/nologin mysql
chown -R mysql:mysql /app/mysql57
chown -R mysql:mysql /data/mysql57

4.下载所需目录
cd /usr/local/tools
wget http://downloads.sourceforge.net/project/boost/boost/1.59.0/boost_1_59_0.tar.gz
tar xzf boost_1_59_0.tar.gz
wget https://www.percona.com/downloads/Percona-Server-5.7/Percona-Server-5.7.15-9/source/tarball/percona-server-5.7.15-9.tar.gz
tar xf percona-server-5.7.15-9.tar.gz
cd percona-server-5.7.15-9

cmake . -DCMAKE_INSTALL_PREFIX=/app/mysql57 \
-DMYSQL_DATADIR=/data/mysql56/data/ \
-DDOWNLOAD_BOOST=1 \
-DWITH_BOOST=../boost_1_59_0 \
-DSYSCONFDIR=/etc \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DENABLE_DTRACE=0 \
-DDEFAULT_CHARSET=utf8mb4 \
-DDEFAULT_COLLATION=utf8mb4_general_ci \
-DWITH_EMBEDDED_SERVER=1

make -j `grep processor /proc/cpuinfo | wc -l` 
make install

cp /app/mysql57/support-files/mysql.server /etc/init.d/mysqld57

5.创建配置文件
/etc/my.cnf，仅供参考(DB1配置)
[client]
port = 3306
socket = /data/mysql57/mysql.sock

[mysql]
#prompt="(\u:HOSTNAME:)[\d]> "
prompt="\u@\h \R:\m:\s [\d]> "
no-auto-rehash
user=root
password=system

[mysqld]
#user = nobody
port = 3306
socket = /data/mysql57/mysql.sock
basedir = /app/mysql57
datadir = /data/mysql57/data
character-set-server = utf8mb4
skip_name_resolve = 1
open_files_limit    = 3072
back_log = 103
max_connections = 512
max_connect_errors = 100000
table_open_cache = 512
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 51
query_cache_size = 0
query_cache_type = 0
#default_table_type = InnoDB
tmp_table_size = 96M
max_heap_table_size = 96M
slow_query_log = 1
slow_query_log_file = /data/mysql57/slow.log
log-error = /data/mysql57/error.log
long_query_time = 0.1
server-id = 2134
log-bin = /data/mysql57/data/bin_log
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 8M
max_binlog_size = 1024M
expire_logs_days = 7
master_info_repository = TABLE
relay_log_info_repository = TABLE
#gtid_mode = on
#enforce_gtid_consistency = 1
log_slave_updates
binlog_format = row 
relay_log_recovery = 1
key_buffer_size = 32M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
#myisam_max_sort_file_size = 10G
#myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
#myisam_recover
lock_wait_timeout = 300
innodb_thread_concurrency = 0

transaction_isolation = REPEATABLE-READ
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 717M
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_max_dirty_pages_pct = 30
innodb_flush_method = O_DIRECT
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_locks_unsafe_for_binlog = 0

auto_increment_increment=2
auto_increment_offset=1
lower_case_table_names = 1
[mysqldump]
quick
max_allowed_packet = 32M
user=root
password=system

DB2 /etc/my.cnf配置
[client]
port = 3306
socket = /data/mysql57/mysql.sock

[mysql]
#prompt="(\u:HOSTNAME:)[\d]> "
prompt="\u@\h \R:\m:\s [\d]> "
no-auto-rehash
user=root
password=system
[mysqld]
#user = nobody
port = 3306
socket = /data/mysql57/mysql.sock
basedir = /app/mysql57
datadir = /data/mysql57/data
character-set-server = utf8mb4
skip_name_resolve = 1
open_files_limit    = 3072
back_log = 103
max_connections = 512
max_connect_errors = 100000
table_open_cache = 512
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 51
query_cache_size = 0
query_cache_type = 0
#default_table_type = InnoDB
tmp_table_size = 96M
max_heap_table_size = 96M
slow_query_log = 1
slow_query_log_file = /data/mysql57/slow.log
log-error = /data/mysql57/error.log
long_query_time = 0.1
server-id = 2135
log-bin = /data/mysql57/data/bin_log
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 8M
max_binlog_size = 1024M
expire_logs_days = 7
master_info_repository = TABLE
relay_log_info_repository = TABLE
#gtid_mode = on
#enforce_gtid_consistency = 1
log_slave_updates
binlog_format = row 
relay_log_recovery = 1
key_buffer_size = 32M
read_buffer_size = 1M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
#myisam_max_sort_file_size = 10G
#myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
#myisam_recover
lock_wait_timeout = 300
innodb_thread_concurrency = 0

transaction_isolation = REPEATABLE-READ
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 717M
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1024M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 16M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_max_dirty_pages_pct = 30
innodb_flush_method = O_DIRECT
innodb_file_format = Barracuda
innodb_file_format_max = Barracuda
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_locks_unsafe_for_binlog = 0
auto_increment_increment=2
auto_increment_offset=2
lower_case_table_names = 1
[mysqldump]
quick
max_allowed_packet = 32M
user=root
password=system

6.初始化数据库
/app/mysql57/bin/mysqld --initialize-insecure --user=mysql --basedir=/app/mysql57 --datadir=/data/mysql57/data
备注:--initialize-insecure不会生成随机密码,安装后建议运行安全脚本

7.修改环境变量
echo "export PATH=$PATH:/app/mysql57/bin " >/etc/profile.d/mysql57.sh
source /etc/profile.d/mysql57.sh
/etc/init.d/mysqld57 start

三.配置复制
1.db01配置
CREATE USER 'repl'@'%' IDENTIFIED  BY '12345678';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

2.db02配置
CREATE USER 'repl'@'%' IDENTIFIED  BY '12345678';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

3.两台主机锁库
FLUSH TABLES WITH READ LOCK;

db01主机查看master pos
root@localhost 23:21:  [(none)]> show master status \G
*************************** 1. row ***************************
             File: bin_log.000009
         Position: 2810194
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 5311bf4f-abe4-11e6-9732-000c29c7d527:1-3
1 row in set (0.00 sec)

db02主机查看master pos
root@localhost 19:16:  [(none)]> show master status \G
*************************** 1. row ***************************
             File: bin_log.000010
         Position: 194
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 33655518-a3b3-11e6-8e89-000c29635439:1-3
1 row in set (0.00 sec)

db01,db02分别操作
Unlock Tables;

db02操作
CHANGE MASTER TO MASTER_HOST = '192.168.213.4', MASTER_USER = 'repl', MASTER_PASSWORD = '12345678', MASTER_LOG_FILE = 'bin_log.000009', MASTER_LOG_POS = 2810194;
start slave;

db01操作
CHANGE MASTER TO MASTER_HOST = '192.168.213.5', MASTER_USER = 'repl', MASTER_PASSWORD = '12345678', MASTER_LOG_FILE = 'bin_log.000010', MASTER_LOG_POS = 194;
start slave;

db02:
root@localhost 19:19:  [(none)]> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.213.4
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin_log.000009
          Read_Master_Log_Pos: 2810194
               Relay_Log_File: ORADB-213-5-relay-bin.000036
                Relay_Log_Pos: 318
        Relay_Master_Log_File: bin_log.000009
             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: 2810194
              Relay_Log_Space: 531
              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: 2134
                  Master_UUID: 5311bf4f-abe4-11e6-9732-000c29c7d527
             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: 33655518-a3b3-11e6-8e89-000c29635439:1-3
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

db01:
root@localhost 23:21:  [(none)]> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.213.5
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin_log.000010
          Read_Master_Log_Pos: 194
               Relay_Log_File: ORA11G-213-4-relay-bin.000025
                Relay_Log_Pos: 318
        Relay_Master_Log_File: bin_log.000010
             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: 194
              Relay_Log_Space: 741
              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: 2135
                  Master_UUID: 33655518-a3b3-11e6-8e89-000c29635439
             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: 5311bf4f-abe4-11e6-9732-000c29c7d527:1-3
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

到此，相信大家对“怎么实现mysql 5.7主主同步”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！