[MySQL] Copy (3)-create a master-slave copy (copy from another server) _ MySQL

[MySQL] Replication (3)-Create master-slave replication (replication from another server) bitsCN.com

In the previous article, the creation of master-slave replication assumes that the master-slave databases are all the databases that have just been installed, that is to say, the data on the two servers is the same. this is not a typical case, in most cases, there is a master database that has been running for a period of time, and then synchronize it with a newly installed Slave Database. This article describes how to configure it in this case.

1. install MySQL on the slave database host. Note that the version of the slave database cannot be lower than that of the master database.

2. back up the master database, copy the backup file to the slave database, and restore the file to the slave database.

There are many ways to implement the above process. I will introduce how to use innobackupex to back up the master database online, so that you do not need to stop the master database (note: innobackupex is only applicable to MyISAM and innodb engines ).

First, execute the following statement on the master database for full backup:

innobackupex --defaults-file=/opt/mysql/my.cnf  --user=root --password=*** /backup/mysql/data  

Copy all the backup files to the same directory of the slave database, and then execute the following statement to restore:

innobackupex --defaults-file=/opt/mysql/my.cnf --user=root --password=***--use-memory=4G --apply-log /data/mysql/backup/2013-11-27_18-18-51innobackupex --defaults-file=/opt/mysql/my.cnf --user=root --password=***--copy-back /data/mysql/backup/2013-11-27_18-18-51

After the restoration is complete, start the MySQL server:

service mysqld start

3. start replication

Before the slave database starts replication, we need to create a backup account on the master database:

GRANT REPLICATION SLAVE ON *.* TO repluser@'192.168.1.%' IDENTIFIED BY 'replpwd';

Then, determine the log and its offset of the master database. Note that we cannot obtain it through show master status, because new logs are generated in the master database during the backup and replication process, so as not to cause data loss, we must obtain it from the backup log.

[mysql@lx16 2013-11-27_18-18-51]$ cat xtrabackup_binlog_infomysql-bin.000149        69191646

From the above backup log, we can obtain the log of the master database and its offset at the time of backup, so that we can start replication in the slave database through the following statement:

change master to        master_host='192.168.1.15',        master_user='repluser',        master_password='replpwd',        master_log_file='mysql-bin.000149',        master_log_pos=69191646; start slave;

After replication is started, the standby database status is as follows:

root@(none) 03:43:47>show slave status/G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.1.15                  Master_User: repluser                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000149          Read_Master_Log_Pos: 194385112               Relay_Log_File: mysqld-relay-bin.000002                Relay_Log_Pos: 125193719        Relay_Master_Log_File: mysql-bin.000149             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 194385112              Relay_Log_Space: 125193876              Until_Condition: None               Until_Log_File:                 Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 101 row in set (0.00 sec)

BitsCN.com