MySQL Master-Slave replication (Master-Slave)

1 replication overview the built-in replication function of Mysql is the basis for building large and high-performance applications

1 replication overview the built-in replication function of Mysql is the basis for building large and high-performance applications

1 copy Overview

The built-in replication function of Mysql is the basis for building large and high-performance applications. This distribution mechanism replicates data from a Mysql host to another Server Load balancer instance, and execute it again. During the replication process, one server acts as the master server, and one or more other servers act as slave servers. The master server writes updates to the binary log file and maintains an index of the file to track log loops. These logs can record updates sent to the slave server. When a slave server connects to the master server, it notifies the master server of the last successful update location read from the server in the log. The slave server receives any updates from that time, blocks them, and waits for the master server to notify you of new updates.

Note that when you perform a copy, all updates to the tables in the copy must be performed on the master server. Otherwise, you must be careful to avoid conflicts between updates to tables on the master server and those on the slave server.

1.1 mysql supports the following replication types:

(1) Statement-based replication: SQL statements executed on the master server run the same statement on the slave server. MySQL uses statement-based replication by default, which is more efficient. The row-based replication option is automatically selected once the exact replication fails.
(2) Row-based replication: copy the changed content, instead of executing the command on the slave server. It is supported from mysql5.0.
(3) hybrid replication: Statement-based replication is used by default. If statement-based replication fails, row-based replication is used.

1.2 problems solved by Replication

MySQL replication technology has the following features:
(1) Data distribution)
(2) load balancing)
(3) backup (Backups)
(4) High availability and fault tolerance High availability and failover

1.3 How replication works

In general, there are three steps for replication:

(1) The master will record changes to binary logs (these records are called binary log events and binary log events );
(2) slave copies the binary log events of the master to its relay log );
(3) slave redo the events in the relay log, which will change and reflect its own data.

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151111/121K95423-0.jpg "title =" mysql_master_slave.jpg "alt =" wKiom1SOwveBlyTdAADpsdSzYhI977.jpg "/>

2. Copy configurations

There are two MySQL database servers: Master and slave. The Master is the Master server. The slave is the slave server. In the initial state, the data information in Master and slave is the same. When the data in Master changes, slave also changes accordingly, so that the master and slave data information is synchronized to achieve the purpose of backup.

Key points:

The media responsible for transmitting various modification actions on the master and slave servers is the binary change log of the master server, which records the various modification actions to be transmitted to the slave server. Therefore, the master server must activate the binary log function. The slave server must have the permission to connect to the master server and request the master server to transmit the binary change log to it.

1. Install MySQL

Note: perform the following operations on the two MySQL servers 192.168.21.169 and 192.168.21.168 respectively to install MySQL 5.5.22.

2. Configure the MySQL master server (192.168.21.169)

Mysql-u root-p # Enter MySQL console create database AMT_DB; # create database AMT_DBinsert into mysql. user (Host, User, Password) values ('localhost', 'archermind', password ('000000'); # create the user archermind, create a MySQL master-slave database to synchronize the user's replication password 123456 flush privileges; # refresh the System Authorization Table # authorize the user's replication to only access the database above the master server 192.168.21.169 from the IP address 192.168.21.168, and only have the database backup permission grant replication slave on *. * to 'replicase' @ '192. 168.21.168 'identified by '20140901' with grant option;

3. Import the database AMT_DB in MySQL master server 192.168.21.169 to MySQL slave server 192.168.21.168.
1. Export the database AMT_DB

Mysqldump-u root-p AMT_DB>/home/replication. SQL # operate on the MySQL master server and export the database AMT_DB to/home/replication. SQL

Note:Before exporting data, you can log on to the MySQL console and run the following command:

Flush tables with read lock; # Database read-only lock command to prevent data from being written into the unlock tables during Database Export; # unlock

2. Import the database to the MySQL slave server

Mysql-u root-p # Go To the create database AMT_DB from the MySQL console of the server; # create a database use AMT_DB # Go To The database source/home/replication. SQL # import the backup file to the database mysql-u replication-h 192.168.21.169-p # test logon to the master server on the slave server

4. Configure the my. cnf file of the MySQL master server

# Vim/etc/my. cnf # edit the configuration file. In the [mysqld] section, add the following content: server-id = 1 # Set the server id. If it is set to 1, it indicates the master server. Note: if this line already exists in the original configuration file, you do not need to add it again. Log_bin = mysql-bin # Start the MySQ binary log system. Note: if this line already exists in the original configuration file, you do not need to add it again. Binlog-do-db = AMT_DB # Name of the database to be synchronized. If multiple databases exist, repeat this parameter, binlog-ignore-db = mysql # Do not synchronize mysql System database # service mysqld restart # restart MySQL

Mysql-u root-p # enter the mysql console show master status; view the master server, the following similar information appears + ------------------ + ---------- + ------------ + ratio + | File | Position | Binlog_Do_DB | usage | + usage + ---------- + ------------ + usage + | mysql-bin.000001 | 7131 | AMT_DB | mysql | + ------------------ + ---------- + -------------- + -------------------- + 1 row in set (0.00 sec)

Note:Remember the value of File: mysql-bin.000001 and Position: 7131, which will be used later.
5. Configure the MySQL slave server's my. cnf File

# Vim/etc/my. cnf # edit the configuration file. In the [mysqld] section, add the following content: server-id = 2 # the configuration file contains a line of server-id = 1, and change the value to 2, start the MySQ binary log system from the Database log-bin = mysql-bin #. Note: if this line already exists in the original configuration file, you do not need to add it any more. Replicate-do-db = AMT_DB # Name of the database to be synchronized. If multiple databases exist, repeat this parameter, replicate-ignore-db = mysql # Do not synchronize mysql System Database: wq! # Save and exit # service mysqld restart # restart MySQL

Note:MySQL 5.1.7 and later versions do not support writing master configuration attributes to the my. cnf configuration file. You only need to write the synchronized database and the database to be ignored.

Mysql-u root-p # enter the MySQL console slave stop; # stop the slave synchronization process change master to master_host = '2017. 168.21.169 ', master_user = 'replicase', master_password = '000000', master_log_file = 'mysql-bin.000001', master_log_pos = 123456; # Run the synchronization statement slave start; # enable the slave synchronization process show slave status \ G # view the slave synchronization information, the following content is displayed: *************************** 1. row ************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.21.169 Master_User: replication Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 connector: 7131 Relay_Log_File: MySQLSlave-relay-bin.000001 Relay_Log_Pos: 253 connector: mysql-bin.000001 connector: Yes connector: AMT_DB connector: mysql connector: replicate_Ignore_Table: 1 row in set (0.00 sec)

Note:
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
The values of the preceding two parameters are Yes, indicating that the configuration is successful!

6. Test whether MySQL Master/Slave server hot standby is successful
1. Enter the MySQL master server

Mysql-u root-p # enter the master server MySQL console use AMT_DB # enter the database CREATETABLE test (id int not null primary key, name char (20); # create test

2. log on to the MySQL slave server

Mysql-u root-p # enter the MySQL console use AMT_DB # enter the database show tables; # view the structure of the AMT_DB table. A new table test is displayed, indicating that the Database Synchronization is successful.

So far, the MySQL database has configured a master-slave server for dual-host hot backup.