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

1. Fault level

S1 is the most serious, affecting 30% of the transaction volume for more than 15 minutes, or affecting 30% of users' normal access for more than 15 minutes.

S2 is more serious, affecting 15% of the transaction volume for more than 15 minutes.

S3 serious failure, affecting 5% of the transaction volume for about 15 minutes

S4 failure, affecting 1% of transaction volume

two。 Performance problem

Scale up: hardware upgrade of a single server to improve performance, easy to reach the limit

Scale out: increase the number of servers and make use of load balancing for unified management

3.mysql master-slave replication process architecture diagram

Step 1: mysql writes the transaction serial to the binary log

Step 2: slave copies the binary log (binary log events) of master to its relay log through the Ibind O thread.

Step 3: slave's SQL (from the thread) reads events from the relay log and replays the events to update the data in slave to make it consistent with the data in master.

4. Database master-slave schema replication (turn off selinux and iptables)

Environment preparation: one to do mysql master server, one to do mysql slave server, the clock had better be synchronized.

1) configure the primary master server

2) configure master master server

Configure the / etc/my.cnf file first

Server-id=1 # configures an ID number to distinguish it from other servers

Log-bin=mysql-bin # Open mysql log in binary format

Next, create an account for slave.

MariaDB [(none)] > GRANT REPLICATION SLAVE, REPLICATION CLIENT ON. TO slave@'172.17.253.127' IDENTIFIED BY '123456'

View primary server status

Show master status

3) configure slave server

Configure the / etc/my.cnf file first

Server-id=2 # configures the ID number to distinguish it from other servers

Relay-log=mysql-relay-log # Open mysql log in binary format

Read-only=1 # set read-only permission

Log-bin=mysql-bin # Open a slave server binary log

Log-slave-updates=1 # writes updated data to the binary log

Next, start the replication thread from the server (within a network segment with master)

MariaDB [(none)] > CHANGE MASTER TO MASTER_HOST='172.17.253.191', MASTER_USER='slave', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=419

Start the replication thread

Start slave

Check the server status. If you have these two items, you will start successfully.

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

4) create a library on master, which can be seen on slave. End!

5.mysql replication filter

Concept: there are two ways to achieve this: it does not copy all data, but only copies data related to one or more databases.

One is to set up on the primary server to record only write operations related to a specific database to the binary file.

Binlog_do_db= just does something.

Binlog_ignore_db= does everything except the set operation.

Second, in the setting of the slave server, only the database or table-related events of interest are placed in the SQL thread.

Replicate_do_db= reads only the specified data

Replicate_ignore_db= reads all data except the specified data

6. Common database architecture in enterprises

1) single master and multiple slave

In fact, 90% of the architecture is like this, because in most cases, read access is much more than write access. In this case, if the real-time requirements for data are not very high, simply increasing the number of slave is relatively cheap and effective.

2) two master are master and slave to each other.

The two servers replicated by Master-master are both master and the other's salve, so that changes made by either party are applied to the other's database through replication.

Do the following on # 172.17.253.191

Add to the My.cnf configuration file

Auto_increment_offset=1

Auto_increment_increment=2 # # use odd ID

Configure the database

MariaDB [LN] > GRANT REPLICATION SLAVE, REPLICATION CLIENT ON. TO slave@'172.17.253.127' IDENTIFIED BY '123456'

MariaDB [LN] > CHANGE MASTER TO MASTER_HOST='172.17.253.127', MASTER_USER='slave', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=245

Start slave

Show status slave\ GbomIO and SQL can be yes.

Do the following on 172.17.253.127

Auto_increment_offset=2

Auto_increment_increment=2## uses even ID

Configure the database

MariaDB [LN] > GRANT REPLICATION SLAVE, REPLICATION CLIENT ON. TO slave@'172.17.253.191' IDENTIFIED BY '123456'

MariaDB [LN] > CHANGE MASTER TO MASTER_HOST='172.17.253.191', MASTER_USER='slave', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=245

Start slave

Show status slave\ GbomIO and SQL can be yes.

Quiz:

Create a table on 191and set it to self-growing ID to verify the effect

Using the LN database

Create a self-growing ID table

Insert data on 127

View the table, successful

7. Self-growing ID, primary key, foreign key, index

Self-growing ID: for some unique fields, such as the student number of a class, you can set the self-growing ID. The data of the self-growing ID means that there is a unique record in this table, and the self-growing ID will certainly not be repeated.

Primary key: a record in a relational database has several attributes. If a property group (note that it is a group) can uniquely identify a record, the attribute group can become a primary key. For example, in the student table (student number, name, gender, class), the student number is a primary key.

Foreign keys: for example, the grade sheet (student number, course number, grade), the student number and the course number are called the primary key, because a single student number or course number cannot identify a student's grade, but in the student table (student number, name, gender, class), the student number is the primary key, and the student number in the grade table is the foreign key in the student table.

Foreign keys are mainly used for table query, that is, after querying the relevant information through the student number, and then mapping the student number to the score table, combined with the course number, you can check the results.

Index: the key to fast search. If there is no index, the whole database will be queried, which is relatively slow.

8. Replication strategy: asynchronous, fully synchronous, semi-synchronous

Async: this is the default replication policy. Master will immediately return the result to the client after executing the client's request, regardless of whether slave has accepted and processed it. This will bring a problem. When master goes down, all the data of master may not be transferred to slave. If you forcibly promote slave to master at this time, the data will be incomplete.

Full synchronization: when master executes a client request, it waits for all slave to perform data replication before returning the transaction to the client. However, this performance will be seriously affected.

Semi-synchronization: between the above two, when the master executes the client's request, it will wait for at least one slave to complete the data replication before returning the transaction to the client, which improves the security of the data, but also causes a certain degree of delay. Therefore, semi-synchronization is best used in networks with low latency.

9. Turn on semi-synchronous replication

Master

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'

SET GLOBAL rpl_semi_sync_master_enabled=ON

SHOW GLOBAL VARIABLES LIKE 'rpl_semi%'

| | rpl_semi_sync_master_enabled | ON |

| | rpl_semi_sync_master_timeout | 10000 | |

| | rpl_semi_sync_master_trace_level | 32 | |

| | rpl_semi_sync_master_wait_no_slave | ON |

+-+ +

From the top

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so'

MariaDB [mydb] > STOP SLAVE IO_THREAD

MariaDB [mydb] > SET GLOBAL rpl_semi_sync_slave_enabled = ON

MariaDB [mydb] > START SLAVE

MariaDB [mydb] > SHOW GLOBAL VARIABLES LIKE 'rpl_semi%'

Check to see if it is effective

Tail-200 / var/log/mariadb/mariadb.log, effective if the following records

10.MHA

1)

What is it: (Master HA), that is, master height availability, which provides automatic master function for mysql master-slave replication architecture. When master goes down, mha will monitor the failed node and upgrade the slave node with the latest data to the new master, and will automatically obtain additional information through other nodes to avoid consistency problems. Mha also provides master online switching function. That is, switch the master/slave node as needed.

2) Summary of the working principle of MHA

 saves binary log events from crashed master

 identifies the slave with the latest updates

 application differential relay logs to other slave

Binary log events saved from master by  application

 promotes a slave to a new master

 uses another slave to connect to the new master for replication

MHA of 11.Mysql High availability Architecture

1) Environmental preparation

One is a manager node, one is a master node, and more than one slave node.

2) add to the configuration content of the / etc/hosts file of each node:

172.17.253.25 node1.magedu.com node1

172.17.253.127 node2.magedu.com node2

172.17.253.191node3.magedu.com node3

The purpose of this is to find the faulty machine more quickly through the corresponding number of the domain name in the future work.

3) matse node configuration

[mysqld]

Server-id=1

Log-bin=master-log

Relay-log=relay-log

Skip_name_resolve=ON

Slave node configuration

[mysqld]

Server-id = 12

Relay-log = relay-log

Log-bin = master-log

Read_only = ON

Relay_log_purge = 0

Skip_name_resolve = YES

4) do master-slave replication to ensure that slave and master work properly, and that IO and SQL threads on slave are linked properly (above)

5) prepare the communication environment based on ssh

Ssh-keygen-t rsa # generate key

Ssh-copy-id root@172.17.253.191 # passes the public key to the machine that needs a face-to-face password to log in, and after entering the yes and password, it is successful.

6) install on master

Mha4mysql-manager-0.56-0.el6.noarch.rpm and mha4mysql-node-0.56-0.el6.norch.rpm.

Install mha4mysql-node-0.56-0.el6.norch.rpm. 0.el6.norch.rpm on master and all slave nodes.

7) define the configuration file for mha

8) check whether the ssh communication between each node is OK on manager

Check whether the link configuration of the replication cluster is normal after OK

If there is an error, you can see what the error indicates. It may be a permission problem, a firewall problem, a master-slave replication failure, and so on.

9) start mha

Detect open statu

10) simulate master failure, turn off the mariadb service on master, and check the manager.log log to see if master is automatically transferred to slave.

11) after the transfer is successful, configure master and slave, and then start the mha service to OK

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.