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;
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:
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