Topsy MySQL Master

++++++++++++++++++++++++ processing ++++++++++++++++ on Master
#1 the configuration file to add and guarantee unique
Server-id=1
Log-bin=/var/lib/mysql/mysql-bin

#2 restart
/etc/init.d/mysqld restart

#3 Create an account rep for synchronization
Grant Replication Slave on * * to ' rep ' @ ' 192.168.11.% ' identified by ' 123456 ';
Flush privileges;
Show grants for [email protected] ' 192.168.11.% ';
NOTE: Replication slave must have permissions for MySQL sync, do not authorize all to be hacked.

#4 the library operation (read-only to the database lock table) do not close the current window
Note: When the production environment, the operation of master-slave replication, the need for downtime. The data volume is large the lock table can affect the business.

Flush table with read lock;
Hint: The time of this lock Table command, in the case of different engines, will be controlled by the following parameters, when the lock table is not operated automatically unlocked if the setting time is exceeded.
Constrained by two parameters: Interactive_timeout = Wait_timeout = 60
Duration By default: Show variables like '%timeout% ';

#5 Query Bin-log log where to query the state of the main library
Show master status;
Queries the main library state, that is, the current Binlog log file name and binary Binlog log offset.
That means I'm now in the main vault, writing data to Bin-log, 000001, 342-bit location for this file.

#6 back up all libraries to export all data in the database
Mysqldump-u root-p123456--events--compact--default-character-set=utf8-a-b-f |gzip > mysql_all_$ (Date +%F). sql . gz

#7 Unlocking
Unlock table
++++++++++++++++++++++++ on slave ++++++++++++++++
Set Server-id in #1 configuration file and turn off Bin-log parameter configuration
server-id=2
#log-bin=/var/lib/mysql/mysql-bin
#2 restart
/etc/init.d/mysqld restart

#3 pull the database of the main library to the local input database
SCP [email protected]:/home/yeqing/mysql_all_2017-01-18.sql.gz.
Gzip-d mysql_all_2017-01-18.sql.gz

Note: Table with foreign key is not good guide, MySQL and other system tables do not guide

#4 Configure synchronization parameters (according to the show Master status setting)
Mysql> Change MASTER to
Master_host= ' 192.168.11.16 ',
master_port=3306,
-Master_user= ' rep ',
-master_password= ' 123456 ',
Master_log_file= ' mysql-bin.000001 ',
master_log_pos=342;
Query OK, 0 rows affected (0.11 sec)

#5 Check if there is Master.info
Cat/var/lib/mysql/master.info

#6 perform the sync switch.
Slave start;

#7 Check that you have synchronized
show slave status\g;

IO and SQL process to be yes:
Slave_io_running:yes
Slave_sql_running:yes
Delay to be 0
seconds_behind_master:0

#8 If you encounter an IO process and the SQL process has not yes, do the following:

Scenario 1:
1. On Master
Clear Bin-log:reset Master;
Re-view location point: Show Master status;
2. On the slave
Turn off sync: Slave stop
Clear Bin-log:rester
Reconfigure synchronization parameters against the master location point
Re-open sync: Slave start;
Check again to see if sync: show slave status\g;
Scenario Two:
Small differences in the case directly through the NAVICAT data comparison function to find out to fill up from the library
Turn off sync: Slave stop
Clear Bin-log:rester
Re-open sync: Slave start;

Programme III:
The difference is large in the case of direct re-decision from synchronization.

#9 Test Synchronization
On master:
CREATE TABLE test_2 (id int);
Mysqlbinlog mysql-bin.*|egrep-v ' #|*! '

On the Slave:
Show tables;

This article from the "Development and operation of the" blog, declined to reprint!

Topsy MySQL Master