您现在的位置是：网站首页 >服务部署 >Linux应用服务配置服务部署

【mysql、docker】MySQL数据库实现主从同步及Django下实现读写分离

admin2019年10月26日 21:33 【Django | Linux | MySQL 】 1047人已围观

Linux应用服务配置简介 各类Linux软件安装配置

# MySQL主从同步配置 ## 主从配置主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。异步复制，从服务器不需要一直连接着主服务器，从服务器甚至可以通过拨号断断续续地连接主服务器。通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。 ## 主从配置好处 - 如果主服务器出现问题，可以快速切换到从服务器提供的服务 - 通过增加从服务器来提高数据库的性能，在主服务器上执行写入和更新，在从服务器上向外提供读功能，可以动态地调整从服务器的数量，从而调整整个数据库的性能。 - 提高数据安全，因为数据已复制到从服务器，从服务器可以终止复制进程，所以，可以在从服务器上备份而不破坏主服务器相应数据。在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能。 ## 主从同步步骤 1. master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）； 2. slave将master的binary log events拷贝到它的中继日志(relay log)； 3. slave重做中继日志中的事件，将改变反映它自己的数据。 ## 主从配置的原理主从配置是基于二进制日志来实现的，主服务器在执行增删改查时会将相关指令都记录在二进制日志文件中，然后从服务器通过读取和执行该文件来保持和主服务器一致。 ## 安装Docker 环境为CentOS7 ```bash [root@centos7 ~]# yum install docker # 启动 [root@centos7 ~]# service docker start Redirecting to /bin/systemctl start docker.service # docker开机自启 [root@centos7 ~]# chkconfig docker on 注意：正在将请求转发到“systemctl enable docker.service”。 Created symlink from /etc/systemd/system/multi-user.target.wants/docker.service to /usr/lib/systemd/system/docker.service. ``` ## Docker启动MySQL ### 下载镜像 ```bash # 搜索 [root@centos7 ~]# docker search mysql INDEX NAME DESCRIPTION STARS OFFICIAL AUTOMATED docker.io docker.io/mysql MySQL is a widely used, open-source relati... 8605 [OK] docker.io docker.io/mariadb MariaDB is a community-developed fork of M... 2991 [OK] docker.io docker.io/mysql/mysql-server Optimized MySQL Server Docker images. Crea... 635 [OK] docker.io docker.io/percona Percona Server is a fork of the MySQL rela... 450 [OK] # 下载镜像 [root@centos7 ~]# docker pull docker.io/mysql # 查看镜像 [root@centos7 ~]# docker images REPOSITORY TAG IMAGE ID CREATED SIZE docker.io/mysql latest b8fd9553f1f0 8 days ago 445 MB ``` ### 启动主mysql ```bash [root@centos7 ~]# docker run -d --name mysql_master -p 33061:3306 -e MYSQL_ROOT_PASSWORD=mysql_pswd -v /home/appdata/mysql/master/data:/var/lib/mysql -v /home/appdata/mysql/master/conf/my.cnf:/etc/mysql/my.cnf mysql 1d89bf3ffaf57c613df7df9b968d951934bd23776064037ea4b5ae84f9c8fd03 ``` 1、`-d`：后台启动；这里直接后台启动即可，如果需要前台调试，用`-it`,或者直接不写。 2、`-p`：映射端口，容器的端口和宿主机的端口一一映射。 3、`-v`：文件系统共享。本地的`/home/appdata/mysql`目录和容器的`/var/lib/mysql`目录映射共享。 4、`–name`：给容器命名，便于后续直接通过`docker start mysql_master`来快速启动 5、`-e`：初始化mysql的`root`密码。 ```bash [root@centos7 ~]# ls /home/appdata/mysql/ [root@centos7 ~]# 并没有东西，说明真的要手动创建，容器才能正常运行 [root@centos7 ~]# [root@centos7 ~]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES [root@centos7 ~]# docker ps -a CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 1d89bf3ffaf5 mysql "docker-entrypoint..." About a minute ago Exited (1) About a minute ago mysql_master # 删除容器后重新创建，不指定文件映射 [root@centos7 ~]# docker rm mysql_master mysql_master # 启动mysql [root@centos7 ~]# docker run -d --name mysql_master -p 33061:3306 -e MYSQL_ROOT_PASSWORD=mysql_pswd mysql 955c8fa369cbad14455885f1c397da134fcd0c54bbf297652c065eff05015036 [root@centos7 ~]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 955c8fa369cb mysql "docker-entrypoint..." 5 seconds ago Up 4 seconds 33060/tcp, 0.0.0.0:33061->3306/tcp mysql_master [root@centos7 ~]# docker port mysql_master 3306/tcp -> 0.0.0.0:33061 ``` ### 启动从mysql ```bash # 启动从mysql [root@centos7 ~]# docker run -d --name mysql_slave -p 33062:3306 -e MYSQL_ROOT_PASSWORD=mysql_pswd mysql efa85a2ff4f59a34eb467bdc0ee3cc105e3621fd3d662b68b689b593e1e6941f # 查看启动好的主从mysql [root@centos7 ~]# docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES efa85a2ff4f5 mysql "docker-entrypoint..." 34 seconds ago Up 32 seconds 33060/tcp, 0.0.0.0:33062->3306/tcp mysql_slave 955c8fa369cb mysql "docker-entrypoint..." 22 hours ago Up 4 seconds 33060/tcp, 0.0.0.0:33061->3306/tcp mysql_master ``` - IP:33061 连接到主mysql：`docker start mysql_master` - IP:33062 连接到从mysql：`docker start mysql_slave` 但是不允许root直接外部连接。 ## 进入Docker配置mysql_master ```bash [root@centos7 ~]# docker exec -it mysql_master /bin/bash # 修改主mysql配置 root@955c8fa369cb:/# vim /etc/mysql/my.cnf # 但是这儿提示vim没有安装，在安装之前还需要进行update root@955c8fa369cb:/# apt-get update root@955c8fa369cb:/# apt-get install vim ``` ### 修改主mysql配置 ```bash # 修改mysql配置 root@955c8fa369cb:/# cd /etc/mysql/ root@955c8fa369cb:/etc/mysql# vim my.cnf [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql secure-file-priv= NULL # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # master config # 启动二进制日志系统【必要】 log-bin=mysql-bin # 服务器id，标识服务器实例，默认为1，主服务器server-id比从服务器server-id小，需要为唯一的值【必要】 server-id=1 # 值为0表示支持可读写、1的话表明仅支持可读 read-only=0 # 主从同步针对的数据库（除了mysql,sys,information_schema,performance_schema这些以外）的其它所有数据库都会进行同步（含数据库、表、数据等） replicate-ignore-db=mysql replicate-ignore-db=sys replicate-ignore-db=information_schema replicate-ignore-db=performance_schema # Custom config should go here !includedir /etc/mysql/conf.d/ ``` 配置参考 ```bash binlog-do-db=mysqldb # 表示只备份mysqldb，为数据表的名字，如果多个，则添加多行。 binlog_ignore_db=mysql # 表示忽略备份mysql。 # 不加binlog-do-db和binlog_ignore_db，那就表示备份全部数据库。 ``` ### 创建mysql同步账户并授权 ```bash # 进入mysql创建同步账号，并赋予权限 root@955c8fa369cb:/etc/mysql# mysql -uroot -p Enter password: mysql_pswd # 创建用户 CREATE USER 'user'@ 'X.X.X.X' IDENTIFIED BY '密码'; mysql> CREATE USER 'sync'@'%' IDENTIFIED BY 'sync_pswd'; Query OK, 0 rows affected (0.02 sec) # 查看指定用户的权限 mysql> show grants for 'sync'@'%'; +----------------------------------+ | Grants for sync@% | +----------------------------------+ | GRANT USAGE ON *.* TO `sync`@`%` | +----------------------------------+ 1 row in set (0.00 sec) # 主库上创建同步用户并授权 mysql> GRANT REPLICATION SLAVE ON *.* TO 'sync'@'%'; Query OK, 0 rows affected (0.01 sec) # 刷新权限，并查看该用户的权限 mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec) mysql> show grants for 'sync'@'%'; +----------------------------------------------+ | Grants for sync@% | +----------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO `sync`@`%` | +----------------------------------------------+ 1 row in set (0.00 sec) ``` ### 查看master状态 ```bash # 查询master的状态，这儿的File，Position数据在配置从库时需要用到 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 542 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) ``` ## 进入Docker配置mysql_slave ```bash [root@centos7 ~]# docker exec -it mysql_slave /bin/bash root@efa85a2ff4f5:/# apt-get update root@efa85a2ff4f5:/# apt-get install vim ``` ### 修改从mysql配置从库只需要开启`server-id`且值不能和主库相同，一般做备份仅开启这个就行了，如果计划在主库故障时，从库要替代主库，那么还需要开启`log-bin`。如果要指定只同步的数据库，需要添加`binlog-do-db=数据库名`，如果多个，添加多行即可；如果要指定忽略的数据库，指定`replicate-ignore-db` ```bash # 修改从库mysql配置 oot@efa85a2ff4f5:/# vim /etc/mysql/my.cnf # 在[mysqld]中添加 # slave config log-bin=mysql-bin server-id=2 replicate-ignore-db=mysql replicate-ignore-db=sys replicate-ignore-db=information_schema replicate-ignore-db=performance_schema # 重启Docker中的mysql root@efa85a2ff4f5:/# exit exit [root@centos7 ~]# docker restart mysql_slave mysql_slave # 重启进入 [root@centos7 ~]# docker exec -it mysql_slave /bin/bash root@efa85a2ff4f5:/# mysql -uroot -p Enter password: mysql_pswd ``` ### 配置从mysql连接到master 在从mysql中使用`change master to`命令就同步配置，意思就是指定谁是`master`，即同步谁的数据。需要指定主库当前的状态，即`File`，P`osition`，如果`change`失败，需要先`mysql> stop slave;`再执行。 ```bash # 配置从服务器slave mysql> change master to master_host='192.168.99.100',master_port=33061,master_user='sync',master_password='sync_pswd',master_log_file='mysql-bin.000002',master_log_pos=542; Query OK, 0 rows affected, 2 warnings (0.03 sec) ``` ### 开启slave 在从mysql中使用`start slave`命令来启动slave。 ```bash mysql> start slave; Query OK, 0 rows affected (0.01 sec) ``` ### 查看slave状态在从mysql中通过`show slave status\G`命令查看状态。 ```bash mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 192.168.99.100 Master_User: sync Master_Port: 33061 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 542 Relay_Log_File: efa85a2ff4f5-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Connecting Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql,sys,information_schema,performance_schema 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: 542 Relay_Log_Space: 155 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 2003 Last_IO_Error: error connecting to master 'sync@192.168.99.100:33061' - retry-time: 60 retries: 1 message: Can`t connect to MySQL server on '192.168.99.100' (113) Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_UUID: Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: 190925 12:24:50 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: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set (0.00 sec) ``` 此时发现错误 ```bash Last_IO_Error: error connecting to master 'sync@192.168.99.100:33061' - retry-time: 60 retries: 1 message: Can`t connect to MySQL server on '192.168.99.100' (113) ``` 另外两个进程状态，IO进程和SQL进程状态： ```bash Slave_IO_Running: Connecting # 该状态表示会一直尝试重连主，如果主正常了，该进程状态会自动变成Yes Slave_SQL_Running: Yes ``` ## 处理slave连接不上master问题 ### 防火墙允许主mysql被连接 ```bash # 报错问题：Can't connect to MySQL server on '192.168.99.100' (113) # 在从mysql的Docker中测试连接主mysql root@efa85a2ff4f5:/# mysql -usync -psync_pswd -h 192.168.99.100 --port=33061 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.99.100' (113) ``` 关闭宿主机防火墙，或者**允许防火墙**，由于测试CentOS，直接配置`firewall-cmd`即可 ```bash [root@centos7 ~]# firewall-cmd --zone=public --add-port=33061/tcp --permanent success [root@centos7 ~]# firewall-cmd --reload success ``` ### 测试连接主mysql及主从状态在从mysql的Docker中，测试连接主mysql，看是否能连接上，如果能连接，证明防火墙修改成功。 ```bash root@efa85a2ff4f5:/# mysql -usync -psync_pswd -h 192.168.99.100 --port=33061 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16 Server version: 8.0.17 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> ``` 登录从mysql，查看状态 ```bash mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.99.100 Master_User: sync Master_Port: 33061 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 542 Relay_Log_File: efa85a2ff4f5-relay-bin.000003 Relay_Log_Pos: 322 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql,sys,information_schema,performance_schema 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: 542 Relay_Log_Space: 704 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: aee839e0-dbaf-11e9-9b84-0242ac110002 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL 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: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set (0.00 sec) ERROR: No query specified ``` 从状态结果可以看到 ```bash Slave_IO_Running: Yes Slave_SQL_Running: Yes Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates # 表示所有的relay log全部读取完毕 ``` 当这两个值都是`Yes`是，表明主从同步已正常运行。 ## 测试主mysql创建数据表后从mysql变化登录**主**mysql，查看数据表 ```bash # 主mysql root@955c8fa369cb:/etc/mysql# mysql -uroot -pmysql_pswd mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.03 sec) ``` 在**从**mysql上查看数据表 ```bash # 从mysql mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.07 sec) ``` 在**主**mysql随意创建一个数据表 ```bash # 主mysql mysql> create database example; Query OK, 1 row affected (0.01 sec) # 查看状态 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 736 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) ``` 然后在**从**mysql查看 ```bash # 从mysql mysql> show databases; +--------------------+ | Database | +--------------------+ | example | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) # 查看状态 mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.99.100 Master_User: sync Master_Port: 33061 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 736 Relay_Log_File: efa85a2ff4f5-relay-bin.000003 Relay_Log_Pos: 516 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql,sys,information_schema,performance_schema 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: 736 Relay_Log_Space: 898 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: aee839e0-dbaf-11e9-9b84-0242ac110002 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL 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: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set (0.00 sec) ERROR: No query specified ``` 主库因修改了数据，`Position`会发生变化，同时从库中`Read_Master_Log_Pos: 736`也会指向主库的`Position`，如果结果不一致，且`Slave_IO_Running、Slave_SQL_Running`都值都为`Yes`时，则表明数据有延迟，还未同步完成。 ### Docker重启主mysql导致File变化另一种情况是，每次Docker重启后，mysql_master中状态的`File`会发生变化。 ```bash mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 155 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) # 停止Docker再开启 [root@centos7 ~]# docker stop mysql_master mysql_master [root@centos7 ~]# docker start mysql_master mysql_master [root@centos7 ~]# docker exec -it mysql_master /bin/bash root@955c8fa369cb:/# mysql -uroot -pmysql_pswd mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000004 | 155 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) ``` 也就是每次重新生成了binlog文件，所以从数据库需要修改关联的master。所以从数据库需要进行修改 ```bash mysql> stop slave; Query OK, 0 rows affected (0.01 sec) mysql> change master to master_host='192.168.99.100',master_port=33061,master_user='sync',master_password='sync_pswd',master_log_file='mysql-bin.000004',master_log_pos=155; Query OK, 0 rows affected, 2 warnings (0.01 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) ``` 注意，此时容易出现下面的错误，请等待一下 ```bash mysql> show slave status\G; # ... Slave_IO_Running: Connecting Slave_SQL_Running: Yes # ... Last_IO_Error: error connecting to master 'sync@192.168.99.100:33061' - retry-time: 60 retries: 2 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection. # 过一会就正常了 Slave_IO_Running: Yes Slave_SQL_Running: Yes ``` ### 主mysql中查看mysql-bin文件内容可以通过`mysqlbinlog`命令查看 ```bash root@955c8fa369cb:/# cd /var/lib/mysql/ root@955c8fa369cb:/var/lib/mysql# ls #innodb_temp binlog.000002 binlog.index client-cert.pem ib_buffer_pool ibdata1 mysql-bin.000001 mysql-bin.000004 performance_schema server-cert.pem undo_001 auto.cnf binlog.000003 ca-key.pem client-key.pem ib_logfile0 ibtmp1 mysql-bin.000002 mysql-bin.index private_key.pem server-key.pem undo_002 binlog.000001 binlog.000004 ca.pem example ib_logfile1 mysql mysql-bin.000003 mysql.ibd public_key.pem sys root@955c8fa369cb:/var/lib/mysql# mysqlbinlog mysql-bin.000004 | more ``` 可以看到里面有很多对数据库操作的命令，如果数据量很多，这将会有很多行，也就是一般恢复数据不会直接读取该文件。 ## 当业务从master转移到slave 从master转移到slave，即master因为某种原因宕机，业务已经连接slave进行读写，那么同步就会出错。 ### 从mysql添加了新数据例如在从mysql的example数据库中添加了新的表 ```bash # 从mysql mysql> show databases; +--------------------+ | Database | +--------------------+ | example | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) # 在example数据库中添加student表 mysql> use example; Database changed mysql> creat table student ( -> id int, -> name varchar(20) -> ); Query OK, 0 rows affected (0.01 sec) mysql> show tables; +-------------------+ | Tables_in_example | +-------------------+ | student | +-------------------+ 1 row in set (0.00 sec) # 也就是从库更新了数据，状态也是正常的 mysql> show slave status\G; # ... Slave_IO_Running: Yes Slave_SQL_Running: Yes ``` ### 主mysql也添加了新数据测试进入主库更新数据 ```bash # 主mysql mysql> use example; Database changed mysql> show tables; Empty set (0.00 sec) mysql> create table course (id int, name varchar(20)); Query OK, 0 rows affected (0.03 sec) mysql> show tables; +-------------------+ | Tables_in_example | +-------------------+ | course | +-------------------+ 1 row in set (0.00 sec) mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000004 | 372 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.01 sec) ``` ### 从mysql同步了主mysql新创建的数据查看从mysql状态变化 ```bash mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.99.100 Master_User: sync Master_Port: 33061 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 372 Relay_Log_File: efa85a2ff4f5-relay-bin.000002 Relay_Log_Pos: 539 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes # 而实际它有另外一张表，也就是假定master故障后slave创建的student表，当master启动后，会把master新创建的course表同步过来。 mysql> use example; Database changed mysql> show tables; +-------------------+ | Tables_in_example | +-------------------+ | course | | student | +-------------------+ 2 rows in set (0.00 sec) ``` # Django配置读写分离 ## 添加数据库在 settings.py 添加主从数据库信息 ```python DATABASES = { 'default': { 'ENGINE': 'django.db.backends.mysql', 'HOST': '192.168.99.116', 'PORT': 3306, # 主 'USER': 'root', # 主数据库用户名 'PASSWORD': 'password', # 主数据库密码 'NAME': 'database_name' # 主数据库名字 }, 'slave': { 'ENGINE': 'django.db.backends.mysql', 'HOST': '192.168.99.118', 'PORT': 3306, # 从数据库 'USER': 'root', # 从数据库用户名 'PASSWORD': 'password', # 从数据库密码 'NAME': 'database_name' # 从数据库名字 } } ``` ## 创建数据库操作路由分发类在项目中创建一个utils文件夹，添加 db_router.py 文件 ```python 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): """是否运行关联操作""" ``` ## 配置读写分离路由在 settings.py 中添加 ```python # 配置读写分离 DATABASE_ROUTERS = ['ProjectName.utils.db_router.MasterSlaveDBRouter'] # 指定路由分发类 ```