MHA implements manual switching of mysql Master/Slave databases, and mhamysql Master/Slave Databases

MHA implements manual switching of mysql Master/Slave databases, and mhamysql Master/Slave Databases

This article describes how MHA implements manual switching between the mysql master database and slave database for your reference. The specific method is as follows:

I. Preparations

1. Execute the following commands on the Master and Slave respectively to facilitate mha to check replication:
Copy codeThe Code is as follows: grant all privileges on *. * to 'root' @ '10. 1.1.231 'identified by 'rootpass ';
Grant all privileges on *. * to 'root' @ '10. 1.1.234 'identified by 'rootpass ';
Grant replication slave on *. * to 'jpsync' @ '10. 1.1.231 'identified by 'jppasswd ';
Grant replication slave on *. * to 'jpsync' @ '10. 1.1.234 'identified by 'jppasswd ';
Flush privileges;
2. Set the master to read-only
 
Copy codeThe Code is as follows: mysql> set global read_only = 1;
Query OK, 0 rows affected (0.00 sec)
Mysql> show variables like 'read _ only ';
+ --------------- + ------- +
| Variable_name | Value |
+ --------------- + ------- +
| Read_only | ON |
+ --------------- + ------- +
1 row in set (0.00 sec)
Interaction Mode:
Copy codeThe Code is as follows: # masterha_master_switch -- master_state = alive -- conf =/etc/masterha/app1.cnf -- new_master_host = 10.1.1.231 -- new_master_port = 63306
Or non-interactive mode:
Copy codeThe Code is as follows: # masterha_master_switch -- master_state = alive -- conf =/etc/masterha/app1.cnf -- new_master_host = 10.1.1.231 -- new_master_port = 63306-interactive = 0

Ii. How to Set 10.1.1.231 as the master node and 10.1.1.234 as the slave node after switching:

1. Execute on the master:
Copy codeThe Code is as follows: mysql> show master status;
+ ------------------------- + ---------- + ---------------- + ------------------------------------ + ------------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+ ------------------------- + ---------- + ---------------- + ------------------------------------ + ------------------- +
| Mysql-master-bin.000013 | 120 | denovo_ng | mysql, denion, test, information_schema |
+ ------------------------- + ---------- + ---------------- + ------------------------------------ + ------------------- +
1 row in set (0.00 sec)
2. Execute the following SQL command on 10.1.1.234;
Copy codeThe Code is as follows: change master to master_host = '10. 1.1.231 ', master_port = 63306, master_user = 'jpsync ',
Master_password = 'jppasswd', master_log_file = 'mysql-master-bin.000013 ', master_log_pos = 120;
 
Mysql> show slave status \ G;
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.1.231
Master_User: jpsync
Master_Port: 63306
Connect_Retry: 60
Master_Log_File: mysql-master-bin.000013
Read_Master_Log_Pos: 120
Relay_Log_File: compute-0-52-relay-bin.000002
Relay_Log_Pos: 290
Relay_Master_Log_File: mysql-master-bin.000013
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
3. view the master Status and Test
Copy codeThe Code is as follows: mysql> show slave hosts;
+ ----------- + ------ + ------- + ----------- + -------------------------------------- +
| Server_id | Host | Port | Master_id | Slave_UUID |
+ ----------- + ------ + ------- + ----------- + -------------------------------------- +
| 1052 | 63306 | 1025 | e25a3e4a-39c0-11e4-80cb-00259086c4b6 |
+ ----------- + ------ + ------- + ----------- + -------------------------------------- +
1 row in set (0.00 sec)
Insert records on the master database 10.1.1.231
Copy codeThe Code is as follows: mysql> insert into test_slave_002 values (555538511,1, 55555,99999, 44.11, 2222,91919 );
Query OK, 1 row affected (0.00 sec)
The slave database query record already exists.
Copy codeThe Code is as follows: mysql> select * from test_slave_002 where id = 555551111;
+ ----------- + ----- + ----------- + -------------- + ---------- + ---------------- + -------------- +
| Id | tag | ticket_id | candidate_id | duration | source_file_id | source_start |
+ ----------- + ----- + ----------- + -------------- + ---------- + ---------------- + -------------- +
| 555551111 | 1 | 55555 | 99999 | 44.11 | 2222 | 91919 |
+ ----------- + ----- + ----------- + -------------- + ---------- + ---------------- + -------------- +
1 row in set (0.00 sec)
4. Update the configuration file:
Update master database my. cnf Configuration Add
Copy codeThe Code is as follows: skip_slave_start
Note: Avoid database restart and slave process startup, resulting in data inconsistency.
Update the slave database my. cnf configuration and set the slave database to read-only:
Copy codeThe Code is as follows: read_only = 1
Relay_log_purge = 0
Restart the master database and slave database to observe the database information:
Master database information:
Copy codeThe Code is as follows: mysql> show processlist;
+ ---- + -------- + ---------------- + ------ + ------------- + ------ + ----------------------------------------------------------------------- + ---------------------- +
| Id | User | Host | db | Command | Time | State | Info |
+ ---- + -------- + ---------------- + ------ + ------------- + ------ + ----------------------------------------------------------------------- + ---------------------- +
| 1 | jpsync | 10.1.1.234: 49085 | NULL | Binlog Dump | 17 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 2 | root | localhost | NULL | Query | 0 | init | show processlist |
+ ---- + -------- + ---------------- + ------ + ------------- + ------ + ----------------------------------------------------------------------- + ---------------------- +
2 rows in set (0.00 sec)
 
Mysql> show master status;
+ ------------------------- + ---------- + ---------------- + ------------------------------------ + ------------------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+ ------------------------- + ---------- + ---------------- + ------------------------------------ + ------------------- +
| Mysql-master-bin.000014 | 120 | denovo_ng | mysql, denion, test, information_schema |
+ ------------------------- + ---------- + ---------------- + ------------------------------------ + ------------------- +
1 row in set (0.00 sec)
Slave database information:
Copy codeThe Code is as follows: mysql> show slave status \ G;
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.1.231
Master_User: jpsync
Master_Port: 63306
Connect_Retry: 60
Master_Log_File: mysql-master-bin.000014
Read_Master_Log_Pos: 120
Relay_Log_File: compute-0-52-relay-bin.000005
Relay_Log_Pos: 290
Relay_Master_Log_File: mysql-master-bin.000014
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
 
Mysql> show processlist;
+ ---- + ------------- + ----------- + ------ + --------- + ------ + --------------------------------------------------------------------------- + -------------------- +
| Id | User | Host | db | Command | Time | State | Info |
+ ---- + ------------- + ----------- + ------ + --------- + ------ + --------------------------------------------------------------------------- + -------------------- +
| 1 | system user | NULL | Connect | 58 | Waiting for master to send event | NULL |
| 2 | system user | NULL | Connect | 58 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
| 3 | root | localhost | NULL | Query | 0 | init | show processlist |
+ ---- + ------------- + ----------- + ------ + --------- + ------ + --------------------------------------------------------------------------- + -------------------- +
3 rows in set (0.00 sec)

I hope this article will help you design MySQL database programs.

How does mysql implement master-slave synchronization database backup?

1. master server:
# Master start
Log-bin = "d:/log/mysql/mysql_log_bin"
Server-id = 1
# Master end
2. slave server:
# Slave start
Log-bin = "D:/log/mysql2/log-bin.log"
Relay_log = "D:/log/mysql2/relay-log-bin"
# Slave id, different from the host id
Server-id = 2
# Host ip address, used by the slave machine to connect to the host
# Master-host = localhost
# Host port
# Master-ports = 3300
# The account you just created to copy host data from the slave machine
# Master-user = slave
# The new password for copying host data from the slave
# Master-password = 654321
# Retry Interval: 10 seconds
# Master-connect-retry = 10
# Databases to be synchronized
# Replicate-do-db = test
# Enable slave database logs for chained Replication
Log-slave-updates
# Whether the slave database is read-only. 0 indicates read/write, and 1 indicates read-only.
Read-only = 1

# Copy only a table
# Replicate-do-table = tablename
# Copy only some tables (matching characters available)
# Replicate-wild-do-table = tablename %
# Only copy a database
# Replicate-do-db = dbname
# Do not copy a table
# Replicate-ignore-table = tablename
# Do not copy some tables
# Replicate-wild-ignore-table = tablename %
# Do not copy a database
# Replicate-ignore-db = dbname
# Slave end
3. Set the MASTER server's change master statement for the slave server
Note: 1. You must create a user on the master server that can execute replication.
2. the user name can be remotely logged on to the master server.
3. Enable MySQL log-bin.
Reference: blog.163.com/..31959/

When the MySQL database Master/Slave structure is used, what problems can be encountered when there are too many slave servers?

I am not using this database.