MySQL 5.5 Master-Slave synchronization Setup Tutorial

Modify Master First (10.1.123.197) is configured in the MY.CNF[mysqld]The following additions are added to theLog-Bin=Mysql-binLog-Bin-Index=Mysql-Bin.IndexServer-Id= 1Sync_binlog=1Binlog_format=mixed then specifies the database to be synchronized and ignores the database that does not need to be synchronized Binlog-Do-Db=Testdbbinlog-Ignore-Db=Mysqlbinlog-Ignore-Db=Performance_schemabinlog-Ignore-Db=Information_schema in[mysqldump]the content of the modification is Max_allowed_packet=32M Note: Make sure the max_allowed_packet has a larger value, such as Max_allowed_packet=100M Restart Mysqlservice mysql Restart create a replication user on MasterCREATE USERfunsion;GRANT REPLICATIONSLAVE on *.*  toFunsion identified by 'ifunsion.com'; # test environment password is xyzzy+ ---------------------------------------------------------- +then modify the Slave (10.1.123.160) configuration, modify the MY.CNF in the[mysqld]The following content server is added to the-Id=2Log-Bin=Mysql-Binrelay-Log-Index =Slave-Relay-Bin.IndexRelay-Log =Slave-Relay-Binsync_master_info= 1Sync_relay_log= 1Sync_relay_log_info= 1# above three jargon only used in MySQL5.5Enter MySQL-U root-p go to MySQL command line and enter change MASTER toMaster_host= '10.1.123.233', Master_user= 'funsion', Master_port= 3306, Master_password= 'ifunsion.com'; Change MASTER toMaster_connect_retry= -; In the input START SLAVE;+ -----------------------cannot start if an error occurs-------------------+Enter Master's database input to show Master Status\g Records currently in useLog-Bin files, as well as position (e.g. now logs are MySQL-Bin.000001, the location is107then enter the slave database input stop Slave;change master toMaster_log_file='mysql-bin.000001', Master_log_pos=107Finally, enter start slave, and finally execute Yum on the server.-y install ntpdatentpdate cn.pool.ntp.orgclock-W Synchronize the time of several servers+ ----------------------------Some of the commands that might be used-------------------------+Execution Lock table: Flush Tables with Readlock; This step is to enable us in the process of making master and slave, there will be no new data in the main library, otherwise it will bring trouble to our synchronization settings main Library unlock: Unlock Tables;reset Master The command removes all binary log files and empties the binary log index file. The Reset Slave command deletes all files used for slave replication and starts over again. + -----------------------Other reference documents (not verified)--------------------------+The library service can not be stopped to make images from the library: case: Server 1th Number: master server 2nd: From now on due to load problems need to be listed server 3rd is also 1th number of slave servers. However, no. 1th library can not stop, No. 2nd can not stop, master-slave synchronization process can not stop (demanding). Can do this: in the main: mysqldump-Uroot-Pxxxx-h127.0.0.1 --databases db1 DB2 db3--master-data=1 > Bak.sqlnumber 3rd server slave stop, and then Bak.sql import 3rd from the server slave the start;3 number server will automatically update from the node at the moment of export. Because--master-data=1 This parameter adds the change statement at the bottom of the SQL file after it is exported. If--master-data=0, it will not be with. Very convenient, but only suitable for the library is not too large case, the case exported the library altogether 6G.

MySQL 5.5 Master-Slave synchronization Setup Tutorial