MySQL Master-slave replication

MySQL master-slave replication is also known as Replication

1 The principle of his

Http://www.cnblogs.com/crazylqy/p/4065374.html

2 The realization of his

Here is a topic dedicated to MySQL from the history to the development to the architecture, to the MySQL cluster has a more detailed introduction: http://www.cnblogs.com/crazylqy/category/625963.html

3 Concrete Implementation Steps

Http://www.cnblogs.com/alvin_xp/p/4162249.html

4 problems encountered

1) MySQL error code 1872 (er_slave_rli_init_repository): SLAVE failed to initialize relay log info structure from the repos Itory

WORKAROUND: Reset the connection from the database, http://www.cnblogs.com/Bccd/p/5856716.html

5 Detailed primary Database configuration reference

[Client]

port=5869

[MySQL]

Default-character-set=utf8


[Mysqld]

port=5869
#basedir =d:/mysql-5.6.21-winx64
#datadir =d:/mysql-5.6.21-winx64/data
Character-set-server=utf8
Default-storage-engine = MYISAM
Sql-mode= "Strict_trans_tables,no_auto_create_user,no_engine_substitution"

Log-bin=mysql-bin #slave会基于此log-bin to do replication.
Log-bin-index=mysql-bin.index
Server-id=11 #master的标示
Read-only=0
Binlog-do-db=xsmart_mssp_basedatacenter #同步数据库, you can copy and write multiple
Binlog-ignore-db=mysql #忽略需要同步的数据库, you can copy and write multiple
Log-slave-updates=1 #这个参数一定要加上, otherwise the updated record is not written to the binary file
Innodb_flush_log_at_trx_commit=1
Sync_binlog=1
Skip-external-locking #加上可以避免因资源锁引起MySQL服务停止
Skip-name-resolve #加上可以避免因资源锁引起MySQL服务停止

max_connections=300
query_cache_size=186m
table_open_cache=1520
tmp_table_size=67m
thread_cache_size=38
myisam_max_sort_file_size=100g
myisam_sort_buffer_size=67m
key_buffer_size=290m
Read_buffer_size = 1M
Read_rnd_buffer_size = 8M
Sort_buffer_size = 1M
#innodb_additional_mem_pool_size =80m
#innodb_flush_log_at_trx_commit =0
#innodb_log_buffer_size =8m
#innodb_buffer_pool_size =2g
#innodb_log_file_size =1024m
#innodb_thread_concurrency =18
#innodb_support_xa = 1
#innodb_file_per_table = 1
#innodb_file_format = Barracuda

#interactive_timeout = 2147482
#wait_timeout = 2147482
Interactive_timeout = 86400
Wait_timeout = 86400
Max_allowed_packet = 1G

# Binlog
#log_bin = D:/mysql-5.6.21-winx64/log/binlog/mysql-bin.log
#log_bin_index = D:/mysql-5.6.21-winx64/log/binlog/mysql-bin.index
#log_error = D:/mysql-5.6.21-winx64/log/mysql-error.log
Sync_binlog = 1
Expire_logs_days = 7
Binlog_format = MIXED

[Mysqldump]
Max_allowed_packet = 1G

6 statement references for the primary database execution

CREATE USER Repl; ' Create a master-slave replicated database user, rel
GRANT REPLICATION SLAVE on * * to ' repl ' @ ' 192.168.50.% ' identified by ' MySQL '; ' Assign permissions to this user, and his password is MySQL

SHOW MASTER STATUS; ' Displays the primary database master-slave replication Run status

7 executing a statement reference from a database

STOP SLAVE; ' To stop connecting to the primary database
RESET SLAVE all; ' Resetting the connection to the primary database
Change MASTER to master_host= ' 192.168.50.192 ',
master_port=5869,
Master_user= ' Repl ',
master_password= ' MySQL ',
Master_log_file= ' mysql-bin.000006 ',
master_log_pos=5750;

START SLAVE; ' Initiates a connection to the primary database and begins a master-slave copy

SHOW SLAVE STATUS; ' Check the master and slave copy running status, when you see Slave_io_running and slave_sql_running are yes, prove that the synchronization is successful, or see SQL execution error log

MySQL Master-slave replication