MySQL Replication (3) Creating a master copy (copying from another server)

The creation of a master copy mentioned in the previous article assumes that the primary repository is a newly installed database. That means that the data on the two servers is the same, this is not a typical case, in most cases there is a master library that has been running for some time, and then synchronizes with a newly installed standby, this article describes how to configure it in this case.

1. Install MySQL on the standby host, note that the version of the standby library cannot be lower than the main library.

2, backup the main library, copy the backup files to the standby, and restore on the standby.

There are a number of ways to do this, and I'm here to use Innobackupex to back up the main library online so that you don't have to stop the main library (note: Innobackupex only applies to the MyISAM and InnoDB engines).

First, execute the following statement on the main library:

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

The backup file is then copied to the same directory as the Repository, followed by the following statement for recovery (note: The MySQL server must be closed before recovery and the original data and logs have been deleted):

Innobackupex--defaults-file=/opt/mysql/my.cnf--user=root--password=***--use-memory=4g--apply-log/data/mysql/ Backup/2013-11-27_18-18-51

Innobackupex--defaults-file=/opt/mysql/my.cnf--user=root--password=***--copy-back/data/mysql/backup/2013-11-27 _18-18-51

When the restore is complete, start the MySQL server:

Service mysqld Start

3, start the copy

Before the repository initiates replication, we need to create a backup account on the main library:

GRANT REPLICATION SLAVE on *.* to repluser@ ' 192.168.1.% ' identified by ' replpwd ';

Then, we want to determine the main library's log and its offset. It is important to note here that we cannot get it through show master, because the main library has a new log generation in the process of backup and replication, and we must obtain it from the backup log in order not to cause data loss.

[Mysql@lx16 2013-11-27_18-18-51]$ Cat Xtrabackup_binlog_info

mysql-bin.000149 69191646

From the backup log above we can get the log of the main library and its offsets at the time of the backup so that we can start the replication in the repository with 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 you start replication, the status of the Repository is as follows:

root@ (none) 03:43:47>show slave status\g *************************** 1. Row *************************** slave_io_state:waiting for master to send event Mast er_host:192.168.1.15 Master_user:repluser master_port:3306 Co  
               Nnect_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:my   
          sql-bin.000149 Slave_io_running:yes slave_sql_running:yes replicate_do_db: Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_W   
                 ild_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:0 Master_ssl_verify_server_cert:no last_io_errno:0 last_i   
             o_error:last_sql_errno:0 Last_sql_error:replicate_ignore_server_ids: Master_server_id:10 1 row in Set (0.00 sec)

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/