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

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.