Shulou(Shulou.com)06/01 Report--

There are many high-availability solutions for MySQL, such as dual-host, MHA, MMM, and so on. Here is just the simplest two-master high-availability scheme.

1. Configure MySQL mutual master and slave 1. Environment preparation system IP hostname service Centos 7.5192.168.20.2mysql01MySQL+keepalivedCentos 7.5192.168.20.3mysql02MySQL+keepalived

Note: MySQL has been deployed. Please refer to Centos deployment MySQL 5.7for deployment.

2 、 Open binary log and relay log # Host mysql01 configuration file is as follows: [root@mysql01 ~] # cat / etc/my.cnf [mysqld] basedir=/usr/local/mysqldatadir=/usr/local/mysql/dataport=3306server_id=1 # server_id must be unique socket=/usr/local/mysql/mysql.socklog-error=/usr/local/mysql/data/mysqld.errbinlog_format = mixed # specify binary format log-bin=/usr/local/mysql/data / log_bin # specify binary log file relay-log=/usr/local/mysql/data/relay-bin # specify relay log relay-log-index=relay-bin.indexauto_increment_increment=2auto_increment_offset=1# host mysql02 configuration file is as follows: [root@mysql02 ~] # cat / etc/my.cnf [mysqld] basedir=/usr/local/mysqldatadir=/usr/local/mysql/dataport=3306server_id=2 socket=/usr/local/mysql/mysql.socklog- Error=/usr/local/mysql/data/mysqld.errbinlog_format = mixedlog-bin=/usr/local/mysql/data/log_binrelay-log=/usr/local/mysql/data/relay-binrelay-log-index=relay-bin.indexauto_increment_increment=2auto_increment_offset=2

Note: only server-id and auto_increment_offset are different between mysql01 and mysql02

There are self-growing fields in mysql, and two self-growing related configurations need to be set when doing primary primary synchronization of the database: auto_increment_offset and auto_increment_increment. Auto-increment-increment represents the amount of each increment from the growth field, and its default value is 1. Its value should be set to the total number of servers in the entire structure. I use two servers here, so the value is set to 2. Auto-increment-offset is used to set the starting point (that is, the initial value) of automatic growth in the database, because both servers set an automatic growth value of 2, so their starting points must be different in order to avoid primary key conflicts when the two servers synchronize data.

With regard to the "binlog_format = mixed" configuration item, which is used to define the format of binary logs, there are three values to choose from, as follows:

STATEMENT: based on sql statements to record binary logs, for example, some sql statements may affect hundreds of data changes, then only record a sql statement. Advantages: it can reduce the size of the binary log and the amount of I / O written to the log. Cons: when data recovery is needed, some custom stored procedures or functions may fail and the data may not be recovered. ROW: binary logs are recorded based on rows. If a SQL statement affects multiple rows of data, multiple binary logs will be logged. Advantage: binary logs can be used to accurately recover data. Disadvantages: when the amount of data that changes is large, it will bring some pressure to the disk Ipicuro. Mixed: binary logging is based on mixed mode. MySQL determines whether to log based on rows or sql statements. It is recommended to use this format. If you can accurately record changes in data based on sql statements, it will be based on sql statements. If sql statements contain stored procedures or environment variables, they will be recorded based on rows.

For more information on binary logging, you can refer to the official documentation of MySQL.

Note: you can add a "binlog_do_db= database name" configuration item to the my.cnf file (you can add multiple) to specify the database to be synchronized

3. Set mysql02 to mysql01 slave server 1) Firewall releases traffic from port 3306 (both hosts need to release port 3306, if the firewall is not enabled You can ignore) [root@mysql01 ~] # firewall-cmd-- add-port=3306/tcp-- permanent [root@mysql01 ~] # firewall-cmd-- reload2) create an authorized user [root@mysql01 ~] # mysql-uroot-p123.commysql > grant replication slave on *. * to rep@'192.168.20.%' identified by '123.com' on mysql01 3) check the current binlog status information of mysql01 mysql > show master status\ gateway * 1. Row * * File: log_bin.000001 # this value will use Position: 609 # this value will use Binlog_ Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec) 4) specify mysql01 as master on mysql02 And enable slave function # specify master address mysql > change master to master_host='192.168.20.2',-> master_user='rep',-> master_password='123.com',-> master_log_file='log_bin.000001', # must be the same as the name found on master-> master_log_pos=609 # same as above, this value is also found on master # start slave function mysql > start slave # confirm the successful configuration mysql > show slave status\ G # View slave status * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.20.2 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: log_bin.000001 Read_Master_Log_Pos: 609 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 318 Relay_Master_Log_File: log_bin.000001 Slave_IO_Running: Yes# this value must be Yes Slave_SQL_Running: Yes# this value must also be Yes# as long as the above two values are yes Then there is no problem with master and slave, # where the IO thread reads the binary log to the local relay log on master. The SQL thread converts the contents of the local relay log into sql statements and executes them. 4. The slave server # host mysql02 whose mysql01 is set to mysql02 is as follows: mysql > grant replication slave on *. * to rep@'192.168.20.%' identified by '123.compositionshare MySQL > flush privileges Mysql > show master status\ G # get the required file and Position** 1. Row * * File: log_bin.000002 Position: 609 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row In set (0.00 sec) # the operations on the host mysql01 are as follows: # specify mysql02 as mastermysql > change master to master_host='192.168.20.3' -> master_user='rep',-> master_password='123.com',-> master_log_file='log_bin.000002',-> master_log_pos=609 Mysql > start slave # start slavemysql > show slave status\ G # View slave status * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.20.3 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: log_bin.000002 Read_Master_Log_Pos: 609 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 318 Relay_Master_Log_File: log_bin.000002 # make sure the following two values are yes Slave_IO_Running: Yes Slave_SQL_Running: Yes5, Test master master synchronization 1) Host mysql01 creates test data mysql > create database test Mysql > use testmysql > create table T1 (id int,name varchar (4)); mysql > insert into T1 values (1), (2) from); # confirm the mysql01 data mysql > select * from T1 +-+-+ | id | name | +-+-+ | 1 | a | 2 | b | +-+-+ 2 rows in set (0.00 sec) 2) confirm that mysql02 has been synchronized and insert new data # perform the following operations on host mysql02 mysql > select * from T1 # make sure the data is synchronized with +-+-+ | id | name | +-+-+ | 1 | a | 2 | b | +-+-# insert data test mysql > insert into T1 values (3dc'), (4d`); mysql > select * data T1 # determine the latest data +-+-+ | id | name | +-+-+ | 1 | a | 2 | b | | 3 | c | 4 | d | +-+-3) make sure that mysql01 can synchronize mysql02 data # query on mysql01, whether to synchronize data on mysql02 host mysql > select * from T1 +-+-+ | id | name | +-+-+ | 1 | a | 2 | b | 3 | c | 4 | d | +-+-+ 4 rows in set (0.00 sec)

At this point, the updated data on any MySQL will be synchronized to another MySQL,MySQL.

Note: if the master MySQL server already exists, the slave server will only be built in the later stage of business development. Before configuring database synchronization, copy the database to be synchronized from the MySQL server to the slave server (for example, back up the database on the master MySQL, then use backup and then restore from the MySQL server).

2. Configure keepalived with high availability 1. Install keepalived

Both nodes need to execute the following command to install keepalived.

[root@mysql01 ~] # yum-y install keepalived2, configure firewall to release related traffic

Note: both hosts need to execute the following command in order to release the related traffic. 224.0.0.18 is the multicast address of keepalived, using the vrrp protocol.

[root@mysql02] # firewall-cmd-- direct-- permanent-- add-rule ipv4 filter OUTPUT 0-- in-interface ens33-- destination 224.0.0.18-- protocol vrrp-j ACCEPT [root@mysql02] # firewall-cmd-- direct-- permanent-- add-rule ipv4 filter INPUT 0-- in-interface ens33-- destination 224.0.0.18-- protocol vrrp-j ACCEPT [root@mysql02 ~] # firewall-cmd-- reload2, Modify the keepalived configuration file of host mysql01 [root@mysql01 ~] # cat / etc/keepalived/keepalived.conf! Configuration File for keepalivedglobal_defs {router_id mysql-01 # the value here must be unique} vrrp_instance VI_1 {state BACKUP # specifies the role is backup, and the roles of both MySQL servers are backup. Setting backup will specify the network card virtual_router_id 51 # that hosts the virtual IP according to the priority, and the values must be the same in the same cluster. And can not conflict with other groups in the local area network priority 100 # priority range: 0: 100 advert_int 1 # send vrrp packet interval, that is, how often the master election (can be considered as the health check interval) nopreempt # does not preempt, that is, allow a node with a lower priority as a master Authentication {# authentication zone auth_type PASS auth_pass 1111} virtual_ipaddress {# VIP region, specify vip address 192.168.20.20}} virtual_server 192.168.20.20 3306 {# set up virtual server, need to specify virtual IP address and service port Delay_loop 2 # is separated by a space between IP and port to set the running check time (in seconds) lb_algo rr # sets the back-end scheduling algorithm lb_kind DR # sets lvs load balancing mechanism. There are three modes: NAT, TUN and DR. DR mode is the most efficient persistence_timeout 60 # session duration, in seconds protocol TCP # specifies the forwarding protocol type. There are two kinds of real_server, TCP and UDP, 192.168.20.2 3306 {# configure the service node. What is specified here is the script executed after the local real IP weight 1 # sets the weight notify_down / etc/keepalived/bin/mysql.sh # detects the MySQL service downtime of real_server. TCP_CHECK {connect_port 3306 # Health check Port connect_timeout 3 # connection timeout retry 3 # retry delay_before_retry 3 # reconnection interval} # prepare the specified script [root@mysql01 keepalived] # pwd/etc/keepalived [root@mysql01 keepalived] # mkdir bin [root@mysql01 keepalived] # vim bin/mysql .sh #! / bin/bashpkill keepalived # stop keepalived service [root@mysql01 keepalived] # chmod + x bin/mysql.sh # give script execution permission [root@mysql01 ~] # systemctl start keepalived # start keepalived service # make sure that the ens33 Nic has virtual Ip [root@mysql01 ~] # ip a show ens33 # you must use the ip a command to see The ifconfig command looks at less than 2: ens33: mtu 1500 qdisc pfifo_fast state UP group default qlen 1000 link/ether 00:0c:29:c0:39:80 brd ff:ff:ff:ff:ff:ff inet 192.168.20.2 brd 24 brd 192.168.20.255 scope global noprefixroute ens33 valid_lft forever preferred_lft forever inet 192.168.20.20 scope global noprefixroute ens33 valid_lft forever preferred_lft forever inet 32 scope global ens33 # you can see that the specified VIP has been bound to ens33 Valid_lft forever preferred_lft forever inet6 fe80::659e:9312:318a:e52b/64 scope link noprefixroute valid_lft forever preferred_lft forever# sends the configuration file of keepalived to the mysql02 host [root@mysql01 ~] # scp / etc/keepalived/keepalived.conf root@192.168.20.3:/etc/keepalived/3, Modify the keepalived configuration file of host mysql02 # modify the configuration file [root@mysql02 keepalived] # cat / etc/keepalived/keepalived.conf sent by msyql01! Configuration File for keepalivedglobal_defs {router_id mysql-02 # change router_id Here you must have a unique} vrrp_instance VI_1 {state BACKUP interface ens33 virtual_router_id 51 priority 90 # change priority advert_int 1 nopreempt authentication {auth_type PASS auth_pass 1111} virtual_ipaddress {192.168.20.20}} virtual_server 192.168.20.20 3306 {delay_loop 2 lb_ Algo rr lb_kind DR persistence_timeout 60 protocol TCP real_server 192.168.20.3 3306 {# change to local IP address and listening port weight 1 notify_down / etc/keepalived/bin/mysql.sh TCP_CHECK {connect_port 3306 connect_timeout 3 retry 3 delay_before_retry 3}} # prepare the required script [root@mysql01 keepalived ] # pwd/etc/keepalived [root@mysql02 keepalived] # mkdir bin [root@mysql02 keepalived] # vim binpool mysql.shroupkill keepalived [root@mysql02 keepalived] # chmod + x bin/mysql.sh# launch keepalived [root @ mysql02 ~] # systemctl start keepalived

At this point, you can achieve the dual-master effect of MySQL (as long as the node where the VIP is located and the MySQL service port cannot be connected, the VIP will switch to another node, and the VIP will not be preempted even if the down mysql server is restored). Although there are two MySQL databases, they use the virtual IP address provided by keepalived to provide services. No matter which server the virtual Ip address falls on, it can ensure the consistency of the data, because they are master and slave to each other, and the state of the keepalived is backup, and it is also set not to preempt (reducing the number of VIP switching times), which can greatly avoid the brain fissure problem of keepalived.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.