Database Master-slave replication

Note: MySQL version is best consistent, otherwise there may be a binary file recognition error

First step: Modify the configuration file Windows environment Modify My.ini (in MySQL installation directory) Linux environment/etc/my.cnf need to restart the server after modifying
Add under [mysqld]
Must be 1.server-id=xxx# #数据库唯一标识, must be different
Must 2.log-bin= file name # #表示打开binlog, open this option can be written to slave relay-log through I/O, also can be replication the premise;
3.binlog-do-db=xxx# #表示需要记录二进制日志的数据库. If you have multiple databases that can be separated by commas, or use multiple binlog-do-db options
4.binlog-ignore-db=xxx# #表示不需要记录二进制日志的数据库. If you have multiple databases that can be separated by commas, or use multiple binlog-do-db options
You must 5.replicate-do-db# #表示需要同步的数据库 if multiple databases can be separated by commas, or use multiple replicate-do-db options
6.replicate-ignore-db# #表示不需要同步的数据库 If multiple databases can be separated by commas, or multiple replicate-ignore-db=mysql options are used
7.log-slave-updates=1 or 0, closed by default # #配置从库上的更新操作是否写入二进制文件, if this from the library, but also to do other from the library's main library, then you need to hit this parameter, so that the library from the library to be able to log synchronization
8.SLAVE-SKIP-ERRORS=[ERR1,ERR2,... all]# #在复制过程, due to various causes of SQL error in Binlog, by default, from the library will stop replication, the user to intervene.
# #可以设置slave-skip-errors to define the error number and can be skipped if the error number encountered during the copy process is a defined error number.
# #如果从库是用来做备份, setting this parameter will have inconsistent data, do not use. If you are sharing the query pressure of the main library, consider it.
9.sync_binlog# #sync_binlog的默认值是0, in this mode, MySQL will not sync to disk. In this case, MySQL relies on the operating system to flush binary log binaries,
# #就像操作系统刷其他文件的机制一样. So if the operating system or machine (not just the MySQL server) crashes, it is possible that the last statement in the Binlog is lost.
# #要想防止这种情况, you can use the Sync_binlog global variable to synchronize binlog with the hard disk after every n binlog write.
# #当sync_binlog变量设置为1是最安全的, because in the case of crash crashes, your binary logs binary log can only lose a maximum of one statement or a transaction.
# #但是, this is also the slowest way (unless the disk has cache caches with battery backup power, which makes it very fast to sync to disk).
# #即使sync_binlog设置为1, there may be inconsistencies between the table content and the Binlog content when there is a crash.
# #如果使用InnoDB表, the MySQL server processes the commit statement, which writes the entire transaction to Binlog and commits the transaction to InnoDB.
# #如果在两次操作之间出现崩溃, when restarted, the transaction is InnoDB rolled back, but still exists in Binlog.
# #可以用 –innodb-safe-binlog option to increase consistency between innodb table content and Binlog.
# # (Note: –innodb-safe-binlog is not required in MySQL 5.1; This option is deprecated due to the introduction of XA transaction support).
# #该选项可以提供更大程度的安全, Binlog each transaction (Sync_binlog =1) and (True by default) InnoDB logs to the hard disk,
# #该选项的效果是崩溃后重启时, after rolling back the transaction, the MySQL server cuts back the InnoDB transaction from Binlog.
# #这样可以确保binlog反馈InnoDB表的确切数据等 and keep the slave from the server in sync with the primary server (without taking the rollback statement).
10.auto_increment_offset# #增量初值
11.auto_increment_increment# #增量值
# #9和10组合使用解决同步过程中自增主键冲突
12.slave_net_timeout# #单位为秒 default setting of 3,600 seconds to reduce latency due to network causes

Step Two: Set the primary server slave (note: In the case of no need for a two-machine backup, you do not need to set up slave, only need to set slave from the server)
1. Login to MySQL mysql-u user-p dbname
Additional: View master information
Show Master Status
2. Stop the synchronous transfer process stop slave;
3. Set slave
3.1
Change Master to
Master_host= ' 192.168.1.118 ', # #主机地址 (required)
Master_user= ' Root ', # #主机拥有同步权限的用户 (required)
Master_password= ' 12345 ', # #密码 (required)
Master_log_file= ' mysql-bin.000023 ', # #主服务器上查询的二进制文件名称对应file (generally from the server will be read directly from the primary server, in the synchronization process of an exception or interrupt requires human intervention, need to record this value)
master_log_pos=107,# #主服务器文件记录位置 corresponding position (generally from the server will be read directly from the primary server, in the synchronization process anomalies or interrupts require human intervention, need to record this value)
master-connect-retry=60;# #服务器重连时间默认60秒 reduce delays due to network causes
4. Start slave
Start slave;
5. View slave status (show slave status\g;)
In the slave state
slave_io_running:yes# #说明可以正常进行IO操作---to communicate with the host
slave_sql_running:yes# #说明可以正常执行sql Statement---Responsible for its own SQL operations
Note: master-slave replication is possible only if yes is at the same time
Solution: IO error, to check the communication status between the master and slave servers and the communication status between the databases
SQL error, 1. Check host processlist show prcesslist to see if there are pending tasks
2.show slave status\g; Viewing error conditions requires skipping errors to proceed with synchronization,
First close SLAVE, then set GLOBAL sql_slave_skip_counter=1; Skip the error, start SLAVE again, (usually)
3. Reset Master/Slave
3.1 Enter the main Library lock table FLUSH TABLES with READ lock;
3.2 Export the database to be backed up mysqldump-u root-p backuptest>bacuptest.sql
3.3 Import data from server to new database source Bacuptest.sql;
3.4 Stop slave Stop Slave,
3.5 Retrieving binary files and location information from the master server (set master, same as 3.1 binary file name and record location must)
3.6 Start slave;

Step three: Set slave from server
1, if you need to realize the dual-machine backup, you need the same settings as the primary server
2, solve the problem with the second step

MySQL database master-slave replication

Delay
Generally solved by architecture tuning
For example, the main library is write, the data security is higher, Sync_binlog=1,innodb_flush_log_at_trx_commit = 1
From the library is read, sync_binlog=0

Read/write separation
Note: Generally do read/write separation requires a master library and a common user from the library

1. Framework for background database switching
2, Mysql_proxy (Lua script implementation, LUA script configuration complex)
3. Amoeba for MySQL (easier to use, but does not support transactions and stored procedures)

Database Master-slave replication