MySQL master and slave instructions, MySQL main never synchronous processing scheme

MySQL master-slave instructions synchronization problem

Mysqldump: This tool is suitable for databases below 10G or several tables
Percona-xtrabackup Backup tool: For 100g-500g
LVM Snapshots: Larger data volumes, or sub-database tables

Purpose of Master-slave replication

1. Read and write separation, reduce the main library load or data analysis;
2. Data security, do backup recovery;
3. Master-slave switch, do high-availability;

Common master-Slave structure:

One Master one from: a Master, a Slave
One master multi-slave: one master, multiple slave

Master node: Responsible for all "write" requests
Slave node: Responsible for most of the "read" requests

Master-slave Replication steps

1. A Data update
2. A write to Bin Log
3. A's IO thread communicates with B's IO thread transport
4. B write the transmitted information to relay LOG
5. b Check that the SQL statement is executed after relay log has new content
6. b Update Data

MySQL Master Master library (i.e. Master Service library) is not synchronized

mysql>show processlist;            //查看进程是否sleep太多mysql>show master status;        //查看主服务器的master数据状态

Slave library (from library)

mysql>show slave status\G            //查看slave的同步状况，主要是IO与SQLSlave_IO_Running: YesSlave_SQL_Running: No这两个必须是yes状态，由此可见slave不同步状态

Handling Scenario Method One: Ignore the error and continue syncing

Applicable to the master-slave database data is not small, or require data can not be completely unified situation, data requirements are not strict;

Slave库修复mysql>stop slave;                                                                //停止同步mysql>set global sql_slave_skip_counter =1;                        //跳过一步错误mysql>start slave;                                                                //开启同步

Method Two: Re-master from, fully synchronized

It is suitable for the case that the master-slave database data is large or the data is completely unified;

master库设置mysql>flush tables with read lock;                //锁定为只读，防止数据写入mysqldump -uroot -p‘123456‘ mysql > /tmp/mysql.bak.sql        //备份数据到目录mysql> show master status;                    //查看master的状态，写同步配置会用到相关数据scp /tmp/mysql.bak.sql [email protected]:/tmp/        //将mysql的备份文件传到从库机器

Slave库设置mysql>stop slave;                //停止同步mysql>source /tmp/mysql.bak.sql        //使用备份就行恢复数据，也可以使用mysqldump命令change master to master_host = ‘192.168.128.100‘, master_user = ‘rsync‘, master_port=3306, master_password=‘‘, master_log_file = ‘mysqld-bin.000001‘, master_log_pos=3260;         //设置从库同步，其中log_file与log_pos的参数来自于主服务的show master status里mysql>start slave;                    //开启从同步mysql>show slave status\G                //查看同步状态

MySQL master and slave instructions, MySQL main never synchronous processing scheme