[Svc] get MySQL master-slave synchronization

MySQL Database Settings master-Slave synchronization

Features of Master-slave synchronization:
of logic
of asynchronous

The role of master-slave synchronization

1. Backup
2. Read and write separation

Steps for master-slave synchronization

• Prepare the Environment
  1, MySQL version consistent more than 5.5
  2, the master-slave database is consistent

Master Library Master Modification 1. Modify MY.CNF

# 打开主库binlog  log-bin = mysql-bin    # 主数据库端ID号(区别于从库)  server-id = 1

2. Restart MySQL to create an account for synchronization

grant replication slave on *.* to 'rep'@'%' identified by '123456'; flush privileges;

View Master Status

mysql> show master status;  +------------------+----------+--------------+------------------+  | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |  +------------------+----------+--------------+------------------+  | mysql-bin.000009 |      196 |              |                  |  +------------------+----------+--------------+------------------+  

Note: Do not operate the primary database until this step is complete, preventing changes in the primary database state value

Modifications from the library slave 1. Modify the MySQL configuration

# 从数据库端ID号  

Execute synchronization command

# 执行同步命令，设置主数据库ip，同步帐号密码，同步位置  mysql>change master to master_host='192.168.1.2',master_user='rep',master_password='123456',master_log_file='mysql-bin.000009',master_log_pos=196;    # 开启同步功能  mysql>start slave;  

Check the status from the library

mysql> show slave status\G;  *************************** 1. row ***************************                 Slave_IO_State: Waiting for master to send event                    Master_Host: 192.168.1.2                    Master_User: rep                    Master_Port: 3306                  Connect_Retry: 60                Master_Log_File: mysql-bin.000009            Read_Master_Log_Pos: 196                 Relay_Log_File: vicky-relay-bin.000002                  Relay_Log_Pos: 253          Relay_Master_Log_File: mysql-bin.000009               Slave_IO_Running: Yes              Slave_SQL_Running: Yes                Replicate_Do_DB:            

Note: The slave_io_running and slave_sql_running processes must be running normally, that is, the Yes state, otherwise the synchronization fails.
Here, the master-slave database setup work has been completed, you can create a new database and tables, insert and modify data, test the success

Other relevant parameters that may be used the master side

# 不同步哪些数据库  binlog-ignore-db = mysql  binlog-ignore-db = test  binlog-ignore-db = information_schema    # 只同步哪些数据库，除此之外，其他不同步  binlog-do-db = game    # 日志保留时间  expire_logs_days = 10    # 控制binlog的写入频率。每执行多少次事务写入一次  # 这个参数性能消耗很大，但可减小MySQL崩溃造成的损失  sync_binlog = 5    # 日志格式，建议mixed  # statement 保存SQL语句  # row 保存影响记录数据  # mixed 前面两种的结合  binlog_format = mixed  

Slave end

# 停止主从同步  mysql> stop slave;    # 连接断开时，重新连接超时时间  mysql> change master to master_connect_retry=50;    # 开启主从同步  

The above connection time-out settings, similar way can be used to set the primary database IP, synchronization account password, synchronization location

Expand reading: MySQL log format Binlog_format

[Svc] get MySQL master-slave synchronization