Table of Contents
MySQL主从同步的作用
Home Database Mysql Tutorial Mysql主从同步安装及配置

Mysql主从同步安装及配置

Jun 07, 2016 pm 03:46 PM
mysql Synchronize Install Configuration

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.rpm
rpm -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;  


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

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

PHP's big data structure processing skills PHP's big data structure processing skills May 08, 2024 am 10:24 AM

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.

How to optimize MySQL query performance in PHP? How to optimize MySQL query performance in PHP? Jun 03, 2024 pm 08:11 PM

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 use MySQL backup and restore in PHP? How to use MySQL backup and restore in PHP? Jun 03, 2024 pm 12:19 PM

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 a MySQL table using PHP? How to insert data into a MySQL table using PHP? Jun 02, 2024 pm 02:26 PM

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.

How to fix mysql_native_password not loaded errors on MySQL 8.4 How to fix mysql_native_password not loaded errors on MySQL 8.4 Dec 09, 2024 am 11:42 AM

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

How to use MySQL stored procedures in PHP? How to use MySQL stored procedures in PHP? Jun 02, 2024 pm 02:13 PM

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.

How to create a MySQL table using PHP? How to create a MySQL table using PHP? Jun 04, 2024 pm 01:57 PM

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.

The difference between oracle database and mysql The difference between oracle database and mysql May 10, 2024 am 01:54 AM

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.

See all articles