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

This article mainly explains "MySQL master-slave configuration and mysqldump backup steps", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn the steps of MySQL master-slave configuration and mysqldump backup.

1. MySQL master-slave concept

The master-slave replication of MySQL is used to establish a database environment exactly the same as the master database. The slave database will synchronize all the data of the master database and can easily achieve failover.

1.1 the main role of MySQL master and slave

In a system with complex business, there is a scenario in which a sql statement needs to lock the table, resulting in the temporary inability to use the read service, which will affect the running business. Use master-slave replication, and let the master database be responsible for writing and the slave database for reading. In this way, even if the master database locks the table, the normal operation of the business can be ensured by reading the slave database.

As a hot backup of data, the main database can be replaced in time after downtime to ensure business availability.

An extension of the architecture. Due to the increasing volume of business, the access frequency of Iamp O is too high to be satisfied by a single machine. At this time, the storage of multiple databases can be done to reduce the access frequency of disk Iamp O and improve the performance of single machine.

1.2 Common MySQL Master-Slave Architecture

The process of 1.2.1MySQL master-slave replication

Update events (update, insert, delete) of the main library are written to binlog

Start from the library and initiate a connection to the main library

The master library creates a binlog dump thread to send the contents of the binlog to the slave library

After booting from the slave library, create an binlog O thread that reads the content of the main library and writes it to relay log

After starting from the library, create a SQL thread that reads from the relay log, executes the read update event from the Exec_Master_Log_Pos location, and writes the update to the slave database.

1.2.2 the principle of MySQL master-slave replication

MySQL master-slave replication is an asynchronous replication process, the master library sends update events to the slave library, reads the update record from the slave library, and executes the update record to make the content of the slave library consistent with that of the master library.

Binlog:binary log, which holds binaries for all update event logs in the main library. Binary log is a file that keeps records of all changes (database structure and content) in the database from the moment the database service starts. In the master library, whenever an update event occurs, it is written to the binary log in turn, and then pushed to the data source from the library as a copy from the library.

Binlog output thread: whenever a slave library connects to the master library, the master library creates a thread and sends binlog content to the slave library. For each sql event to be sent to the slave library, the binlog output thread locks it. Once the event is read by the thread, the lock is released, even when the event is fully sent to the slave library.

In the slave library, when replication begins, the slave library creates the slave library Imax O thread and the slave library SQL thread for replication processing.

Slave start slave O thread: after the execution of the slave statement, a slave database is created. The thread connects to the master library and requests the master library to send update records in binlog to the slave library. The updates sent by the binlog output thread of the main library are read from the library Iswap O thread and copied to the local file, including the relay log file.

SQL thread from the library: create a SQL thread from the library that reads and executes the update event written from the library Imax O thread to relay log.

To sum up, we can know:

For each master-slave replication connection, there are three threads. The master library with multiple slave libraries creates an binlog output thread for each slave library connected to the master library, each with its own I / O thread and SQL thread.

The slave library separates the read and write from the library when copying by creating two separate threads. Therefore, even if the thread responsible for execution runs slowly, the thread responsible for reading the update statement does not become slow as a result. For example, if the slave library hasn't been running for a while, its binlog thread can quickly read all SQL content from the main library, even though its SQL thread executes slowly. In this way, even if the slave library stops running before the SQL thread finishes executing all the read statements, the Ibig O thread at least fully reads everything and backs it up safely in the relay log local to the slave library, ready to execute the statement the next time the slave library starts.

1.3 MySQL Master / Slave deployment node3:master,192.168.48.183node4:slave, 192.168.48.1841.3.1 master configuration # install MySQL/mariadb database: [root@node03 ~] # yum install mariadb mariadb-server-y # modify / etc/my.cnf configuration file Add the following line in the [MySQLd] directive: log-bin=node3-binserver-id=1# starts database service: [root@node03 ~] # systemctl start mariadb [root@node03 ~] # # View MySQL process: [root@node03 ~] # ps-ef | grep MySQLdMySQL 6130 10 20:37? 00:00:00 / bin/sh / usr/bin/MySQLd_safe-- basedir=/usrMySQL 6316 6130 0 20:37? 00: 00:00 / usr/libexec/MySQLd-- basedir=/usr-- datadir=/var/lib/MySQL-- plugin-dir=/usr/lib64/MySQL/plugin-- log-error=/var/log/mariadb/mariadb.log-- pid-file=/var/run/mariadb/mariadb.pid-- socket=/var/lib/MySQL/MySQL.sockroot 6365 5819 0 20:38 pts/0 00:00:00 grep-- color=auto MySQLd [root@node03 ~] # # View MySQL port: [ Root@node03 ~] # netstat-ntlp | grep 3306tcp 00 0.0.0.0 ntlp 3306 0.0.0.0 LISTEN 6316/MySQLd [root@node03 ~] # 1.3.2 check whether the configuration is valid # enter the database directly through MySQL: [root@node03 ~] # MySQLWelcome to the MariaDB monitor. Commands end with; or\ g.Your MariaDB connection id is 2Server version: 5.5.65-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.MariaDB [(none)] > # check whether log_bin and sql_log_bin are both on;MariaDB [(none)] > show variables like "% log_bin" +-+-+ | Variable_name | Value | +-+-+ | log_bin | ON | | sql_log_bin | ON | +-+-+ 2 rows in set (0.00 sec) MariaDB [(none)] > 1.3.3 grant Rights from library MariaDB [(none)] > grant replication slave on *. * to "superman" @ "192.168.48.184" identified by "123456" Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] > flush privileges;Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] > 1.3.4 View master status xxxxxxxxxxMariaDB [(none)] > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-- -+-+ | node3-bin.000004 | 479 | +- + 1 row in set (0.00 sec) MariaDB [(none)] > 1.3.5 slave side configuration # modify / etc/my.cnf configuration file Add the following line under the [MySQLd] instruction block: server-id=21.3.6 starts the slave database service [root@node04 ~] # systemctl start mariadb1.3.7 specifies master[ root @ node04 ~] # MySQLWelcome to the MariaDB monitor on the slave database. Commands end with; or\ g.Your MariaDB connection id is 2Server version: 5.5.65-MariaDB MariaDB ServerCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.MariaDB [(none)] > MariaDB [(none)] > change master to-> master_host= "192.168.48.183",-> master_user= "superman",-> master_password= "123456",-> master_log_file= "node3-bin.000004",-> master_log_pos=479 Query OK, 0 rows affected (0.02 sec) MariaDB [(none)] > 1.3.8 View slave status MariaDB [(none)] > slave start Query OK 0 rows affected (0.00 sec) MariaDB [(none)] > MariaDB [(none)] > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168. 48.183 Master_User: superman Master_Port: 3306 Connect_Retry: 60 Master_Log_File: node3-bin.000004 Read_Master_Log_Pos: 479 Relay_Log_File: mariadb-relay-bin.000002 Relay_Log_Pos: 529 Relay_Master_Log_File: node3-bin. 000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 479 Relay_Log_Space: 825 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids : Master_Server_Id: 11 row in set (0.00 sec) MariaDB [(none)] > 1.3.9 verify data synchronization # create a database in the main library: MariaDB [(none)] > create database zabbix charset=utf8 Query OK, 1 row affected (0.00 sec) MariaDB [(none)] > MariaDB [(none)] > show databases +-+ | Database | +-+ | information_schema | | MySQL | | performance_schema | | test | | zabbix | +-+ 5 rows in set (0.00 sec) MariaDB [(none)] > # check from the library: MariaDB [(none)] > show databases +-+ | Database | +-+ | information_schema | | MySQL | | performance_schema | | test | | zabbix | +-+ 5 rows in set (0.00 sec) MariaDB [(none)] > 1 .3.10 synchronization error analysis xxxxxxxxxxSlave_IO_Running: Connecting# first: master library downtime # second: the username and password specified by the slave library are incorrect (inconsistent with the authorized username and password of the master library) # third: turn off the firewall Slave_IO_Running: No# the binary specified by the slave library is incorrect Slave_SQL_Running: No# pos point problem 1.4 master-slave replication delay problem and solution 1.4.1 excessive slave libraries

It is recommended that the number of slave libraries is 3-5. Too many slave nodes to be replicated will cause replication delay.

1.4.2 hardware difference of slave library

The hardware of the slave library is worse than that of the master database, resulting in replication delay. Viewing the system configuration of master and slave may cause replication delay due to machine configuration problems, including disk IO, CPU, memory and other factors, which generally occur in scenarios with high concurrency and large data writes.

1.4.3 Network issu

The network delay between master and slave libraries, network cards, network cables, connected switches and other network devices of the master library may become the bottleneck of replication, resulting in replication delay.

1.5 MySQLdump backup 1.5.1 only the tables are backed up, not the data itself # backup all tables in the zabbix database, but the statements to create the zabbix database are not automatically generated: [root@node03 ~] # MySQLdump-uroot-paired tables * zabbix > zabbix_ tables.sql [root @ node03 ~] # 1.5.2 backup zabbix database and tables backup all tables in the zabbix database and generate SQL statements to create the zabbix database That is, you do not need to create a database first when importing: [root@node03 ~] # MySQLdump-uroot-paired databases *-- databases zabbix > zabbix_ database.sql [root @ node03 ~] # 1.5.3 backup multiple databases [root@node03 ~] # MySQLdump-uroot-paired databases *-- databases zabbix MySQL > zabbix_MySQL_ database.sql [root @ node03 ~] # 1.5.4 backup all databases [root ~] # MySQLdump-uroot- Paired databases *-- all-databases > all_ databases. Sql [root @ node03 ~] # or: [root@node03 ~] # MySQLdump-uroot-paired databases *-A > all.sql [root@node03 ~] # 1.5.5 backup zabbix database And record the pos point [root@node03 ~] # MySQLdump-uroot-paired backup *-- master-data zabbix > zabbix_ pos.sql [root @ node03 ~] # 1.5.6 back up the database, and refresh the log [root@node03 ~] # MySQLdump-uroot-paired backup *-- master-data-- flush-logs zabbix > zabbix_pos_ flush.sql [root @ node03 ~] # so far, I believe you have a better understanding of "MySQL master-slave configuration and mysqldump backup steps" You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.