LNMP+HAProxy+Keepalived Load Balancing (IV) – MySQL Dual Computer Inter-standby and Automatic Backup

Time:2019-1-14

The first three chapters have achieved the most basic load balancing, but there are still some problems, such as the two databases are not synchronized, the uploaded attachments are not synchronized, the database has not made a backup plan, and the load balancing parameters need to be optimized. Here, we will first complete the contents of the dual-computer backup and automatic backup.

  • Configure the account password of MySQL database (the following commands are for reference only, the purpose here is to have an account for other servers in the intranet to access the local database);

    # Stop database services
    service mysql stop
    # Validation-free Start Database Services uuuuuuuuuuu
    mysqld --user=mysql --skip-grant-tables --skip-networking
    # Log on to MySQL
    mysql -u root mysql
    Several Ways to Modify User Password
    mysql> UPDATE user SET authentication_string=PASSWORD('dbpwd') where USER='dbadmin';
    mysql> UPDATE user SET authentication_string=PASSWORD('dbpwd') where USER='dbbaker';
    mysql> UPDATE user SET Password=PASSWORD('dbpwd') where USER='dbadmin';
    mysql> alter user user() identified by "dbpwd";
    # Make the configuration work
    mysql> FLUSH PRIVILEGES;
    mysql> quit;
    
    # Restart MySQL service. Note that if MySQL can't find service here, you can try mysqld. If not, you can find the process ID (pid) through ps-ef | grep mysql, and then stop service through kill-9 pid.
    service mysql restart
    mysql -udbadmin -p
    # Enter password: <Enter a new password>
    # Create users and configure permissions:
    mysql> GRANT ALL ON *.* TO 'dbadmin'@'%' IDENTIFIED BY 'dbpwd' WITH GRANT OPTION;
    # Remove permissions
    # REVOKE ALL ON *.* TO 'dbadmin'@'%';
    FLUSH PRIVILEGES;
    # Global Level: *. *
    # Database hierarchy: db_name. *
    # Surface Level: db_name. tbl_name
    # ALL refers to the allocation of all permissions, specific permissions can be viewed through the following query statement
    
    # Query User Rights
    select * from mysql.user \G;
    # Delete Users
    # delete from mysql.user where Host <> '%';
    At this point, the database can be remotely connected through the above account.
  • Configure MySQL’s dual standby-master database server (192.168.6.200)/etc/my.cnfFor configuration, please refer to LNMP + HAProxy + Keepalived Load Balancing (III) – Configuration File Summary.

    # Perform configuration on the primary database server:
    vim /etc/my.cnf
    
    # Modify configuration:
    Ser-id = 6 * Unique in  Database Cluster
    Lower_case_table_names = 1  new rows, data tables are case-insensitive
    Replicate_wild_do_table = sync_db_name.%# Synchronizes only the tables under the "sync_db_name" Library
    Log-slave-updates = YES # Logging after synchronization from the server
    
    # Save the configuration and restart MySQL service:
    service mysql restart
    # Enter the MySQL command line to view the status information of the main database, where the "File" and "Position" values will be used later:
    mysql> use sync_db_name;
    mysql> show master status;

    LNMP+HAProxy+Keepalived Load Balancing (IV) - MySQL Dual Computer Inter-standby and Automatic Backup

  • Configure MySQL dual standby-standby database server (192.168.6.210);

    Stop slave:
    mysql> stop slave;
    
    # Enter the MySQL command line and configure the source of the main library to be synchronized:
    mysql> CHANGE MASTER TO MASTER_HOST='192.168.6.200',MASTER_PORT=10002,MASTER_USER='dbadmin',MASTER_PASSWORD='dbpwd',MASTER_LOG_FILE='mysql-bin.000016',MASTER_LOG_POS=1285;
    # MASTER_HOST: IP of the main database;
    MASTER_PORT: Port (int) of the main database, default 3306;
    # MASTER_USER, MASTER_PASSWORD: Main database account, password;
    # MASTER_LOG_FILE: File value queried in the main database;
    # MASTER_LOG_POS: The "Position" value queried in the main database;
    
    # Start slave:
    mysql> start slave;
    
    # Check slave:
    mysql> show slave status \G;
    
    Command line summary:
    show master status;
    stop slave;

    Slave_IO_Running and Slave_SQL_Running are shown as Yes to show that the primary and standby synchronization services are running normally, as follows:
    LNMP+HAProxy+Keepalived Load Balancing (IV) - MySQL Dual Computer Inter-standby and Automatic Backup

  • Synchronize the initial state of the database;
    Before starting the synchronization service, we need to manually synchronize the initial state of the database, and then we can check whether the changes from the database are synchronized by modifying the tables in the main database.

    Lock the database to be synchronized (192.168.6.200):
    msyql> use sync_db_name;
    msyql> flush tables with read lock;
    # The database export script to be synchronized:
    mysqldump -udbadmin -pdbpwd sync_db_name >/home/backup/sync_db_name.sql
    # Unlock the previously locked database:
    msyql> unlock tables;
    
    #Copy the SQL backup above to the database server (192.168.6.210), then create the database with the same name and restore the data:
    msyql> create database sync_db_name;
    msyql> use sync_db_name;
    msyql> source /home/backup/sync_db_name.sql;
  • Make two or more machines reciprocal;
    Then the master-slave database server is inverted (i.e. the master identity swaps of 192.168.6.200 and 192.168.6.210), and then the synchronization can be configured once. That is, 192.168.6.200 changes will be synchronized to 192.168.6.210, and vice versa. To put it simply:
    Dual computers are mutually available: A master B slave + A slave B master;
    Multiprocessor cycle mutual readiness: A master B slave + B master C slave + C master N slave + N master A slave (recommend not too much, especially in the case of large amount of database data and frequent changes, synchronization is also delayed after all);
  • Automatic backup of database (mentioned in the previous three articles);

    • Install planning tools;

      yum -y install crontabs
    • Edit MySQL configuration file;

      vim /etc/my.cnf
      
      # Add configuration file content:
      [mysqldump]
      # for backing up the database
      user=dbbaker
      password=dbpwd
    • Restart database services;

      service mysql restart & service mysql status
    • Prepare backup scripts;

      mkdir -p /home/bakup/lgd_system
      
      # Edit the contents of bakdb.sh:
      echo 'mysqldump sync_db_name | gzip > /home/backup/lgd_system/sync_db_name_$(date +%Y%m%d_%H%M%S).sql.gz' > /home/bakup/bakdb.sh
      chmod +x /home/bakcup/bakdb.sh
      
      # Unzip the specified backup file:
      gunzip sync_db_name_*.gz
    • Add backup plan;

      Mode 1:
      crontab -e
      Mode 2:
      vim /etc/crontab
        
      Editorial Plan:
      # Example of job definition:
      #. ------------------------------- minute (0 - 59)* denotes every minute. uuuuuuuuuu
       # |. ---------- - --- hour (0 - 23) * denotes hourly time. uuuuuuuuuuu
      # | |. ------------- day of month (1 - 31)* denotes every day. uuuuuuuuuuu
       # | | |. ----- month (1 - 12) OR jan, feb, mar, apr... * denotes a month uuuuuuuuuuuu
      Day of week (0 - 6) (Sunday = 0 or 7) OR sun, mon, tue, wed, thu, fri, sat * denotes every day.
      # |  |  |  |  |
      # * * * * * user-name (user name) command to be executed (script path)
      # 3010 * * / home / backup / bakdb. sh # indicates that a backup script is executed at 10:30 a.m. a day (the comments in front are removed)
    • Common operational commands for planning tasks;

      Service crond start  Start the service
      Service crond stop  Close the service
      Service crond restart  Restart service
      Service crond reload # reload configuration
      Service crond status  View service status
      Tail-f/var/log/cron # View Execution Log
    • Backup file decompression

      Gzip command:
      Option parameters:
      - c: The compressed data is displayed on the screen and can be used for redirection.
      - d: parameters for decompression;
      - t: Check the consistency of compression to see if there are any errors.
      - v: Display the compression ratio of source file size to compressed file size;
      -#:# is the meaning of the number, representing the compression level, -1 is the fastest, but the compression ratio is the worst, -9 is the slowest, but the compression ratio is the best! The default is -6.
      Gzip-v=> Compressed file, -v View Compression Ratio
      gzip -d 
      gunzip file.gz

Recommended Today

Basic operations of Sqlalchemy connecting to MySQL database

sqlalchemy_test.py #!/usr/bin/env python3.8.6 # _*_ coding: utf-8 _*_ # Description: # Author: qiaoxiaohang # Date: 2022/4/8 from config import DB_URI from sqlalchemy import create_engine #Todo creates the engine and links the database # engine = create_ Engine (db_uri) # create engine # conn = engine. Connect() # link # result = conn.execute(‘select * from boo’) […]