Shulou(Shulou.com)06/01 Report--

Record the replication of ♪ (^ ∇ ^ *) 1, binaries and locations; 2. Master-slave replication based on global transaction identifier (GTID): (test environment is CentOS 7.3)

(1) based on binary log files (traditional method) (1), working principle: 1. Master records database operations to Binary log and sends them to the I / O thread of Slave; 2. I / O thread of Slave stores Binary log to local Relay log;3, SQL thread of Slave reads information from Relay log and updates it locally; (2) specific configuration

Master database (Master,192.168.100.51):

1. Modify the configuration file # vi / etc/my.cnf [mysqld] # the id needs to be set to unique server-id = 1 # Open binary log log-bin = bin-log2, create a replication account and enter mysql create user 'slave' @'% 'identified by' slavepass'; grant replication slave on *. * to 'slave' @'%; 3. Check the Master binary file name and location show master status

From database (SLAVE,192.168.100.52):

1. Modify the configuration file: # vi / etc/my.cnf [mysqld] # the id should be set to a unique server-id = 22, and the configuration connection # corresponds to the mysql > CHANGE MASTER TO MASTER_HOST='192.168.100.51', MASTER_USER='slave' of the replication account created by the master database. MASTER_PASSWORD='slavepass', MASTER_LOG_FILE=' corresponds to master server file, and MASTER_LOG_POS=' corresponds to master server pos. 3. Start SLAVE mysql > START SLAVE; 4, check the SLAVE status mysql > SHOW SLAVE STATUS\ G, check whether the Slave SQL thread and IO thread are running properly (3), test to create a database in the master database, add new tables and data, and check whether the slave database is updated. II. Replication based on global transaction identifiers (GTID) (1), principles

GTID is a new feature of MYSQL 5.6references. When a database transaction is committed, a corresponding GTID is generated in binlog, master and slave.

When replicating, the slave database will determine the location of synchronization through GTID, instead of looking for File and Position. Why is it global? because the pos used in the traditional way is different between master and slave. For GTID, the same transaction is consistent in both master and slave. In this way, when a master goes down and slave1 is upgraded to master, slave2 does not have to look for slave1's File and Position.

(2) specific configuration

Master database (Master,192.168.100.51):

1. Modify the configuration file # vi / etc/my.cnf [mysqld] # the id needs to be set to unique gtid_mode = ON server-id = 1 # Open binary log log-bin = bin-log enforce_gtid_consistency = ON 2, create replication account mysql > grant replication slave on *. * to 'slave' @'% 'identified by' slavepass'

From database (Master,192.168.100.52):

1. Modify the configuration file # vi / etc/my.cnf [mysqld] gtid_mode = ON server_id = 2 enforce_gtid_consistency = ON 2, and configure replication connection mysql > CHANGE MASTER TO MASTER_HOST='192.168.100.51', MASTER_USER='slave', MASTER_PASSWORD='slavepass' MASTER_AUTO_POSITION = 1 3. Start slave and view mysql > start slave; mysql > show slave status\ G

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.