Mysql- high availability cluster [master-slave single mode -binlog]

Operation process:

  • Install mysql service respectively in master and slave nodes
  • Set common mysql configurations in master and slave nodes
    1. Add administrator user, setting: server ID
    2. Enable binlog to synchronize users
    3. Add firewall rules
  • Synchronize master node
    1. Set synchronization information of primary node: file, index location
    2. View status
    3. Start synchronization
  • Test synchronization operation
  • Resynchronization after slave node failure

Node IP:

  • Main node: 192.168.2.40
  • Slave node: 192.168.2.41

1. Install mysql services on the primary and secondary nodes respectively:

Download and install: Mysql5.7, page:
https://dev.mysql.com/downloads/mysql/5.7.html#downloads
Download address (my operating system centos):
https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.25-1.el7.x86_64.rpm-bundle.tar
Need to build:

// Uninstall - mariadb
        rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64
// Upload to server:
        /usr/local/src/mysql-5.7.1
// install
        rpm -ivh *.rpm
// Set service self start, start
    systemctl enable mysqld && systemctl start mysqld
// mysql-5.7 will write the initialization password in the log file: 
        cat /var/log/mysqld.log | grep password
        A temporary password is generated for root@localhost: %!CpVaxli5#s
// Test login
    mysql -uroot -p
    %!CpVaxli5#s

2. Set common mysql configuration in [Master, slave node], add administrator user, and set: server ID

2-1. Configure services in primary and secondary nodes:

vim /etc/my.cnf
// character set
character_set_server = utf8
// Node ID: Master, cannot be consistent from the difference
server-id = 40
// Enable binlog in [master node]
log-bin = mysql-bin
// Password security level verification cancelled
validate_password = OFF

// Restart service
systemctl restart mysqld

2-2. Add user in primary and secondary node:

// Sign in
mysql -uroot -p
%!CpVaxli5#s

// Change password
set password for root@localhost=password('Aa123456');

// Add external access user
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'Aa123456' WITH GRANT OPTION;
FLUSH PRIVILEGES;

// Click master node to add synchronization user
GRANT REPLICATION SLAVE ON *.* to 'repl'@'%' identified by 'Aa123456';

2-3. [Master, slave] open the port, test the link:

// Allow 3306 port and restart the firewall to take effect
firewall-cmd --permanent --add-port=3306/tcp && firewall-cmd --reload

3. Synchronize master node with slave node

3-1. View binlog information in main node

mysql -uroot -pAa123456
show master status;
![](https://s1.51cto.com/images/blog/201904/05/e20a25e3c7881d6a2a752f1a14db15f9.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)

3-2. Set synchronization in slave node

// Set synchronization information of primary node
mysql -uroot -pAa123456
change master to master_host='192.168.2.40',master_port=3306,master_user='repl',master_password='Aa123456',master_log_file='mysql-bin.000001',master_log_pos=154;
// To view synchronization status:
show slave status \G;
![](https://s1.51cto.com/images/blog/201904/05/25e790b30b51252c13a4d7613e13d269.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=)
// Start synchronization:
start slave;
// To view synchronization status:
show slave status \G;
    Slave_IO_Running: YES
    Slave_SQL_Running: YES
// View threads (you can see these two threads: waiting for the main server to update the event, waiting for reading the local log to write data):
show processlist;

4. Test master-slave synchronization

//1. Remote connection to the main database: create data, tables, add data
 //2. Check whether it has been synchronized from the database

5. Resynchronization after failure of slave node

# Primary node
reset master;
show master status \G;

# Slave node
stop slave;
reset slave;
change master to master_host='192.168.2.40',master_port=3306,master_user='repl',master_password='Aa123456',master_log_file='mysql-bin.000001',master_log_pos=154;
start slave;

# View synchronization status
show slave status \G;

# Add data test slave synchronization to [master node]

# If you clone from a virtual machine, you need to pay attention to the following points:
1.change IP
2.delete: /var/lib/mysql/auto.conf´╝îuuid Save the file. It will not be generated automatically when restarting
3.change: /etc/my.cnf 
    server-id
4.restart mysqld service:
    systemctl restart mysqld

Tags: Big Data MySQL firewall RPM MariaDB

Posted on Mon, 02 Dec 2019 01:29:40 -0500 by mnetsys