59.MYSQL Master-Slave configuration

First, 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
  

Second, master-slave configuration-Lord operation

Install MySQL
http://blog.51cto.com/13569831/2096308
Modify MY.CNF, add server-id=133 and Log_bin=chinantfy1

After modifying the configuration file, start or restart the Mysqld service

 service mysqld restart ls /data/mysql

Backup and restore MySQL library to Chinantfy library as test data

mysqldump -uroot -p123456 mysql > /tmp/mysql.sql mysql -uroot -p123456 -e ‘create database chinantfy‘ mysql -uroot -p123456 chinantfy < /tmp/mysql.sql

Create a user to use to synchronize data

 mysql -uroot -p123456 grant replication slave on *.* to ‘repl‘@192.168.127.134 identified by ‘123456‘; flush tables with read lock; show master status;

chinantfy1.000001 665002

Third, master-slave configuration-from the top operation

Install MySQL
View my.cnf, configuring server-id=134, requirements not the same as the master
After modifying the configuration file, start or restart the Mysqld service

 vim   /etc/profile   export PATH=$PATH:/usr/local/mysql/bin/

Sync the Lord Chinantfy library to the top

 scp @192.168.127.133:/tmp/mysql.sql  /tmp/chinantfy.sql

You can create the Chinantfy library first, then copy the/tmp/mysql.sql of the Lord to the top, and then import the Chinantfy library

  mysql -uroot -e ‘create database chinantfy‘  mysql -uroot chinantfy </tmp/chinantfy.sql

 mysql -uroot stop slave; change master to master_host=‘192.168.127.133‘, master_user=‘repl‘, master_password=‘123456‘, master_log_file=‘chinantfy1.000001‘, master_log_pos=665002;   //ip是主的ip start slave;

And go to the Lord to execute

  unlock tables;

Four, check whether the master-slave synchronization is normal

Execute Mysql-uroot from top
Show Slave Status\g
See if there is
Slave_io_running:yes
Slave_sql_running:yes
Also need to pay attention to
seconds_behind_master:0//main from the time of the delay
last_io_errno:0
Last_io_error:
last_sql_errno:0
Last_sql_error:

Five, several configuration parameters

On the primary server
binlog-do-db=//Synchronize only the specified libraries
binlog-ignore-db=//Ignore specified library
From the server
replicate_do_db=
replicate_ignore_db=
replicate_do_table=
replicate_ignore_table=
replicate_wild_do_table=//As chinantfy.%, wildcard% supported
replicate_wild_ignore_table=

Vi. Test Master and slave

Lord Mysql-uroot Chinantfy
Select COUNT () from DB;
TRUNCATE TABLE db;
To Mysql-uroot Chinantfy from the top
Select COUNT () from DB;
The Lord continues to drop table db;
View the DB table from the top

59.MYSQL Master-Slave configuration