This article brings you an introduction to the MySQL two-way backup method (with code). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you.
MySQL two-way backup is also called master-master backup, that is, both MySQL services are Master, and any one of them is the Slave of another service.
Preparation
Server
MySQL Server
Version
IP Address
masterA
5.6.41
192.168.1.201
##masterB
5.6.41
192.168.1.202
Note:
The backup MySQL server version should be kept consistent as much as possible. Different versions may have binary log formats. Not compatible.
Specific operation
AttentionDuring the operation, pay attention to the consistency of the data on both sides! ! ! masterA configurationmy.cnf
[mysqld]
# 服务器唯一标识
server-id=1
# 二进制日志文件名
log-bin=mysql-bin
# 需要备份的数据库，多个数据库用 , 分隔
binlog-do-db=piumnl
# 需要复制的数据库，多个数据库用 , 分隔
replicate-do-db=piumnl
# 中继日志文件名
relay_log=mysqld-relay-bin
# 手动启动同步服务，避免突然宕机导致的数据日志不同步
skip-slave-start=ON
# 互为主从需要加入这一行
log-slave-updates=ON
# 禁用符号链接，防止安全风险，可不加
symbolic-links=0
# 可不加
# resolve - [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
master-info-repository=table
relay-log-info-repository=table
relay-log-recovery=1
# 可不加
# 禁用 dns 解析，会使授权时使用的域名无效
skip-host-cache
skip-name-resolve
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
Copy after login
masterB configurationmy.cnf
# 不再解释各个配置项
[mysqld]
server-id=2
log-bin=mysql-bin
binlog-do-db=piumnl
replicate-do-db=piumnl
relay_log=mysql-relay-bin
skip-slave-start=ON
log-slave-updates=ON
symbolic-links=0
# resolve - [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
master-info-repository=table
relay-log-info-repository=table
relay-log-recovery=1
skip-host-cache
skip-name-resolve
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
Copy after login
Create backup usermasterA & masterB both need to create a backup user:
create user 'rep'@'%' identified by 'rep'; # 创建一个账户
grant replication slave on *.* to 'rep'@'%'; # 授予该账户对任意数据库任意表的主从备份权限
Copy after login
Note:
MySQL under Linux is closed for the
root@% usergrant_privPermissions, so if it is a remote login, authorization failure will occur
The backup user account and password here may not be consistent, here to simplify the operation, use the same account and password
Restart the serverRestart the serverEnable backupmasterAView masterB status
show slave status\G;
# 查看最重要的两项，两个都必须为 Yes ，有一个为 No 都要去查看错误日志文件，看看什么地方存在问题
# Slave_IO_Running: Yes
# Slave_SQL_Running: Yes
Copy after login
masterB
Reverse the operation of masterA
TestInsert data into masterA and masterB respectively, and check whether the expected data appears on the other server in time ProblemMySQL Slave Failed to Open the Relay LogThis should be a problem with the relay log. You can try the following operations
stop slave;
flush logs;
start slave;
Copy after login
Got fatal error 1236 from master when reading data from binary logWhen pulling the log from the main library, it is found that the first file in the mysql_bin.index file of the main library does not exist.
The above is the detailed content of Introduction to MySQL two-way backup method (with code). For more information, please follow other related articles on the PHP Chinese website!
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Big data structure processing skills: Chunking: Break down the data set and process it in chunks to reduce memory consumption. Generator: Generate data items one by one without loading the entire data set, suitable for unlimited data sets. Streaming: Read files or query results line by line, suitable for large files or remote data. External storage: For very large data sets, store the data in a database or NoSQL.
Backing up and restoring a MySQL database in PHP can be achieved by following these steps: Back up the database: Use the mysqldump command to dump the database into a SQL file. Restore database: Use the mysql command to restore the database from SQL files.
MySQL query performance can be optimized by building indexes that reduce lookup time from linear complexity to logarithmic complexity. Use PreparedStatements to prevent SQL injection and improve query performance. Limit query results and reduce the amount of data processed by the server. Optimize join queries, including using appropriate join types, creating indexes, and considering using subqueries. Analyze queries to identify bottlenecks; use caching to reduce database load; optimize PHP code to minimize overhead.
How to insert data into MySQL table? Connect to the database: Use mysqli to establish a connection to the database. Prepare the SQL query: Write an INSERT statement to specify the columns and values to be inserted. Execute query: Use the query() method to execute the insertion query. If successful, a confirmation message will be output.
To use MySQL stored procedures in PHP: Use PDO or the MySQLi extension to connect to a MySQL database. Prepare the statement to call the stored procedure. Execute the stored procedure. Process the result set (if the stored procedure returns results). Close the database connection.
Creating a MySQL table using PHP requires the following steps: Connect to the database. Create the database if it does not exist. Select a database. Create table. Execute the query. Close the connection.
One of the major changes introduced in MySQL 8.4 (the latest LTS release as of 2024) is that the "MySQL Native Password" plugin is no longer enabled by default. Further, MySQL 9.0 removes this plugin completely.
This change affects PHP and other app
Oracle database and MySQL are both databases based on the relational model, but Oracle is superior in terms of compatibility, scalability, data types and security; while MySQL focuses on speed and flexibility and is more suitable for small to medium-sized data sets. . ① Oracle provides a wide range of data types, ② provides advanced security features, ③ is suitable for enterprise-level applications; ① MySQL supports NoSQL data types, ② has fewer security measures, and ③ is suitable for small to medium-sized applications.
