Create a MySQL replication with existing data

1. Configure the Master Library
You must turn on the second-level log and assign a unique server ID
• If you do not set Server-id or set it to the 0,master node, the slave connection will be rejected
• It is recommended to set innodb_flush_log_at_trx_commit=1 and sync_binlog=1 on the master node to ensure the persistence and consistency of replication environment transactions
• Do not turn on skip-networking on the master node, or the slave node cannot connect to the master node after the network is disconnected

[Mysqld]log-bin=mysql-binserver-id=1

Create a user for replication in the 2.master library

It is recommended to create a separate user for replication (user name and password will be stored in a plain text file master.info)

' Repl ' @'%.mydomain.com'slavepass'; MySQL ' Repl ' @'%.mydomain.com';

3. Get the current state of master

Starting replication must tell the slave node where to start copying, that is, the current location of the master node's binary log

(1) on the master node, open a session to execute the following command:

lock;

Executing this command will flush all the tables and block other writes to master. If it is a InnoDB table, the commit operation will also be blocked.
(2) on the master node, open another session to execute the following command:

MySQL > show master Status; +------------------+----------+--------------+------------------+| File             | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+| Mysql-bin. 000003        | Test         | manual,mysql     |+------------------+----------+--------------+------------------+

4. Configuring the Salve Library

Configure a unique Server-id bath the slave node can not turn on the binary log, but after the binary log is turned on, data backup and crash recovery operations can be performed on the slave node

If you turn on parameter report-host=hostname, you can use the command "show slave hosts" on the master node

[Mysqld]server-id=2

5. Data synchronization

Depends on how the data is synchronized in that way

With mysqldump

(1) Export data

The--master-data parameter automatically adds a "change Masterto" operation to the Salve node.

If you do not use this parameter, you need to execute the "flush tables with read lock" command before executing mysqldump, get the location of the binary log, and "Change Master to", and then manually release the lock after the mysqldump has finished executing.

(2) Start slave, plus parameter--skip-slave-start (do not turn on replication)

(3) Import data

shell> MySQL < fulldb.dump

Snapshot with bare data file

If the database is large, the efficiency of copying the bare data files is faster than Mysqdump. Preferably a cold copy of the shutdown database.

(1) Extract the data file copy to the appropriate directory

(2) Start slave, plus parameter--skip-slave-start (do not turn on replication)

6. Configure slave(name and location of binary files) using master location information

mysql> Change Master         to-, master_host='master_host_name',         -master_user='replication_user_name',    - >     master_password='replication_password',         master_log_file='recorded_log_file_name',         master_ Log_pos=recorded_log_position;

7. Start slave

Create a MySQL replication with existing data