MySQL5.6 master-slave replication (simple version)
There are several common Replication topologies:
1. Single Master---Multiple Slaves
Slaves do not communicate with each other, only with masters
Apply to application scenarios with high reading pressure, distribute reading operations to different slaves, and reduce master pressure
2. Multiple masters---Single slave
mysql does not seem to be supported for the time being. Personally, I think it can be implemented through slave multiple instances.
3. Active mode Master---Master
The current master is the slave of another master.
Problem: Updating an operation at the same time creates conflicts
4. Master---Master in active-passive mode
There can only be one readable.
5. Master---Slaves--Slaves
Replication in structure 1 causes too many threads for slave to connect to master, and replication pressure is too high
Cascading can relieve the pressure on the master to some extent, but log_slave_updates=1 is required.
Each slave is the master of the subordinate slave, and all redo relay logs also need to be written into binlog.
The following is the most basic installation of a single slave single master
Step 1: Preparation
Operating System: Centos 7.1
Database: mysql-5.6.30
Host: 172.17.220.145
From host: 172.17.220.220
Step 2: Set up the firewall
Centos 7.1 default firewall is firewalld, you need to install iptables-service through yum
In the master and slave servers respectively:
vi /etc/sysconfig/iptables
Add after "-A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT"
-A INPUT -p tcp -m state --state NEW -m tcp --dport 3306 -j ACCEPT
systemctl start iptables.service
Step 3: Modify the configuration file my.cnf of the master database
server-id=145 (ID value of the primary server, recommended as IP last paragraph)
log_bin = /opt/mysql/data1/mysql-bin.log (binary log)
log_slave_updates = 0 (0 means slave writes replication events to its binary log)
binlog_format=MIXED (binary log type)
Step 4: Modify the configuration file my.cnf from the database
server-id=224
log_bin = /opt/mysql/data1/mysql-bin.log(binary must be enabled if the slave is the master of another slave)
#relay_log=
read_only=1 (prevents data modification from the library, read only from the library)
log_slave_updates = 1
Step 5: Create a Master User
Log in to the main database
mysql> GRANT REPLICATION SLAVE ON *.* TO 'mypri'@'172.17.220.145' IDENTIFIED BY '123456';
Step 6: Restart the master-slave database
service mysql restart
Step 7: If Master Inventory is in Data
5.1 Lock the table to the main database first, and prevent data from being written again
mysql> flush tables with read lock;
5.2 mysqldump -uroot -p --all-databases > all.sql (export all databases)
5.3 If there is no data in the slave library, import all.sql to the slave library.
mysql -uroot -p
