1. Master/slave replication

Master/slave replication principle:

The mysql database provides a master-slave backup mechanism. In fact, all the data of the primary database is written to the backup database at the same time. Hot backup of mysql database.

To implement hot standby, you must first understand the requirements of the versions of the primary and secondary database servers. All versions of mysql to implement hot standby are older than 3.2. As a general rule, the data version of the slave database can be higher than the master database, but not lower than the master database.

REPLICATION can also be implemented using Heartbeat software. The basic operation of REPLICATION is to execute the SQL that has been executed on the primary server again on the slave server. As long as the initial database state (database structure, data, configuration) of the two machines is the same, we can ensure that they are always in the same state once master-slave replication is enabled. These are all implemented by mysql itself, so we can configure them.

As you can see from the graph, the main server needs to do a very simple task, which is to execute the SQL statement to the binary log file, while the secondary server needs to do a lot of work, and also has to use two threads for the whole event monitoring and processing.

Our master-slave replication consists of three steps:

The master logs the changes to the binary log. These logging processes are called binary log events. The slave copies the 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. The basic principle of

Due to the nature of master-slave replication, there are some principles to follow in order to ensure data consistency:

  • Each slave has only one master
  • Each slave can only have a unique server ID
  • Each master can have multiple salves

1.1 Environment Preparation:

  • A Server (Master server) : 192.0.0.131

  • B Server (Slave server) : 192.0.0.188

  • The Mysql version on both the primary and secondary servers is 5.6

  • Ensure that port 3306 of the firewall is enabled

  • The data table structure and configuration of both server databases must be the same.

1.2 main server Settings:

SQL > create user with mysql on primary server

CREATE USER 'slave'@The '%' IDENTIFIED BY '123456';

GRANT ALL PRIVILEGES ON *.* TO slave@"%" IDENTIFIED BY "123456";
Copy the code

Modify the my.cnf configuration file

log-bin=mysql-bin
server-id=1
Copy the code

Description:

    1. Log-bin: enables binary logging, which is used to write log files when transactions are committed. The default size is 1 gb, followed by 001,002.
    1. Server-id, which uniquely identifies the host. The primary and secondary mysql instances are configured differently. This value defaults to 0, and if it is 0, the master server rejects any connections from the slave server.

Other configurations (not required) :

  • Mysql > select * from binlog-do-db=db_001; mysql > select * from binlog-do-db=db_001;

  • Mysql > select * from binlog-ignore-db;

  • 3, binlog_format = mixed # configure the format of binlog

  • Read -only = 0 # Whether the configuration is read-only 0 indicates that it is not read-only and 1 indicates that it is read-only

  • Increament = 10 # increament = 10 # increament = 10 # increament = 10 #

  • Auto-increment-offset = 1 # Indicates the serial number of the server. The value starts from 1 and does not exceed auto-increament-increment

Restarting the Database

systemctl restart mysqld.service
Copy the code

1.3 Setting of secondary server:

Configure my CNF

server-id=2
replicate-do-db=test
skip-slave-start=true
Copy the code

Note: If you are using a virtual machine and the slave machine is cloned from the master host, you will need to do this step.

Mysql > select * from mysql;

The default address is datadir
cd /var/lib/mysql

vim auto.cnf
Copy the code

Change the uUID to a different one.

Restarting the Database

systemctl restart mysqld.service
Copy the code

1.4. Configure primary authentication for the secondary server

Get information about binlog

Mysql > select binlog from master server;

show master status;
Copy the code

Here is the file name of the binlog used by our current master server, where position is the offset in the file, which will be used to configure the slave later. This file will be different after each state change of the server

Enter the mysql server interface and enter the following command:

stop slave; // It is important to stop the slave service thread first. If you do not do this, the following operation will not succeed.Copy the code
change master to 
master_host='192.0.0.131',
master_user='slave',
master_password='123456',
master_log_file='mysql-bin.000002', 
master_log_pos=1472;
Copy the code

Here, user and password are the username and password created on the master in the first step, and MASTER_LOG_FILE is the binlog file used by the master in the previous step (this file is different after each state change on the master). MASTER_LOG_POS is the binlog offset used for synchronous scanning. Master_log_file corresponds to File and master_log_pos corresponds to Position. Mysql 5.x and later does not support specifying the primary server in the configuration file.

It doesn’t matter if the throw warning is executed, as long as there are no exceptions.

Start the slave thread on the slave server

start slave;
Copy the code

View the Slave status

show slave status\G;
Copy the code

Seeing two yes’s in the figure means that our slave server is fully up and running.

Now we can make a change on the primary database to see if it is synchronized to the secondary database to prove availability.

1.5. Master/slave verification

Create a new table user_test in the test library.

It is important to note here that both databases are executed

Create a new library
CREATE DATABASE test;

# specify code
CREATE DATABASE IF NOT EXISTS my_db default charset utf8 COLLATE utf8_general_ci;

Create a new table
create table user_test( id int comment'ID',name VARCHAR(20) comment'name', create_time timestamp DEFAULT now() comment'Creation time' );
Copy the code

All ready, ready for test

Master library query:

Query from library:

Now we manually insert a piece of data into the main library

use test;

insert INTO user_test value(1,"Zhang",NOW());
Copy the code

Query directly from the library to see if there is data synchronization

It is clear that the data has been synchronized, indicating that our master/slave replication configuration is Ok.

2. Dual-system hot backup

To realize dual-system hot backup, the principle is to do the mutual master and slave of two machines. We switch the master and slave steps above, and then do it again to achieve dual-system hot backup

Use the configuration file my.cnf for the two servers

Server A configuration:

log-bin=mysql-bin
server-id=1

Add hot standby
log-slave-updates
sync_binlog = 1
auto_increment_offset = 1
auto_increment_increment = 2

Copy the code

Server B Configuration

log-bin=mysql-bin
server-id=2

Add hot standby
log-slave-updates
sync_binlog = 1
auto_increment_offset = 1
auto_increment_increment = 2

Copy the code

To begin testing — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — >

Delete A file from server A to view the data on server B

Add and delete an item on server B to view the data on server A

The result shows that dual-system hot backup is also successful. The above is just an implementation idea, and the specific configuration depends on your actual situation. The following are some configuration parameters that will be used:

  • Server-id: The ID value uniquely identifies the primary and secondary servers in the replication cluster, so they must be different. The Master_id value must be a positive integer between 1 and 232-1, and the slave_id value must be a positive integer between 2 and 232-1.

  • Log-bin: enables binlog. If this option is enabled, the Slave can write to the Slave relay Log through I/O and replication can be performed before this option is enabled.

  • Binlog-do-db: indicates the database for recording binary logs. If you have more than one data, separate it with commas, or use multiple binlog-do-dg options.

  • Binglog-ingore-db: indicates a database that does not need to record binary logs. If there are multiple databases, separate them by commas, or use the multi-binglog-ignore-db option.

  • Replicate-do-db (configuration from a database) : Represents a database that needs to be synchronized if multiple data is separated by commas, or multiple replicate-do-DB options are used.

  • Replicate-ignore-db (replication from database configuration) : Represents a database that does not need to be synchronized if there are multiple databases separated by commas, or multiple replicate-ignore-DB options.

  • Master-connect-retry: master-connect-retry=n Indicates that the connection between the secondary server and the primary server is not successful. The management mode will be restarted after n seconds (60 seconds by default). If the mater. Info file exists on the slave server, it will ignore some options.

  • Log-slave-updates: Configures whether updates to the slave database are written to binary files. If this slave database is the master database of another slave database, this parameter is required so that the slave database can synchronize logs.

  • Skip-slave-start The slave does not automatically start after the slave server restarts. You need to manually start the slave to prevent errors

3. Frequently asked Questions

The mysql binary log file is full and the service is unavailable

Disk is full writing './mysql-bin.000032' (Errcode: 15753600 - No space left on device). Waiting for someone to free space...
Copy the code

3.1. Log files can be automatically deleted through the following Settings

Vim /etc/my. CNF // Change the value of expire_logs_days. X is the number of days on which binary logs are automatically deleted. The default value is 0, which means "Not automatically deleted"Copy the code

This method requires a mysql restart

You can set expire_logs_days to expire_logs_days without restarting mysql

show binary logs;
show variables like '%log%';
set global expire_logs_days = 10;
Copy the code

3.2 manually clear the binlog file

So let’s log in to MySQL

PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY); PURGE MASTER LOGS show MASTER LOGS PURGE MASTER LOGS show MASTER LOGS PURGE MASTER LOGSCopy the code

You can also reset master and delete all binlog files:

reset master; // Appendix 3 describes the effect of clearing binlog from mysqlCopy the code