Replication of Mysql-13mysql, replication of Mysql-13mysql

Replication of Mysql-13mysql, replication of Mysql-13mysql

1. mysql replication Concept

The DDL and DML operations of the primary database are uploaded to the replication server through binary logs, and these log files are re-executed on the replication server to synchronize data between the replication server and the primary server. During the replication process, one server acts as the master server, and one or more other servers act as slave servers ). The master server re-writes updates to the binary log file and maintains an index of the file to track log loops. These logs can record updates sent to the slave server. When a slave server is connected to the master server, it notifies the master server of the location where the last successful update read from the log from the master server. The slave server accepts any updates that have occurred since then, blocks them, and waits for the master server to notify the new updates.

 

2. Usage of Replication

Data is synchronized through master-slave replication (master-slave), and the database's concurrent load capability is improved through read/write splitting (mysql-proxy), or designed as a master-slave server, after the host stops responding, you can switch the application to the slave machine for a short period of time to continue running.

Advantages:

(1) The database cluster system has multiple database nodes. If a single node fails, other normal nodes can continue to provide services.

(2) If a problem occurs on the master server, you can switch to the slave server.

(3) through replication, the query operation can be performed on the slave server, reducing the access pressure on the master server and Realizing Data Distribution and load balancing.

(4) backup can be performed on the slave server to avoid affecting the services of the master server during the backup.

 

3. Implementation of replication (3 methods)

(1) DRBD is a software-implemented, non-shared, storage replication solution that copies the content of Image Block devices between servers.

(2) Mysql cluster (also known as mysql cluster ). Mysql replicaion itself is a relatively simple structure, that is, a slave server (slave) reads binary logs from a master server and then parses and applies them to itself.

(3) In a simple replication environment, only two mysql-running hosts are required. You can even start two mysqld instances on one physical server host. One works as the master and the other acts as the slave to complete the replication environment. However, in the actual application environment, you can use the mysql replication function to build other replication architectures that are more conducive to expansion, such as the most commonly used master-slave architecture.

The master-slave architecture refers to the use of a mysql server as the master, one or more mysql servers as the slave, the master data is copied to the slave. In practical applications, the master-slave architecture mode is the most commonly used for mysql replication. Generally, in this architecture, system write operations are performed in the master, while read operations are distributed to various slave. Therefore, this architecture is particularly suitable for the high Internet read/write problems.

Mysql Database Replication involves the following steps:

(1) Enable binary logs on the master node. The operations for enabling binary logs are described in detail in log management.

(2) the I/O Process on the slave connects to the master and requests the log content after the specified location of the specified log file (or from the first log.

(3) After the master receives an I/O Process request from slave, it reads the log information after the specified log location through the I/O Process responsible for replication according to the request information, the I/O that is returned to slave. In addition to the information contained in the log, the returned information also includes the name of the bin-log file on the master end and the location of the bin-log.

(4) After the Slave I/O process receives the information, it adds the received log content to the end of the relay-log file at the slave end in sequence, and record the file name and location of the bin-log on the master end to the master-info file.

(5) After the Slave SQL process detects the new content in relay-log, it will immediately parse the content of relay-log and execute it on its own.

 

4. Centralized mysql replication Mode

In Versions later than MySQL, the new replication technology-row-based replication was introduced to improve replication. This technology focuses on the changes in the table, rather than the previous binlog mode. From mysql5.1.12, you can use the following three modes.

(1) SQL statement-based replication (statement-base replication, sbr)

(2) Row-based replication (rbr)

(3) hybrid replication (mbr)

Correspondingly, there are three binlog formats: statement, row, and mixed. In Mbr mode, the default version is "sbr. The binlog format can be dynamically changed during running. The method for setting the master-slave replication mode is very simple. You only need to add a parameter based on the previous replication configuration, as shown below:

 

binlog_format=”statement”#binlog_format=”row”#binlog_format=”mixed”

 

Of course, you can also dynamically modify the binlog format at runtime.

Mysql> set session binlog_format=”statement”

 

5. control master server operations

Master: 192.168.11.139

Slave: 192.168.11.130

(1) master server:

mysql> show variables like '%datadir%';+---------------+--------------------------+| Variable_name | Value                    |+---------------+--------------------------+| datadir       | /application/mysql/data/ |+---------------+--------------------------+

Enable binary log on the master server:

mysql> show variables like 'log_bin';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin       | OFF   |+---------------+-------+1 row in set (0.00 sec)

OFF indicates that binary logs are disabled.

Step 3:

① Enable the mysql installation directory/my. cnf

② Locate the [mysqld] label, and add the following statement under this label:

Log_bin [filename]

In this statement, log-bin indicates that the binary file is enabled; filename indicates the name of the binary log. If not specified, the default host name is followed by-bin as the file name, which is stored in the datadir directory by default. Specify binary_log here. If only binary files are generated for the specified database, add the following statement:

Binlog-do-db = db_name (database name)

If the binary file log is not generated for the specified database, add the following statement:

Binlog-ignore-db-db_name (database name)

③ Restart the mysql service. You can see "binary_log. number file, such as binary_log.00001. after mysql service is restarted, the binary file is generated again, and the number in the file name is increased once.

 

After the instance is successfully started, modify the mysql configuration file my. cnf and set the server-id. The Code is as follows:

Server-id = 1Binlog-do-db = xscjBinlog-ignore-db = mysqlServer-id = 1: Each database server must specify a unique Server-id. Generally, the master server is 1, the server-id of the master and slave cannot be the same. Binlog-do-db: indicates the database to be copied. Here, xscj is used as an example: Binlog-ignore-db: indicates the database that does not need to be copied.

 

Users required to create a copy on the master

mysql> grant replication slave on *.* to rep_user@'%';Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.01 secmysql> show master status\G*************************** 1. row ***************************            File: binary_log.000001        Position: 303    Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec)

 

Back up the data of the master host, save it in the/data/binary_dump.txt file, and import it to the slave. The specific execution statement is as follows:

[root@localhost bin]# mysqldump -h localhost>/data/binary_dump.txt

(2) control slave server operations

Modify the database configuration file of the slave server. The configuration is as follows:

Server-id = 2 # Set slave Server idMaster-host = 192.168.11.129Master-user = rep_userMaster-password = # Set the password to connect to the master Server Replicate-do-db # Set database, you can set multiple Master-port = <port >## configure the port number to restart slave. Run the following command again on mysql of the slave host to disable the slave service Mysql> stop slave; set the Server Load balancer instance to copy related information. Run the following command: Mysql> change master to> master_host = '',> master_user ='',> master_password = '',> master_log_file = 'binary _ log.000007 ',> master_log_pos = 120; input: show slave status \ G to provide slave services The key parameter information of the thread.

Common commands are as follows:

Option	Function
Slave start	Start the replication thread
Slave stop	Stop the replication thread
Reset slave	Reset the replication thread
Show slave status	Display the replication thread status
Show slave status \ g	Show replication thread status (branch display)
Show master status \ G	Displays the status of the primary database (displayed by Branch)
Show master logs	Show master database logs
Change master	Dynamically change the configuration to the master database
Show processlistv	Show which threads are running