First: experimental environment

Operating System: CentOS release 6.5 (Final)

mysql dual master replication + keepalive achieves high availability:

master1:10.192.203.201

master2:10.192.203.202

vip 10.192.203.203

Virtual ip is currently on master 1. The program writes vip.

 

2. Experimental steps

2.1 Upgrade Master 2

2.1.1 Backup Mas2

show databases; see which libraries are available

mysqldump --single-transaction -u root -p-A > /download/bak/all.bak_20161125

Check backup files to make sure they are correct.

2.1.2 Record binlog location

# Add a read lock to Master 1 to record the File and Postion that Master 1 writes at this moment:

flush tables with read lock; # lock master 1, not allowed to write

show master status ;

# Stop slave of master 2:

stop slave;

Show slave status G; record the binlog location of read and executed

# unlock tables on master 1;

 

The reason for the above steps is to upgrade master2, import the backup data and know where to start copying, so as to be consistent with master1.

2.1.3 Unload Master 2

Unload script contents such as:

1. mysqldir='/data/mysql'  
2. basedir='/usr/local/mysql'  
3. cnf=/etc/my.cnf  
4. password='123456'  
5.    
6. #1: Close the database.
7. mysqladmin -u root -p$password shutdown  
8. killall -u mysql  
9.    
10. #2: Delete users and groups
11. userdel mysql  
12. groupdel mysql  
13.    
14. #3: Delete directories
15. rm -rf $mysqldir  
16. rm -rf $basedir  
17.    
18. #4: Cancel boot-up automatic start-up.
19. rm -rf /etc/rc.d/init.d/mysql  
20. chkconfig --del mysql  
21.    
22. #5: Delete PATH
23. #root user
24. sed -i'/^PATH=/s/:\/usr\/local\/mysql\/bin//' /root/.bash_profile  
25. source /root/.bash_profile  
26. #mysql user
27. sed -i'/^PATH=/s/:\/usr\/local\/mysql\/bin//' /home/mysql/.bash_profile  
28. source /home/mysql/.bash_profile  
29.    
30. #6: Cancel Firewall Port
31. #Modify the file / etc/sysconfig/iptables
32. #Delete - A INPUT - m state - state NEW - m tcp - p tcp - dport 3306 - j ACCEPT
33. sed -i '/-A INPUT -m state --state NEW -mtcp -p tcp --dport 3306 -j ACCEPT/d' /etc/sysconfig/iptables  
34.    
35. service iptables restart  

2.1.4 Install version 5.6.33 mysql

2.1.4.1 Installation script content

1. #Remember to upload the mysql installation package to the $dir directory first.
2. #Configuration file my.cnf uploads master 2-sample-my.cnf and modifies the value of innodb_buffer_pool_size according to the actual situation. Remember to modify the value of log-bin,relay-log,relay-log-index,log-bin according to the actual installation directory.
3. #Note that if mysqldir is not / data/mysql and changes to other directories (such as / database/mysql), you need to modify the line sed - i'47s / dataDir = / dataDir = \ database\ MySQL / G'/ etc / rc. D / mysqld in the script.
4.    
5. #Define Catalogue
6. basedir='/usr/local/mysql'  
7.         
8. mysqldir='/data/mysql'   
9.         
10. datadir=$mysqldir  
11.  #binlogdir=$mysqldir/binlog  
12.       
13. cnf=/etc/my.cnf  
14.    
15. dir='/download/'  
16.    
17. socket='/tmp/mysql.sock'  
18.    
19. port='3306'  
20.    
21. filename='mysql-5.6.33-linux-glibc2.5-x86_64'  
22.    
23. password='123456'  
24.    
25. password2=\'$password\'  
26.    
27. date=`date "+%y%m%d"`  
28.    
29. #Modify the configuration file
30. mv /etc/my.cnf /etc/my.cnf_bak_$date  
31. mv $dir'master2-sample-my.cnf' /etc/my.cnf  
32.    
33. #Build User
34. groupadd mysql  
35. useradd -g mysql mysql  
36.         
37. #Install dependency packages
38. yum install libaio -y  
39.        
40. #Decompression
41. cd $dir   
42. if ( test -s $filename )  
43. then  
44.    echo 'Undecompressed, no need to repeat decompression'  
45. else  
46.    tar -xvf $filename.tar.gz  
47. fi       
48.    
49.  #Copy the decompressed mysql directory to the system's local software directory:
50. cp $filename $basedir -r  
51.    
52. #New Directory
53. #mkdir -p $datadir  
54. #mkdir -p $binlogdir  
55. mkdir -p $mysqldir  
56. chown -R mysql:mysql $basedir  
57. chown -R mysql:mysql $mysqldir  
58.    
59. #Establishment of a basic database
60. $basedir/scripts/mysql_install_db--defaults-file=$cnf --user=mysql --basedir=$basedir --datadir=$datadir  
61. #Set the boot-up auto-start.
62. cp $basedir'/support-files/mysql.server''/etc/rc.d/init.d/mysqld'  
63. #Modify the file datadir
64. #You already know that datadir = is on line 47.
65. sed -i '47s/datadir=/datadir=\/data\/mysql/g' /etc/rc.d/init.d/mysqld  
66.    
67. chmod +x /etc/rc.d/init.d/mysqld  
68.    
69. chkconfig --add mysqld  
70.    
71. chkconfig mysqld on  
72.    
73. #Configure PATH
74. #root user
75. sed -i'/^PATH=/s/$/:\/usr\/local\/mysql\/bin/' /root/.bash_profile  
76. source /root/.bash_profile  
77.    
78. #mysql user
79. sed -i '/^PATH=/s/$/:\/usr\/local\/mysql\/bin/'/home/mysql/.bash_profile  
80. source /home/mysql/.bash_profile  
81. #Start the database.
82.    
83. service mysqld start  
84.    
85. #Open Firewall Port
86. #Open port 3306 and insert it behind the line - AINPUT-m state -- state NEW-m tcp-p TCP -- dport 22-j ACCEPT.
87. sed -i '/-A INPUT -m state --state NEW -mtcp -p tcp --dport 22 -j ACCEPT/s/$/\n-A INPUT -m state --state NEW -m tcp -ptcp --dport 3306 -j ACCEPT/' /etc/sysconfig/iptables  
88.    
89. service iptables restart  
90. #The company's firewall is closed, so the port is not open here.   
91.    
92. #Modify mysql root password
93. #Modify it to $password based on the final output of the script ('Establish the base library'root password). It needs to be executed interactively.
94. #mysql 5.6.33 default password is empty
95. mysqladmin -u root  password $password  
96.    
97.    
98. #To create a new user for remote access, the password is the same as the local user.   
99. $basedir/bin/mysql -u root -p$password -e"grant all privileges on *.* to 'root'@'%' identified by  $password2"  

2.1.4.2 Configuration file master 2-sample-my.cnf content

Since it is upgraded from version 5.5 to version 5.6, errors occur when low versions copy high versions of master libraries from slave libraries:

1. Got fatal error 1236 from master when reading data from binary log: 'Slave can not handle replication events with the checksum that master is configured to log;   

You need to ensure that you add parameters to the configuration file: binlog_checksum=none

The content of master 2-sample-my.cnf file:

1. [client]  
2. port           =3306  
3. socket                =/tmp/mysql.sock  
4. [mysqld]  
5. port           =3306  
6. socket                =/tmp/mysql.sock  
7. binlog_checksum=none  
8. innodb_buffer_pool_size = Set it to the appropriate value G  
9. server-id = 2  
10. max_connections = 5000  
11. max_connect_errors = 300  
12. max_allowed_packet = 64M  
13. binlog_format=row  
14. slow_query_log  
15. long_query_time = 2  
16. sync_binlog=1  
17. innodb_support_xa=1  
18. innodb_file_per_table=1  
19. replicate-ignore-db = mysql  
20. replicate-ignore-db = information_schema  
21. relay-log=/data/mysql/master2-relay-bin  
22. relay-log-index=/data/mysql/master2-relay-bin.index  
23. log-bin=/data/mysql/master2-mysql-bin  
24. log_slave_updates  
25. [mysqldump]  
26. # Do not buffer the whole result set inmemory before writing it to  
27. # file. Required for dumping very largetables  
28. quick  
29.    
30. max_allowed_packet = 64M  
31.    
32. [mysql]  
33. no-auto-rehash  
34.    
35. # Only allow UPDATEs and DELETEs that usekeys.  
36. #safe-updates  
37.    
38. [myisamchk]  
39. key_buffer_size = 512M  
40. sort_buffer_size = 512M  
41. read_buffer = 8M  
42. write_buffer = 8M  
43.    
44. [mysqlhotcopy]  
45. interactive-timeout  
46.    
47. [mysqld_safe]  
48. open-files-limit = 8192  

2.1.5 Data recovery

Import backup data

mysql -u root -p < all.bak_20161125

2.1.6 Manufacturing Test Data

Insert several test data on master 1

1. mysql> use sds;  
2. Database changed  
3. mysql> show tables;  
4. Empty set (0.00 sec)  
5.    
6. mysql> create table t(id int);  
7. Query OK, 0 rows affected (0.06 sec)  
8.    
9. mysql> insert into t(id)values(1),(2),(3);  
10. Query OK, 3 rows affected (0.02 sec)  
11. Records: 3 Duplicates: 0  Warnings: 0  
12.    
13. mysql> select  * from t;  
14. +------+  
15. | id  |  
16. +------+  
17. |   1 |  
18. |   2 |  
19. |   3 |  
20. +------+  
21. 3 rows in set (0.02 sec)  

2.1.7 Points Master 2 to master 1

Master_log_file, master_log_pos need and show master status in step 2.1.2; the results are consistent:

1. change master tomaster_log_file='mysql-bin.000003',master_log_pos=1109,master_host='10.192.203.201',master_user='RepUser',master_password='beijing',master_port=3306;  
2. mysql> start slave;  
3. Query OK, 0 rows affected (0.03 sec)  
4.    
5. show slave status \G;Check under Slave_IO_Running，Slave_SQL_Running Are they all Yes.   
6.    
7. #Verify that the difference data is synchronized.
8. mysql> use sds;  
9. Database changed  
10. mysql> show tables;  
11. +---------------+  
12. | Tables_in_sds |  
13. +---------------+  
14. | t             |  
15. +---------------+  
16. 1 row in set (0.00 sec)  
17.    
18. mysql> select * from t;  
19. +------+  
20. | id  |  
21. +------+  
22. |   1 |  
23. |   2 |  
24. |   3 |  
25. +------+  
26. 3 rows in set (0.01 sec)  

The Master 2 synchronization is successful.

2.2 Upgrade Master 1

2.2.1 drift vip to master 2 machine

Since I have configured mysql timing tasks on my own machine (monitoring mysql status once a minute, killing the heartbeat process on that machine if mysql goes down), upgrading Master 2 will definitely result in heartbeat being shut down. Therefore, the heartbeat process of Master 2 needs to be started first:

service heartbeat start

# Close the master 1 process:

service heartbeat stop

# Use the ip addr command on maser2 to verify that the virtual IP drift is successful.

1. [root@slave2 download]# ip addr  
2. 1: lo: <LOOPBACK,UP,LOWER_UP> mtu16436 qdisc noqueue state UNKNOWN  
3.    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00  
4.    inet 127.0.0.1/8 scope host lo  
5.    inet6 ::1/128 scope host  
6.       valid_lft forever preferred_lft forever  
7. 2: eth0:<BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen1000  
8.    link/ether 08:00:27:04:05:16 brd ff:ff:ff:ff:ff:ff  
9.    inet 10.192.203.202/24 brd 10.192.203.255 scope global eth0  
10.    inet 10.192.203.203/24 brd 10.192.203.255 scope global secondary eth0  
11.    inet6 fe80::a00:27ff:fe04:516/64 scope link  
12.       valid_lft forever preferred_lft forever  
13. 3: eth1:<BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen1000  
14.    link/ether 08:00:27:3a:ec:3c brd ff:ff:ff:ff:ff:ff  
15.    inet 10.0.0.2/24 brd 10.0.0.255 scope global eth1  
16.    inet6 fe80::a00:27ff:fe3a:ec3c/64 scope link  
17.       valid_lft forever preferred_lft forever  

We see vip: 10.192.203.203, which indicates that the drift is successful.

2.2.2 Backup Master 1

slightly

2.2.3 Record binlog location

Refer to 2.1.2 for logical steps.

2.2.4 Unload master 1

The content of the uninstall script is the same as "2.1.3 uninstall master 2".

2.2.5 Install 5.6.33 version of mysql

2.2.5.1 Installation script content

Note: There is only one difference between installing Master 2 scripts:

mv $dir'master2-sample-my.cnf' /etc/my.cnf

mv $dir'master1-sample-my.cnf' /etc/my.cnf

 

The following is the installation script content:

1. #Remember to upload the mysql installation package to the $dir directory first.
2. #Configuration file my.cnf uploads master 1-sample-my.cnf and modifies the value of innodb_buffer_pool_size according to the actual situation. Remember to modify the value of log-bin,relay-log,relay-log-index,log-bin according to the actual installation directory.
3. #Note that if mysqldir is not / data/mysql and changes to other directories (such as / database/mysql), you need to modify the line sed - i'47s / dataDir = / dataDir = \ database\ MySQL / G'/ etc / rc. D / mysqld in the script.
4.    
5. #Define Catalogue
6. basedir='/usr/local/mysql'  
7.         
8. mysqldir='/data/mysql'   
9.          
10. datadir=$mysqldir  
11.  #binlogdir=$mysqldir/binlog  
12.       
13. cnf=/etc/my.cnf  
14.    
15. dir='/download/'  
16.    
17. socket='/tmp/mysql.sock'  
18.    
19. port='3306'  
20.    
21. filename='mysql-5.6.33-linux-glibc2.5-x86_64'  
22.    
23. password='123456'  
24.    
25. password2=\'$password\'  
26.    
27. date=`date "+%y%m%d"`  
28.    
29. #Modify the configuration file
30. mv /etc/my.cnf /etc/my.cnf_bak_$date  
31. mv $dir'master1-sample-my.cnf' /etc/my.cnf  
32.    
33. #Build User
34. groupadd mysql  
35. useradd -g mysql mysql  
36.         
37. #Install dependency packages
38. yum install libaio -y  
39.        
40. #Decompression
41. cd $dir   
42. if ( test -s $filename )  
43. then  
44.    echo 'Undecompressed, no need to repeat decompression'  
45. else  
46.    tar -xvf $filename.tar.gz  
47. fi       
48.    
49.  #Copy the decompressed mysql directory to the system's local software directory:
50. cp $filename $basedir -r  
51.    
52. #New Directory
53. #mkdir -p $datadir  
54. #mkdir -p $binlogdir  
55. mkdir -p $mysqldir  
56. chown -R mysql:mysql $basedir  
57. chown -R mysql:mysql $mysqldir  
58.    
59. #Establishment of a basic database
60. $basedir/scripts/mysql_install_db--defaults-file=$cnf --user=mysql --basedir=$basedir --datadir=$datadir  
61. #Set the boot-up auto-start.
62. cp $basedir'/support-files/mysql.server''/etc/rc.d/init.d/mysqld'  
63. #Modify the file datadir
64. #You already know that datadir = is on line 47.
65. sed -i '47s/datadir=/datadir=\/data\/mysql/g' /etc/rc.d/init.d/mysqld  
66.    
67. chmod +x /etc/rc.d/init.d/mysqld  
68.    
69. chkconfig --add mysqld  
70.    
71. chkconfig mysqld on  
72.    
73. #Configure PATH
74. #root user
75. sed -i'/^PATH=/s/$/:\/usr\/local\/mysql\/bin/' /root/.bash_profile  
76. source /root/.bash_profile  
77.    
78. #mysql user
79. sed -i'/^PATH=/s/$/:\/usr\/local\/mysql\/bin/' /home/mysql/.bash_profile  
80. source /home/mysql/.bash_profile  
81. #Start the database.
82.    
83. service mysqld start  
84.    
85. #Open Firewall Port
86. #Open port 3306 and insert it behind the line - AINPUT-m state -- state NEW-m tcp-p TCP -- dport 22-j ACCEPT.
87. sed -i '/-A INPUT -m state --state NEW -mtcp -p tcp --dport 22 -j ACCEPT/s/$/\n-A INPUT -m state --state NEW -m tcp -ptcp --dport 3306 -j ACCEPT/' /etc/sysconfig/iptables  
88.    
89. service iptables restart  
90. #The company's firewall is closed, so the port is not open here.   
91.    
92. #Modify mysql root password
93. #Modify it to $password based on the final output of the script ('Establish the base library'root password). It needs to be executed interactively.
94. #mysql 5.6.33 default password is empty
95. mysqladmin -u root  password $password  
96.    
97.    
98. #To create a new user for remote access, the password is the same as the local user.   
99. $basedir/bin/mysql -u root -p$password -e"grant all privileges on *.* to 'root'@'%' identified by  $password2"  

2.2.5.2 Configuration file master 1-sample-my.cnf content

# The contents of master 1-sample-my.cnf and master 2-sample-my.cnf configuration files are different in only a few places:

server-id ,relay-log,relay-log-index

Configuration file content:

1. [client]  
2. port           =3306  
3. socket                =/tmp/mysql.sock  
4. [mysqld]  
5. port           =3306  
6. socket                =/tmp/mysql.sock  
7. binlog_checksum=none  
8. innodb_buffer_pool_size = Set it to the appropriate value G  
9. server-id = 1  
10. max_connections = 5000  
11. max_connect_errors = 300  
12. max_allowed_packet = 64M  
13. binlog_format=row  
14. slow_query_log  
15. long_query_time = 2  
16. sync_binlog=1  
17. innodb_support_xa=1  
18. innodb_file_per_table=1  
19. replicate-ignore-db = mysql  
20. replicate-ignore-db = information_schema  
21. relay-log=/data/mysql/master1-relay-bin  
22. relay-log-index=/data/mysql/master1-relay-bin.index  
23. log-bin=/data/mysql/master1-mysql-bin  
24. log_slave_updates  
25. [mysqldump]  
26. # Do not buffer the whole result set inmemory before writing it to  
27. # file. Required for dumping very largetables  
28. quick  
29.    
30. max_allowed_packet = 64M  
31.    
32. [mysql]  
33. no-auto-rehash  
34.    
35. # Only allow UPDATEs and DELETEs that usekeys.  
36. #safe-updates  
37.    
38. [myisamchk]  
39. key_buffer_size = 512M  
40. sort_buffer_size = 512M  
41. read_buffer = 8M  
42. write_buffer = 8M  
43.    
44. [mysqlhotcopy]  
45. interactive-timeout  
46.    
47. [mysqld_safe]  
48. open-files-limit = 8192  

 

2.2.6 Data recovery

Import backup data

mysql -u root -p < all.bak_20161125

2.2.7 Manufacturing Test Data

slightly

2.2.8 Points master 1 to master 2

The idea is the same as "2.1.7 points Master 2 to master 1".

1. change master tomaster_log_file='master2-mysql-bin.000003' ,master_log_pos=489108,master_host='10.192.203.202',master_user='RepUser',master_password='beijing',master_port=3306;  
2.  mysql> start slave;  
3. Query OK, 0 rows affected (0.03 sec)  

Finally, check whether the slave libraries on master 1 and master 2 are all normal.

Slve error was found on master 2:

Last_IO_Error: error connecting to master'RepUser@10.192.203.201:3306' - retry-time: 60 retries: 33

I manually rebuilt the duplicate user on master 1, then stop slave;start slave; no further error was reported.

I don't know why I reported this mistake. The backup file clearly shows the insertion record of the replica user.

 

A new error was reported at the start slave:

Last_IO_Error: Got fatal error 1236 frommaster when reading data from binary log: 'Could not find first log file namein binary log index file'

flush logs on master 1;

show master status;

Respecify the binlog location on Master 2 and start copying.

 

2.3 Modify parameter binlog_checksum

Initially, when mysql 5.6.33 was installed, binlog_checksum was specifically set to none, for the reasons listed below: http://blog.csdn.net/yabingshi_tech/article/details/53319904

 

Now Master 1 and Master 2 have become 5.6.33. So now you need to comment out binlog_checksum=none in the configuration file and restart mysql. By the way, vip is switched back to master 1.

master1:

Comment out binlog_checksum=none in the configuration file and restart mysql.

Start heartbeat for master 1;

 

master2:

Comment out binlog_checksum=none in the configuration file and restart mysql.

Restart Master 2 heartbeat;

 

Execute the ip addr command on master 1 to verify that vip drift is successful.

 

Check whether the parameter has been successfully modified:

mysql> show variables like '%checksum%';

+---------------------------+--------+

| Variable_name             | Value  |

+---------------------------+--------+

| binlog_checksum           | CRC32  |

| innodb_checksum_algorithm | innodb |

| innodb_checksums          | ON     |

| master_verify_checksum    | OFF   |

| slave_sql_verify_checksum | ON     |

+---------------------------+--------+