本篇内容主要讲解“怎么实现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主主同步”有了更深的了解，不妨来实际操作一番吧！这里是创新互联网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
重庆分公司
重庆分公司
