Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Between the two to achieve the main MySQL database synchronization from     - MongoDB version 3.2 WiredTiger storage engine performance tests (Database)

- Upgrading Oracle 11.2.0.1.0 to 11.2.0.3.0 (Database)

- Mongo-connector integrated MongoD to achieve incremental Solr index (Server)

- Linux Tutorial: Open multiple tabs in the GNOME terminal in Ubuntu 15.04 (Linux)

- Ubuntu install Avast antivirus software (Programming)

- How apt-get limited use IPv4 or IPv6 protocol to download (Linux)

- TypeScript basic grammar (Programming)

- git checkout generated in the use of temporary br (Linux)

- Disk Management LVM (Linux)

- ORA-00911 invalid character error Solution (Database)

- Redhat Close SELinux correct step (Linux)

- Configuring ftp server and nfs server under Linux (Server)

- Linux using RAID how to use Mdadm Tool Management Software RAID (Linux)

- UUID in Java (Programming)

- How to install Git on CentOS 7 (Linux)

- Oracle EBS R12 for Linux installation (Database)

- mysqldump implement database logical backup (Database)

- File easier to compare tools under Linux (Linux)

- Ubuntu apt-mirror established local private sources (Linux)

- Learning MySQL data types (Database)

 
         
  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:      
 
- Flask deploy applications using Nginx on Ubuntu (Server)
- SA weak password security system of the security risks posed (Linux)
- Installation under Linux Mint system guidelines for Gtk (Linux)
- MySQL Installation Troubleshooting (Database)
- XenServer Virtual Machine Installation --- first ISO image file storage expansion (Linux)
- The new task parallel library feature in .NET 4.6 (Programming)
- Linux kernel modules related to the management Comments (Linux)
- Zypper command for SUSE Linux package management (Linux)
- Linux file permissions and access modes (Linux)
- Two classic macro definition under Linux (Linux)
- Android Launcher3 Application List Modify a transparent background (Linux)
- RedHat Linux 6.4 installation RTL8188CUS wireless network card driver (Linux)
- Android judgment toward camera pictures (Programming)
- Swift notes - let you two hours to learn Swift (Programming)
- Embedded Linux Optimization (Programming)
- Linux batch copy data script (Linux)
- On FreeBSD 10.2 Bugzilla how to install and configure Apache SSL (Server)
- Easy to get hidden administrator account (Linux)
- CentOS 6.6 running level (Linux)
- How to remove the Linux memory Cache, Buffer and swap space (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.