MySQL Master-slave configuration

MySQL Master-Slave introduction

• MySQL master-slave is also called replication, AB replication. Simply speaking is a and b two machines from the back, write the data on a, the other B will follow the writing data, both data real-time synchronization
• MySQL master-slave is based on Binlog, the Lord must open Binlog to carry out master and slave.
• The master-slave process has a roughly 3-step
• 1) The change operation is recorded in Binlog.
• 2) from synchronizing the main binlog event (SQL statement) to the machine and recording it in Relaylog
• 3) Execute sequentially from the SQL statements inside the Relaylog
• The Lord has a log dump thread that is used to communicate with the I/O thread from Binlog
• There are two threads from the top, where I/O threads are used to synchronize the main binlog and generate Relaylog, and another SQL thread is used to put the SQL statements inside the Relaylog

MySQL master and slave schematic diagram

Master-Slave configuration-Lord operation

安装mysql 修改my.cnf，增加server-id=130和log_bin=aminglinux1 修改完配置文件后，启动或者重启mysqld服务 把mysql库备份并恢复成aming库，作为测试数据 mysqldump -uroot mysql > /tmp/mysql.sql mysql -uroot -e “create database aming” mysql -uroot aming < /tmp/mysql.sql 创建用作同步数据的用户 grant replication slave on *.* to ‘repl‘@slave_ip identified by ‘password‘; flush tables with read lock; show master status;

Master-Slave configuration-operation from top

安装mysql 查看my.cnf，配置server-id=132，要求和主不一样 修改完配置文件后，启动或者重启mysqld服务 把主上aming库同步到从上 可以先创建aming库，然后把主上的/tmp/mysql.sql拷贝到从上，然后导入aming库 mysql -uroot stop slave； change master to master_host=‘‘, master_user=‘repl‘, master_password=‘‘, master_log_file=‘‘, master_log_pos=xx, start slave; 还要到主上执行 unlock tables

See if master-slave synchronization is working

从上执行mysql -uroot show slave stauts\G 看是否有 Slave_IO_Running: Yes Slave_SQL_Running: Yes 还需关注 Seconds_Behind_Master: 0  //为主从延迟的时间 Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error:

Several configuration parameters

主服务器上 binlog-do-db=      //仅同步指定的库 binlog-ignore-db= //忽略指定库 从服务器上 replicate_do_db= replicate_ignore_db= replicate_do_table= replicate_ignore_table= replicate_wild_do_table=   //如aming.%, 支持通配符%  replicate_wild_ignore_table=

Test Master

主上 mysql -uroot aming   select count(*) from db; truncate table db; 到从上 mysql -uroot aming select count(*) from db; 主上继续drop table db; 从上查看db表

MySQL Master-slave configuration