## Build instructions * Two masters and one subordinate , In essence , It's just a machine master-a and master-b They are masters and slaves of each other （ Hot standby ）, And then through **keepalived** High availability configuration , So that at the same time there will be only one external service , Implement single write mechanism , Another machine slave As master-a The slave exists （ Cold preparation ）. * Slave slave Can't directly virtual IP（VIP） As a host ： In normal execution ,slave Bound to a host master-a, Once tied , stay master-a When it's not available , Can't automatically switch to master-b, Because you need to think about Binary log file name (master_log_file) And location (master_log_pos） stay master-a and master-b It's not consistent , At this time, only in master-a When it is available again ,master-a From master-b After synchronizing the data ,slave The data will eventually come into contact with master-a bring into correspondence with . ## Environmental preparation 1. Three centos7 Server for , Server ip For 192.168.86.124(master-a)、192.168.86.125(master-b)、192.168.86.126(slave) 2. A virtual IP（VIP）：192.168.86.250 3. [mysql-5.7.24-linux-glibc2.12-x86_64 Installation package ](https://downloads.mysql.com/archives/community/) ## Database installation 1. Will mysql The installation package is uploaded to three servers 2. Unzip the installation package ``` tar -zxvf mysql-5.7.24-linux-glibc2.12-x86_64.tar.gz ``` 3. establish mysql Installation path , Then move the folder you just extracted and rename it ``` mkdir /data # Set up the installation path mv mysql-5.7.24-linux-glibc2.12-x86_64 /data/mysql # Move files and rename ``` 4. establish mysql Users and groups 、 establish mysql Implement the required folder and authorization ``` groupadd mysql # Create user groups useradd -r -g mysql mysql # Create users cd /data/mysql # Switch to the installation directory mkdir data # Create a data storage directory mkdir mysql-log # Create a log directory mkdir mysql-log/err-log mkdir mysql-log/slow-log mkdir mysql-log/relay-log mkdir mysql-log/bin-log chown -R root:mysql . # Put the current directory and subdirectories , The owner changed to mysql, Change the group to mysql chown -R mysql:mysql data chown -R mysql:mysql mysql-log ``` 5. Set user operating system resource limits ``` vi /etc/security/limits.conf # Add the following to the file mysql soft nproc 2047 mysql hard nproc 16384 mysql soft nofile 1024 mysql hard nofile 65536 ``` 6. Set up links and services ``` cp /data/mysql/support-files/mysql.server /etc/init.d/mysql # have access to service mysql start ln -s /data/mysql/bin/mysql /usr/bin # Any path can be used mysql command ``` 7. To configure /etc/my.cnf ``` vi /etc/my.cnf ``` * master-a ``` [mysqld] port = 3306 # Port basedir = /data/mysql #mysql Installation path datadir = /data/mysql/data/ #mysql Data storage path # Log settings log-error = /data/mysql/mysql-log/err-log/db-err.log # Error log path slow-query-log-file = /data/mysql/mysql-log/slow-log/db-slow.log # slow SQL Log path relay-log=/data/mysql/mysql-log/relay-log/relay-log long-query-time = 20 # What is slow sql, In seconds # Turn on binlog Sync server_id = 0001 # In a cluster MySQL Server ID, Global uniqueness log-bin = /data/mysql/mysql-log/bin-log/db-binlog # Turn on Binlog And write down the location of the log max-binlog-cache_size = 64M #binlog Maximum use of cache Memory size of max-binlog-size = 1G #binlog Every time the log reaches the set size , Will use the new binlog The Journal expire_logs_days = 15 # Keep only the recent 15 Days of bin The Journal binlog-format = mixed # Hybrid mode replication innodb_flush_log_at_trx_commit = 1 # and sync_binlog control MySQL Disk write policy and data security sync-binlog = 1 # Controlling the database binlog Brush to disk ## Master master synchronization configuration replicate-do-db=db_test # Need to be from master library -2 Synchronized databases 1 replicate-do-db=db_dev # Need to be from master library -2 Synchronized databases 2 auto-increment-increment=2 auto-increment-offset=1 log-slave-updates=1 # Performance tuning configuration innodb_buffer_pool_size = 24576M max_connections = 5000 max_connect_errors = 6000 external-locking = FALSE max_allowed_packet = 64M join_buffer_size = 64M sort_buffer_size = 2M read_rnd_buffer_size = 16M #SQL Pattern sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES ``` * master-b ``` [mysqld] port = 3306 basedir = /data/mysql datadir = /data/mysql/data # Log settings slow-query-log-file=/data/mysql/mysql-log/slow-log/db-slow.log log-error=/data/mysql/mysql-log/err-log/db-err.log relay-log=/data/mysql/mysql-log/relay-log/relay-log long-query-time = 20 # What is slow sql, In seconds # Turn on binlog Sync server_id = 0002 # In a cluster MySQL Server ID, Global uniqueness log-bin = /data/mysql/mysql-log/bin-log/db-binlog # Turn on Binlog And write down the location of the log max-binlog-cache_size = 64M #binlog Maximum use of cache Memory size of max-binlog-size = 1G #binlog Every time the log reaches the set size , Will use the new binlog The Journal expire_logs_days = 15 # Keep only the recent 15 Day's diary binlog-format = mixed # Hybrid mode replication innodb_flush_log_at_trx_commit = 1 # and sync_binlog control MySQL Disk write policy and data security sync-binlog = 1 # Controlling the database binlog Brush to disk # Master master settings replicate-do-db=db_test # Need to be from master library -1 Synchronized databases 1 replicate-do-db=db_dev # Need to be from master library -1 Synchronized databases 2 auto-increment-increment=2 auto-increment-offset=2 log-slave-updates=1 # Performance tuning settings innodb_buffer_pool_size = 24576M max_connections = 5000 max_connect_errors = 6000 external-locking = FALSE max_allowed_packet = 64M join_buffer_size = 64M sort_buffer_size = 2M read_rnd_buffer_size = 16M #SQL Pattern sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES ``` * slave ``` [mysqld] port = 3306 basedir = /data/mysql datadir = /data/mysql/data # Log settings expire_logs_days = 15 long-query-time = 3 slow-query-log-file=/data/mysql/mysql-log/slow-log/db-slow.log log-error=/data/mysql/mysql-log/err-log/db-err.log # Master slave settings server_id = 0003 # Here you need to be unique in the cluster replicate-do-db=db_test # Databases that need to be synchronized from the master database 1 replicate-do-db=db_dev # Databases that need to be synchronized from the master database 2 read_only=1 # Read only settings # Performance tuning settings innodb_buffer_pool_size = 24576M max_connections = 5000 max_connect_errors = 6000 external-locking = FALSE max_allowed_packet = 64M join_buffer_size = 64M sort_buffer_size = 2M read_rnd_buffer_size = 16M #SQL Pattern sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES ``` ## Database initialization 1. Initialize database ``` cd /data/mysql/bin/ ./mysqld --defaults-file=/etc/my.cnf --user=mysql --initialize # initialization ``` 2. Check the password ( After initializing the database, a root The default password for the account ) ``` cat /data/mysql/mysql-log/err-log/db-err.log ``` 3. Start mysql ``` service mysql start ``` 4. Log in and modify root code ``` mysql -u root -p set password = password('root'); flush privileges; # If needed root Make a remote connection , Execute the following statement use mysql; update user set host = '%' where user = 'root'; flush privileges; ``` 5. Create users and empower * Host master-a、master-b ``` # Create a slave for and slave “ Communications ” Users of , Have synchronization permission grant replication slave on *.* to 'copy'@'%' identified by 'copy' flush privileges; # Create a user to access and manipulate specific libraries , Applications should not be allowed to use directly root Users . grant all privileges on db_test.* to "app_user"@'%' identified by "app_user"; flush privileges; ``` * Slave slave ``` # Create a user to access and manipulate specific libraries , Applications should not be allowed to use directly root Users . grant all privileges on db_test.* to "app_user"@'%' identified by "app_user"; flush privileges; ``` ## Master slave synchronous start 1. Host master-a 1. stay master-b Get binary log file name from (master_log_file) And location (master_log_pos) ``` show master status ``` .png) 2. stay master-a Execute the following command ``` stop slave; # Pause reset slave; # Reset change master to master_host="master-b Of IP", master_user="copy",master_password="copy",master_log_file="db-binlog.000002",master_log_pos=2532; # Set the user information synchronized with the host , Log file information start slave; # Start show slave status \G # View slave status , Mainly to see Slave_IO_Running Slave_SQL_Running Is it all yes ``` .png) 2. Host master-b、 Slave slave 1. stay master-a Get binary log file name from (master_log_file) And location (master_log_pos) ``` show master status ``` 2. stay master-b and slave Execute the following command ``` stop slave; # Pause reset slave; # Reset change master to master_host="master-a Of IP", master_user="copy",master_password="copy",master_log_file="db-binlog.000002",master_log_pos=2532; # Set the user information synchronized with the host , Log file information start slave; # Start show slave status \G # View slave status , Mainly to see Slave_IO_Running Slave_SQL_Running Is it all yes ``` ## keepalived Installation start up （master-a、master-b） 1. keepalived Install ``` yum install -y keepalived ``` 2. keepalived To configure ``` vi /etc/keepalived/keepalived.conf ``` The content of the file is ： ``` global_defs { router_id LVS_DEVEL } vrrp_sync_group VG_1 { group { VI_1 } } vrrp_instance VI_1 { state BACKUP # Both machines have this value , Don't set it to MASTER, Use the following priority To control interface ens33 # Through ifconfig Look at virtual_router_id 51 priority 100 # Priorities , The other machine is set to 90 advert_int 1 nopreempt # Another low priority machine does not set this argument ！ authentication { auth_type root auth_pass root } virtual_ipaddress { 192.168.86.250 # This is VIP } } virtual_server 192.168.86.250 3306 { # This is VIP delay_loop 2 lb_algo rr lb_kind DR persistence_timeout 50 protocol TCP real_server 192.168.86.124 3306 { # This is the machine IP Address weight 3 notify_down /data/mysql/bin/mysql.sh TCP_CHECK { connect_timeout 5 nb_get_retry 3 delay_before_retry 3 connect_port 3306 } } } ``` 3. Create a closure keepalived The command code of , Used in mysql When you can't access , Shut down keepalived To release VIP ``` vi /data/mysql/bin/mysql.sh ``` The content of the file is ： ``` #!/bin/sh pkill keepalived ``` Make the file executable ： ``` chmod +x /data/mysql/bin/mysql.sh ``` 4. keepalived Start ``` systemctl start keepalived ``` 5. keepalived Other related commands ``` systemctl enable keepalived # Power on and self start systemctl start keepalived # Start systemctl stop keepalived # Pause systemctl restart keepalived # Restart systemctl status keepalived # Check the status tail -f /var/log/messages ``` ## Test 1. First , All the machines 、 The software runs normally * Through various IP and VIP The connection database view can connect to the database normally .png?a=1604647194590) * master-a Got vip ``` ip addr ```  2. Through VIP Connect to the database , And then set up a db_test Library and tb_user surface * master-a、master-b、slave There are established libraries and tables 3. Connect master-a The database of , And add a piece of information * master-b、slave There are new information 4. Connect master-b The database of , And add a piece of information * master-a、slave There are new information 5. Shut down master-a Of mysql ``` service mysql stop # Shut down mysql systemctl status keepalived # Look at keepalived Status ``` * master-a Of keepalived It's turned off automatically , as a result of mysql Closing triggers keepalived Configured notify_down Instruction code * master-b Got vip 6. Through VIP Connect to the database , And insert a piece of information * master-a（ Can't connect ） * master-b （ There are new data ） * slave（ There is no new data , Because master-a Hang up ） 7. Restart master-a Of mysql and keepalived * vip Still in master-b , Because master-a keepalived Non preemptive mode is configured **nopreempt**, Therefore, if the host machine troubleshooting needs to be the host of keepalived Restart , Then restart the slave keepalived, The host needs to get VIP * master-a、master-b Synchronize the newly added data 8. Shut down master-b、slave Of mysql, Through master-a Add a piece of data , Then restart master-b、slave Of mysql, as well as master-b Of keepalived * master-b There are new data * slave There are new data * vip Still in master-a ## often