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

1. Brief introduction

MySQL is one of the most widely used SQL databases in enterprise applications. It can manage transactions and ACID behavior in the kernel, and the use of the database itself and related commands are very convenient.

The MySQL server is a core and important component in the open source Web application framework LAMP (including Linux,Apache,MySQL and PHP). The MySQL database server is written in C and C + + and internally uses a lexical analyzer to parse and understand SQL queries.

As the system becomes decentralized, scalable, and highly fault-tolerant, we are increasingly unable to withstand failures in the database, such as database servers that fail and cannot be managed automatically. Therefore, this article will discuss database replication with you.

When the MySQL database of the system fails, using database replication, we can transfer to its copy and manage data from it, even users are not aware of the error in the database. The original reasons why different enterprises use database replication include, but are not limited to:

Ensure that data is backed up directly from the database

Run the analysis or check the data without interfering with the main database

Extend the database for better performance

2. MySQL settings

We created two new servers with different IP and used them as master and slave servers in the replica set. For further research, we set up MySQL server and client tools on them.

Install the MySQL server and client:

Sudo apt-get install mysql-server mysql-client

After running this command, the above program is installed on the server, and then the same configuration is made on both servers and the MySQL root password is set:

Set Root password

After the installation process is complete, use the following command to confirm that the MySQL server is up and running:

Sudo service mysql status

Output:

Check MySQL server status

The MySQL server is up and running, connecting with the username and password during the installation.

Log in to MySQL

Mysql-u root-p

At this point, the MySQL server will wait for us to enter the password, and for security reasons, the password will not be echoed to the terminal. After logging in to the MySQL command line, the following prompt appears:

MySQL login

When we enter the MySQL command prompt, we can use the given command to display the database that exists on the system and ensure that MySQL is running properly:

Show all databases

Show databases

Output:

Check the MySQL database

In the output, MySQL displays only a list of MySQL default databases for administrative purposes. As long as we see the Active status on both servers, we can continue with the configuration of the Master and Slave databases.

3. Master MySQL server configuration

After MySQL is installed, we can configure the master database, that is, add the configuration in the main MySQL configuration file, open it on Ubuntu using the nano editor and execute the following command:

Edit configuration file

Sudo nano / etc/mysql/mysql.conf.d/my.cnf

This file contains a number of options that allow you to modify and configure the behavior of MySQL servers running on your system. First, we need to find the bind-address attribute in the file:

Bind address attribute

# Instead of skip-networking the default is now to listen only on

# localhost which is more compatible and is not less secure.

Bind-address = 127.0.0.1

Modify this IP to the current server IP:

Update the Bind Address property

Bind-address =

View the server-id properties:

Server ID Properties

# note: if you are setting up a replication slave, see README.Debian about

# other settings you may need to change.

# server-id = 1

Update server ID properties

Server-id = 1

The log_bin attribute notifies the file to which the replica set details are actually saved.

Log Bin attribute

Log_bin = / var/log/mysql/mysql-bin.log

In this file, the slave server records the changes it holds from the master database. Now we will uncomment the property and edit the binlog_do_db property, which tells the database server which database to copy from. We can include multiple databases by repeating this row for all the databases we need:

DB backup:

Binlog_do_db = jcg_database

Update properties displayed in the configuration file:

Updated configuration file

After we have completed all the properties, we can save the file and restart the MySQL server so that these updates are reflected in the server. To restart the MySQL server, run the following command:

Restart MySQL:

Sudo service mysql restart

Once the MySQL server is restarted, the next change we need to make is inside the MySQL shell itself. Log in to the MySQL command line again.

Authorize Slave DB so that it can access and copy the data jcg_database in the database we mentioned in the configuration file.

Grant authority

GRANT REPLICATION SLAVE ON *. * TO 'root'@'%' IDENTIFIED BY' password'

Refresh permissions:

FLUSH PRIVILEGES

Switch to the database to be replicated after creation:

Mysql > CREATE SCHEMA jcg_database

Query OK, 1 row affected (0.00 sec)

Mysql > USE jcg_database

Database changed

Lock the database and prohibit changes:

Read Lock:

FLUSH TABLES WITH READ LOCK

Before applying locks, we need to develop some new tables and insert data.

Check master statu

SHOW MASTER STATUS

Output:

Master database status

It is important to note that this is where the secondary DB starts replicating the database. If we make any changes to DB, it will automatically unlock, so don't make any new changes in the same window. The next part is a bit tricky, open a new terminal window or tab (do not close the current tab) and log in to the MySQL server and execute the following command:

Dump MySQL

Mysqldump-u root-p-- opt jcg_database > jcg_database.sql

Output:

MySQL dump

Exit the new tab that was opened separately and return to the old tab. On this tab, unlock the database and exit MySQL:

Unlock and exit

UNLOCK TABLES

QUIT

In this way, we have completed all the configurations required on the master database.

4. Secondary MySQL server configuration

Now we are ready to configure the slave database for replicating data, log in to the Slave server and open the MySQL command line in it. Create a database with the same name, copy and exit the MySQL terminal:

MySQL Slave DB

Import the original database into the Slave MySQL server using the SQL file we created, make sure to bring the file to this new server and run the following command to import it into the secondary MySQL database:

Import database

Mysql-u root-p jcg_database

< /root/jcg_database.sql 点击Enter后，数据库内容和元数据将导入从数据库。完成之后，我们也可以配置Slave MySQL DB： 配置DB nano /etc/mysql/mysql.conf.d/mysqld.cnf 我们需要确保此配置文件中的某些属性已设置，server-id设置的默认值为1，也可利用下面命令设置为其它值： 这个财产是server-id。它当前设置为1，这是默认值。将其设置为其他值： Server ID for Slave server-id = 2 Slace的其他属性设置： relay-log = /var/log/mysql/mysql-relay-bin.log log_bin = /var/log/mysql/mysql-bin.log binlog_do_db = jcg_database 添加relay-log属性，因为默认情况下它不在配置文件中。完成此操作后，还需要重新启动Slave MySQL DB，配置更改才能生效。 重启MySQL sudo service mysql restart 一旦MySQL服务器重新启动，我们需要做的下一个更改是在MySQL shell本身内部。所以再次登录MySQL命令行。 在MySQL shell中，执行以下命令： 启用复制 CHANGE MASTER TO MASTER_HOST='',MASTER_USER='root', MASTER_PASSWORD='hello123', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 1306; 此命令一次完成各个步骤，包括： 通知当前MySQL服务器，它是给定的MySQL主服务器的Slave 为Slave提供了Master Server的登录凭据 通知Slave需要启动复制过程的位置以及日志文件详细信息 使用以下命令最终激活从服务器： 激活MySQL Slave Server START SLAVE; 使用以下命令查看一些主要细节： MySQL主状态 SHOW SLAVE STATUS\G; 输出： MySQL主状态信息 mysql>

SHOW SLAVE STATUS\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 206.189.133.122

Master_User: root

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 1306

Relay_Log_File: mysql-relay-bin.000002

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000001

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: 1306

Relay_Log_Space: 527

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: 0

Master_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: 1

Master_UUID: 3b890258-be5d-11e8-88c2-422b77d8526c

Master_Info_File: / var/lib/mysql/master.info

Slave_SQL_Running_State: Slave has read all relay log

Waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

If you have problems connecting, you can try to start slave using the command:

MySQL master statu

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; SLAVE START

In this way, we have completed the configuration of MySQL replication, the data is being replicated on the MySQL server, and try to insert some data into the Master database and check to see if the data is also replicated to the slave database.

5. Replication lag

MySQL replication uses two threads to replicate between the primary and secondary databases:

1. IO_THREAD

2. SQL_THREAD

IO_THREAD connects to the primary MySQL server, reads binary logs to track and change events in the database, copies them to local relay log files, SQL_THREAD of the Slave database reads and tracks changes, and copies them to the Slave database.

If we observe any replication latency, we first need to determine whether the delay comes from Slave's IO_THREAD or Slave's SQL_THREAD.

In general, the I / O thread does not cause any significant replication delays because it only reads binary logs from the primary database, but some factors affect its performance, such as network connections, network latency, and the speed of the communication network. If there are a large number of writes on the Master, replication may be slow due to bandwidth problems.

On the other hand, if the SQL thread is delayed in Slave, the most likely reason is that the SQL query of the primary database needs to be executed in the Slave database for a long time. In addition, prior to MySQL 5.6, slave was single-threaded, which is another reason for the delay of slave SQL_THREAD.

6. Advantages of replication

MySQL replication has some obvious advantages in a production environment:

Performance: the Slave server can be easily used to provide READ support to any client that requests data. This means that the load on the Master database is much lower because it is not read.

Backup performance: if you have any running backup tasks, you can run it through the Slave database when replicating data. This means that the backup job does not affect the Master database at all.

Disaster recovery: in the event that the Master database is completely offline, if configured in this way, the Slave database can quickly replace it and start writing. This will allow minimum site downtime when rebuilding and restoring the primary server.

7. The shortcomings of replication

Judging from the above, MySQL Replication is good, but it also has many disadvantages:

Complexity: applications with a lot of Slave to replicate can cause maintenance nightmares if not managed correctly.

Performance: to complete the replication process, the binary log needs to be written to disk, and although its impact may be small, it still needs to be considered when looking at the overall server performance. You can limit the IO performance problem by writing binary logs to a separate partition of the disk.

8. Limitations of replication

In addition to the above, there are some restrictions on data replication that need to be explained:

Replication is not a backup of the application logic, and any changes made on the Master database are always replicated to the Slave database and cannot be restricted. If the user deletes data on the master database, it will also be deleted in the Slave database.

In the case of multiple Slaves, performance does not increase, but decreases, because database connections are distributed across multiple servers and the risk of problems in the event of any server failure increases.

9. Type of MySQL replication

In essence, MySQL supports three different ways to copy data from the primary server to the secondary server. All of these methods use binary logs, but it is different from the way logs are written. Here is how to copy:

Statement-based replication: using this method, each changed SQL statement in the database is stored in a binary log file. The slave device reads these SQL statements and executes them on its own MySQL database to generate an exact copy of the data from the master server. This is the default replication method in MySQL 5.1.11 and MySQL 5.1.29.

Row-based replication: in this method, the binary log file stores all record-level changes that occur in the primary database table. Read this data from the server and update its own records based on the master data to produce an exact copy of the master database.

Mixed format replication: in this approach, the server will dynamically choose between statement-based replication and row-based replication, depending on certain conditions, such as using user-defined functions (UDF), using INSERT commands with DELAYED clauses, temporary tables, or statements using system variables. This is the default replication method in MySQL 5.1.12 through MySQL 5.1.28.

In a use case, when you are not sure which replication method to use, it is best to use statement-based replication because it is the most common and easiest way to perform. If you have a system with heavy writes, statement-based replication is not recommended because it also applies table locks. In this case, you can use a row-based replication method.

10. Impact on performance

As mentioned earlier, replication may affect the performance of the database, but compared to other things, the impact of replication on the primary server is usually very small, because master only needs to do two important things in the replication environment:

Make the event and write it to the binary log on the local hard drive

Send each copy of the event it is written to the binary log to each connected slave station

Even if there is no replication, the binary log is always open, so there is no need to include the binary log when considering the replication cost.

In addition, the cost of sending replication events to the slave device is negligible because the slave device is responsible for maintaining a persistent TCP connection to the master device, which simply copies the data to the socket when the event occurs. In addition, the master device does not care at all about whether the slave device is or is suitable for execution.

Part of the exception in the last statement is semi-synchronous replication, which is not the default value. In this mode, the master server waits for at least one slave server to confirm the receipt and persistence of binary log events from each transaction (although not actually executed), and then the master server returns control to the client each time it commits.

In any case, the master server is not responsible for actually performing updates on the slave server, it only sends two things to the slave server: a copy of the actual input query running (statement-based mode) or the rows that the data is actually inserted / updated / deleted for each query (in row-based mode). In mixed mode, the query optimizer decides which format to use on a per-event basis.

11. Overview of MySQL replication

MySQL Replication is a good choice to ensure that the production system has failover reliability and makes it a fault-tolerant system, which is necessary for today's distributed and highly available systems.

This article introduces you to the important configuration and system changes that need to be made to replicate data on a single secondary server. Of course, because there is no configuration associated with or bound to the slave server on the master server, we can set up any number of slave servers without affecting the master server.

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.