Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ MySQL main and backup replication structures (using mysqld_multi)     - Installation CD audio file extraction tool Flacon (Linux)

- Example of use WebSocket (Programming)

- PostgreSQL with the C Completing the storage process instances (Database)

- SSH security note (Linux)

- Linux boot process (Linux)

- CentOS6.x and Windows XP and Windows Server 2003 Open IPv6 related matters (Linux)

- MySQL in order by inaccurate results in problems and solutions (Database)

- The difference between Linux su and sudo commands (Linux)

- PL / SQL in forall simple test (Database)

- Using C / C ++ extensions Python (Programming)

- Oracle Character Set Summary (Database)

- About Linux iptables firewall interview questions and answers (Linux)

- Linux file permissions chmod chown (Linux)

- Linux Proc File System Experiment (Linux)

- MySQL migration tool used in the production environment (Database)

- OpenvSwitch 2.1.2 shell script to start and stop (Linux)

- Python substring format (Programming)

- Detailed installation OpenCV2.3.1 under CentOS 6.5 (Linux)

- OpenSSH version smooth upgrade method (Linux)

- CoreOS use register mirror to build private warehouse (Linux)

 
         
  MySQL main and backup replication structures (using mysqld_multi)
     
  Add Date : 2017-08-31      
         
         
         
  Here I use a single server on the two examples of MySQL structures, the main use of the MySQL mysqld_multi
 
First, the replication principle
Began to build a mysql replication before the basic knowledge of the need to add:
Mysql for the use of the main preparation and replication to the three threads:
1. The main database on the transfer storage thread:
    Mysql server will be submitted to the affairs of the document into the binary file, the binaries called binlog.
2. The connection thread on the standby library:
    After the standby library is started, it is responsible for communicating with the main library and reading binlog. At the same time, binlog is stored in one of its relaylogs called relaylog.
3. Backuplog on the backup library Thread:
  This thread will be relaylog events in the standby library on the playback, said the white point is to re-run once
 
Second, build the steps
1./etc Add the file mysqld_multi.cnf
Copy the /user/share/mysql/my-innodb-heavy-4G.cnf file to / etc and rename it to xxx.cnf (any name you want)
Add three instances to the configuration file

-------------------------------------------------- ------------------------------
[Mysqld_multi]
Mysqld = / usr / bin / mysqld_safe
Mysqladmin = / usr / bin / mysqladmin
User = multi_admin
Password = multipass
Log = /var/log/mysqld_multi.log

[Mysqld3307]
Port = 3307
Pid-file = /var/lib/mysql3307/mysql3307.pid
Socket = /var/lib/mysql3307/mysql3307.sock
Datadir = / var / lib / mysql3307
User = mysql
Set-variable = max_connections = 27000
Log_bin = mysql-bin
Server_id = 3307

[Mysqld3308]
Port = 3308
Pid-file = /var/lib/mysql3308/mysql3308.pid
Socket = /var/lib/mysql3308/mysql3308.sock
Datadir = / var / lib / mysql3308
User = mysql
Set-variable = max_connections = 28000
Log_bin = mysql-bin
Server_id = 3308
Relay_log = / var / lib / mysql3308 / mysql-relay-bin
Log_slave_updates = 1
Read_only = 1

[Mysqld3309]
Port = 3309
Pid-file = /var/lib/mysql3309/mysql3309.pid
Socket = /var/lib/mysql3309/mysql3309.sock
Datadir = / var / lib / mysql3309
User = mysql
Set-variable = max_connections = 29000
Log_bin = mysql-bin
Server_id = 3309
Relay_log = / var / lib / mysql3309 / mysql-relay-bin
Log_slave_updates = 1
Read_only = 1

-------------------------------------------------- ------------------------------


 Here I will be mysqld3307 this instance as the main library, mysqld3308 and mysql3309 this instance as a backup library

2. After doing the configuration, open two instances:
Mysqld_multi --defaults-file = / etc / mysql / mysqld_muti.cnf start

 
3. Prepare the main library before copying:
  1. In the main library to add a copy account:
    Use the sock file login mysql:
      Mysql-uroot-p -S / var / lib / mysql3307 / mysql3307.sock (the socket file remember, it is defined before the definition of the instance)

-------------------------------------------------- ------------------------------
Mysql> grant replication slave, replication client on *. * To replication @ 'localhost' identified by 'replication';

Mysql> flush privileges;

-------------------------------------------------- ------------------------------
  View the main library on the binlog is open:

-------------------------------------------------- ------------------------------

Mysql> show master status;
+ ------------------ + ---------- + -------------- + ---- -------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ---- -------------- +
| Mysql-bin.000001 | 1001 |
+ ------------------ + ---------- + -------------- + ---- -------------- +
1 row in set (0.00 sec)

-------------------------------------------------- ------------------------------
 
4. Turn on replication:
Login to the standby:
Mysql -uroot -p -S /var/lib/mysql3308/mysql3308.sock
 

-------------------------------------------------- ------------------------------

Change master to master_host = "localhost",

Master_user = 'replication',

Master_password = 'replication', (you previously created in the main library copy account specified)

Master_port = 3306;

 
Start slave

 
To see if replication is working:

-------------------------------------------------- ------------------------------
Mysql> show slave status \ G;
*************************** 1. row ******************** *******
              Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: replication
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 622
              Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 767
        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: 622
              Relay_Log_Space: 922
              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)

-------------------------------------------------- ------------------------------
 When you see Slave_IO_Running: Yes and Slave_SQL_Running: Yes verify that both the io communication thread and the sql playback thread are already started. At this point, the active-standby replication fabric is configured

5. Perform normal master-slave test:
The mysql3308 database stops replication

-------------------------------------------------- ------------------------------
Mysql> Stop Slave;
Query OK, 0 rows affected (0.00 sec)

Mysql> show slave status \ G;
*************************** 1. row ******************** *******
              Slave_IO_State:
                  Master_Host: localhost
                  Master_User: replication
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 408
              Relay_Log_File: mysql-relay-bin.000012
                Relay_Log_Pos: 553
        Relay_Master_Log_File: mysql-bin.000005

-------------------------------------------------- ------------------------------


The mysql3309 database stops replication

-------------------------------------------------- ------------------------------
Mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

Mysql> show slave status \ G;
*************************** 1. row ******************** *******
              Slave_IO_State:
                  Master_Host: localhost
                  Master_User: replication
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000006
          Read_Master_Log_Pos: 316

-------------------------------------------------- ------------------------------


View mysql log situation:

-------------------------------------------------- ------------------------------
150510 1:33:39 [Note] Error reading relay log event: slave SQL thread was killed
150510 1:33:39 [Note] Slave I / O thread killed while reading event
150510 1:33:39 [Note] Slave I / O thread exiting, read up to log 'mysql-bin.000005', position 408


150510 1:35:41 [Note] Error reading relay log event: slave SQL thread was killed
150510 1:35:41 [Note] Slave I / O thread killed while reading event
150510 1:35:41 [Note] Slave I / O thread exiting, read up to log 'mysql-bin.000006', position 316

-------------------------------------------------- ------------------------------


    During this period, the main library mysql3307 flush logs operation, re-generated mysql-bin log, and the table to add, delete operations. Then start the copy from the library to view.

-------------------------------------------------- ------------------------------

150510 1:42:48 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000005' at position 408, relay log '/var/lib/mysql3308/mysql-relay-bin.000012' position: 553
150510 1:42:48 [Note] Slave I / O thread: connected to master 'replication @ localhost: 3307', replication started in log 'mysql-bin.000005' at position 408


150510 1:43:04 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000006' at position 316, relay log '/var/lib/mysql3309/mysql-relay-bin.000015' position: 461
150510 1:43:04 [Note] Slave I / O thread: connected to master 'replication @ localhost: 3307', replication started in log 'mysql-bin.000006' at position 316

-------------------------------------------------- ------------------------------
    Explanation: All data for the stop period have been replenished from the point in time when the library was stopped.

6. Abnormal test:
      Stop from the library mysql3308 and mysql3309 copy, and the main library for log removal:

-------------------------------------------------- ------------------------------
Flush logs
Delete from sky where id = '2000';
Purge binary logs to 'mysql-bin.000010';

-------------------------------------------------- ------------------------------
    And then restart the replication from the library, the log for error, data loss, master-slave failure.

-------------------------------------------------- ------------------------------
150510 1:50:53 [ERROR] Slave I / O: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file', Error_code: 1236


-------------------------------------------------- ------------------------------
Solution:
    Can only be stopped from the database and then restarted at the beginning of the CHANGE MASTER TO from the database, pointing to the correct binaries and offsets

-------------------------------------------------- ------------------------------
Change master to master_log_file = 'mysql-bin.000001', master_log_pos = 106;

-------------------------------------------------- ------------------------------
    After the data will be restored to normal master-slave synchronization.

     
         
         
         
  More:      
 
- SSH port forwarding application (Server)
- Will Laravel become the most successful PHP framework? (Programming)
- Git uses a small mind (Linux)
- CentOS 5.5 install ntop (Linux)
- MariaDB 10 Multi-source replication (Database)
- Teach you how to choose to install CentOS 6.5 installation package (Linux)
- Justniffer installed on Ubuntu 15.04 (Linux)
- Java foundation comb: Array (Programming)
- Windows using SSH management Ubuntu (Linux)
- How to configure AWStats in Ubuntu Server (Server)
- Linux User Rights Study Notes (Linux)
- Make command Detailed Tutorial (Programming)
- I like Linux Security (Linux)
- Installation Eduspec university management systems 17 Linux Mint (Server)
- Stunning exclamation point at the Linux command line (Linux)
- Physical structure and process disk IO (Linux)
- CentOS 6.5 three ways to configure the IP address (Linux)
- CentOS RedHat YUM Source Extensions Supplement (including 32-bit, 64-bit) (Linux)
- The basic principles for the protection of a good linux server security (Linux)
- How to use Java to read OpenOffice document (Programming)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.