This article mainly introduces the detailed analysis of replication in Mysql. It introduces the basic concepts, uses, implementation methods and centralized modes, and then shares the specific implementation code, which has certain reference value. Friends who need it can learn more.
1.mysql replication concept
refers to transmitting the DDL and DML operations of the primary database to the replication server through the binary log, and then transmitting them to the replication server on the replication server. These log files are re-executed, keeping the data on the replicate and master servers in sync. During the replication process, one server acts as the master and one or more other servers act as slaves. The master rewrites updates to binary log files and maintains an index of the files to track log rotation. These logs record updates sent to slave servers. When a slave connects to the master, it notifies the master of the location of the last successful update that the slave read in the log. The slave accepts any updates that have occurred since then, then blocks and waits for the master to be notified of new updates.
2. Purpose of replication
Synchronize data through master-slave replication, and then separate reading and writing ( mysql-proxy) to improve the concurrent load capacity of the database, or to be used as a primary and backup machine to ensure that the application can be switched to the backup machine to continue running in a short period of time after the host stops responding.
Advantages:
(1) The database cluster system has multiple database nodes. In the event of a single node failure, other normal nodes Services can continue to be provided.
(2) If a problem occurs on the master server, you can switch to the slave server
(3) Query operations can be performed on the slave server through replication, which reduces the access pressure on the master server and achieves data distribution and load balancing
(4) Backup can be performed on the slave server to avoid affecting the service of the master server during backup.
3. Replication implementation (3 methods)
(1) DRBD is a software-based, shared-nothing, Storage replication solution for mirroring block device content between servers.
(2) Mysql cluster (also known as mysql cluster). Mysql replication (replication) itself is a relatively simple structure, that is, a slave server (slave) reads the binary log from a master server (master) and then parses and applies it to itself.
(3) A simple replication environment only requires two hosts running mysql, and you can even start two mysqld instances on one physical server host. One serves as the master and the other serves as the slave to complete the configuration of the replication environment. However, in actual application environments, you can use the mysql replication function to build other replication architectures that are more conducive to expansion according to actual business needs, such as the most commonly used master-slave architecture.
The master-slave architecture refers to using one mysql server as the master, one or more mysql servers as the slave, and copying the master's data to the slave. In practical applications, the master-slave architecture mode is the most commonly used for mysql replication. Generally, under this architecture, the write operations of the system are performed in the master, while the read operations are dispersed to various slaves. Therefore, this architecture is particularly suitable for the high read and write problems of the current Internet.
Mysql database replication operation is roughly divided into the following steps:
(1) Master enables binary logs. The operation of enabling binary logs is described in detail in Log Management.
(2) The I/O process on the slave connects to the master and requests the log content from the specified position of the specified log file (or from the beginning of the log).
(3) After receiving the I/O process request from the slave, the master reads the log information after the specified position of the specified log according to the request information through the I/O process responsible for replication, and returns it to the slave's I/O. In addition to the information contained in the log, the returned information also includes the name of the bin-log file and the location of the bin-log in which the returned information has been sent to the master.
(4) After receiving the information, the Slave I/O process will add the received log content to the end of the relay-log file on the slave side, and read the file name and bin-log file name of the master side. The location is recorded in the master-info file.
(5) After Slave's sql process detects the new content in relay-log, it will immediately parse the content of relay-log and execute it on its own.
4. Centralized mode of mysql replication
In versions after mysql5.1, the improvement in replication is the introduction of new replication Technology - Row-based replication. This technology is to focus on the records that have changed in the table, rather than copying the previous binlog mode. Starting from mysql5.1.12, the following three modes can be used to achieve this.
(1) SQL statement-based replication (statement-base replication, sbr)
(2) Row-based replication (rbr)
(3) Mixed mode replication (mbr)
Correspondingly, there are three formats of binlog: statement, row, and mixed. In Mbr mode, sbr mode is the default. The format of binlog can be changed dynamically at runtime. The method of setting the master-slave replication mode is very simple. Just add another parameter based on the previously set replication configuration, as follows:
binlog_format=”statement” #binlog_format=”row” #binlog_format=”mixed”
Of course, you can also dynamically modify the binlog format at runtime
Mysql> set session binlog_format=”statement”
5. Control the main server Operation
Master: 192.168.11.139
Slave: 192.168.11.130
(1) Master server:
mysql> show variables like '%datadir%'; +---------------+--------------------------+ | Variable_name | Value | +---------------+--------------------------+ | datadir | /application/mysql/data/ | +---------------+--------------------------+
Enable binary logs on the main server:
mysql> show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | OFF | +---------------+-------+ row in set (0.00 sec)
OFF means the binary log is closed
3 steps to open the log:
①Open the mysql installation directory/my.cnf
② Find the label [mysqld], add the following line in the line below this label:
log_bin[filename]
In this statement, log- bin indicates to open the binary file; filename is the name of the binary log. If not specified, the default is the host name followed by -bin as the file name, which is stored in the datadir directory by default. Specify binary_log here. If you only generate binary files for the specified database, you need to add the following statement
Binlog-do-db=db_name(数据库名称)
If you do not generate binary files for the specified database Log, you need to add the following statement
Binlog-ignore-db-db_name(数据库名称)
③Restart the mysql service. You can see the "binary_log.numeric number" file in the mysql installation directory/data folder, such as binary_log.00001. Every time the mysql service is restarted in the future, the binary file will be regenerated, and the numerical number in the file name will increase.
After successful startup, modify the mysql configuration file my.cnf and set the server-id. The code is as follows
Server-id=1 Binlog-do-db=xscj Binlog-ignore-db=mysql Server-id=1:每一个数据库服务器都要指定一个唯一的server-id,通常主服务器为1,master和slave的server-id不能相同。 Binlog-do-db:表示需要复制的数据库,这里以xscj为例 Binlog-ignore-db:表示不需要复制的数据库
Create the user required for replication on the master
mysql> grant replication slave on *.* to rep_user@'%'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec mysql> show master status\G *************************** 1. row *************************** File: binary_log.000001 Position: 303 Binlog_Do_DB: Binlog_Ignore_DB: row in set (0.00 sec)
Back up the data of the master host and save it in /data /binary_dump.txt file, and then import it into the slave machine. The specific execution statements are as follows
[root@localhost bin]# mysqldump -h localhost>/data/binary_dump.txt
(2 ) Control slave server operations
Modify the database configuration file of the slave server, the configuration is as follows:
Server-id=2 ##设置从服务器id Master-host=192.168.11.129 Master-user=rep_user Master-password= ##设置连接主服务器的密码 Replicate-do-db ##设置你要同步的数据库,可以设置多个 Master-port=<port> ##配置端口号 重启slave,在slave主机的mysql重新执行如下命令,关闭slave服务 Mysql>stop slave; 设置slave实现复制相关的信息,执行如下命令 Mysql>change master to >master_host='', >master_user='', >master_password='', >master_log_file='binary_log.000007', >master_log_pos=120; 输入:show slave status\G用于提供有关从服务器线程的关键参数信息。
Commonly used commands are as follows
#Options | Function |
Start the replication thread | |
Stop replication thread | ##Reset slave |
Reset replication thread |
Show slave status |
Show replication thread status |
Show slave status\g |
Show replication thread status (displayed in separate lines) |
| Show master status\G
Show the status of the master database (displayed in separate lines) |
Show master logs |
Display master database log |
Change master to |
Dynamic changes to the configuration of the main database |
##Show processlistv |
Show which threads are running | Have you all learned it? Hurry up and give it a try. |
Summary of methods for copying table structures in mysql_MySQL
Copying data tables in MySQL Tutorial on how to transfer data to a new table_MySQL
Overview, installation, faults, techniques, and tools of MySQL replication (Shared by Huo Ding)
The above is the detailed content of Detailed analysis of copying in Mysql. For more information, please follow other related articles on the PHP Chinese website!

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

SecLists
SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

WebStorm Mac version
Useful JavaScript development tools

DVWA
Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software

Zend Studio 13.0.1
Powerful PHP integrated development environment