MYSQL主从复制 1. 环境主机地址端口号描述本机 (192.168.8.114)13306主本机 (192.168.8.114)23306从2. 授权外部访问use mysql;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedupdate user set Host='%' where user='root'; ERROR 1062 (23000): Duplicate entry '%-root' for key 'user.PRIMARY' // 不影响flush privileges;Query OK, 0 rows affected (0.00 sec)3. 异步主从文件位置：/etc/my.cnf1. 主机配置# [必选] 指定mysql唯一标识 server-id=1 # [可选] 指定binlog文件名称 log-bin=mysql-bin # [可选,建议填写] binlog的格式 (mixed/statement/row) binlog_format=mixed # [可选] 设置不要复制的数据库 binlog-ignore-db=mysql binlog-ignore-db=sys # [可选] 设置要复制的数据库 # binlog-do-db=需要复制的数据库 # binlog-do-db=需要复制的数据库2. 从机配置# [必选] 指定mysql唯一标识 server-id=2 # [可选] 指定binlog文件名称 # log-bin=mysql-bin # [可选] 配置中继日志 relay_log=mysql-slave-bin # [可选,建议填写] binlog的格式 (mixed/statement/row) binlog_format=mixed # [可选] 设置不要复制的数据库 # binlog-ignore-db=mysql # binlog-ignore-db=sys # [可选] 设置要复制的数据库 # binlog-do-db=需要复制的数据库 # binlog-do-db=需要复制的数据库[!IMPORTANT]修改配置后需要重启mysql服务3. 主机创建用户同步的账号CREATE USER 'salave1'@'%' IDENTIFIED BY '123456'; GRANT REPLICATION SLAVE ON *.* TO 'salave1'@'%'; # 不加这句 后面需要加上一句 ALTER USER 'salave1'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; flush privileges;4. 查询主机Master状态show master status;记录File，PositionFilePositionBinlog_Do_DBBinlog_Ignore_DBExecuted_Gtid_Setmysql-bin.0000011150 mysql,sys 1 row in set (0.00 sec)[!WARNING]执行完后不要再操作主数据库5. 从机配置同步指令CHANGE MASTER TO MASTER_HOST='主服务器IP', MASTER_USER='主服务器用户名', MASTER_PORT='3306', # 默认3306不用填写 MASTER_PASSWORD='主服务器密码', MASTER_LOG_FILE='mysql-bin.xxxxx', MASTER_LOG_POS=XXX; # GET_SOURCE_PUBLIC_KEY=1; 如果不设置为mysql_native_password,则需要加上这句# 例如: CHANGE MASTER TO MASTER_HOST='192.168.8.114', MASTER_PORT=13306, MASTER_USER='salave1', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1150;Query OK, 0 rows affected, 9 warnings (0.02 sec)启动start slave;Query OK, 0 rows affected, 1 warning (0.01 sec)停止#从库: stop slave; #主库: reset master;Query OK, 0 rows affected, 1 warning (0.01 sec)查看状态show slave status\G;需要保证下面两项为YesSlave_IO_Running: YesSlave_SQL_Running: Yes 1. row ** Slave_IO_State: Waiting for source to send event Master_Host: 192.168.8.114 Master_User: salave1 Master_Port: 13306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1150 Relay_Log_File: mysql-slave-bin.000002 Relay_Log_Pos: 326 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: 1150 Relay_Log_Space: 536 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: 1 Master_UUID: e363fcec-96ca-11f0-8795-2a4e6aa1306f Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica 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, 1 warning (0.00 sec)ERROR: No query specified验证在主数据库中新增数据库/表/数据, 查看同步状态其他提示[!CAUTION]当从数据库新增数据有冲突后无法正常同步先查看状态查看输出中的这些字段，它们会告诉你SQL线程为什么停止：Last_SQL_ErrorLast_SQL_ErrnoLast_SQL_Error_Timestamp原因A：重复的主键或数据冲突STOP SLAVE; SET GLOBAL sql_replica_skip_counter = 1; START SLAVE; # 方法B：在配置文件中设置自动跳过重复键错误 #编辑 my.cnf 文件： #[mysqld] #replica_skip_errors = 1062原因B：从库上手动修改了数据导致冲突-- 重新初始化从库数据 STOP REPLICA; RESET REPLICA ALL; -- 重新导入主库的数据快照，然后重新配置复制原因C：表不存在或结构不一致-- 检查具体的错误信息，然后手动创建缺失的表预防措施为了避免未来的重复键错误：确保从库初始数据与主库一致避免手动在从库上修改数据使用GTID复制（可以更好地处理冲突）-- 在主库和从库上启用GTID [mysqld] gtid_mode = ON enforce_gtid_consistency = ON -- 然后使用自动定位 CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.8.114', SOURCE_PORT=13306, SOURCE_USER='master1', SOURCE_PASSWORD='123456', SOURCE_AUTO_POSITION = 1, GET_SOURCE_PUBLIC_KEY=1;4. 半同步主从(生成环境)文件位置：/etc/my.cnf1. 主机配置[mysqld] # 加载插件 plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" # [必选] 指定mysql唯一标识 server-id=1 # [可选] 指定binlog文件名称 log-bin=mysql-bin # 插件配置 rpl_semi_sync_master_enabled = 1 # 在master开启半同步模式 rpl_semi_sync_master_timeout = 1000 # 主机在某次事务中的等待时间, 默认10000ms 超时后降为异步同步 # [可选,建议填写] binlog的格式 (mixed/statement/row) binlog_format=mixed # [可选] 设置不要复制的数据库 binlog-ignore-db=mysql binlog-ignore-db=sys # [可选] 设置要复制的数据库 # binlog-do-db=需要复制的数据库 # binlog-do-db=需要复制的数据库2. 从机配置[mysqld] # 加载插件 plugin-load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so" # [必选] 指定mysql唯一标识 server-id=2 # [可选] 指定binlog文件名称 # log-bin=mysql-bin # [可选] 配置中继日志 relay_log=mysql-slave-bin rpl_semi_sync_slave_enabled = 1 # [可选,建议填写] binlog的格式 (mixed/statement/row) binlog_format=mixed # [可选] 设置不要复制的数据库 # binlog-ignore-db=mysql # binlog-ignore-db=sys # [可选] 设置要复制的数据库 # binlog-do-db=需要复制的数据库 # binlog-do-db=需要复制的数据库# 查询插件状态 SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE '%semi%';3. 查询主机Master状态show master status;FilePositionBinlog_Do_DBBinlog_Ignore_DBExecuted_Gtid_Setmysql-bin.000002157 mysql,sys 1 row in set (0.00 sec)[!WARNING]执行完后不要再操作主数据库4. 从机配置同步指令CHANGE MASTER TO MASTER_HOST='主服务器IP', MASTER_USER='主服务器用户名', MASTER_PORT='3306', # 默认3306不用填写 MASTER_PASSWORD='主服务器密码', MASTER_LOG_FILE='mysql-bin.xxxxx', MASTER_LOG_POS=XXX; # GET_SOURCE_PUBLIC_KEY=1; 如果不设置为mysql_native_password,则需要加上这句# 例如: CHANGE MASTER TO MASTER_HOST='192.168.8.114', MASTER_PORT=13306, MASTER_USER='salave1', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=157;Query OK, 0 rows affected, 9 warnings (0.02 sec)启动start slave;Query OK, 0 rows affected, 1 warning (0.01 sec)停止#从库: stop slave; #主库: reset master;Query OK, 0 rows affected, 1 warning (0.01 sec)查看状态show slave status\G;需要保证下面两项为YesSlave_IO_Running: YesSlave_SQL_Running: Yes 1. row ** Slave_IO_State: Waiting for source to send event Master_Host: 192.168.8.114 Master_User: salave1 Master_Port: 13306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1150 Relay_Log_File: mysql-slave-bin.000002 Relay_Log_Pos: 326 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: 1150 Relay_Log_Space: 536 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: 1 Master_UUID: e363fcec-96ca-11f0-8795-2a4e6aa1306f Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica 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, 1 warning (0.00 sec)ERROR: No query specified验证在主数据库中新增数据库/表/数据, 查看同步状态5. 读写分离1. 引入时机大多数业务往往读多写少, 当达到业务瓶颈后, 需要使用到读写分离, 需要创建主从库, 主库用来写, 从库用来读主机: 写 , (读)从机: 读优化: 主库只写的话，可以不加索引，提升写的效率，从库增加多个索引读写分离架构可以消除读写锁的冲突从而提升数据库的读写性能但是需要注意：主从同步延时和读写分配机制的问题2. 主从同步延时优化方案:写后立刻读主库 (例如: 写入后5s从主库读, 之后从从库读)二次查询策略 (先从从库里面读, 读不到再去主库里读)业务特殊处理 (实时性要求高的放主库里读写, 次要业务全部从从库读)3. 落地基于编程实现 (应用层)优点: 灵活, 规则可自定义;缺点: 有入侵性, 多个从机选择规则(随机/权重/轮询),需要自定义基于数据库代理实现(数据网关)目前性能不错的数据库中间件MySQL Proxy：官方提供的MySQL中间件，实现负载均衡、读写分离等MyCat：阿里云开源CobarShardingSphere：Atlas：6. MySQL-Proxy的使用官网下载地址1. 复制到容器中docker cp E:\Downloads\mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz 4c7981af5e1e:/home2. 容器中解压tar -xvf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz3. 配置mysql-proxytouch /etc/mysql-proxy.cnf vi /etc/mysql-proxy.cnf[mysql-proxy] user=root # 运行用户 admin-username=root # 主库从库的账户名 admin-password=root # 主库从库的密码 log-level=debug # 日志级别 从高到底 (error|warning|info|message|debug) log-file=/var/log/mysql-proxy.log proxy-address=127.0.0.1:4040 # 运行端口, 默认4040 proxy-backend-addresses=192.168.8.144:13306 #,192.168.8.114:13307 # 指定后端主机Master写入数据,多个用,隔开,下同 proxy-read-only-backend-addresses=192.168.8.114:23306 # 指定后端从salve读取数据 proxy-lua-script=/home/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/share/doc/mysql-proxy/rw-splitting.lua admin-lua-script=/home/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/share/doc/mysql-proxy/admin-sql.lua daemon=true # 是否采用后台模式运行 keepalive=true # 当mysql-proxy崩溃时尝试重启使用中不建议加入中文注释,以下是简版[mysql-proxy] user=root admin-username=root admin-password=root log-level=debug log-file=/var/log/mysql-proxy.log proxy-address=127.0.0.1:4040 proxy-backend-addresses=192.168.8.144:13306 proxy-read-only-backend-addresses=192.168.8.114:23306 proxy-lua-script=/home/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/share/doc/mysql-proxy/rw-splitting.lua admin-lua-script=/home/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/share/doc/mysql-proxy/admin-sql.lua daemon=true keepalive=true[!CAUTION]rw-splitting.lua 中 设置了最小达到4个连接数的时候才开启读写分离, 测试时可做修改vim /home/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/share/doc/mysql-proxy/rw-splitting.lua找到if not proxy.global.config.rwsplit then​ proxy.global.config.rwsplit = {​ min_idle_connections = 4,​ max_idle_connections = 8,​ is_debug = false​ }end将min_idle_connections 设置为14. 相关命令cd /home/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/bin lsmysql-binlog-dump mysql-myisam-dump mysql-proxy5. 启动服务命令./mysql-proxy --defaults-file=/etc/mysql-proxy.cnf #./mysql-proxy --defaults-file=/etc/mysql-proxy.cnf --daemon ## 或使用后台模式 #nohup ./mysql-proxy --defaults-file=/etc/mysql-proxy.cnf > /var/log/mysql-proxy.log 2>&1 &6. 查询是否启动1. 查看日志cat /var/log/mysql-proxy.log2. 查看进程ps -ef | grep mysql

欢迎使用 Typecho 如果您看到这篇文章,表示您的 blog 已经安装成功.