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
tar xzf boost_1_59_0.tar.gz
wget
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)
總結:
如果不配置increment_increment會出現以下問題
Last_SQL_Error: Could not execute Write_rows event on table my.userinfo; Duplicate entry '1297' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log bin_log.000005,
db01 my.cnf
auto_increment_increment=2
auto_increment_offset=1
db02 my.cnf
auto_increment_increment=2
auto_increment_offset=2
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
tar xzf boost_1_59_0.tar.gz
wget
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)
總結:
如果不配置increment_increment會出現以下問題
Last_SQL_Error: Could not execute Write_rows event on table my.userinfo; Duplicate entry '1297' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log bin_log.000005,
db01 my.cnf
auto_increment_increment=2
auto_increment_offset=1
db02 my.cnf
auto_increment_increment=2
auto_increment_offset=2
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24486203/viewspace-2128667/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL 5.7的安裝及主從複製(主從同步)MySql主從同步
- mysql主主同步MySql
- MySQL 5.6主主同步MySql
- mysql5.7主從複製，主主複製MySql
- Mysql主主同步-配置資料同步MySql
- MySQL 5.7 多主一從（多源複製）同步配置MySql
- MySQL5.7主從複製-半同步複製搭建MySql
- mysql主從同步MySql主從同步
- mysql 5.7 GTID主從配置MySql
- MySQL5.7 Master-Master主主搭建for Centos7MySqlASTCentOS
- MySQL主從同步配置MySql主從同步
- MySQL 5.7主從新增新從庫MySql
- MySQL5.7主從複製教程MySql
- MySQL 資料主從同步MySql主從同步
- MySql主從同步介紹MySql主從同步
- Mysql 主從同步實戰MySql主從同步
- mysql主從同步機制MySql主從同步
- mysql master slave 主從同步MySqlAST主從同步
- 生產環境mysql主主同步主鍵衝突處理MySql
- MySQL主從複製、半同步複製和主主複製MySql
- MySQL5.7在滴滴雲主機上的主從複製MySql
- MySQL 5.7 主庫崩潰切備庫MySql
- mysql 5.7 主從複製搭建及原理MySql
- MySQL 5.7 用mysqldump搭建gtid主從MySql
- MySQL 5.7 用xtrabackup搭建gtid主從MySql
- MYSQL資料庫主從同步(一主一從)MySql資料庫主從同步
- MySQL主從複製、半同步複製和主主複製概述MySql
- [資料庫]MYSQL主從同步資料庫MySql主從同步
- Mysql 主從同步原理簡析MySql主從同步
- mysql主從同步問題整理MySql主從同步
- MySQL主從同步配置記錄MySql主從同步
- MySQL主從同步加速Transfer–FAQMySql主從同步
- MySQL-主從複製之同步主從資料MySql
- mysql5.6主主叢集自動同步指令碼MySql指令碼
- MySQL的主從複製、半同步複製、主主複製詳解MySql
- 配置mysql5.5主從複製、半同步複製、主主複製MySql
- MySQL主從同步校驗與重新同步薦MySql主從同步
- Mysql主從同步實戰(一)【知其然】MySql主從同步