怎么实现mysql 5.7主主同步

45次阅读

共计 10556 个字符，预计需要花费 27 分钟才能阅读完成。

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