My environment is using two DB instances, I wrote about my previous articles about DB instances. The following is only a reference role, need to understand its principles to understand the following configuration, or it will be wrong, the error can be private chat me.
1, open the main library Bin-loglog-bin =/data/3306/mysql-bin2, make sure Server-id is not the same 3, check, grep is not displayed [[email protected] 3306]# egrep " Log-bin|server-id "/data/3306/my.cnf [mysqld]log-bin =/data/3306/mysql-binserver-id = 14, see if Log-bin turns on show variables Like ' Log_bin '; 5. Create an account rep for copying from the library, authorizing grant replication Slave on the main library on * * to ' rep ' @ ' 10.0.0.% ' identified by ' oldboy123 '; flush Privileges;6, in the main library to do backup, the following is to add a read lock to the main library, do not let the user write data,,, this can also not lock, this is the official offer of the scheme, you can also add the mysqldump when the X Auto Lock flush table with read lock;7, Open another window, do a backup, and check the file and location before backup mysql>show master status; From this point on the backup, remember, if you do not see this at this time, then in the backup with Master-data=1 or 2, the difference is the future in the change master to write the file and location point, this can see the above figure mysql>show Master Logs;mysqldump-uroot-p123-s/data/3306/mysql.sock-a-B--events|gzip >/opt/rep.sql.gz here-A is all data, and-B is included in the library Statement 8, Unlock unlock tables;9, transfer the data from the main library back to the library, and import from the library mysql-uroot-p123-s/data/3307/mysql.sock </opt/rep.sql10, log in from the library, Write config change MASTER tomaster_host= ' 10.0.0.7 ', master_post=3306,master_user= ' rep ', master_password= ' 123 ', Master_log_ File= ' Mysql-bin.000003 ', master_log_pos=333; in the/data/3307 directory from the library will be saved again Master.info, save the login main library related information 11, in the library to open the synchronization of start slave; see if two threads are working show Slave status\g
MySQL Master-slave replication