1. Basic principles of replication

The basic principle of replication

The slave reads the binlog from the master for data synchronization, a three-step process of master/slave replication

  1. The master logs the changes to the binary log. These logging processes are called binary log Events
  2. Slave copies master’s binary log events to its relay log.
  3. Slave reworks events in relay logs and applies changes to its own database. MySQL replication is asynchronous and serialized

2. Basic principles of replication

  1. Each slave has only one master
  2. Each slave can only have a unique server ID
  3. Each master can have multiple salves

3. The biggest problem with replication

There is a delay problem due to multiple I/OS

4. Common configuration of one master and one slave

Prerequisites: The mysql versions are the same and the primary and secondary mysql servers are on the same network segment

Ping test

  • Linux ping in the Windows
[root@Heygo desktop]# ping 10.206.207.131 ping 10.206.207.131 (10.206.207.131) 56(84) bytes of data.64 bytes from 10.206.207.131: ICmp_seq =1 TTL =128 time=1.27 ms 64 bytes from 10.206.207.131: Icmp_seq =2 TTL =128 time=0.421 ms 64 bytes from 10.206.207.131: Icmp_seq =3 TTL =128 time=1.12 ms 64 bytes from 10.206.207.131: Icmp_seq =4 TTL =128 time= 0.159ms ^C -- 10.306.307.131 ping statistics -- 3 packets transmitted, 4 received 0% packet loss, time 3719ms RTT min/avg/ Max /mdev = 0.421/0.835/1.279/0.373ms [root@Heygo desktop]#Copy the code
  • Ping Linux in the Windows
C:\Users\Heygo>ping 192.168.152.129 Ping 192.168.152.129 with 32 bytes of data: reply from 192.168.152.129: bytes =32 time < 1ms TTL=64 reply from 192.168.152.129: bytes =32 time < 1ms TTL=64 reply from 192.168.152.129: bytes =32 time =1ms TTL=64 reply from 192.168.152.129: bytes =32 time < Packets: sent = 4, received = 4, lost = 0 (0% lost), estimated round-trip time (in milliseconds): Minimum = 0ms, maximum = 1ms, average = 0msCopy the code

Modify the my.ini configuration file for the host (Windows)

The master/slave configuration is in the [mysqld] node, both lowercase, the following is the teacher configuration file


The following two configurations are mandatory

  • Configuring host IDS
server-id=1
Copy the code
  • Enabling binary Logging
Log-bin =C:/Program Files (x86)/MySQL/MySQL Server 5.5/log-bin/mysqlbinCopy the code

The following configuration is optional

  • Startup error log
Log-err =C:/Program Files (x86)/MySQL/MySQL Server 5.5/log-bin/mysqlerrCopy the code
  • The root directory
Basedir ="C:/Program Files (x86)/MySQL/MySQL Server 5.5/"Copy the code
  • The temporary directory
Tmpdir ="C:/Program Files (x86)/MySQL/MySQL Server 5.5/"Copy the code
  • The data directory
Datadir ="C:/Program Files (x86)/MySQL/MySQL Server 5.5/Data/"Copy the code
  • Host, read and write can be
read-only=0
Copy the code
  • Set up databases that do not replicate
binlog-ignore-db=mysql
Copy the code
  • Set the data to be copied
Binlog-do-db = The name of the primary database to be replicatedCopy the code

Modify the my.cnf configuration file from the machine (Linux)

  • Unique ID of the secondary server
server-id=2
Copy the code
  • Optional Enable binary files

Preparations after modifying configuration files

The mysql service is restarted on both the host and the slave host because the configuration file is modified

  • Windows
net stop mysql
net start mysql
Copy the code
  • Linux
service mysqld restart
Copy the code

Disable the firewall on the slave host

  • Windows Manually disables the firewall
  • Disable the Linux firewall on the VM
service iptables stop
Copy the code

Resume account on Windows host and authorize slave

  • Create a user and authorize it
GRANT REPLICATION SLAVE ON *.* TO 'Backup account'@'Slave machine database IP' IDENTIFIED BY 'Account password';
GRANT REPLICATION SLAVE ON *.* TO 'Heygo'@'192.168.152.129' IDENTIFIED BY '123456';
Copy the code
  • Refreshing permission Information
flush privileges;
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Copy the code
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Copy the code
  • Query the status of the master and record File and Position. These two parameters are required to start the Slave
show master status;
Copy the code
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin000001. |      107 | mysql        |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Copy the code

Configure the host to be replicated on the Linux slave

  • Slave machine for authentication
CHANGE MASTER TO 
MASTER_HOST='host IP',
MASTER_USER='Create username',
MASTER_PASSWORD='Created password',
MASTER_LOG_FILE='File name',
MASTER_LOG_POS=The Position number; CHANGE MASTERTO 
MASTER_HOST='10.206.207.131',
MASTER_USER='Heygo',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107;
Copy the code
  • Enable replication from the secondary server
start slave;
Copy the code
  • Run Slave_SQL_Running:Yes and Slave_IO_Running:Yes to check whether the slave host is successfully connected to the host
show slave status\G;
Copy the code
  • How do I stop the slave service replication function
stop slave;
Copy the code