MySQL master-slave sync settings

Host Settings
/etc/my.cnf

#ID must be unique
Server-id=1
# #二进制文件名称前缀, generated files mysql-bin.000001,mysql-bin.000002
Log-bin=mysql-bin
# #记录目前有哪些mysql-bin file SHOW MASTER STATUS;
Log-bin-index=master-bin.index
# #有更新是否立即同步到日志, performance will be low, but data will not be lost.
Innodb_flush_log_at_trx_commit=1
Sync_binlog=1
# #二进制日志自动删除的天数. The default value is 0, which means "No automatic deletion"
expire_logs_days=10
# #二进制文件大小, operation size generate new File Store (minimum 4096byte max 1073741824 (1G) default
max_binlog_size=1073741824
# #同步哪些数据库
Binlog-do-db=kintech_crm
Binlog-do-db=kintech_eq
Binlog-do-db=kintech_pd
Binlog-do-db=kintech_sys

#二进制复制方式
Binlog_format=row
#控制当服务器无法写入二进制日志时发生的情况, this may cause the primary server log to become inconsistent and the server will lose synchronization.
#IGNORE_ERROR忽略错误继续进行, Abort_server Stop the log and shut down the service when an error fails to write to the binary log.
Binlog_error_action=abort_server

# #下面配置不是必须的
# #数据库编码
Character-set-server=utf8
# #忽略表ming大小写
Lower_case_table_names=1
# #开启慢查询日志, log in more than 10 seconds
Slow_query_log=1
long_query_time=10.000000
# #默认值: 128M dedicated MySQL server set size: OS memory 70%-80% best.
# #并不是设置的越大越好. Too large a setting can cause the swap space in the system to be occupied, slowing down the operating system, thus reducing the efficiency of SQL queries.
innodb_buffer_pool_size=500m

Create user
Grant Replication Slave on * * to [e-mail protected] '% ' identified by ' [email protected] ';
Flush privileges;

Backing up data
Mysqldump-u root-p--databases mytest--master-data > Dbdump.db

--master-data This parameter is more important
1: First before the backup it will call FLUSH TABLES with READ lock Lock the database, the database will not be able to write operations.
2: It will record the change on the top of the backup file MASTER to master_log_file= ' mysql-bin.000002 ', master_log_pos=154, red may not be the same as you test.
It records the file name and location of the master server binary log, which is where the current data is cut off, so that when slave is started, the data is copied from this point.
3: Then back up the data to the file.
4: Last release lock UNLOCK TABLES;

Import data from a machine
scp/var/lib/mysql/dbdump.db [Email protected]:/home/mysqldata/

Restarting the host
FLUSH TABLES with READ LOCK;
SHOW MASTER STATUS; (mysql-bin.000002 154)
UNLOCK TABLES;

modifying slave machines
My.ini

#唯一ID
server-id=2
#中继日志
Relay-log=slave-relay-bin
Relay-log-index=slave-relay-bin.index
#二进制日志自动删除的天数. The default value is 0, which means "No automatic deletion"
expire_logs_days=10
Relay_log_recovery=on
Relay_log_info_repository=table

Restart
Change MASTER to
Master_host= ' 101.200.50.31 ',
Master_user= ' Repl ',
master_password= ' [email protected] ',
Master_log_file= ' mysql-bin.000007 ',
master_log_pos=154;

Mysql> START SLAVE;
Mysql> Show slave status;

MySQL master-slave sync settings