Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Between the two to achieve the main MySQL database synchronization from     - Management DB2 logs (Database)

- Classical sorting algorithm - Selection Sort (Linux)

- Under CentOS 7 installation and deployment environment Ceph (Server)

- Python developer of time-saving method (Programming)

- Linux system firewall defense network attacks (Linux)

- About AWR More Description (Database)

- Install mono offline on CentOS (Server)

- JavaScript function definition mode (Programming)

- Github with .gitignore ignore specified file (Linux)

- lack of SWAP space during installation of Oracle (Database)

- OpenGL Superb Learning Notes - Vertex Shader example (Programming)

- VMware Workstation + Ubuntu 10.04 Download the Android 2.2 source code (Linux)

- Graphics of Java Tools (Programming)

- Use cmake to compile and install MySQL 5.5 (Database)

- Mind mapping software installed in CentOS 7 in XMind (Linux)

- OpenSUSE 13.1 OpenSUSE 12.3 and how to install Cinnamon 2.2 Desktop (Linux)

- Hadoop + Zookeeper NameNode achieve high availability (Server)

- Java eight new features 8 (Programming)

- Android project and coding specifications (Programming)

- Mounting Windows shared directory system under the Linux (Linux)

 
         
  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:      
 
- Linux shell in back quotes (`) Application (Linux)
- The basic principles of AIX system security (Linux)
- Linux bash: scp: command not found the problem (Linux)
- To assign multiple IP addresses NIC on the CentOS 7 (Linux)
- Seven Steps to Help Google Chrome Speed - (Linux)
- Ubuntu 12.04 install RTL8723BE wireless network card driver (Programming)
- C ++ sequence containers basics summary (Programming)
- floating IP in OpenStack neutron (Server)
- Bash common several configuration files (Linux)
- To install Docker under CentOS7 (Linux)
- Why you should choose Python Programming (Programming)
- Git uses a small mind (Linux)
- How to install Linux Go Language (Linux)
- Understanding Linux firewall Iptables (Linux)
- C language programming entry - macro definitions and enum (Programming)
- How to upgrade to Ubuntu 14.04 Ubuntu 14.10 (Linux)
- Learning C ++ Standard Template Library and data structures (Programming)
- RCU lock in the evolution of the Linux kernel (Linux)
- VMware virtual machines to install virt-manager unable to connect to libvirt's approach (Linux)
- How to use the tab in Vim carried Python code completion (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.