MySQL Master-slave replication

Test environment: Master:centos 6.6 (x86_64), Mysql5.6.22slave:windows 8 (x86_64), MySQL5.6.19 related nouns: master-slave copy, read/write separation, data backup Replication allows data from the master database to be replicated to multiple slave databases, replication is asynchronous by default, so slave does not need a permanent connection to Master,slave can be out of line, once slave online, Will synchronize the latest data increment of master, replication allows copying the entire database, or a database, or a table. 1. Close the master database, edit the master's/usr/my.cnf,my.ini, and add the configuration:

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

Innodb_flush_log_at_trx_commit=1

Sync_binlog=1

Restart MySQL 2, close the slave database, edit the slave My.ini, add configuration

[mysqld]server-id=2

Restart MySQL 3 to create a replication account on master

CREATE USER ‘repl‘@‘%.mydomain.com‘ IDENTIFIED BY ‘slavepass‘;GRANT REPLICATION SLAVE ON *.* TO ‘repl‘@‘%.mydomain.com‘;

FLUSH privileges;

4, get the name and location of the master database binary log opens the first master session locks the database, allowing only read operations

FLUSH TABLES WITH READ LOCK;

Open another master session to see the binary log name, position

SHOW MASTER STATUS;+------------------+----------+--------------+------------------+| File             | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+| mysql-bin.000003 |       | Test         | manual,mysql     |+------------------+----------+--------------+------------------+

If your master database is not using binary log before, then the name and postion you see are empty, then name= ' in later operations, position=4 if your database already has data, You need to import into slave before replication, keep the master read lock, and use mysqldump to export the master data and import it into the slave database. Please refer to step 5th for details. If your database does not have data before it, or if you do not want the previous data, you can release the Master lock and release it in 2 ways: 1) directly exiting the first session2) execution:

UNLOCK TABLES;

Then skip to section 7th 5 and use mysqldump on master to create a data snapshot description: The purpose of this section is that if master has data before replication, you need to export the data from the master database before replication. It is then imported into the slave database, which can be replication after this operation is completed, and if your master does not have data before it, skip this section. On the master machine's shell, execute the following command to export the entire DB to the Dbdump.db file. (plus--master-data to lock the table in the export, then the data, and finally release the lock)-uroot-p parameter is optional, is to specify the master database account password

mysqldump -uroot -p --all-databases --master-data > dbdump.db

6, import data into the slave database-uroot-p parameter optional, is to specify the slave database account password

mysql -uroot -p < dbdump.db

7. Set the master database connection information on slave

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;

8, start slave thread

START SLAVE;

9, when you do not want to replication, you can stop the slave thread on Slave

STOP SLAVE;

You can also restart the start Slave,replication mechanism automatically synchronizes the data updates from the time master to slave. 10, think: In the project we can use replication to achieve database read and write separation, thereby improving server performance. The master database is used for writing, and the slave database is used for reading. http://dev.mysql.com/doc/connector-j/en/connector-j-master-slave-replication-connection.html 11, Reference:/http Dev.mysql.com/doc/refman/5.6/en/replication.html 12,

MySQL Master-slave replication