Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Between the two to achieve the main MySQL database synchronization from     - PHP loop reference caused strange problems (Programming)

- Linux PXE unattended installation PXE-E32: TFTP OPen timeout the solution (Linux)

- Thinking in Java study notes - Access modifiers (Programming)

- CentOS install SystemTap-2.6 (Linux)

- sudo command scenario analysis (Linux)

- Java Annotation Comments (Programming)

- The temporary operation of the document mkstemp on Linux (Linux)

- Oracle Database ORA-01555 snapshot too old (Database)

- MySQL Online DDL tools of pt-online-schema-change (Database)

- Linux system font color change (Linux)

- How to Start a Linux application running in the background using the terminal mode (Linux)

- Source install Python3.4 on CentOS (Linux)

- How SSHfs mount a remote file system on Linux (Linux)

- Linux and Unix systems really do network more secure (Linux)

- How to install Ubuntu applications Device 7 (Linux)

- Kubuntu 14.04 desktop to the user how to upgrade KDE 4.13.2 (Linux)

- Tecplot Installation under Linux (Linux)

- PostgreSQL procedural language learning (Database)

- Oracle can not change the tablespace to backup mode in non-archive mode (Database)

- SaltStack installation and testing (Server)

 
         
  Between the two to achieve the main MySQL database synchronization from
     
  Add Date : 2018-11-21      
         
         
         
  First. Overview
MySQL offers from later versions 3.23.15 database replication (replication) feature This feature allows you to achieve two databases synchronized, master-slave mode, another function of the backup mode
Second, the environment
Operating System: Linux 2.6.23.1-42.fc8 # SMP (do not install XEN)
Mysql version: 5.0.45-4.fc8
Device environment: PC (or virtual machine) two
Third, the configuration
Database replication feature set are reflected in the configuration file of MySQL, MySQL configuration file (usually my.cnf): Under this environment /etc/my.cnf.
3.1 Set the environment:
 IP settings:
A host IP: 10.10.0.119
      Mask: 255.255.0.0
B Host IP: 10.10.8.112
      Mask: 255.255.0.0
After IP setup is complete, you need to determine the two host-based firewall, it has been closed. You can use the command service iptables status to view the status of the firewall. If the firewall status is still running. Using service iptables stop to disable the firewall. If you want to start off the firewall can disable or customize using the setup command.
Eventually two hosts can ping each other better.
3.2 Configuration A primary (master) B from (slave) mode
3.2.1 A configured as master
1, add a user account simultaneous use:
  . GRANT FILE ON * * TO 'backup'@'10.10.8.112' IDENTIFIED BY '1234';
. GRANTREPLICATION SLAVE ON * * TO 'backup'@'10.10.8.112' IDENTIFIED BY '1234';
10.10.8.112 is assigned Slave machine has File permissions, giving the machine has only Slave File permission is not enough, but also to give it REPLICATION SLAVE privilege can.
2, add a database as a synchronized database:
create database test;
3, create a table structure:
      create table mytest (username varchar (20), password varchar (20));
4, modify the configuration file:
            A modification of the /etc/my.cnf file and add the following in the my.cnf configuration item configuration:
server-id = 1 #Server logo
log-bin
binlog-do-db = test # Specifies the log database
            
5, restart the database service:
 service mysqld restart
 See server-id:
 show variable like 'server_id';
Example:
mysql> show variables like 'server_id';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Server_id | 1 |
+ --------------- + ------- +
1 row in set (0.00 sec)
 
6, with the show master status / G command to see the log situation.
      Normally:
mysql> show master status / G
*************************** 1. row ******************** *******
File: mysqld-bin.000002
Position: 198
Binlog_Do_DB: test, test
Binlog_Ignore_DB:
1 row in set (0.08 sec)
3.2.2 Configuration B is a slave
1, add a database as a synchronized database:
create database test;
2. Create a table structure:
create table mytest (username varchar (20), password varchar (20));
3, modify the configuration file:
            Modify /etc/my.cnf file B, add the following configuration in my.cnf configuration item:
server-id = 2
master-host = 10.10. 0.119
master-user = backup # synchronize user accounts
master-password = 1234
master-port = 3306
master-connect-retry = 60 # default retry interval 60 seconds
replicate-do-db = test # tell slave only do backup database update
            
5, restart the database service:
 service mysqld restart
 See server-id:
 show variables like 'server_id';
Example:
mysql> show variables like 'server_id';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Server_id | 2 |
+ --------------- + ------- +
1 row in set (0.00 sec)
 
6, with the show slave status / G command to see the log situation.
      Normally:
mysql> show slave status / G
*************************** 1. row ******************** *******
            Slave_IO_State: Waiting for master to send event
                Master_Host: 10.10.0.119
                Master_User: backup
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysqld-bin.000001
        Read_Master_Log_Pos: 98
            Relay_Log_File: mysqld-relay-bin.000003
              Relay_Log_Pos: 236
      Relay_Master_Log_File: mysqld-bin.000001
          Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: test, test
        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: 98
            Relay_Log_Space: 236
            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
1 row in set (0.01 sec)
 
 
3.2.3 Authentication Configuration
Respectively using insert, delete, update the A host CRUD database; View B's database is consistent with the host A host; if agreed, the configuration was successful.
3.3 pairs of active preparation mode
If the slave set to join the A host B host joins the master set, you can do B-> A synchronization of.
1, A host configuration file mysqld configuration item by adding the following settings:
master-host = 10.10.8.112
master-user = backup
master-password = 1234
replicate-do-db = test
master-connect-retry = 10
 
2, B in the configuration file in the mysqld configuration item by adding the following settings:
log-bin
binlog-do-db = test
 
Note: When an error occurs, the thread exits * err log file synchronization, when the correct error to make synchronization work, run the slave start.
 
Up the A, B machine, you can achieve two-way hot spare.
IV Common Problems and Solutions
1, permission issues Slave machine, not only to give the slave machine File permissions, but also to give it REPLICATION SLAVE privilege.
Before 2, after modifying Slave machine /etc/my.cnf, slave machine mysql service starts, remember to delete master.info
3, in the show master status or the show slave status is not normal to see .err what to say.
4, on the Slave Mysql Replication work with two threads, I / O thread and the SQL thread. The role of I / O ports from the master 3306 to take over its binlog (master has been modified in any of the contents, the will to modify what is written own binlog waiting slave update), and then writes the local relay- log, and the SQL thread is to read the local relay-log, and then convert it costs Mysql statement can be understood, then sync it to complete this step by step. decision I / O thread is / var / lib / mysql / master.info, but decided SQL thread is /var/lib/mysql/relay-log.info.
5, start the slave, command start slave; restart with restart slave
     
         
         
         
  More:      
 
- How to install Virtualbox 4.3.20 in Ubuntu 14.04 (Linux)
- Java development environment to build under Ubuntu (Linux)
- MySQL enabled SSD storage (Database)
- Advanced Linux security settings (Linux)
- GCC library link order problems (Programming)
- The Gitlab migrated to Docker container (Server)
- Linux security settings (Linux)
- Linux (CentOS) SSH login without password authentication (Linux)
- Java interface and inheritance (Programming)
- Ubuntu Install OpenSSL (Linux)
- grep command output highlighted word (Linux)
- CentOS 6.4 under PXE + Kickstart unattended operating system installation (Programming)
- JDK tools jstat (Linux)
- Netfilter / Iptables Comments (Linux)
- Java multi-threaded in a three way (inheritance, implementation, anonymous inner classes) (Programming)
- WEB-based Android Remote Tools Python implementation (Programming)
- Btrfs file system creation and their characteristics in Linux (Linux)
- Customize the output format in Linux history (Linux)
- Debian 7.8 system installation and configuration process (Linux)
- Set multiple IP addresses for a single network card on Ubuntu 15.10 (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.