Shulou(Shulou.com)05/31 Report--

This article shows you how to use Django to achieve MySQL read-write separation, the content is concise and easy to understand, it can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Introduction and advantages of master-slave synchronization

In multiple data servers, it is divided into master server and slave server. One master server corresponds to multiple slave servers.

The master server is only responsible for writing data, while the slave server is only responsible for synchronizing the data of the master server and allowing external programs to read the data.

After the master server writes the data, it immediately sends the command to write the data to the slave server, thus synchronizing the master-slave data.

The application can randomly read the data from the server, so that the pressure of reading the data can be shared.

When the slave server does not work, the whole system will not be affected; when the master server does not work, you can easily select one from the slave server to serve as the master server

The advantages of using master-slave synchronization:

Improve read and write performance

Because after master-slave synchronization, data writing and reading are carried out on different servers, and the read performance of the database can be improved by adding slave servers.

Improve data security

Because the data has been copied to the slave server, it can be backed up on the slave server without destroying the corresponding data on the master server.

Master-slave synchronization mechanism

Note: the master-slave synchronization between MySQL servers is based on the binary log mechanism. The master server uses binary logs to record changes in the database, and the slave server reads and executes the log file to keep consistent with the data of the master server. Docker install and run MySQL Slave

In this project, we build a master-slave synchronization. Master server: MySQL in the ubuntu operating system. Obtain the MySQL image from the MySQL in the server: Docker container # master-slave synchronization ensures that the versions of multiple MySQL are the same or similar as far as possible

Sudo docker image pull mysql:5.7.22 # version 5.7.22

Sudo docker load-I file path / mysql_docker_5722.tar

Specify the MySQL slave configuration file you need to prepare the slave configuration file before you can install and run the MySQL slave using Docker.

In order to quickly prepare the configuration file of the slave, we copy the configuration file of the host directly to the slave.

# create a MySQL slave configuration folder

Mkdir mysql_slave

# create a database data folder

Mkdir datacp-r / etc/mysql/mysql.conf.d. /

Modify MySQL slave configuration file and edit ~ / mysql_slave/mysql.conf.d/mysqld.cnf file.

Since the master and slave computers are all on the same computer, we choose to use different port numbers to distinguish between the master and slave computers. The slave port number is 8306.

# Slave port number

Port = 8306

# close the log

General_log = 0

# Slave unique number

Server-id = 2

Docker installs and runs MySQL slave to create root user's password is mysql sudo docker run-- name mysql-slave-e MYSQL_ROOT_PASSWORD=mysql-d-- network=host-v / home/python/mysql_slave/data:/var/lib/mysql-v / home/python/mysql_slave/mysql.conf.d:/etc/mysql/mysql.conf.d mysql:5.7.22

Test whether the slave has successfully created mysql-uroot-pmysql-h 127.0.0.1-- port=8306

Master-slave synchronization implementation

If there are any modifications to the configuration file of the configuration host (MySQL in ubuntu), you need to restart the host. Sudo service mysql restart

# enable log

General_log_file = / var/log/mysql/mysql.log

General_log = 1

# unique host number

Server-id = 1

# binary log file

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

Backup the original data of the slave host # if the slave needs the original data on the host during master-slave synchronization, one copy must be copied to the slave first

# 1. Collect the original data of the host

Mysqldump-uroot-pmysql-all-databases-- lock-all-tables > ~ / master_db.sql# 2. Copy the original host data from the slave

Mysql-uroot-pmysql-h227.0.0.1-- port=8306

< ~/master_db.sql 主从同步实现# 登录到主机 $ mysql -uroot -pmysql # 创建从机账号 $ GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by 'slave'; # 刷新权限 $ FLUSH PRIVILEGES; # 展示ubuntu中MySQL主机的二进制日志信息 $ SHOW MASTER STATUS; # 可以看到master_log_file和master_log_pos=Position # 登录到从机 $ mysql -uroot -pmysql -h 127.0.0.1 --port=8306 # 从机连接到主机 $ change master to master_host='127.0.0.1', master_user='slave', master_password='slave',master_log_file=XXX, master_log_pos=XXX; # 开启从机服务 $ start slave; # 展示从机服务状态 $ show slave status \G Django实现MySQL读写分离 增加slave数据库的配置 DATABASES = { 'default': { # 写（主机） 'ENGINE': 'django.db.backends.mysql', # 数据库引擎 'HOST': 'XXXX', # 数据库主机 'PORT': 3306, # 数据库端口 'USER': 'XX', # 数据库用户名 'PASSWORD': 'XXX', # 数据库用户密码 'NAME': 'XXX' # 数据库名字 }, 'slave': { # 读（从机） 'ENGINE': 'django.db.backends.mysql', 'HOST': 'XXXX', 'PORT': 8306, 'USER': 'XXX', 'PASSWORD': 'XXX', 'NAME': 'XXX' } } 创建和配置数据库读写路由 创建数据库读写路由# 在meiduo_mall.utils.db_router.py中实现读写路由 class MasterSlaveDBRouter(object): """数据库读写路由""" def db_for_read(self, model, **hints): """读""" return "slave" def db_for_write(self, model, **hints): """写""" return "default" def allow_relation(self, obj1, obj2, **hints): """是否运行关联操作""" return True 配置数据库读写路由# 配置数据库读写分离路由 DATABASE_ROUTERS = ['meiduo_mall.utils.db_router.MasterSlaveDBRouter'] 冷备份 冷备份发生在数据库已经正常关闭的情况下，当正常关闭时会提供给我们一个完整的数据库。冷备份是将关键性文件拷贝到另外位置的一种说法。对于备份数据库信息而言，冷备份是最快和最安全的方法。简单说法就是：离线（没有新的数据来）复制一份保存，有事就直接用这备份来恢复。 冷备份的优点： 是非常快速的备份方法（只需拷贝文件） 容易归档（简单拷贝即可） 容易恢复到某个时间点上（只需将文件再拷贝回去） 能与归档方法相结合，作数据库"最新状态"的恢复。 低度维护，高度安全。 冷备份的缺点： 单独使用时，只能提供到"某一时间点上"的恢复。 在实施备份的全过程中，数据库必须要作备份而不能作其它工作。也就是说，在冷备份过程中，数据库必须是关闭状态。 若磁盘空间有限，只能拷贝到磁带等其它外部存储设备上，速度会很慢。 不能按表或按用户恢复。 值得注意的是冷备份必须在数据库关闭的情况下进行，当数据库处于打开状态时，执行数据库文件系统备份是无效的 。而且在恢复后一定要把数据库文件的属组和属主改为mysql。热备份 热备份是在数据库运行的情况下，备份数据库操作的sql语句，当数据库发生问题时，可以重新执行一遍备份的sql语句。简单说就是：在线的保存对数据库操作的sql语句，有事就再跑一遍这些sql语句。# 在做主从同步时，如果从机需要主机上原有数据，就要先复制一份到从机 # 1. 收集主机原有数据 mysqldump -uroot -pmysql --all-databases --lock-all-tables >

~ / master_db.sql# 2. Copy the original data of the host from the slave (in the directory where the configuration file is located, the data will be written to the data file)

Mysql-uroot-pmysql-h227.0.0.1-- port=8306 < ~ / master_db.sql

Advantages of hot backup:

Can be backed up in table space or at the data file level with short backup time.

The database is still available at the time of backup.

Can achieve second-level recovery (restore to a certain point in time).

Almost all database entities can be restored.

Recovery is fast, in most cases while the database is still working.

Disadvantages of hot backup:

No mistakes can be made, or the consequences will be serious.

If the hot backup is not successful, the result cannot be used for point-in-time recovery.

Because it is difficult to maintain, you should be very careful not to "end in failure"

The above content is how to use Django to achieve MySQL read-write separation. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.