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
- The master logs the changes to the binary log. These logging processes are called binary log Events
- Slave copies master’s binary log events to its relay log.
- Slave reworks events in relay logs and applies changes to its own database. MySQL replication is asynchronous and serialized
2. Basic principles of replication
- Each slave has only one master
- Each slave can only have a unique server ID
- 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