MySQL Replication (Master/Slave server) configuration instance

Part 1 configure MASTER (10.0.0.1)

1. Enable binary logging on the master database server and assign a unique server-id. Open the configuration file/etc/qa_my.cnf and add the command under the [mysqld] group:

[Mysqld]
Log-bin = mysql-bin
Server-id = 3

2. Restart the master server:

Mysqladmin-uroot-p shutdown
/Usr/local/mysql/bin/mysqld_safe -- user = mysql &

Part 2 configure SLAVE

1. configure a unique server-id for the slave server. Open the configuration file/etc/my. cnf and add the command below the [mysqld] group:

[Mysqld]
Server-id = 5

2. Restart the slave server:

Mysqldump-uroot-p shutdown
/Usr/local/mysq/bin/mysqld_safe -- user = mysql &

Part 3 get the coordinates of the MASTER BINARY LOG

1. Open a local terminal and add a read lock to the data table to prevent write operations:

Mysql-h 10.0.0.1-uxingwang-p
-> Flush tables with read lock;

2. Open another terminal locally and execute the following statement:

Mysql-h 10.0.0.1-uxingwang-p
-> Show master status;

In the query results, the File column indicates the current log File name, and the Position column indicates the Position in the File.

That is to say, now binlog records to this location.

Part 4 manually synchronize the master server to the slave server

1. Keep the first terminal opened in the previous part not closed;

2. copy the data files in the data directory of the master server to the slave server. Including the folder of the target data table and the ibdata * And ib_logfile * files of the innodb engine:

Mkdir/usr/local/mysql/data/test
Scp xingwang @ '10. 0.0.1 ':/usr/local/var/test/usr/local/mysql/data/test
Scp xingwang @ '10. 0.0.1 ':/usr/local/var/ibdata1/usr/local/mysql/data/
Scp xingwang @ '10. 0.0.1 ':/usr/local/var/ibdata2/usr/local/mysql/data/
Scp xingwang @ '10. 0.0.1 ':/usr/local/var/ib_logfile1/usr/local/mysql/data/
Scp xingwang @ '10. 0.0.1 ':/usr/local/var/ib_logfile2/usr/local/mysql/data/

Part 5 configure master server parameters on slave server

1. Open a terminal on the slave server and execute the following command:

Mysql-u root-p
-> CHANGE MASTER
MASTER_HOST = '10. 0.0.1 ',
MASTER_PORT = '123 ',
MASTER_USER = 'xingwang ',
MASTER_PASSWORD = '***',
MASTER_LOG_FILE = '',
MASETE_LOG_POS = '';

2. Run start salve;

Recommended reading:

Load Nginx in Ubuntu for high-performance WEB Server 5 --- MySQL master/Master Synchronization

Production Environment MySQL master/Master synchronization primary key conflict handling

MySQL Master/Slave failure error Got fatal error 1236

MySQL master-slave replication, implemented on a single server