MySQL Master-slave replication

MySQL master-slave replication
1, basic master-Slave server configuration
(1) Primary server configuration
[Client]
Port = 3306
Socket =/tmp/mysql.sock

[Mysqld]
user = MySQL
Innodb_buffer_pool_size = 128M
Log_bin = Master-log ===========
Max_binlog_size = 64M
Binlog_format = Mixed ========
Basedir =/usr/local/mysql
DataDir =/database/mydata
Port = 3306
server_id = 1 ==========
Socket =/tmp/mysql.sock
Log_error =/database/mydata/server1.err
Character_set_server = UTF8
Explicit_defaults_for_timestamp = On
Sql_mode=no_engine_substitution,strict_trans_tables

initialization of the database
./scripts/mysql_install_db--user=mysql--datadir=/database/mydata

to authorize from the server
mysql> grant replication Slave on * * to ' repuser ' @ ' 10.0.5.151 ' identified by ' aixocm ';
mysql> flush Privileges;
Mysql> Show Master Status\g
(2) configuration from server
[Client]
Port = 3306
Socket =/tmp/mysql.sock

[Mysqld]
user = MySQL
Innodb_buffer_pool_size = 128M
Log_bin = Slave-log ============
Max_binlog_size = 64M
Log_slave_updates = on =====
Binlog_format = Mixed =====the master and slave servers only need these two items, from the server does not need
Relay_log = Relay-bin ============
Basedir =/usr/local/mysql
DataDir =/database/mydata
Port = 3306
server_id = 2 ======
Socket =/tmp/mysql.sock
Log_error =/database/mydata/server1.err
Character_set_server = UTF8
Explicit_defaults_for_timestamp = On
Sql_mode=no_engine_substitution,strict_trans_tables

initialization of the database
#./scripts/mysql_install_db--user=mysql--datadir=/database/mydata

set up information about the primary server
mysql> Change Master to master_host= ' 10.0.5.150 ', master_user= ' repuser ', master_password= ' aixocm ', master_log_file = ' master-log.000003 ', master_log_pos=409,master_port=3306;
mysql> start slave;
Mysql> Show Slave Status\g
mysql> stop Slave;

(3) binary log operations
# Mysqlbinlog master-log.000001
# mysqlbinlog--start-datetime= ' 2015-07-13 7:10:0 ' master-log.000003
# mysqlbinlog--start-datetime= ' 2015-07-13 7:10:0 '--stop-datetime= ' 2015-07-13 7:33:0 ' master-log.000003
# Mysqlbinlog--start-position=330 master-log.000003
# Mysqlbinlog--start-position=330--stop-position=1100 master-log.000003
# mysqlbinlog--start-position=330--stop-position=1100 master-log.000003 | Mysql-u root \ \restores the contents of the specified binary log

Mysql> show master logs;
Mysql> show binary logs;

Mysql> show Binlog events in ' master-log.000003 ' limit 20;
Mysql> show Binlog events in ' master-log.000003 ' from 409 limit 10;

Mysql> purge master logs to ' master-log.000002 '; \\Delete the log before the specified number
Mysql> purge master logs before ' 2015-07-22 08:00:00 ';
(4) Setting the ignored database
master server Settings
binlog_do_db = Sxjy\ \ Records the database of the binary log
binlog_ignore_db = Test\ \ Do not log binary logs of the database
binlog_ignore_db = Teach

from server Settings
replicate_do_db = Sxjy\ \ Set the default database for binary log replication
replicate_ignore_db = Test\ \ non-binary log replication database
replicate_ignore_db = Teach
replicate_ignore_db = MySQL
replicate_ignore_db = Information_schema
Replicate_do_table = Sxjy.stu\ \ Set the table for the update
Replicate_ignore_table = Sxjy.class\ \ Does not update the table



recommended settings from the server
replicate_ignore_db = Test\ \ non-binary log replication database
replicate_ignore_db = MySQL
replicate_ignore_db = Information_schema
Replicate_wild_do_table = Sxjy.stu\ \ Copy the binary log of the specified database or table
Replicate_wild_do_table = sxkj.%
Replicate_wild_ignore_table = Sxjy.class\ \ Does not copy the binary log of the specified database or table

Exercise: First build a MySQL database server, do not enable binary logging, and then start the server, first create a SXJY database,
Create Stu and teach two tables under the SXJY database, add 3-4 fields yourself, and insert 4-5 records. Then the
The server changes to master-slave structure, requires the original server server, and on the slave server to have the original data on the primary server,
After the master-slave structure is set up, the data added later can be automatically synchronized to the slave server, and the master server data should be fully consistent.
2. Gtid-Based transactional replication
(1) Ability to perform multi-threaded replication based on a database (requires 2 or more than 2 databases to be synchronized)
(2) Ability to automatically determine the location to be copied
(3) cannot support non-transactional storage engines, such as MyISAM
(4) Do not support CREATE TABLE ... select from ... Statement


Primary server Configuration
Log_slave_update = On
Gtid_mode = on \ Open Gtid mode
enforce_gtid_consistency = on \ \ Mandatory Gtid consistency
Master_info_repository =table \ \ Master server information is logged (TABLE or file)
relay_log_info_repository = TABLE \ \ Trunk Log information recording method
Sync_master_info = 1 \ \ Synchronize master database information
slave_parallel_workers = 4 \ \ The number of SQL threads from the server, same as the database to be replicated
Binlog_checksum =crc32 \ \ binary log check mode
master_verify_checksum = 1 \ \ Primary server enable checksum
slave_sql_verify_checksum = 1 \ \ Enable checksum from the server
binlog_rows_query_log_events = 1 \ \ Binary log verbose logging events
Report_port = 3306 \ \ Enough to copy the report port, and the database port is consistent
Report_host = 10.0.5.150 \ \ Provides replication report host, set as native address

mysql> grant replication Slave on * * to ' repuser ' @ ' 10.0.5.151 ' identified by ' aixocm ';
mysql> flush Privileges;
From server configuration
Log_slave_update = On
Gtid_mode = on \ Open Gtid mode
enforce_gtid_consistency = on \ \ Mandatory Gtid consistency
Master_info_repository =table \ \ Master server information is logged (TABLE or file)
relay_log_info_repository = TABLE \ \ Trunk Log information recording method
Sync_master_info = 1 \ \ Synchronize master database information
slave_parallel_workers = 4 \ \ The number of SQL threads from the server, same as the database to be replicated
Binlog_checksum =crc32 \ \ binary log check mode
master_verify_checksum = 1 \ \ Primary server enable checksum
slave_sql_verify_checksum = 1 \ \ Enable checksum from the server
binlog_rows_query_log_events = 1 \ \ Binary log verbose logging events
Report_port = 3306 \ \ Enough to copy the report port, and the database port is consistent
Report_host = 10.0.5.151 \ \ Provides replication report host, set as native address

mysql> Change Master to master_host= ' 10.0.5.150 ', master_user= ' Repuser ',
Master_password= ' aixocm ', master_auto_position=1;
mysql> start slave;
Mysql> Show Processlist\g
Mysql> Show status like ' thread% ';


Slave_skip_errors = All
Slave_skip_errors = 1062,1756,2003
3. Dual master replication
First server Settings
auto_increment_increment = 2 \ \ Data table records the self-increment, which is generally equal to the number of servers
Auto_increment_offset = 1 \ \ data table records each increment, the first is 1, the second is 2,...
Sync_binlog = 0 \ \ Binary log writes to disk
replicate_same_server_id = 0 \ \ Prevent MySQL loop update

mysql> grant replication Slave on * * to ' repuser ' @ ' 10.0.5.151 ' identified by ' aixocm ';
mysql> flush Privileges;
mysql> Change Master to master_host= ' 10.0.5.151 ', master_user= ' Repuser ',
Master_password= ' aixocm ', master_auto_position=1;
mysql> start slave;

This article is from the "Kenasel" blog, make sure to keep this source http://kenasel.blog.51cto.com/10620829/1839522

MySQL Master-slave replication