Mysql主从同步安装及配置
MYSQL主从同步是目前使用比较广泛的数据库架构,技术比较成熟,配置也不复杂,特别是对于负载比较大的网站,主从同步能够有效缓解数据库读写的压力。 MySQL主从同步的机制 MYSQL主从同步是在MySQL主从复制(Master-Slave Replication)基础上实现的,通过设置
MYSQL主从同步是目前使用比较广泛的数据库架构,技术比较成熟,配置也不复杂,特别是对于负载比较大的网站,主从同步能够有效缓解数据库读写的压力。
MySQL主从同步的机制
MYSQL主从同步是在MySQL主从复制(Master-Slave Replication)基础上实现的,通过设置在Master MySQL上的binlog(使其处于打开状态),Slave MySQL上通过一个I/O线程从Master MySQL上读取binlog,然后传输到Slave MySQL的中继日志中,然后Slave MySQL的SQL线程从中继日志中读取中继日志,然后应用到Slave MySQL的数据库中。这样实现了主从数据同步功能。
MySQL主从同步的作用
1、可以作为一种备份机制,相当于热备份2、可以用来做读写分离,均衡数据库负载
环境:
主从服务器上的MySQL数据库版本同为MySQL_5.1.58
主机IP:10.200.37.177
从机IP:10.200.37.178
先把MySQL_5.1.58-1.rhel5.x86_64.zip文件get到服务器上。
安装mysql。
unzip MySQL_5.1.58-1.rhel5.x86_64.zip
rpm -ivh MySQL-client-community-5.1.58-1.rhel5.x86_64.rpmrpm -ivh MySQL-devel-community-5.1.58-1.rhel5.x86_64.rpm
rpm -ivh MySQL-server-community-5.1.58-1.rhel5.x86_64.rpm
rpm -ivh MySQL-shared-community-5.1.58-1.rhel5.x86_64.rpm
通过rpm 安装的mysql是没有/etc/my.cnf配置文件的。
默认路径如下:
数据库目录:/var/lib/mysql/
配置文件:/usr/share/mysql
相关命令:/usr/bin(mysqladmin、mysqldump等命令)(*mysql的一种安全启动方式:/usr/bin/mysqld_safe --user=root &)
启动脚本:/etc/rc.d/init.d/
因此,将目录/usr/share/mysql下的文件my-huge.cnf拷贝到/etc/下并改名为my.cnf
从数据库同理进行设置。
MySQL主从同步的步骤
一、主数据库master修改
1、修改MySQL配置:
vim /etc/my.cnf
[mysqld]
# 日志文件名
log-bin=mysql-bin
# 主数据库端ID号
server-id = 1
2、启动mysql,创建用于同步的账户:
/etc/init.d/mysql start
# 创建slave帐号slave_account,密码123456
mysql>grant replication slave on *.* to 'slave_account'@'%' identified by '123456';
mysql>flush privileges;
3、查询master的状态
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | | |
+------------------+----------+--------------+------------------+
注:此时主数据库基本修改完毕。
二、从数据库slave修改
1、修改MySQL配置:
vim /etc/my.cnf
# 从数据库端ID号
server-id =2
2、执行同步命令
# 执行同步命令,设置主数据库ip,同步帐号密码,同步位置
mysql>change master to master_host='10.200.37.177',master_user='slave_account',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=106;
# 开启同步功能
mysql>start slave;
3、检查从数据库状态:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.200.37.177
Master_User: slave_account
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 816
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 961
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 816
Relay_Log_Space: 1120
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
注:Slave_IO_Running及Slave_SQL_Running进程必须正常运行,即YES状态,否则说明同步失败。
三、可能用到的相关参数
1、master端:
# 不同步哪些数据库
binlog-ignore-db =
dbname
# 只同步哪些数据库,除此之外,其他不同步
binlog-do-db =
dbname
# 日志保留时间
expire_logs_days = 10
# 控制binlog的写入频率。每执行多少次事务写入一次
# 这个参数性能消耗很大,但可减小MySQL崩溃造成的损失
sync_binlog = 5
# 日志格式,建议mixed
# statement 保存SQL语句
# row 保存影响记录数据
# mixed 前面两种的结合
binlog_format = mixed
2、slave端:
# 停止主从同步
mysql> stop slave;
# 连接断开时,重新连接超时时间
mysql> change master to master_connect_retry=50;
# 开启主从同步
mysql> start slave;

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

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

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.

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.

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.

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.

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

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.

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.
