• notice
  • Congratulations on the launch of the Sought Tech site

MySQL master-slave copy, SSL-based master-slave replication, main copy and semi-synchronous replication

MySQL replication is divided into master-slave replication and master-master replication.

What is master-slave replication

Master-slave replication refers to having one server as the master server and one or more servers as the slave server.The slave server does not write any data, the source of the data is the master server.The purpose of replication is for data synchronization.If the slave server also writes data, in the master-slave architecture, the master server will not copy the data of the slave server, so the data will be out of sync, so the slave server cannot write.data.The basis of MySQL replication is the binary log, because the binary log records all SQL statements that can change or potentially change database data.When these SQL statements are copied from the server and executed again, the data will follow The main server is the same.Therefore, the MySQL version of the slave server must be higher than that of the master server or the same as the master server.It is best to have the same master and slave version, so that errors will not be caused due to different versions and different SQL statements.

What is master-master replication

Master-master replication means that two or more MySQL servers are master servers, and everyone replicates each other.The advantage of this is that each server can write data, and the master-slave, slave The server cannot perform write operations.The principle of master-master replication is similar to that of master-slave replication, except that the data are replicated and synchronized with each other.

Whether it is master-slave replication or master-master replication, the replication process is asynchronous.It can speed up the query of the data, and the writing of the data does not have any improvement.Master-slave or master-master replication can also achieve data redundancy.When the slave server is DOWN, the master server can work normally without any impact.When the master server is down, it is only necessary to temporarily switch the slave server to the master server.It is also convenient for data backup.When backing up, you can stop the slave server, then back up the data on the slave server, and then enable it after the backup is completed.

Implementation of master-slave replication

On START SLAVE, the slave creates an I/O thread to connect to the master and let the master send binary logs.

The master server creates a Binlog Dump thread to send the contents of the binary log to the slave server.Read main service from server I/O thread

The content sent by the Binlog Dump thread of the server and copy the data to a local file in the data directory of the slave server, ie relay

Log.The third thread is the SQL thread, which is used by the slave to read the relay log and perform the updates contained in the log.

The SHOW PROCESSLIST statement can query information about replication that occurs on both the master and slave servers.

The slave server creates two additional state files master.info and relay-log.info in the data directory.State file save

On the hard disk, it is not lost when the slave is shut down.The next time the slave server starts, read these files to make sure that it has started from the master server

How much binary log the server reads, and how well it processes its own relay logs.

The slave server flushes tables with read lock; after that, the data cannot be synchronized temporarily.Only after unlocking, the copied statement will be executed.

The slave server does not need to close the binary log or open the relay log, because the server will automatically open or close the account for you.If the binary log is not closed, the SQL statements copied from the master server will not be recorded in the binary log when executed.Only the SQL statements executed on the local machine will be recorded in the binary log.The relay log is stored in the data directory by default, and the file name is hostname.relay-00000N.If you want to save it to another location, you can specify it in the configuration file.

1.Install MySQL database (omitted)

2.Configure the master and slave servers

Main server operation

# vim/etc/my.cnf

server-id=1  //It must not be the same as the slave server, use the default here

log-bin=mysql-bin//Open an account to specify the MySQL binary log and storage location, the default account is opened , saved in the data directory

sync-binlog=1      //Synchronize to disk immediately, it is recommended to open an account

mysql> GRANT REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO repl@'1.1.1.2' IDENTIFIED BY '123456';

mysql> FLUSH PRIVILEGES;

REPLICATION CLIENT allows use of SHOW STATUS on replication master (Master) and slave (Slave)

   REPLICATION SLAVE allows replication slaves to connect to the master

Operate from server

# vi/etc/my.cnf

#log-bin=mysql-bin    //The # sign means closing, or not closing

server-id=11          //ID number, must not be the same as the main server

relay-log=relay-bin  //Define the location and file name of the relay log save, you can also not define it

relay-log-index=relay-bin-index  //The name and save location of the relay log index file

mysql> CHANGE MASTER TO MASTER_HOST='1.1.1.1',MASTER_USER='repl',MASTER_PASSWORD='123456';

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS;

You can see that the following two items are YES, indicating that the slave server is working normally

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

mysql> show processlist G

**************************** 1.row **************** **********

Id: 6

User: system user

Host:  

    db: NULL

Command: Connect

Time: 1159

State: Waiting for master to send event

Info: NULL

**************************** 2.row **************** **********

Id: 7

User: system user

Host:  

    db: NULL

Command: Connect

Time:-153412

State: Slave has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

You can see that there are two threads, one that waits for the master server to send data, called I/O, and one that reads the relay log and executes it, and then waits for the I/O thread on the slave to update the log, called SQL thread

Then create a database on the master server, check it on the slave server, you can see the database, indicating that it is OK.If the slave server does not close the binary log, the database is created on the master server.After the synchronization of the slave server is completed, you can see the SQL statement to create the database in the relay log of the slave server, but you cannot see it in the binary log.of.The binary log of the slave server only records the SQL statement executed directly on the slave server, but does not record the SQL statement copied from the master server and then executed.When creating a database directly on the slave server, you can see that the slave server's binary log records the statements.However, the master server will not synchronize the data of the slave server, which is master-slave replication.

You can also write the following configuration to the slave server without the trouble of CHANGE MASTER TO, and there are some other definitions.

//Configure the slave server/etc/my.cnf file and add the following:

server-id=2 # slave server ID number, not the same as the master ID

master-host=1.1.1.3 # Specify the master server IP address

master-user=repl # Specify the username that can be synced on the master server

master-password=123456 # password

master-port=3306 # The port used for synchronization

master-connect-retry=60 # Breakpoint reconnect time

replicate-ignore-db=mysql # Shield synchronization of mysql library

replicate-do-db=test1 # The name of the synchronized database

Common debugging commands and their explanations:

flush master;  //clear binary log

flush slave;    //Empty binary log

The above command, when the master-slave replication error, especially when the report field is repeated, clear the binary log of the master server and the slave server, and then use the CHANGE MASTER TO command to point to it, and then open an account on the slave server, it should be No error will be reported.

Note: The above two commands will clear the binary log.In the production environment, please make sure that the log has been backed up and no data is written.

show master status;    //View the binary files currently used by the master server and the location of the binary files

Note: If you don't want to clear the binary log, but the error is not easy to troubleshoot, you can specify the binary file and the location of the binary file used by the master server when specifying the master server, which means starting from the current binary file position, starting from copy.The previous content is not copied.Before that, you can export the data first and then import it to the slave server through the mysqldump command.To achieve data consistency, and then use the command to specify the binary file and location to ensure that the master-slave data is consistent.

The above, at this point, the master-slave replication is over.

Tags

Technical otaku

Sought technology together

Related Topic

1 Comments

author

order lipitor online & lt;a href="https://lipiws.top/"& gt;lipitor 10mg pill& lt;/a& gt; buy lipitor 40mg without preion

Quejwn

2024-03-09

Leave a Reply

+