MySQL Architecture Backup Dual Hot Standby

M-S Architecture: Achieving Dual Hot Standby (AB Replication)

1. Reducing master Reading Pressure

2. The database can be "hot-standby". Hot-standby can only solve the hardware and software faults of hardware master, but it can not solve the logic faults caused by human misoperation (such as deleting important records by incorrect input of SQL statements), so regular backup is necessary.

Environmental preparation and requirements:

1. Close firewalls and selinux

2. Host name and ip address of two servers in hosts file correspond one by one

3. System Time Need Synchronization

4. The database versions of master and slave are consistent (system versions are consistent)

Train of thought:

1. master must open binary logs

2. Slve must open the relay log

3. The server-id of master and slave must be different (2 ^ 23-1)

4. The initial data of master and slave are consistent

M-S Architecture Operating Steps

Environmental Science:

master: 192.168.1.166

slave: 192.168.1.114

1. Environmental preparation

# master: 
[root@master ~]# vim /etc/hosts
192.168.1.114   slave

# slave: 
[root@slave ~]# vim /etc/hosts
192.168.1.166   master

2. Modifying configuration files

# master: 
root@master ~]# vim /etc/my.cnf
log-bin=mysql-bin
server-id = 1

# slave: 
[root@slave ~]# vim /etc/my.cnf
server-id = 2
relay-log=/data/DB/relay.log

3. Data Initialization Consistency

Scenario 1: There is no business data on either side, and the master and slave databases are initialized directly.

Case 2: The master has business data on it

masteer: 
1>Stop the database
[root@master ~]# /etc/init.d/mysqld stop

2>Physical level backup
[root@master ~]# cd /data/DB/ && ll
[root@master DB]# tar -cvzf /tmp/mysql.tar.gz *
[root@master DB]# scp /tmp/mysql.tar.gz 192.168.1.114:/tmp

slave: 
3>slave Data recovery at the end
[root@slave ~]# cd /data/DB/ && ll
[root@slave DB]# tar -xf /tmp/mysql.tar.gz -C .
[root@slave DB]# /etc/init.d/mysqld start

master and slave: 
4>See slave and master Is the data above the same?
[root@master DB]# echo "show databases;" | mysql -p123 
[root@slave DB]# echo "show databases;" | mysql -p123

The sample master has business data on it

4. Create authorized users on master and view binlog information

master: 
1>Lock the table first to prevent inconsistency of data on both sides;If the business is not on line, there is no need for it.
mysql> flush tables with read lock;

2>Creating Authorized Users
mysql> grant replication slave on *.* to 'slave'@'192.168.1.114' identified by '123';

3>See binglog Information, only open the binary log, this command will have results, indicating where the binary log of the current database is written.
mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 331
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.02 sec)
# Note: Don't write anything in it at this time.

5. Configure synchronization information on slave

slave: 
mysql> change master to master_host='192.168.1.166', master_user='slave', master_password='123', master_port=3306, master_log_file='mysql-bin.000003', master_log_pos=331;
//Description of parameters:
    master_host:         master ip
    master_user:         Synchronous user
    master_password:     Password
    master_port: 　　　　 port
    master_log_file:     master The binary log name seen above
    master_log_pos:      master Seen above POS value
    
mysql> show slave status \G;
//Check slave to find an error
//Solve:
    1.Check the configuration file server-id Is it inconsistent?
    2.Check the data catalog auto.cnf Documentation uuid Is it the same, if the same will slave Medium auto.cnf After deletion master and slave Service restart.

mysql> show slave status \G;
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

6. Test Verification

Test one:
    //First look at the user table information in ceshi libraries of master and slave
    master: 
    mysql> select * from ceshi.users;
    +----+-------+----------+-----------------+
    | id | name  | password | address         |
    +----+-------+----------+-----------------+
    |  1 | zhang | 1234     | NULL            |
    |  2 | wang  | 4321     | Wuhan        |
    |  3 | li    | 5678     | Haidian, Beijing        |
    |  4 | liu   | 1122     | NULL            |
    |  5 | zou   | 4311     | Changsha, Hunan        |
    |  6 | zhou  | 6789     | Babaoshan, Beijing      |
    |  7 | ding  | 7891     | Xili, Shenzhen        |
    +----+-------+----------+-----------------+
    7 rows in set (0.00 sec)

    slave: 
    mysql> select * from ceshi.users;
    +----+-------+----------+-----------------+
    | id | name  | password | address         |
    +----+-------+----------+-----------------+
    |  1 | zhang | 1234     | NULL            |
    |  2 | wang  | 4321     | Wuhan        |
    |  3 | li    | 5678     | Haidian, Beijing        |
    |  4 | liu   | 1122     | NULL            |
    |  5 | zou   | 4311     | Changsha, Hunan        |
    |  6 | zhou  | 6789     | Babaoshan, Beijing      |
    |  7 | ding  | 7891     | Xili, Shenzhen        |
    +----+-------+----------+-----------------+
    7 rows in set (0.11 sec)

    //Delete data on master and go back to slave to see
    master: 
    mysql> delete from ceshi.users where id>=3;
    mysql> select * from ceshi.users;
    +----+-------+----------+--------------+
    | id | name  | password | address      |
    +----+-------+----------+--------------+
    |  1 | zhang | 1234     | NULL         |
    |  2 | wang  | 4321     | Wuhan     |
    +----+-------+----------+--------------+
    2 rows in set (0.00 sec)

    slave: 
    mysql> select * from ceshi.users;
    +----+-------+----------+--------------+
    | id | name  | password | address      |
    +----+-------+----------+--------------+
    |  1 | zhang | 1234     | NULL         |
    |  2 | wang  | 4321     | Wuhan     |
    +----+-------+----------+--------------+
    2 rows in set (0.00 sec)


//Test two:
    //Stop services on slave
    [root@slave ~]# /etc/init.d/mysqld stop
    
    //Insert data on master and view it
    mysql> insert into ceshi.users(name, password, address) values("ceshi","666666","Shenzhen, Guangdong");
    Query OK, 1 row affected (0.04 sec)

    mysql> select * from ceshi.users;
    +----+-------+----------+--------------+
    | id | name  | password | address      |
    +----+-------+----------+--------------+
    |  1 | zhang | 1234     | NULL         |
    |  2 | wang  | 4321     | Wuhan     |
    |  8 | ceshi | 666666   | Shenzhen, Guangdong     |
    +----+-------+----------+--------------+
    3 rows in set (0.00 sec)
    
    //Start slave again and see if there are updates
    [root@slave ~]# /etc/init.d/mysqld start
    mysql> select * from ceshi.users;
    +----+-------+----------+--------------+
    | id | name  | password | address      |
    +----+-------+----------+--------------+
    |  1 | zhang | 1234     | NULL         |
    |  2 | wang  | 4321     | Wuhan     |
    |  8 | ceshi | 666666   | Shenzhen, Guangdong     |
    +----+-------+----------+--------------+
    3 rows in set (0.00 sec)

Test verification

1. Tests show that it can only be written on the master side and synchronized to the slave side.

2. If the slave service shuts down, it can not be managed, and then it can continue to write on the master side. When the slave service starts, it will automatically synchronize to the slave side.

Programmer Group

MySQL Architecture Backup Dual Hot Standby

M-S Architecture: Achieving Dual Hot Standby (AB Replication)

M-S Architecture Operating Steps

Hot Keywords