Home PC Games Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ Between the two to achieve the main MySQL database synchronization from     - How to use the Vault secure password storage and API key (Linux)

- Let MySQL 5.6 support Emoji expression (Database)

- Ubuntu GCC, G ++ and fortran Version Switch (Linux)

- Oracle background processes daemons (Database)

- Ubuntu 12.04 64bit Install Source Insight 3.5 and create desktop icons (Linux)

- RAID disk array Description (Linux)

- Linux Security and Optimization (Linux)

- Sysdig: system troubleshooting tool (Linux)

- CentOS 6 / Linux su: Unable to set user ID: Resource temporarily unavailable (Linux)

- MySQL monitoring tools -orzdba (Database)

- Linux, Chrome browser font settings beautification (Linux)

- Use Docker containers (Linux)

- Distributed Firewall Design on Linux platform (Linux)

- RHEL5.x RHEL6.x replace CentOS yum source (Linux)

- Simple steps allows you to build a more secure Linux server (Linux)

- SUSE Linux network configuration and firewall configuration (Linux)

- C language function pointer and a callback function (Programming)

- How to create a cloud encrypted file system in Linux systems (Linux)

- Ubuntu 15.04 installed JDK and configured as the default JDK (Linux)

- Simple to use multi-threaded programming under Linux mutex and condition variable (Programming)

  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 # 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:
B Host IP:
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'@'' IDENTIFIED BY '1234';
. GRANTREPLICATION SLAVE ON * * TO 'backup'@'' IDENTIFIED BY '1234'; 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
binlog-do-db = test # Specifies the log database
5, restart the database service:
 service mysqld restart
 See server-id:
 show variable like 'server_id';
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.
mysql> show master status / G
*************************** 1. row ******************** *******
File: mysqld-bin.000002
Position: 198
Binlog_Do_DB: test, test
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';
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.
mysql> show slave status / G
*************************** 1. row ******************** *******
            Slave_IO_State: Waiting for master to send event
                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
                Last_Errno: 0
              Skip_Counter: 0
        Exec_Master_Log_Pos: 98
            Relay_Log_Space: 236
            Until_Condition: None
              Until_Log_Pos: 0
        Master_SSL_Allowed: No
      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 =
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:
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
- HashMap in Android and Java different implementations (Programming)
- Open SSH remote access service that allows Android phone Ubuntu 14.04 (Linux)
- wget command examples (Linux)
- Ubuntu uses under KVM + Qemu virtual machine build (Linux)
- Oracle online redefinition (Database)
- After the first remote installation GlassFish Web to remotely access their back office management system error solution appears (Server)
- OpenDaylight Helium version installed (Linux)
- Talking about modern programming language syntax and standard library tightly bound phenomenon (Programming)
- Those functions under Linux you do not know the df command (Linux)
- Talking about the implementation and the JVM garbage collection mechanism (Programming)
- The difference between vi and nano (Linux)
- Java semaphores (Programming)
- Squid proxy server (Server)
- Python, and / or (Programming)
- MySQL tmpdir parameter modification (Database)
- Python pickle module for simple use notes (Programming)
- Analytical Ceph: handle network layer (Server)
- VMware virtual machine to use bridged mode fast Internet access (Linux)
- Linux operating tips: Can not open file for writing or operation not permitted solution (Linux)
- The best known for archiving / compression tool under linux (Linux)
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.